资源描述:
《用excel编制等额本金与等额本息还款表.doc》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、用excel编制等额本金与等额本息还款表目前,银行的个人按揭贷款的还款方式主要有以下2种方式:等额本金与等额本息。借贷者往往十分纠结于是选用等额本金,还是等额本息实惠,还有提前还款是否要吃亏等问题,下面通过excel计算,一方面给出了两种方式的还款明细表,另一面通过对计算过程的理解,让大家知道其实无论选用哪一种方式都是一样的,无所谓吃亏与否。一、等额本金设借贷金额元,贷款年限5年,贷款期数60期,首次还贷日期2016年1月20日,按如下图所示设计等额本金贷款还款计划书。在单元格A6键入公式:=IF(ROW()-5>$
2、F$3,"",ROW()-5),然后向下引用。在单元格B6键入公式:=H3&"/"&J3&"/"&L3在单元格C6键入年利率百分点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*INT(($J$
3、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<>"",F7+G7,""),然后向下引用。在单元格H7键入公式:=IF(A7<>"",H6-F7-D6,""),然后向下引用。在单元格J7键入公式
4、:=IF(A7<>"",E7+J6+D6,""),然后向下引用。最后,进行一些格式设计,可得完整的等额本金还款计划书如下表所示:表中,黄色单元格内容可根据实际情况按实填写。二、等额本息仍设借贷金额元,贷款年限5年,贷款期数60期,首次还贷日期2016年1月20日,按如下图所示设计等额本金贷款还款计划书。在单元格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在单元格E
5、7键入公式:=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,""),然后向下引用。第6、7两行其它单元格公式都与等额本金时基本相同,不再赘述。其中单元格E7键入的是月还款额计算公式:=[贷款本金×月利率×(1+月利率)^还款月数]÷[(1+月利率)^还款月数-1]公式推导如下:设贷款总额为A,银行月利率为β,总期数为
6、m(个月),月还款额设为X, 则各个月所欠银行贷款为: 第一个月A(1+β)-X] 第二个月[A(1+β)-X](1+β)-X=A(1+β)^2-X[1+(1+β)]第三个月{[A(1+β)-X](1+β)-X}(1+β)-X=A(1+β)^3-X[1+(1+β)+(1+β)^2] … 由此可得第n个月后所欠银行贷款为: A(1+β)^n-X[1+(1+β)+(1+β)^2+…+(1+β)^(n-1)]=A(1+β)^n-X[(1+β)^n-1]/β 由于还款总期数为m,也即第m月刚好还完银行所有贷款,因
7、此有: A(1+β)^m-X[(1+β)^m-1]/β=0 由此求得: X=Aβ(1+β)^m/[(1+β)^m-1]最后,进行一些格式设计,可得完整的等额本息还款计划书如下表所示:三、还款方法的比较等额本金与等额本息在计算利息时的单元格都是G7,如下图所示:利息的计算公式都是=IF(A7<>"",ROUND((H6*C7/100/12),2),""),表示银行利息都是按照借款余多少算利息,所以在利息上根本不存在哪一种好与不好,即使提前还款也一样。有所不同的是:等额本金:本金保持相同,利息逐月递减,月还款额递减
8、。等额本息:本金逐月递增,利息逐月递减,月还款额不变。各人可以根据自己的财务情况作出选择。