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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
OK, say I have the following stream of monthly cash flows:

months 1-12 -125,000 each month
months 13-24 +75,000 each month
months 25-36 +75,000 each month
months 37-48 +75,000 each month
months 49-60 +75,000 each month

The annual IRR should be somewhere around 50%. Excel calculates a monthly IRR of around 3.3%. If I multiply that by 12 to annualize it, the result would be 39.6%, which is too low. Is there a formula that will take monthly cash flow and calculate an annual rate of return?

Thanks.
 
Upvote 0
Why don't you do the IRR calc. against annual figures: I.e., year 1 is $1.5 mil, etc...50% IRR? That's pretty large, almost too large....Good luck.

Formula, =irr(a1:a10,b1) helps where each cell in a1 through a10 contains annualized cash flows and b1 is your guess (close to where the IRR should fall) a reasonable guess is your hurdle rate. If you're well above your hurdle rate, you may want to double-check your assumptions.

Didn't notice your cash outflows, money for nothing?
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-15 14:38
 
Upvote 0
That is specifically what I am trying to avoid. There must be a formula out there that will do this without adding the second step of annualizing cash flow into yearly buckets before doing the calculation.

Any ideas?
 
Upvote 0
Well, one could try

0=(125000/(1+irr)^(1/12))+(125000/(1+irr)^(2/12))......

And so forth, matching the cf for the month with the exponential numerator.

Set this up in your spreadsheet so that the numerator of the exponentional and cash flows are references on a timeline and irr is an absolute reference. Do a goal seek to solve for the irr value where the sum equals 0.

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-15 14:55
 
Upvote 0
Also, the IRR is a hit and miss iteration calculation for Excel. Your goal seek will be most accurate if you set Excel to perform it's maximum amount of iterations.

Click tools->options->calculation. Check iteration and enter 32767 for the number of iterations. Probably helps to reduce your maximimum change amount as well.

Hope this helps.
 
Upvote 0
Hi Corey,

B1:B60 houses your cashflows
in A1 put 1/1/02 (US date convention)
in A2 put =DATE(YEAR(A1),MONTH(A1)+1,1)
copy down to A60

Wherever you want your result, place the following formula:
=XIRR(B1:B60,A1:A60,SIGN(SUM(B1:B60))*0.1)
=47.1899%

XIRR returns an annualized result, whereas IRR returns the period result and you must annualize the values yourself.

Slight differences you'll notice here is that I chose the dates to fall at the beginning of each month, so there are 28, 29, 30, or 31 days between cashflows. XIRR is sensitive to that, so be aware.

Suggestions for use...
1. Always give a guess. I chose to use +/- 10% depending on the siummation of the total cashflows.

2. If you have non-normal cashflows, where the sign changes more than once, you have multiple IRRs and the initial guess becomes exceedingly important. Even more important is that the result is almost meaningless in that case. It is a probelm with the concept more than the Excel function.

HTH,
Jay


HTH,
Jay

On 2002-03-15 14:22, Corey wrote:
OK, say I have the following stream of monthly cash flows:

months 1-12 -125,000 each month
months 13-24 +75,000 each month
months 25-36 +75,000 each month
months 37-48 +75,000 each month
months 49-60 +75,000 each month

The annual IRR should be somewhere around 50%. Excel calculates a monthly IRR of around 3.3%. If I multiply that by 12 to annualize it, the result would be 39.6%, which is too low. Is there a formula that will take monthly cash flow and calculate an annual rate of return?

Thanks.
 
Upvote 0
Notwithstanding the above problems with odd dates and multiple results, if your cashflows are definately monthly and you're looking to annualise then the following will convert it
=((1+0.033)^12)-1
Where 0.033 was your IRR result.

i.e. it's not just x 12. This gave me a result of 47.64% which was what I think you were expecting

XIRR is a very handy alternative though.
This message was edited by RaoulF on 2002-03-21 03:59
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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