A practical tool to assist in analyzing risk associated with income capitalization approach valuation or investment analysis.

abstract

Appraisers often apply the discounted cash flow (DCF) model when performing an appraisal or investment analysis using the income capitalization approach. Use of DCF requires an appraiser to forecast future periodic cash flows and terminal value. It often provides the best estimate of the market value (MV) of a project, but normally does not provide an estimate of risk associated with the appraiser's forecast. Developing an actual measurement of risk, i.e., an estimate of the standard deviation of a forecasted variable, is time consuming and difficult. This paper presents a simple Excel model that provides a measure of the standard deviation of a forecasted value.

**********

Real estate appraisers often use the discounted cash flow (DCF) model to aid in estimating the market value (MV) of a project. This model is also frequently used in leased fee valuation and when analyzing risk in an investment context. Often value is estimated using a debt-free assumption, where net operating income [NOI] and before-tax cash flow [BTCF] are the same amount. The periodic before-tax cash flows from operations and before-tax cash flow from sale (before-tax reversion) are usually the important cash flows, although some situations call for the use of after-tax cash flows. Which cash flow definition is used depends upon the analyst's goal and the situation.

A market-derived discount rate, often developed from market yields of recent transactions, is usually used to discount the estimated future cash flows back to present value (PV). The use of a market-derived rate does not imply that estimated future cash flows can be forecasted with more or less accuracy; it means that market participants view certain types of properties as being more or less risky. The selection of a market-derived yield rate as a discount rate reflects the fact that certain classes of investments are more or less risky than other types. It does not contain project-specific risk information as to whether the particular property being analyzed is more or less risky than the market norm.

Most appraisers cease analysis at this point unless they use some version of sensitivity analysis to determine how sensitive their MV estimate is to changes in discount rate, operating cash flow assumptions, or terminal value (reversion) assumptions.

In the appraisal industry, as in traditional finance, risk is often defined mathematically as variance about some forecasted value. For example, risk can be associated with forecasted variables such as rent, vacancy, bad debt, various operating expenses, and reversion or terminal value. When a real estate appraiser forecasts future value, which may be variable, then there will be risk (variability) associated with that forecast. Sometimes there will be a lot of risk, sometimes less, depending upon the situation.

Note that risk is present in both appraisal work (market value estimates) and investment analysis. Expected future cash flows are just that, "expected" or "forecasted." They will likely vary from the forecast. Market value is usually defined as "the most probable price"; that is, it is a point estimate of a price from among a distribution of prices (hence most probable). The goal of this article, and the accompanying Excel model, is to assist an analyst in investigating the results of this variance of future cash flows.

The model presented is not meant to encourage the appraiser to shortcut the analytical process associated with a valuation problem or to avoid doing the work necessary to develop supportable assumptions the work necessary to develop supportable assumptions used in the analysis. The model is useful, however, in testing those assumptions and in analyzing results in changes in those assumptions. The simple Excel model presented here focuses on estimating the present value of future, forecasted cash flows, along with the risk associated with the forecast for a specific project. (1) In other words, it allows consideration of risks associated with a specific project as well as risk associated with a property class, such as offices, retail, or warehouses.

Academicians and practicing appraisers have discussed the use of Monte Carlo simulation as it may be applied to real estate valuation. The application of simulation in appraisal analysis is discussed articles by Kelliher and Mahoney, (2) and Li. (3) In a more recent article, Kummerow suggests that appraisers should report information about variation (risk) of their value estimates. (4)

Currently available techniques that actually develop a risk measure (variance or standard deviation) involve some variant of simulation software, for example @Risk,[c] (5) Crystal Ball, [c] (6) and other Excel-based simulation programs. All methods of applying simulation to any reasonably "real-world" data set require an appraiser to be not only familiar with the project and its market, but also well trained in statistics and simulation. Simulation software such as @Risk [c] or Crystal Ball[c] requires extensive training in statistics along with a good deal of information about the proposed project and its market. Assuming that a competently trained appraiser is available, estimating all of the input variables for such a simulation model is extremely difficult and expensive. An appraiser must estimate rent for each period over the forecast period, vacancy, bad debt, operating expenses, and the terminal value or reversion at the end of the holding period. Not only must each variable's distribution shape be estimated, but also mean and standard deviation/variances for all variables, along with a correlation matrix showing correlations between all variables. Given the current market for appraisal services, it is unlikely that many appraisers have either the time or fee structure to allow them to develop and use simulation analysis.

