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
=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
注意事项:不同时期贷款基准利率不同,而且折扣不同,需作相应设置修改,公积金账号因人而异,一般每年会调整一次每月缴存额。
推荐系统
雨林木风 winxp下载 纯净版 永久激活 winxp ghost系统 sp3 系统下载
64位系统大小:0MB系统类型:WinXP雨林木风在系统方面技术积累雄厚深耕多年,打造了国内重装系统行业知名品牌,雨林木风WindowsXP其系统口碑得到许多人认可,积累了广大的用户群体,是一款稳定流畅的系统,雨林木风 winxp下载 纯净版 永久激活 winxp ghost系统 sp3 系统下载,有需要的朋友速度下载吧。
系统等级:进入下载 >萝卜家园win7纯净版 ghost系统下载 x64 联想电脑专用
64位系统大小:0MB系统类型:Win7萝卜家园win7纯净版是款非常纯净的win7系统,此版本优化更新了大量的驱动,帮助用户们进行舒适的使用,更加的适合家庭办公的使用,方便用户,有需要的用户们快来下载安装吧。
系统等级:进入下载 >雨林木风xp系统 xp系统纯净版 winXP ghost xp sp3 纯净版系统下载
64位系统大小:1.01GB系统类型:WinXP雨林木风xp系统 xp系统纯净版 winXP ghost xp sp3 纯净版系统下载,雨林木风WinXP系统技术积累雄厚深耕多年,采用了新的系统功能和硬件驱动,可以更好的发挥系统的性能,优化了系统、驱动对硬件的加速,加固了系统安全策略,运行环境安全可靠稳定。
系统等级:进入下载 >萝卜家园win10企业版 免激活密钥 激活工具 V2023 X64位系统下载
64位系统大小:0MB系统类型:Win10萝卜家园在系统方面技术积累雄厚深耕多年,打造了国内重装系统行业的萝卜家园品牌,(win10企业版,win10 ghost,win10镜像),萝卜家园win10企业版 免激活密钥 激活工具 ghost镜像 X64位系统下载,其系统口碑得到许多人认可,积累了广大的用户群体,萝卜家园win10纯净版是一款稳定流畅的系统,一直以来都以用户为中心,是由萝卜家园win10团队推出的萝卜家园
系统等级:进入下载 >萝卜家园windows10游戏版 win10游戏专业版 V2023 X64位系统下载
64位系统大小:0MB系统类型:Win10萝卜家园windows10游戏版 win10游戏专业版 ghost X64位 系统下载,萝卜家园在系统方面技术积累雄厚深耕多年,打造了国内重装系统行业的萝卜家园品牌,其系统口碑得到许多人认可,积累了广大的用户群体,萝卜家园win10纯净版是一款稳定流畅的系统,一直以来都以用户为中心,是由萝卜家园win10团队推出的萝卜家园win10国内镜像版,基于国内用户的习惯,做
系统等级:进入下载 >windows11下载 萝卜家园win11专业版 X64位 V2023官网下载
64位系统大小:0MB系统类型:Win11萝卜家园在系统方面技术积累雄厚深耕多年,windows11下载 萝卜家园win11专业版 X64位 官网正式版可以更好的发挥系统的性能,优化了系统、驱动对硬件的加速,使得软件在WINDOWS11系统中运行得更加流畅,加固了系统安全策略,WINDOWS11系统在家用办公上跑分表现都是非常优秀,完美的兼容各种硬件和软件,运行环境安全可靠稳定。
系统等级:进入下载 >
相关文章
- 手机邮箱格式怎么写
- 苹果保修查询如何查询
- 手机怎么投屏到电脑的教程
- 想知道手机截屏怎么弄
- iphone死机如何重启才顺畅
- 苹果手机生产日期查询方法
- WPS文字行间距太大怎么办
- steam怎么查看ID码-steam17位ID码查看方法
- steam账号不符合添加好友功能怎么办
- 百度网盘电脑版如何停用同步空间功能
- 百度网盘怎么取消自动备份
- 百度网盘如何更改默认的下载路径
- 百度网盘违规内容下载失败怎么办
- WPS怎么取消自动保存
热门系统
- 1微软最新版win10 LTSC X64位 纯净版系统 windows10 LTSC 系统下载系统大小:4.4GB系统类型:Win10
- 2雨林木风Win11游戏版系统下载 windows11 64位游戏专用版本V2021系统大小:4.38GB系统类型:Win11
- 3微软Win11系统 64位 正式版Ghost Windows11镜像 2022.05系统大小:4.55GB系统类型:Win11
- 4深度技术 Windows7 纯净版64位下载 v2021.09系统大小:3.9GB系统类型:Win7
- 5大地系统 Win11精简稳定版 GHOST镜像 X64位 V2022.06下载系统大小:4.7GB系统类型:Win11
推荐软件
推荐应用
推荐游戏
热门文章
- 1打开网页提示404错误怎么办 如何解决网页提示404错误【详解】
- 2键盘上顿号怎么打出来 顿号用键盘打出来的两种方法
- 3电脑截图快捷键ctrl加什么?电脑截图快捷键ctrl组合使用方法
- 4两款真实有效的Windows11永久激活工具 附激活码
- 5Excel怎么打勾?7种Excel表格打勾√的方法
常用系统
- 1Win11稳定版系统下载 风林火山Ghost Win11 64位稳定专业版系统大小:4.7GB系统类型:Win11
- 2系统之家win7企业版纯净ghost系统 V2023镜像下载系统大小:4.42GB系统类型:Win7
- 3外星人系统win10企业版 微软正式版下载 简体中文 x64位最新下载 v2023系统大小:0MB系统类型:Win10
- 4雨林木风 windows11纯净版系统 ghost镜像 V2022.04下载系统大小:4.53GB系统类型:Win11
- 5winxp ghost 雨林木风winxp 稳定版 ISO系统下载 无需 激活密钥 永久激活系统大小:1.01GB系统类型:WinXP
- 6电脑公司win11稳定版 64位典藏版下载 永久免费 ghost系统下载系统大小:0MB系统类型:Win11
- 7雨林木风64位企业版 Windows7独立新机版下载 ghost系统 笔记本专用下载系统大小:0MB系统类型:Win7
- 8win10旗舰版下载 萝卜家园64位最新稳定版 ghost镜像 免激活工具下载系统大小:0MB系统类型:Win10
- 9Windows7技术流畅版下载 萝卜家园x86游戏版 ghost镜像下载 免激活工具系统大小:0MB系统类型:Win7