Excel 中的贷款摊销表

摊销贷款只是一个术语，指的是有固定还款期的贷款。

本质上，所有贷款都会以某种方式摊销。例如，一笔为期 12 个月的贷款，总摊销期为 12 个月，将有 12 次定期还款。每次还款的一部分用于利息，一部分用于本金。您可以创建一个详细说明所有贷款还款情况的贷款摊销表。

摊销表是显示每次还款后剩余余额的表格，将每次还款分解为利息和本金，并详细说明贷款/抵押贷款随时间的定期还款情况。

如何在 Excel 中制作贷款摊销表

使用以下 Excel 函数，我们可以创建抵押贷款或贷款摊销表

• PMT 函数确定定期应付的总金额。此金额在整个贷款期间保持不变。
• PPMT 函数检索每次还款中用于偿还贷款本金或总借款金额的部分。随着每次还款的增加，此金额也会增加。
• IPMT 函数确定每笔交易支付的利息金额。随着每次还款的减少，此金额会变小。

现在让我们逐步进行每个阶段。

1. 建立摊销表

首先，指定将包含已知贷款组成部分的输入单元格

• D4：年利率/年度利率
• D5：贷款年限/长期
• D6：每年还款次数/年还款次数
• D7：贷款金额

下一步是创建一个摊销表，在 B9:F9 中包含“期数”、“还款”、“利息”、“本金”和“余额”等标签。在“期数”列中输入一系列数字（本例中为 1-12），表示还款总次数。

现在我们已经建立了所有必要的要素，让我们继续最有趣的部分：贷款摊销计算。

2. 使用 PMT 公式确定总还款金额。

使用 PMT(rate, nper, pv, [fv], [type]) 方法确定还款金额。

为了正确处理不同的还款频率（每周、每月、每季度等），最好为 rate 和 nper 参数使用一致的值。

• Rate：通过将年利率除以总还款期数来计算 ($D$4/$D$6)。
• nper 是年数乘以还款期数 ($D$5*$D$6)。
• 在 pv 中输入贷款金额 ($D$7)。
• 由于最后一次还款后的金额应为零，并且还款是在每个期末进行的，因此 fv 和 type 参数可以省略，因为默认值对我们来说效果很好。

将上述参数组合起来得到以下公式：

我们使用绝对单元格引用，因为此公式应自动复制到下方的单元格。

当您将 PMT 公式输入到 C10 并沿列拖动时，每个期数的还款金额将保持不变。

3. 使用 IPMT 公式计算利息

可以使用 IPMT(rate, per, nper, pv, [fv], [type]) 函数找到每个定期还款的利息部分：

PMT 公式中的所有参数都相同，除了付款期特定的 per 参数。此输入通过相对单元格引用 (B10) 提供，因为它有望根据复制公式的行的相对位置而变化。

在将此公式向下复制到 D10 后，您可以使用它来满足任意数量的单元格需求。

4. 使用 PPMT 公式查找本金

使用以下 PPMT 公式确定每个定期还款的本金部分：

参数和语法与前面提到的 IPMT 公式完全相同。

此公式从 E10 开始，向上到 E 列。要计算贷款定期还款的本金金额，请使用 PPMT 公式。

这是一个提示：将“本金”和“利息”列中的金额相加，以检查您的计算是否正确。总金额应与同一行的“还款”列中的金额相匹配。

5. 获取剩余余额

两个单独的公式将确定每个期数的剩余余额。

将贷款金额 (D7) 与第一个期数的本金 (E10) 相加，以确定第一次还款后的 F10 余额。

由于本金是负数，而贷款金额是正数，因此本金实际上是从贷款金额中扣除的。

将本期本金加到上一期的余额中，以计算下一期及之后所有期数的余额。

此公式沿列复制，直到 F11。由于使用了相对单元格引用，因此该公式会为每一行进行适当的调整。

就是这样！我们已经完成了我们的月度贷款摊销计划！

提示：将还款金额输入为正数。

Excel 函数将还款、利息和本金显示为负数，因为贷款是从您的银行账户中支付的。图中显示了一些值默认用红色突出显示并用括号括起来。

如果您希望所有返回值均为正数，请在 PMT、IPMT 和 PPMT 函数前加上负号。

对于余额公式，请使用减法而不是加法，如下面的屏幕截图所示。

可变期数摊销表

在上例中，我们为预定的还款期数创建了一个贷款摊销表。对于特定的贷款或抵押贷款，此简单的单次修复非常有效。

要设计一个可重用的、具有可变期数的摊销表，需要一种更彻底的方法，如下所述。

1. 输入最大期数

在“期数”列中输入您将接受的任何贷款的最大还款次数。要更快地输入数字序列，请使用 Excel 的自动填充工具。

2. 在摊销计算中使用 IF 语句。

您需要找到一种方法来限制计算，使其只包含特定贷款应付的实际还款次数，因为您现在有很多膨胀的期数。为此，请将每个公式包含在 IF 语句中。IF 语句的逻辑测试确定该行的期数是否等于或小于总还款次数。如果逻辑测试结果为 FALSE，则返回空字符串；如果为 TRUE，则计算相应的函数。

假设第 1 期在第 8 行，则应在相应的单元格中输入以下公式。然后应将这些公式复制到整个表中。

还款 (C10)

利息 (D10)

本金 (E10)

余额

第 1 期的公式 (F10) 与前面示例中的相同。

在第 2 期 (F12) 以及之后的期数中，公式如下所示：

因此，您将获得一个精确计算的摊销计划，以及许多包含还款期数的空白行，这些行显示在贷款偿还之后。

