Excel 信貸試算表教學總覽
信貸試算表是協助個人或企業規劃貸款還款計畫的重要工具，能精確計算每月還款金額、利息支出及本金分攤。無論是房貸、車貸、學貸或一般信貸，善用Excel製作試算表，不僅能提升理財透明度，也有助於預先掌握資金流向。
Excel具備彈性高、公式豐富、易於自訂的優勢，適合需要自主管理或進行多方案比較的用戶。對於專案經理、財務人員或希望提升理財效率的上班族，Excel信貸試算表能快速回應多變的貸款條件與還款需求。
常見應用場景：
– 計算房貸、車貸、學貸或個人信貸每月還款金額
– 規劃提前還款、變動利率或手續費情境
– 與團隊協作討論貸款方案（可結合如Monday.com等專案管理工具進行協作）
Excel信貸試算表的限制：
– 複雜條件（如多段利率、浮動利率）需進階公式或手動調整
– 大型團隊協作時，建議搭配雲端工具或專業管理平台
開始製作信貸試算表前的準備
必備資料與注意事項
在建立信貸試算表前，請先準備以下資料：
| 項目 | 說明 | 格式建議 |
|---|---|---|
| 貸款金額 | 欲申請的總貸款金額 | 數字（如1000000） |
| 年利率 | 銀行提供的年利率 | 百分比（如2%） |
| 還款年數 | 預計還款的總年數 | 整數（如20） |
| 還款方式 | 等額本息或等本攤還 | 文字選擇 |
| 手續費 | 若有，請一併列入 | 數字 |
注意事項：
– 年利率請確認為「年利率」，勿誤用月利率。
– 若利率為浮動，建議先以目前利率試算，並預留備註。
– 手續費、保險費等額外支出可於後續步驟納入計算。
信貸還款方式簡介（等額本息 vs. 等本攤還）
等額本息：每月還款金額固定，前期利息占比高，後期本金占比高。適合預算穩定者。
等本攤還：每月償還固定本金，利息隨餘額遞減，前期還款壓力較大，總利息較低。適合希望加速還款、減少總利息者。
本教學以「等額本息」為範例，因其為台灣最常見的信貸還款方式。
Excel 信貸試算表詳細步驟
輸入基本資料
- 開啟Excel新檔，於A1~A4輸入標籤，B1~B4輸入對應數據：
| A欄 | B欄 |
|---|---|
| 貸款金額 | 1000000 |
| 年利率 | 2% |
| 還款年數 | 20 |
| 手續費 | 5000 |
小技巧：
– 利率可直接輸入「2%」，Excel會自動轉為0.02。
– 若有手續費，建議單獨列出，方便後續加總。
計算每月還款金額（PMT函數教學）
Excel的PMT函數可計算等額本息每月還款金額。公式如下：
=PMT(年利率/12, 還款年數*12, -貸款金額)
以本案例為例，於B5輸入：
=PMT(B2/12, B3*12, -B1)
參數說明：
– 年利率/12：將年利率換算為月利率
– 還款年數*12：總期數（每年12期）
– -貸款金額：貸款金額需為負值，代表現金流出
常見錯誤排查：
– 若出現#VALUE!，請檢查利率格式是否正確（應為百分比或小數）
– 若還款金額為負，代表公式第三參數未加負號
進階應用建議：
– 若有提前還款，可於還款明細表中手動調整餘額
– 變動利率需分段計算，或以加權平均利率試算
建立還款明細表
建立詳細的每期還款計畫，方便追蹤本金、利息、餘額變化。
- 於新工作表或空白區域建立下列欄位：
| A欄 | B欄 | C欄 | D欄 | E欄 |
|---|---|---|---|---|
| 期數 | 期初餘額 | 本期利息 | 本期本金 | 期末餘額 |
-
A2開始輸入1，A3輸入2，選取後拖曳至還款期數（如240期）。
-
B2輸入
=B1（貸款金額），B3之後輸入=E2，向下填充。 -
C2輸入
=B2*($B$2/12)（期初餘額*月利率），向下填充。 -
D2輸入
=$B$5-C2（每月還款-本期利息），向下填充。 -
E2輸入
=B2-D2（期初餘額-本期本金），向下填充。
自動填充技巧：
– 選取公式儲存格右下角拖曳，即可快速套用至所有期數。
– 使用絕對參照（$符號）確保公式正確引用。
視覺化建議：
– 可利用條件格式標示還款進度
– 插入折線圖顯示本金、利息變化趨勢
視覺化與自動化技巧
- 條件格式：標示即將到期或已還款期數，提升可讀性。
- 資料驗證：設置下拉選單，方便切換還款方式或利率。
- 圖表製作：插入折線圖或圓餅圖，直觀呈現本金與利息分布。
- 自動計算總利息、總還款金額：於表格下方加總所有利息、本金欄位。
常見問題與排查
公式錯誤與解決方法
- #VALUE!錯誤：檢查利率格式與公式參數是否正確。
- 還款金額為負數：PMT第三參數需加負號。
- 期數不正確：確認還款年數*12是否為整數。
利率、期數、金額單位常見混淆
- 年利率應以百分比或小數輸入（如2%或0.02）
- 期數為「月數」，即還款年數*12
- 貸款金額勿加千分位符號，避免公式錯誤
如何加入手續費、提前還款等進階需求
- 手續費：可於總還款金額加總，或於首期本金中扣除
- 提前還款：於還款明細表中，於指定期數調整餘額，重新計算後續利息
- 變動利率：分段建立不同利率區間，分別計算每期還款
實際案例演練
以貸款金額100萬元、年利率2%、還款20年為例，完整示範Excel信貸試算表製作：
- 基本資料輸入：
- 貸款金額：1,000,000
- 年利率：2%
-
還款年數：20
-
計算每月還款金額：
- PMT公式：
=PMT(2%/12, 20*12, -1000000) -
結果：約$5,059元
-
建立還款明細表，依上述公式填充240期，觀察每期本金、利息、餘額變化。
-
加入手續費5,000元，總還款金額=（每月還款*240）+5,000
-
製作折線圖，呈現本金、利息隨期數變化的趨勢。
案例洞見：
– 前期利息占比高，後期本金占比逐漸增加
– 提前還款可大幅減少總利息支出
Excel信貸試算表的進階應用與限制
適用情境：
– 個人或小型團隊需自訂、靈活調整貸款條件
– 需快速比較多種貸款方案
不適用情境：
– 多段變動利率、複雜還款結構
– 需多人協作、版本控管
與其他工具比較：
| 工具 | 優點 | 限制 | 適用情境 |
|---|---|---|---|
| Excel | 彈性高、公式豐富、易自訂 | 難多人協作、進階功能需自建 | 個人/小團隊試算、多方案比較 |
| Google Sheets | 雲端協作、即時同步 | 進階功能略少 | 遠距團隊、需即時協作 |
| Monday.com等專案管理平台 | 可整合任務、文件、協作流程，適合大型專案 | 需額外學習、部分功能需付費 | 複雜貸款專案、多部門協作 |
如需進行團隊協作、專案進度追蹤，建議可結合Monday.com等管理平台，提升流程透明度與溝通效率。
總結與推薦
利用Excel製作信貸試算表，不僅能精確掌握每月還款金額、利息與本金分配，也有助於規劃提前還款、評估不同貸款方案。面對複雜專案或多人協作需求時，可考慮搭配如Monday.com等管理工具，進一步提升專案效率與資料整合能力。建議讀者依自身需求選擇最適合的工具，善用Excel的彈性與自動化功能，讓貸款管理更輕鬆。
