Different IRR result, same data, Excel 2007 vs 2010

[buckeye09]

I have a standard template that has an IRR formula written as =IRR(A1:A63,.02). .02 is the guess because generally speaking it is close to the value the formula will return and usually prevents outliers. I am in the process of converting this template from Excel 2007 to Excel 2010 and when I have the same exact data set using the same exact formula, I am getting different results from Excel 2007 to Excel 2010. I have tried copying the values and pasting only the values into blank 2007 and 2010 files to eliminate any errors in file conversion. If I write the formula with no guess "=IRR(A1:A63)" I get the same result in both 2007 and 2010.

I understand that IRR has 2 correct values and that he guess tries to direct Excel to the preferred result. I can change the guess to get this one to be consistent between 2007 and 2010, but my concern is going forward when this is being used as a template by many people that I need to have a consistent guess in the formula and won't be able to change it on a regular basis.

Below is the data set. In both 2007 and 2010, it returns 15.3616% with no guess in the formula. In 2007, it also returns 15.3616% even with the .02 guess. However in 2010 with .02 as the guess, it returns -0.6550%.

Any insight into a possible setting/add-in/other change I could make in 2010 to get the answer to be 15.3616% while still maintaining the .02 guess would be greatly appreciated.

<table border="0" cellpadding="0" cellspacing="0" width="67"><col style="width: 50pt;" width="67"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt; width: 50pt;" height="20" width="67"> (359,448)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 583 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> (359,448)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 458,793 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 458,793 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 818,824 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 818,824 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> (2,112,166)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 48,019 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 48,019 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 48,019 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 40,319 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 40,319 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 40,319 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 40,319 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 38,141 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 38,141 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 38,141 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 38,141 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 37,309 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 37,309 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 37,309 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 37,309 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 37,166 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 37,166 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 37,166 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 37,166 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 36,564 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 36,564 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 36,564 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 36,564 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 34,737 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 34,737 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 34,737 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 34,737 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 33,330 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 33,330 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 33,330 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 33,330 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 32,559 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 32,559 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 32,559 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 32,559 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 23,814 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 23,814 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 23,814 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 23,814 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 14,812 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 14,812 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 14,812 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 14,812 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 12,648 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 12,648 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 12,648 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 12,648 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 11,004 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 11,004 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 11,004 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> 11,004 </td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> (265,036)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> (265,036)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> (265,036)</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl86" style="height: 15pt;" height="20"> (265,036)</td> </tr> </tbody></table>

 

[brettdj]

As microsoft has chnaged the IRR algorithim from xl07 to xl10 presumbaly the guess parameter is being applied differently, as such i doubt you can change the IRR function

Interestingly all other guesses other than 0.02 give 15.62%
even 0.0199 and .0201

0.02 is smack on the number required to trigger the issue (actually 0.0199991)


So either you use IRR without guesses for consistency, which from your comments provides an less intutive answer for these cashflows, or you could perhaps use MIRR which applies a reinvestment rate, ie MIRR brings sanity by measuring total return not just the internal cashflows left in the project
MIRR(A1:A63,0,0)
gives 0.02%
MIRR with 4% finance/reinvestment gives
4.2%

I'd run with MIRR at a sensible return

Cheers

Dave

 

[Legacy 165240]

-359448 583 -359448 458793 458793 818824 818824 -2112166 48019 48019 48019 40319 40319 40319 40319 38141 38141 38141 38141 37309 37309 37309 37309 37166 37166 37166 37166 36564 36564 36564 36564 34737 34737 34737 34737 33330 33330 33330 33330 32559 32559 32559 32559 23814 23814 23814 23814 14812 14812 14812 14812 12648 12648 12648 12648 11004 11004 11004 11004 -265036 -265036 -265036 -265036

Well I put your dataset to test with Newton Raphson method with this online IRR Calculation tool that Excel uses to calculate IRR. With a default rate of 10% the IRR value is 15.26% however with guess rate of 0.02% the IRR value is -0.65%. But if you meant to use 2% as the guess rate instead of 0.02% then the IRR is still 15.36%. Are you sure you typed in 0.02 as the guess rate without the % or did you type in the guess rate as 0.02%. As 0.02 is taken as 2% but 0.02% is take as 0.0002

IRR Calculation with 10% as a Guess Rate results in an IRR of 15.36%

