需求产生
因为LPR出台,做了LPR房贷计算器。
偶然接触到需要计算民间借贷利息的朋友,做了LPR利息计算器,仅支持LPR公布(2019/8/20)后的日期的利率计算,认为版1(2020/5/9)。
添加了2019/8/20之前的利息计算功能,认为版2(2020/7/8)。
最近,有朋友提出
同时计算人民银行同期贷款利率和LPR利率下的利息的需求。前后大概花了3个小时(怪自己对match函数不够熟悉,不然应该更快),成了现在的版3(2020/8/4)。
思路分析
计息计算,关键参数:借贷金额,借贷起止时间,利率。
金额、起止时间,定值,输入就好。
利率,三个问题:
根据借贷时间,
利率档位不同,人行同期分为五档,LPR分为两档;
不同的借贷起止时间,引用的
利率类别不同,LPR公布以前引用人行同期利率,之后引用LPR利率。
利率不定期更新,存在
利率-时间变量。
解决方案:
建立
人行同期利率和LPR利率分段时间查询表,利用
index+match函数查找所属档位;
建立
计息时间段查询表,确定借贷起止时间下人行同期和LPR分别计算的时间段;
建立
人行同期利率表、LPR利率表、利息计算明细表,根据利率更新的时间段,利用
index+match函数(匹配时间段)和
ifs函数(匹配档位),查找需要引用的利率。
实现步骤
制作利息计算总览表
设置主要输入参数的单元格:借贷金额、起止时间。
设置需要输出结论的单元格:利息等(这里设计可能需要使用的利息-人行同期、利息-LPR和本金+利息)。
主要功能:更新三个输入参数后,不用查看其它表单,就可以得到想要的结果(前提:后续表单中利率输入无误)。
制作人行同期利息计算表
分为三个表单:利率表,计息时间、利息明细。前两者是输入,后者是输出。
前面有说到为什么是它们三个放在一起:方便引用。
利率表:数据网上搜索后填入,并设计了“生效时间-起”“生效时间-止”两列(自动计算)。“生效时间-起”直接等于同期的调整时间,“生效时间-止”等于下一行的调整时间减1。
注:最开始用的date(year(xx),month(xx),day(xx)-1),结果一样,过程复杂,后来改用直接减1,清爽多了。这里如果对excel的时间和日期有一定了解,就能理解了。日期在excel内存里是以数字的形式储存,减一也可以达到同样的目的。
计息时间:引用计息时间段查询表,放在辅助表单中,后续介绍。
利息明细:以利率表的更新周期为一期,引用利率表的起止时间、利率,得到该期的利息。
这里需要处理的内容比较多,总结如下:
起始日期:第一个起始日期引用计息时间的起始日期。当计息时间的起始日期为“”,它也为“”。第二个开始,=上一期期末日期+1
期末日期:当上一行期末日期=贷款时间-止,为空;当贷款时间-止利率表中生效时间-止,=利率表中生效时间-止。
利率:根据贷款时长,利用ifs函数选择引用的档位(office 2016的朋友反馈说函数不兼容导致计算失效,最后下载office 365解决了),利用index+match函数选择引用的期数(调整时间)。
注:利用if函数多层嵌套也可实现目的,但没有ifs简洁。
利息:数学问题,注意年利率换算成日利率(除以360)即可。
制作LPR利息计算表
步骤同上,只有两档,更简单啦。
制作辅助查询表
档位判断:根据贷款起始时间,计算不同档位的起止时间,建立分段时间查询表。通过index+match函数,判断贷款终止时间落入查询表的哪一个区间,输出档位名称。
引用时间段计算:根据贷款起始时间,计算引用人行同期利率和LPR利率的时间段。(因LPR是2019/8/20公布,在该日期前,引用人行同期利率,之后,引用LPR利率。)
经验总结
为了方便,
人行同期的利率、时间段、利息明细一张表单,
LPR的利率、时间段、利息明细一张表单,辅助的
查询表一张表单,相互引用,免得表单之间翻来翻去。(之前想按利率、利息明细分表单做,引用得我烦躁。)
为了美观,很多地方需要加一层
if(XX=“”,“”,YY)或者用
iferror,正文没有阐述这一点,用的时候出现#N/A自然会知道啦。
根据利率公布日期判断自动生成利息计算表的起始日期、期末日期时,用到的
index+match函数。版1、版2时因为对match函数的了解不够透彻,最初多列了一行,将公布时间当月的第一天作为时间线来引用。做版3时才想起最近看的excel书中有类似的例子,研究了下,发现
match的match_type为1或省略时,会查找小于或等于 lookup_value 的最大值。经过尝试,发现已经满足要求。这里摸索浪费了一些时间,不然应该可以更快完成。
【原创内容,引用请注明出处:https://blog.csdn.net/truly121314/article/details/107837771】
更新时间:2020/8/6
本文来源:https://www.longfajr.com/info/222680.html
标签组:[银行] [利息] [利率] [银行贷款利率] [基准利率] [中国人民银行] [利息计算器]
-
整车人的尖叫,穿透屏幕,中转 人的心底,让咱们跟着颤动 的画面而颤抖 。招行e稳健融资项目,项目融资的的功能 根本 面怎么呢? "国家体育场""鸟巢""项目融资",中国ppp项目融资模式感恩冤家 的匡助名词解释:国际项目融资,银监会项目融资业务甚么叫去库存?咱们二0一七到二0一八年上半年,咱们的良多房地产假定是比拟 繁华,繁华缘由是甚么呢?跟1个政策有关,叫棚改,或者者说要棚改货泉化,这个政策复杂...
2020-11-12
阅读全文 >>
-
科技股少数 收涨,恩智浦收涨一二.一三%,AMD收涨一一.0四%,美光科技收涨六.三六%,奈飞收涨五.一七%,亚马逊收涨四.二三%,英伟达收涨三.四五%,博通以及英特尔辨别 收涨二.八六%,在美上市台积电收涨二.四九%,特斯拉收涨二.0六%,苹果收涨一.五四%,高通收涨一.三%,IBM收涨一.二一%,Dropbox收涨一.0七%;Facebook则收跌0.0三%,推特收跌0.三七%,谷歌母公司Al...
2020-09-26
阅读全文 >>
-
因为乐视网有五六.一九亿元债权 将到期,同时孙宏斌又爆出“已经经资不抵债”的成绩 ,届时乐视颇有能够 会得到 对于新乐视智家的实践 节制权。而其第2大股东,融创旗下的天津嘉睿汇鑫企业管理无限 公司,将成为公司实践 节制人。国际项目融资结构,开展项目融资的可行性前几年王某均正常给付,银监会项目融资业务,资产负债表里面没有交易性金融资产科目可以写在短期投资项目吗?“这是二0一六年九月证监会...
2020-09-26
阅读全文 >>
-
一、银行贷款利率:一、贷款利率:项目 年利率(%) 一、短期贷款 一年以内(含一年) 4.35 二、中长期贷款 一至五年(含五年) 4.75 五年以上 4.90 三、公积金贷款利率 年利率% 五年以下(含五年) 2.75 五年以上 3.25 根据人民银行的规定,目前各家银行的贷款利率是可以自由浮动的,因此各家银行各项贷款的贷款利率会不太一样,贷款需要付出的利息有多有少二、申请银行贷款业务的条件:1...
2020-09-18
阅读全文 >>
-
皇帝下“逐客令”,谁敢抵牾圣意呢?冷枚先生只能兴冲冲 地逃出皇宫,在外边流离失所 。多年之后,才捞到天赐良机,再次被乾隆皇帝欣赏,随后复召内宫。其实,冷枚先生的画作拥有典型的西方 美。园区项目融资,基建项目 融资近几年,传统的服装家具制鞋等居民糊口用品显明向强调耐磨弹性手感等功用 性以及专门用处方向开展 ,因而下游客户多样性的需求请求革用聚氨酯企业需求 有延续的产品研发立异才能 。依据市场以及客...
2020-08-06
阅读全文 >>
-
软件教程 哪家银行小额贷款利率比较低?小额贷款利率低的银行是? 投稿人:板亦玉 20180824 23:27 收藏成功 为最大的贷款机构——银行,各大银行都有不同的贷款产品,而且贷款的额度利率都是不一样的。虽然贷款利率有央行统一规定,但是,不同银行的贷款利率也是有差 ... 为最大的贷款机构——银行,各大银行都有不同的贷款产品,而且贷款的额度利率都是不一样的。虽然贷款利率有央行统一规定,但是,不...
2020-08-03
阅读全文 >>
-
很多人在申请小额贷款的时候都会问2018年最新各大银行小额贷款利率,一般情况下,不同的银行的小额贷款利率是不相同的,下面小编就汇总了2018年最新各大银行小额贷款利率。中信银行信金宝信金宝最高提供30万的额度,申请要求是由中信银行代发工资超过3000的年满23周岁的工薪族。只向特定城市开放,不过已囊括大部分一二线城市。信金宝最特别的应该是它的“3天放款保证”,应该是线下正规银行无抵押小额贷款放款速...
2020-08-03
阅读全文 >>
-
目前,有很多手机APP显示可以查询人民银行征信中心的个人信用报告,只要输入姓名身份证等身份信息,就可轻松查询到自己的征信情况。虽然能够快速查询到自己的征信,但您殊不知,这样的查询其实是有风险存在的。近日,中国人民银行重庆营业管理部发出提示:人民银行征信中心目前没有APP;社会上的手机查询APP与人民银行征信中心无关;同时,建议大众不要在APP上查询个人信用报告。用APP查征信报告 有泄密风险人行重...
2020-08-02
阅读全文 >>
-
屋漏偏逢连夜雨。证券时报middot;e公司记者留意 到,西方 系入主的另外一家公司嘉应制药也遭受费事 。与公司运营 团队的混战暂告段落,嘉应制药股价却已经ldquo;跌跌不休rdquo;,西方 系所持股分面临ldquo;爆仓rdquo;风险,乃至呈现冯彪上诉请求解冻 旗下公司所持股分的状况 。管理项目融资 风险,基建项目 融资从英力特历年的分红记载 来看,过往的年度分红记载 其实不凸起,最高1...
2020-08-02
阅读全文 >>
-
每个银行的信用贷款因为各种因素的影响,都会或多或少的产生一些区别。然而对于普通人来说,就算预期年化利率只差那么一点,最后拿到的贷款额度也会有不小的差距。希财网小编在这里提醒大家,银行个人信用贷款预期年化利率差别大,选择需仔细对比。 信用贷款的申请额度大至在1万至30万,贷款期限少则6个月,长则4年,还款方法既可以选择按月还款,也可以选择按月还息一次结清本息的还款方式。并且预期年化利率的高低往...
2020-08-02
阅读全文 >>