XIRR with ebbs and flows of capital

UPDATE: Thank you so much for the help. The solution was that we all missed the fact that we were trying to calculate on a cumulative cash flow rather than the monthly amount. Essentially user error on our part. I am sorry for the inconvenience, we definitely should have caught that.

I think I may have reached the limits of the XIRR function in Excel. I have attached a sample cash flow that contains the ebbs and flows of the required capital needed. However, despite showing a profit at the end of the flow (December 2027) it is returning a 0% IRR. I know this is wrong, but I have not been able to find a work around. Is there a limit to the length of time an XIRR can run? Any coaching or guidance would be appreciated. Thanks!

I could not find a cell in your sample file with an XIRR() function. I tried my own, I cannot replicate the problem. If I enter =XIRR(C6:EP6,C2:EP2) into a cell, I get about 6.2%. I know that Excel's implementation of XIRR() is a little unstable, and other spreadsheets have a more stable algorithm, but I get the same result from LibreOffice Calc as well. Perhaps calculation is set to manual? Or you are making some other error in entering your XIRR() function? Or perhaps I misunderstood?

Originally Posted by NSAdventurer

I think I may have reached the limits of the XIRR function in Excel. [....] it is returning a 0% IRR. [....] Is there a limit to the length of time an XIRR can run?

AFAIK, there is no limit on time span or on the number of cashflows, other than computational limitations of the internal binary representation, which might arise.

Also, what appears to be 0% is probably about +/-2.98E-09. Format as Scientific. In my experience, that is another error state like #NUM and #DIV/0, which Excel XIRR returns typically to indicate the need for a "guess" parameter.

However, as "MrShorty" notes, =XIRR(C6:EP6,C2:EP2) seems to work in you example Excel file. It returns 6.19595676660538%. We can confirm the correctness by noting that
=XNPV(C8,C6:EP6,C2:EP2) is 10.58, which is relatively close to zero for the magnitude of the cash flows. In fact, my own XIRR implementation returns 6.19595689336901%, a difference of about 0.0000001268% (very small). With my XIRR, XNPV returns about -5.51E-07, which is truly close to zero.

So you need to provide an example where XIRR does not return a reasonable result, as demonstrated by using XNPV.

Caveat: Excel XNPV does not allow a negative discount rate; a defect. In that case, use the following formula (works for positive discount rates, as well):

=SUMPRODUCT(C6:EP6/(1+C8)^((C2:EP2-C2)/365))