This article develops a technique that appraisers can easily master to provide actual standard deviation risk measures. The technique also allows the development of "high," "low," and "most likely" value estimates, along with high and low quartile and median figures. Essentially, the technique identifies two of the most significant variables affecting a market value forecast using the discounted cash flow approach, i.e., periodic cash flow forecasts and the terminal value/ reversion forecast. It uses a two-dimensional matrix to examine the results of all possible combinations of likely changes in these two variables. The model is then extended to examine the interaction of periodic cash flows, terminal value, and discount rates, three major variables in the use of DCF.

Use of a matrix approach facilitates the analysis of all possible likely combinations of changes of the variables and allows the variance/standard deviation to be easily calculated. Various descriptive statistics (parametric and nonparametric) can also be calculated from this matrix data to help provide additional understanding of risk associated with the estimate of market value.

The DCF Approach to Valuation

When discounted cash flow techniques are used to estimate market value, there are ultimately only three variables that influence the results:

* Periodic cash flows over the forecasted holding period (typically 5 or 10 years)

* Terminal value/reversion at the end of the forecast period

* Appropriate market-driven discount rate

Of course, many other variables are used to form these estimates. Historically, simulation has focused on these many other variables, such as rent, vacancy, bad debt, a wide variety of operating expenses, and sale price. The annual cash flow estimates (generally before-tax, depending upon the need of the appraiser) over the forecast period are the result of complex interactions between many income and expense variables and, if the appraiser is using after-tax cash flows, income tax rates and depreciation-related variables. These variables are generally not independent of one another; for example, raise income and it is likely that vacancy and operating expenses will change and possibly marginal tax rates. This correlation between variables is one of the reasons that traditional simulation analysis is so difficult to use properly--it is very difficult to accurately forecast all of the possible interactions between the various variables. Likewise, terminal value is the result of an interaction between cash flow and capitalization rates in the year of sale or estimated appreciation over the forecasted holding period.

Appraisers need a way to account for these many variations and correlations while avoiding the complexity of having to forecast a multitude of intercorrelated variables whose distribution shapes, means, and variance/standard deviations are unknown.

This can be accomplished by first using traditional appraisal methodology to develop a point estimate for annual operating cash flows, terminal value/ reversion, and discount rate. This methodology is well defined, (7) and since it is not the focus of this article, it will not be discussed further. This estimate of future cash flows and discount rate may be used to develop an estimate of the project's market value, again using ordinary, traditional appraisal methodology.

One relatively simple, additional step will allow appraisers to develop an actual measure of risk associated with their value forecast. A simple, two-dimensional matrix, such as the one in Figure 1, can be used to forecast values given any combination of periodic cash flows and terminal values. This two-dimensional approach assumes that the appropriate discount rate is known with reasonable certainty. If this is not the case, a three-dimensional version of the matrix may be used to accommodate discount rate variation. An example of each is presented in this article.

Example of Two-Variable Valuation: Normal Risk Distribution

For an example of" the two-variable approach, assume that annual NOIs and the before-tax terminal value have been forecasted as follows:

Year 1     $150,000    Year 6       $250,000
Year 2     $200,000    Year 7       $100,000
Year 3     $250,000    Year 8       $190,000
Year 4     $200,000    Year 9       $220,000
Year 5     $225,000    Year 10      $250,000
With a terminal value of          $1,000,000

Further assume that the appraiser has developed a defensible, market-derived discount rate of 9.75%. The goal of the analysis is to estimate the likely value, given these cash flow estimates. Traditional appraisal analysis would take the present value of these cash flows at the discount rate of 9.75%. The result, the present value of the ten-year projection period figures, is $1,644,704.

Analysis of Risk for Varied Future Cash Flows

If it was believed that annual cash flow and terminal value estimates had normally distributed forecast errors (about an equal chance of being higher or lower), one could increase and decrease these two variables by a given percentage, say by 10%, 15%, and 20%, and then recalculate the resulting present values, as shown in Figure 1. The results of all of these recalculations could be measured as follows:

Traditional Parametric Statistics
  Mean:                                 $1,644,704
  Standard deviation:                     $190,644
Traditional Nonparametric Statistics
  Upper quartile:                       $1,973,645
  Median:                               $1,644,704
  Lower quartile:                       $1,473,530
