Fill in the sheet titled NPV-IRR.

You will buy the property now (Year 0), collect NOI for 5 years Year 1-Year 5, and sell it at the end of Year 5.

Your loan has a 5/4/3/2/1 prepayment penalty structure, so if you prepay in the first year, you will pay a penalty equal to 5% of the balance, in the second year you will pay a penalty equal to 4% of the balance etc. Payoff the loan balance, the prepayment penalty and any transaction costs.

You forecast NOI will grow at 3% per year, compounded annually.

You forecast you can sell the property at the end of year 5 at a 5.25% cap rate.

Recall: Sale Price in Year 5 = NOI6cap rate5

(3.a) How much will You sell the property for in Year 5?

(3.b) What is Your IRR for this investment?

(3.c) What is your cash on cash return for Year 1? Recall that the cash on cash return is equal to your annual Net Cash Flow divided by your total cash investment at closing. The formula = cash on cash return = NCF1NCF0

For the following questions, change only the variable indicated. Keep the others as dictated above.

(3.d) What is your IRR if NOI growth is 5% compounded annually (all other variables held constant)?

(3.e) What is your IRR if your exit cap rate is 4.75% (all other variables held constant)?

(3.f) Complete the data table using the What-If Analysis in Excel.

A B D E F G H J K L 3 M z N Investment Analysis $ Year Year o Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 $ 1 Inputs 2 Purchase Price 3 Purchase Costs 4 Loan amount 5 Loan Closing Costs 6 Prepayment Penalty 7 Debt Service 8 NOI Year 1 9 Going in cap rate 10 NOI growth rate 11 Exit Cap Rate Year 5 12 Selling Costs 13 14 5,400,000.00 3.00% 3,761,443.17 2.00% 5%/4%/3%/2%/1%/0% 228,707.30 274,448.76 1.2 5.08% 3.00% 5.25% 5.00% Purchase Price Purchase Costs Loan Amount Loan Closing Costs $ $ Net Operating Income (NOI) Debt Service Payment Capital Gain $0.00 Sale Price Selling Costs Loan Balance Repaid Loan Prepayment Penalty 15 16 17 18 Sum of Net Cash flow $0.00 19 Net Cash Flow Cash on Cash Return IRR 20 21 22 23 24 IRR Based on Different NOI Growth and Exit Ca #NUMI NOI Growth 3% 1% 2% 4% 5% 25 4.50% 26 27 Exit Cap Rate 4.75% 5.00% 5.25% 5.50% 28 29 30 31 32 33 Breaking Down the Contribution of NOI and Terminal value to Investment Return Reversion End of Year PV of NOI (Terminal Value) PV of Rev 1 NOI 34 35 Discount Rate 8% 36 2 Total PV $0.00 $0.00 $0.00 $0.00 $0.00 37 3 38 4 5 39 40 41 PV of the NOI for 5 years plus the PV of Terminal Value of Property $0.00 B D E F G I H | A 47 48 Mortgage 49 Mortgage Closing Costs 50 Annual Contract Rate 51 Term (years) 52 Payments per year 53 Total Number of Payments 54 Balloon 55 Monthly Payment 56 Annual Payment 57 58 59 60 61 62 63 $3,761,443.17 2.00% 4.50% 30 12 360 $0.00 $19,058.68 $228,704.16 Mortgage cashflow IRR Monthly IRR -92.55% Annualized IRR -1110.56% Principal Payment Interest Month Loan Balance (after pmt) Mortgage Payment 0 $3,761,443.17 1 $3,756,489.90 $19,058.68 $ 2 $3,751,518.05 $19,058.68 $ 3 Cash inflow to Cash outflow Net Cash Flow borrower from borrower to Borrower $3,761,443.17 $75,228.86 $3,686,214.30 -19,058.68 $19,058.68 -19,058.68 $19,058.68 14,105.41 14,086.84 $4,953.27 $4,971.84 4 5 5 6 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 7 8 9 10 11 12 13 14 15 16 A A B D G H 407 408 409 410 411 412 413 414) 415 416 4171 418 419 420 421 422 423 424 425 oc 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 U