如何用excel计算等额本息还款额及利息总额？

请用语音读文章

热门回答：

房贷还款有两种方式：等额本息和等额本金。

1. 等额本息：

每月还款总金额相等

每月贷款利息按月初剩余贷款本金计算并逐月结清

因此。每月还款利息逐月减少。本金逐月增加。维持总金额不变

2. 等额本金：

将本金平均分摊到每个月内

同时付清上一交易日至本次还款日之间的利息

每月还款总金额逐月递减

下图可以非常直观地理解两种还款方式的区别：

理解了房贷还款方式以后。我们以一个实际案例来学习制作房贷计算模板。

案例：

打开二手房网站。随便点击一套 13 年房龄的小房子。价格是这样的……

就以此为例。计算等额本息和等额本金的每月还款金额。

操作步骤：

1. 先设计出空的模板。如下：

2 至 5 行中的蓝色背景区域为手工输入区。根据实际情况填写

第 10 行开始是计算区域。绿色区域按等额本息计算。黄色区域按等额本金计算

2. 填写手工输入区域：

房屋售价：1800万

首付比例：70%。二套房首付门槛

贷款利率：5.88%。基准利率

贷款年限：20年

3. 设置图中 A 至 D 列数据。公式/方法如下：

房屋售价：=B2

首付金额：=B2*B3

贷款金额：=A10-B10

还款月数：1 至 240 填充。填充方式可以参见 Excel 99%的人忽略了的“填充”功能

4. 等额本息公式设置：

Excel 计算等额本息还款额非常简单。因为有 3 个专用函数：

月还款额＝PMT(各期利率,总期数,本金,,）

本金＝PPMT(各期利率,第几期,总期数,本金）

利息＝IPMT(各期利率,第几期,总期数,本金）

具体应用到本例。公式如下：

1) 每月还款总额：

=-PMT($B$4/12,$B$5*12,$C$10*10000,,)

公式释义：

$B$4/12：5.88% 是年化利率。需要除以 12。得到每月的利率

$B$5*12：同理。20 年乘以 12 才能得出需要还款的总月数。即总期数

$C$10*10000：贷款金额。单位是“万元”。乘以 10000 换算成“元”

,,：最后 2 个可选参数可以不填。所以两个“,” 后面留空

PMT 函数是用于计算扣款金额的。所以默认结果为负数。本例中。我们不需要负数表示。所以在公式前面添加“-”。转换成正数

2) 每月本金金额：

=-PPMT($B$4/12,$D10,$B$5*12,$C$10*10000)

释义同上。不多解释

3) 每月利息金额：

=-IPMT($B$4/12,$D10,$B$5*12,$C$10*10000)

4) 把 F10:H10 的公式下拉。并且计算“实际总付款金额”：

=A10+F10*20*12/10000

公式释义：

A10 为首付金额

F10*20*12/10000：因为每月还款总金额相等。所以总还款金额为每月基数 * 还款期数。最后再除以 10000 将单位换算为“万元”

5. 等额本金公式设置：

等额本金没有专门函数。所以需要用公式来计算：

1) 每月本金金额：

=($C$10*10000)/($B$5*12)

公式释义：

($C$10*10000)：贷款金额。单位换算为“元”

/($B$5*12)：除以总还款期数

2) 每月利息金额：

=($C$10*10000-($D10-1)*K10)*$B$4/12

公式释义：

($C$10*10000-($D10-1)*K10)：贷款金额。减去还清的本金总数。得到剩余应还本金

*$B$4/12：乘以贷款利率。年化换算为月。即得到当月应还利息

3) 每月还款总额：

=SUM(K10:L10)

4) 把 J10:L10 的公式下拉。并且计算“实际总付款金额”：

=A10+SUM(J10:J249)/10000

公式释义：

A10 为首付金额

SUM(J10:J249)/10000：每月还款金额加总。除以 10000 将单位换算为“万元”

其他观点：

感谢邀请！

我们以贷款金额60万元、贷款期限30年（即360个月）为案例来计算；

开始输入计算所需数据：

1、“还款期数”：按顺序填写1-360（分别代表1个月—360个月）。

2、“年利率”：题目所设置的5%。

3、“月利率”：将“年利率”/12个月换算成为月利率。换算后“月利率”为0.4167%。

4、“贷款剩余本金”：填写我们的贷款金额60万元。

5、首先来计算“本息合计”。我们先选择第一期“本息合计”框。然后在Execl菜单栏里找到“插入”菜单。并选择“函数”。

6、在“函数”对话框中我们选择“PMT”函数。然后点击确定。

“PMT”函数是指：计算在固定利率下。贷款的等额分期偿还额。

1. 开始输入函数计算所需数字。

1、“Rate”：各期利率。填写“月利率”（年利率/12）。

2、“Nper”：贷款期。填写“360”（30年=360个月）。

3、“Pv”：从该项贷款开始计算时已经入账时款项。填写“-600000”（负数）。

4、“Fv”、“Type”：未来值和逻辑值。可填写“0”、“1”或不填写。

5、点击“确定”按键。

2. 开始计算“归还利息”。

1、选择第一期“归还利息”框。

2、输入=G3*C3。然后“回车键”确定。

3. 开始计算“归还本金”。

1、选择第一期“归还本金”框。

2、输入=F3-E3。然后“回车键”确定。

4. 开始计算“剩余贷款本金”。

1、第一期剩“剩余贷款本金”=600000元（最初贷款金额）。

2、第二期“剩余贷款本金”=第一期“剩余贷款本金”-第一期“归还本金”。

3、第三期“剩余贷款本金”=第二期“剩余贷款本金”-第二期“归还本金”。

4、剩余期数：以此类推······

5. 计算结果：

1、每月贷款月供数为：3220.93元。

2、30年共计归还本金：600,000.00元。

3、30年共计归还利息：559,534.71元。

4、30年共计归还本息：1,159,534.71元。

其他观点：

Excel公式如下：

每月等额本息额 = PMT(5%/12,30*12,600000)=¥-3,220.93

30年累计需要归还利息 = {SUM(IPMT(5%/12,ROW(A1:A360),360,600000))}=¥-559,534.71

30年累计需要归还利息计算公式为数组公式

以上就是由优质生活领域创作者 生活常识网 整理编辑的，如果觉得有帮助欢迎收藏转发~