Simple Averages
  High-range average:                   $1,891,410
  Most likely range:                    $1,644,704
  Low-range average:                    $1,397,999

Quartile figures, as used here, are the average of the top or bottom quarter of the population.

All of the above measures report the same most likely figure ($1,644,704) since the periodic cash flows and the terminal value were changed evenly up and down, i.e., [+ or -] 10%, [+ or -] 15%, [+ or -] 20% (normal distribution).

Note that all of the above measures attempt to assist the analyst in understanding what would happen in "best case," "worst case," and "most likely case" situations. For example, the best case would result from higher-than-expected periodic cash flows together with higher-than-expected terminal value. The resulting best-case value estimate is about $1.9 to $2.0 million (simple average or upper quartile). Alternatively, the mean value of $1.645 million plus a couple of standard deviations would represent the likely high end (about $2.026 million). This analysis indicates that the most probable best-case scenario would be about $2.0 million.

The worst case would occur when the periodic cash flows and the terminal value turned out to be lower than expected. Indicated values here are about $1.4 million. Of course, the most likely case contains a mix of some higher and some lower values and the $1.645 million estimate is the best point estimate.

There is no tight or wrong way of measuring these figures (parametric, nonparametric, or simple averages). The proper choice depends upon the preferences of the analyst and probably more importantly, upon the preferences of the reader/user of the report. The calculated value estimate is still $1,644,704. This remains the best point estimate of value because the cash flow from operations and cash flow from sale were varied equally higher (+10%, +15%, and +20%) and lower (-10%, -15%, and -20%). Note that when all combinations of higher and lower cash flow and terminal value are calculated, the standard deviation is $190,644. Now there is an actual measure of risk based upon reasonable and defensible expectations of potential changes in the cash flows and terminal value. Based on basic statistical theory, the true value of the project will be between [+ or -] $190,644 from the mean value estimate about 66% of the time; it will be within plus or minus two standard deviations 95% of the time.

Analysis of Non-Normal Risk Distribution

What if the appraiser expects that estimates of future cash flows from operations may not be as variable as the terminal value estimate?

Suppose the valuation deals with a fully leased office building with major AAA tenants whose lease terms extend well past the ten-year forecast period. In a case such as this, an appraiser might choose to use actual rents and not market rents, and feel that the forecast of cash flow from operations might be quite accurate. However, the appraiser might still feel that the estimate of terminal value/reversion is still fairly variable. (8) Further, suppose that the leases all contain strong expense pass-throughs, so the owner is fairly insulated against unforeseen operating-expense changes. Moreover, assume that rent increases are contracted each year in addition to consumer price index (CPI) increases. At this point, the appraiser may fed that future cash flows from operations are likely to be fairly stable and able to be accurately forecasted, but they might not be normally distributed; that is, it is probable that they will be higher rather than lower than forecasted. The point is that the appraiser is not limited to assuming that risk is normally distributed. The appraiser may feel that the forecast may have a greater probability of being higher rather than lower, or vice versa.

Here the cash flow from operations will be varied upward by 10%, 15%, and 20%, but downward by only -5%, -10%, and -15%. Perhaps the terminal value also will be varied upward by 5%, 10%, and 15%, but downward by -10%, -15%, and -20% on the theory that the sale price ten years in the future is more likely to be lower than higher than that forecasted.

According to Figure 2, when MV is computed for all combinations of higher and lower cash flows, then the most likely, best, and worst case estimates can be seen along with the mean and standard deviation or the upper quartile, median, and lower quartile. The best point estimate of MV has now changed, depending upon how it is measured (traditional parametric statistics [mean], traditional nonparametric statistics [median], or simple averages), as the following shows:

Traditional Parametric Statistics
  Mean:                                 $1,663,045
  Standard deviation:                     $161,678
Traditional Nonparametric Statistics
  Upper quartile:                       $1,953,925
  Median:                               $1,644,704
  Lower quartile:                       $1,523,028
Simple Averages
  High-range average:                   $1,871,689
  Most likely range:                    $1,661,270
  Low-range average:                    $1,460,513

Quarter figures, as used here, are the average of the top or bottom quarter of the population.

