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)
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
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)

png

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