如何在 Excel 中创建摊销计划：步骤详解

摊销计划为您的财务提供清晰的路线图，展示在债务完全还清之前，每期还款如何在利息与本金之间分配。

借助 Excel 的内置函数，您可以创建详细的摊销计划，可视化贷款的整个生命周期，做出明智的财务决策，甚至探索提前还清的选项。

本文将教您如何从零开始在 Excel 中构建一份完整的摊销计划。我们将涵盖 PMT()、IPMT() 和 PPMT() 等关键函数，带您按步骤完成构建过程，并结合实用场景，强化您的财务规划工具箱。

究竟什么是摊销计划？

摊销计划是一张完整的分期贷款还款表，显示在贷款到期还清之前，每期还款中包含的本金与利息金额。

当您申请一笔贷款时，有几个关键要素决定您的还款方式：

• 本金：借入的原始金额，须偿还
• 利息：借款成本，按本金的一定比例计算
• 还款周期：您进行还款的频率（每月、每两周等）
• 贷款期限：您偿还贷款的总时长

对于大多数标准贷款——如按揭、汽车贷款和个人贷款——整个期限内的每期还款额保持一致。然而，每期还款中利息与本金的占比会发生变化。在早期阶段，每期还款中较大比例用于支付利息。随着期限推进，更多的还款将用于减少本金余额。

创建摊销计划的主要目的在于提升贷款透明度。它可让您清楚看到随时间推移需支付多少利息、累积权益的速度，以及额外还款将如何影响贷款时间线。对于企业，这些计划还可用于会计与税务筹划。

需要掌握的 Excel 函数与公式

Excel 并没有单一的“摊销”函数。相反，您通常会借助多个财务函数协同工作，计算贷款还款的不同组成部分来构建摊销计划。以下是您需要的关键函数：

PMT() 函数

PMT() 函数基于固定还款额与固定利率计算贷款的每期还款：

=PMT(rate, nper, pv, [fv], [type])

• rate：每期利率（年利率除以还款频率）

• nper：总还款期数

• pv：现值（贷款金额）

• [fv]：可选的未来值（对于全额还清的贷款通常为 0）

• [type]：可选；0 表示期末付款，1 表示期初付款

要计算一笔 30 年期、年利率 4.5%、金额 250,000 美元的按揭贷款的月供：

=PMT(4.5%/12, 30*12, 250000)

该公式返回 -$1,266.71，表示借款人的现金流出。

关于 PMT() 函数的详细解读，包括常见错误与高级用法示例，您可参阅我们的完整 PMT() Excel 指南。

IPMT() 函数

IPMT() 函数用于计算特定期次的利息部分：

=IPMT(rate, per, nper, pv, [fv], [type])

• per：您要计算的具体期次

per 参数至关重要，它决定您计算的是哪一期的利息金额——不同于对所有期次给出相同还款额的 PMT()，IPMT() 的结果会随期次不同而显著变化，因为利息占比会随贷款期限递减。

这可精确显示某一期还款中有多少用于支付利息。

PPMT() 函数

PPMT() 函数用于计算特定期次的本金部分：

=PPMT(rate, per, nper, pv, [fv], [type])

与 IPMT() 类似，本金金额会随期次变化，前期本金占比较小，后期则更大。它展示某一期还款中有多少用于减少贷款余额。

自定义公式

对于每个还款期，您需要跟踪：

剩余余额：每次还款后的未偿贷款金额

Remaining balance = Previous balance - Current principal payment

累计本金：截至当前已支付的本金总额

Cumulative principal = Previous cumulative principal + current principal payment

累计利息：截至当前已支付的利息总额

Formula: Previous cumulative interest + current interest payment

Round() 函数

即便很小的四舍五入误差，也可能在摊销计划中随时间累积：

=ROUND(value, 2)

这可确保所有金额四舍五入到美分位。

绝对引用与相对引用

在构建摊销表时，您需要：

• 对利率与贷款金额等固定值使用绝对引用（$）

• 对每期变化的数值使用相对引用

例如，下面的公式对利率、贷款期限与金额使用绝对引用，而对期次使用相对引用：

=PPMT($B$2/12, A10, $B$3*12, $B$1)

创建摊销计划的分步指南

了解关键函数后，让我们从零开始构建一份摊销计划。通过本教程，您将创建一张完整的贷款还款表，跟踪贷款全周期内的每期还款。

1. 输入贷款数据

在专用输入单元格中输入您的贷款详情，如下例所示：

• 贷款金额：$250,000（单元格 B1）
• 年利率：4.5%（单元格 B2）
• 贷款期限：30 年（单元格 B3）
• 还款频率：每月（单元格 B4）

设置贷款输入参数。图片来源：作者。

2. 计算月供

在单元格 B5 中，使用 PMT 函数计算月供：=PMT(B2/12, B3*12, B1)

该公式返回 -$1,266.71，即您的每月还款额。负号表示这是付款（现金流出）。

设置列标题与公式。图片来源：作者。

3. 创建计划的第一行

