在 Excel 中创建贷款分期偿还计划表 – 分步教程

在 Excel 中创建贷款分期偿还计划表是一项非常有价值的技能，可以帮助您直观地查看并有效管理贷款的还款情况。分期偿还计划表是一张详细列出每期还款金额的表格（通常用于抵押贷款或汽车贷款）。它将每笔还款分解为利息和本金两部分，并显示每次还款后的剩余余额。接下来，我们将逐步指导您如何在 Excel 中创建这样的计划表。

什么是分期偿还计划表？

在 Excel 中创建分期偿还计划表

创建适用于可变期数的分期偿还计划表

创建包含额外还款的分期偿还计划表

使用 Excel 模板创建分期偿还计划表（含额外还款）

什么是分期偿还计划表？

分期偿还计划表是一种详细的表格，用于贷款计算，展示随着时间推移还清贷款的过程。分期偿还计划表常用于固定利率贷款，例如房屋抵押贷款、汽车贷款和个人贷款，在这些贷款中，支付金额在整个贷款期限内保持不变，但支付中的利息与本金比例会随时间变化。

要在 Excel 中创建贷款分期偿还计划表，内置函数 PMT、PPMT 和 IPMT 确实至关重要。让我们来了解每个函数的作用：

• PMT 函数：此函数用于根据固定的支付金额和固定利率计算每期贷款的总支付金额。
• IPMT 函数：此函数用于计算特定期间的支付中的利息部分。
• PPMT 函数：此函数用于计算特定期间的支付中的本金部分。

通过在 Excel 中使用这些函数，您可以创建一个详细的分期偿还计划表，显示每次支付的利息和本金组成部分以及每次支付后的剩余贷款余额。

在 Excel 中创建分期偿还计划表

在本节中，我们将介绍两种不同的方法来在 Excel 中创建分期偿还计划表。这些方法适应不同用户的偏好和技能水平，确保任何人，无论其对 Excel 的熟练程度如何，都可以成功构建详细且准确的贷款分期偿还计划表。

公式提供了对基础计算的更深入理解，并能够根据具体需求自定义计划表。这种方法非常适合那些希望亲身体验并清楚了解每次支付如何分解为本金和利息部分的人。现在，让我们一步步详细介绍如何在 Excel 中创建分期偿还计划表：

⭐️ 第一步：设置贷款信息和分期偿还计划表

1. 输入相关的贷款信息，例如年利率、贷款年限、每年还款次数和贷款金额到单元格中，如下图所示：
   
2. 然后，在 Excel 中创建一个分期偿还计划表，并指定标签，如“期次”、“支付金额”、“利息”、“本金”、“剩余余额”，分别填入 A7:E7 单元格中。
3. 在“期次”列中，输入期次编号。在此示例中，总还款期数为 24 个月（2 年），因此，您将在“期次”列中输入数字 1 至 24。见下图：
   
4. 一旦您设置了带有标签和期次编号的表格，就可以根据贷款的具体信息继续输入“支付金额”、“利息”、“本金”和“余额”列的公式和数值。

⭐️ 第二步：使用 PMT 函数计算总支付金额

PMT 函数的语法为：

在单元格 B7 中输入以下公式，然后向下拖动填充柄以将该公式填充到其他单元格中，您将看到所有期次的固定支付金额。见下图：

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ 第三步：使用 IPMT 函数计算利息

在此步骤中，您将使用 Excel 的 IPMT 函数计算每个支付期的利息。

在单元格 C7 中输入以下公式，然后向下拖动填充柄以填充该公式并获取每个期次的利息。

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ 第四步：使用 PPMT 函数计算本金

在计算每个期次的利息后，创建分期偿还计划表的下一步是计算每次支付的本金部分。这可以通过使用 PPMT 函数完成，该函数旨在基于固定支付和固定利率确定特定期次的支付中的本金部分。

IPMT 函数的语法为：

PPMT 公式的语法和参数与前面讨论的 IPMT 公式相同。

在单元格 D7 中输入以下公式，然后向下拖动填充柄以填充每个期次的本金。见下图：

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ 第五步：计算剩余余额

在计算每次支付的利息和本金之后，创建分期偿还计划表的下一步是计算每次支付后的剩余贷款余额。这是计划表的关键部分，因为它展示了贷款余额如何随着时间减少。

1. 在余额列的第一个单元格 E7 中，输入以下公式，这意味着剩余余额将是原始贷款金额减去第一次支付的本金部分：

   =B4-D7

   
2. 对于第二期及所有后续期次，通过从前一期的余额中减去当期的本金支付来计算剩余余额。请在单元格 E8 中应用以下公式：

   =E7-D8

    注意：余额单元格的引用应为相对引用，以便在向下拖动公式时自动更新。
   
3. 然后向下拖动填充柄至整列。可以看到，每个单元格都会根据更新的本金支付自动调整以计算剩余余额。
   

⭐️ 第六步：制作贷款摘要

在设置好详细的分期偿还计划表后，创建贷款摘要可以快速概览贷款的关键方面。此摘要通常包括贷款的总成本和支付的总利息。

● 计算总支付金额：

=SUM(B7:B30)

● 计算总利息：

=SUM(C7:C30)

⭐️ 结果：

现在，一个简单而全面的贷款分期偿还计划表已成功创建。见下图：

使用 Kutools AI 解锁 Excel 魔法

• 智能执行：执行单元格操作、分析数据和创建图表——所有这些都由简单命令驱动。
• 自定义公式：生成量身定制的公式，优化您的工作流程。
• VBA 编码：轻松编写和实现 VBA 代码。
• 公式解释：轻松理解复杂公式。
• 文本翻译：打破电子表格中的语言障碍。

