flexible XIRR with different starting dates

Hello all, I am looking for a flexible IRR that looks up dates and cash flows when each cash flow (one set of cash flows per line with dates all located on the top line.
If I use simple IRR I cannot copy it down as then there are zero cash flows before the first negative cash flow so I need a flexible IRR that can basically either look up a start date and then XIRR all cash flows or a flexible IRR that will ignore the zeros and start at the first negative cash flow. My excel looks like this below. Any responses appreciated!

excel.JPG

In this forum, you can attach an Excel file, which is always better than an image. Please see my Excel file attachment. I think I faithfully reproduced your data as well as my formulas.

Also, in this case, it would be prudent to show the calculations that you want manually. I believe you want the XIRR from the first non-zero cash flow to the last non-zero cash flow for each row. See my formulas in F8:F10, which I call "XIRR min range". They are:

Model 1: =XIRR(H2:T2, H1:T1)
Model 2: =XIRR(J3:U3, J1:U1)
Model 3: =XIRR(J4:Q4, J1:Q1)

Please confirm my assumptions. Based on those assumptions....

-----

Since your cash flows are monthly, I believe it would be easier and possibly more correct to use the Excel IRR function.

Unlike Excel XIRR, Excel IRR has no problem with initial zero cash flows, as well as zero cash flows at the end.

For consistency with Excel XIRR, the annualized monthly IRRs are calculated D8:D10, which I call "IRR D:U range". They are:

Model 1: =(1+IRR(D2:U2))^12 - 1
Model 2: =(1+IRR(D3:U3))^12 - 1
Model 3: =(1+IRR(D4:U4))^12 - 1

You might be concerned that the IRRs are calculated to the wrong "present value" date because we always start with column D. Mathematically, we can explain why it does not matter. But more simply, I calculate the "IRR min range" values in C8:C10, to wit:

Model 1: =(1+IRR(H2:T2))^12 - 1
Model 2: =(1+IRR(J3:U3))^12 - 1
Model 3: =(1+IRR(J4:Q4))^12 - 1

They all compare equal, as demontrated by the TRUE results in E8:E10.

You will note that there is (and always will be) a small difference between the annualized monthly IRR and the XIRR results. This is because Excel XIRR uses the exact numbers of days between cash flows, and it compounds daily; whereas, Excel IRR assumes equal days between cash flows, and the annualized calculation compounds monthly.

-----

If you prefer to use Excel XIRR, I demonstrate the calculation of what I call "flex XIRR" in C2:C4. The formulas are:

Model 1: =XIRR(INDEX(D2:U2,B8):U2, INDEX($D$1:$U$1,B8):$U$1)
Model 2: =XIRR(INDEX(D3:U3,B9):U3, INDEX($D$1:$U$1,B9):$U$1)
Model 3: =XIRR(INDEX(D4:U4,B10):U4, INDEX($D$1:$U$1,B10):$U$1)

For simplicity and efficiency, those formulas depend on calculating the relative column number of the first non-zero cash flow for each model in B8:B10. The formulas are:

Model 1: =MATCH(TRUE, INDEX(D2:U2<>0,0), 0)
Model 2: =MATCH(TRUE, INDEX(D3:U3<>0,0), 0)
Model 3: =MATCH(TRUE, INDEX(D4:U4<>0,0), 0)

The INDEX expression is a "trick" to avoid array-entering the MATCH formula. That is, the MATCH formula is normally-entered: just press Enter as usual.

If you prefer not to rely on such "helper cells", you can replace both instances of B8 in the "flex XIRR" with the corresponding MATCH expression. Do the same with B9 and B10, of course.

For simplicity, the "flex XIRR" formulas extend to column U, including possible zero cash flows and corresponding dates at the end. The comparisons in G8:G10 demonstrate that the result is the same as the "XIRR min range".