XIRR shows 0 instead of negative

[havenhurst]

Hello,

I'm getting an XIRR of 0 for an investment that is at a complete loss (so instead of getting the correct answer of -100%). My formula is simply =XIRR($D$31:BB31,$D$26:BB26), which refers to the relevant dates and cashflows (about a 6 year period,with an aggregate of $6.5m invested at different times during the period, and a cash flow of $0.001 at the end of the period to signify the total loss.Any thoughts? Could it have something to do with not inputting a guess (I tried inputting a negative number for the guess, but it returns a #NUM error? There is another investment in my spreadsheet that is also at a complete loss, but it properly shows the IRR as -100%, and really doesn't seem like there is a difference between the two formulas. Thanks!

 

[Fazza]

are the cashflows opposite signs? (positive & negative values)

 

[joeu2004]

I'm getting an XIRR of 0 for an investment that is at a complete loss (so instead of getting the correct answer of -100%). [....] There is another investment in my spreadsheet that is also at a complete loss, but it properly shows the IRR as -100%

First, I suspect that XIRR returns about 2.98E-09, not exactly zero. Format the cell as Scientific to determine the actual value. 2.98E-09 seems to be another error state, like #NUM and #DIV/0. It indicates that the IRR cannot be calculated. XIRR also returns 2.98E-09 when the first cash flow is zero.

Second, XIRR should never return exactly -100%. For the "other investment", try formatting the cell as Percentage with 13 decimal places. An IRR is a discount rate that causes the NPV to be (relatively close to) zero, where NPV = CF0 + CF1/(1+r) + CF2/(1+r)^2 +.... If "r" were exactly -100%, the first discounted term would be CF1/0, which is not calculable.

I have seen instances where XIRR returns less than -100% (i.e. more negative, like -110%). That is a defect. If the IRR were less than -100%, (1+r)^i would alternate signs, changing the sign of every other discounted cash flow, CFi/(1+r)^i. That is incorrect.

My formula is simply =XIRR($D$31:BB31,$D$26:BB26), which refers to the relevant dates and cashflows (about a 6 year period,with an aggregate of $6.5m invested at different times during the period, and a cash flow of $0.001 at the end of the period to signify the total loss.Any thoughts?

We really cannot help you without knowing the values and dates of all 51 cash flows. Ideally, provide the values formatted to display at least 15 significant digits.

However, mathematically, a "reasonable" IRR might not be calculable for some cash flows.

For example, consider the following 51 cash flows in B1:B51: -1000 every month except for a last cash flow of 0.001 (a "total loss"). IRR(B1:B51) returns #NUM . (Note that in this example, Excel IRR returns a monthly rate, whereas Excel XIRR always returns an annual rate.)

If we build a table of NPVs for varying discount rates starting with -99%, we can see why:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD="align: right"]-99%
[/TD]
[TD="align: right"]-1.01E+103
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD="align: right"]-90%
[/TD]
[TD="align: right"]-1.11E+53
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD="align: right"]-80%
[/TD]
[TD="align: right"]-1.11E+38
[/TD]
[/TR]
[TR]
[TD="align: right"]

--​

[/TD]
[TD="align: right"]----
[/TD]
[TD="align: right"]----
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD="align: right"]80%
[/TD]
[TD="align: right"]-1.25E+03
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD="align: right"]90%
[/TD]
[TD="align: right"]-1.11E+03
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD="align: right"]100%
[/TD]
[TD="align: right"]-1.00E+03
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD="align: right"]200%
[/TD]
[TD="align: right"]-5.00E+02
[/TD]
[/TR]
[TR]
[TD="align: right"]

--​

[/TD]
[TD="align: right"]----
[/TD]
[TD="align: right"]----
[/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD="align: right"]1000%
[/TD]
[TD="align: right"]-1.00E+02
[/TD]
[/TR]
[TR]
[TD]31
[/TD]
[TD="align: right"]2000%
[/TD]
[TD="align: right"]-5.00E+01
[/TD]
[/TR]
[TR]
[TD="align: right"]

--​

[/TD]
[TD="align: right"]----
[/TD]
[TD="align: right"]----
[/TD]
[/TR]
[TR]
[TD]68
[/TD]
[TD="align: right"]39000%
[/TD]
[TD="align: right"]-2.56E+00
[/TD]
[/TR]
[TR]
[TD]69
[/TD]
[TD="align: right"]40000%
[/TD]
[TD="align: right"]-2.50E+00
[/TD]
[/TR]
</tbody>[/TABLE]

