大家好!今天和大家分享可以用于计算房贷月供、利息的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年,开始低于等额本息还款月供。