——EXCEL2016系列教程“贷款还款选择”
个人买房买车，企业扩大生产引进新生产线，购置设备都需要贷款。大家都知道银行提供贷款有两种方式：等额本息还款和平均资金还款。如何根据自己的实际情况选择最佳还款方式？
贷款方式的选择要考虑：还款期限和每月最高还款额。
今天假设非云想在美丽的成都买房，他准备从个人住房公积金贷50万，贷款期限10年，每月最高还款额5000元。他应该选择哪种还款方式？贷款利率如图所示。
一、介绍两种还贷方式
等额本息还贷法：贷款人从还款日起每月向银行偿还相同数额的贷款。其特点是在：的整个还款期内，每月还款额保持不变，贷款人能准确掌握每月还款额。注意，虽然每个月还款金额相同，但包含的本金和利息金额是不同的。这种还款方式更适合收入稳定的家庭。
等额本金还贷法：贷款人每月偿还的贷款本金金额是确定的，利息是变化的，呈下降趋势，每月还款总额是下降的。虽然每月还款额在逐渐减少，但还款速度保持不变。这种方式更适合有一定储蓄、还款初期还款能力强、想在还款初期偿还一大笔钱以减少利息支出的借款人。
二、制作贷款方式表格
操作步骤：
1.新建“贷款方式选择”工作簿，将“sheet1”工资表名称改为“最佳还款方式”；
2.在工作表中输入贷款相关数据，完成两个贷款还款方案，如图。
三、计算每月还贷额
1、等额本息还贷
（1）认识PMT函数
PMT函数：根据固定利率和等额分期付款方式，返回每期贷款的应付金额。
PMT函数的语法是： PMT(速率、nper、PV、[Fv]、[类型])
相关参数：
利率——贷款利率。
这笔贷款的总付款额。
Pv——现值，或未来付款的当前价值的累计总和，也称为本金。
Fv——可选参数，最后一次付款后的预期未来值或现金余额。如果省略Fv，则假设其值为0(零)，即贷款的未来值为0。
键入——可选参数。数字0(零)或1用于指示每个期间的支付时间是在期间的开始还是结束。
（2）使用PMT函数，计算等额本息还贷法，每月还贷额
x;">第一步：在等额本息还贷方式中的“每月还贷额”对应的“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。
在C10单元格输入公式“=C4/12”，即可，如图。
- 日利息率：年利率除以360。
在C11单元格输入公式“=C4/360”，即可，如图。
特别说明：
360指的是会计周期的360天，根据月份对应的天数不同，对应的月利息率在30天的月利息率基础上进行利息的加减，即大于30天的加，小于30天的减。
（2）根据月份，计算每月实际的月利息率
- 判断每月的天数：
第一步：在“当月天数”对应的单元格“k5”中输入公式“=DAY(DATE($I$5,J5+1,0))”，即可计算出2021年1月的天数，如图。
第二步：拖动填充柄，完成其他月，当月天数的计算。如图
特别说明：
- 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年对应月份的天数。
- 计算每月实际的月息：
根据当月的天数，计算当月的实际利率；主要是判断当月的天数是28天，29天，30天或31天，进而在“月利息率”基础上加减对应天数的“日利息率”即可。
第一步：在“月息”对应的单元格“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<C6,"考虑本方案","该方案不可行")”;在K127单元格输入公式“=IF(K126<C6,"考虑本方案","该方案不可行")”,即可得到结果。
结论：通过比较以上的两种结果我们发现，本实例采用等额本息还贷为最佳。原因是月还贷最高的小于5000，满足还款人最大的月偿还能力。
今天的知识就是这些，你学会了吗？自己去探索吧！如果你有任何问题，关注我评论留言，
飞云老师，会在第一时间回复你。
思维决定命运，方法决定效率！
更多EXCEL操作技巧，将陆续更新，请关注！！
