使用Excel表格计算房贷(等额本息与等额本金)的方法

使用Excel表格计算房贷(等额本息)的方法

假设使用组合贷款基础数据为商业贷款10万，公积金贷款30万，使用商贷年利率3.2%，公积金利率2.85%，贷款年限14年（168个月）。

示例：使用等额本息的方式

基础数据如下图

基础数据计算表

计算公式如下：
计算每月还款金额：
O4单元格的公式就为 =-PMT($O$2/12,$Q$1,$O$1)
$为绝对引用，防止下拉时变动引用的单元格位置数据，下同

PMT函数用于计算等额分期付款的贷款或投资的每期付款金额。
公式解释：

pmt函数直接输出为负值，前面加-可以显示正值
PMT函数的语法为：PMT(rate,nper,pv,fv,type)，其中：
rate表示每期的利率；
nper表示总的期数；
pv表示现值，即贷款或投资的当前价值；
fv表示未来值，即贷款或投资到期时的价值，默认为0；
type表示指定付款是在期初还是期末，0表示期末（默认），1表示期初。

计算本金还款金额
P4单元格的公式就为 =-PPMT($O$2/12,N4,$Q$1,$O$1)

PPMT函数用于计算等额分期付款的贷款或投资中每期的本金偿还金额
公式解释：

-PPMT函数直接输出为负值，前面加-可以显示正值
PPMT函数是基于固定利率及等额分期付款方式，返回投资在某一给定期间内的本金偿还额。
PPMT函数有以下组成部分：

rate：各期利率。
per：用于计算其本金数额的期数，必须介于1到nper之间。
nper：总投资期，即该项投资的付款期总数。
pv：现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款当前值的累积和，也称为本金。
fv：未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零，也就是一笔贷款的未来值为零。
type：数字0或1，用以指定各期的付款时间是在期初还是期末

每期利息还款金额
我们已知前两个结果，直接相减就能实现这个功能,也可以使用IPMT函数
Q4单元格的公式就为 =-IPMT($O$2/12,$N4,$Q$1,$O$1)

IPMT函数用于计算等额分期付款的贷款或投资中每期的利息偿还金额

IPMT函数的语法结构为：IPMT(rate,per,nper,pv,[fv],[type])。其中，
rate为各期利率；
per为用于计算其利息数额的期数，必须在1到nper之间；
nper为总投资期，即该项投资的付款期总数；
pv为现值，或一系列未来付款的当前值的累积和，也称为本金；
fv为未来值，或在最后一次付款后希望得到的现金余额，为可选参数；
type数字0或1，用以指定各期的付款时间是在期初还是期末，也为可选参数

以上部分用公式实现比较简单，直接下拉即可

计算本金与利息时引用的单元格其实是一样的，只是一个计算本金，另一个计算利息，我们只需要将PPMT换成IPMT就可以直接出结果了。

从公式可以看出，PPMT只计算本金，IPMT只计算利息，PMT计算他俩之和

提前还款部分（期数不变，只减少月供金额）

为计算提前还款部分，我将基础数据部分放到4行到171行，共168期的数据

计算提前还款的表格数据如下图：

注：
提前还款期数只有第一行是当月最准确的，从第二开始就是按新的期数给出的提前还款数据结果，也就是比第一行晚还一个月的差异。只能与第一行的数据做对比。基本与第一行的数据无关了（等额本息的情况下月还款是一样的，不存在可变金额数）。

由于组合贷款提前还款部分是等比例同步减少的，因此只需要提前还款部分输入金额即可
提前还款后每月的还款总金额：
B4单元格的公式就为 =-PMT($O$2/12,$Q$1-$A4+1,XLOOKUP($A4-1,$N$3:$N$171,$R$3:$R$171)-$D$1)

公式解释：
$O$2/12 计算出月利息
$Q$1-$A4+1 计算出还剩下总共要还款的期数以及当月这期的合计总数
XLOOKUP($A4-1,$N$3:$N$170,$R$3:$R$170)-$D$1)计算出当期对应的上一期剩余本金（原始数据中的剩余本金）-$G$11 减去本次提前还款的金额

合并后的意思为： 首先计算出要还款的当期对应的上一期剩余本金再减去提前还款的金额，再用剩余后本金根据剩余期数与利率计算出剩余期数每月对应的本金与利息总共金额

提前还款后每月的本金还款金额：
C4单元格的公式就为 =-PPMT($O$2/12,$A4-($A4-1),$Q$1-$A4+1,XLOOKUP($A4-1,$N$3:$N$171,$R$3:$R$171)-$D$1)

公式解释：
$O$2/12 计算出月利息
$A4-($A4-1) 计算对应的期数，因提前部分还款后本金变少，因此从第1期开始，结束期保持不变，只减少月供金额
$Q$1-$A4+1 计算出还剩下总共要还款的期数以及当月这期的合计总数
XLOOKUP($A4-1,$N$3:$N$171,$R$3:$R$171)计算出当期对应的上一期剩余本金（基础数中的剩余本金）-$D$1 减去提前还款的金额

合并后的意思为： 首先计算出要还款的当期对应的上一期剩余本金再减去提前还款的金额，再用剩余后本金根据剩余期数与利率计算出剩余期数每月对应的本金

提前还款后每月的利息还款金额：（也可以直接总共减去本金就是利息）
D4单元格的公式就为 =-IPMT($O$2/12,$A4-($A4-1),$Q$1-$A4+1,XLOOKUP($A4-1,$N$3:$N$171,$R$3:$R$171)-$D$1)

计算本金与利息时引用的单元格其实是一样的，只是一个计算本金，另一个计算利息，我们只需要将PPMT换成IPMT就可以直接出结果了。

公积金部分使用相同的公式，此处不做详细介绍，可参考下面的公式。如需表格数据可私信联系。

公积金提前还款后每月还款金额

=-PMT($T$2/12,$V$1-$A4+1,XLOOKUP($A4-1,$S$3:$S$171,$W$3:$W$171)-$I$1)

公积金提前还款后本金还款金额

=-PPMT($T$2/12,$A4-($A4-1),$V$1-$A4+1,XLOOKUP($A4-1,$S$3:$S$171,$W$3:$W$171)-$I$1)

公积金提前还款后利息还款金额

=-IPMT($T$2/12,$A4-($A4-1),$V$1-$A4+1,XLOOKUP($A4-1,$S$3:$S$171,$W$3:$W$171)-$I$1)

等额本金的计算方法

基础数据如上图

计算公式如下：
计算每月还款金额：
AA4单元格的公式就为 =$AA$1/$AC$1+($AA$1-SUM($AB$4:$AB4))*$AA$2/12
每月还款额=贷款本金/总还款月数+(贷款本金-累计已还款本金)*月利率

每月的本金还款额：
AB4单元格的公式就为 =$AA$1/$AC$1
每月的本金还款额= 贷款本金/总还款月数

每月的利息还款额：
AC4单元格的公式就为 =($AA$1-SUM($AB$4:$AB4))*$AA$2/12
每月的利息还款额=(贷款本金-累计已还款本金)*月利率

剩余本金：
AD4单元格的公式就为 =$AA$1-SUM($AB$4:$AB4)
剩余本金=贷款本金-累计已还款本金

对应的每一行数据直接下拉即可得出结果

贷款计算表原文件下载链接：
https://cloud.189.cn/t/vI7z6vRbuMry（访问码：p188）