2 張
工作表
54 列
資料列
156 個
內建公式
完整內容預覽
📄 工作表
| 房貸試算器 (等額本息) | ||||||
| 貸款金額 (元) | 2000000 | |||||
| 年利率 (%) | 2.5 | |||||
| 貸款年限 (年) | 20 | |||||
| 每月還款金額 (元) | =PMT(B4/1200, B5*12, -B3) | |||||
| 總利息 (元) | =(B7*B5*12)-B3 | |||||
| 總還款金額 (元) | =B7*B5*12 | |||||
| 期數 | 期初餘額 | 每月還款 | 每月利息 | 每月本金 | 期末餘額 | |
| 1 | =B3 | =B7 | =B2*B4/1200 | =C2-D2 | =B2-E2 | |
| 2 | =F2 | =B7 | =B3*B4/1200 | =C3-D3 | =B3-E3 | |
| 3 | =F3 | =B7 | =B4*B4/1200 | =C4-D4 | =B4-E4 | |
| 4 | =F4 | =B7 | =B5*B4/1200 | =C5-D5 | =B5-E5 | |
| 5 | =F5 | =B7 | =B6*B4/1200 | =C6-D6 | =B6-E6 | |
| 6 | =F6 | =B7 | =B7*B4/1200 | =C7-D7 | =B7-E7 | |
| 7 | =F7 | =B7 | =B8*B4/1200 | =C8-D8 | =B8-E8 | |
| 8 | =F8 | =B7 | =B9*B4/1200 | =C9-D9 | =B9-E9 | |
| 9 | =F9 | =B7 | =B10*B4/1200 | =C10-D10 | =B10-E10 | |
| 10 | =F10 | =B7 | =B11*B4/1200 | =C11-D11 | =B11-E11 | |
| 11 | =F11 | =B7 | =B12*B4/1200 | =C12-D12 | =B12-E12 | |
| 12 | =F12 | =B7 | =B13*B4/1200 | =C13-D13 | =B13-E13 | |
| 13 | =F13 | =B7 | =B14*B4/1200 | =C14-D14 | =B14-E14 | |
| 14 | =F14 | =B7 | =B15*B4/1200 | =C15-D15 | =B15-E15 | |
| 15 | =F15 | =B7 | =B16*B4/1200 | =C16-D16 | =B16-E16 |
📄 工作表
| 房貸試算器 (等額本金) | ||||||
| 貸款金額 (元) | 2000000 | |||||
| 年利率 (%) | 2.5 | |||||
| 貸款年限 (年) | 20 | |||||
| 首月還款金額 (元) | =(B3/(B5*12))+(B3*B4/1200) | |||||
| 總利息 (元) | =SUM(D12:D26) | |||||
| 總還款金額 (元) | =B3+B8 | |||||
| 期數 | 期初餘額 | 每月還款 | 每月利息 | 每月本金 | 期末餘額 | |
| 1 | =B3 | =E2+D2 | =B2*B4/1200 | =B3/(B5*12) | =B2-E2 | |
| 2 | =F2 | =E3+D3 | =B3*B4/1200 | =B3/(B5*12) | =B3-E3 | |
| 3 | =F3 | =E4+D4 | =B4*B4/1200 | =B3/(B5*12) | =B4-E4 | |
| 4 | =F4 | =E5+D5 | =B5*B4/1200 | =B3/(B5*12) | =B5-E5 | |
| 5 | =F5 | =E6+D6 | =B6*B4/1200 | =B3/(B5*12) | =B6-E6 | |
| 6 | =F6 | =E7+D7 | =B7*B4/1200 | =B3/(B5*12) | =B7-E7 | |
| 7 | =F7 | =E8+D8 | =B8*B4/1200 | =B3/(B5*12) | =B8-E8 | |
| 8 | =F8 | =E9+D9 | =B9*B4/1200 | =B3/(B5*12) | =B9-E9 | |
| 9 | =F9 | =E10+D10 | =B10*B4/1200 | =B3/(B5*12) | =B10-E10 | |
| 10 | =F10 | =E11+D11 | =B11*B4/1200 | =B3/(B5*12) | =B11-E11 | |
| 11 | =F11 | =E12+D12 | =B12*B4/1200 | =B3/(B5*12) | =B12-E12 | |
| 12 | =F12 | =E13+D13 | =B13*B4/1200 | =B3/(B5*12) | =B13-E13 | |
| 13 | =F13 | =E14+D14 | =B14*B4/1200 | =B3/(B5*12) | =B14-E14 | |
| 14 | =F14 | =E15+D15 | =B15*B4/1200 | =B3/(B5*12) | =B15-E15 | |
| 15 | =F15 | =E16+D16 | =B16*B4/1200 | =B3/(B5*12) | =B16-E16 |
內建公式，自動計算
這份模板已寫好 156 個公式，您只需要填入原始數字，合計、小計與百分比都會自動更新。 線上編輯器會即時運算，下載後在 Excel 或 Google 試算表同樣有效。
- C8
=PMT(B4/1200, B5*12, -B3) - C9
=(B7*B5*12)-B3 - C10
=B7*B5*12 - C13
=B3 - D13
=B7
怎麼使用這份模板
- 1. 線上編輯：點上方「線上編輯」直接在瀏覽器修改，不需安裝任何軟體，公式即時計算。
- 2. 存到我的專案：登入後可把編輯結果存到雲端，下次接著改；也能產生唯讀分享連結給同事或客戶看。
- 3. 下載 .xlsx：需要離線使用時下載，公式與格式完整保留，可用 Excel、Google 試算表、Numbers 開啟。