Fix IRR & XIRR
- XIRR and IRR should use the exact derivative in its Newton-Raphson algorithm.
I suspect that XIRR and IRR approximates the derivative using the Newton difference quotient dNPV(r) = (NPV(r+h) - NPV(r)) / h. Consequently, XIRR and IRR often fail with #NUM (or #DIV/0) when, in fact, the IRR could be calculated.
The exact derivative is dNPV(r) = SIGMA(d[i]*CF[i]/(1+r)^(d[i]+1), i=0,...,n). For IRR, d[i] = i. For XIRR, d[i] = (d[i] - d)/365.
- XIRR and IRR should test NPV(g) = 0 initially, where "g" is the guess.
I suspect that XIRR and IRR test NPV(r) = 0 at the end of each iteration, where the r is not g.
Consequently, XIRR and IRR fail with #NUM (or #DIV/0) or they return a poor or incorrect IRR even when "g" is, in fact, the correct IRR; that is, when NPV(g) = 0 or nearly so.
- XIRR and IRR should always return #NUM (not even #DIV/0 or 2.98E-9) when NPV(r) <> 0 or nearly so.
Sometimes, XIRR and IRR return bogus IRRs such that NPV(r) is not zero or nearly so. Consequently, we must calculate NPV(r) ourselves before accepting the result of XIRR and IRR.
- XIRR should support range unions of the form (range,range,...), just as IRR does. For example, XIRR((B1:B10,C11),A1:A11).
This would make it easier to calculate stepwise IRRs for subsets of cash flows; for example, the IRR after the first 5, 6, 7, etc periods.
- XIRR always returns 2.98E-9 when the first cash flows are zero, even though XNPV(2.98E-9) is not even close to zero.
This might be avoided by suggestion #3. But it would be prudent to investigate when/why the agorithm degenerates to an IRR of 2.98E-9.
- XIRR should ignore initial cash flows and dates that are blank, just as IRR seems to do.
Instead, XIRR seems to treat initial blank dates as zero (1/0/1900).
- XIRR should ignore interstitial cash flows and dates that are text (notably the null string), just as IRR seems to do.
Instead, XIRR returns a #VALUE error. This suggestion would make it easier to create ranges using array-entered IF() expressions, for example.
- It would be nice if XIRR used the earliest date for d0 in the calculation (d[i]-d0)/365, instead of always using the first date in the range.
Generally, XIRR does not require that the date range be in order. So this suggestion would be consistent with that.
- XIRR and IRR should never return IRR <= -1 (-100%).
They should return #NUM instead. The NPV curve is "discontinuous" (mathematically) at IRR = -1. So an IRR <= -1 is bogus.