接下来，我们创建以下表头并完成第一行数据（第 7 和第 8 行）：

• 期次：在单元格 A8 中输入 1

• 还款金额：单元格 B8 输入 =$B$5（引用已计算的月供）

• 利息支付：单元格 C8 输入 =IPMT($B$2/12, A8, $B$3*12, $B$1)

• 本金支付：单元格 D8 输入 =PPMT($B$2/12, A8, $B$3*12, $B$1)

• 剩余余额：单元格 E8 输入 =$B$1+D8（原始贷款加上本金支付，由于 D8 为负值，实质上从贷款中扣减本金）

• 累计本金：单元格 F8 输入 =D8（首行与本金支付相同）

• 累计利息：单元格 G8 输入 =C8（首行与利息支付相同）

展示首期还款的构成。图片来源：作者。

4. 完成整个计划

对于第二行（第 9 行）：

• 期次：单元格 A9 输入 =A8+1（每次加 1）

• 还款金额：将 B8 的公式复制到 B9

• 利息支付：单元格 C9 输入 =IPMT($B$2/12, A9, $B$3*12, $B$1)

• 本金支付：单元格 D9 输入 =PPMT($B$2/12, A9, $B$3*12, $B$1)

• 剩余余额：单元格 E9 输入 =E8+D9（上期余额加本期本金）

• 累计本金：单元格 F9 输入 =F8+D9（上期累计加本期本金）

• 累计利息：单元格 G9 输入 =G8+C9（上期累计加本期利息）

请注意，在 IPMT() 与 PPMT() 两个函数中，A9 使用的是相对引用（无美元符号），当您向下复制公式时，会自动更新为 A10、A11 等，每次都计算正确的期次。

设置可向下复制的公式。图片来源：作者。

摊销计划中的前几期还款。图片来源：作者。

选中第 9 行的所有单元格，向下拖拽至第 367 行（30 年期按月还款共 360 期）。

随着贷款期限推进，每期还款中用于本金的比例上升、用于利息的比例下降。图片来源：作者。

5. 核验最后一期还款

在构造良好的摊销计划中，最后一期还款应使剩余余额精确为零，且累计本金应等于原始贷款金额。

最后一期还款将剩余余额精确归零。图片来源：作者。

如您在最后一行所见，剩余余额为 $0.00（已高亮），累计本金等于 $250,000，与原始贷款金额一致。这表明我们的摊销计划准确无误。

在核验最终余额时，将 ROUND() 应用于关键计算会很有帮助。由于 Excel 的计算精度，微小的小数差异可能导致最终余额出现极小残差而非精确为零。使用 =ROUND(value, 2) 可确保所有金额与常见财务报告标准保持一致。

您可能还需考虑的事项

标准计划是起点。考虑以下方面以增强模型的灵活性：

管理还款变动

当您为不同还款场景进行自定义时，摊销计划将更具价值。若要建模额外还款，可创建一个用于补充还款的输入单元格，并在本金支付公式中加入该金额。此方法有助于直观展示额外还款如何缩短贷款期限并减少总利息。对于含有尾款（balloon payment）或浮动利率的贷款，考虑在计划中建立单独的部分，在特定时点反映利率变化。您可以使用 Excel 的情景管理器（Scenario Manager）比较不同利率环境及其对总成本的影响。

提升可读性与分析性

恰当的格式能让摊销计划从基础表格升级为有洞见的财务工具。对所有金额应用统一小数位的货币格式，并使用条件格式突出关键里程碑——例如已还清一半本金或达到特定权益阈值。同样重要的是了解不同计息方式对还款的影响。按日计息（学生贷款常见）基于 365 天，许多按揭则采用按月计息。记录您的计划所采用的计息方式，可确保与实际贷款对账单对比时的准确性。

针对特定贷款类型进行定制

不同贷款具有独特特征，可能需要对基础摊销模板进行调整。对于含有代管账户（escrow）的按揭，可新增列以跟踪保险与税费，与本金和利息并列。对于负摊销贷款（即还款未覆盖应计利息），需修改剩余余额计算，将未付利息计入本金。Excel 财务函数中的付款时点选项（type 参数）可用于模拟期初或期末付款，这对租赁与部分商业贷款尤为实用，因为其时点惯例不同于标准消费贷款。

结语

在 Excel 中创建摊销计划可让您对贷款有超越多数贷方提供信息的洞察。通过自行构建计划，您能清楚看到每期还款如何影响余额，理解借款在时间维度上的真实成本，并就提前还款选项做出明智决策。通过这一过程所培养的技能——运用 Excel 财务函数、构建动态公式与设计信息丰富的表格——远不止于贷款分析，还能应用于个人与企业财务规划的诸多方面。

如果您希望超越摊销范畴，构建更完整的财务模型——包括收入预测、投资评估与情景分析——欢迎查看我们的 Financial Modeling in Excel 课程。这是强化您用于财务分析的表格技能的理想下一步。请记住，您对这些财务计算越熟练，就越能掌控自己的财务未来——无论是管理个人债务、规划商业投资，还是为客户提供财务方案建议。