用excel编制等额本金与等额本息还款表

1、用excel编制等额本金与等额本 息还款表用excel编制等额本金与等额本息还款表目前，银行的个人按揭贷款的还款方式主要有 以下2种方式：等额本金与等额本息。借贷者往往十分纠结于是选用等额本金，还是 等额本息实惠，还有提前还款是否要吃亏等问题， 下面通过excel计算,一方面给出了两种方式的还 款明细表，另一面通过对计算过程的理解，让大家 知道其实无论选用哪一种方式都是一样的，无所谓 吃亏与否。一、等额本金设借贷金额500000元，贷款年限5年，贷款 期数60期，首次还贷日期2016年1月20日，按 如下图所示设计等额本金贷款还款计划书。C6卜A =RCW(B3>C6/lt>0/1

2、2, 2)hBCDEFG« rJ °；】-12还款计划书(等鼬本金傅用闻明；卡在茁芭白充卸分说上恫宓昭抽出行辛屯耳蕊1贮叶可左掠应危芒卍亏野化晴旳和罕桂前郦幻丑黒隗可在損前呻苓栏萌二龙萤主戶3蓟书主事(元6D0C0Q幷藍年開5SA2 DU年号刘日4启台计3SBK OS启赶签丰爼占计539B05.065則勺逵熬日期还釜金at亡蕊612016 I 20 ' J 2510104.'6833*331770.S3J91666.671010d 16722aiG/2l20 l內100卩4 GSB333 331741 3346J333 3J2017S 31S3lois/a

3、山10C45 148333 331711J147SM0.MJ0223 %在单元格 A6 键入公式：=IF(ROW()-5>$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键入公式：

4、=E6在单元格B7键入公式：=IF(A7<>"",$H$3+INT($J$3-2+A7)/12)&"/"&($ J$3-1+A7)-12*INT($J$3-2+A 7)/12)&"/"& $L$3,"")，然后向下引用。在单元格C7键入公式：=IF(A7<>"",C6,"")， 然后向下引用。在单元格F7键入公式：=IF(A7<>"",H6/($F$3-A6),"")，

5、然后向下引用。在单元格G7键入公式：=IF(A7<>"",ROUND(H6*C7/100/12,2),""),然后向下引用。在单元格 E7 键入公式：=IF(A7<>"",F7+G7,"")，然后向下引用。在单元格H7键入公式：=IF(A7<>"",H6-F7-D6,"")，然后向下引用。在单元格J7键入公式：=IF(A7<>"",E7+J6+D6,"")，然后向下引用。最后，进行一些格式

6、设计，可得完整的等额本 金还款计划书如下表所示：ACUEFH-J £ L 111:34忡弔谥開：sna 细鮭囲还款计划书（等额本金）片邕第克卸劳K上梱应的册Mb览杼年科塞空:钉HA恫垃诫曹飲专交化馬的利率，凄前却匸讦盍盯可3用輛证落拦境上逼畫:£刼 S00WID兹舌运用S阴百左还畫H朗?fl16i 1 H ?（1冃期魏忌荒曰匪反行年利辜播前还惡屯我利冃.黑计辽讓至輙©12016 1'204.2610104.163333.333770.83491666.6710104J6V22016/2/204 25"0074 出H333 331T41 31war

7、 4»t7C8fl:3201&/204 2610M5.138333.331711 30475DOO.OO3022 3.9JA42C1&HJ/204,26W b G28333.3316B2.2940239 561Q520侶加4輛9痂108333J31655 774M333.34M225-6611&2015/204 2599S6.E98333.33162? 2646CUOO.OD帥日2261272C1EW204 2&3927 CS03J 3 331b&3 75-141666 6?F01J9 3413&201 嘶&4 259S97 5

8、6833? 331564 23433333.S4144201他加4药essa.iK03333E34.72424 DM 1063B.9Gd10201b.|J ,14 25SB3S BJB333 331SD6 20416666 6?MT13.431617112016/11/2C4.259301021475.69406333.1139522.611220-16/f 2.204.26叩:g上18333.331446.18400DOU JD119J02 03ISIJ132017H/204 DO5G6& B6aiJ3 u1333 33391666 67128068 651420I7：204 DOs

9、esaa5333.331305.553B3333.242016J i, - c4 DO9611.10B3J3.31277.773F4DOOOOUB2lB6a21221620 Em创4 DO9583 33&33S331250 00366606.67157802.011720l7f5?204 00966& 55K33L331222 2216?3&?.66J31H2017«/204 DO9S27.7T9333 331194 趾3MOOOOD176U&3424192017/7/204 00閃阳加116 663416«6 6716365.3325202

10、017,*0/204 DO9472216313.331133.OS933333 J4牺 5MX54屈212017/204 DO9UJ 44S333 331111.11325000 02D5501 的27222017/10/204 DO94r6.E«O33L331063 33316666.6721471B.653823201 Ml/204 DO««.帥am 33)061 阳3O&33J 142241Q7：53J J303124Z01M2/204 002D040D9361 108JJ33310?/ 77aooDoo.ao233*5B.&l25201W&

