贷款利息计算方法、生源地贷款利息计算方法…

本文是接着文章一文读懂贷款利率、利息、分期还款额的计算（附案例演示），继续探讨如何运用Excel计算贷款利率、利息、分期还款额。

前一篇文章讲解了RATE函数、PMT函数，本文将介绍贷款函数。

一、PPMT函数与IPMT函数

PMT函数用来计算基于固定利率和等额分期还款方式下的每期还款额。PPMT函数和IPMT函数则基于固定利率和等额分期还款方式，分别计算每期还款额中的本金和利息。三者关系为PMT=PPMT+IPMT。

① PPMT函数：=PPMT（rate，per，nper，pv，[fv]，[type]）

用途：PPMT函数是Principle of Payment的缩写，其中Principle意思是本金，Payment意思是每期还款额。因此，Principle of Payment的意思是每期还款额中的本金数额。PPMT函数是基于固定利率及等额分期付款方式，返回贷款的每期付款额中的本金数额。

参数：Rate为每期利率，Per（Period的缩写，意思是某一特定时期）用于计算本金数额的期数（1≤Per≤Nper），Nper为付款期数，Pv为本金，Fv为期末本金和利息和，Type指定各期的还款时间是在期初还是在期末（期初值为1，期末值为0）。

② IPMT函数：=IPMT（rate，per，nper，pv，[fv]，[type]）

用途：IPMT函数是Interest of Payment的缩写，其中Interest是利息的意思。因此，Interest of Payment的意思是每期还款额中的利息。IPMT函数是基于固定利率及等额分期还款方式，计算贷款的每期还款额中的利息。

参数：IPMT函数的参数与PPMT参数完全一致，所以不再介绍。

案例：在借呗借款10000元，选择分6期，即6个月偿还，日利率万分之五，请问第3期偿还的本金和利息分别是多少？

在Excel输入：=PPMT（rate，per，nper，pv，[fv]，[type]）=（0.05%，3，6，10000）和=IPMT（rate，per，nper，pv，[fv]，[type]）=（0.05%，3，6，10000），分别得到结果为-1653.77元、-101.48元，即第3期时需要偿还本金1653.77元，需要支付利息101.48元。每期还款额PMT为1755.25元。

二、CUMPRINC函数、CUMIPMT函数

CUMPRINC函数计算一定时期内（从start-period到end-period），累计还款额中的本金之和；CUMIPMT函数计算一定时期内（从start-period到end-period），累计还款额中的利息之和。这两个函数与PMT函数的关系为：PMT*求和期数=CUMPRINC+CUMIPMT。

① CUMPRINC函数：=CUMPRINC（rate，nper，pv，start_period，end_period，type）

用途：CUMPRINC函数由表示累计的Cumlulative的前三个字母和表示本金的Principal的前五个字母组合而成，给定了计算的范围为表示时间开始的start_period和表示时间结束的end_period，因此CUMPRINC函数意为特定时期的本金之和。

参数：rate为每期利率，nper为还款期数，pv为贷款总额，start_period开始时间，end_period结束时间，type指定各期的还款时间是在期初还是在期末（期初值为1，期末值为0）。由于每一期还款是在期末，比如下面会列举的借呗案例中2017年10月5日借款的首次还款时间为第一个月满后的2017年11月5日，所以type不能省略，需要填写表示期末的值0。

② CUMIPMT函数：=CUMIPMT（rate，nper，pv，start_period，end_period，type）

用途：CUMIPMT函数由Cumlulative的前三个字母和Interest of Payment的缩写IPMT构成，给定了计算的范围为表示时间开始的start_period和表示时间结束的end_period，因此CUMIPMT函数意为特定时期的利息之和。

参数：CUMIPMT函数的参数与CUMPRINC函数的参数完全一致，所以不再介绍。

案例：在借呗借款10000元，选择分6期，即6个月偿还，日利率万分之五，请问第3期到第5期一共还了多少本金，又一共还了多少利息？

在Excel中输入=CUMPRINC（rate，nper，pv，start_period，end_period，type）=（0.05%,6,10000,3,5）和=CUMIPMT（rate，nper，pv，start_period，end_period，type）=（0.05%,6,10000,3,5），分别得到结果为-5036和-229.7，即第3期到第5期偿还借呗的本金之和为5036元，偿还利息之后为229.7元。

