贷款等额本息还款明细表(自动生成表格)
公积金贷款等额本金和等额本息逐月还款明细表
37
1275.00 2941.67
38
1268.75 2935.42
39
1262.50 2929.17
40
1256.25 2922.92
41
1250.00 2916.67
42
1243.75 2910.42
43
1237.50 2904.17
44
1231.25 2897.92
1
12
13
14
15
16
17
18
78
79
80
81
82
83
7
84
85
86
87
88
89
90
91
92
93
94
95
8
96
97
98
99
100
101
102
103
104
105
106
107
9
108
109
110
111 112 113 114 115 116 117 118 119 10 120 121 122 123 124 125 126 127 128 129 130 131 11 132 133 134 135 136 137 138 139 140 141 142 143
28
1331.25 2997.92
29
1325.00 2991.67
30
1318.75 2985.42
31
1312.50 2979.17
32
1306.25 2972.92
33
1300.00 2966.67
34
1293.75 2960.42
等额本息贷款计算表格
I: 月还款额计算公式 D=AxBx(1+B)^C/[(1+B)^C-1]
还贷期数 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
2/6
A: 贷款总额
（B:元月）利率
C: D:
还款期数 月还款额
（元）
¥210,000.00 0.391667% 240 ¥1,351.34
公积金贷款基本情况
E: 还款总额（元）
¥324,322.03
F: 年利率
4.70%
G: 利息总额（元）
¥114,322.03
H: 利息总额计算公式 G=Ax[BxC-1)x(1+B)^C+1]/[(1+B)^C-1]
当月本金还款额（元） ¥728.68 ¥731.53 ¥734.39 ¥737.27 ¥740.16 ¥743.06 ¥745.97 ¥748.89 ¥751.82 ¥754.77 ¥757.72 ¥760.69 ¥763.67 ¥766.66 ¥769.66 ¥772.68 ¥775.71 ¥778.74 ¥781.79 ¥784.86 ¥787.93 ¥791.02 ¥794.11 ¥797.22 ¥800.35 ¥803.48 ¥806.63 ¥809.79 ¥812.96 ¥816.14 ¥819.34 ¥822.55 ¥825.77 ¥829.01 ¥832.25 ¥835.51 ¥838.78 ¥842.07 ¥845.37 ¥848.68 ¥852.00
I: 月还款额计算公式 D=AxBx(1+B)^C/[(1+B)^C-1]
用Excel制作等额本金和等额本息两种还贷表
例：同事小张为了买新房准备到银行贷款30万元，商定20年还清。
如果用等额本息法，会把贷款总额的本息之和平均分摊到整个还款期，按月等额还款；如果用等额本金法，针将贷款额的本金平均分摊到整个还款期限内每月归还，同时付清上一交易日到本次还款日之间的贷款利息。
到底哪种还款方式对自己更有利一些?先算第一种方案，月还款额的计算在Excel中有专门的函数PMT。
其用法是PMT(月利率,还款期限,贷款总额)，非常简单。
所以，我们只要如图1所示表格的B5单元格中，输入公式“=PMT(B2/12,B3,B4)”，回车后就可以得到按等额本息法计算的每月还款额，其中第一个参数“B2/12”是为了得到月贷款利率。
再在B6单元格输入公式“=B5*B3”，回车就可以轻松得到20年的还款总额了。
用第二种方案稍显麻烦一些。
按照规则，我们可以先将本金300000元分摊到每个月中，再计算每个月应支付的利息，显然，每个月应付利息数值是不同的。
因此，我们可以建立一个如图2所示的表格。
表格直至E244单元格，正好表示240个月。
在E2单元格输入公式“=300000/240”，回车后即可得到每月平均分摊的贷款本金。
在利息计算上，我们可以很容易地推想：每交完一次款后，那么下月付款时就会少交相应部分的利息。
因此，我们在表格的E5单元格输入公式“=(300000-($D5-1)*$E$2)*($B$2/12)”，回车后就可以得到第一次交款时所应付的利息部分。
其中“$B$2/12”仍然是根据年利率计算月利率。
拖动E5单元格的填充句柄向下直至E244单元格填充公式，那么每月应付的利息数就都有了。
我们每个月应付款为每月分摊的本金及应付利息数。
所以，找个空白单元格，输入公式“=SUM(E5:E244)+300000”，回车，就可以得到这20年中我们一共向银行交纳的钱数“519611.25”(真够恐怖的)。
有了这两个数字，哪种付款方案对自己更有利不就一目了然了吗?网友提问：我现在大兴买了一套房,代款30万,分20年付清,月供为2000多一左右,但我不知道这个结果是如何计算出来的,你能把计算公式告诉我吗?王立宏律师回复：等额本息还款法，指贷款期间每月以相等的额度平均偿还贷款本息。
公积金贷款等额本金和等额本息逐月还款明细表
元30年360‰400000元30年 =360月1111.11元月利率0.003825月利率总还款额676165.00总还款额还款年份还款月份每月应还利息每月总还款还款年份还款月份2013.611530.002641.1112013.721525.752636.86 22013.831521.502632.61 341517.252628.36 451513.002624.11 561508.752619.86 671504.502615.61 781500.252611.36 891496.002607.11 9101491.752602.86 10 111487.502598.61 113.825借款金额月 利 率借款期限等额本息较等额本金多付利息：61182.55总还款额只需修改下面蓝框中的本金余额、红框中的年份（或月份）和绿框中的利按20年计算，如果超过20年，可拖动表格最下行；如果不足20年（240月），表下半部出现的删除不看即可。
每月应还本金每月应还本金等额本金还款明细本金余额借款期限等额本息本金余额借款期限4000001121483.252594.36112 131479.002590.1113141474.752585.8614151470.502581.6115161466.252577.3616171462.002573.1117181457.752568.8618191453.502564.6119201449.252560.3620211445.002556.1121221440.752551.8622231436.502547.6123 2241432.252543.36224 251428.002539.1125261423.752534.8626271419.502530.6127281415.252526.3628291411.002522.1129301406.752517.8630311402.502513.6131321398.252509.3632331394.002505.1133341389.752500.8634351385.502496.6135 3361381.252492.36336 371377.002488.1137381372.752483.8638391368.502479.6139401364.252475.3640411360.002471.1141421355.752466.8642431351.502462.6143441347.252458.3644451343.002454.1145461338.752449.8646471334.502445.6147 4481330.252441.36448 491326.002437.1149501321.752432.8650511317.502428.6151521313.252424.3652531309.002420.1153541304.752415.8654551300.502411.6155561296.252407.3656571292.002403.1157581287.752398.8658591283.502394.6159 5601279.252390.36560 611275.002386.1161621270.752381.8662631266.502377.6163641262.252373.3664651258.002369.1165661253.752364.8666671249.502360.6167681245.252356.3668691241.002352.1169701236.752347.8670711232.502343.6171 6721228.252339.36672 731224.002335.1173741219.752330.8674751215.502326.6175761211.252322.3676771207.002318.1177781202.752313.8678791198.502309.6179801194.252305.3680811190.002301.1181821185.752296.8682831181.502292.6183 7841177.252288.36784 851173.002284.1185861168.752279.8686871164.502275.6187881160.252271.3688891156.002267.1189901151.752262.8690911147.502258.6191921143.252254.3692931139.002250.1193941134.752245.8694951130.502241.6195 8961126.252237.36896 971122.002233.1197981117.752228.8698991113.502224.6199 1001109.252220.36100 1011105.002216.11101 1021100.752211.86102 1031096.502207.61103 1041092.252203.36104 1051088.002199.11105 1061083.752194.86106 1071079.502190.61107 91081075.252186.369108 1091071.002182.11109 1101066.752177.861101111062.502173.611111121058.252169.361121131054.002165.111131141049.752160.861141151045.502156.611151161041.252152.361161171037.002148.111171181032.752143.861181191028.502139.61119 101201024.252135.3610120 1211020.002131.111211221015.752126.861221231011.502122.611231241007.252118.361241251003.002114.11125126998.752109.86126127994.502105.61127128990.252101.36128129986.002097.11129130981.752092.86130131977.502088.61131 11132973.252084.3611132 133969.002080.11133134964.752075.86134135960.502071.61135136956.252067.36136137952.002063.11137138947.752058.86138139943.502054.61139140939.252050.36140141935.002046.11141142930.752041.86142143926.502037.6114312144922.252033.3612144 145918.002029.11145146913.752024.86146147909.502020.61147148905.252016.36148149901.002012.11149150896.752007.86150151892.502003.61151152888.251999.36152153884.001995.11153154879.751990.86154155875.501986.61155 13156871.251982.3613156 157867.001978.11157158862.751973.86158159858.501969.61159160854.251965.36160161850.001961.11161162845.751956.86162163841.501952.61163164837.251948.36164165833.001944.11165166828.751939.86166167824.501935.61167 14168820.251931.3614168 169816.001927.11169170811.751922.86170171807.501918.61171172803.251914.36172173799.001910.11173174794.751905.86174175790.501901.61175176786.251897.36176177782.001893.11177178777.751888.86178179773.501884.61179 15180769.251880.3615180 181765.001876.11181182760.751871.86182183756.501867.61183184752.251863.36184185748.001859.11185186743.751854.86186187739.501850.61187188735.251846.36188189731.001842.11189190726.751837.86190191722.501833.61191 16192718.251829.3616192 193714.001825.11193194709.751820.86194195705.501816.61195196701.251812.36196197697.001808.11197198692.751803.86198199688.501799.61199200684.251795.36200201680.001791.11201202675.751786.86202203671.501782.61203 17204667.251778.3617204 205663.001774.11205206658.751769.86206207654.501765.61207208650.251761.36208209646.001757.11209210641.751752.86210211637.501748.61211212633.251744.36212213629.001740.11213214624.751735.86214215620.501731.61215 18216616.251727.3618216 217612.001723.11217218607.751718.86218219603.501714.61219220599.251710.36220221595.001706.11221222590.751701.86222223586.501697.61223224582.251693.36224225578.001689.11225226573.751684.86226227569.501680.61227 19228565.251676.3619228 229561.001672.11229230556.751667.86230231552.501663.61231232548.251659.36232233544.001655.11233234539.751650.86234235535.501646.61235236531.251642.36236237527.001638.11237238522.751633.86238239518.501629.61239511976.6720240514.251625.3620240月总利息=总贷款数×月利率×（还款次数＋1）÷2400000元30年 =360月2048.19元0.003825737347.55元每月应还本金每月应还利息剩余本金总还款额518.191530.00399481.81520.171528.02398961.64522.161526.03398439.48524.161524.03397915.33526.161522.03397389.17528.171520.01396860.99530.191517.99396330.80532.221515.97395798.57534.261513.93395264.32536.301511.89394728.01538.351509.83394189.66框内内容不要修改！中的利率即可，其他部分不要修改。
等额本息贷款计算表格
I: 月还款额计算公式 D=AxBx(1+B)^C/[(1+B)^C-1]
还贷期数 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123
当月利息（元） ¥822.50 ¥820.43 ¥818.35 ¥816.26 ¥814.17 ¥812.06 ¥809.95 ¥807.83 ¥805.70 ¥803.56 ¥801.42 ¥799.26 ¥797.10 ¥794.93 ¥792.75 ¥790.56 ¥788.37 ¥786.16 ¥783.95 ¥781.73 ¥779.50 ¥777.26 ¥775.01 ¥772.75 ¥770.48 ¥768.21 ¥765.93 ¥763.63 ¥761.33 ¥759.02 ¥756.70 ¥754.37 ¥752.03 ¥749.69 ¥747.33 ¥744.96 ¥742.59 ¥740.20 ¥737.81 ¥735.41 ¥732.99
3/6
A: 贷款总额
（B:元月）利率
C: D:
还款期数 月还款额
（元）
¥210,000.00 0.391667% 240 ¥1,351.34
公积金贷款基本情况
E: 还款总额（元）
¥324,322.03
F: 年利率
4.70%
G: 利息总额（元）
¥114,322.03
H: 利息总额计算公式 G=Ax[BxC-1)x(1+B)^C& 月还款额计算公式 D=AxBx(1+B)^C/[(1+B)^C-1]
等额本金和等额本息逐月还款明细表(可修改)
元20年240‰300000元20年 =240月1250.00元月利率0.004083333月利率总还款额447612.49总还款额还款年份还款月份每月应还利息每月总还款还款年份还款月份11225.002475.00121219.902469.90 231214.792464.79 341209.692459.69 451204.582454.58 561199.482449.48 671194.372444.37 781189.272439.27 891184.172434.17 9101179.062429.06 10 111173.962423.96 114.083333借款金额月 利 率借款期限等额本息较等额本金多付利息：23587.21总还款额只需修改下面蓝框中的本金余额、红框中的年份（或月份）和绿框中的利按20年计算，如果超过20年，可拖动表格最下行；如果不足20年（240月），表下半部出现行删除不看即可。
每月应还本金每月应还本金等额本金还款明细本金余额借款期限等额本息本金余额借款期限3000001121168.852418.85112 131163.752413.7513141158.652408.6514151153.542403.5415161148.442398.4416171143.332393.3317181138.232388.2318191133.122383.1219201128.022378.0220211122.922372.9221221117.812367.8122231112.712362.7123 2241107.602357.60224 251102.502352.5025261097.402347.4026271092.292342.2927281087.192337.1928291082.082332.0829301076.982326.9830311071.872321.8731321066.772316.7732331061.672311.6733341056.562306.5634351051.462301.4635 3361046.352296.35336 371041.252291.2537381036.152286.1538391031.042281.0439401025.942275.9440411020.832270.8341421015.732265.7342431010.622260.6243441005.522255.5244451000.422250.424546995.312245.314647990.212240.2147 448985.102235.10448 49980.002230.004950974.902224.905051969.792219.795152964.692214.695253959.582209.585354954.482204.485455949.372199.375556944.272194.275657939.172189.175758934.062184.065859928.962178.9659 560923.852173.85560 61918.752168.756162913.652163.656263908.542158.546364903.442153.446465898.332148.336566893.232143.236667888.122138.126768883.022133.026869877.922127.926970872.812122.817071867.712117.7171 672862.602112.60672 73857.502107.507374852.402102.407475847.292097.297576842.192092.197677837.082087.087778831.982081.987879826.872076.877980821.772071.778081816.672066.678182811.562061.568283806.462056.4683 784801.352051.35784 85796.252046.258586791.152041.158687786.042036.048788780.942030.948889775.832025.838990770.732020.739091765.622015.629192760.522010.529293755.422005.429394750.312000.319495745.211995.2195 896740.101990.10896 97735.001985.009798729.901979.909899724.791974.7999 100719.691969.69100 101714.581964.58101 102709.481959.48102 103704.371954.37103 104699.271949.27104 105694.171944.17105 106689.061939.06106 107683.961933.96107 9108678.851928.859108 109673.751923.75109 110668.651918.65110111663.541913.54111112658.441908.44112113653.331903.33113114648.231898.23114115643.121893.12115116638.021888.02116117632.921882.92117118627.811877.81118119622.711872.71119 10120617.601867.6010120 121612.501862.50121122607.401857.40122123602.291852.29123124597.191847.19124125592.081842.08125126586.981836.98126127581.871831.87127128576.771826.77128129571.671821.67129130566.561816.56130131561.461811.46131 11132556.351806.3511132 133551.251801.25133134546.151796.15134135541.041791.04135136535.941785.94136137530.831780.83137138525.731775.73138139520.621770.62139140515.521765.52140141510.421760.42141142505.311755.31142143500.211750.2114312144495.101745.1012144 145490.001740.00145146484.901734.90146147479.791729.79147148474.691724.69148149469.581719.58149150464.481714.48150151459.371709.37151152454.271704.27152153449.171699.17153154444.061694.06154155438.961688.96155 13156433.851683.8513156 157428.751678.75157158423.651673.65158159418.541668.54159160413.441663.44160161408.331658.33161162403.231653.23162163398.121648.12163164393.021643.02164165387.921637.92165166382.811632.81166167377.711627.71167 14168372.601622.6014168 169367.501617.50169170362.401612.40170171357.291607.29171172352.191602.19172173347.081597.08173174341.981591.98174175336.871586.87175176331.771581.77176177326.671576.67177178321.561571.56178179316.461566.46179 15180311.351561.3515180 181306.251556.25181182301.151551.15182183296.041546.04183184290.941540.94184185285.831535.83185186280.731530.73186187275.621525.62187188270.521520.52188189265.421515.42189190260.311510.31190191255.211505.21191 16192250.101500.1016192 193245.001495.00193194239.901489.90194195234.791484.79195196229.691479.69196197224.581474.58197198219.481469.48198199214.371464.37199200209.271459.27200201204.171454.17201202199.061449.06202203193.961443.96203 17204188.851438.8517204 205183.751433.75205206178.651428.65206207173.541423.54207208168.441418.44208209163.331413.33209210158.231408.23210211153.121403.12211212148.021398.02212213142.921392.92213214137.811387.81214215132.711382.71215 18216127.601377.6018216 217122.501372.50217218117.401367.40218219112.291362.29219220107.191357.19220221102.081352.0822122296.981346.9822222391.871341.8722322486.771336.7722422581.671331.6722522676.561326.5622622771.461321.46227 1922866.351316.3519228 22961.251311.2522923056.151306.1523023151.041301.0423123245.941295.9423223340.831290.8323323435.731285.7323423530.621280.6223523625.521275.5223623720.421270.4223723815.311265.3123823910.211260.21239447612.4920240 5.101255.1020240月总利息=总贷款数×月利率×（还款次数＋1）÷2300000元20年 =240月1963.33元0.004083333471199.70元每月应还本金每月应还利息剩余本金总还款额738.331225.00299261.67741.351221.99298520.32744.371218.96297775.95747.411215.92297028.53750.471212.87296278.07753.531209.80295524.54756.611206.73294767.93759.701203.64294008.23762.801200.53293245.44765.911197.42292479.52769.041194.29291710.48框内内容不要修改！中的利率即可，其他部分不要修改。
等额本息月还款计算表
月还款额 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102
还款期 应还本金 1989 1997 2004 2012 2019 2027 2034 2042 2049 2057 2064 2072 2080 2087 2095 2103 2111 2119 2126 2134 2142 2150
差额
备注
0.00
372231.20
联系电话：###### 4.45% 期限(月)：
所在部门及职务：######### 120 身份证号：#####
还款期 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94
月还款额 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102
联系电话：###### 4.45% 期限(月)：
所在部门及职务：######### 120 身份证号：#####
还款期 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
月还款额 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102 3102