Equity to Offset Home Purchase Transaction Costs

import datetime as dt
import pandas as pd
import matplotlib.pyplot as plt

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

15-Year, $250K Mortgage

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

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

payment_day_15_year = (daily_15_year[(daily_15_year['paid_principal']!=0)|
                                     (daily_15_year.index==0)]
                       .reset_index(drop=True))

payment_day_15_year.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

30-Year, $250K Mortgage

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

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

payment_day_30_year = (daily_30_year[(daily_30_year['paid_principal']!=0)|
                                     (daily_30_year.index==0)]
                       .reset_index(drop=True))

payment_day_30_year.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 249620.150685 0.0 379.849315 743.150685 379.849315
2 2000-03-01 249191.299871 0.0 428.850814 1437.299871 808.700129
3 2000-04-01 248809.046612 0.0 382.253259 2178.046612 1190.953388
4 2000-05-01 248401.798664 0.0 407.247948 2893.798664 1598.201336

Calculate

Assume that the total transaction costs associated with both transactions required to obtain and then sell a home (both the purchase and the subsequent sale) together amount to ~10% of the total value of the home.

How long would it take to accrue 10% equity (and therefore be “in the money”) for a 15-year mortgage? A 30-year mortgage?

  • 15-Year: 23 months
  • 30-Year: 59 months
percent_to_break_even = 0.10
mort_15_breakeven_date = (
    payment_day_15_year[payment_day_15_year['cum_paid_principal'] > percent_to_break_even * 250000]
    .head(1)
    ['date']
    .values[0]
    .astype(str)[:10])
print(mort_15_breakeven_date)
index = payment_day_15_year[payment_day_15_year['date']==mort_15_breakeven_date].index
payment_day_15_year[index.values[0]-2:index.values[0]+3]
2001-12-01
date principal interest paid_principal cum_paid_interest cum_paid_principal
21 2001-10-01 227131.166559 0.0 1130.357260 14658.166559 22868.833441
22 2001-11-01 226019.337287 0.0 1111.829272 15333.337287 23980.662713
23 2001-12-01 224882.529901 0.0 1136.807386 15983.529901 25117.470099
24 2002-01-01 223764.016326 0.0 1118.513575 16652.016326 26235.983674
25 2002-02-01 222642.177854 0.0 1121.838472 17317.177854 27357.822146
mort_30_breakeven_date = (
    payment_day_30_year[payment_day_30_year['cum_paid_principal'] > percent_to_break_even * 250000]
    .head(1)
    ['date']
    .values[0]
    .astype(str)[:10])
print(mort_30_breakeven_date)
index = payment_day_30_year[payment_day_30_year['date']==mort_30_breakeven_date].index
payment_day_30_year[index.values[0]-2:index.values[0]+3]
2004-12-01
date principal interest paid_principal cum_paid_interest cum_paid_principal
57 2004-10-01 225645.052294 0.0 472.524778 39656.052294 24354.947706
58 2004-11-01 225192.805395 0.0 452.246899 40326.805395 24807.194605
59 2004-12-01 224717.620314 0.0 475.185080 40974.620314 25282.379686
60 2005-01-01 224262.616528 0.0 455.003786 41642.616528 25737.383472
61 2005-02-01 223806.260197 0.0 456.356332 42309.260197 26193.739803

Visualize

def create_loan_payoff_plot(d, title, breakeven_date):
    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)
    
    ax.set_xlim([dt.date(1998,1,1),dt.date(2032,1,1)])
    
    plt.title(title)
        
    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.axvline(breakeven_date,
               color='k',
               linewidth=0.75)
    ax.axhline(25000,
               color='k',
               linewidth=0.75)
    
    ax.legend()

15-Year, $250K Mortgage

create_loan_payoff_plot(daily_15_year, 
                        '15-Year, $250K Mortgage',
                        pd.to_datetime(mort_15_breakeven_date))

png

30-Year, $250K Mortgage

create_loan_payoff_plot(daily_30_year,
                        '30-Year, $250K Mortgage',
                        pd.to_datetime(mort_30_breakeven_date))

png