住房贷款利率计算(Excel太强大了,居然还能计算房贷月供和利息)
2023-04-26 富美财经 浏览量:次
大家好!今天和大家分享可以用于计算房贷月供、利息的Excel函数。
这些函数分别为:
PMT:计算等额本息还款下月供;
CUMIPMT:计算等额本息还款下支付的总利息;
IPMT:计算等额本息还款下某期支付的利息;
ISPMT:计算等额本金还款方式下某期支付的利息。
在介绍函数之前,我们先来认识这些函数都会使用到的三个参数:
1、rate:利率;
2、pv:present value,现值。即贷款总额。
3、nper:还款期数。例如贷款30年,按月还款,则nper为30*12=360期。
接下来分别介绍等额本息还款、等额本金还款方式下的月供、利息的计算。
1
等额本息
如下图所示,贷款100万,期限30年,利率5.48%。等额本息还款方式下,分别计算月供、支付的总利息、每月支付的本金和利息。
1、计算月供
计算月供使用PMT函数,语法为
PMT(rate,nper,pv,[fv],[type])
rate指贷款利率、nper指贷款总期数、pv指贷款总额。
在B5单元格输入公式:=PMT(B4/12,B3*12,B2)
由于是按月还款,因此公式中的利率为“年利率/12”,将年利率转换为月利率;贷款30年,贷款总期数为“30*12=360期”。
每月还款属于资金流出,因此计算出的月供为负值。可以在公式前添加负号,将其转为正数。
2、计算总支付利息
计算支付的总利息使用CUMIPMT函数,语法为
CUMIPMT(rate,nper,pv,start_period,end_period,type)
在B6单元格输入公式:
=-CUMIPMT(B4/12,B3*12,B2,1,B3*12,0)
前三个参数rate、nper、pv与PMT函数参数含义相同。参数start_period、end_period分别指计算累计利息的开始期数、结束期数。计算30年贷款支付的总利息时,start_period为1,end_period为360。最后一个参数type可以为0或1,用于确定期末支付或期初支付。
3、计算每月支付的利息
想要计算每月还款中,支付的利息有多少,可以使用IPMT函数。语法为IPMT(rate,per,nper,pv,[fv],[type])
参数per指计算第几期支付的利息。
在F2单元格输入公式:
=-IPMT($B$4/12,D2,$B$3*12,$B$2)
从F2单元格计算结果可知,第1个月月供5665元,其中支付利息4567元。
2
等额本金
在等额本金还款方式下,每月支付的本金是固定的,比如贷款100万,期限30年,那么每月支付的本金为 1000000/(30*12)=2778元。每月支付的利息不是固定的,随着支付的本金越来越多,利息逐渐减少。
等额本金还款方式下,可以使用ISPMT函数计算每期支付的利息,语法为ISPMT(rate,per,nper,pv)
如下图所示,在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年,开始低于等额本息还款月供。
-
-
客流量增长“烟火气”回归,大悦城控股北京大区商业如何焕燃城市经济?
消费是社会大生产的起点,也是终点,也是顺应人民对美好生活向往的直接体...
- 房产地产 2024-05-09
-
客流量增长“烟火气”回归,大悦城控股北京大区商业如何焕燃城市经济?
-
-
碧桂园去年交房近70万套领跑行业,今年将重启拿地,在分化市场中寻找确定性
3月30日,碧桂园控股(02007.HK)交出2023年经营成绩单。这一年格外特殊,受多...
- 房产地产 2024-05-09
-
碧桂园去年交房近70万套领跑行业,今年将重启拿地,在分化市场中寻找确定性
-
-
不确定的房地产行情中,藏着哪些确定?
中国商品房历史上,或许没有哪一年如2023年一般曲折,一些开发商倒在了黎...
- 房产地产 2024-05-09
-
不确定的房地产行情中,藏着哪些确定?
-
-
海南:单位生产困难可申请调低公积金缴存比例
3月30日电 “海南省住房公积金管理局”微信号30日消息,海南省住房公积金管...
- 房产地产 2024-05-09
-
海南:单位生产困难可申请调低公积金缴存比例
-
-
报告:3月楼市成交量大涨 南京土地出让揽金近195亿领跑
4月3日电 “中指研究院”微信号3日发布数据显示,3月楼市成交量同环比均大...
- 房产地产 2024-05-09
-
报告:3月楼市成交量大涨 南京土地出让揽金近195亿领跑
-
-
百年朝外焕新颜 引爆北京年轻力创新消费新征程
3月31日,由朝阳文旅集团及u group·年轻力文化消费集团共同主办的“盒启朝外...
- 房产地产 2024-05-09
-
百年朝外焕新颜 引爆北京年轻力创新消费新征程