In Figure 2, the highlighted cells containing the best case and worst case estimates reflect the DCF values resulting from a combination of the highest and lowest annual and terminal cash flows. The best case (about $1.872 million) and worst case (about $1.461 million) estimates are based on averages of the nine cells containing the high-range values and the nine cells containing the low-range values. The balance of cells containing the most likely case estimates reflect values that result from combining both the higher and lower annual and terminal cash flow values. These cells result in a mixed estimate of DCF values, after combining increasing and decreasing cash flow estimates. Given this data, the best point estimate of MV using this model is $1,661,270 (about $1.661 million).

Alternatively, an overall mean value ($1,663 million) and standard deviation of about $161,678 can be calculated with high and low values being defined by plus or minus one or two standard deviations.

Nonparametric measurements using the upper and lower quartiles and the median also provide an acceptable method for summarizing the analysis and developing upper and lower likely ranges.

Example of Three-Variable Valuation: Non-Normal Errors

A more advanced version of the process is to incorporate a third variable into the analysis, as shown in Figure 3. For instance, this might be used where appraisers believe that they cannot defend an exact discount rate, but may be able to estimate a range of discount rates fairly accurately. This is accomplished by starting with the previous data (which used a 9.75% discount rate) and introducing an additional layer of tables for each additional discount rate. For example, using the numbers assumed previously, the discount rate could be varied up and down by 25 basis points or 3%.

Putting the three variables together into a matrix format allows an appraiser to assess the overall value of the project and the risk associated with the value forecast by analyzing all possible combinations of likely changes in the operating cash flows, the terminal value, and the discount rate. Note that, as additional variables are added, the number of cells analyzed increases geometrically, i.e., two variables result in a 7 x 7 matrix or 49 cells, three variables result in a 7 x 7 x 7 matrix or 343 cells, and adding a fourth variable would result in a 7 x 7 x 7 x 7 matrix with 2,401 cells.

Figure 3 presents the model when three variables (periodic cash flows, terminal value, and discount rate) are allowed to vary. The exhibit shows a summary of the worksheet pages resulting from allowing the:

* Discount rate to vary from 9.0% to 10.5% in 0.25% increments

* Periodic cash flows to change in a non-normal distribution (-15%, -10%, -5% and +10%, +15%, and +20%)

* Terminal value to change in a non-normal fashion (-20%, -15%, -10% and +5%, +10%, and +15%)

The mean MV is $1,663,967 with a standard deviation of $166,840. This mean and standard deviation is calculated using all 343 cells in the 7 x 7 x 7 matrix. Alternatively, the average high quartile is $1,955,008, the average low quartile is $1,523,858, and the average median is $1,650,036. The net present value (NPV) and PV portions of the table are the same since a value of $0 was input for equity.

Note that averages refer to the average quartile or median, using each individual discount rate. That is, quartiles and the median are calculated for each of the seven discount rates and then averages are taken. This is preferable to simply taking the overall quartile since the only information contained here would be the worst or best figure that would be generated by the highest or lowest discount rate.

In addition to the overall summary mean and standard deviation, the "Input-Output" page of the Excel model presents a table of means and standard deviations at each of the discount rates selected, so an analyst can see the influence of changing discount rates upon these variables.

Conclusion

This Excel model helps appraisers better understand their value estimate and its sensitivity to varying levels of annual cash flows, terminal values, and discount rates. After inputting the relevant variables in the model, an analyst can easily estimate value along with its standard deviation and basic nonparametric measures of central tendency, such as quartiles and medians. This model allows an analyst to develop a range of estimates with a measure of risk without the necessity of developing a full Monte Carlo simulation model.

In a sense, what is accomplished is sensitivity analysis. That is, how sensitive are value estimates to changes in cash flows and/or discount rates? How do the high/low/most likely estimate, the upper/ lower quartile, and the median change, given changes in periodic cash flows, terminal value, and/or discount rate?

On the other hand, when the standard deviation is calculated, a measure of risk is developed since it is measuring the volatility or probability of change of the value estimate, given changes in the input assumptions. Further, the model accounts for the effects of the interaction of three major variables on value (periodic cash flows, terminal value, and discount rate), rather than traditional methods that examine changes in values, given a change in a single assumption.

Note that while we know that various cash flow and discount rate forecasts are likely wrong (they are, after all, forecasts of the future), the use of market-derived forecasts should represent our best point estimate of what the market is forecasting at the time of the analysis. This model is an excellent sensitivity analysis tool and may assist in determining market value when appraisers feel that typical market participants are using ranges rather than point estimates in their analysis.

