If you format C3 as Scientific, you will see that XIRR returns about 2.98E-09, not really zero.
Originally Posted by kylc3
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.