Excel IRR vs IRR calculations - can someone confirm my results?

[jg60036]

I am working on calculating IRR for a project we are considering.

Here is the projected cash flow:

[TABLE="width: 4325"]
<colgroup><col span="12"><col><col span="26"><col><col><col><col><col span="10"></colgroup><tbody>[TR]
[TD="align: right"]($1,354,953)[/TD]
[TD="align: right"]($989,464)[/TD]
[TD="align: right"]($462,155)[/TD]
[TD="align: right"]($462,155)[/TD]
[TD="align: right"]$2,474,935[/TD]
[TD="align: right"]($462,155)[/TD]
[TD="align: right"]($657,155)[/TD]
[TD="align: right"]($1,104,155)[/TD]
[TD="align: right"]($717,205)[/TD]
[TD="align: right"]($717,205)[/TD]
[TD="align: right"]$1,498,588[/TD]
[TD="align: right"]$2,615,328[/TD]
[TD="align: right"]$1,626,345[/TD]
[TD="align: right"]($1,201,909)[/TD]
[TD="align: right"]($685,109)[/TD]
[TD="align: right"]($685,109)[/TD]
[TD="align: right"]$2,251,981[/TD]
[TD="align: right"]$4,327,696[/TD]
[TD="align: right"]($640,059)[/TD]
[TD="align: right"]($1,094,559)[/TD]
[TD="align: right"]($704,094)[/TD]
[TD="align: right"]($704,094)[/TD]
[TD="align: right"]$1,507,011[/TD]
[TD="align: right"]$5,951,596[/TD]
[TD="align: right"]($1,507,655)[/TD]
[TD="align: right"]($1,150,655)[/TD]
[TD="align: right"]($633,855)[/TD]
[TD="align: right"]($633,855)[/TD]
[TD="align: right"]$2,303,235[/TD]
[TD="align: right"]$4,378,950[/TD]
[TD="align: right"]($585,641)[/TD]
[TD="align: right"]($1,040,141)[/TD]
[TD="align: right"]($653,191)[/TD]
[TD="align: right"]($653,191)[/TD]
[TD="align: right"]$1,557,914[/TD]
[TD="align: right"]$5,972,499[/TD]
[TD="align: right"]($815,491)[/TD]
[TD="align: right"]($508,213)[/TD]
[TD="align: right"]($508,213)[/TD]
[TD="align: right"]$976,907[/TD]
[TD="align: right"]($505,999)[/TD]
[TD="align: right"]$4,401,806[/TD]
[TD="align: right"]($681,949)[/TD]
[TD="align: right"]($481,799)[/TD]
[TD="align: right"]($481,799)[/TD]
[TD="align: right"]$1,003,321[/TD]
[TD="align: right"]$2,888,121[/TD]
[TD="align: right"]($454,849)[/TD]
[TD="align: right"]($198,237)[/TD]
[TD="align: right"]($198,237)[/TD]
[TD="align: right"]($198,237)[/TD]
[TD="align: right"]($198,237)[/TD]
[TD="align: right"]$3,171,683[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]2/1/2014[/TD]
[TD="align: right"]3/1/2014[/TD]
[TD="align: right"]4/1/2014[/TD]
[TD="align: right"]5/1/2014[/TD]
[TD="align: right"]6/1/2014[/TD]
[TD="align: right"]7/1/2014[/TD]
[TD="align: right"]8/1/2014[/TD]
[TD="align: right"]9/1/2014[/TD]
[TD="align: right"]10/1/2014[/TD]
[TD="align: right"]11/1/2014[/TD]
[TD="align: right"]12/1/2014[/TD]
[TD="align: right"]1/1/2015[/TD]
[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]3/1/2015[/TD]
[TD="align: right"]4/1/2015[/TD]
[TD="align: right"]5/1/2015[/TD]
[TD="align: right"]6/1/2015[/TD]
[TD="align: right"]7/1/2015[/TD]
[TD="align: right"]8/1/2015[/TD]
[TD="align: right"]9/1/2015[/TD]
[TD="align: right"]10/1/2015[/TD]
[TD="align: right"]11/1/2015[/TD]
[TD="align: right"]12/1/2015[/TD]
[TD="align: right"]1/1/2016[/TD]
[TD="align: right"]2/1/2016[/TD]
[TD="align: right"]3/1/2016[/TD]
[TD="align: right"]4/1/2016[/TD]
[TD="align: right"]5/1/2016[/TD]
[TD="align: right"]6/1/2016[/TD]
[TD="align: right"]7/1/2016[/TD]
[TD="align: right"]8/1/2016[/TD]
[TD="align: right"]9/1/2016[/TD]
[TD="align: right"]10/1/2016[/TD]
[TD="align: right"]11/1/2016[/TD]
[TD="align: right"]12/1/2016[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]2/1/2017[/TD]
[TD="align: right"]3/1/2017[/TD]
[TD="align: right"]4/1/2017[/TD]
[TD="align: right"]5/1/2017[/TD]
[TD="align: right"]6/1/2017[/TD]
[TD="align: right"]7/1/2017[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD="align: right"]9/1/2017[/TD]
[TD="align: right"]10/1/2017[/TD]
[TD="align: right"]11/1/2017[/TD]
[TD="align: right"]12/1/2017[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]5/1/2018[/TD]
[/TR]
</tbody>[/TABLE]

