当前位置: 主页 > 我评百科

excel怎么计算房贷利息(Excel太强大了)

  • 我评百科
  • 2022-09-23
  • 佚名

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

  • 关注微信

猜你喜欢

微信公众号