Ann would like to buy a house. It costs $1,000,000. She will make an $80,000 down payment.
She will take out a mortgage for $920,000. It will be a 30 year, fully amortizing, fixed rate mortgage (FRM), with monthly payments and monthly compounding. The annual interest rate is 3.41%. She must pay 2% 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.
Ann’s net cash flow each month is equal to the total cash inflows minus total cash outflows that month.
She forecasts three possible scenarios for house price appreciation (HPA):
A. Optimistic Case: 9% annual HPA, hence 9/12% monthly HPA
B. Base Case: 4.5% annual HPA, hence 4.5/12% monthly HPA
C. Pessimistic Case: 0% annual HPA, hence 0/12% monthly HPA
1. Fill in the Excel spreadsheet for Ann (the amortization schedule).
2. Compute Ann’s annualized IRR from the mortgage in the spreadsheet (use the net cash flow).
3. What is the IRR? ___________________
a. Is it higher or lower than the mortgage contract rate?
b. Why?
4. Plot Ann’s mortgage balance in one graph. Place the graph here or on a separate tab on the Excel spreadsheet.
5. Plot Ann’s mortgage payment, interest payment and principal payment in one graph.Place the graph here or on a separate tab on the Excel spreadsheet.
6. Plot Ann’s home equity every month under each of the three HPA scenarios in one graph.Place the graph here or on a separate tab on the Excel spreadsheet.
7. Assume Ann will make the required monthly payment every month for 30 years. How much home equity will Ann have after 15 years (180 months) of payments under each of the three HPA scenarios?