IRR - Annual IRR calc based on montlhy cash flow

Corey · Mar 15th, 2002, 09:40 AM

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.

Al Chara · Mar 15th, 2002, 02:07 PM

Explain your problem a little more.

Corey · Mar 15th, 2002, 03:22 PM

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.

NateO · Mar 15th, 2002, 03:27 PM

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 ]

Corey · Mar 15th, 2002, 03:38 PM

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?

NateO · Mar 15th, 2002, 03:51 PM

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 ]

Corey · Mar 15th, 2002, 03:53 PM

Thank! Will give that a shot.

NateO · Mar 15th, 2002, 04:55 PM

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.

Jay Petrulis · Mar 20th, 2002, 11:54 PM

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

Quote:

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.

RaoulF · Mar 21st, 2002, 04:58 AM

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 ]

Mar 15th, 2002, 04:55 PM	#8
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	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. __________________ Regards, Nate Oliver Microsoft Excel MVP Nate's Excel Blog

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 15th, 2002, 09:40 AM	#1
Corey New Member Join Date: Mar 2002 Location: Seattle, WA Posts: 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.

Mar 15th, 2002, 02:07 PM	#2
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Explain your problem a little more.

Mar 15th, 2002, 03:22 PM	#3
Corey New Member Join Date: Mar 2002 Location: Seattle, WA Posts: 25	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.

Mar 15th, 2002, 03:27 PM	#4
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	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 ]

Mar 15th, 2002, 03:38 PM	#5
Corey New Member Join Date: Mar 2002 Location: Seattle, WA Posts: 25	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?

Mar 15th, 2002, 03:51 PM	#6
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	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 ]

Mar 15th, 2002, 03:53 PM	#7
Corey New Member Join Date: Mar 2002 Location: Seattle, WA Posts: 25	Thank! Will give that a shot.

Mar 21st, 2002, 04:58 AM	#10
RaoulF New Member Join Date: Mar 2002 Location: London, UK Posts: 17	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 ]