【Excel】可浮动利率（LPR）和提前还款的房贷计算器

【Excel】可浮动利率（LPR）和提前还款的房贷计算器

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，贷款余额和期数同时变化。贷款余额⾃动引⽤，期数需特别处理。提前还款后，期数变成了多少？这是这⾥的⼀个关键问题。

分析过程：

提前还款后，剩余本⾦减少，即期末贷款余额减少，保持⽉还款额不变，则还款的次数减少；

提前还款⾦额⼤概率不是⽉还本⾦的整数倍，故减少的期数，理论上会有⼩数点，实际还款期数为整数，故期数要么向上取整（五

⼊），要么向下取整（四舍）；

为保证提前还款后的⽉还款额不超过原来的还款额（不⽐原来的⽉供更多负担更重），采⽤期数向上取整（五⼊）；

核⼼问题转换成：如何计算新的期数？

经过了尝试和摸索，最终⽅案如下：

具体⽅法为：

等额本⾦⽤PER函数，等额本息直接提前还款额除以原⽉还本⾦，以计算新的期数，提前还款后的⽉份新期数会产⽣⼩数；

新列中⽤round函数保留两位⼩数后输出；

注：如果没有这⼀步，后续处理成整数时，还款的最后⼀期剩余期数可能是0.000000000x，roundup会处理成1，与需要的结果不

符。保留两位后回避了这个问题。当然也可以有其他⽅法。

等额本息中⽤roundup函数取整，等额本⾦⽤rounddown函数取整，得新的剩余期数（为保证新的⽉供略少于原始⽉供）；

新期数与上⼀⾏对⽐，输出提前还款减少的期数，注意需两者相减后再减1，列求和可得提前还款导致的总的还款期数减少值。

⽉还款额引⽤新的剩余期数。

⾄此，提前还款的问题解决。

4 总结分析

本次计算器的思路重点为：每⽉引⽤新的参数，包括剩余贷款额、利率、剩余期数等，由此实现浮动利率和提前还款的计算。

提前还款期数减少，有朋友反馈说银⾏并没有处理⼩数点产⽣的误差那部分，⽽是保持原来的结果，将误差放到最后⼀个⽉的⽉还款

额。（⽐如平时还1000，提前还款后还是还1000，不过最后⼀个⽉可能只⽤200。）

强迫症认为这种⽅法简单但不是很完美。不过实际操作起来差别应该不⼤，所以这份计算器的结果还是可以拿来参考的。

这份计算器逻辑清晰，功能强⼤，个⼈还是挺满意的啦，啦啦啦。

【原创内容，引⽤请注明出处：】