在 Excel 中建立貸款攤銷表──逐步教學

在 Excel 中建立貸款分期攤還表是一項極具價值的技能，能幫助您清晰掌握並有效管理貸款還款進度。分期攤還表會詳細列出每期應付金額（常見於房貸或車貸等分期貸款），並將每筆付款明細拆解為利息與本金兩部分，同時即時顯示每次付款後的剩餘貸款餘額。接下來，我們將一步步引導您在 Excel 中輕鬆打造專屬的分期攤還表。

什麼是攤銷表？

在 Excel 中建立攤銷表

為可變期數建立攤銷表

建立包含額外付款的攤銷表

使用 Excel 範本建立攤銷表（含額外付款）

什麼是攤銷表？

分期攤還表是一種詳細呈現貸款償還過程的計算表格，適用於房貸、車貸與個人貸款等固定利率貸款。此類貸款在整個還款期間每月付款金額固定，但每期付款中利息與本金的比例會隨時間逐漸變化。

要在 Excel 中建立貸款分期攤還表，確實必須仰賴內建的 PMT、PPMT 與 IPMT 函數。讓我們來了解這些函數各自的作用：

• PMT 函數：此函數可根據固定利率與每期固定付款金額，精準計算貸款的每期應付總金額。
• IPMT 函數：用於計算特定期間付款中的利息部分。
• PPMT 函數：此函數用於計算特定期間付款中所包含的本金部分。

透過在 Excel 中運用這些函數，您能輕鬆建立一份詳盡的分期攤還表，清晰呈現每筆付款中利息與本金的組成，以及每次付款後的貸款剩餘餘額。

在 Excel 中建立攤銷表

本節將介紹兩種在 Excel 中建立分期攤還表的實用方法，無論您是初學者或進階使用者，都能依個人偏好與技能水平，輕鬆打造詳盡且精準的貸款分期攤還表。

運用公式能深入掌握底層的計算邏輯，並根據自身需求靈活自訂表格。此方法特別適合喜歡親自動手操作、清楚了解每筆付款如何拆分為本金與利息的使用者。現在，就讓我們一步步說明如何在 Excel 中建立分期攤還表：

⭐️ 步驟 1：設定貸款資訊與分期攤還表

1. 輸入相關貸款資訊，例如年利率、貸款年限、每年付款次數與貸款金額，如以下截圖所示：
   
2. 接著，在 Excel 中建立分期攤還表，並在儲存格 A7:E7 設定以下標籤：期間、付款、利息、本金與剩餘餘額。
3. 在「期間」欄位中輸入期數編號。以本範例為例，總付款期數為 24 個月（2 年），因此您需在「期間」欄位中依序輸入 1 到 24 的數字。請參閱截圖：
   
4. 設定好表格標籤與期間編號後，即可依據貸款細節，在「付款」、「利息」、「本金」及「餘額」欄位中輸入相應的公式與數值。

⭐️ 步驟 2：使用 PMT 函數計算總付款金額

PMT 的語法如下：

在儲存格 B7 中輸入下列公式，然後向下拖曳填滿控點，將公式套用至其他儲存格，即可看到所有期間的付款金額均維持不變。請參閱截圖：

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ 步驟 3：使用 IPMT 函數計算利息

在此步驟中，您將運用 Excel 的 IPMT 函數，精準計算每期付款所含的利息金額。

在儲存格 C7 中輸入下列公式，然後向下拖曳填滿控點至該欄，即可取得每期的利息。

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ 步驟 4：使用 PPMT 函數計算本金

計算每期利息後，建立分期攤還表的下一步便是計算每筆付款中的本金部分。這可透過 PPMT 函數輕鬆完成——該函數專為根據固定付款金額與固定利率，精準計算特定期間內付款所含本金而設計。

IPMT 的語法如下：

PPMT 公式的語法與參數，與先前介紹的 IPMT 公式完全一致。

