File Name: Real Estate – Commercial ROI
Location: Modeling Toolkit | Industry Applications | Real Estate – Commercial ROI
Brief Description: Illustrates how to use Risk Simulator for simulating a financial ROI analysis for real estate projects and running scenario analysis on the optimal holding period of the asset that maximizes NPV
Requirements: Modeling Toolkit, Risk Simulator
This model is used to value the profit and loss from a real estate deal with industry standards, such as cap rates, vacancy and collection factors, and assumed growth rates, and Consumer Price Index (CPI) adjusted pricings. The pro forma income statement and cash flow statement with the relevant salvage sale value of the property and amortization table of the mortgage are also included. The model uses function calls from the Modeling Toolkit and is simulated using Risk Simulator. See Figure 120.1.
Figure 120.1: Commercial real estate ROI analysis
Procedure
To run this model, simply click on Risk Simulator | Run Simulation. However, if you wish, you can create your own simulation model:
- Start a new simulation profile and add various assumptions on cells E5:E13 and L5:L8. You can make them all triangular distributions and link the input parameters to the relevant cells (e.g., for Gross Rent the First Year, link Min to F5, Likely to E5, and Max to G5). Define the assumption by first making sure there is a simulation profile (Risk Simulator | New Profile), then selecting the relevant cells and clicking on Risk Simulator | Set Input Assumption.
- You can then select the predefined cell, click on Risk Simulator | Copy Parameter, hit the Escape key once (so that the values will not be copied; only the assumptions will be copied), and select the other cells to copy to (e.g., E6:E13 and later, L5:L8) and click on Risk Simulator | Paste Parameter.
- Add forecasts to the key outputs such as Net Present Value (Risk Simulator | Set Output Forecast).
- Run the simulation (Risk Simulator | Run Simulation) and interpret the results.
In addition, a tornado sensitivity analysis and a scenario analysis can be run on the model. The generated reports for both techniques are included in the model. To run these analyses, follow these instructions:
· Tornado Analysis: Select cell I15 (Net Present Value) and start the tornado analysis (Risk Simulator | Analytical Tools | Tornado Analysis) and select the checkbox Use Cell Addresses and click OK. A report will be generated. Interpret the tornado results as usual.
· Scenario Analysis: Start the Scenario Analysis tool (Risk Simulator | Analytical Tools | Sensitivity Analysis) and enter the relevant inputs as seen in Figure 120.2.
Figure 120.2: Scenario analysis tool
The scenario report worksheet will be generated, listing all the possible combinations of Holding Periods and Mortgage Maturities as well as the resulting NPV (Figure 120.3).
Figure 120.3: Scenario analysis results table
