Excel 房貸試算表:自己做一個專業級計算機

不想用網路上的計算機?這篇教你用 Excel 公式自己做,還能客製化。

作者:Ultra Advisor|分類:工具教學|發布:2026-01-25|最後更新:2026-09-26

網路上房貸計算機很多,但你有沒有想過:
自己用 Excel 做一個,想怎麼改就怎麼改?

這篇教你用 Excel 內建的財務函數,做出專業級的房貸試算表。
學會之後,你可以自己加功能、改參數,比任何線上工具都靈活。

核心公式:PMT 函數

Excel 的 PMT 函數 是房貸試算的核心,用來計算「本息均攤」的每月還款金額。

PMT 函數語法

=PMT(rate, nper, pv)

實際範例

貸款條件:本金 800 萬、年利率 2.1%、30 年期

=PMT(2.1%/12, 30*12, 8000000)

結果:-29,971(負數代表支出)
每月還款約 29,971 元

進階函數:拆解本金與利息

IPMT:計算某期的利息

=IPMT(rate, per, nper, pv)

範例:第 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,因為每期還款金額不同。
但計算更簡單:

加入額外還款功能

想看提前還款能省多少利息?在明細表加一欄「額外還款」:

注意:上面明細表的 IPMT、PPMT 都是用原始本金 $B$1 計算,加了額外還款之後,利息欄不會跟著變少,算不出省多少利息。要改成「逐期遞推」:每一期的利息都用上一期實際剩下的本金來算。

逐期遞推版(金額都用正數,月付金維持不變、縮短年限):

把「本期利息」整欄加總,就是總利息。先在額外還款欄全填 0 算一次,再在某一期輸入 50 萬、100 萬算一次,兩次總利息相減,就是提前還款省下的利息。剩餘本金變成 0 的那一期,就是提前還清的時間點。

幫客戶算房貸,不一定要先開 Excel

站內房貸試算已內建青安、一般、信貸三種模式,填額度、利率、年限就看到月付金與寬限期落差,公式都幫你拉好了。

免費用現成的房貸試算 →

其他實用財務函數

函數 用途
RATE 已知月付金,反推利率
NPER 已知月付金,反推還款期數
PV 已知月付金,反推可貸金額
FV 計算未來值(投資試算用)

為什麼要自己做?

小提醒

如果你覺得自己做太麻煩,也可以用 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 房貸試算表?

四個理由:完全客製化想加什麼功能就加、離線也能算、客戶資料不會上傳到網路、用自己的試算表更專業。如果覺得自己做太麻煩,也可以用站內現成的房貸試算,內建青安、一般、信貸三種模式,而且免費。