IRR miscalculation help

[JoeSarton]

Hello all -

I have a set of cash flows that occur annually over 20 years (listed below). When I use the IRR function, it returns an IRR of ~20%. However, if I push the large payment in year 9 out one year, so that it occurs in year 10, the IRR jumps all the way up to ~63%. While I understand the change in IRR will not be linear, this jump seems much to great (if I push it 1 further period back or 1 period earlier, the move is <10%). It would be helpful if someone can explain why this jump is happening and if there is any way to fix it.

Thanks in advance for the help!

Year 1 Cash flow = -2,000,000
Y2 = 0
Y3 = 0
Y4 = +10,000,000
Y5 = -400,000
Y6 = 0
Y7 = 0
Y8 = 0
Y9 = -25,000,000
Y10 = 0
Y11 = 0
Y12 = +10,000,000
Y13 = -250,000
Y14 = 0
Y15 = 0
Y16 = 0
Y17 = -250,000
Y18 = 0
Y19 = 0
Y20 = +29,000,000

 

[MrVillareal]

try this illustration,

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">-1.6</td><td style="text-align: right;;"></td><td style=";">IRR</td><td style="text-align: right;;">400.0000%</td><td style=";">NPV</td><td style="text-align: right;;">($0.00)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style=";">IRR</td><td style="text-align: right;;">25.0000%</td><td style=";">NPV</td><td style="text-align: right;;">$0.00 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">-10</td><td style="text-align: right;;"></td><td style=";">Cost of Capital</td><td style="text-align: right;;">30.0000%</td><td style=";">NPV</td><td style="text-align: right;;">$0.13 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th with="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F1</th><td style="text-align:left">=NPV(<font color="Blue">D1,$A$1:$A$3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F2</th><td style="text-align:left">=NPV(<font color="Blue">D2,$A$1:$A$3</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F3</th><td style="text-align:left">=NPV(<font color="Blue">D3,$A$1:$A$3</font>)</td></tr></tbody></table></td></tr></table><br />

as you can see there are 2 IRR 25% and 400%. Cost of capital of 30% using IRR as your basis will reject the project based on 25% IRR while accept it if based on 400% IRR. Actually the positive NPV in this case is between 25% and 400%. So better based your evaluation using NPV instead of IRR. Hope this will give you clarity in your case. This is one limitation of IRR with nonnormal cash flows.

 

[energman58]

So the answer is 63%! There is probably another value for the 20% which is closer to the 63% one then but as IRR are almost invariably not unique in cases where the cashflow changes sign it probably isnt the best measure to use here.

 

[IRR]

In a previous post I outlined my findings for this interesting problem.

You have observed that both schedule 1 and 2 are mixed problems, that is they have some periods of investing and others of lending.

Note that non-normal cash flows (as in this problem) do not always mean we have a mixed problem and a mixed problem does not always mean that we have multiple IRRs. (schedule2 is mixed but only has 1 real IRR).

Recall that schedule1 has 2 real values of IRR and schedule 2 has only 1 real value of IRR as far as I can see.

-----------------------------------------------------------------------
"Normal IRR theory" for a mixed problem assumes that the rate for investing is the same as that for lending and is called the IRR.
-----------------------------------------------------------------------

This is not at all realistic and work by "Teichroew, Robichek and Montalbano" assumed a more realistic value of the rate for lending to be MARR (minimum acceptable rate of return) in order to calculate the rate for investing and called this the RIC (return on invested capital) which is an excellent measure of return for a mixed problem.

Teichroew, Robichek and Montalbano, “Mathematical Analysis of
Rates of Return under Certainty,” Management Science, Vol. 11 (January 1965).
Teichroew, Robichek and Montalbano, “An Analysis of Criteria for
Investment and Financing Decisions under Certainty,” Management Science, Vol. 12 (November
1965).

For a worksheet related discussion see Investment Economics - IRR/RIC by PA Jensen.
http://www.me.utexas.edu/~jensen/ORMM/omie/computation/unit/econ_add/irr_err.html
This uses an add-in to a worksheet to calculate RIC.

 

[IRR]

Comment on the post by MrVillareal on the problem (-1.6, 10, -10).

You correctly stated that this problem has multiple IRRs of 25% and 400% and has positive NPVs between these IRRs. (A mixed IRR problem).

You state that at a cost of capital of 30% this is a good project - I agree.

The first period is "investing" and the second period is "lending".
If we take the lending rate to be 30% then the investing rate becomes 44.23% so the return on invested capital (RIC) is 44.23%.

Note that the RIC gives a unique solution for a mixed problem (which may have multiple solutions) which is consistent with NPV findings.

We no longer need dismiss mixed problems as having no realistic solution. Use the return on invested capital (RIC).