First, if you format the second result as Scientific, you will see that what
appears to be 0.0% is
really about 2.98E-09.
In my experience, +/-2.98E-09 is another error state. Ostensibly, it should be treated like
#NUM , namely: we should provide a "guess" discount rate.
Second, the
mathematical IRR is a fragile "calculation" -- really, an iterative algorithm. It does not respond well to contrived cash flow models like yours.
The Excel XIRR implementation is even more fragile (flawed, IMHO) [1]; so is the Excel IRR implementation, albeit to a lesser extent, in my experience. My own XIRR implementation works well with your second cash flow model.
Since you have monthly cash flows, we can use a formula of the form =(1+IRR(A3:H3))^12-1 to estimate the annual compound IRR (what XIRR returns).
That returns 9.35545125625277% for the first example; 20.0545228418081% for the second example. And for each cash flow model, using those estimated annual IRRs with XNPV returns "close" to zero: less than 0.01 when rounded.
So you can see that the sign change of the first cash flow does indeed result in a significant difference in the
mathematical IRR.
For the first example, =XIRR(A3:H3,A2:H2) returns 9.36799734830856%; and using that with XNPV returns nearly zero (about -2.27E-08).
But for the second example, =XIRR(A6:H6,A5:H5,20%) still returns 2.98E-09 or
#NUM , even with a reasonable "guess".
I have not been able to find a "guess" that works. But again, using my own implementation, =myxirr(A6:H6,A5:H5) returns 20.0784186338246%; and XNPV returns nearly zero (6.66E-16). So the (X)IRR is indeed computable.
In situations where Excel XIRR cannot be made to work, and Excel IRR cannot be used or it fails to provide a suitable estimate, you might be able to use Goal Seek or Solver, varying the discount rate until Excel XNPV returns nearly zero.
However, Excel XNPV does not allow negative discount rates; a design flaw. So I would use a formula like the following to calcuate the NPV if the discount rate might be negative (e.g. using Goal Seek or Solver):
=SUMPRODUCT(A6:H6/(1+I6)^((A5:H5-A5)/365))
I hope that helps. I'm sorry there is no simple answer to your inquiry.
-----
[1] TMI.... I suspect that Excel XIRR (and Excel IRR) uses an
approximated derivative, which is sensitive to a "step size". The arbitrary "step size" might cause the implementation to overshoot a solution, thereby finding an alternate IRR or none at all within the internal iteration limits. In contrast, my XIRR implementation uses the
exact derivative, which is computable. Nevertheless, even my implementation might fail to find a solution. Moreover, there might be multiple
mathematical IRRs, or none at all. So even a well-behaved implementation might fail to find a "good" solution.
