IRR - Annual IRR calc based on montlhy cash flow

Corey

New Member
Joined
Mar 14, 2002
Messages
25
Does anyone know how to calculate the annual IRR on a monthly stream of cash flow? I don't think multiplying the calculated IRR by 12 produces the correct result.
 
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
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top