f(x) = -359448(1+i)^0 +583(1+i)^-1 -359448(1+i)^-2 +458793(1+i)^-3 +458793(1+i)^-4 +818824(1+i)^-5 +818824(1+i)^-6 -2112166(1+i)^-7 +48019(1+i)^-8 +48019(1+i)^-9 +48019(1+i)^-10 +40319(1+i)^-11 +40319(1+i)^-12 +40319(1+i)^-13 +40319(1+i)^-14 +38141(1+i)^-15 +38141(1+i)^-16 +38141(1+i)^-17 +38141(1+i)^-18 +37309(1+i)^-19 +37309(1+i)^-20 +37309(1+i)^-21 +37309(1+i)^-22 +37166(1+i)^-23 +37166(1+i)^-24 +37166(1+i)^-25 +37166(1+i)^-26 +36564(1+i)^-27 +36564(1+i)^-28 +36564(1+i)^-29 +36564(1+i)^-30 +34737(1+i)^-31 +34737(1+i)^-32 +34737(1+i)^-33 +34737(1+i)^-34 +33330(1+i)^-35 +33330(1+i)^-36 +33330(1+i)^-37 +33330(1+i)^-38 +32559(1+i)^-39 +32559(1+i)^-40 +32559(1+i)^-41 +32559(1+i)^-42 +23814(1+i)^-43 +23814(1+i)^-44 +23814(1+i)^-45 +23814(1+i)^-46 +14812(1+i)^-47 +14812(1+i)^-48 +14812(1+i)^-49 +14812(1+i)^-50 +12648(1+i)^-51 +12648(1+i)^-52 +12648(1+i)^-53 +12648(1+i)^-54 +11004(1+i)^-55 +11004(1+i)^-56 +11004(1+i)^-57 +11004(1+i)^-58 -265036(1+i)^-59 -265036(1+i)^-60 -265036(1+i)^-61 -265036(1+i)^-62

f'(x) = -583(1+i)^-2 +718896(1+i)^-3 -1376379(1+i)^-4 -1835172(1+i)^-5 -4094120(1+i)^-6 -4912944(1+i)^-7 +14785162(1+i)^-8 -384152(1+i)^-9 -432171(1+i)^-10 -480190(1+i)^-11 -443509(1+i)^-12 -483828(1+i)^-13 -524147(1+i)^-14 -564466(1+i)^-15 -572115(1+i)^-16 -610256(1+i)^-17 -648397(1+i)^-18 -686538(1+i)^-19 -708871(1+i)^-20 -746180(1+i)^-21 -783489(1+i)^-22 -820798(1+i)^-23 -854818(1+i)^-24 -891984(1+i)^-25 -929150(1+i)^-26 -966316(1+i)^-27 -987228(1+i)^-28 -1023792(1+i)^-29 -1060356(1+i)^-30 -1096920(1+i)^-31 -1076847(1+i)^-32 -1111584(1+i)^-33 -1146321(1+i)^-34 -1181058(1+i)^-35 -1166550(1+i)^-36 -1199880(1+i)^-37 -1233210(1+i)^-38 -1266540(1+i)^-39 -1269801(1+i)^-40 -1302360(1+i)^-41 -1334919(1+i)^-42 -1367478(1+i)^-43 -1024002(1+i)^-44 -1047816(1+i)^-45 -1071630(1+i)^-46 -1095444(1+i)^-47 -696164(1+i)^-48 -710976(1+i)^-49 -725788(1+i)^-50 -740600(1+i)^-51 -645048(1+i)^-52 -657696(1+i)^-53 -670344(1+i)^-54 -682992(1+i)^-55 -605220(1+i)^-56 -616224(1+i)^-57 -627228(1+i)^-58 -638232(1+i)^-59 +15637124(1+i)^-60 +15902160(1+i)^-61 +16167196(1+i)^-62 +16432232(1+i)^-63

x0 = 0.1
f(x0) = 89066.8452
f'(x0) = -2315851.4401
x1 = 0.1 - 89066.8452/-2315851.4401 = 0.138459654035
Error Bound = 0.138459654035 - 0.1 = 0.03846 > 0.000001

x1 = 0.138459654035
f(x1) = 19879.7093
f'(x1) = -1411369.1217
x2 = 0.138459654035 - 19879.7093/-1411369.1217 = 0.152545061633
Error Bound = 0.152545061633 - 0.138459654035 = 0.014085 > 0.000001

x2 = 0.152545061633
f(x2) = 1315.1518
f'(x2) = -1233868.4046
x3 = 0.152545061633 - 1315.1518/-1233868.4046 = 0.153610938548
Error Bound = 0.153610938548 - 0.152545061633 = 0.001066 > 0.000001

x3 = 0.153610938548
f(x3) = 5.9955
f'(x3) = -1222570.8339
x4 = 0.153610938548 - 5.9955/-1222570.8339 = 0.153615842597
Error Bound = 0.153615842597 - 0.153610938548 = 5.0E-6 > 0.000001

