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

 

[Sal Paradise]

If it is wrong, then it is affecting the NPV calculation too:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;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><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Year</td><td style=";">Schedule 1</td><td style="text-align: right;;"></td><td style=";">IRR:</td><td style="text-align: right;;"></td><td style=";">Schedule 2</td><td style="text-align: right;;"></td><td style=";">IRR:</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">-2,000,000</td><td style="text-align: right;;"></td><td style="text-align: right;;">20%</td><td style="text-align: right;;"></td><td style="text-align: right;;">-2,000,000</td><td style="text-align: right;;"></td><td style="text-align: right;;">63%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">NPV:</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">NPV:</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">10,000,000</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">10,000,000</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">-400,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-400,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">6</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">7</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">8</td><td style="text-align: right;;">-25,000,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-25,000,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">11</td><td style="text-align: right;;">10,000,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">10,000,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">12</td><td style="text-align: right;;">-250,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-250,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">13</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">14</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">15</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">16</td><td style="text-align: right;;">-250,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-250,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">17</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">18</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">19</td><td style="text-align: right;;">29,000,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">29,000,000</td><td style="text-align: right;;"></td><td style="text-align: right;;"></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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=IRR(<font color="Blue">B2:B21</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H2</th><td style="text-align:left">=IRR(<font color="Blue">F2:F21</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=NPV(<font color="Blue">D2,B2:B21</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">H5</th><td style="text-align:left">=NPV(<font color="Blue">H2,F2:F21</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Pete A]

It's correct. You can back check it "by hand" by discounting the cash flows using the formula
=[cash flow] / (1+[IRR])^[year]
Add them up and it should be 0.

As for the finance reason of why it jumps so much, (I'm not 100% sure that its the entire reason), it has to do with solving polynomial equations, and the fact that the string of cash flow in your table is not "conventional", meaning its not a cash in flow in the beginning, then a string of cash out flow, vice versa.

 

[IRR]

I have looked at the data for the two IRR problems, schedule1 and schedule2 and find as follows.

schedule1: IRR1 = 19.50% and IRR2 = 52.20%, ie two values.
If we take a MARR = 10% then RIC = 13.11%

schedule2: IRR = 62.74% I only found one value.
If we take a MARR = 10% then RIC = 16.36%

These two problems are both mixed (have investment and loan periods), so we require to use MARR (minimum acceptable rate of return) to calculate the RIC (return on invested capital) which is a better mearure of return for a mixed problem.

 

[Legacy 165240]

Moving -25,000,000 one step ahead t+1 of the time period makes it's discounted value lower than what it was at t

This means that a higher interest rate would be required to make the NPV = 0 or to make the profitability index = 1

Take the following two comparisons of discounted benefits and discounted costs at IRR of 19.5% (Schedule1) and IRR of 62.02% (Schedule2)
b(0.195005176715) = $8,251,564
c(0.195005176715) = $8,251,564
Profitability Index at IRR of 19.5% = 1

b(0.620241732216) = $2,383,460
c(0.620241732216) = $2,383,460
Profitability Index at IRR of 62.02% = 1

As you can see when the discount rate is 19.5% the benefits and costs are 8.25 million dollars yet at the discount rate of 62.02% the benefits and costs are only 2.38 million dollars

 

[Firefly2012]

Poster IRR has made the valuable point that the first set of cashflows (with IRR of c.20%) actually has a second IRR at 52.1%.

The second set has no IRR around the 20% level - it only has the 63% one (which is comparable to the 52.1% second IRR of the first set).

So the IRR function is returning the correct values even if it isn't immediately intuitive.

 

[MrVillareal]

You have Non-normal cash flows, like this: -++++--+++, in case like this you will have a multiple internal rates of return. If you are going evaluate a project as in your case, better if you use net present value instead of IRR.

 

[tusharm]

This is a known limitation with IRR. if your cash flows reverse direction more than once, IRR is not a reliable metric.

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

 

[energman58]

It isnt a bug - there is no unique value for IRR if the cashflow changes sign - you are effectively solving a polynomial.

However the Excel IRR function has an optional value which is called something like "guess" which tends to bias the answer to something that is sensible in terms of what you are trying to do. So if you set guess to be 20% I would expect your answer to be a sensible number even though 63% is equally valid. Though if the real answer is nowhere near 20% then it wont have an impact.

 

[Firefly2012]

It isnt a bug - there is no unique value for IRR if the cashflow changes sign - you are effectively solving a polynomial.

However the Excel IRR function has an optional value which is called something like "guess" which tends to bias the answer to something that is sensible in terms of what you are trying to do. So if you set guess to be 20% I would expect your answer to be a sensible number even though 63% is equally valid. Though if the real answer is nowhere near 20% then it wont have an impact.

There isn't an IRR around 20% for the second set of cashflows - setting a guess of 20% returns 63%.

So in this instance IRR is probably not the best tool to use to evaluate (at least not exclusively on its own anyway )