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

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

Hi, I am calculating XIRR for uneven cash flows. For some entries, XIRR is giving zero when it should be some negative value.  Register To Reply

2. ## Re: 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.  Register To Reply

3. ## Re: XIRR function returning zero, when it should be a negative value

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.  Register To Reply

4. ## Re: XIRR function returning zero, when it should be a negative value

Hi guys,
Apologies for not attaching the date.
I have attached a sample right now. XIRR should be a negative value.
Thanks  Register To Reply

5. ## Re: XIRR function returning zero, when it should be a negative value

@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.  Register To Reply

6. ## Re: XIRR function returning zero, when it should be a negative value

PPS.... Originally Posted by joeu2004 Starting with a cash flow -1 in Jul 2014 might be a misguided work-around that results in a misleading IRR. [....] LMK if you want help with setting up XIRR to start with the first non-zero cash flow.
And since your cash flows occur on a regular basis (monthly), it might be better to use Excel IRR instead of XIRR. Excel IRR is more reliable; and it tolerates models with initial zero cash flows.

(Also, XIRR might be "too precise" because it calculates the exact days between cash flows, not strictly monthly.)

For example, in CH5 write:

=(1+IRR(A5:CF5))^12 - 1

That assumes that A5:A7 are zero, not -1.  Register To Reply

7. ## Re: XIRR function returning zero, when it should be a negative value

Thanks so much!
How did you figure out -10%?
Also, I can't figure out what to use for the cashflows attached. They give zero no matter what.  Register To Reply

8. ## Re: XIRR function returning zero, when it should be a negative value

[.... deleted by me .... replaced by posting #10 ....]  Register To Reply

9. ## Re: XIRR function returning zero, when it should be a negative value

[.... deleted by me .... replaced by posting #10 ....]  Register To Reply

10. ## Re: XIRR function returning zero, when it should be a negative value

(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.  Register To Reply

11. ## Re: XIRR function returning zero, when it should be a negative value

Hi joeu2004,

I can't thank you enough for explaining this in so much deeail!!

It totally makes sense.

With regards to row 8, I have added the missing cashflow. According to the calculation, rate should be -5% - but it still gives out zero. Can you please have a look at the attached sheet and advise what I am missing?

Kind regards  Register To Reply

12. ## Re: XIRR function returning zero, when it should be a negative value Originally Posted by excelnerd95 With regards to row 8, I have added the missing cashflow. According to the calculation, rate should be -5%
Good job on the formulas in AU14:AU35 and BC14:BC35.

FYI, the SUMPRODUCT formulas in column AU14:AU35 can be normally-entered; that is, just press Enter, not ctrl+shift+Enter. Originally Posted by excelnerd95 According to the calculation, rate should be -5%
No. The "NPV curve" tells us that the IRR should be between 0% and -10%.

We use the midpoint (-5%) as the "guess". But the IRR can be anywhere within that range (if there is one there).

-----

BTW, you should use a guess of -5% in the XIRR formula in CH8, not -99%. You got lucky.

But actually, you could have tried the "lucky" guess of -10% first. No need for the "NPV curve" formulas in AU14:AU35.

And you could have tried the IRR formula in CI8 with no guess at all. No need for the "NPV curve" formulas in BC14:BC35.

Nevertheless, the "exercise" was worthwhile for education purposes. Originally Posted by excelnerd95 but it still gives out zero. [...] what I am missing?
Format CH8 and CI8 with at least 2 percentage decimal places; 4 is better.

You will see that XIRR returns about -0.4084% (-0.0040835943708089) in CH8.

And the annualized Excel IRR without a guess returns about -0.4088% (-0.0040876985261491) in CI8.

(Excel IRR returns an infinitesimally different value when we use a guess of -5%.)

----

Anyway, it looks like you got the hang of it. Good work!  Register To Reply