The formula in F1 is =NPV(D1,$B$1:$B$51).

We can imagine that the NPV never "crosses" zero. That is, there is no "reasonable" discount rate that causes the NPV to be relatively close to zero. The operative word is "reasonable": when the discount rate is 10,000,000%, the NPV is about 0.01, which is "relatively close to" zero.

Note: We cannot use XNPV to build a similar table to study the behavior of XIRR and to determine a "guess". XNPV does not accept negative discount rates. There is no good reason for that limitation; IMHO, it is a defect. We must use SUMPRODUCT instead; for example, in F1:

=SUMPRODUCT($B$1:$B$51/(1+D1)^(($A$1:$A$51-$A$1)/365))

 

[joeu2004]

Errata....

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]F
[/TD]
[/TR]
</tbody>[/TABLE]
[....]
The formula in F1 is =NPV(D1,$B$1:$B$51).
[....]
when the discount rate is 10,000,000%, the NPV is about 0.01, which is "relatively close to" zero.

Sigh, tired eyes! All references to column F should be column E. And for 10,000,000%, the NPV is about -0.01, not +0.01.

 

[joeu2004]

I'm getting an XIRR of 0 for an investment that is at a complete loss (so instead of getting the correct answer of -100%). [....] There is another investment in my spreadsheet that is also at a complete loss, but it properly shows the IRR as -100%

We really cannot help you without knowing the values and dates of all 51 cash flows. Ideally, provide the values formatted to display at least 15 significant digits.

That is a request for the values and dates for the investment that seems to have an IRR of 0%.

But out of curiosity, I would also appreciate seeing the values and dates for the "other investment" that seems to have an IRR of -100%.

Some participants would prefer that you post the numbers (at least) in a posting here. That fine.

But I would also appreciate it if you uploaded an example Excel file (redacted) that demonstrates the problem and the "other investment" to a file-sharing website (e.g. box.net/files) and post the public/share URL in a response here. Please test the URL first, being careful to log out of the file-sharing website.

 

[DRSteele]

If all the cashflows have the same sign, IRR and NPV formulas are meaningless and produce errors in Excel. I think maybe you should just write a formula that determines whether the cashflows are all the same sign (excepting 0). If they are, the formula will report a message; if they are not, XIRR will be calculated. Like this. Copy B11 across.

	A	B	C	D	E

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]8/1/2017[/TD]
[TD="align: right"]-20000[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]-20000[/TD]
[TD="align: right"]20000[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]8/1/2018[/TD]
[TD="align: right"]-21000[/TD]
[TD="align: right"]21000[/TD]
[TD="align: right"]-21000[/TD]
[TD="align: right"]21000[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8/1/2019[/TD]
[TD="align: right"]-22000[/TD]
[TD="align: right"]22000[/TD]
[TD="align: right"]-22000[/TD]
[TD="align: right"]22000[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]8/1/2020[/TD]
[TD="align: right"]-23000[/TD]
[TD="align: right"]23000[/TD]
[TD="align: right"]-23000[/TD]
[TD="align: right"]23000[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]8/1/2021[/TD]
[TD="align: right"]-24000[/TD]
[TD="align: right"]24000[/TD]
[TD="align: right"]-24000[/TD]
[TD="align: right"]24000[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]8/1/2022[/TD]
[TD="align: right"]-25000[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]-25000[/TD]
[TD="align: right"]25000[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]8/1/2023[/TD]
[TD="align: right"]-26000[/TD]
[TD="align: right"]26000[/TD]
[TD="align: right"]-26000[/TD]
[TD="align: right"]26000[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8/1/2024[/TD]
[TD="align: right"]-27000[/TD]
[TD="align: right"]27000[/TD]
[TD="align: right"]-27000[/TD]
[TD="align: right"]27000[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8/1/2025[/TD]
[TD="align: right"]-28000[/TD]
[TD="align: right"]28000[/TD]
[TD="align: right"]-28000[/TD]
[TD="align: right"]28000[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]8/1/2026[/TD]
[TD="align: right"]900000[/TD]
[TD="align: right"]-900000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFF2CC"]Internal rate of Return:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]29.264%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]29.264%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]all losses[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]all gains[/TD]

</tbody>

Sheet21​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B11[/TH]
[TD="align: left"]=IF(COUNTIFS(B1:B10,"<>"&0)=MAX(FREQUENCY(SIGN(B1:B10),SIGN(B1:B10))),IF(SIGN(SUM(B1:B10))<0,"all losses","all gains"),XIRR(B1:B10,$A$1:$A$10))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]