An appraiser may elect to utilize this model to develop a nonparametric analysis; report high, low, and most likely values; or alternatively simply report a mean and standard deviation, depending upon the needs and sophistication of the client.

An important feature of this model is that it allows analysts to have a high level of comfort in their estimate. Knowing the range, standard deviation, and upper and lower quartiles and median will provide additional information to help them make useful conclusions and recommendations.

As a last and most important comment, this model is not intended to replace careful and thoughtful analysis on the part of an appraiser. It is not designed to reduce the up-front analytical effort required to properly and accurately estimate likely future cash flows and current discount rates. Instead, it acknowledges the fact that, in almost every valuation/analytical situation, future cash flows are mere estimates that likely will vary (up or down) from forecasted levels. Discount rates are estimated from market data, and they can and do vary.

This model is designed to allow the appraiser to formally consider the value implications with changes in these three basic DCF input variables.

The model does have a weakness; it assumes an equal probability of changes in cash flow, terminal value, and discount rate. That is, it is possible that one of these variables may have a relatively low variance (the distance from high and low points is small), but a very high probability of changing, whereas another may have a relatively high variance (wide distance from high and low figures), but a low probability of changing over time. Future work on the model will attempt to deal with this element, though it remains an extremely useful sensitivity tool as it is.

Appendix

Recreation Ranch Sale 6

District:    Mackay Reservoir             County:    Custer, Idaho
Buyer:       Notellum Creek Ranch, LLC    Seller:    Burget
Price:       $2,200,000                   Date:      July 2000
Terms:       Cash

Legal Description

  Land Class             Acres    Rate/Acre      Value

  Meadowland             1,022     $2,035      $2,080,000
  Improvements Value:                            $120,000
  Total Sale Price:                            $2,200,000

Description
  Location:           Excellent. Three miles north Mackey Reservoir
  Seclusion:          Fair
  Access:             Good. Paved county road-Fish Hatchery Road
  Visual Appeal:      Excellent. Meadows with three streams, two ponds,
                        reservoir near, mountain valley floor with
                        foothills near to west
  Timber:             None
  Water:              Excellent. Big Wood River, Parsons Creek,
                        Notellum Creek, 2 ponds
  Topography:         Good. Generally level bottomland meadow; gentle
                        rolling
  View:               Bottomland ranches near and territorial mountains
                        flanking Big Wood River valley
  Crops:              2,045 AUMs, estimated
  Irrigation:         Sub-irrigation, some handline
  Grazing permits:    None
  Inholding:          No
  Utilities:          Power and telephone
  Buildings:          Residence. 2,500 sq. ft., two-car garage, barn

Comments:
  Excellent fishing, large game animals frequent
  170 AUs estimated @ $12,941 per AU
  Net Income $20,230
  Capitalization Rate 0.009
  Previously sold 1996 for $1,600,000
  Motivation: Ownershiof recreational ranch

Table 1 Summary of Sale Data Sheets

Sale    Buyer            Date    Acres    $/Acre    Location

 1      Claymore Mgmt    1988      645    $1,240    Good
 2      G. Williams      1999      171    $1,980    Excellent
 3      San Felipe       1999    9,100      $495    Isolated
 4      Name withheld    2000      640    $1,992    Excellent
 5      R. Rembelski     2001      640    $2,109    Remote
 6      Notellum, LLC    2000    1,022    $2,035    Excellent
 7      B. Tallent       1999      560      $625    Remote
 8      K. Cockran       1999      950      $763    Good
 9      B. Freihe        1998      199    $2,508    Excellent

Sale    Visual Appeal    Timber     Water

 1      Good             Limited    Good
 2      Good             Modest     Creek
 3      Fair             Groves     Modest
 4      Satisfactory     Limited    Modest
 5      Fair             Distant    Good
 6      Excellent        None       Excellent
 7      Fair             Modest     Livestock
 8      Fair             On site    Limited
 9      Excellent        Modest     Excellent

Sales 3, 7, and 8 are sales of dry grazing land; all other sales are of
recreation meadowland.

Table 2 Animal Unit/Carrying Capacity Analysis

                                                  Price per   Rent per
                             %Yearly     Animal    Animal      Animal
