月度复利计算溢出公式出错求助：结果偏差随时间扩大

核心问题诊断 #

你的公式存在3个关键错误，导致后期复利计算偏差极大：

• 月度增长因子逻辑冗余且混淆：Year2MonthlyRate定义的_NYears参数完全未使用，且计算的是月度增长因子却未结合复利滚动逻辑，混淆了利率与增长因子的应用场景。
• 复利逻辑错误：当前公式采用单利思维，直接用初始总贡献加累计缴款后乘以月度因子，未实现每月本息和滚动计算的复利逻辑，时间越长偏差越显著。
• 初始余额取值错误：用MAX(_Contributed)作为复利计算的初始值，实际应取缴款阶段最后一个月的期末本息和，而非最大缴款额。

修正后的公式 #

以下公式实现了正确的动态滚动复利计算，支持缴款阶段与后续复利阶段的连续衔接：

=LET(
    // 转换年收益率为月度增长因子
    MonthGrowthFactor, (1 + $B$4) ^ (1/12),
    // 时间线与已缴款数据
    Timeline, $D$4:$D$328,
    Contributed, $E$4:$E$328,
    // 定位最后一笔缴款的行位置
    LastContribRow, XMATCH(TRUE, Contributed>0, 0, -1),
    // 复利计算的初始余额（缴款阶段最后一个月的期末值）
    InitialBalance, INDEX(Contributed, LastContribRow),
    // 生成全时间线的月份索引
    MonthSeq, SEQUENCE(ROWS(Timeline), 1, 1, 1),
    // 分阶段计算：缴款阶段返回已存数据，后续阶段滚动复利
    IF(MonthSeq <= LastContribRow,
        Contributed,
        // 用SCAN实现迭代复利：上月本息和+当月缴款后乘以月度增长因子
        SCAN(InitialBalance, OFFSET(Timeline, LastContribRow, 0, ROWS(Timeline)-LastContribRow),
            LAMBDA(prev, curr, (prev + $B$5) * MonthGrowthFactor)
        )
    )
)

关键修正说明 #

1. 简化增长因子计算：直接生成月度增长因子，去掉冗余的LAMBDA定义，逻辑更清晰。
2. 滚动复利实现：使用SCAN函数完成迭代计算，每个月的余额基于上月本息和加当月缴款后再放大，完全匹配复利计息规则。
3. 初始值修正：通过XMATCH精准定位缴款阶段的终点，取对应期末余额作为复利计算的起点，确保数据衔接无误差。

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