需求产生

因为LPR出台，做了LPR房贷计算器。偶然接触到需要计算民间借贷利息的朋友，做了LPR利息计算器，仅支持LPR公布（/8/20）后的日期的利率计算，认为版1（/5/9）。添加了/8/20之前的利息计算功能，认为版2（/7/8）。最近，有朋友提出同时计算人民银行同期贷款利率和LPR利率下的利息的需求。前后大概花了3个小时（怪自己对match函数不够熟悉，不然应该更快），成了现在的版3（/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 的朋友反馈说函数不兼容导致计算失效，最后下载office 365解决了），利用index+match函数选择引用的期数（调整时间）。

注：利用if函数多层嵌套也可实现目的，但没有ifs简洁。

利息：数学问题，注意年利率换算成日利率（除以360）即可。

制作LPR利息计算表

步骤同上，只有两档，更简单啦。

制作辅助查询表

档位判断：根据贷款起始时间，计算不同档位的起止时间，建立分段时间查询表。通过index+match函数，判断贷款终止时间落入查询表的哪一个区间，输出档位名称。

引用时间段计算：根据贷款起始时间，计算引用人行同期利率和LPR利率的时间段。（因LPR是/8/20公布，在该日期前，引用人行同期利率，之后，引用LPR利率。）

经验总结

为了方便，人行同期的利率、时间段、利息明细一张表单，LPR的利率、时间段、利息明细一张表单，辅助的查询表一张表单，相互引用，免得表单之间翻来翻去。（之前想按利率、利息明细分表单做，引用得我烦躁。）为了美观，很多地方需要加一层if(XX=“”,“”,YY）或者用iferror，正文没有阐述这一点，用的时候出现#N/A自然会知道啦。根据利率公布日期判断自动生成利息计算表的起始日期、期末日期时，用到的index+match函数。版1、版2时因为对match函数的了解不够透彻，最初多列了一行，将公布时间当月的第一天作为时间线来引用。做版3时才想起最近看的excel书中有类似的例子，研究了下，发现match的match_type为1或省略时，会查找小于或等于 lookup_value 的最大值。经过尝试，发现已经满足要求。这里摸索浪费了一些时间，不然应该可以更快完成。

【原创内容，引用请注明出处：/truly121314/article/details/107837771】

更新时间：/8/6