如何使用Excel公式安排您的貸款還款

貸款還款是償還以前從貸方借來的款項，通常是通過包括本金加利息在內的一系列定期付款。您知道您可以使用軟件程序Excel計算您的還款？

本文是設置貸款計算的分步指南。

關鍵要點：

使用Excel來確定您的每月付款，利率和貸款時間表來處理抵押或貸款。
您可以更深入地了解與Excel的貸款分解，並創建適合您的還款時間表。
每個步驟都可以進行計算，您可以調整以滿足您的特定需求。
分解並逐步檢查您的貸款可以使還款過程變得不那麼壓倒性和更易於管理。

了解您的抵押貸款

使用Excel，您可以更好地了解自己的抵押在三個簡單的步驟中。第一步決定了每月付款。第二步計算了利率，第三步確定了貸款時間表。

您可以在Excel中構建一張表，該表將告訴您利率，貸款期限的貸款計算，貸款的分解，攤銷和每月付款。

步驟1：計算每月付款

首先，這是如何計算抵押的每月付款。使用年利率，本金和持續時間，我們可以確定每月償還的金額。

如上面的屏幕截圖所示，該公式的寫入如下：

= -pmt（速率;長度; present_value; [future_value]; [type]）

由於公式返回負數，因此需要在PMT前面的負符號。前三個論點是貸款利率，貸款的長度（時期數）和本金借用。最後兩個論點是可選的。剩餘值默認為零，並且應提前付款（一個）或末尾（零）也是可選的。

用於計算貸款每月支付的Excel公式是：

= PMT（（1+B2）^（1/12）-1; B4*12; B3）= PMT（（1+3,10％）^（1/12）-1; 10*12; 120000）

就速率而言，我們使用每月的費率（費用周期），然後計算出週期數（120年的120年乘以12個月），最後，我們指出主要的借來的，即$ 120,000。我們的每月付款將在10年內為1,161.88美元。

步驟2：計算年利率

我們已經看到瞭如何設置抵押貸款每月付款的計算。但是，我們可能想設定最高的每月付款，我們可以負擔得起，這也顯示了我們必須償還貸款的年數。因此，我們想知道相應的年度利率。

如上面的屏幕截圖所示，我們首先計算週期率（在我們的情況下），然後計算年度率。所使用的公式將是速率，如上面的屏幕截圖所示。寫如下：

= rate（nper; pmt; prestry_value; [future_value]; [type]）

前三個論點是貸款（時期數），償還貸款的每月付款以及校長借用。最後三個參數是可選的，剩餘值默認為零；提前（一個）或結束時（零）管理成熟度的術語參數也是可選的。最後，估計參數是可選的，但可以對速率進行初始估計。

用於計算貸款率的Excel公式為：

=速率（12*b4; -b2; b3）=速率（12*13; -960; 120000）

請注意，必須給每月付款中的相應數據一個負標誌。這就是為什麼公式之前有一個負標誌的原因。利率週期為0.294％。

我們使用公式=（1 + B5）為12-1 ^ =（1 + 0.294％） ^ 12-1為了獲得我們的貸款年稅率，即3.58％。換句話說，要在13年內借入120,000美元，每月支付$ 960，我們應該以每年3.58％的最高利率進行談判。

快速事實

使用Excel是跟踪您欠下的內容並提出償還時間表的好方法，以最大程度地減少您最終欠的任何費用。

步驟3：確定貸款的長度

現在，我們將看到如何確定您知道年度稅率，委託人借款以及要償還的每月付款時的貸款期限。換句話說，我們需要多長時間償還$ 120,000的抵押貸款，費率為3.10％，每月付款為1,100美元？

如上所述，我們將使用的公式是nper，如下所示，如下所示：

= nper（rate; pmt; Present_value; [future_value]; [type]）

前三個論點是貸款的年度稅率，償還貸款所需的每月付款以及本金借款。最後兩個參數是可選的，剩餘價值默認為零。提前（一個）或末尾（為零）提前支付的參數術語也是可選的。

= nper（（1+b2）^（1/12）-1; -b4; b3）= nper（（1+3,10％）^（1/12）-1; -1100; 120000）

每月付款中的相應數據必須給出負標誌。這就是為什麼我們在公式之前有一個減號的原因。報銷長度為127.97個時期（在我們的情況下）。

我們將使用年數的公式= B5 / 12 = 127.97 / 12完成貸款還款。換句話說，要藉$ 120,000，年率為3.10％，每月支付1,100美元，我們應該償還128個月或10年零8個月的期限。

步驟4：分解貸款

貸款付款由本金和利息組成。利息是在每個時期計算的，例如，10年以上的每月還款將為我們提供120個時期。

