A practical tool to assist in analyzing risk associated with income capitalization approach valuation or investment analysis.
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.
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:
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: firstname.lastname@example.org; 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: email@example.com; 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.
|Printer friendly Cite/link Email Feedback|
|Title Annotation:||features; Excel model|
|Author:||Weaver, William; Michelson, Stuart|
|Date:||Oct 1, 2003|
|Previous Article:||Recreation ranch appraisal.|
|Next Article:||The future of the valuation profession: diagnostic tools and prescriptive practices for real estate markets.|