Excel如何制作每月的房贷车贷提前还贷计算器
我们知道银行贷款的按揭还贷,主要分为等额本息、等额本金两种还款方式。其中等额本息是每月还款固定金额,等额本金是逐月递减(总利息最少)。本文结合公积金贷款、商业贷款,等多种复合条件,分享给大家如何使用身边的Excel,根据不同情况,详细计算每月贷款还款额度。
使用Excel设置贷款计算器模板标题
1、在Excel中,我们先建一张如下图模板的表格。
颜色说明:
黄色背景单元格,用来自己填写数据。
绿色背景的单元格,一般也需要自己填好。
橙色背景的单元格,是我们着重关注的贷款每月还款金额。
其他单元格,一般都是让公式自动计算生成数据。
2、第1列,在单元格A4使用下列公式填写年月。
=EDATE("2015-11-2",ROW()-3)
其中2015-11-2,需修改为贷款实际开始还款的那个月,当中的任意一天。
注意:这里使用公式,而不是直接填写年份月份,是为了方便往下拉公式,实现自动填充。
3、右击单元格,设置单元格格式。
4、 在自定义中,修改为yyyy-m ,这样的格式。
5、第1行,在单元格D1使用公式
="年限("&E1*12&"期)"
在单元格H1使用公式
="打折(实际为"&TEXT(G1*I1,"0.00%")&")"
在单元格L1使用公式
="总利息:"&ROUND(C1*IF(K1=1,G1*I1/12*(E1*12+1)/2,G1*I1/12/(1-1/(1+G1*I1/12)^(E1*12))*E1*12-1),2)&" 相当于本金的"&ROUND(IF(K1=1,G1*I1/12*(E1*12+1)/2,G1*I1/12/(1-1/(1+G1*I1/12)^(E1*12))*E1*12-1),2)&"倍"
6、然后将上述3个单元格,分别往下拉到第2行(复制公式),这样按揭贷款计算器的标题,即制作完毕。
二、使用公式计算公积金贷款每月还贷金额、本金、利息
1、计算公积金逐月还贷,每月需还款的金额:在单元格B4,使用公式
=ROUND(IF($K$1=1,$C$1/$E$1/12+$C$1*(1-((ROW()-4)/$E$1/12))*$G$1/12,$C$1*$G$1*$I$1/12/(1-1/(1+$G$1*$I$1/12)^($E$1*12))),2)
2、为了计算公积金等额本金或者等额本息,每期还款时,贷款本金和贷款利息分别还了多少,我们在单元格C4,输入公式:
="本金:"&ROUND(IF($K$1=1,$C$1/$E$1/12,-($C$1*$G$1*$I$1/12-B4)*(1+$G$1*$I$1/12)^(ROW()-4)),2)&" 利息:"&ROUND(IF($K$1=1,$C$1*(1-((ROW()-4)/$E$1/12))*$G$1*$I$1/12,($C$1*$G$1*$I$1/12-B4)*(1+$G$1*$I$1/12)^(ROW()-4)+B4),2)
3、公积金月缴额(单位+个人),是需要自己填写的,因为因人而异。
注意,需要填写公积金月缴总金额,包含单位交的和个人交的 。
然后在单元格F4,输入公式
=B4-D4
即可立即得到,每月需要实际准备的现金,用于还公积金贷款。如果结果为负值,说明你的公积金足够多,根本不需要另外准备现金或打款到贷款银行账户。
三、使用公式计算商业贷款每月还贷金额、本金、利息
1、类似地,我们在单元格H4使用公式
=ROUND(IF($K$2=1,$C$2/$E$2/12+$C$2*(1-((ROW()-4)/$E$2/12))*$G$2/12,$C$2*$G$2*$I$2/12/(1-1/(1+$G$2*$I$2/12)^($E$2*12))),2)
来计算逐月按揭商业贷款,需要每月还款多少。
2、然后在单元格J4,输入公式
="本金:"&ROUND(IF($K$2=1,$C$2/$E$2/12,-($C$2*$G$2*$I$2/12-H4)*(1+$G$2*$I$2/12)^(ROW()-4)),2)&" 利息:"&ROUND(IF($K$2=1,$C$2*(1-((ROW()-4)/$E$2/12))*$G$2*$I$2/12,($C$2*$G$2*$I$2/12-H4)*(1+$G$2*$I$2/12)^(ROW()-4)+H4),2)
来计算商贷,每月还贷本金和利息的明细。
3、至此,我们已经完成了所有的公式制作,直接选中第4行的相应单元格,往下拉公式即可实现快速计算每月贷款还款金额。
四、Excel贷款计算器使用方法与金融数学原理
1、最后,我们总结一下,刚刚使用Excel制作的贷款计算器的具体使用方法与原理。使用方法很简单,直接按照图示7个步骤,填好数据和公式,最后往下拉即可。
2、为了兼顾需要深究钻研的朋友,本文最后补充一下等额本息、等额本金,每月还款本金、利息的计算原理与方法。等额本金,顾名思义,就是每月还款中,包含的还款本金都一样。只不过,由于欠银行的贷款本金逐月减少,那么相应的利息也逐月减少。因此,会出现逐月还款后,所还的金额越来越少的现象(逐月递减)。
假设贷款总金额a,年利率P,年限n(共n*12个月,即分为n*12期按揭),
月利率p=P/12
具体等额本金的计算公式(第i个月):
每月还款含本金(都相等):a/(12n)
每月还款含利息(逐月递减,等差数列,首项为ap,公差-ap/(12n)):
ap(1-(i-1)/(12n))
每月还款总额: a/(12n)+ap(1-(i-1)/(12n))
总利息(等差数列求和,),为(ap-(n*12-1)*ap/(n*12)/2)*n*12
=ap(n*12+1)/2
3、等额本息,顾名思义,就是每月还的本金加利息,总和固定。假设贷款总金额a,年利率P,年限n(共n*12个月,即分为12n期按揭),
月利率p=P/12
每月还款总额都为x
具体等额本息的计算公式:
第1个月:
还款利息:ap
还款本金:x-ap
第2个月:
还款利息:(a-(x-ap))p = (ap-x)(1+p) +x
还款本金:x-( (ap-x)(1+p) +x ) = -(ap-x)(1+p)
第3个月:
还款利息:(a-(x-ap)+(ap-x)(1+p))p = (ap-x)(1+p)? +x
还款本金:x-( (ap-x)(1+p)? +x ) = -(ap-x)(1+p)?
以此类推,
第i个月:
还款利息:(ap-x)(1+p)^(i-1) +x
还款本金: -(ap-x)(1+p)^(i-1)
一直到最后一个月(第n*12个月):
还款利息:(ap-x)(1+p)^(12n-1) +x
还款本金: -(ap-x)(1+p)^(12n-1)
将每个月的还款本金(是等比数列,首项为x-ap,公比为1+p),
相加之和应该等于总本金a,即
(x-ap)(1-(1+p)???) / (1-(1+p)) = a
则
x=ap(1+1/((1+p)??? - 1))
将x再代入上面的各月的式子,得到:
第1个月:
还款利息:ap
还款本金:ap/((1+p)??? - 1)
第2个月:
还款利息:ap((1+p)???-(1+p))/((1+p)??? - 1)
还款本金:ap(1+p)/((1+p)??? - 1)
第3个月:
还款利息:ap((1+p)???-(1+p)?)/((1+p)??? - 1)
还款本金:ap(1+p)?/((1+p)??? - 1)
以此类推,
第i个月:
还款利息:ap((1+p)???-(1+p)^(i-1))/((1+p)??? - 1)
还款本金: ap(1+p)^(i-1)/((1+p)??? - 1)
总利息,ap(1+p)???(12n)/((1+p)??? - 1) - [(1-(1+p)???)/(1-(1+p))] *ap/((1+p)??? - 1)
=ap(1+p)???(12n)/((1+p)??? - 1)-a
注意事项:不同时期贷款基准利率不同,而且折扣不同,需作相应设置修改,公积金账号因人而异,一般每年会调整一次每月缴存额。
蓝宝石hd6850参数及评测图文介绍一直以来,千元级别的中端显卡都是游戏玩家的最爱。这类显卡既能保障良好的游戏表现,也在大部分消费者的消费能力之下,因此备受玩家们的青睐。今天,小编就为代价带来一款非公版的Radeon
各品牌hd6850市场报价显卡是一台电脑的核心,它的是计算机最基本的配置之一。它用于输出转换数字信号,在转换成图形输出。显卡是连接在电脑主板上的,它有一定的图像处理能力,并且可以辅助CPU运行,提高电脑整体
OPPOReno7Pro怎么关闭HD设置高清通话方法VoLTE就是我们说的高清通话功能,可以为用户提供更好的手机通话体验,同时这个功能也提升了手机的功耗,那么OPPOReno7Pro怎么关闭HD呢?今天就跟着IT百科一起看看吧。OP
显卡加速怎么开启显卡加速方法介绍在一般情况下,电脑在装完系统以后电脑的显卡加速功能是默认已经启用的,但有时会不小心把它给关掉了,要知道倘若我们并未启动显卡加速功能的话,那么电脑很容易卡机的,而且如果开了许多个窗口
7370显卡怎么样7370显卡测评介绍电脑的面世以及普及对我们的生活工作带来了很多的改变。它代表着我们正式进入到了互联网时代,而这个时代我们又将其称为信息化时代。电脑它看似简单,但是实际上却又是十分复杂的。它的内部有很
显卡种类有哪些显卡型号推荐导语显卡的选择决定了电脑的优良中差,在现在这个鱼龙混杂的市场上,外观看似相同的显卡出现了不同的价格和性能,那是因为现如今显卡厂商越来越多,生产的产品近百种,用户们选购时就不知从何下
HD3470的参数详情及性能价格详细介绍图文详解ATIMobilityRadeonHD3470的简介是一款显卡,它凭借出色的性能参数优势以及优秀的实际使用评测而能够在市面上取得良好的评价,保持消费者心中较高的地位。那么接下来不妨
7900gtx显卡配置介绍7900gtx显卡性能介绍7900gtx是由影驰制造的一款显卡产品,定位于中低端消费市场,它的制造工艺并不是特别先进,而且性能也不算特别优秀,但是在办公电脑的领域和普通娱乐电脑领域却依然有不小的市场。影驰7
AMDhd8570显卡怎么样AMDhd8570显卡评测我们都知道,一台笔记本电脑的好坏,最主要的两个个硬件,一个是显卡,一个是处理器,这两个硬件几乎决定了一台电脑能否运行大型高配置要求的游戏以及运行软件的能力。而很多人在购买笔记本电脑
真我gtneo充电要多久充满电需要多长时间真我gtneo这是一款主打游戏性能的手机,这款手机为用户带来目前最好的游戏芯片天玑1200,在为用户提供高性能游戏的同时为用户提供很好手机功耗管理,可以为用户提供很好的续航体验,这
小米12pro功耗怎么样手机功耗大吗小米12pro这款手机搭载上了说功耗很大的骁龙8gen1,那么这款手机的实际体验怎么样?手机的功耗怎么样?这里小编为大家带来最新的手机资讯,可以为用户提供很好的手机购机建议。小米1