A House Equity and Mortgage Payoff Spreadsheet:
Question: A person buys a house and plans to either sell and move or pay off the mortgage in twelve years.
The person is considering taking out a 15-year or a 30-year fixed rate mortgage.
The assumptions on the home purchase, house equity growth, the cost of selling and moving, and the cost of funds for the payoff of the mortgage are presented in the table below.
Table One: Assumptions for 30-year vs 15-year FRM Comparison:
| Label | 30-year FRM | 15-year FRM |
| Purchase Price of House | $500,000 | $500,000 |
| Down payment percentage | 0.9 | 0.9 |
| Initial Loan Balance | $450,000 | $450,000 |
| Mortgage Term | 30 | 15 |
| House appreciation rate | 3.0% | 3.0% |
| Mortgage Interest Rate | 4.0% | 3.3% |
| Years person owns house | 12.00 | 12.00 |
| Cost of selling and moving to a new home as % of house value | 9.0% | 9.0% |
| Tax Rate on Disbursements from 401(K) Plan | 30.0% | 30.0% |
Create a spreadsheet that provides estimates of house equity after the sale and move or mortgage payoff amounts after twelve years when the house buyer uses a 30-year FRM and when the house buyer uses a 15-year FRM
Base your mortgage payoff calculation on the assumption that the source of funds for the mortgage payoff are fully taxed funds from a 401(k) plan.
Spreadsheet:
http://wp.me/a2WYXD-4i
Results:
The results for the comparison of the 15-year and 30-year FRM for the assumptions presented in table one are presented in Table 2.
Table Two: Results for the 30-year vs 15-year FRM Comparison:
| 30-year FRM | 15-year FRM | |
| House Equity after Selling and Moving Costs | $318,303 | $540,109 |
| Forecasted Mortgage Payoff Amount | -$472,025 | -$155,160 |
Observations on the 30-year vs 15-year FRM comparison:
The person taking out the 15-year FRM mortgage has around $222,000 more in house equity at the end of the 12-year holding period.
The mortgage payoff calculation when funds are disbursed from a 401(k) plan includes tax on the disbursements. Inclusive of the tax bill, the mortgage payoff amount is $317,000 higher for the buyer who uses the 30-year FRM than for the buyer who uses the 15-year FRM.
Other Applications for the House Equity or Mortgage Payoff Spreadsheet:
Modify the mortgage payoff calculation to allow for a situation where funds for the mortgage payoff are obtained from three sources – (1) a savings account, (2) sales of common stock, and (3) disbursements from a 401(k) plan. Treat tax rates as an endogenous variable in the new model.
Compare results for both mortgage types under the 90% LTV assumption to results under an 80% LTV assumption.
Run the model on 15-year and 30-year FRMs for holding periods ranging from 1 to 15 years. How does the advantage of the 15-year FRM vary with holding period?
Authors Note: This problem was discussed further in the post below.
Essay Nine: Retire Mortgage Debt or Accumulate in Your 401(k) Plan:
https://financememos.com/2015/10/09/essay-nine-retire-mortgage-debt-or-accumulate-in-your-401k-plan/
Essay nine points out that many financial advisors stress accumulation of wealth in 401(k) plans rather than mortgage balance reductions even when their clients are nearing retirement. The major banks employing the same financial advisors issue mortgages and sponsor 401(k) plans. As a result, the interests of the financial advisors and the interests of their clients are not automatically aligned.
This approach can backfire when stock markets underperform nearing retirement.
During working years. the tax code favors people with large mortgages and people who are contributing to their 401(k) plan. However, after retirement the person who must disburse funds from a 401(k) plan often has a hefty tax bill.