Friday, September 22, 2017

How to calculate house equity and mortgage payoff amounts?

Question:   A person buys a house and plans to either sell and move or pay off the mortgage in ten years.   Create a spreadsheet that will provide estimates of the amount of house equity after the sale and move and the amount it takes to pay off the mortgage when funds for the mortgage payoff are obtained from fully taxed disbursements from a 401(k) plan.

Assumptions:  The assumptions for the spreadsheet -- price of house, downpayment  percentage, initial loan balance, mortgage term, house appreciation rate, mortgage interest rate, years person owns home, cost of selling and moving as percent of house sale price, and tax rate on disbursements from the 401(k) plan -- are presented in b2 through b9.


Calculations:   Outcome of calculations are presented in b10 through b15 with formulas in c10 through c15.

Spreadsheet Assumptions and Calculations:


Col A -- Label
Col B -- input or output
Col C  -- comment
Purchase Price of House
$500,000
Input number in b2
Down payment percentage
0.9
Input number in b3
Initial Loan Balance
$450,000
Input number in b4
Mortgage Term
30
Input number in b5
House appreciation rate
3.0%
Input number in b3
Mortgage Interest Rate
4.0%
Input number in b6
Years person owns house
12.00
Input number in b7
Cost of selling and moving to a new home as % of house value
9.0%
Input number in b8
Tax Rate on Disbursements from 401(K) Plan
30.0%
Input number in b9
Expected house value at sale date
$712,880
=B2*(1+B6)^B8
Mortgage Payment
($2,148)
=PMT(B7/12,B5*12,B4,0,0)
Mortgage Balance on Date of House Sale
($330,418)
=FV(B7/12,B8*12,B12,B4,0)
House Equity after Selling and Moving Costs
$318,303
=B11*(1-B9)+B13
Forecasted Mortgage Payoff Amount
-$472,025
=B13/(1-B10)




Want to find out how the choice between 15-year and 30-year FRM impacts available equity and projected mortgage payout amount?

Go Here:
http://www.dailymathproblem.com/2017/09/house-equity-and-mortgage-payoff-spread.html


Thursday, September 21, 2017

House Equity and Mortgage Payoff Spread Sheet


House Equity and Mortgage Payoff Spread Sheet

The Issue: Your client, nearing retirement is planning to purchase a house.  At retirement, she will either sell the house and take equity or pay off the entire mortgage.

Your client has no liquid assets outside her 401(k) plan.  Hence, the mortgage will be paid off with 401(k) disbursements which are fully taxed at ordinary income tax rates.  There are costs associated with moving to a new home, which impact the amount of house equity that your client will receive if she decides to sell and move.      

Build a spread sheet that provides estimates of available house equity and mortgage payout for both a 15-year and 30-year fixed rate mortgage. Design the spread sheet so it is easily modified for different financial transactions and situations.

Comment on advantages and disadvantages of the different mortgage terms for this older buyer.

Your Client’s Information:   Information on the house purchase is presented in the table below.  

The 30-year FRM analysis is on the left and the 15-year FRM analysis is on the right.

The mortgage interest rate for the the 15-year FRM is 3.3 percent 0.7 percentage points less than the interest rate on the 30-year FRM.  This differential is typical of the market.



Assumptions and Input for
 Mortgage Choice Question
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
assumption
3.0%
3.0%
Mortgage Interest Rate
4.0%
3.3%
Years person owns house
12.0
12.0
Cost of selling and moving to a new home as % of house value
assumption
9.0%
9.0%
Tax Rate on Disbursements from 401(K) Plan
assumption
30.0%
30.0%


The only difference in the two scenarios is the mortgage term – 30 years versus 15 years. 

Forecasted House Equity and Mortgage Payoff Amounts:

The bottom two rows of the table provide information on forecasted house equity after selling and moving costs and forecasted mortgage payoff amounts for your client for a 30-year and 15-year mortgage.


House Equity and Mortgage Payoff Calculations

30-year
FRM
15-year
FRM
Expected house value at sale date
$712,880
$712,880
Mortgage Payment
($2,148.37)
($3,172.96)
Mortgage Balance on Date of House Sale
($330,417.79)
($108,612.27)
House Equity after Selling and Moving Costs
$318,303
$540,109
Forecasted Amount Needed to Retire Mortgage Including Taxes on 401(k) Disbursements
-$472,025
-$155,160



Discussion of Results:  

The older worker who brings a larger mortgage balance into retirement is almost certainly going to have to sell her house and move. 

One of the factors driving the results presented here is the person pays off the mortgage from 401(k) funds, which are fully taxed at ordinary income tax rates.

The use of a 15-year mortgage substantially decreases the amount needed to pay off the mortgage.


Appendix: Describing the House Equity and Mortgage Payoff Calculations:


The description of the documented spread sheet for the available house equity calculation and the mortgage payoff calculation are available at the link below.

Go Here:
http://www.dailymathproblem.com/2017/09/how-to-calculate-house-equity-and.html


Concluding Thoughts   The use of a 15-year FRM substantially increases the probability your client will be able to stay in her home after retirement.


However, the higher payment on the 15-year FRM will require your client to reduce contributions to her 401(k) plan.    Spread sheets which examine issues pertaining to 401(k) growth will be available shortly.