Irr strange result

H

hardgrafting

New Member
Joined
Feb 6, 2017
Messages
25
Hi,

I am trying to work out the IRR of the below set of cash flows, with the formula being =(1+IRR(A94:AJ94))^12-1

[TABLE="width: 5317"]
<tbody>[TR]
[TD="class: xl93, width: 92"] [/TD]
[TD="class: xl93, width: 91"][/TD]
[TD="class: xl93, width: 91"][/TD]
[TD="class: xl93, width: 91"][/TD]
[TD="class: xl93, width: 91"][/TD]
[TD="class: xl93, width: 91"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[TD="class: xl93, width: 90"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 5317"]
<tbody>[TR]
[TD="class: xl93, width: 92"] 660,512 [/TD]
[TD="class: xl93, width: 91"] 660,512 [/TD]
[TD="class: xl93, width: 91"] (3,672,280)[/TD]
[TD="class: xl93, width: 91"] 162,754 [/TD]
[TD="class: xl93, width: 91"] 163,090 [/TD]
[TD="class: xl93, width: 91"] (3,672,280)[/TD]
[TD="class: xl93, width: 90"] 38,606 [/TD]
[TD="class: xl93, width: 90"] 38,624 [/TD]
[TD="class: xl93, width: 90"] 38,906 [/TD]
[TD="class: xl93, width: 90"] 52,133 [/TD]
[TD="class: xl93, width: 90"] 163,169 [/TD]
[TD="class: xl93, width: 90"] 163,519 [/TD]
[TD="class: xl93, width: 90"] 48,625 [/TD]
[TD="class: xl93, width: 90"] 55,885 [/TD]
[TD="class: xl93, width: 90"] 55,993 [/TD]
[TD="class: xl93, width: 90"] 56,161 [/TD]
[TD="class: xl93, width: 90"] 56,279 [/TD]
[TD="class: xl93, width: 90"] 56,422 [/TD]
[TD="class: xl93, width: 90"] 56,600 [/TD]
[TD="class: xl93, width: 90"] 56,710 [/TD]
[TD="class: xl93, width: 90"] 151,806 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] (42,353)[/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 22,912 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 227,677 [/TD]
[TD="class: xl93, width: 90"] 88,178 [/TD]
[TD="class: xl93, width: 90"] 273,212 [/TD]
[TD="class: xl93, width: 90"] 273,212 [/TD]
[TD="class: xl93, width: 90"] 273,212 [/TD]
[TD="class: xl93, width: 90"] 273,212 [/TD]
[TD="class: xl93, width: 90"] 273,212 [/TD]
[TD="class: xl93, width: 90"] 273,212 [/TD]
[TD="class: xl93, width: 90"] 273,212 [/TD]
[TD="class: xl93, width: 90"] 273,212 [/TD]
[TD="class: xl93, width: 90"] 273,212 [/TD]
[TD="class: xl93, width: 90"] (511,564)[/TD]
[/TR]
</tbody>[/TABLE]




The result returns 435731.6% which is not sensible. Any ides why this could be? I deleted the last cash flow - 511k and the number becomes sensible (around 20%), however I need the IRR with the last cash flow included
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
First, your data goes through BG94, not AJ94.

I confirmed that the annualized IRR for AJ94:BG94 is indeed about 435732.67%, and for AJ94:BF94, it is indeed about 20.67%, which are close to your 435731.6% [sic] and "about 20%" [sic].

(The differences are probably because you posted rounded displayed values, but the underlying values have digits after the decimal point.)

Second, your cash flows are what is strange, IMHO. I suspect they are incorrect. If you want help with that, post an explanation of the numbers.

Because of the "strange" cash flows, the data in AJ94:BG94 have 3 IRRs between -99% and 200%. BTW, the data in AJ94:BF94 have 2 IRRs.

(I didn't bother to look beyond 200%.)

The "stranger" the cash flows, the more likely it is that there are multiple IRRs or even none at all. See the wiki IRR webpage for more information.

By coincidence, with the default "guess" (10%), IRR(AJ94:BG94) finds the highest IRR, but IRR(AJ94:BF94) finds the lower IRR.

The "inconsistent" behavior is not unexpected. First, mathematically, the iterative IRR method is unpredictable when there are multiple IRRs. Second, the Excel IRR implementation is flawed, IMHO. Another implementation might behave more "consistently" with this data.

However, IRR(AJ94:BG94,5%) finds the IRR that annualizes to about 18.71%, which is close to 20.66% for IRR(AJ94:BF94).

The multiple IRRs for the data in AJ94:BG94 can be seen by creating a table of the "NPV curve", which is the NPV for various discount rates between -99% and 200% (in this case).


[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]%discnt[/TD]
[TD="align: right"]npv[/TD]
[TD="align: right"][/TD]
[TD="align: right"]guess[/TD]
[TD="align: right"]monthly irr[/TD]
[TD="align: right"]annl irr[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]-99%[/TD]
[TD="align: right"]-5.09E+123[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-35%[/TD]
[TD="align: right"]-34.64%[/TD]
[TD="align: right"]-99.39%[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]-90%[/TD]
[TD="align: right"]-4.81E+64[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]1.44%[/TD]
[TD="align: right"]18.71%[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]-80%[/TD]
[TD="align: right"]-7.69E+46[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]101.04%[/TD]
[TD="align: right"]435732.67%[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]-70%[/TD]
[TD="align: right"]-2.79E+36[/TD]
[TD="align: right"][/TD]
[TD="align: right"]105%[/TD]
[TD="align: right"]101.04%[/TD]
[TD="align: right"]435732.67%[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]-60%[/TD]
[TD="align: right"]-9.91E+28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]-50%[/TD]
[TD="align: right"]-1.38E+23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]-40%
[/TD]
[TD="align: right"]-1.27E+18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]-30%
[/TD]
[TD="align: right"]1.62E+14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]-20%[/TD]
[TD="align: right"]2.82E+11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]-10%[/TD]
[TD="align: right"]8.32E+08[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]0%
[/TD]
[TD="align: right"]3.10E+06[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]-2.91E+06[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]-2.08E+06[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]-6.27E+04[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: right"]100%
[/TD]
[TD="align: right"]-5.05E+03[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: right"]110%[/TD]
[TD="align: right"]3.78E+04[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: right"]120%[/TD]
[TD="align: right"]6.99E+04[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]32[/TD]
[TD="align: right"]200%[/TD]
[TD="align: right"]1.55E+05[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
B2: =NPV(A2, $A$94:$BG$94)
E2: =IRR($A$94:$BG$94, D2)
F2: =(1+E2)^12 - 1
Copy the formulas down their columns


The inflection points (changes of sign) in column B suggest that there might be an IRR between -40% and -30%, 0% and 10%, and 100% and 110%. The midway "guesses" in column D demonstrate that Excel IRR does indeed return reasonable results.

FYI, the following table shows the "NPV curve" for the data in AJ94:BF94.


[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TH][/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[TH]K[/TH]
[TH]L[/TH]
[TH]M[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]%discnt[/TD]
[TD="align: right"]npv[/TD]
[TD="align: right"][/TD]
[TD="align: right"]guess[/TD]
[TD="align: right"]monthly irr[/TD]
[TD="align: right"]annl irr[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]-99%[/TD]
[TD="align: right"]2.76E+121[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"]1.58%[/TD]
[TD="align: right"]20.66%[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]-90%[/TD]
[TD="align: right"]3.04E+63[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]1.58%[/TD]
[TD="align: right"]20.66%[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]-80%[/TD]
[TD="align: right"]1.18E+46[/TD]
[TD="align: right"][/TD]
[TD="align: right"]105%[/TD]
[TD="align: right"]101.04%[/TD]
[TD="align: right"]435732.67%[/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]-10%[/TD]
[TD="align: right"]1.09E+09[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]0%
[/TD]
[TD="align: right"]3.61E+06[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]-2.91E+06[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]-2.08E+06[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]-6.27E+04[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="align: right"]100%
[/TD]
[TD="align: right"]-5.05E+03[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="align: right"]110%[/TD]
[TD="align: right"]3.78E+04[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="align: right"]120%[/TD]
[TD="align: right"]6.99E+04[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]32[/TD]
[TD="align: right"]200%[/TD]
[TD="align: right"]1.55E+05[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
I2: =NPV(H2, $A$94:$BF$94)
L2: =IRR($A$94:$BF$94, K2)
M2: =(1+L2)^12 - 1
Copy the formulas down their columns


The inflection points (changes of sign) in column I suggest that there might be an IRR between 0% and 10%, and 100% and 110%. The midway "guesses" in column K demonstrate that Excel IRR returns reasonable results.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top