SHOW EXCEL FORMULA FOR YELLOW BOXES AND ANSWER QUESTIONS

Ann would like to buy a house.

It costs $$2,500,000.$

Her down payment will be $$50,000.$

She will take out a mortgage for the remainder.

It will be a $30$ year, fully amortizing, FRM$,$ with constant monthly payments and monthly compounding.

The annual interest rate is $4\mathrm{.}00\%.$

She will pay $$5,000$ in closing costs at origination.

She will also pay $1\mathrm{.}75\%$ of the balance in buy$-$down points at origination.

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.

$2.$ Fill in the spreadsheet $($sheet $$FA AMORTIZATION SCHEDULE$)$ for Ann.

$($It is called an amortization schedule or amortization calendar.$)$

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

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

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

$(3.$c$)$ Why?