Sale     Price      Date    increase     Units      Unit        Unit

 1       $800,000   1998                  125       $6,400      $141
 2       $580,000   1999                   52      $11,154      $145
 3     $4,500,000   1999                  208      $21,635      $110
 4     $1,275,000   2000                   33      $38,636      $161
 5     $1,350,000   2001                  266       $5,075      $170
 6     $2,200,000   2000                  170      $12,941      $119
 7       $350,000   1999   4 yrs @ 8%      17      $20,958      $124
 8       $725,000   1999   6 yrs @ 20%
 9       $500,000   1998   4 yrs @ 20%

        Total    Capitalization
Sale    Income        Rate

 1     $17,600        0.022
 2      $7,540        0.013
 3     $23,000        0.005
 4      $5,304        0.004
 5     $45,220        0.034
 6     $20,230        0.009
 7      $2,100        0.006
 8
 9

Capitalization rates were provided by informed sources.

Table 3 Sales Adjustment Chart for Meadowlands

                                     Time-      Location/
Sale     Price          Time        adjusted      Access
        per acre    adjustment *     price      Inholdings    Size

 1       $1,151         $200         $1,351         0         $200
 2         $800            0           $800         0         $200

          Timber      Water--     Views--
Sale    Topography    sports &    nearby &    Indicated
        Vegetation     stock      distant       Value

 1         $100            0        $300       $1,951
 2         $400         $200        $200       $1,800

This adjustment chart is changed in specifics because it is related to
two ranches currently appraised but confidentially restricted. It is
numerically realistic and demonstrates the technique correctly.

* The adjustment for market conditions is often referred to as a time
adjustment.

Table 4 Sales Adjustment Chart for Rangeland

                                   Time-      Location/
Sale     Price         Time       adjusted      Access
        per acre    adjustment     price      Inholdings    Size

 1       $1,007        $150        $1,157       -$200       -$150
 2         $763           0          $763       -$150           0

          Timber      Water--      Views--
Sale    Topography    sports &    nearby ST    Indicated
        Vegetation     stock       distant       Value

 1         $100           0            0         $907
 2          $50         $50          $50         $763

This adjustment chart is changed in specifics because it is related to
two ranches currently appraised but confidentially restricted. It is
numerically realistic and demonstrates the technique conectly.

Figure 1 Two-Variable Risk Model--Varied Cash Flows

Matrix Data Input

Equity (enter     Net C.F.    Net C.F.    Net C.F.    Net C.F.
  as negative)     yr. 1       yr. 2       yr. 3       yr. 4

$0                $150,000    $200,000    $250,000    $200,000
Discount Rate      9.75%

Equity (enter     Net C.F.    Net C.F.    Net C.F.    Net C.F.
  as negative)     yr. 5       yr. 6       yr. 7       yr. 8

$0                $225,000    $250,000    $100,000    $190,000
Discount Rate

Equity (enter     Net C.F.    Net C.F.    Net Terminal
  as negative)     yr. 9       yr. 10         Value

$0                $220,000    $250,000     $1,000,000
Discount Rate

Change in           Input Change in Annual Cash Flows
  Terminal
  Value        PV       -20%          -15%          -10%

               20%    $1,473,530    $1,536,044    $1,598,559
               15%    $1,453,809    $1,516,324    $1,578,838
               10%    $1,434,088    $1,496,603    $1,559,117
                0%    $1,394,647    $1,457,161    $1,519,676
              -10%    $1,355,205    $1,417,720    $1,480,234
              -15%    $1,335,484    $1,397,999    $1,460,513
              -20%    $1,315,764    $1,378,278    $1,440,792

Change in               Input Change in Annual Cash Flows
  Terminal
  Value           0%           10%           15%           20%

              $1,723,588    $1,848,617    $1,911,131    $1,973,645
              $1,703,867    $1,828,896    $1,891,410    $1,953,925
              $1,684,146    $1,809,175    $1,871,689    $1,934,204
              $1,644,704    $1,769,733    $1,832,248    $1,894,762
              $1,605,263    $1,730,292    $1,792,806    $1,855,320
              $1,585,542    $1,710,571    $1,773,085    $1,835,600
              $1,565,821    $1,690,850    $1,753,364    $1,815,879

PV at zero change:    $1,644,704

Mean PV:              $1,644,704

Std Dev of PV:          $190,644

High Range:           $1,891,410
Most Likely Range:    $1,644,704

Low Range:            $1,397,999

N = 9

N = 31

N = 9

Upper Quartile:       $1,973,645

Median:               $1,644,704

Lower Quartile:       $1,473,530

Note: you only need to change cash
flow % changes and terminal value
changes in this area--the data
below will automatically change as
required