11、#187;4 DO9333 33B331331000.00»16«.67442tti 初262010.204 DO292 J. Ed5G19«3fl$.S6B9O47 6244ST2E 5?兑33272018/204 DO2915 07261905期B286128.5744SM2.44282CI讷加4 DO2907 H2619 05獅09838H53451M&57J4292018/204 DO2BS8 412619 OS273 35311W454弭5簡J5302018/204 DO2889 602519 05270 6373571.43457337.65&

12、quot;3&"苛312D16/7/204 002980 952619 05»1 N7a9£2.3346D2I3 61332C18'-®/M4加酋理翌2619 OS2S3.1773333.334C3(n(l J3J33J2019/3/20J的2SSJ493E1I9 05204 447Q714.29465K4 3I39：.'i5*2t)18/-0b'2O4 0028£4 762619 05235 716«095.2i«BB09 07352018/11/20J oo20i5.O32619 0522

13、5.9366476 194；166i 10413C2tl112'20J的2B3TX26119 0521B2S6205? li474492 404237»19/1/203T62B1E.472619 051K4260238： ra477M7 8543J82012/203.?S2HC皿2t19.db愉2457619.K4AD11& IS443920120r art2T99 102C19 05SDO55000 K)432914 25454640201&M/3)3.7627? W2619-35171.875238C.9Sd85R6 174121m血C3.?627S2.Z

