返回目录：理财投资
本文是接着文章一文读懂贷款利率、利息、分期还款额的计算（附案例演示），继续探讨如何运用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
在明细表上，可以清晰地看到等额本息还款模式下，每期偿还的本金越来越多，而每期偿还的利息越来越少。
关于贷款函数的介绍，本文依然没有写完，敬请期待后续文章。