Excel贷款计算器制作指南：等额本息和等额本金还款计算

Excel贷款计算器制作指南：等额本息和等额本金还款计算

本文以个人房屋按揭贷款为例，演示如何使用Excel计算房屋贷款利息与每月还款。学会了“如何在Excel中进行利息计算”的方法，可以应用到其它具有相同计算方法的贷款利息计算上。

若仅需要一个贷款计算器，且对如何使用Excel编制贷款计算器没有兴趣的，请勿往下。

个人房屋贷款按揭的两种主要还款方式

每月还款形式分为本额等息和本额本金两种：

• 本额等息：每月所还本金与利息总额相同
• 本额本金：每月所还本金相同

文中使用到的计算公式，在Excel、WPS、Numbers等电子表格软件中均可使用。

使用Excel计算本额等息方式的每月还款

Excel计算函数解释

使用Excel中的关键函数为：

PMT(利率, 支付总期数, 现值, [终值, [是否期初支付]])

先忽略非必选参数：

• 利率：数字值，每期的利率。按月还款时，要折算为月利率，即年利率/12。
• 支付总期数：整数，表示各期利率相同的时限，比如月、季、年。按月还款时，指总月数。
• 现值：表示初期投资值，或贷款或年金的金额。接收的金额为正值，投资或者支出的金额为负值。
• 终值：可选，表示最终付款后投资的值或年金的剩余现金值（正值），或贷款余额（负值）。省略为0。
• 是否期初支付：可选，表示每期付款的时候，如按月还款时，是月头（1）还是月末（0）？贷款还款一般表现为期末还款，为0（默认）。

计算等额本息每期还款金额

假设参数如下：

每月还款金额的计算公式为：

$$
\begin{aligned}
每月还款金额 &= PMT(年利率 \div 12, 还款年限 \times 12, -贷款金额) \
&= PMT(0.05 \div 12, 30 \times 12, -500000) \
&= 2684.11
\end{aligned}
$$

将=PMT(5%/12, 30*12, -500000)粘贴到Excel某个单元格，可计算出等额本息每月还款金额为2,684.11。

当每月还款金额计算出来后，即可得到总还款金额、累计利息总额：

$$
\begin{aligned}
总还款金额 &= 每月还款金额 \times 12个月 \times 30年 = 966,278.92 \
其中利息总额 &= 总还款金额 - 贷款金额 = 966,278.92 - 50万 = 466,278.92
\end{aligned}
$$

在Excel中编制计算表格

通过Excel来计算，可以将表格做成计算工具，可以根据需要调整贷款金额、贷款期限、利率等，还可以制作多个表格，用来比较不同的还款期限、利率，需要支付的利息差异。

等额本息贷款计算

如上图，在Excel中新建一张名为“贷款”的工作表，在以下位置填写相应的数值：

• 单元格 B2：贷款金额，如 500,000万
• 单元格 B3：贷款期限，按年
• 单元格 B4：贷款年利率，如 5%

则：

• 单元格 B6：每月还款=PMT(B4/12,B3*12,-B2)
• 单元格 B7：还款总额=B6 * B3 * 12
• 单元格 B8：利息总额=B7 - B2

将上述公式粘贴到对应的单元格，则可以得到上图所示表格。

每月还款

还款总额

利息总额

计算每月还款金额中的本金与利息

当还款方式为等额本息时，每月还款金额固定，但每月还款中所包含的本金与利息不同。随着还款期数的增加，所包含的本金递增↑，利息递减↓。

在Excel中分别使用如下函数来计算每期所包含的本金与利息：

// 计算每期还款中的本金
PPMT(利率, 期数, 支付总期数, 现值, [终值, [是否期初支付]])
// 计算每期还款中的利息
IPMT(利率, 期数, 支付总期数, 现值, [终值, [是否期初支付]])

以上函数的参数，除期数外，其它参数与PMT函数的参数含义相同。

期数表示对应第N期的本金或利息，如期数为2时的PPMT函数返回结果，表示第二期还款中所包含的本金。

如将计算每期还款的公式=PMT(B4/12,B3*12,-B2)改为=PPMT(B4/12,2,B3*12,-B2)，则可计算得到第二期还款中的本金部分。改用IPMT函数，即可得到第2期还款中利息部分。

为了表格的整洁，我们新建一张工作表“月还款”，专用于计算每一期的本金与利息。

月还款计划

如上图，从第二行A列开始，分别有期数、月供、月供本金、月供利息：从第三行开始，月供、月供本金、月供利息列分别填写函数，计算每期的情况。期数表示月数，从来开始。若贷款年限是30年，则期数最大为360（30×12）。

• 单元格 B3：月供
• 单元格 C3：月供本金部分
• 单元格 D3：月供利息部分

因引用的贷款金额、利率、还款年限为工作表“贷款”中的单元格，需要在公式中的单元格引用前加工作表名称：

注：

• 参数期数对应每一行的期数列的值，在Excel中直接改为“A行号”则可。
• $号表示绝对引用，“贷款!” B2、B3、B4分别引用了“贷款”工作表中的贷款金额、还款年限、利率，位置是固定的，为了方便单元格公式复制，使用绝对引用。（手输$号，或按F4）
• 因每期还款金额相同，则“月供”一列的值不会变化，通过公式也可以看出，它与期数无数。

下面，介绍第3行、第4行（对应第1、第2期）的月本金部分、月利息部分如何填写函数。

其它按行带公式复制粘贴则可，则可得到一张有360期的月还款计划表。

有360期的还款计划

计算每月还款后剩余待还本金

上面介绍了如何计算每期所还的本金、利息。从此延伸，还可以计算得到累计还款、累计已还本金（总共还了多少钱）、剩余本金（还欠多少钱）、累计已付利息（总共付了多少利息）。

利息是贷款服务所需要支出的成本，故使用付，而不是还，它并不是借来的。

通过还款计划表，可以看到计划中剩余未还本金，以及往后执行时需要付出的利息成本。在要提前结束贷款时，这些数据是非常重要的参考。

以计算第一、第二期累计已还本金、已付利息、未还本金为例：

注：

• A行号表示截止当期的期数，也表示第N期
• B行号表示每期还款，还款方式为等额本息的，每期均相等
• C行号表示当期的所还的本金部分
• E行号表示截止当期累计的已还本金
• F行号表示截止当期累计的已付利息
• G行号表示截止当期还款后，剩余未还的金额

计算本额本金的贷款利息

还款方式为本额本金的，每期应还本金相同，每期应付利息为年利率折算的月利率乘以剩余本金，无须使用到类似于本额本息还款计算使用的函数。本节不再敖述。

等额本金贷款计算

等额本金还款计划