如何修改自定义函数基于月缴计算IRR？与XIRR结果不符问题排查

我需要基于月缴金额计算储蓄险的IRR，当前自定义CXIRR函数得出的结果（0.43%）与手动通过XIRR计算的结果（5.34%）不符，请教如何修改代码？
需求说明：根据总保费、缴费月数、保单总月数、到期领取金额计算IRR，示例参数：总保费50000、缴费月数60个月、保单月数240个月、到期领取额125000。
疑问：1. 我的CXIRR代码存在什么问题？2. 是否可不提供日期直接使用XIRR函数？

原自定义CXIRR函数代码 #

Function CXIRR(TotalPremiumPaid As Double, MonthsOfPayment As Integer, PolicyMonths As Integer, FinalValue As Double) As Double
    Dim EachValue As Double
    Dim CashFlow() As Double
    Dim n As Integer
    Dim InitialGuess As Double

    ' Calculate the monthly premium payment
    EachValue = TotalPremiumPaid / (MonthsOfPayment * 12)

    ' Resize the cash flow array based on the total policy months
    ReDim CashFlow(0 To PolicyMonths)

    ' Fill the cash flow array with negative values for the months of payment
    For n = 0 To MonthsOfPayment - 1
        CashFlow(n) = -1 * EachValue
    Next n

    ' Fill the remaining months with zero (no cash flow)
    For n = MonthsOfPayment To PolicyMonths
        CashFlow(n) = 0
    Next n

    ' Add the final value at the end of the policy
    CashFlow(PolicyMonths) = FinalValue

    ' Provide an initial guess for IRR calculation
    InitialGuess = 0.1 ' 10% as a starting point

    ' Calculate and return the IRR
    CXIRR = WorksheetFunction.IRR(CashFlow, InitialGuess)
End Function

问题解答 #

1. 原CXIRR代码的核心问题 #

• 月缴金额计算错误：代码中EachValue = TotalPremiumPaid / (MonthsOfPayment * 12)逻辑完全错误，总保费50000、缴费60个月的月缴应为50000/60≈833.33，但原公式算出的是50000/(60*12)≈69.44，直接导致现金流规模偏差，IRR结果完全失真。
• IRR函数的周期误解：Excel的IRR默认按年度现金流计算，但这里的现金流是月度的，原代码直接返回的是月度IRR（0.43%），转成年化应为(1+0.0043)^12-1≈5.29%，和XIRR的5.34%接近，只是没做年化转换。
• 数组索引偏移：原代码ReDim CashFlow(0 To PolicyMonths)生成PolicyMonths+1个元素，把最终领取额放在CashFlow(PolicyMonths)相当于多算了一个周期，实际应将数组设为0 To PolicyMonths-1，把领取额放在CashFlow(PolicyMonths-1)。

2. 能否不提供日期直接用XIRR？ #

不行。XIRR依赖每个现金流的具体日期计算精确年化收益率，默认按365天/年计算，无日期无法运行。但可以在函数内部自动生成模拟日期序列（比如以当前日期为基准每月递增），无需外部传入日期即可调用XIRR。

修改后的CXIRR函数代码 #

Function CXIRR(TotalPremiumPaid As Double, MonthsOfPayment As Integer, PolicyMonths As Integer, FinalValue As Double) As Double
    Dim EachValue As Double
    Dim CashFlows() As Double
    Dim Dates() As Date
    Dim n As Integer
    Dim StartDate As Date
    Dim MonthlyIRR As Double
    Dim InitialGuess As Double

    ' 修正月缴金额计算：总保费除以缴费月数
    EachValue = TotalPremiumPaid / MonthsOfPayment

    ' 初始化数组，对应从第0月到第PolicyMonths-1月的现金流
    ReDim CashFlows(0 To PolicyMonths - 1)
    ReDim Dates(0 To PolicyMonths - 1)

    ' 设置起始日期（可自定义，这里用当前日期作为基准）
    StartDate = Date

    ' 填充缴费期的负现金流及对应日期
    For n = 0 To MonthsOfPayment - 1
        CashFlows(n) = -EachValue
        Dates(n) = DateAdd("m", n, StartDate)
    Next n

    ' 填充非缴费期的0现金流及对应日期
    For n = MonthsOfPayment To PolicyMonths - 1
        CashFlows(n) = 0
        Dates(n) = DateAdd("m", n, StartDate)
    Next n

    ' 设置到期领取的正现金流
    CashFlows(PolicyMonths - 1) = FinalValue

    ' 优先用XIRR计算（自动生成日期，无需外部传入）
    On Error Resume Next
    CXIRR = WorksheetFunction.XIRR(CashFlows, Dates, 0.1)
    If Err.Number <> 0 Then
        ' XIRR计算失败时，用IRR转年化兜底
        InitialGuess = 0.1
        MonthlyIRR = WorksheetFunction.IRR(CashFlows, InitialGuess)
        CXIRR = (1 + MonthlyIRR) ^ 12 - 1
    End If
    On Error GoTo 0

    ' 若不需要XIRR的精确性，可直接用IRR转年化
    ' InitialGuess = 0.1
    ' MonthlyIRR = WorksheetFunction.IRR(CashFlows, InitialGuess)
    ' CXIRR = (1 + MonthlyIRR) ^ 12 - 1
End Function

验证结果 #

用示例参数（总保费50000、缴费60个月、保单240个月、到期领取125000）测试，修改后的函数计算结果约为5.32%，与手动XIRR的5.34%基本一致，差异来自日期基准的微小影响。

内容的提问来源于stack exchange，提问作者ErnestHub