★ FEATURED ARTICLE 1. 为什么我要自己动手做一个房贷计算器这几年身边不少朋友买了房聊天时经常被同一个问题卡住银行客户经理给的还款计划表到底怎么算出来的利率明明是浮动的提前还款之后月供又变成多少了我尝试过市面上现成的房贷计算器大部分只能算固定利率、不能灵活调整提前还款时间更别说把LPR的浮动机制加进去。我自己也经历过一次换房贷款当时想把等额本息 vs 等额本金在不同利率下到底差多少钱彻底算清楚结果翻遍网上的工具要么是网页广告太多要么公式写死没法改。后来索性用Excel自己搭了一个可浮动利率LPR加提前还款的房贷计算器用到现在已经好几年每次调LPR、每次提前还款都能立刻看到新的月供和利息变化。这篇博文就把我的完整思路、公式写法、常见坑点全部拆开讲一遍希望能帮到正在纠结贷款方案或者想自己验证银行账单的人。先说清楚这套计算器能干什么支持LPR浮动利率每年的重定价日可以自动切换成新的贷款利率。支持提前还款包括缩短年限和减少月供两种模式可以放在任意还款月份触发。支持等额本息和等额本金两种还款方式随时切换对比。输出完整的还款计划表每期本金、利息、剩余本金、累计利息以及提前还款当月之后的剩余期数变化。适合谁用一类是准备贷款或正在还贷的普通人想验证银行给的还款计划是否准确另一类是从事贷款、理财、财务工作的从业者需要快速做方案测算还有一类是Excel爱好者想理解PMT、PPMT、IPMT这些函数背后的逻辑。我默认你已经会Excel的基本操作比如输入公式、拖动填充、使用下拉列表。不会也没关系后面每个公式我都会解释它是怎么工作的你照着抄也能用。2. 房贷计算的核心逻辑复利、期数与利率换算房贷计算器看起来复杂其实底层就三个变量本金、月利率、还款期数。只要把这三者之间的关系理顺剩下的都是套公式。2.1 月利率的换算千万不要直接除以12很多人犯的第一个错误就是把年利率直接除以12当作月利率。年利率5%时月利率应该是[ 月利率 (1 5%)^{1/12} - 1 \approx 0.4074% ]如果直接用5% / 12 0.4167%虽然差距不大但放在30年的还款周期里累积误差会被放大最终总利息可能差出几千元。银行在实际操作中通常采用年利率除以12的简化方式也就是名义月利率但对个人测算来说用精确的等效月利率更符合复利逻辑。我在计算器里采用的是月利率 年利率 / 12这是银行还款计划表的标准算法也方便和银行对账。提示房贷计息是按期月进行的不是按天所以年利率 / 12是银行惯用的实际计息规则。如果你想做更精细的按日计息模型那就不是这篇博文讨论的范畴了。2.2 等额本息的数学本质等额本息的每月还款额是固定的它的计算式是[ M P \times \frac{r(1r)^n}{(1r)^n - 1} ]其中(P)贷款本金(r)月利率(n)还款总期数这个公式的意思就是把未来所有期数的现金流折算到当前让每期现金流的现值之和等于本金。Excel里对应的函数就是PMT不需要手写公式直接调用即可。每期还款额分成两部分当期利息和当期本金。当期利息 上一期剩余本金 × 月利率当期本金 月供 - 当期利息。下一期的剩余本金再继续计息如此循环。2.3 等额本金的还款逻辑等额本金每月还的本金固定计算公式是[ 每月本金 \frac{P}{n} ]每月利息 剩余本金 × 月利率所以月供是逐月递减的。总利息比等额本息少但前期还款压力大。Excel里没有直接的等额本金函数需要自己算。2.4 为什么30年贷款前期还的几乎全是利息举个例子贷款100万年利率4.2%30年等额本息。第一个月的利息是[ 1000000 \times 4.2% / 12 3500元 ]第一个月的本金只有约1400元。也就是说你月供4900元里超过70%是利息。这个事实让很多人惊讶但这就是复利在起作用。反过来这也是为什么提前还款越早越划算——因为你前期的钱大部分都在支付利息提前还掉本金后面要付的利息就少了。理解了这些核心逻辑接下来就可以把Excel里面的计算表搭起来。3. Excel表格结构设计一个能让公式自动延伸的动态模型我不喜欢用那种把所有期数都写死的固定模板因为LPR变动、提前还款都会改变剩余期数。所以我设计的表格是参数区 动态计算区的结构公式一直填充到最大可能期数比如360期或420期但只在有贷款的时候才显示数据。3.1 参数区布局在Excel里新建一个工作表我把参数区放在左上角建议这样分配单元格单元格内容说明B2贷款本金元比如1000000B3首年执行利率%比如4.2B4还款方式下拉选择等额本息或等额本金B5原贷款总期数月比如360B6LPR重定价日类型每年1月1日或贷款发放日对年对月B7最新LPR%比如3.85B8加点数BP比如-20表示LPR减20个基点B9当前执行利率% B7 B8/100动态变化利率其实分两部分LPR 加点。加点数是签贷款合同时定死的LPR才会每年浮动。所以我单独把LPR和加点拆开这样以后LPR一调整你只需要改B7一个单元格执行利率自动变化。3.2 放款日期与重定价日期放款日期放在B10格式为日期比如2023/6/15。重定价逻辑需要区分两种类型若重定价日为每年1月1日执行利率在次年1月1日切换为最新LPR 加点。若重定价日为贷款发放日对年对月执行利率在每年放款日当月切换。用一段辅助列来标记第几期执行利率切换我在后面会详细展开。3.3 还款计划区域从第12行开始是还款计划表。每行代表一期列结构如下列标题说明A期数从1开始递增B还款日期从放款日期的次月同日开始用EDATE函数生成C当月执行利率年化%根据重定价规则动态切换D月还款额等额本息用PMT等额本金手动计算E当期利息 上一期剩余本金 × 月利率F当期本金 月还款额 - 当期利息等额本金模式下固定本金G剩余本金 上一期剩余本金 - 当期本金H累计利息 上一期累计利息 当期利息I提前还款标记输入提前还款触发计算J提前还款金额手动输入K提前还款后剩余本金 当期还款后剩余本金 - 提前还款金额上面这张表是核心后面的所有公式都围绕它展开。先填充好最大期数再用IF判断是否显示空白的期数自动隐藏。3.4 下拉列表设置B4单元格的还款方式用数据验证做下拉选中B4点击数据→数据验证→设置。允许序列来源输入等额本息,等额本金。确定即可。这样切换还款方式时计算区域会自动跟着变。4. 核心公式详解从PMT函数到LPR自动切换这一节是文章的关键我会把每个公式都拆开并说明为什么这样写。建议你跟着操作一遍理解之后哪怕是换成公积金贷款、组合贷都能自己改。4.1 等额本息的月供公式在D列的每月还款额位置我使用如下公式假设第12行是第一期B2是本金B9是年利率B5是总期数IF(A12, , -PMT(B9/12, $B$5, $B$2))PMT函数返回的是负数代表现金流出所以前面加个负号让它变成正数。这里用$B$9/12作为月利率$B$5作为总期数$B$2作为贷款总额。但注意提前还款之后剩余期数和剩余本金都会变。所以更严谨的做法是每一期都根据当前的剩余本金、剩余期数重新计算月供。我实际使用的D列公式是IF($A12, , IF($I12, -PMT($C12/12, $K12, $G11), -PMT($C12/12, $B$5-$A121, $G11)))解释一下如果当前行有提前还款标记就用提前还款后的剩余本金$K12和剩余期数$K12提前还款后重新按剩余期数计算。如果没有提前还款就根据上一期剩余本金$G11和总期数减去已还期数$B$5-$A121计算。实际上更简洁的做法不用$B$5而是维护一个剩余期数辅助列L每次提前还款后更新。我给个简化版本你在实际使用中可自行选择。4.2 当期利息公式E列当期利息IF($A12, , $G11 * $C12/12)这里的$G11是上一期剩余本金$C12是本期年利率。因为总是按月计息所以除以12。4.3 等额本息当期本金公式F列当期本金IF($A12, , $D12 - $E12)月供减去当期利息得到当期本金。这是等额本息最标准的算法。4.4 等额本金模式下的月供和本金公式如果B4切换成等额本金则每月固定本金 初始本金 / 总期数或者用剩余本金 / 剩余期数提前还款后更精确。月供 固定本金 当期利息。我在F列当期本金用IF($A12, , IF($B$4等额本金, $G11/($B$5-$A121), $D12-$E12))在D列月供用IF($A12, , IF($B$4等额本金, $F12$E12, -PMT($C12/12, $B$5-$A121, $G11)))这样等额本金和等额本息就能在一个模板里共存下拉切换即可。4.5 剩余本金公式G列剩余本金IF($A12, , $G11 - $F12)第一期时$G11相当于初始本金B2所以我在第11行放了一行初始本金作为期初值G11$B$2。这样做的好处是公式统一不需要单独写第一期的逻辑。4.6 LPR重定价怎么让利率自动切换这是浮动利率房贷计算器的灵魂。LPR在一年内可能不变也可能调整但房贷合同的重定价周期通常是一年。所以我需要识别当前月份是否到了重定价月。我新增了一列M辅助列用来标记本期执行利率。假设放款日是某年6月15日重定价日是每年1月1日那第一期2023年7月15日执行的是放款时确定的利率到了2024年1月1日利率变成最新LPR加点。判断方法看本期还款日期的月份和重定价规则。若规则为每年1月1日则当还款日期的月份为1月时执行利率 最新LPR 加点。若规则为放款日对年对月则当还款月份等于放款月份时执行利率 最新LPR 加点。我在C列当月执行利率年化写IF($A12, , IF($B$4等额本息, IF(MONTH($B12)1, $B$7$B$8/100, $C11), IF(MONTH($B12)1, $B$7$B$8/100, $C11)))但这段公式对所有还款方式都一样因为利率切换规则和还款方式无关。所以我直接用IF($A12, , IF(MONTH($B12)1, $B$7$B$8/100, $C11))这里有一个前提$C11是上一期利率第一期的时候C11就是首年执行利率B9。把B9放在参数区对应放款时的合同利率。这样当LPR调整后重定价日那一个月C列自动变成新利率。注意LPR每月20日公布但房贷重定价日通常只认1月1日或者放款日。如果重定价日不是1月把IF(MONTH($B12)1)改成IF(MONTH($B12)MONTH($B$10))即可。对于更复杂的情况比如重定价日为对年对月对日即使不是1月1日也可以用MONTH函数判断月份。对日不是月内的事情房贷计息是按期归整的所以只需要判断月份是否相同即可不需要判断日子。4.7 提前还款的触发逻辑我在I列设置提前还款标记J列输入提前还款金额。触发后的逻辑是当期正常还款后剩余本金 G11 - F12。扣除提前还款金额后新的剩余本金 剩余本金 - J12。提前还款之后剩余期数重新计算。这里还需要一个辅助列L剩余期数公式IF($A12, , IF($I12, ROUNDUP(NPV计算或人工指定新的期数,0), $L11-1))实际上缩短年限和减少月供的处理不同缩短年限提前还款后月供不减少剩余期数变少。新的剩余期数可以用NPER函数反推 [ n -\frac{\ln(1 - \frac{P \times r}{M})}{\ln(1r)} ] 在Excel里就是ROUNDUP(NPER($C12/12, -$D12, $G11-$F12-$J12), 0)。减少月供剩余期数不变月供减少。此时不需要重算期数而是直接用NPER函数算出新的月供。我在D列和L列都加入判断区分两种提前还款模式。简单起见我在参数区B11增加一个下拉提前还款模式缩短年限或减少月供。然后L列公式IF($A12, , IF($I12, IF($B$11缩短年限, ROUNDUP(NPER($C12/12, -$D12, $G11-$F12-$J12), 0), $L11-1), $L11-1))第一期的L11是总期数B5之后每期递减1。一旦触发提前还款新的剩余期数按上述公式计算。D列月供公式也要跟着改IF($A12, , IF($B$11减少月供, IF($I12, -PMT($C12/12, $L11, $G11-$F12-$J12), -PMT($C12/12, $L11, $G11)), -PMT($C12/12, $L11, $G11)))其实这里可以简化无论是否提前还款月供始终根据当前剩余本金和剩余期数用PMT计算。区别只在于提前还款当月使用扣款后的剩余本金否则使用正常剩余本金。这样公式更统一IF($A12, , -PMT($C12/12, $L11, IF($I12, $G11-$F12-$J12, $G11)))当缩短年限时L11已经在触发当月被重算成了更小的期数PMT算出来的月供会比原来大当减少月供时L11维持原剩余期数但剩余本金变小PMT算出来的月供变小。这个设计非常巧妙一个公式同时覆盖两种模式。5. 还款计划表的生成与校验怎么验证算得对不对公式写完只是第一步必须验证结果是否和银行一致。我自己踩过几次坑也看过网上不少模板算出来的数字和银行账单对不上原因是提前还款的当期利息处理方式有问题。5.1 首期利息的特殊处理很多银行对首期还款有一个特殊规则放款日到第一个还款日之间的天数和标准整月不完全一致部分银行按实际天数计算首期利息。比如你在月中放款第一个还款日可能是次月同日这期间不足一个月或超过一个月。我做的计算器默认按整月计息也就是首期利息和后续各期一样。如果你的银行按实际天数算首期利息那只需要把首期E列的公式改成年利率/360×实际天数即可IF($A12, , $G11 * $B9/100 / 360 * 实际天数)实际天数可以用B12和放款日期相减得到。5.2 提前还款当月的利息归属这是最容易出错的地方。提前还款一般有两种模式提前还款的金额直接冲抵本金当月利息仍然按照上一期剩余本金计算而不是按照扣款后的本金计算。少数银行会要求在还款日当天一并结清当月利息这时候利息计算方式会不同。我的模板采用的是正常还完当月月供之后再做提前还款的模式也就是当期利息 上期剩余本金 × 月利率这部分照还不误提前还款金额只减少下一期开始计息的本金。这个逻辑和大多数银行的实操一致。验证方法很简单随便拿一笔贷款先算正常还款计划再看提前还款后的第一期月供是否变小、总利息是否明显减少。如果和银行给的数字有差异先检查首期利息再检查提前还款的当期利息归属。5.3 用Excel自带的函数做交叉校验PMT、NPER、FV这些函数本身是金融领域标准的年金函数不受Excel版本影响。为了验证模板是否正确我在旁边加了一个校验区域校验项公式月供等额本息-PMT(B9/12, B5, B2)总还款额月供 * 期数总利息总还款额 - 本金期末剩余本金FV(B9/12, 已还月数, 月供, -本金)如果前面还款计划表的最终剩余本金能归零说明计算正确如果不归零大概率是某行公式引用错了。5.4 实际案例校验我拿一个模拟项目X来演示本金100万年利率4.2%30年等额本息。月供 -PMT(4.2%/12, 360, 1000000) ≈ 4890.68元。总还款额 ≈ 4890.68 × 360 ≈ 1,760,644元。总利息 ≈ 760,644元。验证银行账单或网上计算器数字基本吻合。若在还款满12期后提前还款10万元选择缩短年限剩余期数会大幅缩短月供保持不变但总利息节省明显。这个数字对比足以推荐你在做提前还款决策前先跑一版自己的计算器。6. 实际使用技巧参数调整、利率更新和可视化对比计算器搭好了日常使用才是重点。我习惯把几个常见操作直接写在Excel里面方便每次打开就能快速更新。6.1 LPR调整后怎么刷新每年重定价日之前我会做一次检查登录银行APP或关注央行发布的当期LPR然后把B7单元格更新为最新数值。因为利率切换在C列自动触发还款计划表会立刻变成新利率下的月供不需要改任何公式。6.2 提前还款方案对比表我做一个简单的对比表把不提前还款缩短年限减少月供三种方案放在同一张表里参数完全一样只有B11不一样然后分别查看总利息和剩余期数。这样对比非常直观可以帮你决定选哪种方案。对比表的字段可以这样组织方案月供元剩余期数总利息元较不提前还款节省利息不提前还款4890360760644-提前10万缩短年限4890约305约651000约109644提前10万减少月供约4470348约694000约66000从这个表能看出一个规律同样提前还10万缩短年限的省息效果远大于减少月供。因为缩短年限相当于缩短了剩余资金的计息时间而减少月供只是降低了每期计息基数。6.3 用模拟运算表做利率敏感性分析当你准备买房、考虑固定利率还是浮动利率时可以借助Excel的模拟运算表功能把不同LPR情景下的月供列出来在表格空白区域输入一列假设的LPR值比如3.45、3.65、3.85、4.05、4.25。在旁边单元格引用月供计算的公式比如D12。选择数据→模拟分析→模拟运算表行引用输入B7列引用留空。确认后会生成每个LPR对应的新月供。这个功能特别适合展示LPR每变动0.2%月供变化多少一目了然。6.4 用图表看本金和利息的变化趋势我还会加一个简单的折线图横轴为期数纵轴为当期利息或累计利息。等额本息模式下前期利息线很高后期慢慢降低等额本金模式下利息线从一开始就在下降。两张曲线放在一起比任何文字都有说服力。图表实现方式选择D列到H列的还款计划数据插入折线图即可。如果只关心利息可以只选E列和H列。7. 避坑指南我做这个计算器踩过的五个坑做这个计算器的过程中我踩过不少坑这里挑几个最有代表性的希望你避开。7.1 坑一PMT参数写反导致月供变成负数PMT函数返回的是负值如果你在公式前不加负号显示出来就是负数。新手经常以为算错了。解决办法统一在PMT前加-或在公式外套ABS函数。7.2 坑二等额本金提前还款后本金公式没有重算等额本金模式下如果提前还款后仍沿用初始本金/总期数作为每月固定本金那么最后几期的本金就会对不上。正确做法是始终用当前剩余本金/当前剩余期数计算本期本金也就是我前面给的公式$G11/($B$5-$A121)。这样即使提前还款后续每期本金会重新均匀分布。7.3 坑三LPR重定价日判断错位如果你用的是每年1月1日重定价但第一年放款日到次年1月1日之间不足12个月那次年1月1日的利率切换是正常的在Excel里判断月份是否为1月即可。但如果你放款日在1月份第一期还款日也在1月那么MONTH($B12)1就会在第一期就触发利率切换这可能不符合真实情况——实际合同通常以放款日LPR为基准重定价日才是次年1月1日。解决办法给B9设置成实际执行利率不要让它也被自动覆盖。我在C列第一期直接引用B9从第二期开始才用月份判断。7.4 坑四提前还款金额超过剩余本金有时候输入提前还款金额时手误比如剩余本金还有150000输成了1500000公式会算出负的剩余本金导致后续PMT报错。我在J列下拉之前加一个有效性判断提前还款金额不得超过当期剩余本金否则弹出警告。提示数据验证中可以用自定义规则公式J12G11-F12这样当金额超限时Excel会拒绝输入。7.5 坑五首期日期用EDATE生成后遇到月末日期出错比如放款日1月31日EDATE(1/31,1)得到2月28日这是正确的。但如果放款日1月30日EDATE(1/30,1)得到2月28日其实银行可能按2月28日或3月1日处理各银行规则不同。我的模板直接使用EDATE遇到月末日期时Excel会自动向前对齐月末对于绝大多数场景足够用。8. 进阶扩展把计算器升级成可复用的贷款工具箱当基础版本稳定后我又陆续加了几个扩展功能让它不只用于房贷也能推广到车贷、消费贷等场景。8.1 支持等额本息与等额本金的一键切换通过参数区B4的下拉选择整张表已经能自动切换两种还款方式。这让我在对比两种方案时特别方便只需要复制一份工作表用副本改成另一种方式然后对比总利息。8.2 支持组合贷或分段利率如果一笔贷款包含公积金贷款和商业贷款两部分利率不同可以采用分段计算第一部分公积金贷款本金、利率、期数单独算一份计划表。第二部分商业贷款单独算一份计划表。每月总还款额 两部分月供之和。我的模板里不必做到完全自动化只要把主表复制两次各自修改参数即可但不建议直接把两段利率写在一个PMT公式里那样会破坏原有的期数逻辑。8.3 支持提前还款后利率同步切换如果LPR重定价日恰好和提前还款日在同一个月C列利率和I列提前还款标记同时生效这处理起来稍微复杂但我的模板实际上已经兼容C列按月份判断利率I列按行判断提前还款两者互不干扰。8.4 加一个敏感度分析面板为了方便汇报我加了一个敏感度分析面板输入不同LPR假设值和不同年份自动生成对应的月供和总利息表。做法很简单就是创建一张二维矩阵行是LPR假设值列是还款期限如10年、20年、30年每个单元格都是-PMT(对应利率/12, 对应期数, 本金)。这个面板在做贷款方案汇报时非常有用能帮决策者快速判断利率波动带来的影响。9. 写在最后这套计算器真正帮我避开了哪些坑如果你问我这套计算器最大的价值是什么我不太想说省了多少利息这种宏大叙事反而更想提那些具体的瞬间比如某次想提前还款银行客户经理在电话里给我的数字和我Excel算出来的不一致我用计算器的每一步倒推回去最终发现是对方把期数算错了一位比如LPR下调后收到银行短信说您的月供将调整至XXXX元我打开Excel一算分毫不差那种踏实感是别的工具替代不了的。还有一个小技巧值得分享我的Excel文件里永远保留两份工作表一份叫当前方案一份叫对比方案。每次LPR调整或者想提前还款时先复制当前方案为对比方案只修改其中一个参数两个版本横向对比马上就能看出差异。这个习惯让我的贷款决策一直很清晰。如果你也想照着搭一套建议不要一上来就追求所有功能全部实现。先把等额本息无提前还款的版本跑通再逐步加入LPR切换、提前还款、等额本金切换。每加一个功能就用一个真实贷款案例做一次验证。我的经验是验证通过比功能多更重要。希望这套Excel房贷计算器模型能帮你少走点弯路。有任何使用上的疑问欢迎在评论区交流我看到会尽量解答。
阅读完成 · 觉得有帮助？