上表顯示了使用PPMT和IPMT公式的貸款分解（總計等於120）。兩個公式的論點是相同的，並且被分解為如下：

= -ppmt（rate; num_period; length; printim; princip; [殘留]; [術語]）

除了“ num_period”之外，這些論點與已經看到的PMT公式相同，該論點添加以顯示給定本金和利息的貸款的期間。這是一個例子：

= -ppmt（（1+b2）^（1/12）-1; 1; b4*12; b3）= ppmt（（1+3,10％）^（1/12）-1; 1; 10*12; 120000）

結果顯示在上面的屏幕截圖中，“貸款分解”在分析的時期，即“一個”；也就是說，第一個時期或第一個月。我們支付的$ 1,161.88損失到$ 856.20本金和305.68美元的利息。

還可以計算多個時期的本金和利息償還，例如頭12個月或頭15個月。

= -cumprinc（rate; length; printimal; start_date; end_date; type; type）

我們找到了我們在公式PMT中已經看到的論點，費率，長度，本金和術語（是強制性的）。但是在這裡，我們也需要“ start_date”和“ end_date”參數。 “ start_date”表示要分析的時期的開始，“ end_date”表示要分析的時期的結束。

這是一個例子：

= -cumbrinc（（1+b2）^（1/12）-1; b4*12; b3; 1; 0）

結果顯示在上面的屏幕截圖“ Cumul第一年”中，因此分析的期間從第一個週期的一個（第一個月）到第十二個月（第12個月）的範圍為12。一年來，我們將支付$ 10,419.55的本金和3,522.99美元的利息。

步驟6：貸款攤銷

先前的公式使我們能夠按時間表創建我們的時間表，了解我們每月支付本金和利息的費用，並知道還能支付多少錢。

步驟7：創建貸款時間表

為了創建貸款時間表，我們將使用上面討論的不同公式並在期間數量上擴展它們。

在第一階段列中，輸入“ 1”作為第一個時期，然後將單元格向下拖動。在我們的案例中，我們需要120個時期，因為10年的貸款付款乘以12個月等於120。

第二列是我們每個月需要支付的每月金額 - 在整個貸款時間表中持續不變。要計算數量，請在我們的第一個時期的單元格中插入以下公式：

= -pmt（tp; b4*12; b3）= -pmt（（1+3,10％）^（1/12）-1; 10*12; 120000）

第三列是將每月償還的本金。例如，在第40個時期，我們將以每月總金額為1,161.88美元償還$ 945.51的本金。

為了計算贖回的本金，我們使用以下公式：

= -PPMT（TP; A18; $ B $ 4*12; $ b $ 3）= -PPMT（（1+3,10％）^（1/12）; 1; 10*12; 120000）

第四列是利息，我們使用該公式來計算我們每月金額償還的本金，以發現要支付多少利息：

= -Intper（tp; a18; $ b $ 4*12; $ b $ 3）= -Intper（（1+3.10％）^（1/12）; 1; 10*12; 120000）

第五列包含剩餘的支付金額。例如，在第40款付款之後，我們將不得不以120,000美元的價格支付83,994.69美元。

公式如下：

18; a18; 0）

該公式在包含本金借入的細胞之前使用本金的組合。當我們複製和拖動單元格時，這個時期開始改變。下表顯示，在120個期末，我們的貸款被償還。

如何在Excel中創建貸款還款時間表？

要在Excel中創建攤銷表或貸款償還時間表，您將在第一列中設置一個貸款期，第二列中的每月付款，第三列中的每月本金，第四列中的每月利息，第五列中的金額保留。每列將使用不同的公式來計算在還款期間數量分配的適當量。

Excel每月付款的公式是什麼？

使用Excel中的PMT函數創建公式：PMT（速率，nper，pv，[fv]，[type]）。該公式可讓您在一年中的月數中將年度利率除以12時計算每月付款。

如何在Excel中創建PMT公式？

當您創建一個PMT公式（例如PMT）（Rate，nper，PV，[FV]，[type]）時，您需要幾個數據點。在此公式中，利率是貸款的利率，NPER是付款總數，PV是當前價值或貸款的原理，FV是在還清貸款後的未來價值，類型是付款到期的時間。如括號所示，FV和類型是可選的參數。

底線

償還貸款的過程可能具有挑戰性，尤其是在組織和問責制方面。為此，Excel可以計算和安排您的貸款還款。您可以在Excel中構建一張桌子，以跟踪利率，貸款持續時間的貸款計算，貸款的分解，貸款的攤銷和每月付款。這可能會使償還貸款的過程更加可實現。