Excel房貸試算教學總覽
房貸試算是什麼？適用情境與Excel優勢
房貸試算是預先計算房屋貸款每月還款金額、利息支出與還款進度的過程。對於購屋族、專案經理或財務規劃人員而言，房貸試算有助於掌握現金流、評估還款壓力，並作為銀行貸款方案比較的依據。
使用Excel進行房貸試算的主要優勢包括：
– 彈性高，可自訂各種參數與情境。
– 透明度佳，所有公式與計算過程皆可檢視與調整。
– 易於製作還款明細表與圖表，便於視覺化分析。
– 適合進行多方案比較與自動化批次試算。
適用情境包含：自助規劃房貸、協助團隊成員評估貸款方案、專案財務預算規劃等。
準備工作與必備資料
在開始前，請確認：
– 已安裝Microsoft Excel（建議使用較新版本以確保函數相容），或可使用Google Sheets（部分函數略有差異）。
– 準備以下資料：
– 貸款金額（單位：元，建議以純數字輸入）
– 貸款年期（單位：年，輸入整數）
– 年利率（建議以百分比格式輸入，例如5%或0.05，並確保格式一致）
常見錯誤提醒：
– 輸入利率時，若以百分比（如5%）輸入，公式需除以100；若以小數（如0.05）輸入，則不需再除以100。
– Google Sheets支援PMT等財務函數，但部分格式與Excel略有不同，請依實際軟體調整。
建立房貸試算表的步驟
步驟一：輸入基本數據
於Excel工作表A欄輸入欄位名稱，B欄輸入對應數值：
| A欄 | B欄 |
|---|---|
| 貸款金額 | 1,000,000 |
| 貸款年期（年） | 20 |
| 年利率 | 5% |
建議將「年利率」儲存格格式設為百分比，避免計算錯誤。
步驟二：計算月利率
於C欄輸入「月利率」，D欄輸入公式：
=B3/12
若年利率格式為百分比，則此公式會自動轉換為月利率（例如5%/12=0.4167%）。若為小數，請確認公式正確。
常見錯誤：
– 忘記將年利率轉為月利率，導致月供金額明顯偏高。
– 利率格式錯誤（百分比與小數混用）。
步驟三：計算總期數
於C欄輸入「總期數」，D欄輸入公式：
=B2*12
此公式將貸款年期轉換為總期數（月數）。提前還款或非整年期數時，請依實際狀況調整。
步驟四：計算月供金額
於A欄輸入「月供金額」，B欄輸入公式：
=PMT(D4, D5, -B1)
PMT函數說明：
– 第一參數（rate）：月利率
– 第二參數（nper）：總期數
– 第三參數（pv）：貸款金額（需加負號，表示現金流出）
不同還款方式比較：
– 本息均攤（等額本息）：每月還款金額相同，適用PMT函數。
– 本金攤還（等額本金）：每月本金固定，利息逐月遞減，需另行設計公式。
步驟五：產生還款明細表
若需詳細掌握每期本金、利息與剩餘餘額，可建立還款明細表。以下為製作步驟：
- 新增表格欄位：期數、期初餘額、本期本金、本期利息、本期還款、期末餘額。
- 期初餘額：第一期為貸款金額，之後為前一期期末餘額。
- 本期利息：期初餘額×月利率。
- 本期本金：月供金額－本期利息。
- 期末餘額：期初餘額－本期本金。
- 依序填入每期數據，直至期末餘額為零。
範例公式：
– A欄：期數（1,2,3,…）
– B欄：期初餘額（第一期為貸款金額，之後為前一期期末餘額）
– C欄：本期利息 = B欄×月利率
– D欄：本期本金 = 月供金額－C欄
– E欄：本期還款 = 月供金額
– F欄：期末餘額 = B欄－D欄
步驟六：視覺化還款進度
利用Excel圖表功能，可將「本金」、「利息」或「剩餘餘額」繪製成折線圖或堆疊圖，直觀呈現還款結構變化。
操作建議：
– 選取還款明細表的「期數」與「期末餘額」欄位，插入折線圖，觀察還款進度。
– 可同時繪製「本金」與「利息」變化，分析每期還款結構。
房貸試算進階應用
寬限期、分段利率、提前還款等特殊情境
- 寬限期：部分貸款初期僅需繳息不還本。可於明細表前數期僅計算利息，本金不變。
- 分段利率：貸款利率於不同期間有不同數值。於明細表依期數調整利率公式。
- 提前還款：若中途償還部分本金，需於當期調整期初餘額，並重新計算後續利息與本金。
產業應用情境舉例：
– 購屋族規劃不同銀行分段利率方案，評估總利息支出差異。
– 專案經理評估提前還款對現金流的影響。
常見錯誤與排查
- 利率格式錯誤：百分比與小數混用，導致月供金額異常。
- 期數計算錯誤：年期與月數未正確轉換。
- PMT參數順序錯誤：導致計算結果不符預期。
- 還款明細表公式錯誤：本金或利息計算未扣除正確期數。
排查建議：
– 檢查所有公式參數來源與格式。
– 逐步驗證每期計算結果，確保餘額最終歸零。
實際案例解析
標準案例：單一方案試算
假設貸款金額為1,000,000元，年期20年，年利率5%。
- 月利率：5%/12=0.4167%
- 總期數：20×12=240
- 月供金額：
=PMT(0.05/12, 240, -1000000)，結果約為6,599元
還款明細表前3期舉例：
| 期數 | 期初餘額 | 本期利息 | 本期本金 | 本期還款 | 期末餘額 |
|---|---|---|---|---|---|
| 1 | 1,000,000 | 4,167 | 2,432 | 6,599 | 997,568 |
| 2 | 997,568 | 4,156 | 2,443 | 6,599 | 995,125 |
| 3 | 995,125 | 4,146 | 2,453 | 6,599 | 992,672 |
不同條件比較
- 貸款金額、年期、利率不同，月供金額與總利息支出差異明顯。
- 以10年與30年期比較，雖然30年期月供較低，但總利息支出大幅增加。
比較表：
| 貸款金額 | 年期 | 年利率 | 月供金額 | 總利息支出 |
|---|---|---|---|---|
| 1,000,000 | 10 | 5% | 10,606 | 272,720 |
| 1,000,000 | 20 | 5% | 6,599 | 583,760 |
| 1,000,000 | 30 | 5% | 5,368 | 932,480 |
FAQ：Excel房貸試算常見問題
PMT公式常見疑問
-
Q：PMT公式為何要加負號？
A：負號代表現金流出（支出），符合Excel財務函數慣例。 -
Q：PMT能否計算本金攤還？
A：PMT適用於本息均攤，若需本金攤還，請自訂公式。
利率格式處理
- Q：年利率應以百分比還是小數輸入？
A：皆可，但公式需對應調整。百分比需除以12，小數直接除以12。
Excel與Google Sheets適用性
- Q：Google Sheets能否使用PMT等函數？
A：可以，語法與Excel相同，但部分進階函數或格式略有差異，請依實際狀況調整。
Excel房貸試算的優缺點與工具推薦
Excel適用情境與限制
優點：
– 彈性高，適合自訂複雜情境。
– 透明度高，便於檢查與調整。
限制：
– 操作需具備一定Excel技能。
– 多方案、大量資料時，手動維護較繁瑣。
– 進階功能（如自動提醒、多人協作）有限。
適合個人或小型團隊進行自助試算，若需多人協作、進階自動化或整合專案管理，建議考慮專業工具。
推薦專業房貸管理工具
若需更高效率與協作性，可考慮如 Monday.com、ClickUp 等專案管理平台。這類工具支援自訂工作流程、提醒、協作與資料整合，適合團隊規劃多案或需與財務、業務部門協同作業時使用。
結論
透過Excel進行房貸試算，不僅能精確掌握每月還款金額，更能彈性調整各種情境，協助你做出更明智的財務決策。若需進一步提升效率與協作，亦可評估專業管理工具，根據自身需求選擇最適合的解決方案，讓房貸規劃更輕鬆、更有保障。
