Excel XIRR: first value is small or zero returns odd value

Question

I'm trying to calculate the XIRR of a large (>200) number of investments, each of which has a set of values associated with dates. For some of the investments, the first date in the table is the date of the investment, so the row contains a negative value in the first column. For others, the investment comes later, so the first column is zero.

I have linked to a screenshot but in case that doesn't work:

Dates          1 Mar 2022      2 Mar 2022      1 Mar 2024      XIRR

Investment A   -100,000        0               120,000         9.53%

Investment B   0               -100,000        120,000         0.00%

The XIRR for Investment A comes out right - a 20% increase over two years is 9.53% pa.

The XIRR for Investment B is calculated at zero - which is obviously wrong. It should be almost exactly the same (9.53%), because the initial investment is only made a day later. But XIRR is confused by the fact that the first number is zero.

I can get around this by making the first number a small negative number, for example -0.01. XIRR then calculates correctly for investment B. But that doesn't work in my real-life example, where there are about 200 columns with different dates, most of them blank for each investment: in that case, having a small negative number in the first column gives the same result as having zero - XIRR miscalculates.

I hope I've explained this clearly. Any suggestions would be very welcome!

Thank you -- David

Answer 1

That first value representing the date of the investment needs to be negative. So one way is, assuming no version constraints, try:

E2: =LET(
    dts, $B$1:$D$1,
    vals, B2:D2,
    first_Col, XMATCH(TRUE, vals < 0),
    XIRR(DROP(vals, , first_Col - 1), DROP(dts, , first_Col - 1))
)

and fill down

Or as a formula entered into a single cell that will spill down:

=LET(
    dts, $B$1:$D$1,
    vals, B2:D3,
    BYROW(
        vals,
        LAMBDA(arr,
            LET(
                first_Col, XMATCH(TRUE, arr < 0) - 1,
                XIRR(DROP(arr, , first_Col), DROP(dts, , first_Col))
            )
        )
    )
)