利用Excel计算利息损失（适用多笔欠款与还款）
作者 张健
来源 法悟
编辑 英子，蛋蛋，Cleis，夏洛克-不二熊
编者按|
本近期小编在处理一起货款纠纷案，庞大的交易量导致小编连续做了三天的excel来理清案件事情，因繁琐而狂躁暴走的同时也深感巧妙掌握办公软件（尤其是Excel神器！）的确可以大大减少律助们的工作量。于是又想起之前收藏的文章，通过一则案例为Excel的初学律师介绍如何利用Excel计算利息损失，这个案例针对的是在某一期间发生多笔欠款，同时发生多笔还款（偿还本金），且在此期间发生多次利率调整的情形，在此推送给大家。文章来源于法悟，在此感谢授权。
…
一、案例介绍
原告将其房屋出租给被告使用，租赁期限自2010年12月31日至2014年12月31日，月租金100000元，每半年支付一次租金，每次支付600000元，每年的6月30日和12月31日支付租金。合同签订后，原告将租赁房屋交付被告使用，被告于2011年6月30日支付了首期租金600000元，2012年3月5日支付租金50000元、2012年5月18日支付租金160000元、2013年1月10日支付租金120000元、2014年8月25日支付租金280000元、2014年10月7日支付租金220000元。2015年1月1日合同约定的租赁期限届满后，被告将租赁房屋返还给了原告。现要求计算自2011年12月31日至2015年3月3日期间，被告应当赔偿原告因逾期支付租金而造成的利息损失。
二、计算过程
（一）Excel表格设计
1?表格内容
设计的Excel表格应当包括如下内容：序号、日期、事件、利率标准、本期应付、累计应付、本期还款，累计还款，累计欠款，利率标准、欠款天数、利息金额、利息合计、本息合计。上述内容确定后，进行Excel表格制作。最终确定的Excel表格设计方案见图1。
图1
2?单元表格数据类型设定
选中需设置数据类型所在列，然后点击鼠标右键，在出现的菜单中选中“设置单元格格式”，然后依据下列要求分别进行数据类型设置：
（1）“序号”一列设置为“常规”格式；
（2）“日期”一列设置为“日期”格式；
（3）“事件”一列设置为“常规”格式；
（4）“利率标准”一列设置为“数字”格式，且小数位数设置为“2”位；
（5）“本期应付”一列设置为“数字”格式，且小数位数设置为“2”位；
（6）“累计应付”一列设置为“数字”格式，且小数位数设置为“2”位；
（7）“本期还款”一列设置为“数字”格式，且小数位数设置为“2”位；
（8）“累计还款”一列设置为“数字”格式，且小数位数设置为“2”位；
（9）“累计欠款”一列设置为“数字”格式，且小数位数设置为“2”位；
（10）“欠款天数”一列设置为“数字”格式，且小数位数设置为“2”位；
（11）“利息金额”一列设置为“数字”格式，且小数位数设置为“2”位；
（12）“利息合计”与“本息合计”设置为“数字”格式，且小数位数设置为“2”位；
（13）“复核”，通过不同行与列中数据的交叉计算，以复核录入数据或计算公式设计是否错误。
（二）基础数据录入
依据设置的数据类型所需的格式，按下述要求录入基础数据：
1?需要录入的基础数据有：序号、日期、事件、本期应付、利率标准、本期还款；
2?第一行录入的基础数据：“序号”为“1”,“日期”为“利息起算日”之前中国人民银行最后一次进行利率调整的日期，因为利息起算日为2011年12月31日，此前最后一次利率调整的日期为2011年7月7日，因此此行的“日期”录入为“2011/7/7”,“事件”确定为“利率调整”，因为在该日期没有发生欠款和付款的事实，因此“本期欠款”确定为“0.00”，“本期付款”确定为“0”，其他项目不录入；
3?第二行录入的基础数据：“序号”为“2”,“本期欠款”确定为“600000.00”,“日期”确定为“2011/12/31”,“事件”确定为“利息起算日”，因为在该日期没有发生还款，因此“本期还款”录入为“0”，其他项目不录入；
4?最后一行基础数据（不含“复核”、“利息合计”与“本息合计”行，下同）：“序号”按实际排列计, “本期欠款”和“本期付款”均为“0.00”,“时间”为“2015/3/3”,“事件”为“利息截止日”，其他项目不录入；
5、第三行和倒数第四行的基础数据，均为利息起算日和截止日之间的利率调整数据，该数据按如下方式录入：
（1）在中国人民银行的官方网站中查询金融机构人民币贷款基准利率的历史数据（网址：/publish/zhengcehuobisi/631/index.html）；
（2）确定适用的利率标准（通常依据不同笔欠款所对应的日期分别确定，本案假定均适用“三至五年”一档的利率标准）；
（3）截取2011年7月7日至2015年3月3日期间的所有数据，然后导入或录入Excel中的“日期”和“利率标准”列的单元表格中（导入或复制时，应当注意将日期调整为“日期”格式，否则不能计算期间天数）；
（4）所有利率调整的日期对应的“事件”均录入为“利率调整”。
6?按实际新增欠款日期的先后顺序，插入相应的行，“事件”录入为“拖欠租金”，然后依据案件事实在该行中录入对应的“序号”、“本期还款”、“利率标准”中录入相应的数据；
7?按实际还款日期的先后顺序，插入相应的行，“事件”录入为“偿还租金”，然后依据案件事实在该行中录入对应的“序号”、“本期应付”、“利率标准”中录入相应的数据；
8?基础数据录入完毕后的效果见图2。
图2
（三）计算流程
1、计算各期的“累计应付”
（1）第一组数据：
由于此前未发生欠款，且本期亦未发生新增欠款，因此在对应的“累计欠款”的单元表格中录入为“0.00”；
（2）第二组数据：
由于本期新增欠款600000元，该期对应的“累计应付”为上一期“累计应付”与“本期应付”相加之和。
具体操作如下：用光标选中“日期”列中“2011/12/31”所在行对应的“累计欠款”的单元表格，然后键入“＝”，点击上面的“0.00”（显示为“Ｆ2”）,键入“＋”，点击“本期应付”中“2011/12/31”对应的“600000.00”(显示为“Ｅ3”)，上述操作完成后显示为“＝Ｆ2＋Ｅ3”。然后回车，即得出截止2011年12月31日（利息起算日）所对应的“累计应付”为“600000.00”，具体操作见图3。
图3
（3）其他各组数据的计算：
进行一个“神操作”，即计算截止各“日期”对应的“累计应付”。
具体操作：用光标选中2011年12月31日对应的“累计应付”的单元表格（指第二组数据的计算结果），即“600000.00”，然后将光标移至该单元表格的右下角（见红色圆圈圈定的位置），此时光标变成“＋”，然后按住鼠标，拖行至序号为“17”所在行对应的“累计应付”的单元表格后，松开鼠标，就完成截止“日期”对应的“累计应付”的全部计算。具体操作见图4、图5和图6。
图4
图5
图6
上述操作完成后，对Ｅ列的数据进行求和并将结果显示在“复核”处的单元表格中，其和为“4200000”,与“利息截止日”的“累计应付”数据一致，说明此“神操作”的计算结果正确，见图7。
图7
2、计算各期“累计还款”
计算公式及操作方式同各期“累计应付”，具体操作见图8、图9?图10?图11。
图8
图9
图10
图11
3、计算各期的“累计欠款”
（1）第一组数据：
点击序列号“1”所在行的“累计欠款”，然后键入“＝”，点击该行的“累计应付”（显示“Ｆ2”），键入“－”，点击该行的“累计还款”（显示“Ｈ2”），录入完该单元表格显示“＝Ｆ2-Ｈ2”，然后回车，就可以得出2011年7月7日对应的“累计欠款”。具体操作见图12和图13。
图12
图13
（2）计算其他各组数据：
用神操作进行，见图14。
图14
（3）数据复核：
用光标点击“复核”所在行与“累计欠款”相交叉的单元表格，然后键入“＝”，点击该行的求和后的“累计应付”，键入“－”，点击该行求和后的“累计欠款”，操作完显示“＝Ｅ19-Ｇ19”，然后回车，即得出截止利息截止日累计欠款金额为3370000元，该结果与通过神操作计算的结果一致，说明该神操作计算正确。见如下图15。
图15
4?计算各期间的“欠款天数”
计算方式和操作与《利用Excel计算欠款利息损失（适用多笔欠款）》相同，见图16和图17：
图16
图17
5?计算各期间的“利息金额”
计算方式和操作与《利用Excel计算欠款利息损失（适用多笔欠款）》相同，见图18和图19：
图18
图19
6?计算“利息合计”与“本息合计”
计算方式和操作与《利用Excel计算欠款利息损失（适用多笔欠款）》相同，见图图20、21和图22：
图20
图21
图22
三、计算说明
1?同一时点的“累计应付”减“累计还款”，就等于该时点的“累计欠款”，明确了一个期间的固定欠款金额和固定利率标准，就满足了计算该期间利息的条件。
2、其他说明与《利用Excel计算欠款利息损失（适用多笔欠款）》相同。
Excel计算利息损失，重点在于逻辑公式设计。为了避免因公试使用错误导致计算结果错误，希望初学着务必随时对一个不熟悉的操作进行反复校验，以保证不正确的操作能够及时被发现并进行改进。
…