14、J2£19.dS1&3L«49761 3148B4印 904742201JMfc/?03 TS7774JG2619 OS15&J5047142.K4»1262.464S 竝4320197/203.752765.372619.05473244皿価舶4020 Q244201203L7627£1TQ2819.05139.13419C 4 re49687 00su45纫他JOr 3T527=0 002619 0(513095JS235.724S9&3S 曲il 辺462019HD/2O3.762741 312611.06122.76366

15、66.G7602273 QU472d19ni；20r 3T527J3 GJ2619 as114 £8U嗣丁 6260501 £43S3482019H2/203 752T?5«2619 OS1Q6J93U2B.67M7737 3754492C20''1/203.762TT7.262619 059B.212朋陽5251C465 13.-5GOStWZ/Mr 3T42709 072619D02飾1帥柏513164 195657512020203.TS2TOD.892619 05I1JM23671.43615K6 38磴2U2Ur4/203.762&

16、;92.712619.05?3.G62OK2 3061BM7 旳>8632G2W5J20F3 752G&1 522E19S47183S3 閃551?4? 3159542020'-5/20r ?.752&75U2619 0557 2916714.29瓏39词64砧2Q2U7/203 7526E9.152C19.0S49.1013CW5 2i52GG96 殍>1562020/8/203 762663 9?2619 0540 921D476.T952和邛6267202020r 3 762K1.732619 0532.737S&J 146Jieyd 

17、3;453狛aacao3 762S43.G02619.06J4.«5233 105345i2 13>45920W1V3#3 75263M2619 0516.362S1> 05637177 54S5602WI2J20”3 762627.232619.05a IB0.00$39ftJd ?7表中，黄色单元格内容可根据实际情况按实填写1、等额本息仍设借贷金额500000元，贷款年限5年，贷款期数60期，首次还贷日期2016年1月20日，按如下图所示设计等额本金贷款还款计划书E6*二出爼6夺=1町侃？*閒/1四0就1+聲1200）飞亦冏+-恥打口+为门叨2飞闕$附：1-也】-“*

18、1曲）/】BCDFGHr：Lxl Im1还款计划书（等额本息）2倩丐诅閃；哂在茁色口充邵兮m二帽应爾苗iff*离丹和*藏优吋可吃椿应伍乏沪丐賈化丙初和寧' 抿前締勿迂載肚可在抿前£；?栏的二龙歳至R3漬報金35（元5000 00£GO苜K赶救F劈?01fi1月20日4启台计45O2S 2S总总g丰息吉计545D25 25期封连尖日飪疋藏利宅枫4生王61XIG'LIO4.»59424.16733'S620B2 5C(JW6J0.3JS42i.l672扣占加*9534?4 嗨7352 032032 131flW &332018/204莊

19、9424. ifi"22 523W1.6420272 48在单元格E6键入公式：=INT(B3*C6/1200*(1+C6/1200f($F$3+1-A6)/ (1+C6/1200F($F$3+1-A6)-1)*100)/100在单元格F6键入公式：=E6-G6在单元格E7键入公式：=IF(A7<>"",INT(H6*C7/1200*(1+C7/1200)A( $F$3+1-A7)/(1+C7/1200)A($F$3+1-A7)-1)*100)/10 0,"")，然后向下引用。在单元格 F7 键入公式：=IF(A7<>&

20、quot;",E7-G7,"")，然后向下引用。第6、7两行其它单元格公式都与等额本金时基本相同，不再赘述 其中单元格E7键入的是月还款额计算公式：=贷款本金X月利率X ( 1+月利率)A还款月 数-(1+月利率)A还款月数一1公式推导如下：设贷款总额为A,银行月利率为B,总期数为m (个月)，月还款额设为X,则各个月所欠银行贷款为：第一个月 A(1+B)-X第二个月A(1+ 3) -X(1+ 3)-X =A(1+3 )A2-X1+(1+ 3 )第三个月A(1+ 3)-X(i+ 3)-X(i+ 3)-X =A(1+ 3 )A3 -X1+(1+ 3 )+(1+ 3

21、)A2由此可得第n个月后所欠银行贷款为:A(1+3 )A*X1+(1+ 3 )+(1+ 3 )A2+ +(1+ 3 呦=A(1+ 3 网(1+ 3 )A-1/ 3由于还款总期数为m，也即第m月刚好还完 银行所有贷款，因此有：A(1+3 )am-X(1+ 3 )am-1/ 3 = 0由此求得：X = A 3 （1+ 3 ）Am/（1+ 3 -im最后，进行一些格式设计，可得完整的等额本息还款计划书如下表所示:ABCDLFGK1 I Ei Lfl1还款计划书（等额本息）2僚用说聊|话在筋邑）6九邵好1S上相程的拽需，血行年刊宝百址酹可江相虑咗 插紅帑（元flKDO刖T年叩5希陟 丁十K£

22、- &ffi玉梢i!Stffl可ti咋代讦君T百卜iT軟W额360R：siTnli1?016 i 1 H ?D B45总希甘4502D25 26期次辽載冃期tt行年创車迁盘主爾试节衣主Ji靳利目累十亡芍壬页$1201671/204 959424.167361 662062.6049?BJa 34BiZ4 IB7220102/204.959424.16却眨0320321348fi24fi.3lI884S.32S32016/3/20 P4.959424.167422.522001/644-7?B3J 阳ZB2T2 4B94ZD1&4?0 r4 9594?4» ieF453

23、 U1371 02470370.653吒轴讯1 11152Dia.p£yM “4.9&W4.1G74S3.S919D.2746ztffiG 碍47120.3D62016. 20 T J.9S9424.167514 761909404Si37Z Z'O5Ez44 ?5137201&.7/20 r4 9$S424 16754S76187B 40447S26.246SMS.123沙祐.'沁“4贴W4.167576 8S1S47>S#(1219.乂T63S1201492016j'5/20 A 9S£42i 16TGOE 141S16 0

24、2W&41 冷ftiaiTM.16to2C IS/1(12(1' r 4.9S542+. 1G7635.52IT34 64425M1704441.=0112016/11/20 f d 9 &9424.167671.031753.134.*?3J0 &7103MS 祐111122016/12/20' r4 952aaodo5424 IGrT02 eb1T21 4B4091527 的113089 92132017/1/20 “4.95W4.16773.46163971j22lU.J014201 /2/Kl "4.9&473128J901.4

25、7S32 Bl19?992 D7J2/UB 36J J?1IS2O17'?7M r 4 954734 2«MV 5781& 71他DM.別331 £32.54IG2017/M 4.鯛4734.28M33L73BM.5Si«U077JJG/16 92221F201 Zj'y2a 4 站4734 28J94? 94FAl 331朋就32M1451 202318?O17.£4 954734 2«3566 25陨0318222.£7ME18MS24192D1 /.7/Kl "4.%473418附i .er17

26、8241350919 76丄3720201778/20 f4 9&4/34 20的聒CHnt 24174242 92355S5A04212017.M f4.鯛4734.29-10155371 a 751?(1Z2； gjtiOSB J2222d 1 /1 ll/2d 4?34.281C32.10A02.1B166194 器366122 &02J2J?3r4 加4734 28J043 73&86 55162146.5M956 胡242G17/1M0 4.鯛4734 28J0GE43663 B51SQN1 13374131.16J02S2D10/V2CI 剧站4734 2S

27、JOB? 20652 O B153993 933T932S 4432X 5013 r 国时4734 28J055&±S3? ?4wq曲q鹑3S4 059722720ia.-2J询4?34.204115 96ma 33US 783 站3SB?94D03S池35册2Qld 4；阳a站4734 2S4133.9G01聒141651 0139352B 28292018304.954734 284M9 97584.31137601 0d30询4734.264W?.O9個1913J3J3 95J0299G 94.3631J0197/20ME4734JS41M 2B650.0C129U9.

28、6?407T31 1237322U 10.20d一站4ru.2tiJ20V6J43274124EW0 134124fi« 40»JS33201620F缭4754M421B3751541120729.264171M 66393420ie*TX'2OFME4734.20423S.2Bm.oc116492 Jfi421933 96403S2010/117204 9&4734 2S4253 跨4SDS3112239J3426663 2i41362010/120F4.5547342H4271 30*吐罰107967.93431442 5242372011/20d耶，4

29、71.2H4288 92UL381M679 014JE1J& BO43382012/?UF小54厲28好曲斜426799372.4344 0971 0B4439加伸3空）F4&E4T34.2A432*37*09.91960dfi.D3嗣站IE汨4540201*20r哪471204342 21K?0790705.8245DU9 544641扣妙5/为F47S4J04360 1?374 16M345.7D455073 9247422C1G/20F4734.2343n 11355.17虧0脚白卽4843出鹑4734 294396 1733L1I17TJ71 «4£

30、4542 4B4944201920F4 SE4T34.23加31993?3l5?.f2朋&2用?&5046201W2UF474.20443L&1JOI.7?ES724 El474011 D4A462019/1W20F丄朋4734 M<450 302354SM273.B14TS746 3252472019H1/2OF4 954734.2344W 16M5.12finO4.G&昴站用G0£4S201% 12/20F4734 2S14S7!2U &95531LH4U213 3 &.449?020fi/aF*5347.234N6 1D22

31、S.13E0B1D.H钩酬1655鮒沁如d.SS4734.20仙一狛2MLE94S2S6 27497882 4疋51202ar3/20F1JS473+294S4J 35190L9341T42.M»2416 7257622020M/2C4 95734.23<562 101721337180.&20058532000/920p出4734 204680 911533732599 加511585 25衍54aewwaaF47.MJ3*599 31134 4728000. tOE16019 56隔20207/20£A眄4T34.2S4616 73H&.idi23381 326513SJ 912020.*8/20F494734 294637 IZ18743 47526063 U<52&7202020F<9E734.29砖66 S777.3114096.60仍阳22 11诉刘御1W如rt 9&474.2946?$ 19£0.10MIO 31SJ5&56 ?0,45920JQ/1VKr血47.