A simple XIRR calculation returned a 226.7% IRR.

This seems way too high for me, so I used IRR on the same cash flows(without dates) to return a IRR of 10.348%. From reading this forum I understand this is the rate of return for a period, which in this case is months, so I used (1+.10348)^12-1 to return an annual IRR of 225.97%, which is close to my XIRR calc.

If someone could help me out and confirm my logic & calculations are correct?

I am also concerned I did not annualize the IRR correctly. Since my project timeline is 53 months and the IRR returned a value of 10.348% monthly return, do I need my calculation to be (1+.10348)^53-1? When I do this it returns a ridiculous 18,000%, but logically if the return is 10.348% a month, wouldn't I need to include all the months of the project to get the actual IRR?

Thanks for your help and comments!

 

[circledchicken]

Hi and welcome to the forum,

Yes, I think you are correct in this case.

XIRR gives you an annualised return, so the 226.7% is comparable to the 225.97% (remember they are slightly different because XIRR takes into account the actual dates rather than assuming equal length periods like IRR).

To convert the XIRR figure for comparison against the monthly IRR, you can approximate it as:
=((1+ 226.7%) ^ (1 / 12)) - 1 = 10.269%, which is close to your original IRR.

 

[jg60036]

Circledchicken,

Thank you for the reply and the welcome. This is a great forum for Excel help and am looking forward to contributing in the future!

Also, I forgot to add in my original post I am using Windows 7 / Excel 2007.

Maybe someone can help me interpret the 226.7% IRR? I thought this calculation must be wrong because I have a total project cost of $40.98 million and net revenue of $65.16 million, for a NOI of $24.18 million. But, a 226.7% IRR would imply more than tripling my investment, right? SO, I would expect a NOI of $120+ million from $40 in total costs, but obviously this is incorrect!

Thanks for your help & insight!

 

[xenou]

Maybe someone can help me interpret the 226.7% IRR? I thought this calculation must be wrong because I have a total project cost of $40.98 million and net revenue of $65.16 million, for a NOI of $24.18 million. But, a 226.7% IRR would imply more than tripling my investment, right?

Your numbers here are not consistent with the data you posted (costs -- cash outflows or investment -- is approx. 24.7 and and revenues -- cash inflow or return on investment -- is approx 45.7).

It's hard to give advice when millions are at stake. The project is clearly profitable if you can survive the first year (and the estimates are reliable). You get all your money back within a year, and profits roll in after that. Is this real?

 

[jg60036]

I think the discrepancy stems from the cash positive months being net costs. For instance, if we spent $500k in a month but have a revenue event of $2.5 million, the number that shows up in the data I posted is $2 million. But, the sum of all the cash flows in OP do equal $24.176 mil ($24.18 rounded).

Yes, the project is real and yes, it is clearly profitable. However, I still need to be able to better explain the 226.7% project IRR, assuming the calculation is correct, which I am unable to do at this point. I think the high return is a construct of the cash flow timing and if I pushed back all the revenue events 6 months and had more speculative construction, the project IRR would decrease sharply. This is what I will work on tomorrow and will update with the results!

 

[xenou]

Okay. The 226% means (basically) if your borrowing costs are 226% (annual rate) you would be able to take on this project (with no ultimate income or loss -- in present value terms). The general point of IRR is the higher the number, the more profitable the project. I'm not sure I believe the 226% , either though. Plugging that back into an NPV formula, it doesn't reduce cash flows to zero. But NPV in Excel is supposed to be 1 cash outflow (the "investment") followed by cash inflows (the "return"). So maybe this doesn't really work "in reverse" as an NPV problem. For such a large and expensive project you might want to consider this in other respects:

1) are there alternative uses for this cash (including, ultimately, just investing it in securities on the open market)
2) what is your actual cost of capital, required rate of return, etc.
3) how risky is the investment
4) how much borrowing will you require to get through the first year (vs. cash reserves that you can invest)
5) what is the income tax savings effect of the first year losses

Note:
You might try putting this into a "yearly" rather than "monthly" schedule just for kicks. The first year would be your initial investment (cash outflow), and subsequent years would be net positive therefore cash inflows. That would make it a simpler problem and the result might be interesting.

 

[circledchicken]

I'm not sure I believe the 226% , either though. Plugging that back into an NPV formula, it doesn't reduce cash flows to zero. But NPV in Excel is supposed to be 1 cash outflow (the "investment") followed by cash inflows (the "return"). So maybe this is a different problem.

Hi xenou,

I don't think this is correct. The rate to plug in to the NPV function would be the ~10% (non-annualised) result of the IRR calculation. You should then get 0 as expected.

 

[jg60036]

Circled,

Yes, I did this check and it does in fact reduce NPV to zero (or very close).

Sure enough, as I change the cash flow assumptions for more speculative building IRR drops sharply. I kept all construction on the same timeline and moved all sales back 3 months resulting in a new IRR of 150%. Still very good, but demonstrates the risk of not meeting sales targets.

 

[xenou]

I don't think this is correct. The rate to plug in to the NPV function would be the ~10% (non-annualised) result of the IRR calculation. You should then get 0 as expected.

Thanks. It is correct. I was doing something dumb like thinking the 226% was an APR.