Excel XIRR：第一个值较小或零返回奇数值

Question

我正在尝试计算大量（>200）投资的 XIRR，每个投资都有一组与日期相关的值。对于某些投资，表中的第一个日期是投资日期，因此该行的第一列包含负值。对于其他投资，投资发生较晚，因此第一列为零。

我已链接到一个屏幕截图但万一它不起作用：

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%

投资 A 的 XIRR 结果正确 - 两年内增加 20%，即每年 9.53%。

投资 B 的 XIRR 计算为零 - 这显然是错误的。它应该几乎完全相同（9.53%），因为初始投资仅在一天后进行。但 XIRR 因第一个数字为零而感到困惑。

我可以通过将第一个数字设为较小的负数（例如 -0.01）来解决这个问题。然后 XIRR 可以正确计算投资 B。但这在我的实际示例中不起作用，其中大约有 200 列具有不同的日期，其中大多数对于每个投资都是空白的：在这种情况下，第一列中的小负数与零的结果相同 - XIRR 计算错误。

希望我已经解释清楚了。欢迎提出任何建议！

谢谢--大卫

Answer 1

表示投资日期的第一个值需要为负数。因此，一种方法是，假设没有版本限制，尝试：

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))
)

并向下填充

或者作为输入到单个单元格中的公式，其作用是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))
            )
        )
    )
)