用excel編制等額本金與等額本息還款表VIP

1、用excel編制等額本金與等額本息還款表目前，銀行的個(gè)人按揭貸款的還款方式主要有以下種方式：等額本金與等額本息。借貸者往往十分糾結(jié)于是選用等額本金，還是等額本息實(shí)惠，還有提前還款是否要吃虧等問題，下面通過excel計(jì)算,一方面給出了兩種方式的還款明細(xì)表，另一面通過對計(jì)算過程的理解，讓大家知道其實(shí)無論選用哪一種方式都是一樣的，無所謂吃虧與否。一、 等額本金設(shè)借貸金額500000元，貸款年限5年，貸款期數(shù)60期，首次還貸日期2016年1月20日，按如下圖所示設(shè)計(jì)等額本金貸款還款計(jì)劃書。在單元格A6鍵入公式：=IF(ROW()-5>$F$3,"",ROW()-5)，然后向下

2、引用。在單元格B6鍵入公式：=H3&"/"&J3&"/"&L3在單元格C6鍵入年利率百分點(diǎn)p=4.25，在單元格F6鍵入公式：=ROUND(B3/F3,2)在單元格G6鍵入公式：=ROUND(B3*C6/100/12,2)在單元格E6鍵入公式：=F6+G，然后向下引用。在單元格H6鍵入公式：=B3-F6在單元格J6鍵入公式：=E6在單元格B7鍵入公式：=IF(A7<>"",$H$3+INT($J$3-2+A7)/12)&"/"&($J$3-1+A7)-12

3、*INT($J$3-2+A7)/12)&"/"&$L$3,"")，然后向下引用。在單元格C7鍵入公式：=IF(A7<>"",C6,"")，然后向下引用。在單元格F7鍵入公式：=IF(A7<>"",H6/($F$3-A6),"")，然后向下引用。在單元格G7鍵入公式：=IF(A7<>"",ROUND(H6*C7/100/12,2),"")，然后向下引用。在單元格E7鍵入公式：=IF(A7

4、<>"",F7+G7,"") ，然后向下引用。在單元格H7鍵入公式：=IF(A7<>"",H6-F7-D6,"") ，然后向下引用。在單元格J7鍵入公式：=IF(A7<>"",E7+J6+D6,"") ，然后向下引用。最后，進(jìn)行一些格式設(shè)計(jì)，可得完整的等額本金還款計(jì)劃書如下表所示：表中，黃色單元格內(nèi)容可根據(jù)實(shí)際情況按實(shí)填寫。二、等額本息仍設(shè)借貸金額500000元，貸款年限5年，貸款期數(shù)60期，首次還貸日期2016年1月20日，按如下圖所示設(shè)

5、計(jì)等額本金貸款還款計(jì)劃書。在單元格E6鍵入公式：=INT(B3*C6/1200*(1+C6/1200)($F$3+1-A6)/(1+C6/1200)($F$3+1-A6)-1)*100)/100在單元格F6鍵入公式：=E6-G6在單元格E7鍵入公式：=IF(A7<>"",INT(H6*C7/1200*(1+C7/1200)($F$3+1-A7)/(1+C7/1200)($F$3+1-A7)-1)*100)/100,"")，然后向下引用。在單元格F7鍵入公式：=IF(A7<>"",E7-G7,"&quo

6、t;)，然后向下引用。第6、7兩行其它單元格公式都與等額本金時(shí)基本相同，不再贅述。其中單元格E7鍵入的是月還款額計(jì)算公式：=貸款本金×月利率×（1+月利率）還款月數(shù)÷（1+月利率）還款月數(shù)1公式推導(dǎo)如下：設(shè)貸款總額為A，銀行月利率為，總期數(shù)為m（個(gè)月），月還款額設(shè)為X， 則各個(gè)月所欠銀行貸款為：第一個(gè)月A(1+)-X第二個(gè)月A(1+)-X(1+)-X = A(1+)2-X1+(1+)第三個(gè)月A(1+)-X(1+)-X(1+)-X = A(1+)3-X1+(1+)+(1+)2由此可得第n個(gè)月后所欠銀行貸款為：A(1+)n-X1+(1+)+(1+)2+(1+)(n-1) = A(1+)n-X(1+)n-1/由于還款總期數(shù)為m，也即第m月剛好還完銀行所有貸款，因此有：A(1+)m-X(1+)m-1/ = 0由此求得：X = A(1+)m/(1+)m-1最后，進(jìn)行一些格式設(shè)計(jì)，可得完整的等額本息還款計(jì)劃書如下表所示：三、 還款方法的比較等額本金與等額本息在計(jì)算利息時(shí)的單元格都是G7，如下圖所示：利息的計(jì)算公式都是=IF(A7<>"",ROUND(H6*C7/100/12),2),"")，表示銀行利息都是按照借款余多少算利