Figure 2 Two-Variable Risk Model--Non-Normal Distribution

Matrix Data Input

Equity (enter     Net C.F.    Net C.F.    Net C.F.    Net C.F.
  as negative)     yr. 1       yr. 2       yr. 3       yr. 4

$0                $150,000    $200,000    $250,000    $200,000
Discount Rate      9.75%

Equity (enter     Net C.F.    Net C.F.    Net C.F.    Net C.F.
  as negative)     yr. 5       yr. 6       yr. 7       yr. 8

$0                $225,000    $250,000    $100,000    $190,000
Discount Rate

Equity (enter     Net C.F.    Net C.F.    Net Terminal
  as negative)     yr. 9       yr. 10         Value

$0                $220,000    $250,000     $1,000,000
Discount Rate

Change in           Input Change in Annual Cash Flows
  Terminal
  Value        PV        -15%          -10%          -5%

               15%    $1,516,324    $1,578,838    $1,641,352
               10%    $1,496,603    $1,559,117    $1,621,632
                5%    $1,476,882    $1,539,396    $1,601,911
                0%    $1,457,161    $1,519,676    $1,582,190
              -10%    $1,417,720    $1,480,234    $1,542,748
              -15%    $1,397,999    $1,460,513    $1,523,028
              -20%    $1,378,278    $1,440,792    $1,503,307

Change in             Input Change in Annual Cash Flows
  Terminal
  Value           0%           10%           15%           20%

              $1,703,867    $1,828,896    $1,891,410    $1,953,925
              $1,684,146    $1,809,175    $1,871,689    $1,934,204
              $1,664,425    $1,786,454    $1,851,969    $1,914,483
              $1,644,704    $1,769,733    $1,832,248    $1,894,762
              $1,605,263    $1,730,292    $1,792,806    $1,855,320
              $1,585,542    $1,710,571    $1,773,085    $1,835,600
              $1,565,821    $1,690,850    $1,753,364    $1,815,879

PV at zero change:    $1,644,704

Mean PV:              $1,663,045

Std Dev of PV:          $161,678

High Range:           $1,871,689

Most Likely Range:    $1,661,270

Low Range:            $1,460,513

N = 9

N = 31

N = 9

Upper Quartile:       $1,953,925

Median:               $1,644,704

Lower Quartile:       $1,523,028

Figure 3 Three-Variable Risk Model--Varying Cash-Flows, Terminal Value,
and Discount Rate

Input initial investment (as a negative), annual cash flows, terminal
value, discount rate and estimate of change in discount rate

Equity (always $0
  for market value    Net C.F.    Net C.F.    Net C.F.    Net C.F.
  calculations)        yr. 1       yr. 2       yr. 3       yr. 4

$0                    $150,000    $200,000    $250,000    $200,000
Discount Rate           9.00%       9.25%       9.50%       9.75%

                                       most likely

Change in Annual
  Cash Flows          -15%        -10%         -5%          0%

                                        no change

Change in
  Terminal Value      -20%        -15%        -10%          0%

                                        no change

Total No. of
  Periods                10

Equity (always $0
  for market value    Net C.F.    Net C.F.    Net C.F.    Net C.F.
  calculations)        yr. 5       yr. 6       yr. 7       yr. 8

$0                    $225,000    $250,000    $100,000    $190,000
Discount Rate          10.00%      10.25%      10.50%

                                       most likely

Change in Annual
  Cash Flows           10%         15%         20%

                                        no change

Change in
  Terminal Value        5%         10%         15%

                                        no change

Total No. of
  Periods

Equity (always $0
  for market value    Net C.F.    Net C.F.    Net Terminal
  calculations)        yr. 9       yr. 10         Value

$0                    $220,000    $250,000     $1,000,000
Discount Rate

                                       most likely

Change in Annual
  Cash Flows

                                        no change

Change in
  Terminal Value

                                        no change

Total No. of
  Periods

Summary Results

                                 PV at zero change, Mean PV, and Std.
                                    Dev. At Various Discount Rates

Overall Summary PV Statistics                    9.00%         9.25%

PV at zero change:               $1,715,349    $1,715,349    $1,691,321
Mean PV:                         $1,663,967    $1,734,033    $1,709,870
Std Dev of PV:                     $166,840      $167,742      $165,681
Median:                          $1,650,036
Upper Quartile NPV:              $1,955,008
Lower Quartile NPV:              $1,523,858

                                 PV at zero change, Mean PV, and Std.
                                    Dev. At Various Discount Rates

