# Calculating a Loan Balance Over Time

import datetime as dt
import pandas as pd


### Create Data

def loan(date, balance, rate, payment, payment_day):
day_list = [(date, balance, 0, 0, 0, 0)]

while day_list[-1][1] > 0:
date               = day_list[-1][0]
principal          = day_list[-1][1]
interest           = day_list[-1][2]
cum_paid_interest  = day_list[-1][4]
cum_paid_principal = day_list[-1][5]

new_principal = principal
new_interest  = interest + principal * rate / 365
new_date      = date + dt.timedelta(1)

if new_date.day == payment_day:
if new_principal - (payment - new_interest) > 0:
paid_principal = payment - new_interest
else:
paid_principal = new_principal

new_principal      -= paid_principal
cum_paid_interest  += new_interest
cum_paid_principal += paid_principal

if payment > new_interest:
new_interest = 0
else:
new_interest -= payment
else:
paid_principal = 0

day_list.append((new_date, new_principal,
new_interest, paid_principal,
cum_paid_interest, cum_paid_principal))
d = pd.DataFrame(day_list,
columns=['date', 'principal', 'interest',
'paid_principal', 'cum_paid_interest',
'cum_paid_principal'])
d['date'] = pd.to_datetime(d['date'])
return d

start_date  = dt.date(2000,1,1)
balance     = 250000
annual_rate = 0.035
payment     = 1787
payment_day = 1

daily = loan(start_date, balance, annual_rate, payment, payment_day)


date principal interest paid_principal cum_paid_interest cum_paid_principal
0 2000-01-01 250000.0 0.000000 0.0 0.0 0.0
1 2000-01-02 250000.0 23.972603 0.0 0.0 0.0
2 2000-01-03 250000.0 47.945205 0.0 0.0 0.0
3 2000-01-04 250000.0 71.917808 0.0 0.0 0.0
4 2000-01-05 250000.0 95.890411 0.0 0.0 0.0
5 2000-01-06 250000.0 119.863014 0.0 0.0 0.0
6 2000-01-07 250000.0 143.835616 0.0 0.0 0.0
7 2000-01-08 250000.0 167.808219 0.0 0.0 0.0
8 2000-01-09 250000.0 191.780822 0.0 0.0 0.0
9 2000-01-10 250000.0 215.753425 0.0 0.0 0.0
payment_days_only = (daily[(daily['paid_principal']!=0)|
(daily.index==0)]
.reset_index(drop=True))


date principal interest paid_principal cum_paid_interest cum_paid_principal
0 2000-01-01 250000.000000 0.0 0.000000 0.000000 0.000000
1 2000-02-01 248956.150685 0.0 1043.849315 743.150685 1043.849315
2 2000-03-01 247861.453405 0.0 1094.697280 1435.453405 2138.546595
3 2000-04-01 246811.247041 0.0 1050.206365 2172.247041 3188.752959
4 2000-05-01 245734.251998 0.0 1076.995043 2882.251998 4265.748002
payment_days_only.tail()


date principal interest paid_principal cum_paid_interest cum_paid_principal
177 2014-10-01 5434.921171 0.0 1766.284204 71733.921171 244565.078829
178 2014-11-01 3664.077033 0.0 1770.844138 71750.077033 246335.922967
179 2014-12-01 1887.617528 0.0 1776.459504 71760.617528 248112.382472
180 2015-01-01 106.228665 0.0 1781.388863 71766.228665 249893.771335
181 2015-02-01 0.000000 0.0 106.228665 71766.544441 250000.000000

### Visualize

import matplotlib.pyplot as plt

def create_loan_payoff_plot(d):
fig = plt.figure(figsize=[11,8.5])

ax = fig.gca()
ax.grid(True)

ax.spines['left'].set_visible(False)
ax.spines['top'].set_visible(False)
ax.spines['right'].set_visible(False)
ax.spines['bottom'].set_visible(False)

plt.tick_params(
axis='y',
left=False)
plt.tick_params(
axis='x',
bottom=False)

plt.title('$250K, 15-Year Mortgage,$1787 Payment')

ax.plot(d['date'],
d['principal'] + d['interest'],
linewidth=1,
label='Loan Balance')

ax.plot(d['date'],
d['cum_paid_principal'],
linewidth=1,
label='Equity')

ax.plot(d['date'],
d['cum_paid_interest'],
linewidth=1,
label='Cumulative Paid Interest')

ax.legend()
create_loan_payoff_plot(daily)


payment_days_only[payment_days_only['date'].dt.month==1]


date principal interest paid_principal cum_paid_interest cum_paid_principal
0 2000-01-01 250000.000000 0.0 0.000000 0.000000 0.000000
12 2001-01-01 237123.291684 0.0 1078.919455 8567.291684 12876.708316
24 2002-01-01 223764.016326 0.0 1118.513575 16652.016326 26235.983674
36 2003-01-01 209929.592813 0.0 1159.515937 24261.592813 40070.407187
48 2004-01-01 195603.121594 0.0 1201.976626 31379.121594 54396.878406
60 2005-01-01 180786.297163 0.0 1245.890620 38006.297163 69213.702837
72 2006-01-01 165423.288949 0.0 1291.423389 44087.288949 84576.711051
84 2007-01-01 149513.865994 0.0 1338.575619 49621.865994 100486.134006
96 2008-01-01 133038.594012 0.0 1387.404907 54590.594012 116961.405988
108 2009-01-01 115990.346533 0.0 1437.932376 58986.346533 134009.653467
120 2010-01-01 98322.746527 0.0 1490.295478 62762.746527 151677.253473
132 2011-01-01 80026.764569 0.0 1544.520973 65910.764569 169973.235431
144 2012-01-01 61080.051054 0.0 1600.675102 68408.051054 188919.948946
156 2013-01-01 41465.334084 0.0 1658.809054 70237.334084 208534.665916
168 2014-01-01 21147.109613 0.0 1719.028057 71363.109613 228852.890387
180 2015-01-01 106.228665 0.0 1781.388863 71766.228665 249893.771335