通过人工智能驱动的工具增强您的 Excel 能力。立即下载，体验前所未有的高效！

创建适用于可变期数的分期偿还计划表

在前面的例子中，我们为固定数量的支付创建了贷款还款计划表。这种方法非常适合处理条款不发生变化的特定贷款或抵押贷款。

但是，如果您希望创建一个灵活的分期偿还计划表，可以反复用于具有不同期数的贷款，并允许您根据不同贷款场景的要求修改支付次数，则需要采用一种更为详细的方法。

⭐️ 第一步：设置贷款信息和分期偿还计划表

1. 输入相关的贷款信息，例如年利率、贷款年限、每年还款次数和贷款金额到单元格中，如下图所示：
   
2. 然后，在 Excel 中创建一个分期偿还计划表，并指定标签，如“期次”、“支付金额”、“利息”、“本金”、“剩余余额”，分别填入 A7:E7 单元格中。
3. 在“期次”列中，输入您可能考虑的任何贷款的最高支付次数，例如，填写从 1 到 360 的数字。这可以覆盖标准的 30 年贷款，如果您每月支付一次的话。
   

⭐️ 第二步：使用 IF 函数修改支付、利息和本金公式

在相应的单元格中输入以下公式，然后拖动填充柄以将这些公式扩展到您设定的最大支付期数。

● 支付公式：

通常，您使用 PMT 函数计算支付金额。为了结合 IF 语句，语法公式为：

所以公式是这样的：

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● 利息公式：

语法公式为：

所以公式是这样的：

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● 本金公式：

语法公式为：

所以公式是这样的：

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ 第三步：调整剩余余额公式

对于剩余余额，您通常会从前一个余额中减去本金。使用 IF 语句进行修改：

● 第一个余额单元格：(E7)

=B4-D7

● 第二个余额单元格：(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ 第四步：制作贷款摘要

在使用修改后的公式设置好分期偿还计划表后，下一步是创建贷款摘要。

● 计算总支付金额：

=SUM(B7:B366)

● 计算总利息：

=SUM(C7:C366)

⭐️ 结果：

现在，您已经在 Excel 中拥有了一个全面且动态的分期偿还计划表，并附有详细的贷款摘要。每当您调整支付期数时，整个分期偿还计划表将自动更新以反映这些更改。请参见下面的演示：

创建包含额外还款的分期偿还计划表

通过超出预定还款额的额外还款，可以更快地还清贷款。在 Excel 中创建包含额外还款的分期偿还计划表，展示了这些额外还款如何加速贷款的偿还并减少支付的总利息。以下是设置方法：

⭐️ 第一步：设置贷款信息和分期偿还计划表

1. 输入相关的贷款信息，例如年利率、贷款年限、每年还款次数、贷款金额和额外还款到单元格中，如下图所示：
   
2. 然后，计算预定还款额。
   除了输入单元格外，还需要另一个预定义单元格来进行后续计算——预定还款金额。这是假设没有额外还款的情况下贷款的常规还款金额。请在单元格 B6 中应用以下公式：

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. 然后，在 Excel 中创建分期偿还计划表：
   • 设置指定的标签，如“期次”、“预定还款”、“额外还款”、“总还款”、“利息”、“本金”、“剩余余额”，分别填入 A8:G8 单元格中；
   • 在“期次”列中，输入您可能考虑的任何贷款的最高支付次数。例如，填写从 0 到 360 的数字。这可以覆盖标准的 30 年贷款，如果您每月支付一次的话；
   • 对于第 0 期（在我们的例子中为第 9 行），使用此公式 =B4 获取余额值，这对应于初始贷款金额。此行中的所有其他单元格应留空。
   • 

⭐️ 第二步：为包含额外还款的分期偿还计划表创建公式

逐个在相应的单元格中输入以下公式。为了增强错误处理能力，我们将此公式及所有后续公式封装在 IFERROR 函数中。这种方法有助于避免因任何输入单元格为空或包含错误值而引发的多种潜在错误。

● 计算预定还款额：

在单元格 B10 中输入以下公式：

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● 计算额外还款额：

在单元格 C10 中输入以下公式：

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● 计算总还款额：

在单元格 D10 中输入以下公式：

=IFERROR(B10+C10, "")

● 计算本金：

在单元格 E10 中输入以下公式：

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● 计算利息：

在单元格 F10 中输入以下公式：

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● 计算剩余余额

在单元格 G10 中输入以下公式：

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

完成每个公式后，选择单元格范围 B10:G10，并使用填充柄向下拖动以扩展这些公式至整个支付期数。对于未使用的期次，单元格将显示 0。见下图：

⭐️ 第三步：制作贷款摘要

● 获取预定支付次数：

=B2:B3

● 获取实际支付次数：

=COUNTIF(D10:D369,">"&0)

● 获取总额外还款额：

=SUM(C10:C369)

● 获取总利息：

=SUM(F10:F369)

⭐️ 结果：

按照这些步骤，您可以在 Excel 中创建一个动态的分期偿还计划表，该表考虑了额外还款。

使用 Excel 模板创建分期偿还计划表

使用模板在 Excel 中创建分期偿还计划表是一种简单且省时的方法。Excel 提供了内置模板，可以自动计算每次支付的利息、本金和剩余余额。以下是使用 Excel 模板创建分期偿还计划表的方法：

1. 点击 文件 > 新建，在搜索框中输入 分期偿还计划表，然后按 Enter 键。然后，选择最适合您需求的模板，点击它。例如，这里我将选择简单的贷款计算器模板。见下图：
   
2. 选择模板后，点击“创建”按钮以新工作簿的形式打开它。
3. 然后，输入您自己的贷款详情，模板应根据您的输入自动计算并填充计划表。
4. 最后，保存您的新分期偿还计划表工作簿。