如何在Google Sheets中实现债务还款计划应还金额动态更新

需求概述 #

• 表格结构：
  • A2:A13：到期日期
  • B2:B13：应还金额（需计算）
  • C2:C13：已付金额
  • G1：总债务
  • G2：还款月数
• 核心规则：
  1. 标准月付为G1/G2
  2. 超额还款可抵扣后续月份应还金额：如600美元分12个月还（每月50），某月多付10则最后一月少付10；若多付125，第11、12月应还0，第10月仅需还25
  3. 应还金额不得为负：已付≥标准月付时显示0，不足则显示差额

现有问题 #

之前使用的公式=IF($G$1-$G$5>=($G$1/$G$2), $G$1/$G$2, $G$1-$G$5)无法处理超额还款超过标准月付的场景，无法正确显示0。

解决方案 #

方法1：数组公式实现（无需脚本） #

在B2单元格输入以下公式，下拉填充至B13：

=MAX(0, MIN($G$1/$G$2, MAX(0, $G$1 - SUM($C$2:C1) - ($G$2 - (ROW()-1))*$G$1/$G$2)))

公式逻辑说明：

• SUM($C$2:C1)：当前行之前的累计已付金额（第2行时C1为空，结果为0）
• $G$1 - SUM($C$2:C1)：当前行开始时的剩余债务
• ($G$2 - (ROW()-1))*$G$1/$G$2：剩余月份按标准月付的总金额
• 先计算剩余债务与剩余标准总付的差额，取非负值后，再与标准月付取最小值，最后确保结果不为负

方法2：自定义递归函数（模拟JavaScript递归逻辑） #

通过Google Apps Script实现递归逻辑：

1. 打开Google Sheets，点击「扩展程序」→「Apps脚本」
2. 粘贴以下代码并保存：

function CALCULATEDUE(totalDebt, monthsPaid, totalMonths, cumulativePaid) {
  // 递归终止条件：剩余月数为0或剩余债务已结清
  if (totalMonths - monthsPaid <= 0 || totalDebt - cumulativePaid <= 0) {
    return 0;
  }
  const standardPayment = totalDebt / totalMonths;
  const remainingDebt = totalDebt - cumulativePaid;
  const remainingMonths = totalMonths - monthsPaid;
  // 计算当前月应还金额：不超过标准月付，且不超过剩余债务
  const adjustedPayment = Math.min(standardPayment, remainingDebt);
  // 确保结果非负
  return Math.max(0, adjustedPayment);
}

3. 返回表格，在B2单元格输入以下公式，下拉填充至B13：

=CALCULATEDUE($G$1, ROW()-2, $G$2, SUM($C$2:C1))

函数逻辑说明：

• 每次调用时传入总债务、已过月份数、总还款月数、累计已付金额
• 递归终止时返回0，否则计算当前月的应还金额，确保不超过标准月付和剩余债务，且不为负

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