Ann would like to buy a house. It costs $800,000. Her down payment will be $40,0
ID: 2790647 • Letter: A
Question
Ann would like to buy a house. It costs $800,000. Her down payment will be $40,000. She will take out a mortgage for $760,000. It will be a 30 year, fully amortizing, FRM, with constant monthly payments and monthly compounding. The annual interest rate is 4.00%. She must pay 2.5% in fees at the time of the loan.
She forecasts four possible scenarios for house price appreciation (HPA).
Optimistic Case: 4.5% annual HPA, hence 4.5/12% monthly HPA
Base Case: 2.5% annual HPA, hence 2.5/12% monthly HPA
Pessimistic Case: 0% annual HPA, hence 0/12% monthly HPA
Very Bad Case: -6% annual HPA, hence -6/12% monthly HPA
1. Assume Ann will make the required monthly payment every month for 30 years.
(1.a) How much home equity will Ann have after 10 years (120 months) of payments under each of the four scenarios?
(1.b) After 30 years?
Explanation / Answer
Cost of the house: 800,000
Mortgage:760,000
Fees for the loan: 19,000
Cost the house-Fees for the Loan=800,000-19,000=781,000
First we will use the excel PMT schedule to find the monthly payments. The syntax for PMT equals:
=PMT(rate,nper,pv,fv,[type])
rate= monthly rate of interest=4/12=0.33%
nper= number of periods=30 years=360 months
PV= Present value of loan =760,000
FV= The value of loan in the future=0
Type=0 or 1. 0 means payments made at the end of the period while 1 means payment made at the beginning of the period. The default setting in excel is 0.
= pmt (0.33,360,-760,000,0,0)
=$ 3628.36
Therefore, the loan will require a monthly payment of $ 3819.32
Loan balance after any point in time can be found as PV of the remaining installments. The formula to do so is:
PV=PMT×(1-(1/(1+i)n)/i
Where PMT =monthly payments=$ 3628.36
i= monthly rate of interest =4/12=0.33
n=Number of loan payments remaining. So after 120 months this will be (360-120)=240 months. After 360 months this will be 0.
First Case (After 120 Months) loan balance due=3628.36×(1-(1/1.003333)240)/0.003333
=$ 598,758.09
Second Case (After 30 years or 360 months) loan balance due=3819.32×(1-(1/1.003333)0)/0.003333
=$ 0
Now let’s estimate the price of houses in all scenarios:
1a) After 120 months:
Optimistic Case: Value of House: 800,000×(1+0.00375)120=$ 1,253,594.22
Value of Equity: 1,253,594.22-598,758.09-19,000=$ 635,836.13
Base Case: Value of House: 800,000×(1+ 0.002083333)120=$ 1,026,953.23
Value of Equity: 1,026,953.23-598,758.09-19,000=$ 409,195.14
Pessimistic Case: Value of House: 800,000×(1+0)120=$ 800,000
Value of Equity: 800,000-598,758.09-19,000=$ $182,241.91
Very Bad Case: Value of House: 800,000 ×(1 -0.005)120=$ 438,389.03
Value of Equity: 438,389.03-598,758.09-19,000= -$ 179,369.06
1b) After 30 years or 360 months the loan balance due will be 0. And thus the value of your equity will just be the house value less the loan fees paid.
Optimistic Case: Value of House: 800,000×(1+0.00375)360 = $ 3,078,158.44
Value of Equity: 3,078,158.44-19,000= $3,059,158.44
Base Case: Value of House: 800,000 ×(1+ 0.002083333)360=$ 1,692,279.24
Value of Equity: 1,692,279.24- 19,000=$ $1,673,279.24
Pessimistic Case: Value of House: 800,000×(1+0)360=$ 800,000
Value of Equity: 800,000-19,000=$ $781,000
Very Bad Case: Value of House: 800,000 ×(1 -0.005)360=$ 131,643.39
Value of Equity: 131,643.39 -19,000= $112,643.39
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.