x4 = 0.153615842597
f(x4) = -0.0001
f'(x4) = -1222519.4638
x5 = 0.153615842597 - -0.0001/-1222519.4638 = 0.153615842518
Error Bound = 0.153615842518 - 0.153615842597 = 0 < 0.000001
IRR = x5 = 0.153615842518 or 15.36%


IRR Calculation with 0.02% as a Guess Rate results in an IRR of -0.65%

x0 = 0.0002
f(x0) = 222993.593
f'(x0) = 23021310.0631
x1 = 0.0002 - 222993.593/23021310.0631 = -0.00948639892268
Error Bound = -0.00948639892268 - 0.0002 = 0.009686 > 0.000001

x1 = -0.00948639892268
f(x1) = -149126.4923
f'(x1) = 56944143.8347
x2 = -0.00948639892268 - -149126.4923/56944143.8347 = -0.00686757840383
Error Bound = -0.00686757840383 - -0.00948639892268 = 0.002619 > 0.000001

x2 = -0.00686757840383
f(x2) = -14335.2911
f'(x2) = 45289333.8549
x3 = -0.00686757840383 - -14335.2911/45289333.8549 = -0.00655105153335
Error Bound = -0.00655105153335 - -0.00686757840383 = 0.000317 > 0.000001

x3 = -0.00655105153335
f(x3) = -47.0185
f'(x3) = 44025046.6542
x4 = -0.00655105153335 - -47.0185/44025046.6542 = -0.00654998353935
Error Bound = -0.00654998353935 - -0.00655105153335 = 1.0E-6 > 0.000001

x4 = -0.00654998353935
f(x4) = 0.5112
f'(x4) = 44020830.0357
x5 = -0.00654998353935 - 0.5112/44020830.0357 = -0.00654999515286
Error Bound = -0.00654999515286 - -0.00654998353935 = 0 < 0.000001
IRR = x5 = -0.00654999515286 or -0.65%

IRR Calculation with 2% as a Guess Rate results in an IRR of 15.36%


x0 = 0.02
f(x0) = 383133.2778
f'(x0) = -1026353.7243
x1 = 0.02 - 383133.2778/-1026353.7243 = 0.393295549852
Error Bound = 0.393295549852 - 0.02 = 0.373296 > 0.000001

x1 = 0.393295549852
f(x1) = -180960.1335
f'(x1) = -532762.1277
x2 = 0.393295549852 - -180960.1335/-532762.1277 = 0.053631515738
Error Bound = 0.053631515738 - 0.393295549852 = 0.339664 > 0.000001

x2 = 0.053631515738
f(x2) = 245717.8543
f'(x2) = -4588766.4763
x3 = 0.053631515738 - 245717.8543/-4588766.4763 = 0.107179207819
Error Bound = 0.107179207819 - 0.053631515738 = 0.053548 > 0.000001

x3 = 0.107179207819
f(x3) = 73298.3599
f'(x3) = -2083170.5117
x4 = 0.107179207819 - 73298.3599/-2083170.5117 = 0.142365170529
Error Bound = 0.142365170529 - 0.107179207819 = 0.035186 > 0.000001

x4 = 0.142365170529
f(x4) = 14476.3158
f'(x4) = -1356392.1052
x5 = 0.142365170529 - 14476.3158/-1356392.1052 = 0.153037833469
Error Bound = 0.153037833469 - 0.142365170529 = 0.010673 > 0.000001

x5 = 0.153037833469
f(x5) = 708.4109
f'(x5) = -1228612.4213
x6 = 0.153037833469 - 708.4109/-1228612.4213 = 0.153614427735
Error Bound = 0.153614427735 - 0.153037833469 = 0.000577 > 0.000001

x6 = 0.153614427735
f(x6) = 1.7297
f'(x6) = -1222534.284
x7 = 0.153614427735 - 1.7297/-1222534.284 = 0.153615842562
Error Bound = 0.153615842562 - 0.153614427735 = 1.0E-6 > 0.000001

x7 = 0.153615842562
f(x7) = -0.0001
f'(x7) = -1222519.4642
x8 = 0.153615842562 - -0.0001/-1222519.4642 = 0.153615842518
Error Bound = 0.153615842518 - 0.153615842562 = 0 < 0.000001
IRR = x8 = 0.153615842518 or 15.36%

 

[xenou]

It's an interesting result. I get the same result in 2010, and also tried using values slightly lower and higher than .02:
guess 0.0211111 returns .1536
guess 0.0199999 returns .1536
guess 0.02 precisely returns the mysterious -.6550%

I'm not knowledgeable enough about financial function to fathom this though I'll try to poke at it. It seems to me like something you may like to try to contact Microsoft or the Excel Team. Though one thing that I find odd about your "guess" its that it's nowhere near the correct answer. You say that .02 is usually about right ... are you sure about that?