XIRR formula not working when adding extra cash flows

First, "add a cash flow" where? I assume you put it before the 19/01/2017 line.

(It would be wrong to put it anywhere else, since Excel XIRR uses the first date in the list as "day zero". It must be the earliest date.)

Second, in that case, XIRR returns about 2.98E-09, not really 0.00%. In my experience, +/-2.98E-09 is another error state, like #NUM and #DIV/0, that indicates that XIRR cannot find a valid IRR (a discount rate that causes NPV to be nearly zero) within the internal limits of its algorithm. See the XIRR help page for details.

Based on that assumption, providing a "guess" parameter sometimes helps. After looking at the NPV curve for varying discount rates from -99% to 100%, it seems that -2.5% might be a good guess.

And indeed, =XIRR(B1:B24,A1:A24,-2.5%) in C3 provides a reasonable result, namely -2.86630576859789%. And note that the NPV with that discount rate is nearly zero, namely 3.72529029846191E-09.

Caveat: We cannot use =XNPV(C3,B1:B24,A1:A24) to calculate the NPV due to a defect in Excel. The following calculates the NPV:
=SUMPRODUCT($B$1:$B$24/(1+C3)^(($A$1:$A$24-$A$1)/365)) .

As for why a "guess" is required one time, but not another, even though the calculated IRRs are similar: who knows why! Mathematically, the algorithm for calculating the IRR is unstable and unpredictable, especially when there are many sign changes and such a large difference in the magnitude of the cash flow values. The Excel XIRR implementation is even more unstable. I believe it is because (I assume) the internal algorithm approximates the derivative instead of using the exact derivative, which is computable.

Are you required to use Excel, or are you allowed to use a different spreadsheet? I have often found, as joeu2004 says, that Excel's internal XIRR algorithm seems less stable than the algorithms used in other spreadsheets. For example, I often find that simply opening a spreadsheet like this in LibreOffice calc or in Google sheets allows their seemingly more stable algorithm to calculate correctly without further changes. If that is an option...