企业官网建设流程全解析

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(1+r)^n}{(1+r)^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里新建一个工作表，我把参数区放在左上角，建议这样分配单元格：

利率其实分两部分：LPR + 加点。加点数是签贷款合同时定死的，LPR才会每年浮动。所以我单独把LPR和加点拆开，这样以后LPR一调整，你只需要改B7一个单元格，执行利率自动变化。

3.2 放款日期与重定价日期

放款日期放在B10，格式为日期，比如2023/6/15。重定价逻辑需要区分两种类型：

• 若重定价日为每年1月1日：执行利率在次年1月1日切换为"最新LPR + 加点"。
• 若重定价日为贷款发放日对年对月：执行利率在每年放款日当月切换。

用一段辅助列来标记"第几期执行利率切换"，我在后面会详细展开。

3.3 还款计划区域

从第12行开始是还款计划表。每行代表一期，列结构如下：

上面这张表是核心，后面的所有公式都围绕它展开。先填充好最大期数，再用IF判断是否显示，空白的期数自动隐藏。

3.4 下拉列表设置

B4单元格的还款方式用数据验证做下拉：

1. 选中B4，点击"数据"→"数据验证"→"设置"。
2. 允许"序列"，来源输入：等额本息,等额本金。
3. 确定即可。这样切换还款方式时，计算区域会自动跟着变。

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-$A12+1, $G11)))

解释一下：

• 如果当前行有提前还款标记，就用提前还款后的剩余本金$K12和剩余期数$K12（提前还款后重新按剩余期数计算）。
• 如果没有提前还款，就根据上一期剩余本金$G11和总期数减去已还期数（$B$5-$A12+1）计算。

实际上更简洁的做法：不用$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-$A12+1), $D12-$E12))

在D列（月供）用：

=IF($A12="", "", IF($B$4="等额本金", $F12+$E12, -PMT($C12/12, $B$5-$A12+1, $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(1+r)} ] 在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版本影响。为了验证模板是否正确，我在旁边加了一个校验区域：

如果前面还款计划表的最终剩余本金能归零，说明计算正确；如果不归零，大概率是某行公式引用错了。

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不一样，然后分别查看总利息和剩余期数。这样对比非常直观，可以帮你决定选哪种方案。

对比表的字段可以这样组织：

从这个表能看出一个规律：同样提前还10万，缩短年限的省息效果远大于减少月供。因为缩短年限相当于缩短了剩余资金的计息时间，而减少月供只是降低了每期计息基数。

6.3 用"模拟运算表"做利率敏感性分析

当你准备买房、考虑固定利率还是浮动利率时，可以借助Excel的模拟运算表功能，把不同LPR情景下的月供列出来：

1. 在表格空白区域输入一列假设的LPR值，比如3.45、3.65、3.85、4.05、4.25。
2. 在旁边单元格引用月供计算的公式，比如=D12。
3. 选择"数据"→"模拟分析"→"模拟运算表"，行引用输入B7，列引用留空。
4. 确认后会生成每个LPR对应的新月供。

这个功能特别适合展示"LPR每变动0.2%，月供变化多少"，一目了然。

6.4 用图表看本金和利息的变化趋势

我还会加一个简单的折线图，横轴为期数，纵轴为当期利息或累计利息。等额本息模式下，前期利息线很高，后期慢慢降低；等额本金模式下，利息线从一开始就在下降。两张曲线放在一起，比任何文字都有说服力。

图表实现方式：选择D列到H列的还款计划数据，插入折线图即可。如果只关心利息，可以只选E列和H列。

7. 避坑指南：我做这个计算器踩过的五个坑

做这个计算器的过程中，我踩过不少坑，这里挑几个最有代表性的，希望你避开。

7.1 坑一：PMT参数写反导致月供变成负数

PMT函数返回的是负值，如果你在公式前不加负号，显示出来就是负数。新手经常以为算错了。解决办法：统一在PMT前加-，或在公式外套ABS函数。

7.2 坑二：等额本金提前还款后，本金公式没有重算

等额本金模式下，如果提前还款后仍沿用"初始本金/总期数"作为每月固定本金，那么最后几期的本金就会对不上。正确做法是始终用"当前剩余本金/当前剩余期数"计算本期本金，也就是我前面给的公式：$G11/($B$5-$A12+1)。这样即使提前还款，后续每期本金会重新均匀分布。

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列下拉之前加一个有效性判断：提前还款金额不得超过当期剩余本金，否则弹出警告。

提示：数据验证中可以用"自定义"规则，公式=J12<=G11-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房贷计算器模型能帮你少走点弯路。有任何使用上的疑问，欢迎在评论区交流，我看到会尽量解答。