Overall Summary PV Statistics      9.50%         9.75%         10.00%

PV at zero change:               $1,667,777    $1,644,704    $1,622,093
Mean PV:                         $1,686,221    $1,663,045    $1,640,329
Std Dev of PV:                     $163,660      $161,678      $159,734
Median:
Upper Quartile NPV:
Lower Quartile NPV:

                                 PV at zero change, Mean PV, and Std.
                                    Dev. At Various Discount Rates

Overall Summary PV Statistics            10.25%        10.50%

PV at zero change:                     $1,599,931    $1,578,208
Mean PV:                               $1,618,063    $1,596,236
Std Dev of PV:                           $157,827      $155,956
Median:
Upper Quartile NPV:
Lower Quartile NPV:

Additional Reading

Benninga, Simon. Financial Modeling. 2d ed. Cambridge, MA: MIT Press, 2000.

Mahoney, Lois S., and Charles F. Kelliher. "Teaching Tools to Deal with the Uncertainty Inherent in Capital Budgeting Models." Journal of Financial Education 25 (Spring 1999): 64-74.

Mayes, Timothy R., and Todd M. Shank. Financial Analysis with Microsoft Excel. 2d ed. Orlando: Harcourt, Inc., 2001.

Ragsdale, Cliff T. Spreadsheet Modeling and Decision Analysis. 3d ed. Cincinnati: South-Western College Publishing, 2001.

Taggart, Robert A. "Spreadsheet Exercises for Linking Financial Statements, Valuation and Capital Budgeting." Financial Practice and Education 9, no. 1 (Spring/Summer, 1999): 102-110.

Winston, Wayne. Financial Models Using Simulation and Optimization. Newfield, NY: Palisade Corporation, 1998.

(1.) A complimentary copy of both the two-dimensional and three-dimensional Excel models presented in this paper may be downloaded from either author's web page, see www.bus.ucf.edu/weaver or www.stetson.edu/~smichels.

(2.) Charles F. Kelliher and Lois S. Mahoney, "Using Monte Carlo Simulation to Improve Long-Term Investment Decisions," The Appraisal Journal (January 2000): 44-56.

(3.) Ling Hin Li, "Simple Computer Applications Improve the Versatility of Discounted Cash Flow Analysis," The Appraisal Journal (January 2000): 86-92.

(4.) Max Kummerow, "A Statistical Definition of Value," The Appraisal Journal (October 2002): 407-416.

(5.) See www.palisade.com.

(6.) See www.decisioneering.com or www.crystalball.com/crystal ball/index.html.

(7.) Appraisal Institute, The Appraisal of Real Estate, 12th ed. (Chicago: Appraisal Institute, 2001), 569-593.

(8.) Note that this may be treading upon thin ice; shifting away from market-level rents may put us close to investment value or leased fee valuation rather than MV, depending upon the situation.

William Weaver, PhD, is a professor of finance at the University of Central Florida in Orlando. He has a forensic-oriented, valuation consulting practice; his clients include the Federal Savings and Loan Insurance Corporation, the Resolution Trust Corporation, the states of Georgia and Florida, and the cities of Atlanta, Dallas, and Orlando. In addition, Weaver is currently on retainer with the Florida Department of Business and Professional Regulation to assist with real estate and appraisal licensing testing and education. His past work has been in the areas of condemnation and business valuation. He has published extensively in academic and professional journals. Weaver holds a PhD in land economics from Georgia State University and an MBA from Loyola University. Contact: Department of Finance, University of Central Florida, Orlando, FL, 32816-1400; T 407-823-5313; F 407-823-3182; E-mail: [e-mail]; Web site: www.bus.ucf.edu/weaver

Stuart Michelson, PhD, is the Roland & Sarah George Professor of Finance at Stetson University in DeLand, Florida. He holds a PhD from the University of Kansas and an MBA from the University of Missouri. Contact: Department of Finance--School of Business Administration, Stetson University, 421 N. Woodland Blvd., Unit 8398, DeLand, FL 32723; T 386-822-7376; F 386-822-7446; E-mail: [e-mail]; Web site: www.stetson.edu/-smichels

The authors would like to thank Charlie Lentz, MAI, for his insight on the first draft of this paper.