Hi, I am calculating XIRR for uneven cash flows. For some entries, XIRR is giving ‘zero’ when it should be some negative value.
Can someone please help me solve this??
Hi, I am calculating XIRR for uneven cash flows. For some entries, XIRR is giving ‘zero’ when it should be some negative value.
Can someone please help me solve this??
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.
Glenn
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.
Last edited by joeu2004; 04-27-2021 at 06:31 AM.
Hi guys,
Apologies for not attaching the date.
I have attached a sample right now. XIRR should be a negative value.
Can someone please help me solve this? My boss is chasing this up ;_;
Thanks
@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.
Last edited by joeu2004; 04-29-2021 at 05:22 AM.
PPS....
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.
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.
Can you please help with these?
[.... deleted by me .... replaced by posting #10 ....]
Last edited by joeu2004; 04-30-2021 at 08:03 PM.
[.... deleted by me .... replaced by posting #10 ....]
Last edited by joeu2004; 04-30-2021 at 08:03 PM.
(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.)
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.
-----
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.
Last edited by joeu2004; 05-04-2021 at 04:26 PM.
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
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.
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.
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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks