在 Excel 中创建贷款摊销计划表——分步教程

在 Excel 中创建贷款分期偿还计划表是一项非常实用的技能，能助您直观、高效地管理贷款还款。分期偿还计划表是一种详细列示每期还款明细的表格，适用于房贷、车贷等分期偿还类贷款。它将每笔还款拆分为利息和本金两部分，并清晰展示每次还款后的剩余贷款余额。接下来，我们将逐步指导您如何在 Excel 中轻松创建此类计划表。

什么是摊销计划表？

在 Excel 中创建摊销计划表

为可变期数创建摊销计划表

创建包含额外还款的摊销计划表

使用 Excel 模板创建摊销计划表（含额外还款）

什么是摊销计划表？

分期偿还计划表是一种用于贷款计算的详细表格，清晰展示贷款随时间逐步偿还的全过程。它适用于固定利率贷款，如房贷、车贷和个人贷款——这类贷款在整个还款期内每期还款金额保持不变，但其中利息与本金的比例会随着时间推移而动态调整。

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

• PMT 函数：该函数可根据固定付款额和固定利率，精准计算贷款每期的总还款额。
• IPMT 函数：用于计算指定期间付款中的利息部分。
• PPMT 函数：用于计算指定期间付款中的本金部分。

通过在 Excel 中运用这些函数，您可轻松创建一份详尽的分期偿还计划表，清晰呈现每期还款中的利息与本金构成，以及每次还款后的贷款剩余余额。

在 Excel 中创建摊销计划表

本节将为您介绍两种在 Excel 中创建分期偿还计划表的实用方法。无论您是 Excel 新手还是资深用户，都能根据自身偏好与技能水平，轻松打造一份详尽且精准的贷款分期偿还计划表。

使用公式能帮助您更深入地掌握底层计算逻辑，并可根据具体需求灵活自定义还款计划表。这种方法特别适合希望亲自动手操作、清晰了解每笔还款如何拆分为本金与利息的用户。接下来，我们将逐步拆解在 Excel 中创建分期偿还计划表的全过程：

⭐️ 步骤 1：设置贷款信息和分期偿还表

1. 输入相关的贷款信息，例如年利率、贷款年限、每年付款次数和贷款金额，如以下截图所示：
   
2. 然后，在 Excel 中创建一个分期偿还表，并在单元格 A7:E7 中设置以下标签：期间、付款、利息、本金和剩余余额。
3. 在“期间”列中输入期间编号。本例中，总付款期数为 24 个月（即 2 年），因此请在“期间”列中依次输入从 1 到 24 的数字。请参见截图：
   
4. 设置好包含标签和期间编号的表格后，即可根据贷款的具体情况，在“付款”、“利息”、“本金”和“余额”列中输入相应的公式与数值。

⭐️ 步骤 2：使用 PMT 函数计算总付款金额

PMT 函数的语法如下：

在单元格 B7 中输入以下公式，然后向下拖动填充柄将其填充至其他单元格，即可看到各期间的付款金额始终保持不变。请参见截图：

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

⭐️ 步骤 3：使用 IPMT 函数计算利息

在此步骤中，您将运用 Excel 的 IPMT 函数，精准计算每一期付款所对应的利息。

在单元格 C7 中输入以下公式，然后向下拖动填充柄，将公式应用至整列，即可自动计算每个期间的利息。

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

⭐️ 步骤 4：使用 PPMT 函数计算本金

在计算每个期间的利息后，创建分期偿还计划表的下一步是计算每笔付款中的本金部分。这可通过 PPMT 函数实现——该函数专为根据固定付款额和固定利率，精准确定特定期间付款中所含本金而设计。

IPMT 函数的语法如下：

PPMT 公式的语法和参数与前文所述的 IPMT 公式完全一致。

在单元格 D7 中输入以下公式，然后向下拖动填充柄，将本金值填充至每个期间。请参见截图：

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

⭐️ 步骤 5：计算剩余余额

在计算每笔付款的利息和本金后，分期偿还计划表的下一步是计算每次付款后的贷款剩余余额。这一环节至关重要，因为它清晰展现了贷款余额随时间逐步减少的过程。

1. 在余额列的第一个单元格（即 E7）中，输入以下公式，表示剩余余额等于原始贷款金额减去第一期付款的本金部分：

   =B4-D7

   
