PLEASE SHOW STEP BY STEP AND SHOW FORMULAS THAT ARE USED IN EXCEL THANK YOU SO MUCH $(4)$ Fill in the sheet titled $$Loan Analysis$$ but ONLY include cash$-$flow related to Ann$$s loan in the sheet $$NPV$-$IRR$.$ Leave cells unrelated to the loan cash flow blank. $(4.$a$)$ What is the annualized IRR for Ann$$s loan? $(4.$b$)$ Is the IRR for Ann$$s loan higher or lower than the advertised loan contract rate? $(4.$c$)$ Why? $(4.$d$)$ Plot Ann$$s NPV for Ann$$s loan, for discount rates $0\%-10\%.$ Copy and paste the chart below. Inputs

Purchase Price $$10,004,529\mathrm{.}41$

Purchase Costs $3\mathrm{.}00\%$

Loan amount $$5,004,529\mathrm{.}41$

Loan Closing Costs $1\mathrm{.}65\%$

Prepayment Penalty $5\%/4\%/3\%/2\%/1\%/0\%$

Debt Service $$375,339\mathrm{.}71$

NOI $2024$ $$850,385\mathrm{.}00$

Going in cap rate $20238\mathrm{.}50\%$

NOI growth rate $2\mathrm{.}00\%$

Exit cap rate $20298\mathrm{.}00\%$

Selling Costs $7\mathrm{.}00\%$ Year $2023202420252026202720282029$

Purchase Price

Purchase Costs

Loan Amount

Loan Closing Costs

Net Operating Income $($NOI$)$

Debt Service Payment

Sale Price

Selling Costs

Loan Balance Repaid

Loan Prepayment Penalty

Net Cash Flow

IRR