请选择搜索范围

whoyzz / 案例 / Excel计算房贷中的等额本息，用等额本息公...

选择朗读音色

亲切女声

稳重女声

成熟男声

选择朗读倍速

0.75倍

1倍

1.5倍

选择循环方式

单篇循环

在 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所示：

图4

6、公式说明：

（1）计算月利息公式。

A、在 =(A$3-(A6-1)*D$3)*B$3/12 中，A$3 表示对行的绝对引用，在往下拖时，A3 不会变为 A4、A5 等，D$3 和 B$3 也是一个意思。

B、A6-1 用于计算已还款期数，(A6-1)*D$3 用于计算已归还本金累计额，当公式在 B6 时，A6 为 1，(1-1)*D$3 结果为 0；当公式在 B7 时，A7 为 2，(2-1)*D$3 恰好是第 1 期还款本金；其它的以此类推。B$3/12 用于求月利率。

（2）计算需还总贷款公式。在 =D3*12*C3+SUM(B3:B305) 中，Sum 用于求 B3 到 B305 的和，即计算所有月的利息总和；公式的意思是把所有月的本金和利息加起来。

三、比较等额本息和等额本金哪个还款少

以上面的对等额本息和等额本金的计算结果为例进行比较，它们的结果如图5所示：

图5

1、等额本息和等额本金需还总贷款分别为 1,607,614.01 和 1,323,924.22，在同等条件下，前者比后者需多还 283689.79 元；这主要是由于后者需要还更多的利息，两者需还总利息分别为 787,614.01 和 503,924.17。

2、尽管等额本息需要还款比等额本金多，但它每月还款固定，且前期还款比等额本金少；而等额本金每月还款不固定（主要是每月还款利息不固定），前期需还款多然后逐渐减少，从图5可以看出，第 2 个月所需还款比第 1 个月减少 11 元左右，第 3 个月比第 2 个月又减少 11 元。

来自： whoyzz > 《案例》

推一荐：发原创得奖金，“原创奖励计划”来了！

0条评论

发表

请遵守用户 评论公约

用Excel制作等额本金和等额本息两种还贷表

用Excel制作等额本金和等额本息两种还贷表。Excel里计算还贷明细非常简单，我们来看针对等额本息、等额本金两种不同方式如何计算每月应...

使用Excel制作还贷计算器的方法

2、选择C8单元格，在编辑栏中输入公式"=$B$3/$C$3";选择D8单元格，在编辑栏中输入公式"=H7*$D$3*$E$3/120";选择E8...

听说你要买房，让Excel告诉你最佳的贷款方案

在1中我们计算的前提是使用了等额本息的还款方式，其实在真正买房贷款时，有两种还款方式，一种是等额本息，就是每期本息之和为一个固定...

一次归还本息；按月付息；等额还本，利息照付；等额还本付息详解

等额还本，利息照付；在等额还本付息条件下，贷款本金1万元，期限1年，年利率12%，贷款人总共收到的利息并不是10000×12%=1200元，因为在等额本息还款方式下，借款人每月都会偿还一部分本金，对于...

excel函数应用技巧：哪种还贷方式更划算-本息变化过程

excel哪种还贷方式更划算-本息变化过程

等额本息和等额本金的爱恨情仇

等额本息和等额本金的爱恨情仇。等额本金和等额本息是贷款还款的两种模式，简单来说，等额本金就是每个月还一样的本金利息每月减少，等额本息就是算上本金和利息的总和每个月还一样的款。因此当我们把...

贷款的真实利率，告诉你教科书上不会教的贷款常识#X0102

贷款的真实利率，告诉你教科书上不会教的贷款常识#X0102.教科书上从来没有教过贷款的真实利率如何计算，不会告诉你哪个指标可以作为贷款...

买房常识：等额本金和等额本息之间的区别

买房常识：等额本金和等额本息之间的区别。还款方式有2种：等额本金、等额本息，这2者之间区别可大着呢，选对了可以省下不少钱。从阴影...

“等额本息”与“等额本金”，你会选择哪种还款方式？？

“等额本息”与“等额本金”，你会选择哪种还款方式？？等额本金：本金保持相同，利息随着本金减少而减少。从“利息”上看，等额本金的...

¥.00

微信或支付宝扫码支付：

开通即同意《个图VIP服务协议》

正在支付中，请勿关闭二维码！

微信支付后，该微信自动注册为你的个人图书馆账号

付费成功，还是不能使用？

复制成功！

绑定帐号，享受特权

恭喜你成为个图VIP！ 在打印前，点击“下一步”观看2个提示

微信支付查找“商户单号”方法： 1.打开微信app，点击消息列表中和“微信支付”的对话 2.找到扫码支付给360doc个人图书馆的账单，点击“查看账单详情” 3.在“账单详情”页，找到“商户单号” 4.将“商户单号”填入下方输入框，点击“恢复VIP特权”，等待系统校验完成即可。

支付宝查找“商户订单号”方法： 1.打开支付宝app，点击“我的”-“账单” 2.找到扫码支付给个人图书馆的账单，点击进入“账单详情”页 3.在“账单详情”页，找到“商家订单号” 4.将“商家订单号”填入下方输入框，点击“恢复VIP特权”，等待系统校验完成即可。

已经开通VIP，还是不能打印？

请通过以下步骤，尝试恢复VIP特权 第1步在下方输入你支付的微信“商户单号”或支付宝“商家订单号” 第2步点击“恢复VIP特权”，等待系统校验完成即可

如何查找商户单号？

正在查询...

订单号过期！ 该订单于2020/09/09 23:59:59支付，VIP有效期：2020/09/09 23:59:59至2020/09/11 23:59:59！如需使用VIP功能，建议重新开通VIP

支付成功！

已获得“发送到手机”权限！

微信扫码，在手机上查看选中内容

确定复制刚才选中的内容？

微信扫码，在手机上查看选中内容

微信扫码，在手机上查看选中内容

复制成功！

¥.00

微信或支付宝扫码支付：

开通即同意《个图VIP服务协议》

正在支付中，请勿关闭二维码！

自动续费¥12/月，可随时取消 

开通即同意《连续订阅服务协议》|《个图VIP服务协议》

支付确认

1. 请在手机上打开的页面进行支付； 2. 如支付完成，请点击“支付完成”。