房贷还款逐月还款明细查询表(可修改)1
- 格式:xls
- 大小:182.00 KB
- 文档页数:21
提前还款日期:2012-9-贷款总额:445000还款总额:916985年利率: 4.90%利息总额:471985应还利息:405224.21贷款期限:360每月还款:2361.73实际利息:192960.100.41%节省利息:212264.11提前还款日期:2012-9-贷款总额:445000还款总额:916985年利率: 4.90%利息总额:471985应还利息:405224.21贷款期限:360每月还款:2361.73实际利息:192960.100.41%节省利息:212264.11提前还款日期:2012-9-贷款总额:445000还款总额:916985年利率: 4.90%利息总额:471985应还利息:405224.21贷款期限:360每月还款:2361.73实际利息:192960.100.41%节省利息:212264.11提前还款日期:2012-9-贷款总额:445000还款总额:916985年利率: 4.90%利息总额:471985应还利息:405224.21贷款期限:360每月还款:2361.73实际利息:192960.100.41%节省利息:212264.11提前还款日期:2012-9-贷款总额:445000还款总额:916985年利率: 4.90%利息总额:471985应还利息:405224.21贷款期限:360每月还款:2361.73实际利息:192960.100.41%节省利息:212264.11提前还款日期:2012-9-贷款总额:445000还款总额:916985年利率: 4.90%利息总额:471985应还利息:405224.21贷款期限:360每月还款:2361.73实际利息:192960.100.41%节省利息:212264.11提前还款日期:2012-9-贷款总额:445000还款总额:916985年利率: 4.90%利息总额:471985应还利息:405224.21贷款期限:360每月还款:2361.73实际利息:192960.100.41%节省利息:212264.11提前还款日期:2012-9-贷款总额:445000还款总额:916985年利率: 4.90%利息总额:471985应还利息:405224.21贷款期限:360每月还款:2361.73实际利息:192960.100.41%节省利息:212264.11提前还款日期:2012-9-贷款总额:445000还款总额:916985年利率: 4.90%利息总额:471985应还利息:405224.21贷款期限:360每月还款:2361.73实际利息:192960.100.41%节省利息:212264.11提前还款日期:2012-9-贷款总额:445000还款总额:916985年利率: 4.90%利息总额:471985应还利息:405224.21贷款期限:360每月还款:2361.73实际利息:192960.100.41%节省利息:212264.1112-9-20元元元12-9-20元元元12-9-20元元元12-9-20元元元12-9-20元元元12-9-20元元元12-9-20元元元12-9-20元元元12-9-20元元元12-9-20元元元提前还款额:100000元应还利息:405224.21元实际利息:317244.68元节省利息:87979.52元提前还款额:100000元应还利息:405224.21元实际利息:317244.68元节省利息:87979.52元提前还款额:100000元应还利息:405224.21元实际利息:317244.68元节省利息:87979.52元提前还款额:100000元应还利息:405224.21元实际利息:317244.68元节省利息:87979.52元提前还款额:100000元应还利息:405224.21元实际利息:317244.68元节省利息:87979.52元提前还款额:100000元应还利息:405224.21元实际利息:317244.68元节省利息:87979.52元提前还款额:100000元应还利息:405224.21元实际利息:317244.68元节省利息:87979.52元提前还款额:100000元应还利息:405224.21元实际利息:317244.68元节省利息:87979.52元提前还款额:100000元应还利息:405224.21元实际利息:317244.68元节省利息:87979.52元提前还款额:100000元应还利息:405224.21元实际利息:317244.68元节省利息:87979.52元。
2019年计算机一级WPS辅导:WPS表格制作还款明细表明明白白还房贷贷款专业称为按揭贷款,即以房产作为抵押的贷款。
每月还款又称为月供。
当前银行多采用等额本息法,利息是递减的。
计算公式复杂,每月还本付息金额 = [ 本金× 月利率×(1+月利率)^贷款月数 ] / [(1+月利率)^还款月数 - 1], 其中:每月利息 = 剩余本金 * 贷款月利率 ,每月本金 = 每月月供额–每月利息。
注意上面的“^”是幂符号,“/”是除以。
因为其中涉及N次方运算,手工无法实现,一般计算器也难以计算。
但是用WPS表格能够轻松实现,因为有专门的函数。
下面介绍是如何实现的1、打开WPS表格,新建一个空白表格,在A1、B1、C1、D1分别输入“期数”、“本金”、“利息”、“月供”。
2、计算月供:在D2中输入“=PMT(0.0051,120,100000)”,拖动填充柄向下复制到D121。
3、计算本金:在A2输入“1”,拖动填充柄向下复制到“120”。
在B2输入公式“=PPMT(0.0051,ROW()-1,120,100000)” ,拖动填充柄向下复制公式到 B121。
4、计算利息:在C2输入公式“=B2-D2”,拖动填充柄向下复制公式到C121。
这样一个详细的还款表就做成了,不但能制作还款表,也能计算年金等投资。
公式讲解:1、函数PMT基于固定利率及等额分期付款方式,返回贷款的每期付款额。
PMT(0.0051,120,100000),0.0051为月利率,120为贷款期限即120月(10年),100000为贷款额。
2、函数PPMT基于固定利率及等额分期付款方式,返回投资在某一给定期间内的本金偿还额。
PPMT(0.0051,ROW()-1,120,100000),0.0051为月利率,ROW()-1为期数,ROW()为行数,借助行数算出期数,120为贷款期限即120月(10年),100000为贷款额。
使用须知
1.黄色表示可输入参数区域,红色表示不同状态下的月供,绿色表示还款计算结果,使用前请在黄色单元格输入相应数据;
2.本表是基于转公积金在前、提前还款在后来计算(即提前还款时间不能早于转公积金贷的时间),否则会出错导致结果不准确;
3.本表提前还款是基于贷款年数不变来计算;
4.如没有转公积贷或每年的利率调整,可将公积金贷利率改成与商业贷款相同的利率或调整后的利率,执行时间相应更改,则结果显示的是提前还款能节省的利息。
5.多次提前还款及多次调整利率计算方式:先把第二次调整利率、提前还款前已还的利息总额、本金金额记录,贷款总额手动输入为原贷款总额-已还本金,期数输入为总期数-已还期数,其它黄色单元格信息相应调整即可,这样可以得到后续多次调整利率或多次提前还款之后每月的月供。
元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框内内容不要修改!中的利率即可,其他部分不要修改。