Originally Posted by
kylc3
XIRR is returning a 0.00% instead of a negative number.
If you format C3 as Scientific, you will see that XIRR returns about 2.98E-09, not really zero.
Although you might think that is close enough to zero, in my experience, that constant represents an error state in XIRR.
In this case, I believe it is an alternative for the #NUM error.
Thus, XIRR might require a "guess" in order to determine the IRR.
And in fact, =XIRR(C6:C126,A6:A126,-6.5%) results in about -6.23%.
I did not pick -6.5% out of thin air. Instead, I determined the inflection point by using the following tables.
FYI, I use SUMPRODUCT instead of XNVP because XNPV does not accept a negative discount rate, for no good reason. It's a defect, at least in Excel 2010 and earlier.
PS.... Based on only the left-hand table, =XIRR(C6:C126,A6:A126,-5%) would have worked just as well. I drilled down one more level (right-hand table) because the NPV curve for varying discount rates seems to be very steep near the inflection point; and in my experience, often the first level on the left is not sufficient for a steep NPV curve.
Bookmarks