買房貸款 20 年，月供怎么算？等額本息還是等額本金更劃算？用 WPS 表格 (Excel 通用) 建立動態試算模型，輸入貸款額、年限、利率，自動生成 240 期明細賬，直觀對比總利息、月供壓力、提前還款節點收益。本文提供公式詳解、建模步驟、對比分析與模板下載思路。

一、核心公式速查

還款方式	月供計算公式	特點
等額本息	月供 = [本金 × 月利率 × (1+月利率)^期數] / [(1+月利率)^期數 - 1] 月利息 = 剩余本金 × 月利率 月本金 = 月供 - 月利息	月供固定，前期利息佔比高，後期本金佔比高，總利息較高，前期壓力小
等額本金	月供 = (本金/期數) + (本金 - 累計已還本金) × 月利率 月本金 = 總本金/期數 (固定) 月利息 = 剩余本金 × 月利率 (遞減)	月供遞減，本金固定，利息隨本金減少，總利息較低，前期壓力大

二、WPS 表格建模實戰 (以商貸 200 萬、20 年、基準利率 5.94%×0.7=4.158% 為例)

步驟 1：參數區佈局 (第 1-2 行)

單元格	內容/公式	說明
A1	貸款年限(年)	標題
B1	20	輸入值
C1	總期數(月)	=B1*12
D1	年利率	=5.94%*0.7 (或引用儲存格)
E1	月利率	=D1/12
F1	貸款本金	=2000000

步驟 2：等額本息明細表 (第 4 行起)

表頭 (第 4 行)：期數 | 月供 | 月利息 | 月本金 | 剩余本金

第 5 行 (第 1 期初始)：1 | (公式見下) | =F1*$E$1 | =B5-C5 | =F1-D5

關鍵公式 (B5 月供)：

=($F$1*$E$1*(1+$E$1)^($C$1-A5))/((1+$E$1)^($C$1-A5)-1)

※ 使用絕對引用 `$E$1` `$C$1` `$F$1` 鎖定參數，相對引用 `A5` 隨填充遞增。

C5 月利息：`=E5*$E$1` (E5 為上期剩余本金，首期即 F1)

D5 月本金：`=B5-C5`

E5 剩余本金：`=E4-D5` (E4 為上期剩余本金，首期引用 F1)

圖1：等額本息首期公式設置 (注意絕對引用 $)

步驟 3：填充生成全周期明細

1. 選中 A5:E5 區域 → 雙擊右下角填充柄 (或拖動至第 244 行，共 240 期)。
2. 檢查最後一期剩余本金約為 0 (浮點數誤差 ±0.01 正常)。

步驟 4：等額本金明細表 (同工作表右側/新工作表)

表頭：期數 | 月供 | 月本金 | 月利息 | 剩余本金

關鍵公式 (假設從第 5 行開始，參數引用同上)：

• 月本金 (固定)：`=$F$1/$C$1`
• 月利息：`=($F$1-SUM($D$4:D4))*$E$1` (D4 為月本金列標題，SUM 累計已還本金)
• 月供：`=月本金+月利息`
• 剩余本金：`=上期剩余本金-本期月本金`

步驟 5：彙總對比儀表盤

指標	等額本息	等額本金	差異 (本金-本息)
首月月供	≈12,280 元	≈15,265 元	+2,985 元 (本金壓力大)
末月月供	≈12,280 元	≈8,347 元	-3,933 元 (本金輕鬆)
總利息	≈94.7 萬	≈83.2 萬	省 11.5 萬
總還款	≈294.7 萬	≈283.2 萬	省 11.5 萬
適合人群	收入穩定、前期現金流緊、通脹預期強	收入高增長快、前期可承受高壓、想省利息

圖2：等額本息 vs 等額本金 核心指標對比

三、進階功能：讓模型更好用

• 數據驗證下拉選單： 利率類型 (基準/上浮/下浮/LPR)、貸款類型 (商貸/公積金/組合貸)。
• 條件格式高亮： 月供 > 警戒線 (如月收入 50%) 紅色標記；剩余本金 < 10 萬 綠色標記。
• 提前還款模擬： 增加「提前還款金額/日期」輸入區，用 `IF` 判斷重新計算後續剩余本金/利息/月供 (等額本息通常縮短期數或減少月供，等額本金直接減少剩余本金及後續利息)。
• 圖表可視化： 插入「月供走勢圖」(等額本息平線 vs 等額本金下降線)、「剩余本金曲線」、「累計利息佔比餅圖」。
• 命名範圍/表格結構化引用： 將參數區命名為 `LoanParams`，明細區轉為「超級表」(Ctrl+T)，公式語義化如 `=[@月供]-[@月利息]`，維護性極強。

四、避坑指南

• ❌ 利率輸入「5.94%」卻忘記除 12 得月利率 → 月供算錯 12 倍。
• ❌ 公式未加 `$` 絕對引用參數儲存格 → 填充後參數跟著偏移，數據全錯。
• ❌ 等額本金月利息公式用 `剩余本金*月利率` 但剩余本金引用錯誤 (應引用上期剩余本金)。
• ❌ 忽略浮點數誤差，最後一期剩余本金 -0.02 導致總利息對不上 → 最後一期用 `IF(剩余本金<0.5, 剩余本金, 正常計算)` 修正。
• ❌ 不保存模板，每次重新寫公式 → 建立 `.xltx` 模板文件，新建即用。

結語：貸款 20 年，利息可能超過本金。花 10 分鐘在 WPS 表格搭建一個動態試算模型，看清每一筆錢去向，比盲目聽銷售/中介忽悠強百倍。等額本息「穩」，等額本金「省」，沒有標準答案，只有適合你現金流規劃的選擇。下載模板、輸入數據、看圖做決策，理財從算清一筆賬開始。