- E$18.NPVISE2518$18) Investment Analysis $21.352.76452 1.50 $19.757.764 40 NPVIRR Loan Analysis f =E$18+NPV(SE32,F$18:$18) . D Selling Costs Loan Balance Repaid Loan Prepayment Penalty 2 Net Cash Flow Discount Rate NPV 0.00% 5.00% 10.00 15.00% 20.00% 25.00% 30,00% 35.00% 40.00% 45.00 50.00% 55.00% SSSSSSSSSSSSS$%%%% 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 SSSS 30 888 OON 888 (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: (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. - E$18.NPVISE2518$18) Investment Analysis $21.352.76452 1.50 $19.757.764 40 NPVIRR Loan Analysis f =E$18+NPV(SE32,F$18:$18) . D Selling Costs Loan Balance Repaid Loan Prepayment Penalty 2 Net Cash Flow Discount Rate NPV 0.00% 5.00% 10.00 15.00% 20.00% 25.00% 30,00% 35.00% 40.00% 45.00 50.00% 55.00% SSSSSSSSSSSSS$%%%% 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 8 SSSS 30 888 OON 888 (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: (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