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.
Want to find out how the choice between 15year and 30year FRM impacts available equity and projected mortgage payout amount?
Go Here:
http://www.dailymathproblem.com/2017/09/houseequityandmortgagepayoffspread.html
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*(1B9)+B13

Forecasted Mortgage Payoff Amount

$472,025

=B13/(1B10)

Want to find out how the choice between 15year and 30year FRM impacts available equity and projected mortgage payout amount?
Go Here:
http://www.dailymathproblem.com/2017/09/houseequityandmortgagepayoffspread.html
No comments:
Post a Comment