在儲存格 D7 中輸入下列公式，然後向下拖曳填滿控點至該欄，即可自動填入每期的本金。請參閱截圖：

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ 步驟 5：計算剩餘餘額

計算每筆付款的利息與本金後，分期攤還表的下一步便是計算每次付款後的貸款剩餘餘額。這一步驟至關重要，能清晰呈現貸款餘額隨時間逐步遞減的情形。

1. 在餘額欄位的第一個儲存格（E7）中，輸入下列公式，表示剩餘餘額等於原始貸款金額減去第一期付款的本金部分：

   =B4-D7

   
2. 針對第二期及後續所有期間，請透過將當前期間的本金付款從上一期餘額中扣除，來計算剩餘餘額。請在儲存格 E8 中套用下列公式：

   =E7-D8

   注意：餘額儲存格的參照應設為相對參照，以便向下拖曳公式時能自動更新。
   
3. 接著向下拖曳填滿控點至該欄。如您所見，每個儲存格將根據更新後的本金付款自動調整剩餘餘額。
   

⭐️ 步驟 6：製作貸款摘要

建立詳細的分期攤還表後，製作貸款摘要即可快速掌握貸款關鍵資訊，通常包含貸款總成本與所支付的總利息。

● 計算總付款金額：

=SUM(B7:B30)

● 計算總利息：

=SUM(C7:C30)

⭐️ 成果：

現在，您已成功建立一份簡潔完整的貸款分期攤還表。請參閱下方截圖：

透過 KUTOOLS AI 解鎖 Excel 的神奇功能

• 智慧執行：只需簡單指令，即可執行儲存格操作、分析資料並建立圖表！
• 自訂公式：打造專屬公式，輕鬆簡化您的工作流程！
• VBA 編碼：輕鬆撰寫並執行 VBA 程式碼！
• 公式解析：輕鬆掌握複雜公式！
• 文字翻譯：輕鬆打破試算表中的語言障礙！

運用 AI 驅動工具強化您的 Excel 能力。立即下載體驗前所未有的高效！

為可變期數建立攤銷表

在前述範例中，我們為固定付款期數建立了貸款還款表，此方法非常適合處理條款不變的特定貸款或房貸。

但若您希望建立一份具彈性的攤銷表，能反覆適用於不同期限的貸款，並根據各種貸款情境靈活調整付款次數，則需採用更詳盡的方法。

⭐️ 步驟 1：設定貸款資訊與攤銷表

1. 輸入相關貸款資訊，例如年利率、貸款年限、每年付款次數與貸款金額，如以下截圖所示：
   
2. 接著，在 Excel 中建立分期攤還表，並在儲存格 A7:E7 設定以下標籤：期間、付款、利息、本金與剩餘餘額。
3. 在「期間」欄位中，輸入您可能考慮的任何貸款之最高付款期數（例如 1 至 360）。若按月付款，此範圍可涵蓋標準的 30 年貸款。
   

⭐️ 步驟 2：使用 IF 函數修改付款、利息與本金公式

在對應儲存格中輸入下列公式，然後拖曳填滿控點，將公式向下複製至您設定的最大付款期數。

● 付款公式：

通常您會使用 PMT 函數來計算付款金額。若要加入 IF 判斷式，其語法公式如下：

因此公式如下：

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● 利息公式：

其語法公式如下：

因此公式如下：

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● 本金公式：

其語法公式如下：

因此公式如下：

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ 步驟 3：調整剩餘餘額公式

關於剩餘餘額，您通常會從上一期餘額中扣除本金；加入 IF 判斷式後，即可調整此計算方式：

● 第一個餘額儲存格：（E7）

=B4-D7

● 第二個餘額儲存格：（E8）

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ 步驟 4：製作貸款摘要

在您使用修改後的公式完成攤銷表設定後，下一步就是建立貸款摘要。

● 計算總付款金額：

=SUM(B7:B366)

● 計算總利息：

