RE 530 Real Estate Investment and Finance Problem Set 3: Monte Carlo Analysis This exercise is an important part of the learning process. The exercise has three objectives: . to ensure that you understand the analysis using simulation, to recognize the uncertainty in cash flow forecasts, and to get you well on your way to your final analysis. Incorporating uncertainty into real estate pro formas not only provides different results over deterministic models, it changes the angle of attack to real estate valuation problems. When uncertainty is taken into account, the focus shifts from simply maximizing financial returns, to modeling and managing uncertainty to make better ex ante investment and finance decisions. Task 1: Prepare a "base case" analysis of the Tower Office building using the following assumptions: Purchase Price Gross Floor Area Office Rent Rent Growth Rate Expense Growth Stabilized Vacancy Expenses Terminal Cap Rate Discount Rate Tower Office Assumptions $17,000,000 170,000 sf $30/sf 3% 3% 5% $17.5/sf 11.5% 12.5% A fully amortizing 70 percent loan can be obtained at 7 percent interest for 30 years (total annual payments will be monthly payments times 12). The building represents 80 percent of value and is depreciable over 39 years (use 1/39 per year). Capital gains from price appreciation will be taxed at 20 percent and depreciation recapture will be taxed at 25 percent. The potential investor indicates that she is in the 36 percent bracket and has enough passive income from other activities so that any passive losses from this activity would not be subject to any passive activity loss limitation. Develop a 10-year pro forma. (a) Use the pro forma to determine the before-tax IRR (BTIRR) and after-tax IRR (ATIRR). (b) What is the NPV for this property using a 12.5 percent (after-tax) discount rate. Task 2: Incorporate uncertainty in the rent growth rate of Tower Office building To model the uncertainty of rent growth use a normal distribution with an average expected rent growth rate of 3 percent and a standard deviation of 2 percent. The latter implies that the rental growth rate should be within 12 percent of the mean (or between 1 percent and 5 percent) 68 percent of the time and should be within 4 percent of the mean (or between-1 percent and 7 percent) 95 percent of the time. Run 500 iterations for Tower Office building, not one more nor one less. (a) After running 500 iterations of the model calculate the mean of the 500 NPVs to yield an Expected Net Present Value (ENPV). (b) Create the cumulative distribution function for the NPV. Graph the table as a scatter plot and format as necessary. (c) Identify the value-at-risk (i.e., the loss at the worst 10 percent of the scenarios) and the value-at-gain (i.e., the gain at the best 10 percent of the scenarios). (d) Compute the probability that the NPV of the property will be zero or less. (e) Discuss how you could plot multiple cumulative distribution functions on the same graph to compare and analyze the probabilistic outcomes across multiple alternatives. RE 530 Real Estate Investment and Finance Problem Set 3: Monte Carlo Analysis This exercise is an important part of the learning process. The exercise has three objectives: . to ensure that you understand the analysis using simulation, to recognize the uncertainty in cash flow forecasts, and to get you well on your way to your final analysis. Incorporating uncertainty into real estate pro formas not only provides different results over deterministic models, it changes the angle of attack to real estate valuation problems. When uncertainty is taken into account, the focus shifts from simply maximizing financial returns, to modeling and managing uncertainty to make better ex ante investment and finance decisions. Task 1: Prepare a "base case" analysis of the Tower Office building using the following assumptions: Purchase Price Gross Floor Area Office Rent Rent Growth Rate Expense Growth Stabilized Vacancy Expenses Terminal Cap Rate Discount Rate Tower Office Assumptions $17,000,000 170,000 sf $30/sf 3% 3% 5% $17.5/sf 11.5% 12.5% A fully amortizing 70 percent loan can be obtained at 7 percent interest for 30 years (total annual payments will be monthly payments times 12). The building represents 80 percent of value and is depreciable over 39 years (use 1/39 per year). Capital gains from price appreciation will be taxed at 20 percent and depreciation recapture will be taxed at 25 percent. The potential investor indicates that she is in the 36 percent bracket and has enough passive income from other activities so that any passive losses from this activity would not be subject to any passive activity loss limitation. Develop a 10-year pro forma. (a) Use the pro forma to determine the before-tax IRR (BTIRR) and after-tax IRR (ATIRR). (b) What is the NPV for this property using a 12.5 percent (after-tax) discount rate. Task 2: Incorporate uncertainty in the rent growth rate of Tower Office building To model the uncertainty of rent growth use a normal distribution with an average expected rent growth rate of 3 percent and a standard deviation of 2 percent. The latter implies that the rental growth rate should be within 12 percent of the mean (or between 1 percent and 5 percent) 68 percent of the time and should be within 4 percent of the mean (or between-1 percent and 7 percent) 95 percent of the time. Run 500 iterations for Tower Office building, not one more nor one less. (a) After running 500 iterations of the model calculate the mean of the 500 NPVs to yield an Expected Net Present Value (ENPV). (b) Create the cumulative distribution function for the NPV. Graph the table as a scatter plot and format as necessary. (c) Identify the value-at-risk (i.e., the loss at the worst 10 percent of the scenarios) and the value-at-gain (i.e., the gain at the best 10 percent of the scenarios). (d) Compute the probability that the NPV of the property will be zero or less. (e) Discuss how you could plot multiple cumulative distribution functions on the same graph to compare and analyze the probabilistic outcomes across multiple alternatives
