I agree that in this example, XIRR should have returned #NUM rather than 0.
Regardless, the fix is still the same -- supply a better guess.
However, I still think the example is academic, and not real world. It's
well known that providing silly numbers to XIRR can cause silly results. If
you can design a better convergence algorithm than Newton-Raphson, more
power to you. But in the real world, and I've done literally millions of
return calculations using real world data, Newton-Raphson works very
effectively.
Regards,
Fred.
When you have results that are so far out of touch
with reality, you need to supply a reasonable guess.
Perhaps. But how is the hapless user supposed to know that the
results are "so far out of touch with reality"? Rhetorical question.
The answer: plug the XIRR result into the rate for XNPV.
But isn't that what XIRR should be doing, effectively? After all,
XIRR has an error return to indicate this very condition. From the
Help page: "If XIRR can't find a result that works after 100 tries,
the #NUM! error value is returned."
With Marc's example, XIRR returns about 2.9802E-09. With that rate,
XNPV returns -28000. I think that is sufficiently far from zero that
XIRR should recognize that the result does not "work" ;-).
I don't displute that the situation arises due to discontinuities.
But this is not an anomaly due to multiple IRRs.
IMHO, this is a simple defect in the XIRR implementation: failing to
recognize a non-solution. In Marc's last example, XIRR should have
returned #NUM! (or #DIV/0!).