=SUM(C7:C366)

⭐️ 成果：

現在，您已在 Excel 中建立了一份完整且動態的攤銷表，並附上詳盡的貸款摘要。每當您調整付款期數，整張攤銷表便會自動更新，即時反映所有變更。請參閱下方示範：

建立包含額外付款的攤銷表

透過支付超出原定金額的額外款項，可加速償清貸款。在 Excel 中建立包含額外付款的攤銷表，能清楚呈現這些額外付款如何加快還款進度並降低總利息支出。以下是設定方法：

⭐️ 步驟 1：設定貸款資訊與攤銷表

1. 輸入相關貸款資訊，例如年利率、貸款年限、每年付款次數、貸款金額與額外付款，如以下截圖所示：
   
2. 接著，計算預定付款金額。
   除了變數儲存格外，後續計算還需要另一個預先定義的儲存格——「預定付款金額」，也就是假設未進行額外付款時的貸款常規付款金額。請在儲存格 B6 中套用下列公式：

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. 接著，在 Excel 中建立分期攤還表：
   • 在儲存格 A8:G8 中設定指定標籤，例如期數、排定付款、額外付款、總付款、利息、本金、剩餘餘額；
   • 在「期數」欄位中，輸入您針對任何貸款可能考慮的最高付款次數。例如，可填入 0 到 360 之間的數字；若為每月付款，此設定即可涵蓋標準的 30 年貸款。
   • 針對第 0 期（在我們的例子中為第 9 列），請使用此公式 =B4 取得對應原始貸款金額的餘額數值，此列中的其他儲存格應留空。
   • 

⭐️ 步驟 2：建立含額外付款的攤銷表公式

請逐一在對應儲存格中輸入下列公式。為提升錯誤處理能力，我們已將此公式及所有後續公式包覆於 IFERROR 函數中，有效避免因變數儲存格留空或填入無效數值而可能引發的各類錯誤。

● 計算預定付款金額：

在 B10 儲存格中輸入下列公式：

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● 計算額外付款金額：

在 C10 儲存格中輸入下列公式：

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● 計算總付款金額：

在 D10 儲存格中輸入下列公式：

=IFERROR(B10+C10, "")

● 計算本金金額：

在 E10 儲存格中輸入下列公式：

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● 計算利息金額：

在 F10 儲存格中輸入下列公式：

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● 計算剩餘餘額

在 G10 儲存格中輸入下列公式：

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

完成所有公式後，請選取儲存格範圍 B10:G10，並利用填滿控點向下拖曳，將公式延伸至整個付款期數範圍。未使用的期數對應的儲存格將顯示為 0. 請參閱截圖：

⭐️ 步驟 3：製作貸款摘要

● 取得預定付款次數：

=B2:B3

● 取得實際付款次數：

=COUNTIF(D10:D369,">"&0)

● 取得總額外付款金額：

=SUM(C10:C369)

● 取得總利息金額：

=SUM(F10:F369)

⭐️ 成果：

依照上述步驟，您就能在 Excel 中輕鬆建立一份納入額外付款的動態攤銷表。

使用 Excel 範本建立攤銷表

在 Excel 中使用範本建立攤銷表，是一種既簡單又省時的絕佳方式。Excel 提供內建範本，能自動計算每期付款所包含的利息、本金及剩餘餘額。以下是運用 Excel 範本建立攤銷表的步驟：

1. 按一下檔案 > 新增，在搜尋方塊中輸入分期攤還表，然後按下 Enter 鍵。接著，點選最符合您需求的範本即可輕鬆套用！例如，這裡我選擇了「簡易貸款計算機」範本。請參閱截圖：
   
2. 選取範本後，按一下建立按鈕，即可將其開啟為新工作簿。
3. 接著輸入您的貸款詳細資料，範本將根據您的輸入自動計算並填入分期攤還表。
4. 最後，別忘了儲存您全新的分期攤銷表活頁簿！