Excel計算房貸中的等額本息，用等額本息公式計算

whoyzz 2019-11-16

展開全文

在 Excel 中，計算房貸中的等額本息可以用PMT函數(shù)，不需用等額本息計算公式，這樣既方便又快速；但計算等額本金沒有直接的函數(shù)，需要用等額本金計算公式一項項計算；下就是它們用 Excel 計算的具體操作方法，操作中所用版本均為 Excel 2016。

一、用Excel計算房貸中的等額本息

假如購房貸款 820000 元，商業(yè)貸款年利率為 4.9%，貸款時間 25 年，還款方式為等額本息；現(xiàn)在要求用 Excel 計算每月應(yīng)該還款數(shù)額和需還總貸款數(shù)額。

（一）用PMT函數(shù)計算

1、計算每月應(yīng)該還款數(shù)額。采用等額本息還款每月還款數(shù)額一樣，所以只需計算一次；雙擊 D3 單元格，把公式 =PMT(B3/12,C3*12,A3) 復制到 D3，按回車，返回 -4,745.98，負號表示需還款。

2、計算需還總貸款數(shù)額。雙擊 E3，輸入公式 =D3*12*C3，按回車，返回 -1,423,795.38；操作過程步驟，如圖1所示：

圖1

3、公式 =PMT(B3/12,C3*12,A3) 說明：

A、Pmt函數(shù)的表達式為 PMT(Rate, Nper, Pv, [Fv], [Type])；Rate 表示貸款月利率，Nper 表示貸款總期數(shù)（月數(shù)），Pv 表示本金；Fv 為可選項，表示未來值，若省略，默認取 0；Type 也為可選項，表示支付時間，0 或省略表示期末，1 表示期初。

B、公式中的 B3/12 用于計算月利率，12 表示一年 12 個月；C3*12 計算總期數(shù)，A3 是本金，公式省略了后兩個參數(shù)。

（二）用等額本息公式計算

1、等額本息計算月還款公式為：（貸款本金×月利率×（1＋月利率）^還款月數(shù)）/（（1＋月利率）^還款月數(shù)－1），把數(shù)值代入此公式變 =(A3*B3/12*(1+B3/12)^(C3*12))/((1+B3/12)^(C3*12)-1)，雙擊 D5 單元格，把公式復制到 D5，按回車，返回 4,745.98，跟用PMT函數(shù)計算結(jié)果一致；確保當前選項卡為“開始”，單擊“常規(guī)”下拉列表框，在彈出的選項中選擇“貨幣”，則數(shù)字前自動加上元符號；再單擊“字體顏色”圖示，選擇“淺藍色”，則文字變?yōu)樗x顏色；操作過程步驟，如圖2所示：

圖2

2、計算需還總貸款方法跟上面一樣，用已計算出的每月還款數(shù)額乘上貸款年數(shù)再乘每年12個月即可。

提示：如果貸款利率變了，例如加息或減息了，只需修改 B3 中數(shù)值，“每月還款和需還總貸款”會自動重新計算返回新值，演示如圖3所示：

圖3

二、用Excel計算房貸中的等額本金

1、計算每月需還本金。采用等額本金還款每月需還本金不變，它的計算公式為：每月需還本金 = 本金/還款月數(shù)，把數(shù)值代入公式變?yōu)?=A3/(C3*12)，雙擊 D3 單元格，把公式復制到 D3，按回車，返回 2,733.33。

2、生成還款期數(shù)（即月數(shù)）。由于貸款 25 年，每年 12 個月，因此還款期數(shù)為 300；選中 B6，輸入 1，單擊一下 A7 退出輸入狀態(tài)，再次選中 A6，按住 Alt 鍵，依次按一次 H、F、I、S 鍵，打開“序列”窗口，選擇左上角的“列”，“終止值”輸入 300，按回車，則生成 1 到 300 的序列；按快捷鍵“Ctrl + 向下方向鍵”定位到表格最后一行，305 行的第 1 列中正是 300。再按快捷鍵“Ctrl + 向上方向鍵”定位到表格第 5 行。

3、計算每月需還利息。由于采用等額本金還款每月需還利息都不一樣，因此每月都需要計算，計算公式為：每月需還利息 =（本金 - 已歸還本金累計額）×月利率，代入數(shù)值變?yōu)?=(A$3-(A6-1)*D$3)*B$3/12，雙擊 B6，把公式復制到 B6，按回車，接著把單元格格式設(shè)置為“貨幣”，則數(shù)值變?yōu)?3,348.33；把鼠標移到 B6 右下角的單元格填充柄上，鼠標變?yōu)榧犹柡?，雙擊左鍵，則 1 到 300 期都計算出所需還利息。

4、計算每月應(yīng)還款。只需把每月應(yīng)還本金和利息加起來，雙擊 C6，把公式 =D$3+B6 復制到 C6，按回車，返回第一個月（期）應(yīng)還款 6,081.67；同樣用雙擊單元格填充柄的方法返回剩余月數(shù)的應(yīng)還款，按“Ctrl + 向下方向鍵”定位到表格最后一行，已經(jīng)有了數(shù)值。

5、計算需還總貸款。計算方法有兩種，一種為把所有“月還款”相加，可用公式 =SUM(C6:C305)；另一種為把每月應(yīng)還本金乘總月數(shù)再加所有月應(yīng)還利息，可用公式 =D3*12*C3+SUM(B3:B305)，這種方法更精確一點；假如用后一種方法，把后一個公式復制到 E3，按回車，返回需還總貸款 1,323,924.22；操作過程步驟，如圖4所示：