Excel制作每月的房貸車貸提前還貸計算器圖文說明教程

我們知道銀行貸款的按揭還貸，主要分為等額本息、等額本金兩種還款方式。其中等額本息是每月還款固定金額，等額本金是逐月遞減（總利息最少）。之前，小編分享給大家如何使用Windows計算器簡單速算等額本息每月還款金額，本文結合公積金貸款、商業(yè)貸款，等多種復合條件，分享給大家如何使用身邊的Excel，根據不同情況，詳細計算每月貸款還款額度。

使用Excel設置貸款計算器模板標題

1、在Excel中，我們先建一張如下圖模板的表格。

顏色說明：

黃色背景單元格，用來自己填寫數據。

綠色背景的單元格，一般也需要自己填好。

橙色背景的單元格，是我們著重關注的貸款每月還款金額。

其他單元格，一般都是讓公式自動計算生成數據。

2、第1列，在單元格A4使用下列公式填寫年月。

=EDATE("2015-11-2",ROW()-3)

其中2015-11-2，需修改為貸款實際開始還款的那個月，當中的任意一天。

注意：這里使用公式，而不是直接填寫年份月份，是為了方便往下拉公式，實現自動填充。

3、右擊單元格，設置單元格格式。

4、在自定義中，修改為yyyy-m，這樣的格式。

5、第1行，在單元格D1使用公式

="年限（"&E1*12&"期)"

在單元格H1使用公式

="打折（實際為"&TEXT(G1*I1,"0.00%")&"）"

在單元格L1使用公式

="總利息："&ROUND(C1*IF(K1=1,G1*I1/12*(E1*12+1)/2,G1*I1/12/(1-1/(1+G1*I1/12)^(E1*12))*E1*12-1),2)&" 相當于本金的"&ROUND(IF(K1=1,G1*I1/12*(E1*12+1)/2,G1*I1/12/(1-1/(1+G1*I1/12)^(E1*12))*E1*12-1),2)&"倍"

6、然后將上述3個單元格，分別往下拉到第2行（復制公式），這樣按揭貸款計算器的標題，即制作完畢。

二、使用公式計算公積金貸款每月還貸金額、本金、利息

1、計算公積金逐月還貸，每月需還款的金額：在單元格B4，使用公式

=ROUND(IF($K$1=1,$C$1/$E$1/12+$C$1*(1-((ROW()-4)/$E$1/12))*$G$1/12,$C$1*$G$1*$I$1/12/(1-1/(1+$G$1*$I$1/12)^($E$1*12))),2)

2、為了計算公積金等額本金或者等額本息，每期還款時，貸款本金和貸款利息分別還了多少，我們在單元格C4，輸入公式：

="本金："&ROUND(IF($K$1=1,$C$1/$E$1/12,-($C$1*$G$1*$I$1/12-B4)*(1+$G$1*$I$1/12)^(ROW()-4)),2)&" 利息："&ROUND(IF($K$1=1,$C$1*(1-((ROW()-4)/$E$1/12))*$G$1*$I$1/12,($C$1*$G$1*$I$1/12-B4)*(1+$G$1*$I$1/12)^(ROW()-4)+B4),2)

3、公積金月繳額（單位+個人），是需要自己填寫的，因為因人而異。

注意，需要填寫公積金月繳總金額，包含單位交的和個人交的。

然后在單元格F4，輸入公式

=B4-D4

即可立即得到，每月需要實際準備的現金，用于還公積金貸款。如果結果為負值，說明你的公積金足夠多，根本不需要另外準備現金或打款到貸款銀行賬戶。

三、使用公式計算商業(yè)貸款每月還貸金額、本金、利息

1、類似地，我們在單元格H4使用公式

=ROUND(IF($K$2=1,$C$2/$E$2/12+$C$2*(1-((ROW()-4)/$E$2/12))*$G$2/12,$C$2*$G$2*$I$2/12/(1-1/(1+$G$2*$I$2/12)^($E$2*12))),2)

來計算逐月按揭商業(yè)貸款，需要每月還款多少。

2、然后在單元格J4，輸入公式

