excel如何計算貸款每期應還利息「怎樣計算房貸月供公式」

大家好！今天和大家分享可以用于計算房貸月供、利息的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年，開始低于等額本息還款月供。