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

使用Excel設(shè)置貸款計(jì)算器模板標(biāo)題

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

顏色說明：

黃色背景單元格，用來自己填寫數(shù)據(jù)。

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

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

其他單元格，一般都是讓公式自動(dòng)計(jì)算生成數(shù)據(jù)。

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

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

其中2015-11-2，需修改為貸款實(shí)際開始還款的那個(gè)月，當(dāng)中的任意一天。

注意：這里使用公式，而不是直接填寫年份月份，是為了方便往下拉公式，實(shí)現(xiàn)自動(dòng)填充。

3、右擊單元格，設(shè)置單元格格式。

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

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

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

在單元格H1使用公式

="打折（實(shí)際為"&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)&" 相當(dāng)于本金的"&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個(gè)單元格，分別往下拉到第2行（復(fù)制公式），這樣按揭貸款計(jì)算器的標(biāo)題，即制作完畢。

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

1、計(jì)算公積金逐月還貸，每月需還款的金額：在單元格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、為了計(jì)算公積金等額本金或者等額本息，每期還款時(shí)，貸款本金和貸款利息分別還了多少，我們?cè)趩卧馛4，輸入公式：

="本金："&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、公積金月繳額（單位+個(gè)人），是需要自己填寫的，因?yàn)橐蛉硕悺?/p>

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

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

=B4-D4

即可立即得到，每月需要實(shí)際準(zhǔn)備的現(xiàn)金，用于還公積金貸款。如果結(jié)果為負(fù)值，說明你的公積金足夠多，根本不需要另外準(zhǔn)備現(xiàn)金或打款到貸款銀行賬戶。

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

1、類似地，我們?cè)趩卧馠4使用公式

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

來計(jì)算逐月按揭商業(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)

來計(jì)算商貸，每月還貸本金和利息的明細(xì)。

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

四、Excel貸款計(jì)算器使用方法與金融數(shù)學(xué)原理

1、最后，我們總結(jié)一下，剛剛使用Excel制作的貸款計(jì)算器的具體使用方法與原理。使用方法很簡(jiǎn)單，直接按照?qǐng)D示7個(gè)步驟，填好數(shù)據(jù)和公式，最后往下拉即可。

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

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

月利率p=P/12

具體等額本金的計(jì)算公式（第i個(gè)月）：

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

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

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

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

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

=ap(n*12+1)/2

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

月利率p=P/12

每月還款總額都為x

具體等額本息的計(jì)算公式：

第1個(gè)月：

還款利息：ap

還款本金：x-ap

第2個(gè)月：

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

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

第3個(gè)月：

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

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

以此類推，

第i個(gè)月：

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

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

一直到最后一個(gè)月（第n*12個(gè)月）：

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

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

將每個(gè)月的還款本金（是等比數(shù)列，首項(xiàng)為x-ap，公比為1+p），

相加之和應(yīng)該等于總本金a，即

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

則

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

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

第1個(gè)月：

還款利息：ap

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

第2個(gè)月：

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

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

第3個(gè)月：

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

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

以此類推，

第i個(gè)月：

還款利息：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

注意事項(xiàng)：不同時(shí)期貸款基準(zhǔn)利率不同，而且折扣不同，需作相應(yīng)設(shè)置修改，公積金賬號(hào)因人而異，一般每年會(huì)調(diào)整一次每月繳存額。

最新評(píng)論