Why does XIRR result in #NUM! when I slightly change one of the cash flow entries?

Question

I am trying to back into a 9% IRR and can do so using XNPV, but would like to do so using XIRR.  Please see attached, when I approach 11 and 10% I start to get the #NUM! error.  I do not get this error in Google sheets.

Answer 1

First, since your dates are equal periods apart, you can use Excel IRR instead of XIRR.  Excel IRR does not have the problem with these cash flow models that XIRR does.

Second, I suspect that your models are incorrect, at least in the last value (2031).  You must use cash flows signs consistently:  one sign for inflows (e.g. minus); the other sign for outflows (e.g. plus).

If we interpret your first-period cash flow (2021) as an inflow (initial balance), usually the last cash flow (final balance) is treated as an outflow.  Since the first cash flow is minus, the last cash flow should be plus.

(As written, the negative last cash flow would mean that we must add to the investment(!) at the end.  Is that what you truly mean?)

Finally, the intermediate cash flows should be external __changes__ in balances. That is, additional investments (minus) or withdrawals (plus).

Considering the magnitude of those cash flows, I suspect they are actually the balances for each year.  That would be wrong.

---

Be that as it may, in part, the answer is:  for the first model (XIRR = #NUM), XIRR is struggling with the fact that there are __two__ potential IRRs (changes in sign of the NPV), __and__ the rate of change of the (X)NPV is very steep when the NPV is near zero.

When there are multiple IRRs, often we must provide a "guess" (last parameter) to force XIRR and Excel IRR to find the "right" one.

But in this case, Excel XIRR seems to be unable to "zero in" on the IRR near 9% and 3%, not matter what we "guess".  (At least, I have not yet found a "guess" that works.)

Why can Google Sheets XIRR "zero in" on it, even without a "guess"?  Probably because its internal algorithm is better.

FYI, even the second model has (at least) two IRRs.  But by "dumb luck", XIRR can deal it.

We can see this by calculating the NPV "curve" for varying discount rates.  See columns F:G, I:J and L:M below.

Formulas:

B1: =XIRR(B6:B16, $A$6:$A$16)

B2: =IRR($B$6:$B$16)

B3: =IRR($B$6:$B$16, 3.5%)

C1: =XIRR(C6:C16, $A$6:$A$16)

C2: =IRR($C$6:$C$16)

C3: =IRR($C$6:$C$16, -5%)

G2: =SUMPRODUCT($B$6:$B$16/(1+F2)^(($A$6:$A$16-$A$6)/365))

J2: =SUMPRODUCT($C$6:$C$16/(1+I2)^(($A$6:$A$16-$A$6)/365))

M2: =SUMPRODUCT($C$6:$C$16/(1+L2)^(($A$6:$A$16-$A$6)/365))

Note:  I use SUMPRODUCT because XNPV does allow negative discount rates (column L).  There is no good reason for the limitation; Excel NPV allows negative discount rates, for example. I consider the XNPV limitation to be a "design flaw" (defect).

Answer 2

Dana wrote:

I believe the real solution should be 0.0375.

I cannot think of a good reason to choose one solution over the other in this case.  Obviously, both are correct mathematically.

But Google Sheets XIRR returns 9.00000294979896% by default.  See below.

And that is closer to the value returned by Excel IRR by default [*], which is also close to the default Google Sheets IRR result.

That is how I would choose the "real solution", if I had to.

And absent the Excel IRR and Google Sheets results, my "common sense" choice would be the result around 9% for Model 1, if only because it is closer to the (default) Model 2 result.  I would expect the two IRRs to be "close" because the series of discounted cash flows are not so different, notably the discounted last cash flows.

(That said, we both know that the mathematical IRR is wildly unpredictable, especially when there are multiple IRRs and probably a malformed cash flow model.)

---

[*] I inadvertently displayed Excel results with 14 significant digits instead of 15.  Excel IRR should display 9.01876075720678%

Answer 3

hankw06 wrote:

I am trying to back into a 9% IRR and can do so using XNPV, but would like to do so using XIRR.

(PS.... Not sure what you mean by "back into" and "can do so using XNPV".  Forgive me if the following is "old news".)

FYI, ostensibly, you can calculate the last cash flow that should result in an IRR of 9% with the following formula (refer to the worksheet image in my previous response):

=-XNPV(9%, B6:B15, A6:A15) * (1+9%)^((A16-A6)/365)

which returns about -16,044,832.6618412.

But the flaw in Excel XIRR still prevents us from confirming the result using XIRR.

(And note, again, that we must use a SUMPRODUCT formula if we want the IRR to be negative, in order to work around the design limitation in XIRR.)

The equivalent formula for use with Excel IRR is:

=-NPV(9%, B6:B15) * (1+9%)^ROWS(B6:B16)

which returns about -16049037.6693695.  Then, =IRR(B6:B16) displays 8.99999999999999%.