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年，开始低于等额本息还款月供。
如何计算房贷剩余贷款？分享三种查询方式
相信还房贷的时候大家都有这种感觉，每还一笔钱，就感觉轻松一些，都迫不及待想知道之后还需要还多少钱，期盼着能早点结束房奴还款的日子。本篇小猫来分享一个计算公式，帮助我们快速计算剩余房贷的贷款金额。
一、剩余房贷如何计算？要知道剩余房贷如何计算，首先要看我们选择的是什么还款方式，房贷一般有等额本息和等额本金两种还款方式，我们就分别以这两种方式来计算。
1.等额本息
如果我们选择了等额本息还款方式，那在整个还贷期间，每个月的还贷金额都不会有任何变化的，每个月都是固定的金额。
只不过金额的占比会悄悄发生变化，等额本息前期还的基本上是利息为主，到后期才会慢慢开始还本金，所以这种还款方式很适合提前还款，提前还了本金，利息相应的也会减少。
计算方式也很简单，我们每个月还多少钱，再用这个金额乘以还贷期数即可，但是如果我们提前还款了，还款的金额就会产生变化。
（此处已添加小程序，请到今日头条客户端查看）2.等额本金
等额本金一般是把贷款的本金平摊到每个月，然后再每个月偿还相同金额的本金和其他剩下的贷款在当月产生的利息。
这种还款方式，前期需要还的金额会比较多，但是由于我们每个月都还了本金，所以利息会逐月下降。
剩余房贷计算公式为：月还款额（本金+利息）=（贷款本金÷还款月数）+（贷款本金－已归还本金累计额）×月利率。
按照这个计算公式，我们把自己的房贷信息套进去计算即可。
上面说的两种方式，都是需要自己去计算剩余房贷金额，其实还有其他更省事的办法，跟着小猫来学习新的小技巧吧！
二、剩余房贷如何查询？1.拨打客服电话
我们可以直接拨打贷款行的银行客服电话查询，转人工服务之后，核实我们的信息之后自然会告诉我们剩余的房贷金额。
2.去贷款行网点查询
我们可以带上个人身份证去贷款行网点查询剩余贷款，可能有人会觉得麻烦，但是这么做一个好处，我们可以让银行帮忙打印一年之后的还款计划，我们能很清楚的看到接下来一年，每个月需要还多少钱，多少利息，提前做规划。
3.通过APP查询
我们可以下载贷款行的官方APP，登录我们自己的账号之后，一般都能查询到我们名下的贷款和剩余需要还款的金额，这种方式还能查询到之前的还贷记录，还能看自己有没有逾期，这种方式是最为便捷。
综上所述，如果我们是等额本息还款，计算起来非常简单，用每个月需要还的金额乘剩余期数就行了，如果是的等额本金的话计算起来会稍微麻烦一点，小猫更推荐用APP或者拨打客服电话查询。
本期的分享就到这这里了，还有不懂的记得评论区留言。
Excel函数之：财务函数
一、利息计算
1.计算贷款每年还款额：PMT函数。其语法结构为=PMT（贷款利率，还款期数，本金，最终还款后的余额，还款时间是期初还是期末）【1代表期初，0代表期末】对应的将每个参数代入进去即可。
2.等额本息时，计算利息：IPMT。其语法结构为：=IPMT（利率，计算期数，总期数，本金，最终付款后的本金，还款时间在期初还是期末）【1代表期初，0代表期末】。
3.等额本金时，计算利息：ISPMT。其语法结构为：=ISPMT（贷款利率，计算期数，总期数，贷款金额）
=ISPMT($B$1,D2,$B$2,$B$3)，稍微注意绝对引用的位置，利率，总期限，总金额都是恒定的，选中单元格后按F4锁定，只有计算期数是变动的，。
注释：IPMT计算贷款偿还时，每期本金递增，利息递减。ISPMT计算每期本金不变，利息递减。
二、投资计算
1.计算贷款还清年数：NPER。其语法结构为：=NPER（利率，每期还款额，本金，最终付款后得到的金额，付款时间是期初还是期末）
2.计算实际利率：EFFECT。其语法结构为：=EFFECT（名义利率，复利期数）
三、收益率函数
1.计算内部收益率：IRR。其语法结构为：=IRR（数据组，估算收益值）
如图，现投资1000000元，预计接下来的6年内的获益分别如图所示，计算第二年和第五年的收益率。
2.计算一笔投资的收益率：RATE。其语法结构为=RATE（获益期数，每期获益金额，投资本金，付款时间是期初还是期末，预期利率）【若省略预期利率，则默认为10%】
如图，拿出200000元投资，预计10年内有回报，每年回报50000元，计算收益率。
