——EXCEL2016系列教程之“贷款偿还选择”
-个人购房，购车，企业扩大生产和引进新产线，购买设备等都需要贷款，大家都知道银行提供贷款的方式有等额本息还贷和等额本金还贷两种法，如何根据自己的实际情况选择最佳的还款方式？
贷款方式选择要考虑：还款期限和每月还款的最大金额 。
今天，假设飞云老师要在美丽的成都买一套房子，准备用个人住房公积金贷款50万元，贷款期限10年，每月最高可还款5000元，该选择哪种还款方式呢？贷款利率如图 。
一、介绍两种还贷方式
等额本息还贷法：贷款人自还贷之日起 ， 每个月向银行偿还的贷款数额相同 。其特点是:整个还款期内，每个月的还款额保持不变，贷款人可以准确掌握每月的还款额 。注意的是虽然每个月还贷的数额相同，但其中包含的本金和利息的数额是不同的，这种还贷方法比较适合收入稳定的家庭 。
等额本金还贷法：贷款人每个月偿还的贷款本金的数额是一定的 ， 利息是变化的，呈现逐渐减少的趋势，每月总的还款数额是递减的，虽然每月还款额逐渐减少，但偿还的速度是保持不变的 。这种方式比较适合于有一定的积蓄，在还款初期还款能力较强，并希望在还款初期归还较大款项来减少利息支出的借款人 。
二、制作贷款方式表格
操作步骤：
1.新建“贷款方式选择”工作簿，同时将“sheet1”工资表更名为“最佳返款方式”；
2.在工作表中输入贷款相关数据，并完成两种还贷款的方案表格，如图 。
三、计算每月还贷额
1、等额本息还贷
（1）认识PMT函数
PMT函数:基于固定利率及等额分期付款方式,返回贷款的每期应付款金额 。
PMT函数的语法是: PMT (rate, nper, pv, [fv], [type])
相关参数:
Rate——贷款利率 。
Nper——该项贷款的付款总数 。
Pv——现值，或一未来付款的当前值得累积和 ， 也称为本金 。
Fv——可选参数,未来值或在最后一次付款后希望得到的现金余额，如果省略fv,则假设其值为0 (零)，也就是一笔贷款的未来值为0 。
Type——可选参数 。数字0 (零)或1,用以指示各期的付款时间是在期初，还是期末 。
（2）使用PMT函数，计算等额本息还贷法，每月还贷额
第一步：在等额本息还贷方式中的“每月还贷额”对应的“G5”单元格输入公式“=PMT($C$4/12,$C$5*12,$C$3)”，计算出当月还贷金额，如图 。
特别说明：
- “$C$4/12”将贷款“年利率” ， 折算成为“月利率”；
- “$C$5*12”本项贷款的付款总期数为120期；
- 等额本息还贷法，每月还贷额相等，本例为￥4,885.95元 。
第二步：拖动填充柄，即可计算出贷款10年，每月应还贷款的金额 。
第三步：在“还款总金额”对应的单元格“G125”中输入公式“=SUM(G5:G124)”，按下“回车”即可计算出等额本息还款的总金额 。
2、等额本金还贷
（1）认识月息和日息计算
- 月利息率：年利率除以12 。
- 日利息率：年利率除以360 。
特别说明：
360指的是会计周期的360天，根据月份对应的天数不同，对应的月利息率在30天的月利息率基础上进行利息的加减，即大于30天的加，小于30天的减 。
（2）根据月份，计算每月实际的月利息率
- 判断每月的天数：
第二步：拖动填充柄，完成其他月 ， 当月天数的计算 。如图
特别说明：
- DATE($I$5,J5 1,0)是利用了DATE函数的一个隐藏特性，本实例中I5为 2021年 、J5为1月2 ， 则DATE($I$5,J5 1,0)即为DATE(2021,2,0)，由于并不存在2021/2/0这一天，于是根据这个特性会自动返回到前一天对应的日期，即2012/1/31 。
- DAY(DATE($I$5,J5 1,0))表示该日期所对应的当月天数
- 同样的方法可以，计算2022-2030年对应月份的天数 。
- 计算每月实际的月息：
第一步：在“月息”对应的单元格“L5”中输入公式“=IF(K5=30,$C$10,IF(K5=31,$C$10 $C$11,IF(K5=28,$C$10-C$11*2,$C$10-$C$11)))”，即可计算出2021年1月的“月息”，如图 。
第二步：拖动填充柄，完成2021年其它月份“月息”的计算 。如图
同样的方法可以，完成2022-2030年对应月份的“月息”计算 。
（3）计算每月还贷额
根据银行提供的等额本金还款工商，每月还款额=（贷款金额÷还款总月数） （贷款金额-累计归还本金）×月息 。
第一步：在“M5”单元格输入0，在N5单元格输入公式“=$C$3/($C$5*12) ($C$3-M5)*L5”，按回车键即可得到结果，如图 。
第二步：在M6单元格输入公式“=N5 M5”，按回车键即可得到“累计归还本金” ， 拖动M6单元格的填充柄至单元格M124,即可 。
第三步：拖动M6单元格的填充柄至单元格N124,计算出“每月还贷额”，如图 。
第四步：在K125单元格中输入公式“=SUM(N5:N124)”，按回车键即可得“还款总额”；
在K126单元格中输入公式“=MAX(N5:N124)”，按回车键即可得“月还款最高额“，如图 。
这样就完成了两种还贷方式，还款总额和月还款最高额的计算 。
四、比较选择，适合你的还款方式 。
在J127单元格输入公式“=IF(-G126
结论：通过比较以上的两种结果我们发现 ， 本实例采用等额本息还贷为最佳 。原因是月还贷最高的小于5000，满足还款人最大的月偿还能力 。
今天的知识就是这些，你学会了吗？自己去探索吧！如果你有任何问题 ， 关注我评论留言，
飞云老师 ， 会在第一时间回复你 。
思维决定命运，方法决定效率！
【哪种方式还款最好，看了这个分析就知道了】更多EXCEL操作技巧，将陆续更新，请关注?。?