Explain your problem a little more.
This is a discussion on IRR - Annual IRR calc based on montlhy cash flow within the Excel Questions forums, part of the Question Forums category; Does anyone know how to calculate the annual IRR on a monthly stream of cash flow? I don't think multiplying ...
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.
Explain your problem a little more.
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.
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, NateO
[ This Message was edited by: NateO on 2002-03-15 14:38 ]
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?
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, NateO
[ This Message was edited by: NateO on 2002-03-15 14:55 ]
Thank! Will give that a shot.
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.
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.
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 ]
Like this thread? Share it with others