在 Excel 中创建贷款摊销时间表可能是一项宝贵的技能，它使您能够有效地可视化和管理您的贷款还款。摊销时间表是详细说明摊销贷款（通常是抵押贷款或汽车贷款）的每笔定期付款的表格。它将每笔付款分解为利息和本金组成部分，显示每次付款后的剩余余额。让我们深入了解在 Excel 中创建此类计划的分步指南。

什么是摊销时间表？

在 Excel 中创建摊销时间表

创建可变周期数的摊销计划

创建包含额外付款的摊销时间表

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

什么是摊销时间表？

摊销表是贷款计算中使用的详细表格，显示随时间推移还清贷款的过程。摊销时间表通常用于固定利率贷款，例如抵押贷款、汽车贷款和个人贷款，在整个贷款期限内，还款额保持不变，但利息与本金的还款比例随着时间的推移而变化。

事实上，要在 Excel 中创建贷款摊销表，内置函数 PMT、PPMT 和 IPMT 至关重要。让我们了解一下每个函数的作用：

• 光电倍增管功能：此函数用于根据固定付款和固定利率计算贷款每期的总付款额。
• 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)

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

计算每次付款的利息和本金后，摊销计划的下一步是计算每次付款后贷款的剩余余额。这是时间表的关键部分，因为它显示了贷款余额如何随着时间的推移而减少。

1. 在余额栏的第一个单元格 - E7中，输入以下公式，这意味着剩余余额将为原始贷款金额减去第一笔还款的本金部分：

   =B4-D7

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

   =E7-D8

    备注：对平衡单元格的引用应该是相对的，因此当您向下拖动公式时它会更新。
   
3. 然后将填充柄向下拖动到该列。正如您所看到的，每个单元格都会根据更新的本金付款自动调整以计算剩余余额。
   

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

设置详细的摊销计划后，创建贷款摘要可以快速概述您的贷款的关键方面。该摘要通常包括贷款总成本、支付的总利息。

● 计算总付款：

=SUM(B7:B30)

● 计算总利息：

=SUM(C7:C30)

⭐️ 结果：

现在，一个简单而全面的贷款摊销时间表已经成功创建。看截图：

创建可变周期数的摊销计划

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

但是，如果您希望创建一个灵活的摊销计划，可重复用于不同期限的贷款，允许您根据不同贷款情况的需要修改还款次数，则需要遵循更详细的方法。

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

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, "") 

⭐️第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. 点击 文件 > 全新，在搜索框中输入 摊销时间表，然后按 输入 钥匙。然后，通过单击选择最适合您需求的模板。例如，在这里，我将选择简单贷款计算器模板。看截图：
   
2. 选择模板后，单击 创建 按钮将其作为新工作簿打开。
3. 然后，输入您自己的贷款详细信息，模板应根据您的输入自动计算并填充时间表。
4. 最后，保存新的摊销计划工作簿。