资金集中管理（六） 如何用excel实现内部往来的“智能”计息 101013-6

进行资金集中管理就会涉及到内部往来的计息。企业内部往来计息可以简单分为两种：单一利率计息、多级利率计息。                                     

资金占用利息 

 = 资金占用金额 * 资金占用天数 * 年利率  / 360 

 = 资金占用积数 * 年利率 / 360

注：1、计息是全年天数通常用360天，也可以根据企业需要用365天；

  2、关于积数的概念可以参考往期文章：102008 资金管理中积数、年化的概念与运用。

从上面资金占用利息公式可见影响利息的几个因素：资金占用金额、计息利率、资金占用天数。只要能实现这三个因素的便捷维护和统计就可以让excel计息变的更“智能”。

资金占用金额

资金占用金额即往来业务发生的实际金额，根据实际业务发生明细进行维护即可。为了维护的便捷性，可以根据网银数据结构对计息表列项目做相应调整，以尽可能实现直接复制粘贴。如果是多家银行结构不一致，可以考虑做一个转换表，将不同银行的格式转换成统一的格式然后粘贴到计息表中，以提高基础数据维护的工作效率。

如果是采用多级利率计息则往来维护相对更复杂一些。需对不同利率档次的业务进行拆分。或者针对每一笔借款/存款初次发生时赋予唯一业务编号，后续针对该笔业务的往来通过该唯一业务编号进行勾稽维护。该操作与金融机构存、贷款操作方式类似。为了更好的业务管理，建议表格拆分为上下两部分，上半部分为业务初次发生时维护，下半部分为后续业务发生时维护，如下图：

资金占用天数

资金占用天数取决于业务发生日期、计息开始日期、计息截止日期三个时间因素。在一个计息期间（如某月）的资金占用天数指该笔业务在这一区间的实际占用天数。实际操作中如果逐笔去维护和确认，操作效率很低，尤其是业务量比较大时。

如果两笔款项，金额、时间、利率都一致，在同一时间区间产生的利息是一致的。借用这个规律，只要将借、还款的金额转换为正、负数表示，就可以对借、还款进行自动“核销”。

例如：

1月6日借款1000万，当日开始计息，至2月25日归还，则借款自1月6日开始计息，还款自2月25日开始进行负数计息。在1月6日至2月25日之间，仅借款这一笔在计息。2月25日以后，借款、还款两笔业务同步计息，但两个数据金额相反，利率和计息天数相同，合计余额和利息均为零。如果该两笔业务不做删除，留在计息明细，2月25日以后无论是往来余额还是计息利率均'无影响'，实现了'自动核销'。

通过上述操作，在往来管理时就不需要对收支进行关联管理，计息时对收入和支出分别计算资金占用天数，然后进行求和即可。

实际工作中绝大部分企业内部计息都是按月进行计算，在确定当月实际计息天数时就涉及到业务发生日期、计息开始日期、计息截止日期三个日期的判断。只有精确判断三个日期的先后才能准确计算资金占用天数。对于EXCEL来说判断日期是非常容易的事情。下面是通过EXCEl公式“自动”计算计息天数的案例：

通过上述公式就可以实现往来计息天数的“自动”计算。如果不考虑计息表中第一个月的上一期计息截止日期，上述公式会更简单，但需要在计息表中单独维护该日期，且后续月份和第一个月公式会有差异，操作起来很不方便且容易出错。对计息区间的理解还可通过画图方法来理解和判断。

计息利率

计息利率即每一笔业务按什么利率计息。如果用单一利率就十分简单，所有业务统一用一个利率即可。

如果是采用多级利率，维护就相对麻烦一些。首先需明确利率规则并建立基础表，然后根据规则用公式从基础表获取相应档次的利率即可实现自动匹配利率。

若需根据不同借/存款期限采用不同的利率，则应先确定好各种期限对应利率，然后针对每笔业务选定业务期限，利率即可实现“自动维护”。根据不同单位采用不同计息利率原理一样。

只要明白了业务逻辑和操作的基本思路，实际业务中的各种需求变化都可以迎刃而解。我们不可能初次就做出一份很满意、很“智能”的计息表格。最有效的办法就是根据管理需要和自己所掌握的知识先做一版出来，然后在使用过程中去找操作最费时、最不方便的点，进行单点解决。经过几轮这样的迭代后您的计息表就会变的很“智能”，用起来也会很顺手。

由于篇幅有限，无法仔细讲解具体excel怎么操作。将我自己经过多年迭代出来的内部往来计息表分享给大家，仅供参考。模板下载地址如下：

链接: 

https://pan.baidu.com/s/1w5NPoGs8SI7U26iMMdCSjQ&shfl=shareset 

提取码: 98ff 

------------   The end   ------------