Excel怎么制作每⽉的房贷车贷提前还贷计算器?
我们知道银⾏贷款的按揭还贷,主要分为等额本息、等额本⾦两种还款⽅式。其中等额本息是每⽉还款固定⾦额,等额本⾦是逐⽉递减(总利息最少)。之前,⼩编分享给⼤家如何使⽤Windows计算器简单速算等额本息每⽉还款⾦额,本⽂结合公积⾦贷款、商业贷款,等多种复合条件,分享给⼤家如何使⽤⾝边的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
汽车按揭贷款计算器最新2013
=ap(n*12+1)/2
3、等额本息,顾名思义,就是每⽉还的本⾦加利息,总和固定。假设贷款总⾦额a,年利率P,年限n(共n*12个⽉,即分为12n期按揭),
⽉利率p=P/12
每⽉还款总额都为x
发布评论