Question

(3) Fill in the sheet titled “NPV-IRR”. Ann will buy the property in 2014, she will collect NOI for 5 years 2015-2019, and she will sell it in 2019.

Ann’s loan has a 5/4/3/2/1 prepayment penalty structure, so if she prepays in the first year, she will pay a penalty equal to 5% of the balance, in the second year she will pay a penalty equal to 4% of the balance etc.

Ann forecasts NOI will grow at 2% per year, compounded annually. Ann forecasts she can sell the property in 2019 at a 6.25% cap rate. Recall: Sale Price in 2019=NOI 2020/Cap rate 2019

(3.a) How much will Ann sell the property for in 2019?

(3.b) How much of a capital gain will Ann earn? (Hint: capital gain = sale price – purchase price)

(3.c) What is Ann’s IRR for this investment?

(3.d) If Ann’s discount rate is 25%, what is her NPV? Should she make this investment?

(3.e) Plot Ann’s NPV for discount rates 0%-100%. Copy and paste the chart below.

Investuent Analysis $11, 832, 852 0 Purchase Price 'urehase Costs 2016 2018 u Closing Cul Prepavenl Peral Purcliase Price Purchiase Cosls Gaing in cp ata 2014 Debt Service aynent ERIT C rate 201 Sellin Costs Capital Gai ellin: Cosls Loan Dslance Repa i Preaaytent Peralt of Net ash fl

Answer 1

Answer:-

Purchase costs Loan amount Loan closing costs Prepayment p Debt Service NOI 2015 Going in cap rate 2014 NOI growth rate Exit cap rate Selling costs 3% 10422261.54 1.65% 590490/3900%/ 1000% 599280.0 768447.00 6.50% 2% 6.25% 7.00% Year 2014 2015 2016 2017 2018 2019 Purchase Price Purchase Costs Loan Amount Loan closing costs 11590452.5 347713.575 10422261.5 171967.315 Net Operating income Debt service Payment 768447.00 599280.04 783815.94 799492.259 815482.104 831791.7461 599280.04 599280.04 599280.04 599280.04 Sale Price Selling costs Loan Balance Repaid Loan Prepayment Penalty 13308667.94 931606.76 10422261.54 104222.6154 NOI 2014 753379.4118 11590452.49 Purchase price Sale Price-NOI 2019 Cap rate 13308667.94 Net cash flow 1687871.84 169166.96 184535.90 200212.22 216202.06 2083088.73 IRR 13.13% Sale of Capital gain IRR NPV @ 25% No she should not make the investment as NPV is negative 13308667.94 1718215.45 13.13% 560783.76 3.b)

Formulas:-

NOI=NOI in previous year*(1+2%). Net Cash Flow for each year = -Purchase Price - Purchase Cost + Loan amount - Loan closing costs +NOI-Debt service+Sale price - selling costs-Balance Repaid-Penalty. IRR=IRR (Select cash flow cells,) NPV = NPV(25%, select cash flow cells for 2015 to 2019)+Cash flow for 2014