In the sheet titled $$PA AMORTIZATION SCHEDULE$$ assume all is the same as before EXCEPT there is a $$500,000$ balloon

due in $30$ years.

$8.$ Fill in the spreadsheet for Ann. $($It is called an amortization schedule or amortization calendar.$)$

$9.$ Compute Ann$$s annualized IRR for the mortgage in the spreadsheet. $($Use the net cash flow.$)$

$(9.$a$)$ What is the annualized IRR for the mortgage?

$(9.$b$)$ Is it higher or lower than the mortgage contract rate?

$(9.$c$)$ Why?

$10.$ Plot Ann$$s mortgage balance in one graph. Place the graph here.

$11.$ Plot Ann$$s monthly mortgage payment, interest payment and principal payment in one graph. Place the graph here.

$($note: don$$t include the final balloon payment in Ann$$s monthly mortgage payment$)$

She forecasts four possible scenarios for house price appreciation $($HPA$).$

Optimistic Case: $4\mathrm{.}5\%$ annual HPA, hence $4\mathrm{.}5/12\%$ monthly HPA

Base Case: $2\mathrm{.}5\%$ annual HPA, hence $2\mathrm{.}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

$12.$ Plot Ann$$s home equity every month under each of the four HPA scenarios in one graph. Place the graph here.

$13.$ Assume Ann will make the required monthly payment every month for $30$ years.

$(13.$a$)$ How much home equity will Ann have after $10$ years $(120$ months$)$ of payments under each of the four scenarios?

$(13.$b$)$ After $30$ years?

Scenario : HPA

Home Equity in $10$

years

Home Equity in $30$

years

Optimistic $4\mathrm{.}50\%$

Base $2\mathrm{.}50\%$

Pessimistic $0\mathrm{.}00\%$

Very Bad $-6\mathrm{.}00\%$