Thanks:  0
Likes:  0

Thread: IRR - Annual IRR calc based on montlhy cash flow

1. Thanks Jay and RaoulF for the good information. I'll try both.

Jay - are you able to explaing what =((1+IRR)^12)-1 does. I know it accurately annualizes a monthly IRR, but need to clearly explain how to my boss. Any help you could provide would be appreciated.

Thanks,

Corey

2. Hi Corey,

Returns are geometrically linked -- think compound interest -- so you cannot just multiply the monthly returns by 12. That is an arithmetically linking and should not be used here.

IRR produces a rate of return based on a series of regular cashflows. It makes no distinction between cashflows that happen 7 days apart or 7 years apart. It just assumes that the intervals are equally spaced.

Sample cashflows
-100
50
10
10
65

IRR = 12.48%

Let's introduce two date ranges just for example.
First series:
Date / Cashflow
1/1/02 -100
1/1/03 50
1/1/04 10
1/1/05 10
1/1/06 65

XIRR returns 12.47%

Change the dates to 30 day intervals (1/1/02, 1/31/02, 3/2/02, 4/1/02, 5/1/02)

XIRR returns 318.05%

Same cashflows, only the timing of them plays a critical role.

OK, now let's see where the IRR function matches when you annualize it.

If we assume monthly intervals, then we should expect that the period return annualized would be the monthly return raised to the 12th power.

More precisely, the (1+IRR)^12 - 1 is calculated on a 360 day-basis year and 30 day intervals.

=(1+IRR)^(360/30)-1
=309.94%

To more closely match the XIRR use a 365 day-basis year
=(1+IRR)^(365/30) - 1
=318.05%

Sorry for the long-winded reply. In short, remember that intra-period returns are geometrically linked period to period. The calculation should be
=(1+IRR)^(365/day interval between each cashflow) - 1

Please note that this works with cashflows greater than a year apart. Suppose two years apart, then the power is 365/730.

Daily, Quarterly, Annually all calculated with teh same convention.

Hope this helps,
Jay

On 2002-03-21 07:34, Corey wrote:
Thanks Jay and RaoulF for the good information. I'll try both.

Jay - are you able to explaing what =((1+IRR)^12)-1 does. I know it accurately annualizes a monthly IRR, but need to clearly explain how to my boss. Any help you could provide would be appreciated.

Thanks,

Corey

3. Re: IRR - Annual IRR calc based on montlhy cash flow

Originally Posted by Corey
Thanks Jay and RaoulF for the good information. I'll try both.

Jay - are you able to explaing what =((1+IRR)^12)-1 does. I know it accurately annualizes a monthly IRR, but need to clearly explain how to my boss. Any help you could provide would be appreciated.

Thanks,

Corey
Actually, that formula does not tell the whole story that XIRR does. XIRR takes into account the number of days in the month (or more accurately, the number of days from the present or first cash flow date). If you have a monthly cash flow on the first of the month, annualizing it using that formula assumes that all months are the same length (~30.4 days). The differences are slight in most cases, but there is a difference, nonetheless.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•