Answer Happy

1a. What is Your IRR for this investment?
1b. 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 = NCF
Year 1 divided by NCF Year 0.
1c. For the following questions, change only the variable
indicated. Keep the others as dictated above.
What is your IRR if NOI growth is 5% compounded annually (all
other variables held constant)?
1d. Change only the variable indicated below. Keep the others as
originally indicated.
What is your IRR if your exit cap rate is 4.75%?
Complete the data table using the What-if Analysis in Excel.
A B С E F G H 1 j к L M z 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 NỘI Year 1 9 Going in cap rate 10 NOI growth rate 11 Exit Cap Rate Year 5 12 Selling Costs 13 14 15 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 $ $ $ 5,400,000.00 $ 162,000.00 $ 3,761,443.17 $ (75,228.86) Net Operating Income (NOI)| Debt Service Payment $ 274,448.76 $ $ 228,707.30 $ 282,682.23 $ 228,707.30 $ 291,162.69 $ 228,707.30 $ 299,897.58$ 228,707.30 $ 308,894.50 $ 318,161.34 228,707.30 Capital Gain $11,460,215.96 Sale Price Selling Costs Loan Balance Repaid Loan Prepayment Penalty $ $ $ $ 6,060,215.96 303,010.80 3,761,443.17 37,614.43 16 17 Sum of Net Cash flow $0.00 18 19 Net Cash Flow Cash on Cash Return IRR 20 21 22 23 24 NOI Growth 3% 1% 2% 4% 5% 25 26 IRR Based on Different NOI Growth and Exit Ca #NUMI 4.50% 4.75% 5.00% 5.25% 5.50% 27 28 29 Exit Cap Rate 30 31 32 33 34 Discount Rate 8% Breaking Down the Contribution of NOI and Terminal value to Investment Return Reversion End of Year NOI PV of NOI (Terminal Value) PV of Rev 1 2 35 36 37 Total PV $0.00 $0.00 $0.00 $0.00 $0.00 3 38 4 39 5 537,601.81 40 41 PV of the NOI for 5 years plus the PV of Terminal Value of Property $0.00 ศา
A B с D E F G H 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 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 17 18 82 83 84 85 86 87 88 89 90 91 92 93 94 19 20 21 22 23 24 25 26 27 28 29
A B D I I 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 331 332 333 334 335 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