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。）
• 强迫症认为这种方法简单但不是很完美。不过实际操作起来差别应该不大，所以这份计算器的结果还是可以拿来参考的。
• 这份计算器逻辑清晰，功能强大，个人还是挺满意的啦，啦啦啦。

【原创内容，引用请注明出处：【Excel】可浮动利率（LPR）和提前还款的房贷计算器】