Excel太强大了，居然还能计算房贷月供和利息

大家好！今天和大家分享可以用于计算房贷月供、利息的Ex函数。

这些函数分别为：

PMT：计算等额本息还款下月供；

CUMIPMT：计算等额本息还款下支付的总利息；

IPMT：计算等额本息还款下某期支付的利息；

ISPMT：计算等额本金还款方式下某期支付的利息。

在介绍函数之前，我们先来认识这些函数都会使用到的三个参数：

1、：利率；

2、v：v，现值。即贷款总额。

3、：还款期数。例如贷款30年，按月还款，则为30*12=360期。

接下来分别介绍等额本息还款、等额本金还款方式下的月供、利息的计算。

1

等额本息

如下图所示，贷款100万，期限30年，利率5.48%。等额本息还款方式下，分别计算月供、支付的总利息、每月支付的本金和利息。

1、计算月供

计算月供使用PMT函数，语法为

PMT(,,v,[fv],[])

指贷款利率、指贷款总期数、v指贷款总额。

在B5单元格输入公式：=PMT(B4/12,B3*12,B2)

由于是按月还款，因此公式中的利率为“年利率/12”，将年利率转换为月利率；贷款30年，贷款总期数为“30*12=360期”。

每月还款属于资金流出，因此计算出的月供为负值。可以在公式前添加负号，将其转为正数。

2、计算总支付利息

计算支付的总利息使用CUMIPMT函数，语法为

CUMIPMT(,,v,_i,_i,)

在B6单元格输入公式：

=-CUMIPMT(B4/12,B3*12,B2,1,B3*12,0)

前三个参数、、v与PMT函数参数含义相同。参数_i、_i分别指计算累计利息的开始期数、结束期数。计算30年贷款支付的总利息时，_i为1，_i为360。最后一个参数可以为0或1，用于确定期末支付或期初支付。

3、计算每月支付的利息

想要计算每月还款中，支付的利息有多少，可以使用IPMT函数。语法为IPMT(,,,v,[fv],[])

参数指计算第几期支付的利息。

在F2单元格输入公式：

=-IPMT($B$4/12,D2,$B$3*12,$B$2)

从F2单元格计算结果可知，第1个月月供5665元，其中支付利息4567元。

2

等额本金

在等额本金还款方式下，每月支付的本金是固定的，比如贷款100万，期限30年，那么每月支付的本金为1000000/(30*12)=2778元。每月支付的利息不是固定的，随着支付的本金越来越多，利息逐渐减少。

等额本金还款方式下，可以使用ISPMT函数计算每期支付的利息，语法为ISPMT(,,,v)

如下图所示，在E2单元格输入公式：

=-ISPMT($B$4/12,D2-1,$B$3*12,$B$2)

由E2单元格计算结果可知，第1个月支付利息4566.67元，再加上支付的本金2777.78元，那么第1个月月供为7344元。

计算支付的总利息，可以在B5单元格输入公式：=SUM(E2:E361)

从B5单元格公式结果可知，等额本金还款方式下支付的总利息为82万元。

从上述计算结果可知，贷款100万，期限30年，年利率5.48%时，等额本息还款方式下，月供5665元，总利息104万。

等额本金还款方式下，总利息82万；第1个月月供7344元，月供逐渐减少，在第12年，开始低于等额本息还款月供。

分期还款式贷款的真正利率有多高？怎样算出被隐藏的真实利率？

说利率的高低，参照物是同期人民银行颁布执行的利率的多少，没有这个参照物，就失掉了标准，就说不上什么是高，什么是低，同样，界定是否属于高利贷也是参照这个标准。

现在以贷款10000元一年期为例来分析说明。

到银行贷款，假设银行年利率为5%，一年后到期，连本带利还给银行10500元。

分期还款式贷款，假设每月固定还款990元，从贷款的第二个月起开始还款，12个月还清，到期后，连本带利应还款11880元，付了利息1880元。这时候，利率怎样算？表面上看很简单，用1880除以10000，得利率为18.8%。这样一算，利率是比银行高点，但是没有超出国家许可的范围，并不算过分，毕竟这种形式贷款方便的多。

实际上，这种算法掩盖了真相，很多使用这种贷款的人被这种算法迷惑了还浑然不知，包括那些本身就是搞金融专业的一些高知人群。

那么，真相是如何被掩盖的呢？这种情况下，它的实际利率是多少？怎样算出它的被隐藏了的真实利率呢？

首先，我们要明白的是，利率是由什么产生的，是由初借本金产生的，还是由实际使用的本金产生的。按照通常的说法，在利息不变的情况下，利率的高低是由实际使用的本金的多少决定的，而不是由初借本金多少决定的。

如果初借本金在借期内固定不变，那么借期内实际使用的本金也是不变的，利率也保持不变。银行贷款就是这种。如果初借本金在借期内逐月递减，那么，借款人实际使用的本金也在逐月减少，在利息不变的情况下，利率也会随着实际使用的本金的逐步减少而不断升高。一些分期还款式的贷款就是这种。

