在 Excel 中創建貸款攤銷表——逐步教程

在 Excel 中創建貸款攤銷表是一項非常有價值的技能，可以幫助您有效地視覺化和管理貸款還款。攤銷表是一個詳細列出每期付款（通常為房貸或車貸）的表格。它將每次付款分解為利息和本金部分，並顯示每次付款後的剩餘餘額。讓我們深入探討如何在 Excel 中逐步創建這樣的表格。

什麼是攤銷表？

在 Excel 中創建攤銷表

創建適用於可變期數的攤銷表

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

使用 Excel 模板創建攤銷表（含額外付款）

什麼是攤銷表？

攤銷表是一張用於貸款計算的詳細表格，展示了一筆貸款隨時間償還的過程。攤銷表通常用於固定利率貸款，例如房屋抵押貸款、汽車貸款和個人貸款，在這些情況下，支付金額在整個貸款期限內保持不變，但支付中用於利息與本金的比例會隨著時間而變化。

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

• PMT 函數：此函數用於根據固定的支付金額和固定利率來計算每期貸款的總支付金額。
• 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)

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

在計算每次支付的利息和本金後，攤銷表的下一步是計算每次支付後的貸款剩餘餘額。這是表格的重要部分，因為它展示了貸款餘額如何隨時間減少。

1. 在餘額列的第一個單元格 – E7 中，輸入以下公式，表示剩餘餘額將是原始貸款金額減去第一次支付的本金部分：

   =B4-D7

   
2. 對於第二個及所有後續期數，通過從上一期的餘額中減去當前期數的本金支付來計算剩餘餘額。請將以下公式應用於單元格 E8：

   =E7-D8

    注意：餘額單元格的引用應該是相對的，這樣當您向下拖動公式時，它會自動更新。
   
3. 然後向下拖動填充柄到該列。正如您所看到的，每個單元格將自動調整，根據更新的本金支付計算剩餘餘額。
   

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

在設置好詳細的攤銷表後，創建貸款摘要可以提供貸款關鍵方面的快速概覽。此摘要通常包括貸款的總成本、支付的總利息。

● 計算總支付金額：

=SUM(B7:B30)

● 計算總利息：

=SUM(C7:C30)

⭐️ 結果：

現在，一個簡單但全面的貸款攤銷表已成功創建。請參見截圖：

使用 Kutools AI 解鎖 Excel 的魔法

• 智能執行：執行單元格操作、分析數據並創建圖表——所有這些都由簡單的指令驅動。
• 自訂公式：生成量身定制的公式，簡化您的工作流程。
• VBA 編碼：輕鬆編寫和實現 VBA 代碼。
• 公式解釋：輕鬆理解複雜的公式。
• 文本翻譯：打破電子表格中的語言障礙。

通過人工智能工具增強您的 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. 最後，保存您的新攤銷表工作簿。