Excel 2016︱计算本金与利息函数

除了计算投资、存款的起始或终止值等函数之外，还有一些函数是可以计算在这过程中某个时间点的本金与利息，或某两个时间段之间的本金与利息的累计值，如表 18-2 所示。

每期还贷本金函数 PPMT 和利息函数 IPMT

PMT 函数常被用在等额还贷业务中，用来计算每期应偿还的贷款金额。而 PPMT 函数和 IPMT函数则可分别用来计算该业务中每期还款金额中的本金和利息部分，PPMT 函数和 IPMT 函数的语法如下。

其中的参数 per 是 period 的缩写，用于计算其利息数额的期数，必须在 1 到 nper 之间。

示例　贷款每期还款本金与利息

如图18-12所示，某人从银行贷款200万元，年利率为4.75%，共贷款25年，采用等额还款方式，计算第 10 个月还款时候的本金和利息各还多少？

在 C7 单元格输入以下公式：

=PPMT(C2/12,C5,C3,C4)

在 C8 单元格输入以下公式：

=IPMT(C2/12,C5,C3,C4)

在 C9 单元格输入以下公式计算每月还款额：

=PMT(C2/12,C3,C4)

C7 和 C8 单元格分别计算出此贷款在第 10 个月还款时所还的本金与利息。在等额还款方式中，还款的初始阶段，所还的利息要远远大于本金。但二者金额的和始终等于每期的还款总额，即在相同条件下 PPMT+IPMT=PMT。

累计还贷本金函数 CUMPRINC 和利息函数 CUMIPMT

使用 CUMPRINC 函数和 CUMIPMT 函数可以计算某一个阶段所需要还款的本金和利息的和。

CUMPRINC 函数和 CUMIPMT 函数的语法如下。

示例　贷款累计还款本金与利息

如图18-13所示，某人从银行贷款200万元，年利率为4.75%，共贷款25年，采用等额还款方式。

需要计算第 2 年，即第 13 个月到第 24 个月期间需要还款的累计本金和利息。

在 C8 单元格输入以下公式：

=CUMPRINC(C2/12,C3,C4,C5,C6,0)

在 C9 单元格输入以下公式：

=CUMIPMT(C2/12,C3,C4,C5,C6,0)

在 C10 单元格输入以下公式计算第二年的还款总额：

br

C8和C9单元格分别计算出此贷款在第2年时所还款的本金和与利息和，它们和PMT的关系为：

CUMPRINC+CUMIPMT=PMT* 求和期数

这两个函数与之前介绍的财务函数不同，最后一个参数 type 不可省略，通常情况下，第一次付款是在第一期之后发生的，所以 type 一般使用参数 0。

制作贷款计算器

利用财务函数可以制作贷款计算器，以方便了解还款过程中的每一个细节。

示例　制作贷款计算器

如图 18-14 所示，C2 单元格输入贷款的年利率，C3 单元格输入贷款的总月数，即贷款年数乘以12。C4单元格输入贷款总额。本例中以年利率为4.75%，共贷款25年，贷款总额200万元为参考。

在 C6 单元格输入以下公式，计算每月的还款额。

=PMT(C2/12,C3,C4)

在 C7 单元格输入以下公式，计算连本带息的还款总金额。

=C6*C3

在 C8 单元格输入以下公式，计算还款利息总金额。

=C7+C4

此公式还可以使用 CUMIPMT 函数直接计算，公式为：

=CUMIPMT(C2/12,C3,C4,1,C3,0)

在 E2:E301 单元格区域输入 1 到 300 的序数。

在 F2 单元格输入以下公式，并向下复制到 F301 单元格，计算每一期还款中所还本金。

=PPMT($C$2/12,$E2,$C$3,$C$4)

在 G2 单元格输入以下公式，并向下复制到 G301 单元格，计算每一期还款中所还利息。

=IPMT($C$2/12,$E2,$C$3,$C$4)

在 H2 单元格输入以下公式，并向下复制到 H301 单元格，计算剩余未还本金。

=$C$4+CUMPRINC($C$2/12,$C$3,$C$4,1,E2,0)

此公式还可以使用 FV 函数做计算，理解为期初 200 万投资，每月取款 11402.35 元，第 n 期后的未来值是多少，公式为：

=-FV($C$2/12,E2,$C$6,$C$4)

在 I2 单元格输入以下公式，并向下复制到 I301 单元格，计算剩余未还利息。

=CUMIPMT($C$2/12,$C$3,$C$4,1,E2,0)-$C$8

至此贷款计算器便制作完成，可以较为直观地看到所需要还款的金额及每期的还款金额。通过每期的还款情况可以看出，初期还款所还利息远远大于本金。随着时间的推移，每月还款的本金越来越多，所还利息越来越少，直到为 0，如图 18-15 所示。

---------------------------------------------------------------------