3. 隐藏不必要的期数

如果您可以容忍在最后一次还款后看到不必要的期数显示，请考虑完成工作并继续下一步。如果您追求完美，创建条件格式规则会将最后一次还款后的任何行中的字体颜色更改为白色，这将有助于隐藏任何不必要的期数。

为此，在选择摊销表中的所有数据行后，选择“开始”选项卡 >“条件格式”>“新建规则...”>。找出要使用公式安排的单元格。

为了验证 B 列中的期数是否大于总还款次数，请在相关框中输入以下公式：

重要提示：请确保使用绝对单元格引用将“贷款期限”和“年还款次数”对应的单元格（$D$5*$D$6）相乘，这样条件格式公式才能正常工作。您将组合单元格引用（$B$10），这是一个绝对列和相对行的组合，以将乘积与第 1 期单元格进行比较。

接下来，通过单击“格式...”选项来选择白色字体颜色。完成！将最后还款期数之后的任何行的字体颜色更改为白色。

4. 创建贷款摘要

在摊销表顶部添加一些额外的公式，以便您可以快速查看贷款的摘要信息。

总还款金额 (G4)

总利息 (G5)

如果您的还款是正数，请删除上述公式中的负号。

这样，我们就完成了贷款摊销计划！

如何使用 Excel 创建带额外还款的贷款摊销表

希望前面示例中涵盖的摊销表易于制作和遵循。但是，它们不提供许多借款人会喜欢的有用功能：提前还款以加快贷款偿还的能力。本例将介绍如何创建带额外还款的贷款摊销表。

1. 声明输入单元格

照常开始配置输入单元格。为了使我们的公式更易于阅读，我们将这些单元格命名如下：

• 年利率为 D4。
• 贷款期限（以年为单位）为 D5。
• 每年还款次数，即 D6，
• 贷款金额 - D7
• 额外还款金额 - D8

2. 确定还款计划

计划还款金额，即如果不进行额外还款的贷款金额，是除输入单元格外，我们进行额外计算所需的一个预设单元格。我们使用以下公式来确定此金额：

(或)

请注意，为了获得正数结果，我们需要在 PMT 函数前加上负号。我们将 PMT 公式封装在 IFERROR 函数中，以防止在某些输入单元格为空时出现问题。

将此公式输入到一个单元格（我们使用 H4）并命名为 计划还款。确定按时应付的金额。

3. 建立摊销表

使用下图中的标题，创建贷款摊销表。在“期数”列中输入一系列整数，从零开始（因为您可以根据需要隐藏第 0 期行）。

如果要构建可重用的摊销表，请输入可以输入的最多还款期数。

在第 0 期中，选择对应于初始贷款金额的余额值。此行的其余单元格将全部留空。

H12 的公式：

4. 创建包含额外还款的摊销表计算。

这是我们工作中的一个关键方面。由于 Excel 的内置函数不能用于额外还款，我们必须执行所有数学计算。

注意：在本例中，第 12 行对应第 0 期，第 13 行对应第 1 期。如果您的摊销表的开头在不同的行，请务必更改单元格引用。

在第 13 行（第 1 期）输入以下公式后，对每一期重复此过程。

计划还款 (C13)

如果计划还款金额（指定为单元格 H4）小于或等于剩余余额（H12），则使用计划还款金额。如果不是，则包括上个月的利息和剩余金额。

我们将 IFERROR 函数封装此公式和所有其他公式，作为额外的安全措施。

如果某些输入单元格为空/包含无效数据，这将避免许多错误。

额外还款 (D13)

对 IF 公式应用以下逻辑：

如果额外还款金额小于本期本金（H12-F13）与剩余余额之差，则应退还额外还款金额。如果不是，则应使用差额。

作为一项有用的提示，您可以直接在“额外还款”部分输入任何变量金额。

总还款 (E13)

将当前期数的额外还款（D13）与计划还款（C13）相加，即可完成！

本金 (F13)

如果本期计划还款大于零，则返回计划还款金额减去利息（C13-G13）或剩余余额（H12）中的较小值；否则返回零。

应注意的是，本金仅代表定期还款（而非额外还款）中用于偿还贷款本金的部分。

利息 (G13)

将年利率（单元格 D4）除以每年还款总次数（单元格 D4），然后乘以上一期期末应付金额，前提是该期的计划还款不为零。如果为零，则返回 0。

余额 (H13)

如果上一期余额（H12）大于零，则从上一期余额（H12）中减去本期本金（F13）和额外还款（D13）；否则返回 0。

注意：其中一些公式可能会产生不正确的结果，因为它们会相互引用（非循环引用！）。因此，请等到输入摊销表中的最后一个公式后再开始故障排除。

如果一切操作正确，您的贷款摊销表此时应如下所示：

5. 隐藏额外期数

使用此提示设置条件格式规则，以隐藏未使用期数中的值。区别在于，这次我们通过使用白色字体颜色将“余额”（H 列）和“总还款”（E 列）的行填充为零或空值。

就这样，值为零的行将不再可见。为了隐藏额外期数中的零值，请实施条件格式规则。

6. 编写贷款摘要

为了最终的完善，您可以使用以下公式生成关于贷款的最重要细节：

计划还款次数

年数乘以年还款额为：

实际还款次数

从第 1 期开始，计算“总还款”列中大于零的单元格数量。

额外/总额外还款金额

从第 1 期开始，将“额外还款”列中的单元格相加。

总利息

从第 1 期开始，将“利息”列中的单元格相加。

隐藏包含第 0 期的行后，包含额外还款的贷款摊销计划将完成！最终结果如下图所示。