如何在Excel中无需脚本计算精确到月份的投资回报率(ROI)

核心思路 #

先定位累计储蓄未达目标的最后一个完整年度，再计算该年度后需要多少个月能突破目标金额，利用年度收益的月度化增长模型实现精确计算。

分步公式实现 #

假设数据列定义：

• A列：年度（如1、2、3...）
• B列：年度收益率（如5%、4.6%、4.2%...逐年递减0.4%）
• D列：累计储蓄金额

1. 定位最后一个未达标年度（N）

=MATCH(1000000, D:D, 1)

• 说明：MATCH函数第三个参数设为1，返回小于等于1000000的最大累计储蓄对应的年度位置，即第N年结束时累计储蓄仍未达标，第N+1年可突破目标。

2. 获取第N年末的累计储蓄（S_N）

=INDEX(D:D, N)

• 说明：用INDEX提取第N年对应的累计储蓄金额。

3. 获取第N+1年的年度收益率（r）

=INDEX(B:B, N+1)

• 说明：提取下一年度的收益率，用于计算月度增长。

4. 计算达标所需的月份数（m）

若年度收益为复利收益率：

=CEILING(12*LN(1000000/INDEX(D:D, N))/LN(1+INDEX(B:B, N+1)), 1)

• 逻辑推导：基于S_N*(1+r)^(m/12) ≥ 1000000的复利增长模型解不等式，用CEILING向上取整确保金额达标。
• 特殊情况处理（若年度末已达标）：

  =IF(INDEX(D:D, N)=1000000, 0, CEILING(12*LN(1000000/INDEX(D:D, N))/LN(1+INDEX(B:B, N+1)), 1))
  

若年度收益为单利收益率：

=CEILING((1000000-INDEX(D:D, N))/(INDEX(D:D, N)*INDEX(B:B, N+1)/12), 1)

5. 合并显示最终结果

=INDEX(A:A, N)&"年"&IF(INDEX(D:D, N)=1000000, "", CEILING(12*LN(1000000/INDEX(D:D, N))/LN(1+INDEX(B:B, N+1)), 1)&"个月")

• 说明：直接输出“X年Y个月”的格式，若已在年度末达标则只显示年份。

关键说明 #

价格上涨因素已包含在现有累计储蓄列（对应年份月度价格固定，年度涨幅已体现在年度收益或累计储蓄的计算逻辑中），无需额外调整公式。

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