三、制作还款明细表

用Excel制作还款明细表，可以让人清楚地看到整个还款期中每一期偿还的本金和利息，以及每一期偿还后剩余应还本金和应还利息。

讲这部分前，需要先讲一个知识点：相对引用、绝对引用和混合引用。

如果在单元格A1中输入=B1，那么，A1就是B1的引用单元格，而B1则成为A1的从属单元格。从属单元格和引用单元格之间有3种不同的引用方式，分别是相对引用、绝对引用和混合引用。Excel中通过美元符号$来区分这三种引用，输入美元符号的办法是英文状态输入状态下shift+4。

①相对引用

相对引用是指，当复制Excel函数到其他单元格时，Excel保持从属单元格与引用单元格的相对位置不变。

如上图所示，在B1单元格输入=A1后，如果向下复制公式，B列每一个单元格执行的都是等于跟其在同一行的A列的单元格，B2单元格中的公式为=A2，B3中变为=A3，B4中的单元格公式为C4，等等；而如果向右复制公式，右边的单元格执行的是等于其左边相邻的单元格的引用规则，如C1单元格的公式变为=B1，D1单元格的公式变为=C1，等等。

②绝对引用

在绝对引用的情况下，复制公示到其他任何单元格，Excel保持所引用的单元格位置不变。

如上图所示，在单元格B1输入=$A$1，即意味着B1绝对引用A1，之后B1的公示无论是复制到C1，还是B5，或者D3，所引用的单元格位置都指向A1。

③ 混合引用

使用混合引用时，复制单元格到其他单元格，Excel出现两种情况：列绝对引用，行相对引用；列相对引用，行绝对引用。

如上图所示，在B1单元格先后输入=A1和=$A1，会发现相对引用状况下，将B1单元格的公式=A1复制到C3时，C3返回的是0，因为遵从的是=B3，而B3是空格，所以返回值0；而在绝对引用的情况下，将B1单元格的公式复制到C3时，C3的值从之前的0变成了2，是因为C3单元格遵从是=$A3。

讲完了相对引用、绝对引用和混合引用，接下来可以讲制作贷款明细表了。

案例：在借呗借款10000元，选择分6期，即6个月偿还，日利率万分之五，请问第1期至第6期每一期偿还的本金是多少，每一期支付的利息是多少，每一期偿还后剩余应还本金是多少，每一期偿还后应还的利息还有多少？

先在Excel中C1单元格录入日利率rate值0.05%，C2单元格录入还款期数nper值6，C3单元格录入贷款总额pv值10000。

在C5单元格，通过PMT函数=PMT(C1*30,C2,C3)计算得到每期还款额pmt值为-1755.25；在C6单元格，通过=C5*C2，即每期还款额乘以还款期数，计算得到还款总额为-10531.51；在单元格C7，可以使用=C6+C3（也可以用更复杂的=CUMIPMT(C1*30,C2,C3,1,6,0)计算出来），即还款总额加贷款总额，计算得到还款利息总额为-531.51。

在E2单元格输入1，向下拉到E7，得到还款期数1到6共6个数值。这个后面计算剩余应还本金、剩余应还利息时均需要相对引用E2单元格。

在F2、G2、H2、I2，依次输入以下四个公式，回车后分别下拉到F7、G7、H7、I7，即可得到此次借呗借款的还款明细表。

每期偿还本金为：=PPMT($C$1*30,$E2,$C$2,$C$3)

每期偿还利息为：=IPMT($C$1*30,$E2,$C$2,$C$3)

剩余应还本金为：=$C$3+CUMPRINC($C$1*30,$C$2,$C$3,1,E2,0)

剩余应还利息为：=CUMIPMT($C$1*30,$C$2,$C$3,1,E2,0)-$C$7

在明细表上，可以清晰地看到等额本息还款模式下，每期偿还的本金越来越多，而每期偿还的利息越来越少。

关于贷款函数的介绍，本文依然没有写完，敬请期待后续文章。