Excel公式如下：

每月等額本息額 = PMT(5%/12,30*12,600000)=¥-3,220.93

30年累計需要歸還利息 = {SUM(IPMT(5%/12,ROW(A1:A360),360,600000))}=¥-559,534.71

30年累計需要歸還利息計算公式為陣列公式

我們以貸款金額60萬元、貸款期限30年（即360個月）為案例來計算；

開始輸入計算所需資料：

1、“還款期數”：按順序填寫1-360（分別代表1個月—360個月）。

2、“年利率”：題目所設定的5%。

3、“月利率”：將“年利率”/12個月換算成為月利率，換算後“月利率”為0.4167%。

4、“貸款剩餘本金”：填寫我們的貸款金額60萬元。

5、首先來計算“本息合計”，我們先選擇第一期“本息合計”框，然後在Execl選單欄裡找到“插入”選單，並選擇“函式”。

“PMT”函式是指：計算在固定利率下，貸款的等額分期償還額。

1. 開始輸入函式計算所需數字。

1、“Rate”：各期利率，填寫“月利率”（年利率/12）。

2、“Nper”：貸款期，填寫“360”（30年=360個月）。

3、“Pv”：從該項貸款開始計算時已經入賬時款項，填寫“-600000”（負數）。

4、“Fv”、“Type”：未來值和邏輯值，可填寫“0”、“1”或不填寫。

2. 開始計算“歸還利息”。

1、選擇第一期“歸還利息”框。

2、輸入=G3*C3，然後“回車鍵”確定。

3. 開始計算“歸還本金”。

1、選擇第一期“歸還本金”框。

2、輸入=F3-E3，然後“回車鍵”確定。

4. 開始計算“剩餘貸款本金”。

1、第一期剩“剩餘貸款本金”=600000元（最初貸款金額）。

2、第二期“剩餘貸款本金”=第一期“剩餘貸款本金”-第一期“歸還本金”。

3、第三期“剩餘貸款本金”=第二期“剩餘貸款本金”-第二期“歸還本金”。

4、剩餘期數：以此類推······

5. 計算結果：

1、每月貸款月供數為：3220.93元。

2、30年共計歸還本金：600,000.00元。

3、30年共計歸還利息：559,534.71元。

4、30年共計歸還本息：1,159,534.71元。

房貸還款有兩種方式：等額本息和等額本金。

1. 等額本息：

每月還款總金額相等每月貸款利息按月初剩餘貸款本金計算並逐月結清因此，每月還款利息逐月減少，本金逐月增加，維持總金額不變

2. 等額本金：

將本金平均分攤到每個月內同時付清上一交易日至本次還款日之間的利息每月還款總金額逐月遞減

下圖可以非常直觀地理解兩種還款方式的區別：

理解了房貸還款方式以後，我們以一個實際案例來學習製作房貸計算模板。

案例：

就以此為例，計算等額本息和等額本金的每月還款金額。

操作步驟：

1. 先設計出空的模板，如下：

2 至 5 行中的藍色背景區域為手工輸入區，根據實際情況填寫第 10 行開始是計算區域，綠色區域按等額本息計算，黃色區域按等額本金計算

2. 填寫手工輸入區域：

房屋售價：1800萬首付比例：70%，二套房首付門檻貸款利率：5.88%，基準利率貸款年限：20年

3. 設定圖中 A 至 D 列資料，公式/方法如下：

房屋售價：=B2首付金額：=B2*B3貸款金額：=A10-B10還款月數：1 至 240 填充，填充方式可以參見 Excel 99%的人忽略了的“填充”功能

4. 等額本息公式設定：

Excel 計算等額本息還款額非常簡單，因為有 3 個專用函式：

月還款額＝PMT(各期利率,總期數,本金,,）本金＝PPMT(各期利率,第幾期,總期數,本金）利息＝IPMT(各期利率,第幾期,總期數,本金）

具體應用到本例，公式如下：

1) 每月還款總額：

=-PMT($B$4/12,$B$5*12,$C$10*10000,,)

公式釋義：

$B$4/12：5.88% 是年化利率，需要除以 12，得到每月的利率$B$5*12：同理，20 年乘以 12 才能得出需要還款的總月數，即總期數$C$10*10000：貸款金額，單位是“萬元”，乘以 10000 換算成“元”,,：最後 2 個可選引數可以不填，所以兩個“,” 後面留空PMT 函式是用於計算扣款金額的，所以預設結果為負數，本例中，我們不需要負數表示，所以在公式前面新增“-”，轉換成正數

2) 每月本金金額：

=-PPMT($B$4/12,$D10,$B$5*12,$C$10*10000)

釋義同上，不多解釋

3) 每月利息金額：

=-IPMT($B$4/12,$D10,$B$5*12,$C$10*10000)

4) 把 F10:H10 的公式下拉，並且計算“實際總付款金額”：

=A10+F10*20*12/10000

公式釋義：

A10 為首付金額F10*20*12/10000：因為每月還款總金額相等，所以總還款金額為每月基數 * 還款期數，最後再除以 10000 將單位換算為“萬元”

5. 等額本金公式設定：

等額本金沒有專門函式，所以需要用公式來計算：

1) 每月本金金額：

=($C$10*10000)/($B$5*12)

公式釋義：

($C$10*10000)：貸款金額，單位換算為“元”/($B$5*12)：除以總還款期數

2) 每月利息金額：

=($C$10*10000-($D10-1)*K10)*$B$4/12

公式釋義：

($C$10*10000-($D10-1)*K10)：貸款金額，減去還清的本金總數，得到剩餘應還本金*$B$4/12：乘以貸款利率，年化換算為月，即得到當月應還利息

3) 每月還款總額：

=SUM(K10:L10)

4) 把 J10:L10 的公式下拉，並且計算“實際總付款金額”：

=A10+SUM(J10:J249)/10000

公式釋義：

A10 為首付金額SUM(J10:J249)/10000：每月還款金額加總，除以 10000 將單位換算為“萬元”