个人住房商业贷款计算器(我竟然使用Excel制作了一款房贷计算器)

今天我们来分享一下财务专业函数,具体通过跟大家联系紧密的房贷计算器来演示!

 

今天所涉及到的函数,属于财务专业函数,需要有一定的财务基础或者金融学基础更加好理解!

 

我们先看一下,我们想要做的效果!

 

效果等额本息还款法下每期还款情况!

 

▼ 动画演示-参数可调节

其中涉及到的知识点

1、财务函数有PMT、IPMT和PPMT

2、常用函数ROW、IF和SUM

3、控件的使用

 

制作教程

 

基础1 | 控件的使用

 

> 【开发工具】- 【插入】 - 【数值调节按钮】,在合适的位置,画出合适的大小

> 动画演示相关设置操作!

 

▼ 动画演示-数值调节按钮设置

操作说明:

1、插入按钮,右击 - 【设置控件格式】

2、在【控制】中设置,最大值、最小值,步长 和 绑定的单元格

 

单元格链接:表示控件关联的单元格,以后数值的调整都会反映在此单元格中!

 

步长:表示每次点击按钮增加或者减少的数值,默认1,这里调整成5,表示房贷的年限,每次为5,根据实际需要设置即可!~

其他按钮同理,不再一一演示,对于一些特殊的数值,小数等,可以通过其他单元格过渡,再设置值!

 

比如这里的百分比:借助H3过度,解决控件不能设置小数等问题!

同样,对于一些特别大的数值也需要借助过度单元格处理!

解决上限为30000的问题!

那么对应的金额、利率和年限三个可调节参数我们就设置OK了,下面我们来进行明细表的设置!

 

基础02 | 明细表设置-财务函数应用

 

期初或者年份设置:

 

=IF(ROW(A1)<=$D$3*12,ROW(A1),"")

 

操作说明:利用名称框,点位足够的区域,按下Ctrl+D 来填充公式!

 

我们先跳过其他字段,先说核心的三个 本金、利息 和 本息和!

 

函数参数说明:

1、本金对应的函数: PPMT(利率,第几期,总期数,现值,[终值],[类型])

2、利息对应的函数: IPMT(利率,第几期,总期数,现值,[终值],[类型])

3、本息和对应的函数: PMT (利率,总期数,现值,[终值],[类型])

4、关系:PMT = IPMT + PPMT

 

我们先列出来,是因为他们有太多相同的参数,方便我们高效学习!

 

以上参数中,常见的现值和终值,财务的朋友应该都没有问题,如果你不懂财务,那么你这里可以简单理解为:

现值:贷款本金

终值:N期获得

 

参数中的方括号表示该参数可以选择使用!

 

主要说一下 【类型】:期初(1)OR期末(0),默认都是期末支付,也符合一般的习惯!

 

其中PMT我们可以理解为年金函数,因为他的性质和年金很像!

 

下面我们就在实际案例中使用看一下!

 

本文由“壹伴编辑器”提供技术支持

偿还的本息和: =PMT($C$3/12,$D$3*12,$B$3)

 

上面的参数都是年化的,下面我们是按月还款,所以我们要折算成月份维度!

由于是偿还,所以默认就是负数,如果你不习惯,可以调整一下!这里月利率已经写进公式,所以后面我们删除月利率列!

偿还的本金: =PPMT($C$3/12,$B6,$D$3*12,$B$3)

 

相对于PMT,本金和利息都多了一个第二参数,那就是目前是第二期!

偿还的利息: =IPMT($C$3/12,$B6,$D$3*12,$B$3)

和本息参数完全一样,只是一个是I开头一个是P,非常的简单!

期初和期末,期初=上期期末。期末=期初-本期偿还的本金

壹伴辑器”提供技术支持

 

期初: =IF(B6=1,$B$3,G5)

 

期末: =C6+E6

 

由于是负数,所以是相加,本质是期初-本期偿还的本金!

本文由“壹伴编辑器”提供技术支持

 

公式都准备好后,我们选择区域,Ctrl+D 一次性填充即可!

 

▼ 动画演示-快速填充公式

由于超过了期限或报错,所以我们可以判断一下,期初是否为空来容错处理!

 

都加上IF容错即可!非本次重点,我们就不再一一演示!

 

最后成品如下:快去动手试试吧!大家也可以网上查找一下房贷计算器,看一下结果是否一致,小编已校验!

OK,今天我们的内容就先到这里!三个财务领域的函数你学会了吗?

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 lqy2005888@qq.com 举报,一经查实,本站将立刻删除。