先来看从银行贷款一万元钱本金，在一年借期内实际使用的本金，12个月月累计本金是120000元，月平均使用10000元，实际使用本金与初借本金保持不变。

再看分期还款式的贷款，初借本金一万元，从借款的第二个月起，每月固定还款990元，这样借款人实际使用的本金在逐月递减，从第一个月到第十二个月，实际使用的本金依次是：10000元、9010元、8020元、7030元、6040元、5050元、4060元、3070元、2080元、1090元、100元、—890元。十二个月月累计实际使用本金是54660元，月平均实际使用本金是4555元，连初借本金一万元的一半都不到。也就是说，这种贷款方式与银行贷款相比，表面上看你借的钱好像同样多，实际上你能使用的本金却大打折扣，缩水了很多，而利息却没有变。这时候再用还款利息除以初借本金的方法来计算利率就是错误的，这也是这种贷款方式之所以迷惑人的地方。按照通常的情况，这时候，利率计算的正确方法应该是：利息除以实际使用本金，这样，得它的实际利率、也就是被掩盖了的真实利率应是：41.27%（年利息1880元除以实际使用本金4555元），而远非表面上18.8%的利率。

这么高的利率，按照国家的标准，已经够上高利贷了。

用精心包装好的看上去较低（实际上已经不低了）的利率（18.8%）假象将实际上很高的真实利率（41.27%）掩盖起来，以迷惑众人，这就是一些分期还款方式贷款的真相。

分期还款贷款利率的计算不难，就是这种算法。还有认为分期还款方式贷款方便或者便宜的，或者算不清真实利率的，借款前，不妨用上述算法，先合计合计，一定要多掂量掂量，如果用这种方式贷款用于个人消费，那么，你的债台会不会越筑越高，如果用于生产或经营，那么，你的产品的利润率到底有多少。

干货，各种消费贷利率对比

假如需要额度30万元，服务费3000元，年化利率4.35%，授信3年，先息后本，每月还息，一年后还本，那么市面上各种消费贷的利率是多少呢？

鲸算师为大家算一下：

为了简单计算我们一次性提取30万元，3000元的断头息，每月归还利息，一年后归还本金。

这里有一个注意点：3000元断头息我们一次性给了贷款中介；相当于我们贷款了297000元，但是计算每月的归还的利息是30万元。

IRR内部收益率计算真实年化利率

从中介贷款：

内部收益率IRR：0.44，年利率：5.51

银行年化4.68%：

内部收益率IRR：0.39，年利率：4.78

银行年化4.90%：

内部收益率IRR：0.41，年利率：5.01

银行年化5.20%：

内部收益率IRR：0.43，年利率：3.33

利率总结：

年化率4.68%、4.9%、5.2%；一万元每月多还22元、52元；还款总利息分别为-14040元，-14700,-15600。中介还款总共利息-16050

产品对比第一种：良心产品，到期再还本金和利息，或者按月付利息，到期以后再还本金。

第二种：中性产品-消费分期，是不是好产品，主要看手续费率高低。0.4%是常规月利率，最好低于0.4%。

第三种：黑心产品，是千万不能碰的产品。比如各种套路贷，要收取砍头息也叫贷款服务费。

利率未来的钱和今天的钱是不一样的，把今天的钱转换到未来的钱，需要一个利率。比如现在10000元，一年后11000元，这个利率就是10%，利率是未来的钱转换到现在钱的一个贴现率。

利率的计算假设2021年1月24日你借入银行10000元，到2022年1月24日你归还银行11000元，利率=（11000-10000）/10000*100=10%；年化利率为10%；

这里利率为一次性归还本金10000，加上1000元的利息；年华利率为10%。这一年你是每月不用归还利息的。

现在市面上好多贷款产品是每个月都要归还利息的，这样即使利率低，并不是真实的利率。

月利率转化年利率月利率=0.390000%；

年利率=(1+月利率)^12-1=4.781703%

而不是：年利率=月利率*12=4.68%

所有贷款公司收取的利息是复利计算的，这没有悬念，因为如果不是复利，那它就不是贷款公司，而是慈善组织了。用月利率乘以12，等于你借了一个月的钱，但属于借了还还、还了借，一共借了12次，而不是一笔钱借了一年，因此那种算法错了。

净现值NPV净现值NPV(NPV)：就是把未来的钱换算到现在它有多少钱。未来的钱和今天的钱不能作为比较的，就是把未来的钱换算到今天的钱，它还有多少。

内部收益率IRRIRR就是把你的资金流，计算出一个收益率。必须包含支出和收入，然后计算出一个这个资金流的收益率。

IRR就是累计净现值为0的情况下的内部收益率。

IRR公式=IRR(v,[])

=IRR(现金流,[估计值])

估计值默认0.1，是用来保证加计算精度的，可以不写，直接使用默认值。

从估计值开始，IRR不断修正计算结果，直至其精度小于0.00001%。

这里有一个问题，就是公式的小数位一定要足够长，不然四舍五入之后，你的IRR值永远计算不对，因此可以使用内部收益率IRR计算器，进行计算，也比较方便。