在 Excel 中建立貸款攤銷時間表可能是一項寶貴的技能，它使您能夠有效地視覺化和管理您的貸款還款。攤銷時間表是詳細說明攤銷貸款（通常是抵押貸款或汽車貸款）的每筆定期付款的表格。它將每筆付款分解為利息和本金組成部分，顯示每次付款後的剩餘餘額。讓我們深入了解在 Excel 中建立此類計劃的逐步指南。

什麼是攤銷時間表？

在 Excel 中建立攤銷時間表

創建可變週期數的攤銷計劃

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

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

什麼是攤銷時間表？

攤銷表是貸款計算中使用的詳細表格，顯示隨時間推移還清貸款的過程。攤銷時間表通常用於固定利率貸款，例如抵押貸款、汽車貸款和個人貸款，在整個貸款期限內，還款金額保持不變，但利息與本金的還款比例隨著時間的推移而變化。

事實上，要在 Excel 中建立貸款攤銷表，內建函數 PMT、PPMT 和 IPMT 至關重要。讓我們來了解一下每個函數的作用：

• 光電倍增管功能：此函數用於根據固定付款和固定利率計算貸款每期的總付款額。
• IPMT功能：此函數計算給定期間付款的利息部分。
• PPMT功能：此函數用於計算特定期間內付款的本金部分。

透過使用 Excel 中的這些函數，您可以建立詳細的攤銷時間表，顯示每筆付款的利息和本金組成部分，以及每次付款後貸款的剩餘餘額。

在 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)

⭐️第五步：計算剩餘餘額

計算每次付款的利息和本金後，攤銷計劃的下一步是計算每次付款後貸款的剩餘餘額。這是時間表的關鍵部分，因為它顯示了貸款餘額如何隨著時間的推移而減少。

1. 在餘額欄的第一個儲存格 - E7中，輸入以下公式，這表示剩餘餘額將為原始貸款金額減去第一筆還款的本金部分：

   =B4-D7

   
2. 對於第二期及以後的所有期間，從上期餘額中減去當期本金支付來計算剩餘餘額。請將下列公式套用到儲存格 E8 中：

   =E7-D8

    備註：對平衡單元格的參考應該是相對的，因此當您向下拖曳公式時它會更新。
   
3. 然後將填充柄向下拖曳到該列。正如您所看到的，每個儲存格都會根據更新的本金付款自動調整以計算剩餘餘額。
   

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

設定詳細的攤銷計劃後，建立貸款摘要可以快速概述您的貸款的關鍵方面。該摘要通常包括貸款總成本、支付的總利息。

● 計算總付款：

=SUM(B7:B30)

● 計算總利息：

=SUM(C7:C30)

⭐️ 結果：

現在，一個簡單而全面的貸款攤還時間表已經成功創建。看截圖：

創建可變週期數的攤銷計劃

在前面的範例中，我們為固定還款次數建立了貸款還款計畫。這種方法非常適合處理條款不變的特定貸款或抵押貸款。

但是，如果您希望建立一個靈活的攤銷計劃，可重複用於不同期限的貸款，允許您根據不同貸款情況的需要修改還款次數，則需要遵循更詳細的方法。

⭐️ 第1步：設定貸款資訊和攤銷表

1. 在儲存格中輸入相關貸款信息，例如年利率、貸款期限（年數）、每年還款次數和貸款金額，如下圖所示：
   
2. 然後，在 Excel 中建立攤銷表，並在儲存格 A7:E7 中使用指定的標籤，例如期間、付款、利息、本金、剩餘餘額。
3. 在「期限」欄中，輸入您可能考慮的任何貸款的最高還款次數，例如，填寫 1 到 360 之間的數字。如果您按月還款，這可以涵蓋標準的 30 年期貸款。
   

⭐️第二步：用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), "")

⭐️第三步：調整剩餘餘額公式

對於剩餘餘額，您通常可以從先前的餘額中減去本金。用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. 最後，保存新的攤銷計畫工作簿。