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


No comments:

Post a Comment