美琳妈咪【Excel】可浮动利率（LPR）和提前还款的房贷计算器
nvidia显卡控制面板1 灵感来源
⽹上有现成的计算器，为什么还要⾃⼰做？
因为⽹页版的房贷计算器看不到过程，不知其所以然，不知道能不能完全信任。
加上朋友咨询提前还款的问题，⼜LPR开始执⾏，从现实和书⾥汲取了灵感，开始构思可浮动利率（LPR）和提前还款的房贷计算器。
经过trial and error，最后稳定成现在的样⼦。
2 思路分析
制作计算器的主要⽬的：二年级教师节手抄报简单又漂亮
关于热爱祖国的手抄报1. 判断是否转LPR；
2. 判断是否提前还款，如果有，还款的时间和⾦额如何决定；
3. 转LPR或者提前还款对现有贷款的影响，解答最关⼼的问题——利息节省了多少。
为达到以上⽬的，按以下步骤设计表格：
1. 设置起始贷款的总览表，反应贷款合同决定的初始状态——通过输⼊贷款⾦额、起始年利率、期数、贷款⽇期等参数，得到使⽤者最
关⼼的⽉还款额和原利息合计两项内容。
注：“起始”“原”是针对后来有提前还款或利率变更的情况⽽⾔，以⽰区别。
2. 设置每⽉明细表格，变化的利率（LPR）和提前还款的时间和⾦额作为参数输⼊，反应计息的变量和变量导致的结果。
3. 设置节省利息单元格，通过原利息合计与每⽉明细中的利息合计相减，得到是否转LPR和提前还款的最关⼼的内容——节省多少利
息。
因为贷款⽅式分为等额本⾦或等额本息，提前还款后可选择提前还款期数不变（⽉还款额减少）或提前还款⽉还款额不变（期数减
少），2×2有四种组合结果，故设置四张表单表达四种情况：
等额本息+提前还款期数不变（记为A）
等额本⾦+提前还款期数不变（记为B）
等额本息+提前还款⽉还款额基本不变（记为C）
等额本⾦+提前还款⽉还款额基本不变（记为D）
3 实现步骤
3.1 起始贷款
今天几点立夏主要项⽬如下：
贷款⾦额——输⼊数值；
起始年利率——输⼊贷款时的年利率；
期数——输⼊贷款期数；
起始⽉还款额——等额本息通过PMT函数可得，等额本⾦公式为起始⽉还款额=贷款⾦额/期数+贷款⾦额×⽉利率；
原利息合计——等额本息通过CUMIPMT函数可得，等额本⾦通过公式利息合计=贷款⾦额×年利率/12×(期数+1)/2 可得；（等⽐数列化简得到。）
贷款⽇期——输⼊贷款起始⽇期，便后续每⽉明细⾥编辑每期的起始⽇期，也为表格添加时间线，⽅便使⽤。
3.2 每⽉明细
⽤来查看每期贷款的具体明细，包括期数、⽇期、⽉利率、还款额、⽉还本⾦、⽉还利息、提前还款、期末贷款余额等。具体如下：
期数——第⼀期为1，往后为上⼀期期数+1；
⽇期——初始为贷款⽇期，往后为上⼀期+1⽉，可⽤edate函数实现；
警的组词
⽉利率——第⼀期为年利率/12，第⼆期开始为等于上⼀期⽉利率（利率变化如有变化，在该⽉份填⼊新利率，后续⽉份会⾃动更新，浮动利率计算由此实现）；
还款额——等额本息⽤PMT函数得，等额本⾦为⽉还利息+⽉还本⾦，引⽤当期的利率、剩余本⾦、剩余期数。
⽉还本⾦——等额本息为该期还款额-⽉还利息，等额本⾦为贷款⾦额/期数；
提前还款——初始为空，发⽣提前还款时填⼊⾦额；
期末贷款余额——上期贷款余额-本期⽉还本⾦-提前还款额。
难啃的地⽅来了。
对于A和C，提前还款后期数不变，主要变量为贷款余额，即每⽉明细中的期末贷款余额。由于每期已⾃动引⽤上⼀期期末余额，⽆需特别处理。
对于B和D，贷款余额和期数同时变化。贷款余额⾃动引⽤，期数需特别处理。提前还款后，期数变成了多少？这是这⾥的⼀个关键问题。分析过程：
提前还款后，剩余本⾦减少，即期末贷款余额减少，保持⽉还款额不变，则还款的次数减少；
提前还款⾦额⼤概率不是⽉还本⾦的整数倍，故减少的期数，理论上会有⼩数点，实际还款期数为整数，故期数要么向上取整（五⼊），要么向下取整（四舍）；
为保证提前还款后的⽉还款额不超过原来的还款额（不⽐原来的⽉供更多负担更重），采⽤期数向上取整（五⼊）；
核⼼问题转换成：如何计算新的期数？
经过了尝试和摸索，最终⽅案如下：
具体⽅法为：
等额本⾦⽤NPER函数，等额本息直接提前还款额除以原⽉还本⾦，以计算新的期数，提前还款后的⽉份新期数会产⽣⼩数；
新列中⽤round函数保留两位⼩数后输出；
注：如果没有这⼀步，后续处理成整数时，还款的最后⼀期剩余期数可能是0.000000000x，roundup会处理成1，与需要的结果不符。保留两位后回避了这个问题。当然也可以有其他⽅法。
等额本息中⽤roundup函数取整，等额本⾦⽤rounddown函数取整，得新的剩余期数（为保证新的⽉供略少于原始⽉供）；
新期数与上⼀⾏对⽐，输出提前还款减少的期数，注意需两者相减后再减1，列求和可得提前还款导致的总的还款期数减少值。
⽉还款额引⽤新的剩余期数。
⾄此，提前还款的问题解决。
4 总结分析
本次计算器的思路重点为：每⽉引⽤新的参数，包括剩余贷款额、利率、剩余期数等，由此实现浮动利率和提前还款的计算。
提前还款期数减少，有朋友反馈说银⾏并没有处理⼩数点产⽣的误差那部分，⽽是保持原来的结果，将误差放到最后⼀个⽉的⽉还款额。（⽐如平时还1000，提前还款后还是还1000，不过最后⼀个⽉可能只⽤200。）
强迫症认为这种⽅法简单但不是很完美。不过实际操作起来差别应该不⼤，所以这份计算器的结果还是可以拿来参考的。
这份计算器逻辑清晰，功能强⼤，个⼈还是挺满意的啦，啦啦啦。
【原创内容，引⽤请注明出处：】
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论