我們知道銀行貸款的按揭還貸,主要分為等額本息、等額本金兩種還款方式。其中等額本息是每月還款固定金額,等額本金是逐月遞減(總利息最少)。本文結(jié)合公積金貸款、商業(yè)貸款,等多種復(fù)合條件,分享給大家如何使用身邊的Excel,根據(jù)不同情況,詳細(xì)計算每月貸款還款額度。
使用Excel設(shè)置貸款計算器模板標(biāo)題
1、在Excel中,我們先建一張如下圖模板的表格。
顏色說明:
黃色背景單元格,用來自己填寫數(shù)據(jù)。
綠色背景的單元格,一般也需要自己填好。
橙色背景的單元格,是我們著重關(guān)注的貸款每月還款金額。
其他單元格,一般都是讓公式自動計算生成數(shù)據(jù)?! ?/p>
2、第1列,在單元格A4使用下列公式填寫年月。
=EDATE("2015-11-2",ROW()-3)
其中2015-11-2,需修改為貸款實際開始還款的那個月,當(dāng)中的任意一天。
注意:這里使用公式,而不是直接填寫年份月份,是為了方便往下拉公式,實現(xiàn)自動填充。
3、右擊單元格,設(shè)置單元格格式?! ?/p>
4、 在自定義中,修改為yyyy-m ,這樣的格式?! ?/p>
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)&" 相當(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個單元格,分別往下拉到第2行(復(fù)制公式),這樣按揭貸款計算器的標(biāo)題,即制作完畢?! ?/p>
二、使用公式計算公積金貸款每月還貸金額、本金、利息
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
即可立即得到,每月需要實際準(zhǔn)備的現(xiàn)金,用于還公積金貸款。如果結(jié)果為負(fù)值,說明你的公積金足夠多,根本不需要另外準(zhǔn)備現(xiàn)金或打款到貸款銀行賬戶?! ?/p>
三、使用公式計算商業(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è)貸款,需要每月還款多少?! ?/p>
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)
來計算商貸,每月還貸本金和利息的明細(xì)?!?/p>
3、至此,我們已經(jīng)完成了所有的公式制作,直接選中第4行的相應(yīng)單元格,往下拉公式即可實現(xiàn)快速計算每月貸款還款金額?! ?/p>
四、Excel貸款計算器使用方法與金融數(shù)學(xué)原理
1、最后,我們總結(jié)一下,剛剛使用Excel制作的貸款計算器的具體使用方法與原理。使用方法很簡單,直接按照圖示7個步驟,填好數(shù)據(jù)和公式,最后往下拉即可?! ?/p>
2、為了兼顧需要深究鉆研的朋友,本文最后補充一下等額本息、等額本金,每月還款本金、利息的計算原理與方法。等額本金,顧名思義,就是每月還款中,包含的還款本金都一樣。只不過,由于欠銀行的貸款本金逐月減少,那么相應(yīng)的利息也逐月減少。因此,會出現(xiàn)逐月還款后,所還的金額越來越少的現(xiàn)象(逐月遞減)。
假設(shè)貸款總金額a,年利率P,年限n(共n*12個月,即分為n*12期按揭),
月利率p=P/12
具體等額本金的計算公式(第i個月):
每月還款含本金(都相等):a/(12n)
每月還款含利息(逐月遞減,等差數(shù)列,首項為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個月,即分為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)
將每個月的還款本金(是等比數(shù)列,首項為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個月:
還款利息: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
注意事項:不同時期貸款基準(zhǔn)利率不同,而且折扣不同,需作相應(yīng)設(shè)置修改,公積金賬號因人而異,一般每年會調(diào)整一次每月繳存額。
1.《車貸按揭貸款計算器 Excel如何制作每月的房貸車貸提前還貸計算器》援引自互聯(lián)網(wǎng),旨在傳遞更多網(wǎng)絡(luò)信息知識,僅代表作者本人觀點,與本網(wǎng)站無關(guān),侵刪請聯(lián)系頁腳下方聯(lián)系方式。
2.《車貸按揭貸款計算器 Excel如何制作每月的房貸車貸提前還貸計算器》僅供讀者參考,本網(wǎng)站未對該內(nèi)容進行證實,對其原創(chuàng)性、真實性、完整性、及時性不作任何保證。
3.文章轉(zhuǎn)載時請保留本站內(nèi)容來源地址,http://f99ss.com/keji/456407.html