IRR / XIRR hurdles for Waterfall splits ... monthly vs yearly cashflows

Originally Posted by kurifodo

I don't understand, however, why the XIRR for the two periods representing 2 years is so wildly different than that of the 2 years expressed per month (given that XIRR makes use of dates). Ideas? Is there anything wrong with the template/reasoning? Which IRR is right?

I think you are referring to XIRR in L6 v. XIRR in J16, to wit:
L6: =XIRR(L4:AI4,L1:AI1)
J16: =XIRR(D16:E16,D14:E14)

The key difference is: the two XIRRs do not cover the same period of time.

D14 is 6/1/2017, whereas L1 is 6/1/2016. If we change D14 to =L1, XIRR in J16 becomes about 88%, close to 86% in L6.

Another key difference is timing: XIRR in J16 covers just 2 cash flows (initial and final); XIRR in L6 covers the interstitial monthly cash flows. Certainly the monthly timing affects the IRR. It is the better IRR, since it relies on finer details.

However, another difference is: the net cash flows are not the same. The initial cash flows are the same. But L16 is =SUM(X4:AI4), whereas XIRR in L6 includes nonzero cash flows in R4:W4. If we change L16 to =SUM(M4:AI4) (as well as correct the date in D14), the XIRR in J16 becomes about 94%.

Again, I believe the difference between 94% in J16 and 86% in L6 is due to the finer detail of the monthly cash flows used in L6, as well as the significant period of time (24 months).