在 Excel 中建立贷款摊销时间表 – 逐渐教学-英雄云拓展知识分享

在 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)

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

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

1. 在余额栏的第一个贮存格 - E7中，输入以下公式，这表示剩余余额将为原始贷款金额减去第一笔还款的本金部份：

   =B4-D7

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

   =E7-D8

    备注：对平衡单元格的参考应当是相对的，因此当您向下拖曳公式时它会更新。

3. 然后将填充柄向下拖曳到该列。正如您所看到的，每一个贮存格都会针对更新的本金付款自动调剂以计算剩余余额。
   

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

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

● 计算总付款：

=SUM(B7:B30)

● 计算总利息：

=SUM(C7:C30)

⭐️ 结果：

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

使用 Excel 範本建立摊销时间表

使用範本在 Excel 中建立摊销时间表是一种简单且省时的方法。 Excel 提供内建模板，可自动计算每笔付款的利息、本金和余额。以下是如何使用 Excel 範本建立摊销时间表：

1. 点击 文件 > 崭新，在搜索框中输入 摊销时间表，然后按 Enter 钥匙。然后，透过点击选择最合适您需求的範本。举例来看，在这里，我将选择简单贷款计算器範本。看截图：
   
2. 选择模板后，点击 创建 按钮将其作为新工作簿开启。
3. 然后，输入您自己的贷款详细信息，模板应针对您的输入自动计算并填充时间表。
4. 最后，保存新的摊销计画工作簿。

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

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

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

⭐️ 第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

● 第2个平衡单元：(E8)

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

⭐️第4步：製作贷款摘要

使用修改后的公式设定摊销计画后，下一步是建立贷款摘要。

● 计算总付款：

=SUM(B7:B366)

● 计算总利息：

=SUM(C7:C366)

⭐️ 结果：

现在，您在 Excel 中具有全面且动态的摊销时间表，并附有详细的贷款摘要。每当您调剂付款期限时，全部摊销时间表都会自动更新以反应这些变更。请参阅下面的演示：