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))
30-Year, $250K Mortgage
create_loan_payoff_plot(daily_30_year,
'30-Year, $250K Mortgage',
pd.to_datetime(mort_30_breakeven_date))
