Ann would like to buy a house. It costs $800,000. Her down payment will be $40,0
ID: 2579648 • 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.
Note: the home is bought and the loan is taken in month 0, the first payment is due in month 1.
In the spreadsheet where it says “cash inflow”, “outflow” and “net cash flow” you should only take into account cash flow related to the mortgage.
1. Fill in the spreadsheet for Ann. (It is called an amortization schedule.)
2. Compute Ann’s annualized IRR for the mortgage in the spreadsheet. (Use the net cash flow.)
(2.a) What is the annualized IRR for the mortgage?
(2.b) Is it higher or lower than the mortgage contract rate?
(2.c) Why?
3. Plot Ann’s mortgage balance in one graph. Place the figure here.
4. Plot Ann’s monthly mortgage payment, interest payment and principal payment in one graph. Place the figure here.
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
5. Plot Ann’s home equity every month under each of the four HPA scenarios in one graph. Place the figure here.
6. Assume Ann will make the required monthly payment every month for 30 years.
(6.a) How much home equity will Ann have after 10 years (120 months) of payments under each of the four scenarios?
(6.b) After 30 years?
Scenario :
HPA
Home Equity in 10 years
Home Equity in 30 years
Optimistic
4.50%
Base
2.50%
Pessimistic
0.00%
Very Bad
-6.00%
Scenario :
HPA
Home Equity in 10 years
Home Equity in 30 years
Optimistic
4.50%
Base
2.50%
Pessimistic
0.00%
Very Bad
-6.00%
Explanation / Answer
2)
Cost of house = 800,000
Down payment = 40,000
Loan required = 800,000 - 40,000 = 760,000
2.5% fee is required at the time of the loan
Actual amount of mortgage = 760,000/(1 - 0.025) = 779,487.18
PV = 779,487.18, N = 360, r = 0.04/12 = 0.01/3, FV = 0
PV = 779,487.18 = (PMT/(0.01/3)) * (1 - 1/(1 + 0.01/3)^360) + 0
PMT = 3,721.39
Ann has to pay $3,721.39 every month but she received loan $760,000 for her house
CF0 = -760,000
CF1-360 = 3,721.39
PV = 760,000, PMT = 3,721.39, N = 360, FV = 0;
Compute r = 0.003509 per month or 0.042110 per year
Annual effective rate = 4.21%
Annualized IRR = 4.21%
Mortgage rate is 4% but because of initial fee of 2.5% which puts additional cost on borrower, the effective rate rises to 4.21%
6)
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.