2. 对于第 2 期及后续所有期间，请从前一期余额中减去当期本金还款，即可计算出剩余余额。请在单元格 E8 中输入以下公式：

   =E7-D8

   注意：余额单元格的引用应使用相对引用，以便向下拖动公式时自动更新。
   
3. 然后向下拖动填充柄至整列，每个单元格将自动根据更新后的本金付款调整剩余余额。
   

⭐️ 步骤 6：制作贷款摘要

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

● 计算总付款额：

=SUM(B7:B30)

● 计算总利息：

=SUM(C7:C30)

⭐️ 结果：

现在，一份简洁而全面的贷款分期偿还计划表已成功生成，请参见下方截图：

借助 KUTOOLS AI 解锁 Excel 的神奇功能

• 智能执行：只需输入简单指令，即可执行单元格操作、分析数据并创建图表。
• 自定义公式：生成量身定制的公式，助您优化工作流程！
• VBA 编程：轻松编写并应用 VBA 代码，快速提升工作效率！
• 公式解析：轻松掌握复杂公式，一目了然！
• 文本翻译：轻松打破电子表格中的语言障碍！

借助 AI 驱动的工具提升您的 Excel 能力。立即下载，体验前所未有的高效！

为可变期数创建摊销计划表

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

但如果您希望创建一个灵活的摊销计划表，既能反复适用于不同期限的贷款，又能根据各种贷款场景按需调整付款次数，就需要采用更详细的方法。

⭐️ 步骤 1：设置贷款信息和摊销表

1. 输入相关的贷款信息，例如年利率、贷款年限、每年付款次数和贷款金额，如以下截图所示：
   
2. 然后，在 Excel 中创建一个分期偿还表，并在单元格 A7:E7 中设置如下标签：期间、付款、利息、本金和剩余余额。
3. 在“期间”列中，输入您可能考虑的任何贷款的最大付款期数，例如填写 1 到 360 之间的数字——若按月还款，即可覆盖标准的 30 年期贷款。
   

⭐️ 步骤 2：使用 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), "")

⭐️ 步骤 3：调整剩余余额公式

对于剩余余额，通常是从上期余额中减去本金。加入 IF 语句后，可将其调整为：

● 第一个余额单元格：（E7）

=B4-D7

● 第二个余额单元格：（E8）

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

⭐️ 步骤 4：制作贷款摘要

在使用修改后的公式设置好摊销计划表后，下一步便是创建贷款摘要。

● 计算总付款额：

=SUM(B7:B366)

● 计算总利息：

=SUM(C7:C366)

⭐️ 结果：

现在，您已在 Excel 中创建了一份全面且动态的摊销计划表，并附带详细的贷款摘要。每当您调整还款期限，整张摊销计划表都会自动更新，即时反映您的修改。请参见下方演示：

创建包含额外还款的摊销计划表

通过在计划付款之外增加额外还款，您能更快还清贷款。在 Excel 中创建包含额外还款的摊销计划表，可直观展示这些额外还款如何加速贷款偿还并减少总利息支出。以下是设置方法：

⭐️ 步骤 1：设置贷款信息和摊销表

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. 此行中的其他单元格应留空。
   • 

⭐️ 步骤 2：为包含额外付款的摊销计划表创建公式

依次在相应单元格中输入以下公式。为提升错误处理能力，我们将此公式及后续所有公式均嵌套于 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. 请参见截图：

⭐️ 步骤 3：制作贷款摘要

● 获取计划付款次数：

=B2:B3

● 获取实际付款次数：

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

● 获取总额外付款：

=SUM(C10:C369)

● 获取总利息：

=SUM(F10:F369)

⭐️ 结果：

通过以上步骤，您即可在 Excel 中创建一份动态摊销计划表，轻松纳入额外付款。

使用 Excel 模板创建摊销计划表

在 Excel 中使用模板创建摊销计划表，是一种简单高效的方式。Excel 提供了内置模板，可自动计算每期付款中的利息、本金及剩余余额。以下是使用 Excel 模板创建摊销计划表的步骤：

1. 单击文件 > 新建，在搜索框中输入分期偿还计划表，然后按 Enter 键。接着，点击选择最适合您需求的模板。例如，此处我将选择“简易贷款计算器”模板。请参见截图：
   
2. 选择模板后，单击创建按钮，即可将其作为新工作簿打开。
3. 然后输入您的贷款详细信息，模板将根据您的输入自动计算并填充还款计划表。
4. 最后，请保存您的全新分期偿还计划工作簿。