最近朋友们聚餐，聊到七八年前的老东家资金管理部门希望能根据贷款的金额，利率，结合之后的还款、利率调整等各种变动，自动计算各个时间段的利息，利息数据能够用来复核，或者按照年统计来方便预算的时候使用。
想法很好，但是目前老东家采用Excel来统计和计算，无法动态的生成数据。朋友说有没有解决方案。Excel的固有问题之一就是无法进行有效的延展，用程序来写就简单多了。以下就按照业务介绍和需求、表设计、实现三个步骤来解决这个问题。
业务介绍 - 贷款及利息计算
日常生活中除了财务和资金人员之外，普通人遇到的最普遍的应该就是住房按揭贷款了，还款方式和利息是有公式进行计算的，自己按月还钱就是了，很少会去考虑要根据不同的起始日期计算利息。
我们今天说的是银行向企业发放的贷款，为了避免太多的财务专业名词冲淡主题，尽量用通俗的语言介绍。
一笔贷款的总金额由各个借据的金额组成
一笔贷款银行可能会一次性发放给企业，也可能设定一个累计放贷的总额，然后根据企业的用款需求，慢慢的在一定的期间内由银行逐步发放给企业。
因此，一笔贷款的金额，实际上是由各次发放的贷款累计起来的，在银行的系统中和财务实践的操作中，具体一次发放的金额和对应的利率，是企业和银行通过借据的形式来确定的。借据上会记录金额、该金额对应的利率、发放时间（实际是以贷款资金到账时间确定）。
我们可以得到第一个重要结论：
借据是贷款组成的最小单元
假如一笔贷款下边有多个借据，有细心的朋友就会想到，那我归还贷款的时候，是不是还要把归还的金额也分拆到各个借据上。确实是这样，在企业归还贷款的时候，会和银行协商好，将还款的金额分配到具体的借据中。在归还之后，就会有一批借据的本金发生变化。无论何时，一笔贷款下所有借据的本金金额合计，就是企业的该笔贷款欠款金额。
借据利率会有变化，每张借据单独计息
由于利率会因国家政策、企业和银行的商谈而产生变更，因此每个借据在放款时的利率会单独根据实际情况确定。
利率在发放之后依然会变化，不同银行的调整利率的时点有所不同，有的是根据每笔借据发放时间每满一年调整，有的是固定每年某个日期。调整的幅度会根据LPR或者双方商定的因素；可能会调整，也可能不会调整。
每笔借据在银行的电子系统内，是单独根据自己的金额和利率来计息的。根据借据当前的金额、利率进行每日计息，然后在每年3、6、9、12四个月的20日左右，银行会按照季度累计的利息金额，向贷款人收取贷款利息。
我们得到第二个重要结论：
每笔借据需要单独计算利息，借据的利息合计就是整笔贷款的利息
寻找解决方案
对业务有所了解之后，我们很明确的将目标聚集到了借据这一个业务单元上。
精确描述问题
我们干财务的有句话都经常挂在嘴边，就是一定要了解业务。实际问题的解决方案本身就可能存在于业务中。
现在重新看一下我们的问题：计算一笔贷款在任意时间段内的利息金额
可以转换成：计算该笔贷款下所有借据在任意时间段内的利息金额
这个问题抽象成通用问题就是：计算任意一个借据在任意时间段内的利息金额
这就是我们要解决的问题。
核心解决逻辑
利息如何计算，很显然，任意时间段内的利息 = 本金 X 利率 X 时间因素
举个例子，欠款100万，利率4%，那么半年的利息就应该100*4%然后乘以0.5，如果是一个季度，就应该乘以0.25（这里只是大概说明，实际每个季度因为计息天数不同，利息并不相同）。
上述计算是在本金和利率不变的情况下，假如任意一个时间点本金和利率发生变化，那么在那个时间点之后的时间内，就应该采用新的本金和新的利率进行计算。
因此逻辑已经很清晰了，用伪代码的形式简单写一下就是：
针对一笔贷款中的每一个借据执行如下操作：
1.从某个时间点开始根据借据当前的金额、利率开始计息
2.每当金额或者利率发生变化，采用新的金额或者利率继续计息
3.直到计算到需要结束的时间点，累计出当前借据在起止时间内的利息
最后汇总每一个借据计算出的利息合计
具体怎么计算呢，其实答案就隐藏在前边，我们也学银行一样，按日计息。
表设计
进入到具体的步骤了，前边已经知道了计算利息需要本金、利率、时间因素三个重要的数据，但是到具体设计上，还是图看的明白：
这是某个贷款中的一张借据从放款到还款的一系列信息。
通过图可以看到，借据的两个关键数据本金和利率，会随着时间不断变化，各自形成一个序列，这两个序列在天然上就和另外一个重要的数据：时间产生关联。
那么我们把图再抽象一下，设计出如下表：
借据表用于存放借据生成（即该借据放贷时）时候的三要素：本金余额，利率，时点。
另外两个序列表，用于按照时间顺序存放本金余额和利率的变动情况，需要保存每一次变化后的当前本金余额，当前利率以及变化时点。
很显然还会有一个一对多贷款～借据关系，但是我们的问题已经集中到计算一个借据的利息上。这里为了简单就不再弄出来了。
实现
在实现的阶段，为了方便讲述核心逻辑，我继续做了一个简化，就是将时间段固定在初始放款时间 至 今天。未来自定义时间段也只不过是增加两个变量并设置相应的计算初始值而已，核心逻辑不会变。
整体思路是先获取借据以及对应的本金和利率变动序列的数据，然后从借据放款日开始逐天计算利息；随着日期不断推进，逐步将本金和利率按照变动序列更新为最新值，然后继续计算。最后将所有的利息累加起来即可。
简单来说，程序整体分为两部分：数据准备和运算
数据准备
我们用sample.xlsx文件中的note、balance_series、ir_series三张表来分别模拟借据、本金变动序列、利率变动序列这三张数据库表。
此处的思路是：
- 设置一些全局变量
- 打开sample.xlsx并读取某一张借据（note表中的某一行数据）的相关数据
- 读取该借据对应的本金变动序列和利率变动序列并做判断和准备
设置全局变量
依然采用Python的openpyxl库，而且由于要对时间进行操作，使用datetime库比较方便，当然硬核的也可以读取Excel字符串进行操作
import openpyxl
import datetime
# 银行每日计息的分割比例
base_tick = 1 / 360
# 日期间隔设置为1天
delta_date = datetime.timedelta(days=1)
# 设置利息总和变量
interest_sum = 0
这里简单解释一下，据我了解，银行每日计息会按照1/360的比例来计算，所以设置了一个每日的tick，每日利息就等于本金乘以利率乘以这个固定的比例。如果这个计息比例有做银行的朋友看到了，还望告知一下是不是这个比例。
日期间隔采用了datetime库中的timedelta，设置间隔为1天。Excel的日期格式单元格在openpyxl读入的时候会装配成datetime.datetime格式，由于我们对具体时间不关心，只关心到日期，所以在后边读入的时候就会将其转换成datetime.date类型。date类型可以与timedelta进行加减，也可以与其他date类型判断相等，非常适合基于日期的计算。Excel中日期数据类型的处理，可以看我的视频 - Python+Openpyxl 真实财务开发教学。
最后就是设置一个利息总和，也是这个程序最终输出的结果。
读取数据
这里就是从打开sample.xlsx开始，逐步的将数据读入到设置的变量中。
先来读取借据的信息：
# 打开sample.xlsx工作簿及三张工作表
wb = openpyxl.open("sample.xlsx")
note = wb["note"]
balance_series = wb["balance_series"]
ir_series = wb["ir_series"]
# 当前借据的id，用于从两个序列表中读入对应的序列
current_note_id = note.cell(2, 1).value
# 读入借据01的起始金额，起始日期，起始利率
start_balance = note.cell(2, 2).value
start_date = note.cell(2, 3).value.date()
start_ir = note.cell(2, 4).value
这里将借据的起始放款金额、起始日期=放款日期，起始利率读入到三个变量中，还读了借据的id，下一步就是用借据的id从两张序列表中读取序列的信息：
# 读入序列的函数，用借据id到两张序列表中将属于该借据的数据读出来并包装成一个列表
# 由于本金变动序列和利率变动序列的数据前四列相同，可以采用同一个函数读取
# Web应用中是通过序列表关联到借据的外键进行查询
def load_series(note_id, worksheet):
# 获取序列的名称
name = worksheet.cell(1, 3).value
# 设置空列表作为返回数据
series = []
# 获取工作表最大行数
max_row = worksheet.max_row
# 从工作表的第二行到最大行进行迭代
for i in range(2, max_row + 1):
# 将所有外键id等于借据id的那一行的本金/利率和对应的时间读取之后包装成一个元组，再将元组加入到seires列表中。读入的时间全部转换成date类型
if worksheet.cell(i, 2).value == note_id:
series.append((worksheet.cell(i, 3).value, worksheet.cell(i, 4).value.date()))
# 返回读到的数据
return series
# 实际读入借据对应的序列并赋给两个变量
note_balance_series = load_series(current_note_id, balance_series)
note_ir_series = load_series(current_note_id, ir_series)
# 判断本金序列和利率序列是否需要更新
# 如果读入的序列长度为0，则整个计算过程中无需更新该序列的值，所以可以设置两个布尔变量用来标记两个序列是否需要更新
is_balance_update = len(note_balance_series) != 0
is_ir_update = len(note_ir_series) != 0
# 在需要更新的情况下，设置序列的指针，实际上不更新不需要设置
# 这里为了简便都设置为0，在后续代码中先判断布尔值，如果为False无需使用对应变量
balance_series_pointer = 0
ir_series_pointer = 0
这里采用了一个函数来从序列中加载该借据对应的本金和利率变动序列到两个列表中，然后判断两个列表的长度，并且用两个pointer指向两个列表的第一个元素，以供后续时间不断推进的时候使用。
到这里我们就做好了数据上的准备，开始来进行运算了。
运算逻辑
整体逻辑用伪代码写一下就是：
1. 确定开始和结束日期，并确定当前本金、当前利率、当前日期三个重要变量（在例子里当前日期是固定为借据初始日期=放款日期，所以当前利率=初始利率，当前本金=初始本金，结束日期固定为今天）
2. 开始一个while循环，在当前本金=0或者当前日期等于今天的情况下退出循环，否则一直执行循环
3. 循环体内部的逻辑:
3.1 计算当前利息 = 当前本金 * 当前利率 * base_tick
3.2 累加当前利息到interest_sum上
3.3 将当前日期向后推进1天
3.4 根据本金变动序列和利率变动序列的布尔值确定是否要进行更新
3.5 如果需要更新，按照日期在序列中寻找到对应的最新的本金/利率数据，将当前本金/当前利率更新为最新值
3.6 更新后将两个序列指针+1，如果已经指向最后一个元素，则保持不变。
3.7 继续执行循环
4 循环结束后interest_sum为想要的结果
现在来看具体实现：
# 确定开始日期对应的三个重要变量
# 设置当前金额，当前利率，当前日期为借据的初始数据
current_balance = start_balance
current_date = start_date
current_ir = start_ir
# 获取今天的日期作为结束日期
end_date = datetime.date.today()
# 启动核心逻辑循环
while current_date != end_date and current_balance != 0:
# 计算当天的利息并累加到利息总和上
interest_sum = interest_sum + current_ir * current_balance * base_tick
# 让日期前进一天
current_date = current_date + delta_date
# 判断本金序列是否需要更新
if is_balance_update:
# 比较当前日期与本金序列指针指向的日期
if current_date == note_balance_series[balance_series_pointer][1]:
# 如果相同，更新当前剩余本金金额
current_balance = note_balance_series[balance_series_pointer][0]
# 更新成功后移动指针，如果指针已经到最后，不需要移动
if balance_series_pointer < len(note_balance_series) - 1:
balance_series_pointer = balance_series_pointer + 1
# 判断利率序列是否需要更新
if is_ir_update:
# 比较当前日期与利率序列指针指向的日期
if current_date == note_ir_series[ir_series_pointer][1]:
# 如果相同，更新当前利率
current_ir = note_ir_series[ir_series_pointer][0]
# 更新成功后移动指针，如果指针已经到最后，不需要移动
if ir_series_pointer < len(note_ir_series) - 1:
ir_series_pointer = ir_series_pointer + 1
运行结束之后，interest_sum就是最后的结果，即该借据在开始和结束日期之间一共应该计息多少。
代码及示例文件
完整代码和示例Excel文件位于https://github.com/minkolee/Interest，里边针对过程变量添加了大量的print语句，方便运行时理解程序逻辑。
后记
程序虽短，五脏俱全。写完之后还有一些针对这个程序的后续思考记录如下。
本金和利率变动序列需要排序
为了突出核心逻辑，Excel表中的本金和利率变动序列已经按照时间正序排放好了。日常维护数据如果是及时维护并且不出错的话，应该是已经按照时间正序排列好的，但实际操作中如果有大量修改删除等操作，在取变动序列的时候，应该将列表内的元组按照其时间正序进行排序再使用。无论数据库还是用Python操作Excel，一维的排序是个很简单的事情，所以就省略了。
自定义起始时间和结束时间
现在程序中起始时间固定为放款时间，所以对应数据从借据表中直接取初始值。
假如自定义起始时间，只需要先进行一些操作和判断：
- 读取序列的时候不再是只读取后来变化的部分，要组装包括初始数据在内的序列，而不能仅仅只读取后来变化部分的序列。
- 开始日期是否早于放款日，早于的话强制将当前本金设置为0，当前利率随便设置，反正会在到达放款日的时候更新为初始利率。
- 开始日期晚于放款日的情况，在序列中找到自定义时间第一次晚于序列中某个日期时那个日期对应的本金和利率作为当前数据，继续向后计算
- 核心逻辑的while条件修改为当时间为自定义终止时间时结束。
结束时间也类似，结束时间如果晚于本金变为0的时间，利息也全是0。
依然可以从自定义起始时间到结束时间一天一天的全部算出来结果，将结果可以保存在一个列表中，再进行加工，按年，按月，都可以再分类汇总。
代码没什么难度，有兴趣的朋友可以自行修改一下。
web应用
这种东西不做成web应用就没太大意义，只能个人使用。而且Web系统相比Excel最核心的进步是不用再人工清洗数据，数据的可使用性得到了保证。
做成web应用的话，按层级让资金人员登记贷款信息，然后在贷款下创建借据信息，再在借据下维护本金和利率的变化信息即可。
本金的变化可以采取设置录入每次还款额，然后系统计算出本金余额写入数据库的形式，更符合资金人员的工作思路。
时间字段就可以做成选择框用于直接选择，而无需输入。
先计算利息还是先更新当前数据
在我的核心循环里，是先用当前数据计算了当天利息，再更新当前数据，用于继续下一个循环。
这种计算方式隐含的假设是：银行用上一天结束时的情况计算下一天利息。由于我没在银行工作过，也没有机会接触银行的系统，不知道这种计算方式是否符合实际情况。
如果银行用当天结束时的情况计算当天的利息，那么应该先更新数据，再进行计算，循环内计算和更新数据的步骤需要颠倒一下。
终极解决方案
对于博主经常做的地产项目贷款来说，一笔贷款的借据量还是挺多的，而且还款时间长，次数多。由于贷款存续期长，利率调整的次数也很多。一笔贷款里少则几十张，多则百张借据，每次利率一变，一还款就是几十上百信息需要维护。
还款企业可以实时知道，而利率调整企业如果不是实时跟踪，无法第一时间知道。一般都是拿到计息单才知道，日常维护数据的工作量还是很大的。
因此终极方案就是：
让银行开放系统的api端口，做好Web界面甚至是定制化服务，供企业查询。
只不过，终极的解决方案需要终极的成本。