关于贷款的公式(人不要太复杂简单就好)

摆账网知识与问答 2024-11-29 00:35:24

上一篇文章解释了RATE函数和PMT函数。本文将介绍贷款功能。

1.PPMT函数和IPMT函数

PMT函数用于根据固定利率和等额分期还款计算每期的还款额。PPMT函数和IPMT函数分别根据固定利率和等额分期还款计算每期的本金和利息。他们之间的关系是PMT=PPMTIPMT。

PPMT函数：=PPMT(rate，per，nper，pv，[fv]，[type])

用途：PPMT函数是支付原则的缩写，其中原则是指本金，支付是指每期的还款额。因此，支付原则是指每笔还款金额中的本金金额。PPMT函数基于固定利率和等额分期付款，返回贷款每期的本金金额。

参数：Rate是每期的利率，PER(period的缩写，意为特定的一期)用于计算本金金额的期数(1PerNper)，Nper是支付期数，Pv是本金，Fv是期末的本息之和，Type指定每期的还款时间是在期初还是期末(期初值为1，期末值为0)。

IPMT函数：=IPMT(rate，per，nper，pv，[fv]，[type])

用途：IPMT函数是支付利息的缩写，其中利息就是利息。因此，还款利息是指每期还款额中的利息。IPMT函数基于固定利率和等额分期还款，计算贷款每期的利息。

参数：IPMT函数的参数与PPMT的参数完全相同，不再介绍。

案例：我在白洁借了一万元，选择分六期还，也就是六个月，日利率万分之五。第三期支付的本金和利息分别是多少？

Excel=PPMT(rate，per，nper，pv，[fv]，[type])=(0.05%，3，6，10000) and=IPMT(rate，per，nper，pv，[fv]，[type])=(0.05)每期还款额PMT为1755.25元。

第二，CUMPRINC函数，CUMIPMT函数

CUMPRINC函数计算一定期间(从期初到期末)累计还款金额中的本金之和；CUMIPMT函数计算一定期间(从期初到期末)累计还款金额的利息之和。这两个函数与PMT函数的关系是：PMT* sum期数=cumprince cumipmt。

累计函数：=累计函数(比率，nper，pv，start_period，end_period，类型)

目的：累计函数由累计的前三个字母和本金的前五个字母组成，表示累计和。给定计算范围为表示时间开始的start_period和表示时间结束的end_period，Cumprince函数表示特定期间的本金之和。

参数：rate为每期利率，nper为还款期数，pv为贷款总额，start_period开始时间，end_period结束时间，type指定每期还款时间是在期初还是期末(期初值为1，期末值为0)。由于每个还款周期都是在期末，比如下面列出的借款案例，2017年10月5日借款的第一个还款周期是第一个月到期后的2017年11月5日，所以不能省略类型，需要填写表示期末的值0。

CUMIPMT函数：=CUMIPMT(rate，nper，pv，start_period，end_period，type)

目的：CUMIPMT函数由Cumlulative的前三个字母和支付利息的缩写组成。给定计算范围，IPMT是表示时间开始的start_period和表示时间结束的end_period。所以CUMIPMT函数就是特定时期利息的总和。

参数：CUMIPMT函数的参数与CUMPRINC函数的参数完全相同，不再介绍。

案例：本人向借款人借款1万元，选择分六期还，即六个月，日利率千分之五。第三期到第五期一共还了多少本金，还了多少利息？

在Excel中输入=cumprince (rate，nper，pv，start_period，end_period，type)=(0.05%，6，10000，3，5)和=CUMIPMT(rate，nper，pv，start_period，end_period，type)。3，5)，结果分别为-5036和-229.7，即第三期至第五期还贷本金之和为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

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

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