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)
daily.head(10)
|
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))
payment_days_only.head()
|
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 |
|
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 |