A Pedagogical Tool to Assist in Teaching Real Estate Investment Risk Analysis

Journal of Real Estate Practice and Education,  2004  by Weaver, William,  Michelson, Stuart

• E-mail
• Print
• Link

Abstract. This paper presents a simple Excel model that provides measures of the standard deviation of forecasted internal rate of return (IRR) given traditional data inputs such as annual cash flows, terminal values and equity. The model first calculates IRR using traditional discounted cash flow methods and then provides heuristic estimates of variability measured in terms of "high," "low" and "most likely" values. It also provides an actual measurement of risk in terms of mean and standard deviation and upper and lower quartiles, along with a graphical presentation of various risk parameters. The only additional analysis required is an estimation of the variability of periodic cash flows and the terminal value of the investment.

Introduction

Real estate investment analysis, in the end, seeks to answer three basic questions:

1. How much to pay (equity usually or total purchase price sometimes)?

2. Given a particular purchase price, what will be the yield on invested equity (IRR^sub equity^)?

3. How risky is the project?

How much to pay is usually asked in terms of how much equity to invest or, sometimes, how much to pay for the whole project. This is usually estimated in terms of the present value of either before tax cash flow or after tax cash flow if equity is of interest and the present value of net operating income (NOI) for the total project's value (sometimes analysis estimate required equity and then simply add debt).

The internal rate of return (IRR) on invested equity is usually measured by using either before or after tax cash flows along with an estimate of equity and solving for IRR. Risk is usually defined as variance about some forecasted future value, most often IRR or present value (PV) or, in some cases, net present value (NPV). Risk can also be associated with other variables such as rent, vacancy, operating expenses or NOI. When a real estate analyst forecasts any future value (rent, vacancy, operating expenses, cash flows, IRR, PV or NPV) and the future result is variable then there will be risk (variability) associated with that forecast. Sometimes a lot of risk, sometimes less, depending on the situation. This paper presents a model that focuses on estimating IRR and the risk associated with that return.

For many years academicians and practitioners have discussed the use of Monte Carlo simulation as it may be applied to real estate analysis. Currently available techniques that actually develop a risk measure (variance or standard deviation) involve some variant of simulation (e.g., @Risk (www.palisade.com), Crystal Ball® (www.decisioneering.com) and other simulation programs]. All methods of applying simulation to any reasonably "real-world' dataset require a seriously sophisticated analyst who is not only familiar with the subject data set but is also well trained in statistics and simulation. Simulation software such as @Risk and Crystal Ball require extensive training in statistics along with a good deal of information about the proposed project and lots of market-oriented information. Assuming that a competently trained analyst is available, parameterizing a simulation model is extremely difficult and expensive. Not only must each variable's distribution type be estimated but mean and standard deviation/variances must be estimated for all variables along with a correlation matrix showing correlations between all variables.

This paper develops a technique that can easily be mastered by any student to provide actual variance/standard deviation risk measures. It allows a student to examine risk given changes in a variety of input variables. Essentially, the technique takes the two most significant variables affecting the variability of an IRR forecast (annual cash flows and terminal value) and uses a two-dimensional matrix to examine the results of all likely combinations of these two variables as they change. The model can be extended to examine the interaction of three or more variables as necessary.

Use of a matrix approach facilitates the analysis of all possible variable combinations and allows the variance/standard deviation to be easily calculated. Various descriptive statistics (parametric and non-parametric) can also be calculated from this matrix data to help provide additional understanding of project risk. While this paper is oriented toward teaching risk associated with IRR forecasting, the techniques and tools presented can certainly be used by analysts seeking to better their understanding of the risk associated with any forecast where periodic and terminal cash flows are involved.

Literature Review

A number of recent authors have developed models that analyze capital budgeting decisions. These models allow the analyst to vary many of the input variables and to quantify the risk of the project. The following discussion summarizes several of these authors' results.

Most current real estate simulation work is predated by, and largely based on, dissertation work done by Steven Phyrr in the 1960s. On a more current note, Taggart (1999) presents a useful capital budgeting analysis model that uses discounted cash flow (DCF) and allows the user to estimate all of the components of the annual cash flows. This model would provide excellent input data for the model presented in this paper. Mahoney and Kelliher (1999) introduce an interesting capital budgeting model that incorporates uncertainty in the cash flow estimates by utilizing Monte Carlo simulation. Their model, while practical and useful, does introduce a higher level of complexity to the capital budgeting decision. Winston (1998) develops a model for multi-period capital budgeting using the Solver Tool in Excel. This linear programming model allows the analyst to sort through many capital budgeting projects and select the best, subject to time and cost constraints, with the objective of maximizing overall NPV. Ragsdale (2001) also presents a model that utilizes Solver to find the optimal combination of capital budgeting projects subject to capital constraints, while maximizing NPV. The author develops a model that uses Crystal Ball and OptQuest (Monte Carlo simulation tools) to perform optimal project selection, while considering risk factors, such as probability of success and minimum and maximum revenues. The solution is subject to an initial investment constraint, while maximizing NPV. Benninga (2000) demonstrates the use of data tables in capital budgeting analysis. After calculating the point estimate for NPV, a set of growth rates are assumed (growth of annual cash flows) and the NPV is calculated for each of these growth rates. The analyst can use the results to assess the risk of the project based on the resulting range of NPVs. Mayes and Shank (2001) provide a through discussion of capital budgeting and the application of Excel models for the analysis. They also integrate risk-adjusted discount rates and Monte Carlo simulation to assess project risk. On a complementary note, Weaver and Michelson (2003) describe the use of a similar model to analyze NPV in a capital budgeting context.