Example input:
Example Output Created using code below:
Code that gets me from one to the other:
# Add relevant packages
import pandas as pd
pd.options.display.float_format = '{:.2f}'.format
import numpy as np
import datetime as dt
from collections import OrderedDict
from dateutil.relativedelta import relativedelta
%matplotlib inline
# SAMPLE DATA
df = pd.DataFrame({
'balance': {1: 1500, 2: 700},
'freq': {1: 999, 2: 999},
'maturity': {1: '2018-01-31', 2: '2018-01-31'},
'period': {1: 'months', 2: 'months'},
})
def dmd_flow(balance, start_date, num_periods, run_off_rate, new_funds_rate, int_rate, period='monthly'):
"""
Implements the cash flow modeling for demand products
Arguments:
balance -- initial balance of product at t0
start_date -- calendar date of t0
num_periods -- number of period to model
run_off_rate -- annualized rate at which deposit runs off- straight line
new_funds_rate -- annualized rate at which funds increase (net)
int_rate -- rate paying on deposit which would be used if modeling interest payable
period -- the period used for modeling i.e. monthly, annual
Returns:
liq_sched -- a schedule of liquidity based on the inputs ordered by time (ordered dictionary)
"""
p=1
b_bal = balance
e_bal = balance
current_date = start_date
while p <= num_periods:
if period == 'monthly':
e_bal = round(b_bal - (balance*1/12*run_off_rate), 4)
e_bal += round((balance*1/12*new_funds_rate), 4)
if period == 'annual':
e_bal = round(b_bal - (balance*run_off_rate), 4)
e_bal += round((balance*new_funds_rate), 4)
yield(OrderedDict([('Month', current_date),
('Period', p),
('Beg bal'Bal', b_bal),
('End Bal', e_bal),
]))
p += 1
current_date += relativedelta(months=+1, day=31)
b_bal = e_bal
def create_timeseries_df(input=None, func=None, args=(dt.date(2018,1,31), 0, 0, 0, 0, 'monthly')):
'''takes a series as an input and applies the fuctions to create the timeseries in the right shape'''
new_df = input['balance'].apply(
func, args=args)
return pd.DataFrame([o_dict for gen in new_df for o_dict in gen])
out_df = create_timeseries_df(df, func=dmd_flow, args=(dt.date(2018,1,31), 12, 0, .10, .03, 'monthly'))
out_df.groupby(['Month', 'Period'])[['Beg bal'Bal', 'End Bal']].sum()
# SAMPLE DATA
df = pd.DataFrame({
'balance': {1: 1500, 2: 700},
'freq': {1: 999, 2: 999},
'maturity': {1: '2018-01-31', 2: '2018-01-31'},
'period': {1: 'months', 2: 'months'},
})
Example input DF (can use sample DF included above, and run in order to get the resulting output:
Example Output Created using code:
