Excel 房貸試算表:自己做一個專業級計算機
不想用網路上的計算機?這篇教你用 Excel 公式自己做,還能客製化。
作者:Ultra Advisor|分類:工具教學|發布:2026-01-25|最後更新:2026-09-26
網路上房貸計算機很多,但你有沒有想過:
自己用 Excel 做一個,想怎麼改就怎麼改?
這篇教你用 Excel 內建的財務函數,做出專業級的房貸試算表。
學會之後,你可以自己加功能、改參數,比任何線上工具都靈活。
核心公式:PMT 函數
Excel 的 PMT 函數 是房貸試算的核心,用來計算「本息均攤」的每月還款金額。
PMT 函數語法
=PMT(rate, nper, pv)
- • rate:每期利率(年利率 ÷ 12)
- • nper:總期數(年數 × 12)
- • pv:貸款本金(現值)
實際範例
貸款條件:本金 800 萬、年利率 2.1%、30 年期
=PMT(2.1%/12, 30*12, 8000000)
結果:-29,971(負數代表支出)
每月還款約 29,971 元
進階函數:拆解本金與利息
IPMT:計算某期的利息
=IPMT(rate, per, nper, pv)
- • per:第幾期(例如第 1 期、第 120 期)
範例:第 1 期利息
=IPMT(2.1%/12, 1, 360, 8000000) = -14,000 元
PPMT:計算某期的本金
=PPMT(rate, per, nper, pv)
範例:第 1 期本金
=PPMT(2.1%/12, 1, 360, 8000000) = -15,971 元
驗證公式
IPMT + PPMT = PMT
14,000 + 15,971 = 29,971 ✓
製作完整還款明細表
用這些公式,你可以做出逐月的還款明細表:
| 欄位 | 公式(假設本金在 B1、利率在 B2、年數在 B3) |
|---|---|
| 期數 | 1, 2, 3...(自動填充) |
| 月付金 | =PMT($B$2/12, $B$3*12, $B$1) |
| 本期利息 | =IPMT($B$2/12, A5, $B$3*12, $B$1) |
| 本期本金 | =PPMT($B$2/12, A5, $B$3*12, $B$1) |
| 剩餘本金 | =前期剩餘本金 + 本期本金 |
本金均攤怎麼算?
本金均攤不能直接用 PMT,因為每期還款金額不同。
但計算更簡單:
-
每期本金(固定):
= 貸款本金 ÷ 總期數
= 8,000,000 ÷ 360 = 22,222 元 -
每期利息(遞減):
= 剩餘本金 × 月利率 -
每期還款:
= 每期本金 + 每期利息
加入額外還款功能
想看提前還款能省多少利息?在明細表加一欄「額外還款」:
注意:上面明細表的 IPMT、PPMT 都是用原始本金 $B$1 計算,加了額外還款之後,利息欄不會跟著變少,算不出省多少利息。要改成「逐期遞推」:每一期的利息都用上一期實際剩下的本金來算。
逐期遞推版(金額都用正數,月付金維持不變、縮短年限):
- 月付金(固定):
=PMT($B$2/12, $B$3*12, -$B$1) - 本期利息:
= 前期剩餘本金 × $B$2/12(第 1 期的「前期剩餘本金」就是 $B$1) - 本期本金:
= MIN(月付金 - 本期利息, 前期剩餘本金) - 額外還款:自己輸入(沒有就填 0)
- 剩餘本金:
= MAX(前期剩餘本金 - 本期本金 - 額外還款, 0)
把「本期利息」整欄加總,就是總利息。先在額外還款欄全填 0 算一次,再在某一期輸入 50 萬、100 萬算一次,兩次總利息相減,就是提前還款省下的利息。剩餘本金變成 0 的那一期,就是提前還清的時間點。
其他實用財務函數
| 函數 | 用途 |
|---|---|
RATE |
已知月付金,反推利率 |
NPER |
已知月付金,反推還款期數 |
PV |
已知月付金,反推可貸金額 |
FV |
計算未來值(投資試算用) |
為什麼要自己做?
- 1. 完全客製化:想加什麼功能就加什麼
- 2. 離線使用:不用上網也能算
- 3. 資料保密:客戶資料不會上傳到網路
- 4. 專業形象:用自己的試算表,比用別人的工具更專業
小提醒
如果你覺得自己做太麻煩,也可以用 Ultra Advisor 傲創計算機,功能更完整,而且免費。
📚 延伸閱讀
本文為知識整理,不構成貸款或投資建議;實際利率與條件以各銀行公告為準,個案規劃請諮詢專業人士。
常見問題
Excel 算房貸月付金要用哪個函數?
用 PMT 函數,語法是 =PMT(rate, nper, pv):rate 是每期利率(年利率除以12)、nper 是總期數(年數乘以12)、pv 是貸款本金。以本金800萬、年利率2.1%、30年期為例,=PMT(2.1%/12, 30*12, 8000000) 算出 -29,971,代表每月還款約29,971元(負數代表支出)。
怎麼用 Excel 拆出每期的利息和本金?
用 IPMT 算某期利息、PPMT 算某期本金,多一個 per 參數指定第幾期。以800萬、2.1%、360期為例,第1期利息14,000元、本金15,971元,兩者相加正好等於月付金29,971元,可以互相驗證。
本金均攤可以直接用 PMT 函數算嗎?
不行,因為本金均攤每期還款金額不同。但算法更簡單:每期本金固定為貸款本金除以總期數(例如8,000,000÷360=22,222元),每期利息是剩餘本金乘以月利率,每期還款就是兩者相加。
想試算提前還款能省多少利息,Excel 怎麼做?
原本用 IPMT、PPMT 的明細表都以原始本金計算,加了額外還款利息也不會變少,所以要改成逐期遞推:本期利息=前期剩餘本金×月利率,本期本金=月付金-本期利息,剩餘本金=前期剩餘本金-本期本金-額外還款。把利息欄加總,比較有無額外還款的總利息差,就是省下的利息。
已知月付金,可以反推利率或可貸金額嗎?
可以。用 RATE 函數反推利率、NPER 反推還款期數、PV 反推可貸金額,另外 FV 可以計算未來值,適合投資試算用。
為什麼要自己做 Excel 房貸試算表?
四個理由:完全客製化想加什麼功能就加、離線也能算、客戶資料不會上傳到網路、用自己的試算表更專業。如果覺得自己做太麻煩,也可以用站內現成的房貸試算,內建青安、一般、信貸三種模式,而且免費。