不知道你有没有遇到过这种情况在手机上用某个房贷计算器App算好了月供过了两年利率一变或者你提前还了一笔钱之前的数字就全部作废了。而且大多数App不支持“利率已经浮动过”加上“中间提前还款”的组合测算你只能拿计算器反复套一个大概的数心里没底。我自己的房贷是在某家商业银行办的合同规定重定价日是每年1月1日。2023年、2024年那几次LPR调整之后银行给我推送的新还款计划表上月供数字和我当初按固定利率模拟的完全对不上。后来我用Excel搭了一个既能跟随LPR浮动、又能模拟提前还款的房贷计算器把合同条款、LPR调整、提前还款策略全都落进一张按月展开的明细表里。算完之后不仅能跟银行每月账单对账还能像做方案一样反复试“今年年底提前还20万、选缩短期限”和“选择减少月供”两种结果的差异。这篇文章不打算讲复杂的金融理论就是把我这套Excel模型的完整搭建过程、核心公式、踩过的坑都拆开给你看。如果你手里有房贷或者正在纠结要不要提前还款又或者想学Excel动态建模都可以拿这篇内容当参考。我不推荐任何贷款产品和银行只聊工具和算法。1. 为什么现成的房贷计算器不够用1.1 在线计算器的三个盲区常见房贷计算器的交互路径基本是一致的输入贷款金额、期限、利率、还款方式瞬间生成一张等额本息或等额本金的还款计划表。这张表看起来精确到分但只有在你“利率永远不变、中途一分钱不多还”的理想状态下才成立。第一个盲区是默认固定利率。很多人的房贷利率是LPR加点形成的浮动利率每年一个重定价日后执行利率就会变化。在线工具大多只给你一个手动填利率的输入框你得每年手动改一次改完之后整张表重新生成原来的累计还款数据就丢失了。第二个盲区是“提前还款”和“利率变化”不能组合测算。某些App支持输入提前还款金额但要么假设利率不变要么只支持“月供不变缩短期限”的单一路径。真实生活里大多数人提前还款会面临三种选择提前还一部分本金后要么月供不变缩短年限要么年限不变减少月供要么直接把贷款结清。再加上利率浮动组合起来之后几乎没有在线计算器能算准。第三个盲区是不透明。你看到的还款计划表是一堆黑盒数字利息是怎么算出来的、提前还款之后剩余的期数是怎么重新推导的界面上根本看不到。你拿银行对账单去核对可能连“首月为什么少几十块钱”都解释不了。1.2 按月拆解逻辑是唯一可靠的路现成工具不好用是因为它们本质上是在套现成的公式而不理解你的现金流路径。经典房贷公式适用于“利率恒定”情景一旦利率或本金发生变化公式本身就要重新推导。我的做法很笨但非常可靠做一张按月展开的明细表。每一行代表一个月列字段包括期初剩余本金、当月适用利率、当月利息、月供、当月偿还本金、提前还款本金、期末剩余本金。一年12行30年360行拉满也就几百行Excel处理起来毫无压力。这张表的核心思想是“逐月滚算”上一月的期末本金就是本月的期初本金本月利息期初本金×月利率月供减去利息得到偿还的本金如果有提前还款直接减掉额外本金剩下的就是新的期末本金。利率变化在哪个月生效就直接改那一行的利率字段提前还款发生在哪个月就那一行填上金额。整个逻辑透明到可以一行一行跟银行账单对账不存在黑盒。1.3 自建模型还有一个隐藏优势策略对比等模型跑通之后你可以把它当成一个决策工具。比如在明细表旁边建两个方案区方案A在2026年3月提前还款10万元并缩短期限方案B选择月供不变期限缩短方案C选择不提前还款。三个方案的累计利息、最后一期日期、月供压力都并排放在一起。这种对比是在线计算器完全做不到的。2. 动工前先把贷款合同翻译成Excel字段2.1 你需要收集哪些原始参数在做模型前先把贷款合同和最近一期银行还款计划表放在手边。下面几个是必须的参数贷款总额合同原始借款本金不是剩余本金。贷款起息日决定首期利息计算和重定价日基准。贷款期限按月算总期数比如30年就是360期。当前剩余本金可以从银行App或还款计划表里查也可以根据已还期数推导。执行利率构成浮动利率需要拆成“LPR加点BP”。BP是基点1BP等于0.01%。比如执行利率4.2%当时5年期以上LPR是4.2%加点为0如果执行利率是4.35%LPR是4.2%那加点就是15BP。重定价日合同里通常写“每年1月1日”或者“贷款发放日对应的每年同月同日”。重定价周期多数是一年少数有半年或一个季度的。还款方式等额本息还是等额本金。计划提前还款的参数预计在哪个月、还多少钱、选择哪种处理方式。这里特别提醒一句加点BP在合同期内是固定不变的变的只是LPR本身。执行利率最新LPR加点BP。很多人把“LPR降了0.1%”理解成“我的利率降0.1%”如果原来有加点实际降幅不一定等于LPR降幅完全取决于重定价日的LPR水平。2.2 等额本息和等额本金怎么选这两种方式月供算法逻辑不同。等额本息是每月还款总额固定前期利息占比高本金偿还速度慢。等额本金是每月偿还等额本金利息按月递减月供逐月递减前期压力大。Excel公式对应起来也简单。等额本息月供用PMT函数计算PMT(月利率, 剩余期数, -剩余本金)等额本金月供是动态的每月本金固定为贷款总额除以总期数月供等于固定本金加上当月剩余本金产生的利息月供 本金 / 总期数 剩余本金 * 月利率在提前还款的语境下绝大多数人建议优先考虑等额本金或提前还款时选择缩短期限本质上都是因为利息是按剩余本金计算的。同样的提前还款金额越早还剩余本金基数减少得越早节省的利息越多。但这是纯数学结论实际还要结合家庭现金流。后面第4部分我会专门说怎么用模型做方案对比。2.3 提前还款的种类先搞清楚银行常见的提前还款有两种场景一种是一次性部分提前还款。还款后重新计算剩余期限和月供通常有“月供不变缩短期限”和“期限不变减少月供”两种选择。某些银行还允许组合调整。另一种是全额提前结清。此时只需要计算到结清日为止的利息加上剩余本金再考虑违约金。全额结清在Excel里很简单在某一行填入剩余本金模型自动会把后面的期数变成0。部分提前还款需要特别注意的是“当期利息”和“额外本金还款”要分开列。很多人提前还款时会误以为当期月供和提前还款额加一起就是当月总支出实际上银行会先把当期利息扣掉剩余的钱全部抵本金。如果顺序弄错账就会平不上。3. 按月明细表的核心公式与完整搭建步骤3.1 工作表结构布局我习惯用一个工作簿包含四张表参数设置、利率区间、还款明细、汇总对比。参数设置里放贷款总额、期限、还款方式、首期日期等全局信息利率区间表记录每段时期的执行利率还款明细是主表一行一个月汇总对比用来输出累计利息、总还款额、省息金额等指标。参数设置表的布局像这样用B列为输入项参数名称输入值贷款总额2000000贷款起息日2020-06-15贷款期限月360当前执行利率4.2%重定价日规则每年1月1日首期模拟日期2020-07-15明细表中日期列可以直接用公式生成。第一行日期等于参数表里的首期日期下一行用EDATE函数往后推一个月EDATE(A2,1)核心字段包括期次、日期、期初本金、月利率、月供、当月利息、当月本金、额外本金还款、期末本金、剩余期数。3.2 逐月公式拆解每一列怎么算我以第2行第一个还款月为例展开。期初本金如果是第1行就引用参数设置表中的贷款总额否则引用上一行的期末本金C2月利率根据当月的执行利率除以12。如果采用4.2%年利率月利率就是0.35%。利率区间的自动匹配见4.1部分。当月利息期初本金 * 月利率等额本息情况下月供不是固定不变的利率一变月供就要重算。第1个月的月供用PMT计算PMT(F2, 剩余期数, -期初本金)这里F2是月利率剩余期数需要单列一列初始值是贷款总期数之后每月减1。当月本金月供 - 当月利息注意如果当月有利息覆盖不了月供的异常情况说明利率或本金参数有问题后面第5部分我会讲怎么排查。期末本金期初本金 - 当月本金 - 提前还款额剩余期数上一行剩余期数 - 1这个模型是不需要IPMT、PPMT这些函数硬凑的反而更容易理解也对账更方便。3.3 提前还款后重算逻辑怎么写写完基础明细表之后整个模型最核心的地方就是提前还款后的两分支逻辑。我们要在明细表里增加两列“提前还款方式”和“重算后状态”。方案A是月供不变、缩短期限。发生提前还款的那一期期末本金用公式直接扣掉提前还款金额。从下一期开始月供保持原值或者银行给的新月供但剩余期限需要重新计算。Excel里用NPER函数反推剩余期数ROUNDUP(NPER(月利率, 月供, -剩余本金),0)NPER返回的是在给定利率和月供下还清剩余本金需要的期数。可能是一个小数比如153.4期银行通常取整到154期。第154期时月供会被重新调整最后一期通常只需要还剩余本金的差额加利息不能生硬套用固定月供。所以我在明细表里加了一个判断IF(期初本金 当月利息 月供, 期初本金 当月利息, 月供)这样最后一期不会多还。方案B是期限不变、减少月供。同样从提前还款后的下一期开始剩余期数保持原值月供重新计算PMT(月利率, 剩余期数, -剩余本金)这里的关键点是银行在执行“减少月供”时重算后的新月供会按新的剩余期数重新等额分摊。只要你把这两列公式写对明细表就能无缝衔接。3.4 对账校验怎么知道模型算得对不对模型建好之后不要急于用来做决策先跟银行最近一期还款计划表对账。对账方法很简单把明细表里累计已还本金求和应该等于贷款总额减去当前剩余本金累计利息求和再对比银行账单上的累计利息两者误差应该在个位数以内。如果误差比较大先排查两个点一是首月计息方式银行首月通常按实际天数计算利息不是整月二是利率切换当月是否分段计息有些银行在重定价日当月会按新旧利率分段计算利息。这两个点都影响金额但只影响该月数值不影响后续本金递推逻辑。为了简化我在明细表里默认按整月计息如果对账时发现差异再按实际天数做修正。4. LPR浮动模拟利率自动切换与敏感度分析4.1 利率区间表的设计浮动利率模型的关键是不把利率写死在明细表里而是单独维护一张“利率区间表”。每一行代表一个时间段内使用的执行利率。比如起始日期结束日期LPR加点BP执行利率2020-06-152021-12-314.65%154.80%2022-01-012022-12-314.60%154.75%2023-01-012023-12-314.20%154.35%明细表里的每个还款日期通过日期匹配找到对应的执行利率。Excel里最顺手的是XLOOKUP的近似匹配模式XLOOKUP(日期, 利率区间表[起始日期], 利率区间表[执行利率], , -1)其中-1表示查找小于或等于日期的最后一个起始日期。如果你用的是不支持XLOOKUP的旧版本Excel可以用INDEXMATCH替代INDEX(利率区间表[执行利率], MATCH(日期, 利率区间表[起始日期], 1))MATCH的第三个参数1表示近似匹配返回小于等于查找值的最大值所在位置效果和XLOOKUP一致。4.2 重定价日的判断LPR不会每个月都变但执行利率确实按照合同约定的重定价日更新。重定价日有两种常见规则对应不同的Excel写法。如果重定价日是每年1月1日只需要在每年1月1日所在行更新利率区间表。手动维护即可非常直观。如果重定价日是贷款发放日对应的每年同月同日可以用DATE和YEAR函数自动生成下一年的重定价日期。假设贷款发放日是2020年6月15日那么重定价日就是每年6月15日。在利率区间表里起始日期写2021-06-15、2022-06-15等就能被明细表自动匹配到。这里有个小技巧银行在执行利率切换时通常在重定价日当天或次月才生效具体以合同为准。你可以在参数设置里加一个“重定价生效月偏移”参数比如0表示重定价日当月生效1表示次月生效。明细表的利率匹配就会自动往后顺延。我把这个模型里加入了一个演示用的情景模拟区。比如你可以在利率区间表里维护三套LPR情景乐观LPR降30BP、基准保持当前LPR、悲观LPR升50BP。每套情景独立占一列明细表用切换按钮或下拉框选择当前情景。这样你就能直观看到未来5年LPR如果涨到4.5%你的月供和总利息会变成多少。4.3 提前还款金额和利率变化的交叉敏感度大部分人提前还款时最关心两件事省多少利息、月供压力变化多少。这两个指标的背后有两个变量提前还款金额和未来利率走势。在Excel里可以用模拟运算表数据表做双因素分析。我先在参数设置里放两个可切换的输入单元格提前还款金额、预计重定价日LPR值。明细表引用这两个单元格。然后新建一个区域行方向放不同提前还款金额比如10万、20万、30万、50万列方向放不同未来LPR水平每个交叉点引用模型输出的累计利息单元格。在旧版Excel里选中表格后用“数据-模拟分析-模拟运算表”填入行引用和列引用。整个操作几分钟就能跑完得到一个完整的“省息矩阵”。实测下来这类双因素表特别适合回答“我现在提前还款和三年后提前还款差别有多大”这类问题。4.4 现金流约束不要只看总利息模型能算出省了多少利息但省下了利息不代表着这个决策是最优的。我在实际给自己做测算时会把提前还款的金额放到一个“家庭现金流表”里这笔钱如果不还房贷放在流动性账户里每年大概能产生多少收益提前还款之后这笔钱失去流动性遇到急用钱时需要额外借钱的成本是多少。这些现金流层面的因素不适合放进同一个还款明细表里但可以在汇总对比区并列展示三行总利息、最后一期日期、月供占家庭月收入比例。我的一位朋友在2024年时差点把全部闲钱拿去提前还款后来用类似模型一对比发现保留一部分现金在手里对比提前还款省下的利息反而更稳妥。Excel算的只是一个数学结果决策还是得结合家庭整体情况。5. 实操检查清单与常见问题排查5.1 完整的搭建顺序为了让新手少走弯路我把整个搭建顺序按依赖关系整理如下先建参数设置表把基础字段全部填好。再建利率区间表至少填两行历史利率和一行未来假设利率。建还款明细表第1行手工输入贷款总额、首期日期、首期利率。第2行开始写公式先用固定利率无提前还款的简单版本跑通。跑通后让明细表改去引用利率区间表验证利率切换自动生效。增加提前还款列和两分支重算逻辑先填一笔金额试跑。添加汇总区累计已还本金、累计利息、总还款额、最后一期日期。最后做对账拿银行最近一期账单核对。对账无误后再建模拟情景和数据表做方案对比。这9步每一步做好再进下一步排查时会轻松很多。不要一上来就写几百行公式出错了根本不知道从哪里查。5.2 常见问题速查表问题现象可能原因解决方式月供计算出来是负数PMT函数参数正负号反了PMT(利率, 期数, -本金)本金必须加负号提前还款后最后一期剩余本金额很大NPER向上取整后最后一期未做特殊处理用IF判断最后一期只还剩余本金加利息利率切换后明细表利率没变化利率区间表的结束日期范围覆盖错误检查起始日期、结束日期是否重叠MATCH近似匹配需要日期升序累计利息和银行账单差几十元首月按实际天数计息或重定价当月分段计息单独调整首月和利率切换月度利息提前还款后当月利息仍然按旧本金计算公式中当月利息引用的是期初本金而非上一行期末本金确认期末本金扣除额外本金发生在该期利息计算之前还是之后按银行规则处理最后一个问题比较细节我多说一句。银行在部分提前还款当月是先算当期的正常月供和利息再在月供之外额外扣一笔本金所以当期利息通常还是按提前还款前的期初本金算。如果你希望完全精确到“提前还款当天开始省息”需要按日切分模型复杂度会明显上升。我的默认做法是当月利息按期初本金算额外本金还款从下一期开始减少本金基数。这样和多数银行的实际扣款规则一致。5.3 我在实操中踩过的坑第一个坑是循环引用。早期设计时我把“重算后的新月供”直接写在了当前行导致PMT函数引用到后续行而后续行又依赖当前行的剩余期数Excel提示循环引用。解决方法是把“当期月供”和“重算后的新月供”分成两列。当期月供是当期的实际扣款重算后的新月供是从下一期开始要用的值。两列之间只通过剩余本金衔接不再互相引用。第二个坑是利率区间表的日期边界问题。有一次我把2023年1月1日的利率写成2022年12月31日结束MATCH结果全部错位整个明细表利率都往后偏了一个月。后来我在利率区间表每个时间段用起始日期代表统一用“起始日期生效”原则不再维护结束日期彻底避免了边界重叠问题。第三个坑是数据表模拟运算表的结果不刷新。在旧版Excel里模拟运算表默认不自动重算换了LPR值后结果不变。这时候要么手动按F9重算要么把工作簿的计算选项设置为自动重算。这个坑能坑掉一大批人的耐心我第一次用数据表时盯着屏幕上不变的金额看了半天以为公式写错了实际只是没有重算。最后分享一点个人心得这个Excel房贷计算器从构思到跑通前后花了我大约两个晚上。最初只是想验证银行给的新还款计划表对不对后来发现它变成了一个完整的家庭财务工具。我现在每个季度打开一次把最新的LPR数值和剩余本金更新进去顺便看看如果年底有闲钱提前还款能省多少利息、值不值得操作。有一点可以确定当你能用Excel一行一行还原银行的计算逻辑时再也不会被各种App的“预估利息”牵着走了。希望这个模型对你有用也欢迎你在实际使用中根据自己的合同条款做调整毕竟每个人的重定价日、还款日和提前还款政策都不完全相同把参数改清楚模型自然就准了。
不知道你有没有遇到过这种情况在手机上用某个房贷计算器App算好了月供过了两年利率一变或者你提前还了一笔钱之前的数字就全部作废了。而且大多数App不支持“利率已经浮动过”加上“中间提前还款”的组合测算你只能拿计算器反复套一个大概的数心里没底。我自己的房贷是在某家商业银行办的合同规定重定价日是每年1月1日。2023年、2024年那几次LPR调整之后银行给我推送的新还款计划表上月供数字和我当初按固定利率模拟的完全对不上。后来我用Excel搭了一个既能跟随LPR浮动、又能模拟提前还款的房贷计算器把合同条款、LPR调整、提前还款策略全都落进一张按月展开的明细表里。算完之后不仅能跟银行每月账单对账还能像做方案一样反复试“今年年底提前还20万、选缩短期限”和“选择减少月供”两种结果的差异。这篇文章不打算讲复杂的金融理论就是把我这套Excel模型的完整搭建过程、核心公式、踩过的坑都拆开给你看。如果你手里有房贷或者正在纠结要不要提前还款又或者想学Excel动态建模都可以拿这篇内容当参考。我不推荐任何贷款产品和银行只聊工具和算法。1. 为什么现成的房贷计算器不够用1.1 在线计算器的三个盲区常见房贷计算器的交互路径基本是一致的输入贷款金额、期限、利率、还款方式瞬间生成一张等额本息或等额本金的还款计划表。这张表看起来精确到分但只有在你“利率永远不变、中途一分钱不多还”的理想状态下才成立。第一个盲区是默认固定利率。很多人的房贷利率是LPR加点形成的浮动利率每年一个重定价日后执行利率就会变化。在线工具大多只给你一个手动填利率的输入框你得每年手动改一次改完之后整张表重新生成原来的累计还款数据就丢失了。第二个盲区是“提前还款”和“利率变化”不能组合测算。某些App支持输入提前还款金额但要么假设利率不变要么只支持“月供不变缩短期限”的单一路径。真实生活里大多数人提前还款会面临三种选择提前还一部分本金后要么月供不变缩短年限要么年限不变减少月供要么直接把贷款结清。再加上利率浮动组合起来之后几乎没有在线计算器能算准。第三个盲区是不透明。你看到的还款计划表是一堆黑盒数字利息是怎么算出来的、提前还款之后剩余的期数是怎么重新推导的界面上根本看不到。你拿银行对账单去核对可能连“首月为什么少几十块钱”都解释不了。1.2 按月拆解逻辑是唯一可靠的路现成工具不好用是因为它们本质上是在套现成的公式而不理解你的现金流路径。经典房贷公式适用于“利率恒定”情景一旦利率或本金发生变化公式本身就要重新推导。我的做法很笨但非常可靠做一张按月展开的明细表。每一行代表一个月列字段包括期初剩余本金、当月适用利率、当月利息、月供、当月偿还本金、提前还款本金、期末剩余本金。一年12行30年360行拉满也就几百行Excel处理起来毫无压力。这张表的核心思想是“逐月滚算”上一月的期末本金就是本月的期初本金本月利息期初本金×月利率月供减去利息得到偿还的本金如果有提前还款直接减掉额外本金剩下的就是新的期末本金。利率变化在哪个月生效就直接改那一行的利率字段提前还款发生在哪个月就那一行填上金额。整个逻辑透明到可以一行一行跟银行账单对账不存在黑盒。1.3 自建模型还有一个隐藏优势策略对比等模型跑通之后你可以把它当成一个决策工具。比如在明细表旁边建两个方案区方案A在2026年3月提前还款10万元并缩短期限方案B选择月供不变期限缩短方案C选择不提前还款。三个方案的累计利息、最后一期日期、月供压力都并排放在一起。这种对比是在线计算器完全做不到的。2. 动工前先把贷款合同翻译成Excel字段2.1 你需要收集哪些原始参数在做模型前先把贷款合同和最近一期银行还款计划表放在手边。下面几个是必须的参数贷款总额合同原始借款本金不是剩余本金。贷款起息日决定首期利息计算和重定价日基准。贷款期限按月算总期数比如30年就是360期。当前剩余本金可以从银行App或还款计划表里查也可以根据已还期数推导。执行利率构成浮动利率需要拆成“LPR加点BP”。BP是基点1BP等于0.01%。比如执行利率4.2%当时5年期以上LPR是4.2%加点为0如果执行利率是4.35%LPR是4.2%那加点就是15BP。重定价日合同里通常写“每年1月1日”或者“贷款发放日对应的每年同月同日”。重定价周期多数是一年少数有半年或一个季度的。还款方式等额本息还是等额本金。计划提前还款的参数预计在哪个月、还多少钱、选择哪种处理方式。这里特别提醒一句加点BP在合同期内是固定不变的变的只是LPR本身。执行利率最新LPR加点BP。很多人把“LPR降了0.1%”理解成“我的利率降0.1%”如果原来有加点实际降幅不一定等于LPR降幅完全取决于重定价日的LPR水平。2.2 等额本息和等额本金怎么选这两种方式月供算法逻辑不同。等额本息是每月还款总额固定前期利息占比高本金偿还速度慢。等额本金是每月偿还等额本金利息按月递减月供逐月递减前期压力大。Excel公式对应起来也简单。等额本息月供用PMT函数计算PMT(月利率, 剩余期数, -剩余本金)等额本金月供是动态的每月本金固定为贷款总额除以总期数月供等于固定本金加上当月剩余本金产生的利息月供 本金 / 总期数 剩余本金 * 月利率在提前还款的语境下绝大多数人建议优先考虑等额本金或提前还款时选择缩短期限本质上都是因为利息是按剩余本金计算的。同样的提前还款金额越早还剩余本金基数减少得越早节省的利息越多。但这是纯数学结论实际还要结合家庭现金流。后面第4部分我会专门说怎么用模型做方案对比。2.3 提前还款的种类先搞清楚银行常见的提前还款有两种场景一种是一次性部分提前还款。还款后重新计算剩余期限和月供通常有“月供不变缩短期限”和“期限不变减少月供”两种选择。某些银行还允许组合调整。另一种是全额提前结清。此时只需要计算到结清日为止的利息加上剩余本金再考虑违约金。全额结清在Excel里很简单在某一行填入剩余本金模型自动会把后面的期数变成0。部分提前还款需要特别注意的是“当期利息”和“额外本金还款”要分开列。很多人提前还款时会误以为当期月供和提前还款额加一起就是当月总支出实际上银行会先把当期利息扣掉剩余的钱全部抵本金。如果顺序弄错账就会平不上。3. 按月明细表的核心公式与完整搭建步骤3.1 工作表结构布局我习惯用一个工作簿包含四张表参数设置、利率区间、还款明细、汇总对比。参数设置里放贷款总额、期限、还款方式、首期日期等全局信息利率区间表记录每段时期的执行利率还款明细是主表一行一个月汇总对比用来输出累计利息、总还款额、省息金额等指标。参数设置表的布局像这样用B列为输入项参数名称输入值贷款总额2000000贷款起息日2020-06-15贷款期限月360当前执行利率4.2%重定价日规则每年1月1日首期模拟日期2020-07-15明细表中日期列可以直接用公式生成。第一行日期等于参数表里的首期日期下一行用EDATE函数往后推一个月EDATE(A2,1)核心字段包括期次、日期、期初本金、月利率、月供、当月利息、当月本金、额外本金还款、期末本金、剩余期数。3.2 逐月公式拆解每一列怎么算我以第2行第一个还款月为例展开。期初本金如果是第1行就引用参数设置表中的贷款总额否则引用上一行的期末本金C2月利率根据当月的执行利率除以12。如果采用4.2%年利率月利率就是0.35%。利率区间的自动匹配见4.1部分。当月利息期初本金 * 月利率等额本息情况下月供不是固定不变的利率一变月供就要重算。第1个月的月供用PMT计算PMT(F2, 剩余期数, -期初本金)这里F2是月利率剩余期数需要单列一列初始值是贷款总期数之后每月减1。当月本金月供 - 当月利息注意如果当月有利息覆盖不了月供的异常情况说明利率或本金参数有问题后面第5部分我会讲怎么排查。期末本金期初本金 - 当月本金 - 提前还款额剩余期数上一行剩余期数 - 1这个模型是不需要IPMT、PPMT这些函数硬凑的反而更容易理解也对账更方便。3.3 提前还款后重算逻辑怎么写写完基础明细表之后整个模型最核心的地方就是提前还款后的两分支逻辑。我们要在明细表里增加两列“提前还款方式”和“重算后状态”。方案A是月供不变、缩短期限。发生提前还款的那一期期末本金用公式直接扣掉提前还款金额。从下一期开始月供保持原值或者银行给的新月供但剩余期限需要重新计算。Excel里用NPER函数反推剩余期数ROUNDUP(NPER(月利率, 月供, -剩余本金),0)NPER返回的是在给定利率和月供下还清剩余本金需要的期数。可能是一个小数比如153.4期银行通常取整到154期。第154期时月供会被重新调整最后一期通常只需要还剩余本金的差额加利息不能生硬套用固定月供。所以我在明细表里加了一个判断IF(期初本金 当月利息 月供, 期初本金 当月利息, 月供)这样最后一期不会多还。方案B是期限不变、减少月供。同样从提前还款后的下一期开始剩余期数保持原值月供重新计算PMT(月利率, 剩余期数, -剩余本金)这里的关键点是银行在执行“减少月供”时重算后的新月供会按新的剩余期数重新等额分摊。只要你把这两列公式写对明细表就能无缝衔接。3.4 对账校验怎么知道模型算得对不对模型建好之后不要急于用来做决策先跟银行最近一期还款计划表对账。对账方法很简单把明细表里累计已还本金求和应该等于贷款总额减去当前剩余本金累计利息求和再对比银行账单上的累计利息两者误差应该在个位数以内。如果误差比较大先排查两个点一是首月计息方式银行首月通常按实际天数计算利息不是整月二是利率切换当月是否分段计息有些银行在重定价日当月会按新旧利率分段计算利息。这两个点都影响金额但只影响该月数值不影响后续本金递推逻辑。为了简化我在明细表里默认按整月计息如果对账时发现差异再按实际天数做修正。4. LPR浮动模拟利率自动切换与敏感度分析4.1 利率区间表的设计浮动利率模型的关键是不把利率写死在明细表里而是单独维护一张“利率区间表”。每一行代表一个时间段内使用的执行利率。比如起始日期结束日期LPR加点BP执行利率2020-06-152021-12-314.65%154.80%2022-01-012022-12-314.60%154.75%2023-01-012023-12-314.20%154.35%明细表里的每个还款日期通过日期匹配找到对应的执行利率。Excel里最顺手的是XLOOKUP的近似匹配模式XLOOKUP(日期, 利率区间表[起始日期], 利率区间表[执行利率], , -1)其中-1表示查找小于或等于日期的最后一个起始日期。如果你用的是不支持XLOOKUP的旧版本Excel可以用INDEXMATCH替代INDEX(利率区间表[执行利率], MATCH(日期, 利率区间表[起始日期], 1))MATCH的第三个参数1表示近似匹配返回小于等于查找值的最大值所在位置效果和XLOOKUP一致。4.2 重定价日的判断LPR不会每个月都变但执行利率确实按照合同约定的重定价日更新。重定价日有两种常见规则对应不同的Excel写法。如果重定价日是每年1月1日只需要在每年1月1日所在行更新利率区间表。手动维护即可非常直观。如果重定价日是贷款发放日对应的每年同月同日可以用DATE和YEAR函数自动生成下一年的重定价日期。假设贷款发放日是2020年6月15日那么重定价日就是每年6月15日。在利率区间表里起始日期写2021-06-15、2022-06-15等就能被明细表自动匹配到。这里有个小技巧银行在执行利率切换时通常在重定价日当天或次月才生效具体以合同为准。你可以在参数设置里加一个“重定价生效月偏移”参数比如0表示重定价日当月生效1表示次月生效。明细表的利率匹配就会自动往后顺延。我把这个模型里加入了一个演示用的情景模拟区。比如你可以在利率区间表里维护三套LPR情景乐观LPR降30BP、基准保持当前LPR、悲观LPR升50BP。每套情景独立占一列明细表用切换按钮或下拉框选择当前情景。这样你就能直观看到未来5年LPR如果涨到4.5%你的月供和总利息会变成多少。4.3 提前还款金额和利率变化的交叉敏感度大部分人提前还款时最关心两件事省多少利息、月供压力变化多少。这两个指标的背后有两个变量提前还款金额和未来利率走势。在Excel里可以用模拟运算表数据表做双因素分析。我先在参数设置里放两个可切换的输入单元格提前还款金额、预计重定价日LPR值。明细表引用这两个单元格。然后新建一个区域行方向放不同提前还款金额比如10万、20万、30万、50万列方向放不同未来LPR水平每个交叉点引用模型输出的累计利息单元格。在旧版Excel里选中表格后用“数据-模拟分析-模拟运算表”填入行引用和列引用。整个操作几分钟就能跑完得到一个完整的“省息矩阵”。实测下来这类双因素表特别适合回答“我现在提前还款和三年后提前还款差别有多大”这类问题。4.4 现金流约束不要只看总利息模型能算出省了多少利息但省下了利息不代表着这个决策是最优的。我在实际给自己做测算时会把提前还款的金额放到一个“家庭现金流表”里这笔钱如果不还房贷放在流动性账户里每年大概能产生多少收益提前还款之后这笔钱失去流动性遇到急用钱时需要额外借钱的成本是多少。这些现金流层面的因素不适合放进同一个还款明细表里但可以在汇总对比区并列展示三行总利息、最后一期日期、月供占家庭月收入比例。我的一位朋友在2024年时差点把全部闲钱拿去提前还款后来用类似模型一对比发现保留一部分现金在手里对比提前还款省下的利息反而更稳妥。Excel算的只是一个数学结果决策还是得结合家庭整体情况。5. 实操检查清单与常见问题排查5.1 完整的搭建顺序为了让新手少走弯路我把整个搭建顺序按依赖关系整理如下先建参数设置表把基础字段全部填好。再建利率区间表至少填两行历史利率和一行未来假设利率。建还款明细表第1行手工输入贷款总额、首期日期、首期利率。第2行开始写公式先用固定利率无提前还款的简单版本跑通。跑通后让明细表改去引用利率区间表验证利率切换自动生效。增加提前还款列和两分支重算逻辑先填一笔金额试跑。添加汇总区累计已还本金、累计利息、总还款额、最后一期日期。最后做对账拿银行最近一期账单核对。对账无误后再建模拟情景和数据表做方案对比。这9步每一步做好再进下一步排查时会轻松很多。不要一上来就写几百行公式出错了根本不知道从哪里查。5.2 常见问题速查表问题现象可能原因解决方式月供计算出来是负数PMT函数参数正负号反了PMT(利率, 期数, -本金)本金必须加负号提前还款后最后一期剩余本金额很大NPER向上取整后最后一期未做特殊处理用IF判断最后一期只还剩余本金加利息利率切换后明细表利率没变化利率区间表的结束日期范围覆盖错误检查起始日期、结束日期是否重叠MATCH近似匹配需要日期升序累计利息和银行账单差几十元首月按实际天数计息或重定价当月分段计息单独调整首月和利率切换月度利息提前还款后当月利息仍然按旧本金计算公式中当月利息引用的是期初本金而非上一行期末本金确认期末本金扣除额外本金发生在该期利息计算之前还是之后按银行规则处理最后一个问题比较细节我多说一句。银行在部分提前还款当月是先算当期的正常月供和利息再在月供之外额外扣一笔本金所以当期利息通常还是按提前还款前的期初本金算。如果你希望完全精确到“提前还款当天开始省息”需要按日切分模型复杂度会明显上升。我的默认做法是当月利息按期初本金算额外本金还款从下一期开始减少本金基数。这样和多数银行的实际扣款规则一致。5.3 我在实操中踩过的坑第一个坑是循环引用。早期设计时我把“重算后的新月供”直接写在了当前行导致PMT函数引用到后续行而后续行又依赖当前行的剩余期数Excel提示循环引用。解决方法是把“当期月供”和“重算后的新月供”分成两列。当期月供是当期的实际扣款重算后的新月供是从下一期开始要用的值。两列之间只通过剩余本金衔接不再互相引用。第二个坑是利率区间表的日期边界问题。有一次我把2023年1月1日的利率写成2022年12月31日结束MATCH结果全部错位整个明细表利率都往后偏了一个月。后来我在利率区间表每个时间段用起始日期代表统一用“起始日期生效”原则不再维护结束日期彻底避免了边界重叠问题。第三个坑是数据表模拟运算表的结果不刷新。在旧版Excel里模拟运算表默认不自动重算换了LPR值后结果不变。这时候要么手动按F9重算要么把工作簿的计算选项设置为自动重算。这个坑能坑掉一大批人的耐心我第一次用数据表时盯着屏幕上不变的金额看了半天以为公式写错了实际只是没有重算。最后分享一点个人心得这个Excel房贷计算器从构思到跑通前后花了我大约两个晚上。最初只是想验证银行给的新还款计划表对不对后来发现它变成了一个完整的家庭财务工具。我现在每个季度打开一次把最新的LPR数值和剩余本金更新进去顺便看看如果年底有闲钱提前还款能省多少利息、值不值得操作。有一点可以确定当你能用Excel一行一行还原银行的计算逻辑时再也不会被各种App的“预估利息”牵着走了。希望这个模型对你有用也欢迎你在实际使用中根据自己的合同条款做调整毕竟每个人的重定价日、还款日和提前还款政策都不完全相同把参数改清楚模型自然就准了。
