XIRR function returning zero, when it should be a negative value

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

Ditto Glenn's comment. We cannot "solve" the problem without concrete data.

At a minimum, the dates and values (showing 15 significant digits, if they are calculated) and the XIRR formula that you entered (copy from the Formula Bar).

But an Excel file attachment is best.

BTW, the value returned by XIRR might be about +/-2.98E-09, not zero. Format the cell as General to confirm.

@excelnerd95.... As I suspected and wrote before, XIRR is returning 2.98E-09, not zero.

Sometimes, we can treat 2.98E-09 as an indication that XIRR requires a "guess" to help its internal algorithm -- just like #NUM (and #DIV/0).

(But sometimes +/-2.98E-09 means that the cash flow model is incorrect.)

For these examples, we can use a "guess" of -10%. So:

CH5: =XIRR(A5:CF5,$A$4:$CF$4,-10%) returns about -16.88% (-0.16877049663338)
CH6: =XIRR(A6:CF6,$A$4:$CF$4,-10%) returns about -5.29% (-0.0528733318201716)
CH7: =XIRR(A7:CF7,$A$4:$CF$4,-10%) returns about -0.66% (-0.00655321908134283)

A guess of -10% will not work in all cases. It is just a coincidence that it works for all 3 examples.

-----

Caveat: Starting with a cash flow -1 in Jul 2014 might be a misguided work-around that results in a misleading IRR. It discounts all cash flow models to Jul 2014. I suspect that the intent is to work around the fact that Excel XIRR does not like a cash flow model that starts with zero cash flows, and the first cash flows are really -499998.84 in Apr 2019, -1007541.31 in Oct 2014, and -50003.10 in Dec 2016.

LMK if you want help with setting up XIRR to start with the first non-zero cash flow.

PS.... In this case, the work-around is not so bad. I suspect that the correct formulas are =XIRR(BF5:CF5,$BF$4:$CF$4), =XIRR(D6:CF6,$D$4:$CF$4) and =XIRR(AD7:CF7,$AD$4:$CF$4). But the difference in the results is relatively small (-3.47E-06 to -2.30E-07). So it might not be worth the trouble to implement the "correct" formula that starts with the first non-zero cash flow.

That said, there might be circumstances where the work-around makes an unintended significant difference. And there might be circumstances where -1 is not a good work-around; in other words, -1 works for these examples only by coincidence.

[.... deleted by me .... replaced by posting #10 ....]

[.... deleted by me .... replaced by posting #10 ....]

(I decided to repost my explanation, because I have made so many improvements over the past 2 days. Sorry for the incessant changes and postings.)

Originally Posted by excelnerd95

How did you figure out -10%?

Experience; and dumb luck! I learned over time that -10% often works when 10% (default) does not. Often, there is no good reason for -10% to work. It's the bizarre nature of the IRR methodology in general and the Excel implementation in particular.

The more methodical way to find a "guess" is to look at the "NPV curve", as described below. Sometimes, that leads to the discovery that there are multiple IRRs, or there are none, especially with "wild" cash flows like your examples.

However, as noted previously, the XIRR implementation is less reliable and more susceptible to the eccentricity of the IRR methodology.

Also, it is more difficult to implement the "NPV curve" formulas, due to an arbitrary limitation (design flaw, IMHO) of the XNPV implementation.

So again, it is better to use Excel IRR whenever you have "regular" cash flows -- monthly, in your examples.

Excel IRR returns a periodic rate (monthly, in your case), whereas XIRR returns an annual rate. So for comparison, we must "annualize" the Excel IRR result. That is accomplished with an expression of the form (1+rate)^12 - 1.

Similarly, if we have an annual rate, the monthly rate for use with Excel NPV and Excel IRR is (1+rate)^(1/12) - 1.

-----

Originally Posted by excelnerd95

I can't figure out what to use for the cashflows attached. They give zero no matter what.

Again, they return 2.98E-09, not zero. +/-2.98E-09 is not just a number that is "almost zero". Instead, it is an error return, to be treated like #NUM and #DIV/0.

I cannot help with the fourth cash flow model (row 8). When I open the attachment, it contains a #REF error in BW8. And if I set BW8 to zero to work around the error, the cash flow model is invalid because it comprises only 4 negative values. Presumably, BW8 is the necessary positive cash flow.

For each of the "valid" cash flow models (rows 5, 6 and 7), the attachment shows how to look at the "NPV curve" based on annual discount rates for use with XNPV and XIRR. See the table in columns A, W, AM and AN starting in row 12.

And the "NPV curve" based on monthly discount rates, for use with Excel NPV and IRR, is shown in the table in columns AX, AZ and BB.

There might be an IRR where the NPV changes sign (positive to negative, or negative to positive). I highlighted the changes in yellow.

Aside....

1. We must use SUMPRODUCT instead of XNPV because XNPV does not accept negative discount rates, for no good reason. (A defect, IMHO.)

2. The columns of the "tables" are a little strange because I hide the columns that have all zeros in rows 6 to 8, for aesthetic reasons.

-----

For row 5, the annual IRR is between -80% and -70% (W17:W18). So a reasonable "guess" might be -75%.

That still does not work for XIRR in CH5.

We might drill down with higher precision discount rates between -80% and -70%. But sometimes, even an exact guess does not work with XIRR. IMHO, that is due to the flawed implementation of XIRR.

However, the monthly IRR is between -20% and -10% (AZ23:AZ24). And a "guess" of -15% does work for Excel IRR in CI5. The monthly IRR is -10.51%, and the annualized IRR is -73.61%.

-----

For row 6, a guess of -10% works. Again, that is "dumb luck", since the IRR is -28.19% in CH6.

And by coincidence, no guess is needed for the use of Excel IRR in CI6.

Aside.... Due to different assumptions, there is almost always a difference between an annualized rate based on Excel IRR and the annual rate returned by XIRR. For row 6, XIRR returns -28.19% in CH6, whereas Excel IRR returns -28.24% in CI6.

-----

For row 7, the annual IRR is between -99.99% (!) and -99% (AN13:AN14). So a reasonable "guess" might be -99.95% (!).

That does not work for XIRR in CH7. But that is not surprising, because the cash flow model is eccentric, namely: 3 negative CFs totaling -$127,000 and 1 positive CF of $1 over a period of 29 months.

Nevertheless, the monthly IRR is between -50% and -40% (BB20:BB21). And a "guess" of -45% does work for Excel IRR in CI7. The monthly IRR is -40.76%, and the annualized IRR is -99.81% (!).

-----

The eccentric nature of the cash flows in row 7 begs the question: what is the nature of these cash flow models?

Please explain the negative and positive values.

I suspect that none of the cash flow models is constructed correctly.