="本金："&ROUND(IF($K$2=1,$C$2/$E$2/12,-($C$2*$G$2*$I$2/12-H4)*(1+$G$2*$I$2/12)^(ROW()-4)),2)&" 利息："&ROUND(IF($K$2=1,$C$2*(1-((ROW()-4)/$E$2/12))*$G$2*$I$2/12,($C$2*$G$2*$I$2/12-H4)*(1+$G$2*$I$2/12)^(ROW()-4)+H4),2)

來計算商貸，每月還貸本金和利息的明細。

3、至此，我們已經完成了所有的公式制作，直接選中第4行的相應單元格，往下拉公式即可實現快速計算每月貸款還款金額。

四、Excel貸款計算器使用方法與金融數學原理

1、最后，我們總結一下，剛剛使用Excel制作的貸款計算器的具體使用方法與原理。使用方法很簡單，直接按照圖示7個步驟，填好數據和公式，最后往下拉即可。

2、為了兼顧需要深究鉆研的朋友，本文最后補充一下等額本息、等額本金，每月還款本金、利息的計算原理與方法。等額本金，顧名思義，就是每月還款中，包含的還款本金都一樣。只不過，由于欠銀行的貸款本金逐月減少，那么相應的利息也逐月減少。因此，會出現逐月還款后，所還的金額越來越少的現象（逐月遞減）。

假設貸款總金額a，年利率P，年限n（共n*12個月，即分為n*12期按揭），

月利率p=P/12

具體等額本金的計算公式（第i個月）：

每月還款含本金（都相等）：a/(12n)

每月還款含利息（逐月遞減，等差數列，首項為ap，公差-ap/(12n)）：

ap(1-(i-1)/(12n))

每月還款總額：a/(12n)+ap(1-(i-1)/(12n))

總利息（等差數列求和，），為（ap-（n*12-1）*ap/(n*12)/2）*n*12

=ap(n*12+1)/2

3、等額本息，顧名思義，就是每月還的本金加利息，總和固定。假設貸款總金額a，年利率P，年限n（共n*12個月，即分為12n期按揭），

月利率p=P/12

每月還款總額都為x

具體等額本息的計算公式：

第1個月：

還款利息：ap

還款本金：x-ap

第2個月：

還款利息：(a-(x-ap))p = (ap-x)(1+p) +x

還款本金：x-( (ap-x)(1+p) +x ) = -(ap-x)(1+p)

第3個月：

還款利息：(a-(x-ap)+(ap-x)(1+p))p = (ap-x)(1+p)² +x

還款本金：x-( (ap-x)(1+p)² +x ) = -(ap-x)(1+p)²

以此類推，

第i個月：

還款利息：(ap-x)(1+p)^(i-1) +x

還款本金： -(ap-x)(1+p)^(i-1)

一直到最后一個月（第n*12個月）：

還款利息：(ap-x)(1+p)^(12n-1) +x

還款本金： -(ap-x)(1+p)^(12n-1)

將每個月的還款本金（是等比數列，首項為x-ap，公比為1+p），

相加之和應該等于總本金a，即

（x-ap）(1-(1+p)¹²ⁿ) / (1-(1+p)) = a

則

x=ap(1+1/((1+p)¹²ⁿ - 1))

將x再代入上面的各月的式子，得到：

第1個月：

還款利息：ap

還款本金：ap/((1+p)¹²ⁿ - 1)

第2個月：

還款利息：ap((1+p)¹²ⁿ-(1+p))/((1+p)¹²ⁿ - 1)

還款本金：ap(1+p)/((1+p)¹²ⁿ - 1)

第3個月：

還款利息：ap((1+p)¹²ⁿ-(1+p)²)/((1+p)¹²ⁿ - 1)

還款本金：ap(1+p)²/((1+p)¹²ⁿ - 1)

以此類推，

第i個月：

還款利息：ap((1+p)¹²ⁿ-(1+p)^(i-1))/((1+p)¹²ⁿ - 1)

還款本金： ap(1+p)^(i-1)/((1+p)¹²ⁿ - 1)

總利息，ap(1+p)¹²ⁿ(12n)/((1+p)¹²ⁿ - 1) - [(1-(1+p)¹²ⁿ)/(1-(1+p))] *ap/((1+p)¹²ⁿ - 1)

=ap(1+p)¹²ⁿ(12n)/((1+p)¹²ⁿ - 1)-a

注意事項：不同時期貸款基準利率不同，而且折扣不同，需作相應設置修改，公積金賬號因人而異，一般每年會調整一次每月繳存額。