【Excel】引用人民银行同期贷款利率（基准利率）和LPR利率的利息计算器（多项目） |
您所在的位置：网站首页 › 日利息计算工具 › 【Excel】引用人民银行同期贷款利率（基准利率）和LPR利率的利息计算器（多项目） |
【Excel】引用人民银行同期贷款利率（基准利率）和LPR利率的利息计算器（多项目）
|
1 背景
之前做了《引用人民银行同期贷款利率（基准利率）和LPR利率的利息计算器》。 在此基础上，利用数组制作了《多项目&跨越多个调整期的利息计算器》，以满足同时需要计算多项借贷的情况。因为计算过程全部浓缩到一个公式里面，需要一步得出结果，引入了一个“综合利率”的概念。 最近有朋友反馈，跟某某APP计算的结果总是有点出入。于是研究了下某某APP的计算过程，它是按照利息=∑调整期天数×日利率×本金（每期四舍五入精确到0.01元）。 这个思路相比我通过综合利率来计算，还是要符合习惯很多。 推测两种方法的误差也是由于思路的不同导致的。虽然金额差得不多，但是总觉得还是不太舒服。所以想着不引入“综合利率”，重新按照常规思路做一次。 2 思路分析 2.1 原始思路原来计算综合利率，即计算在该时间段内加权平均的利率，再由利息=本金× 综合日利率×天数得出结果。 综合利率的计算涉及四个时间点： 利率调整期起始时间（早于计息起始时间的最后一个利率调整日，记为Date A）；计息起始时间（记为Date B）；计息终止时间（记为Date C）；利率调整期终止时间（晚于计息终止时间的第一个利率调整期的最后一天，记为Date D）。综合利率通过Date A至Date D期间的∑调整期天数×利息，减去Date A至Date B及Date C至Date D期间的调整期天数×利息之和，再除以Date A至Date D的总天数得到。即大范围减去两头不包含的时间段。 最后由本金×综合利率×计息天数得到总的利息。 2.2 本次思路本次改为常规思路的话，涉及的时间点如下： 利率调整期起始时间1（早于计息起始时间的最后一个利率调整日，记为Date E）；计息起始时间（记为Date F）；利率调整期起始时间2（晚于计息起始时间的第一个利率调整日，记为Date G）；利率调整期起始时间（早于计息终止时间的最后一个利率调整期的第一天，记为Date H）；计息终止时间（记为Date I）； 利息计算思路改为每期分段计息再求和，即利息=∑调整期天数×日利率×本金（每期四舍五入精确到0.01元），采用首期+中期+末期的思路（因首尾两期不是完整调整期，而中间期数是），即： 总利息=Date F至Date G期间的天数×Date E对应的日利率×本金+∑调整期天数×日利率×本金（Date G至Date H之间的完整调整期）+Date H至Date I期间的天数×Date H对应的日利率×本金。注：天数计算时，会涉及包不包含被减日期的问题，即日期直接相减、还是相减后需要+1的问题。此处不详细阐述，详见下文公式。 3 实现过程 3.1 输入输出项输入项：本金、起始时间、结束时间，利率。 输出项：利息、利息总和（带公式自动生成，用阴影标出）。 此部分参考上述两个计算器，部分在以前的基础上进行了优化。总结如下： 利率档位——根据起始时间、终止时间判断，利用if函数嵌套（或ifs函数，但有的版本不支持，保险起见还是用了传统而略显拖沓的if函数）+date函数实现利率档位判断。 跨越调整期数——根据起始时间、终止时间，对比利率表，利用match函数判断（注意需要+1）。  首期利息——起始时间Date F到起始时间后的第一次利率调整时间Date G之间的天数×起始时间Date F前的最后一次利率调整时间Date E对应的利率/360×本金。其中，利率需根据利率档位进一步选择对应的档位（方法同上），几处日期的定位采用index+match函数，最后用round函数四舍五入精确至0.01元。此处有特殊情况需处理：当跨越调整期数为1时，计息时间=终止时间-起始时间（视需要看要不要+1，见说明1），无需再去利率表查找。故加了一层if函数区别此情况。  中期利息——起始时间和终止时间之间的完整的利率调整期对应的天数×该期利率/360×本金，每期精确至0.01元后求和。其中，index+match函数查找需引用的时间，offset函数引用跨院调整期数的结果选择需要计算的时间段，if函数嵌套选择引用利率档位，各时间段利率利用round函数四舍五入保留0.01位后，用数组公式按shift+ctrl+enter求和。此处有特殊情况需处理：当跨越调整期数为≤2时，首期放在首期利息中计算，第2期作为末期放在末期利息中处理，此处直接令为0，否则会造成重复计算。故加了一层if函数区别此情况。  末期利息——早于计息终止时间的最后一个利率调整期的第一天Date H到计息终止时间Date I之间的天数×Date H对应的利率/360×本金。方法类似首期利息。特殊情况单独处理：当跨越调整期数为1时，末期利息令为0。故加了一层if函数区别此情况。  至此，辅助项计算完毕，选中后右键+H隐藏，使视图看起来更清爽。完毕。
注：因为隐藏处理了所以没有处理#N/A的部分，如果要求更高的话，加一层if函数令为空，看起来更舒服。 4 总结感想做完之后，还是有一些感触。相比多项目&跨越多个调整期的利息计算器，这版还是更顺手顺心。想说的主要以下几点： 这版计算思路更符合习惯，更容易理解，新增了round函数保留两位小数也更准确（原版综合利率的思路可能引入误差，虽然差不了几块钱，但是总有点不舒服）；表格整体看来更为清爽，中间步骤没有冗余（原版有不少中间步骤隐藏起来了）；人行同期利率分为5档，LPR利率分为2档，为了方便统一按5档处理，不影响计算结果但是稍微有点不符合习惯，有点遗憾但是暂时不进一步处理了；对于计息的天数，做的时候有点争议，有说是按终止时间-起始时间，有说是按终止时间-起始时间+1（即最后一天计不计利息），求证结果是，两种情况都有，具体看双方约定（没有约定就无所谓啦）。 6 后记2021/2/3补充了一张表单，用于展示某项借贷的利息计算明细。 原本的计算利用数组公式，使用者虽然可以查看公式，但几乎看不明白怎么回事。补充之后使用时更能理解结果是怎么来的，整个计算器也更清晰完整，我看着也更满意了~ 过程不表，截图纪念。 【相关文章链接】 引用人民银行同期贷款利率（基准利率）和LPR利率的利息计算器多项目&跨越多个调整期的利息计算器【原创内容，引用请注明出处：【Excel】引用人民银行同期贷款利率（基准利率）和LPR利率的利息计算器（多项目）】 |
输入项直接填入。利率表单独放在第二个表单中。 输出项中，利息=首期利息+中期利息+末期利息（见下文），利息求和用sum函数。
【本文地址】