贷款计算不复杂 学会这4个公式就好

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

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

一、PPMT函数与IPMT函数

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

①PPMT函数：=PPMT（，，，v，[fv]，[]）

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

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

②IPMT函数：=IPMT（，，，v，[fv]，[]）

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

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

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

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

二、CUMPRINC函数、CUMIPMT函数

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

①CUMPRINC函数：=CUMPRINC（，，v，_i，_i，）

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

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

②CUMIPMT函数：=CUMIPMT（，，v，_i，_i，）

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

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

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

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

三、制作还款明细表

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

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

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

①相对引用

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

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

②绝对引用

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

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

③混合引用

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

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

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

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

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

在C5单元格，通过PMT函数=PMT(C1*30,C2,C3)计算得到每期还款额值为-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

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

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

听说房贷和利息一样多？计算房贷的魔鬼公式，任何人都会算

很多朋友在后台问我，有没有计算房贷的简便公式。

有的！

银行不是慈善机构，贷款给你买房，是要收取利息的。如果不用收取利息的话，那么算法很简单，比如买100万的房子，首付3成，贷款就是70万，还30年，那每个月还款就是70万除以30，再除以12，每月还款1944.45元。

正因为银行不是慈善机构，要收取利息，所以房贷计算也就变得复杂起来。

房贷的还款方式有两种，等额本金和等额本息。

1、等额本金

等额本金的计算方式相对比较容易，还是以买100万的房子为例，首付3成，贷款70万，那么每月所还月供，就是本金和利息之和。本金不变，每月1944.45元；利息就是未还本金一个月所产生的利息。假如按基准4.9%计算，月利息就是0.4083%，第一个月利息是2858.33，第二个月未还本金的利息就是70万减掉1944.45元后的利息，是2850.39元……

细心的朋友想必已经发现了，每月月供的差额是固定的，就是1944.45乘0.1083%，等于7.94元。

我们今天主要想说的，是第二种方法：等额本息的计算方式。

2、等额本息

这种方式还款简便，前期还款压力相对等额本金来说较小，每月固定金额还款。但是月供的计算比较复杂，因为等额本息的房贷计算方式是按照复利计算的。

我们假设贷款金额是P，月供是，贷款月份数是，月利率是。

那么第一个月的利息就是(1+)，第二个月的利息就是(1+)2，以此类推，第个月的利息就是(1+)。

言归正传，我们先说等额本息的月供计算公式，就是下面这个

其中

：表示月供，是月字的首个拼音字母；

P：表示本金，假如你贷款100万，30年，P就是1000000；

i：表示月息，假如你商业贷款基准（4.9%）上浮10%，i=4.9×（1+10%）÷12=0.449167；

：表示月供的期数，假如你贷款30年，一年12个月，月供期数就是360。

详细推导过程如下：

有数学恐惧症的可以绕过。

下面说下这个公式是怎么推导出来的，这当中要用到一个公式，等比数列求和公式（我附到后面再说）。

我们假设S表示本金利息和（即本息），P表示本金，代表按揭者每月要偿还的等额月供，

那么根据复利公式，银行要获得的本息为

S=P×（1+i）

按揭者每月要偿还的等额月供也要计息，每个月产生的月供及月供的复利利息

第1个月×（1+i）-1

第2个月×（1+i）—2

第3个月×（1+i）--3

第4个月×（1+i）--4

…

第个月×（1+i）0=

这些月供、利息和也要等于银行的本息S

S=×（1+i）-1+×（1+i）—2+×（1+i）—3+…+×（1+i）2+×（1+i）+

=

=P×（1+i）

以上就是详细推导过程。

下面要总结终极大法了：

假如基准年利率是I,月利率是i，那么基准0.9、1、1.1、1.2倍分别是i1、i2、i3、i4。贷款总额大致是月供的199/188/178/168倍，

——约等于200倍（9折利率）

——190倍（基准利率）

——180倍（利率上浮10%）

——170倍（利率上浮20%）

下面要公布魔法公式了！

如果是9折基准利率，月供=贷款总额/200如果是基准利率，月供=贷款总额/190如果是1.1倍基准利率，月供=贷款总额/180如果是1.2倍基准利率，月供=贷款总额/170

月供计算实例：

比如说利率上浮10%，贷款30年等额本息，月供就是100万除以180，每月还款5555.56元。

当然，如果你实在是不愿意算，还可以借助房贷计算器。

奇迹发生了，我们的魔法公式计算的是5555.56，实际上月供是5609.07，相差仅仅54元，是不是算的很准？！

当然，这个魔法公式还可以根据你的月供承受能力，帮你反推你的购房总金额。

比如你每月有9000元存款，那么你能买得起什么样的房子呢？

如果利率是基准上浮10%，那么就是0.9乘180，等于162万。也就是说你买得起162/0.7=231万的房子。

贷款利息如何计算？最简单的计算方法是什么？

很多朋友办理贷款时并不清楚自己的贷款利息是如何计算的，只知道贷款年限及利率等数据，那么具体的贷款利息要如何计算呢？

我们知道贷款利率的基本计算公式为贷款利息＝贷款金额×利率×年限。但若按照这个公式来计算的话，一定和最终实际要还的利息对不上。

实际上，目前市面上所有贷款产品的利息计算方式都是一样的，但根据不同的还款方式，最终的利息计算方式也不同。

因为我们在漫长的还款期限内，是不停在偿还本金的，自然不能用总的贷款金额来计算利息了。

今天，小编汇总常见的四种还款方式，来给大家看看具体的利息到底如何计算。

1.先息后本

先息后本是先支付利息，然后按还款约定支付本金的还款方式。具体计算方式为：贷款金额×利率×年限。其利率为月利率，因此贷款期限按月来计算。

2.等额本息

等额本息是在还款期间，每月偿还同等数额的贷款。其计算公式为[贷款金额×月利率×（1＋月利率）^还款月数]÷[（1+月利率）^还款月数－1]，利率为月利率。

3.一次性还本付息

一次性还本付息是指在贷款期内，借款人不是按月偿还本息，而是贷款到期后一次性归还本金和利息的方式。其计算公式为：贷款金额×利率×年限。这与先息后本不同，利率为年利率，贷款期限按年来计算。

4.等额本金

等额本金是在还款期内，将总贷款数额等分，每月偿还同等数额的本金和剩余贷款在该月所产生的利息。其计算公式为：（贷款金额-已还款金额）×贷款利率。利率对应为月利率。

上述就是市面上常见的还款方式及对应的利息计算方式了。有需要的朋友速度点一波收藏哈，以备不时之需。

当然了，如果大家觉得比较麻烦的话，可以直接在百度上搜索【贷款利息计算器】来查询。

为了方便大家查询，目前各大银行官网都有对应的计算器，只需要根据自身情况输入金额、利率、期限等数据就能看到个人每期应该偿还的利息金额啦。还能够根据不同的数据，对比出最适合自己的还款方式哦。