Excel计算房贷中的等额本息，用等额本息公式计算

whoyzz 2019-11-16

展开全文

在 Excel 中，计算房贷中的等额本息可以用PMT函数，不需用等额本息计算公式，这样既方便又快速；但计算等额本金没有直接的函数，需要用等额本金计算公式一项项计算；下就是它们用 Excel 计算的具体操作方法，操作中所用版本均为 Excel 2016。

一、用Excel计算房贷中的等额本息

假如购房贷款 820000 元，商业贷款年利率为 4.9%，贷款时间 25 年，还款方式为等额本息；现在要求用 Excel 计算每月应该还款数额和需还总贷款数额。

（一）用PMT函数计算

1、计算每月应该还款数额。采用等额本息还款每月还款数额一样，所以只需计算一次；双击 D3 单元格，把公式 =PMT(B3/12,C3*12,A3) 复制到 D3，按回车，返回 -4,745.98，负号表示需还款。

2、计算需还总贷款数额。双击 E3，输入公式 =D3*12*C3，按回车，返回 -1,423,795.38；操作过程步骤，如图1所示：

图1

3、公式 =PMT(B3/12,C3*12,A3) 说明：

A、Pmt函数的表达式为 PMT(Rate, Nper, Pv, [Fv], [Type])；Rate 表示贷款月利率，Nper 表示贷款总期数（月数），Pv 表示本金；Fv 为可选项，表示未来值，若省略，默认取 0；Type 也为可选项，表示支付时间，0 或省略表示期末，1 表示期初。

B、公式中的 B3/12 用于计算月利率，12 表示一年 12 个月；C3*12 计算总期数，A3 是本金，公式省略了后两个参数。

（二）用等额本息公式计算

1、等额本息计算月还款公式为：（贷款本金×月利率×（1＋月利率）^还款月数）/（（1＋月利率）^还款月数－1），把数值代入此公式变 =(A3*B3/12*(1+B3/12)^(C3*12))/((1+B3/12)^(C3*12)-1)，双击 D5 单元格，把公式复制到 D5，按回车，返回 4,745.98，跟用PMT函数计算结果一致；确保当前选项卡为“开始”，单击“常规”下拉列表框，在弹出的选项中选择“货币”，则数字前自动加上元符号；再单击“字体颜色”图示，选择“浅蓝色”，则文字变为所选颜色；操作过程步骤，如图2所示：

图2

2、计算需还总贷款方法跟上面一样，用已计算出的每月还款数额乘上贷款年数再乘每年12个月即可。

提示：如果贷款利率变了，例如加息或减息了，只需修改 B3 中数值，“每月还款和需还总贷款”会自动重新计算返回新值，演示如图3所示：

图3

二、用Excel计算房贷中的等额本金

1、计算每月需还本金。采用等额本金还款每月需还本金不变，它的计算公式为：每月需还本金 = 本金/还款月数，把数值代入公式变为 =A3/(C3*12)，双击 D3 单元格，把公式复制到 D3，按回车，返回 2,733.33。

2、生成还款期数（即月数）。由于贷款 25 年，每年 12 个月，因此还款期数为 300；选中 B6，输入 1，单击一下 A7 退出输入状态，再次选中 A6，按住 Alt 键，依次按一次 H、F、I、S 键，打开“序列”窗口，选择左上角的“列”，“终止值”输入 300，按回车，则生成 1 到 300 的序列；按快捷键“Ctrl + 向下方向键”定位到表格最后一行，305 行的第 1 列中正是 300。再按快捷键“Ctrl + 向上方向键”定位到表格第 5 行。

3、计算每月需还利息。由于采用等额本金还款每月需还利息都不一样，因此每月都需要计算，计算公式为：每月需还利息 =（本金 - 已归还本金累计额）×月利率，代入数值变为 =(A$3-(A6-1)*D$3)*B$3/12，双击 B6，把公式复制到 B6，按回车，接着把单元格格式设置为“货币”，则数值变为 3,348.33；把鼠标移到 B6 右下角的单元格填充柄上，鼠标变为加号后，双击左键，则 1 到 300 期都计算出所需还利息。

4、计算每月应还款。只需把每月应还本金和利息加起来，双击 C6，把公式 =D$3+B6 复制到 C6，按回车，返回第一个月（期）应还款 6,081.67；同样用双击单元格填充柄的方法返回剩余月数的应还款，按“Ctrl + 向下方向键”定位到表格最后一行，已经有了数值。

5、计算需还总贷款。计算方法有两种，一种为把所有“月还款”相加，可用公式 =SUM(C6:C305)；另一种为把每月应还本金乘总月数再加所有月应还利息，可用公式 =D3*12*C3+SUM(B3:B305)，这种方法更精确一点；假如用后一种方法，把后一个公式复制到 E3，按回车，返回需还总贷款 1,323,924.22；操作过程步骤，如图4所示：