Finance
Chapter 11 Valuation in Practice
Learning Objectives
Articulate the criteria for selecting a valuation model
Use the relative value approach to estimate the continuing value of a new venture
Identify and collect the information needed to implement a new venture DCF valuation
Estimate the components of a new venture’s beta
Estimate the opportunity cost of capital for a venture
Recognize and use shortcuts in the valuation process
Implement the primary valuation approaches:
DCF by the RADR and CEQ forms of the CAPM
Relative Value method
Venture Capital method
First Chicago method
2
Criteria for Selecting a New Venture Valuation Method
Is cost of capital used as the discount rate?
Does the approach deal appropriately with cash flows that vary in risk?
Can the model be used to value embedded real options and complex financial claims?
How difficult is it to estimate the information required for the valuation?
Are sufficient data available to have confidence in relative valuation estimates?
3
Criteria for Selecting a New Venture Valuation Method
The following questions are relevant for assessing the relative merits of different approaches:
Is cost of capital used as the discount rate?
Compensating for biased cash flow estimates by discounting with biased hurdle rates causes projects with more distant payoffs to be rejected incorrectly
Discount rates based on total risk can lead to rejecting projects that should be accepted
Does the approach deal with cash flows that vary in risk?
Different cash flow streams in the same period can differ in risk
Cash flows at different times can also differ in risk
Can the model be used to value real embedded options and complex financial claims?
A financial structure that includes real or financial options can alter overall value
How difficult is it to estimate the information required for the valuation?
Complex or difficult valuation approaches are sometimes too costly to justify
This is true particularly if the project is clearly worth pursuing, or where agreements for sharing gains and losses can be reached informally
Are sufficient data available to have confidence in relative valuation estimates?
Relative value works best if the expected future cash flows, total risk, and market risk of the comparables are believed to be proportional to those of the subject venture
4
Implementing the Continuing Value Concept
It is not practical to value a going concern by forecasting cash flows explicitly into the indefinite future
Instead, the normal approach is to summarize the value of cash flows beyond a rapid growth, explicit value period as a single “continuing value”
Continuing value is used to convert cash flows after the explicit value period to a single estimate of value that is equivalent to valuing each subsequent cash flow
Cash flows after the first few years are valued implicitly by applying a multiplier to the last explicit cash flow
The overall valuation is thus divided into two periods
For the first, an explicit cash flow projection is made for each year or other interval
We refer to this as the “explicit value period”
We refer to the period after the explicit value period as the “continuing value period”
5
Implementing the Continuing Value Concept
Determine the “explicit value period” and the “continuing value period”
Determine which multiplier (sales, earnings, etc.) to use for continuing value
Use an appropriate method and data to forecast the multiple at the end of the explicit value period
Estimate continuing value using the multiple
6
Using Continuing Value to Estimate the Value of a Venture
Figure 11.1
-- Figure 11.1 --
Using continuing value to estimate the value of a new venture
A common approach used in DCF valuation is to divide the forecast into two periods. During the explicit period, cash flows are forecast individually and valued directly. During the continuing value period, cash flows are converted to a capitalized value at the end of the explicit value period. This capitalized value is then discounted back to Time 0. Normally, the continuing value period begins when the venture reaches a point of stable growth.
7
Present value is the sum of discounted present values of explicit and continuing value cash flows:
PV is present value of the venture
Ct is the annual cash flow of each explicit period, t
CVT is the continuing value at the end of the explicit value period, T
rt is the discount rate for period t cash flows
Implementing the Continuing Value Concept
8
Determining the explicit value period
Continuing value estimates are most reliable if made for a period when a firm has reached a point of stable growth
It does not work well for valuing the early stages of a new venture
Thus, explicit cash flows are normally estimated for periods when the venture has not yet achieved profitability, and during rapid growth periods
In the figure, the continuing value period begins at the point where the venture is expected to be in steady state
Sometimes continuing value estimates are applied at earlier stages
If comparable companies have gone public at similar stages, underwriters are likely to use a combination of continuing value and explicit DCF methods
Implementing the Continuing Value Concept
9
Determining which multiplier to use
Multipliers can be tied to any accounting or non-accounting items at the end of the explicit value period, and derived from theory or based on comparables
A free cash flow measure seems like the obvious choice, since that is the source of the investor’s return
Sometimes EBIT or sales, can yield more reliable estimates
Other multiples could include: assets, recurring subscription revenues, monthly active users, etc.
Continuing value can therefore be based on a relevant multiple that has a stronger relationship to expected future cash flows than does cash flow at the end of the explicit value period
The strength of the relationship can be evaluated by comparing measures of dispersion of alternative multipliers across a sample of comparable firms
Implementing the Continuing Value Concept
10
Determining the multiplier
It is helpful to test whether the multiple derived from the comparables really makes sense for the venture
The multiple implies something about expected growth and cost of capital, and those implications should be assessed
The equation below summarizes the implicit assumptions
You can easily assess whether a multiple derived from comparables implies a sensible value for expected growth and cost of capital
Vt is value at time t, Ct+1 is cash flow at time t + 1, r is the discount rate, and g is the expected growth rate of cash flows
Implementing the Continuing Value Concept
11
Determining the multiplier
Generally, Ct is the cash flow generated over a year, and Vt is value at the end of that same year
Often referred to as a trailing value
It implies that prior earnings can be used in a consistent way, as shown in the equation, to predict future earnings
Vt, the value at the end of period t, is a function of the cash flows from period t + 1 onward
In the equation, Ct is increased by g, which effectively means that the numerator represents the next period’s cash flow
Implementing the Continuing Value Concept
12
Determining the multiplier
The prior equation is the standard expression for the PV of a growing perpetuity of cash flows
The equation can be rearranged to calculate the cash flow multiplier
where Vt/Ct is the cash flow multiplier
Implementing the Continuing Value Concept
13
Estimating the multiple
The relation between value in one period and all future cash flows
The implied cash flow multiple
Implementing the Continuing Value Concept
14
Determining the multiplier
The equation shows how the expected growth rate and discount rate affect the multiplier
The assumptions of a 10% discount rate and 4% annual growth rate of cash flows yield the following calculation and cash flow multiple:
Higher expected growth would increase the multiplier, as it simultaneously increases the numerator and reduces the denominator
A higher discount rate would reduce the multiplier by increasing the denominator
Implementing the Continuing Value Concept
15
Determining the multiplier
Although the connections between value and other accounting and non-accounting streams are indirect, the same principle applies
Higher expected growth rates imply higher multiples, and larger discount rates imply lower multiples
This suggests a way to use market data to estimate a multiplier
If, relative to comparable firms, the venture has high expected growth, a higher multiplier is implied
If the comparable firm cash flow is the expected cash flow, there is no reason the discount rate should be different from that of the comparable
Implementing the Continuing Value Concept
16
Determining the multiplier
There are two important issues to keep in mind
First, the cash flows of comparables are based on audited publicly reported information, whereas the venture’s forecast has not been
Second, the comparables have survived long enough to have gone public, whereas the venture is at an earlier stage
How best to deal with these issues depends on the purpose of the valuation and comfort level with the financial projections
If the projections of the venture were prepared consistently with GAAP and are unbiased, it is reasonable to assume that the projections are comparable to the reported numbers of the public companies
If the entrepreneur prepared the projections, the projections are likely to reflect the entrepreneur’s inherent optimism and direct application would overestimate continuing value
Implementing the Continuing Value Concept
17
Determining the multiplier
One solution to this survival bias is to base the continuing value estimate on multipliers from private transactions
A second is to adjust the public company multiplier for an estimate of the bias in the accounting projections for the venture
If, for example, you believe that the venture’s probability of failure is 30% and is not reflected in its projected cash flows, it would be appropriate to adjust the public company multiplier down by 30%
This solution is implicit in the actual multipliers that are frequently used in private transaction valuations
Such adjustments are often characterized (incorrectly, we believe) as “illiquidity discounts”
This leads to the third solution
Develop a set of projections that reflect the true expectations, including the risk of failure and are not positively biased
Implementing the Continuing Value Concept
18
Forecasting the multiple
Valuation must be based on a multiple expected to be accurate when the continuing stream of cash flows is being capitalized
The multiples observable today are not the ones you would want to use
To illustrate, consider the data shown in following figure
In 2002, the S&P 500 Index P/E ratio was around 37.3, a historical high, and the aggregate dividend yield was near a historical low
If the basis for equity valuation is the PV of expected future dividends, then either the expected growth rate must have been very high in 2002 or the cost of equity capital must have been very low
The true explanation probably involves a combination of both: rising dividends combined with low cost of equity
Implementing the Continuing Value Concept
19
Historical Price/Earnings Ratios for the S&P 500 Index
Figure 11.2
-- Figure 11.2 --
Historical Price/Earnings ratios of the S&P 500 Index: 1980–2017
The P/E ratio each year is calculated as monthly average of the value of the S&P 500 divided by aggregate earnings over the preceding (trailing) 12 months.
Source: www.quandl.com
20
Forecasting the multiple
P/E multiples are influenced by factors that can affect either the numerator or the denominator
In 2002, they soared in part because earnings levels of technology-related firms declined sharply after the Internet bubble
The 2009 multiple was caused by a large, widespread drop in earnings
Suppose that in 2009 you want to select a multiple for estimating continuing value where harvesting is expected in five years
Between 1980 and 2008 there was a great deal of variation in the ratio
The simple average over the 53-year period is 19.6, but the most recent historical period has seen higher multiples, on average
Implementing the Continuing Value Concept
21
Forecasting the multiple
How can we best estimate an appropriate multiple for 2020?
One valid approach is to use statistical techniques—regression analysis, exponential smoothing, or the like—to estimate future P/E multiples
For some valuations it may be sufficient to recognize that historical multiples appear to be mean reverting and that the multiple in 2008 is historically high
Even if the appropriate multiple is not the S&P 500 Index P/E ratio, if the multiple you use is based on comparables, it is likely to be highly correlated with the S&P Index multiple
Implementing the Continuing Value Concept
22
Methods of New Venture Valuation
The RADR method
Based on the CAPM
The CEQ method
Based on the CAPM
The Venture Capital method
The First Chicago method
The Relative Value method
23
Implementing DCF Valuation Methods
CAPM-based approaches require assumptions about the risk-free rate, the market risk premium, and beta
In lieu of beta, they may require assumptions about market risk, project risk, and correlation
In the RADR, project risk is the standard deviation of holding-period returns, correlation is between project returns and market returns
In the CEQ, project risk is the standard deviation of cash flows, and the correlation is between project cash flows and the market
Estimating the risk-free rate
Estimating the market risk premium
Estimating the new venture beta
Estimating the components of beta separately
24
Estimating the Risk-free Rate
The appropriate risk-free rate for valuing a future cash flow is:
One available in the market as of the valuation date,
and for a holding period of the same duration as the cash flow
Thus, for a cash flow expected in five years, we would use the current risk-free rate for an instrument that would mature in five years
We normally assume we can infer the risk-free rate from current interest rates of U.S. Treasury securities of appropriate maturities
Cash flow projection can be in real or nominal terms
If nominal, the risk-free rate is the nominal rate that can be inferred directly from market data
If real, the real risk-free rate must be estimated by subtracting the rate of inflation that is expected in the market for the holding period
Publicly available inflation forecasts or historical data can be used to adjust the nominal rate
25
Estimating the Risk-free Rate
U.S. Treasury yields at the time of this writing were as follow:
U.S. Treasury yields, January 26, 2018
| Maturity | Yield % | Maturity | Yield % | |
| 3-month | 1.50 | 5-year | 2.47 | |
| 6-month | 1.87 | 10-year | 2.61 | |
| 1-year | 1.83 | 20-year | 2.74 | |
| 2-year | 2.04 | 30-year | 2.94 | |
| 3-year | 2.21 |
Source: Based on http://www.wsj.com/mdc/public/page/2_3020-tstrips.html . Maturities of one year or less are from zero-coupon securities. Maturities greater than one year are based on STRIP (Separate Trading of Registered Interest and Principal) yields.
26
Estimating the Market Risk Premium
The market risk premium is the expected difference between the market return and the risk-free rate from investment until a cash flow is received
In contrast to the current risk-free rate, which is observable, the current market risk premium is not
Three approaches are used to estimate the risk premium:
(1) a long-term historical average
(2) a risk premium implied by discounting a forecast of future dividends (i.e., the IRR that makes the PV of expected dividends equal today’s market price)
(3) a consensus estimate
The easiest, but not necessarily most accurate, is to extrapolate from historical data
27
Historical Stock and Bond Returns
| 1928-2017 | 1960-2017 | |||
| Series | Arithmetic Mean | Standard Deviation | Arithmetic Mean | Standard Deviation |
| S&P 5001,2 | 11.53% | 19.62% | 11.27% | 16.31% |
| U.S. Treasury Bonds (LT)2 | 5.15% | 7.72% | 6.64% | 9.05% |
| U.S. Treasury Bills (ST)2 | 3.44% | 3.05% | 4.64% | 3.11% |
| Inflation3 | 3.05% | 3.84% | 3.78% | 2.81% |
| Series | Geometric Mean | Geometric Mean | ||
| S&P 5001,2 | 10.15% | 9.32% | ||
| U.S. Treasury Bonds (LT)2 | 6.18% | 4.93% | ||
| U.S. Treasury Bills (ST)2 | 4.62% | 3.40% | ||
| Inflation3 | 3.78% | 3.04% | ||
| 1 - Composite Total Return Index (includes dividend reinvestment). | ||||
| 2 - Sources: Stock, T-bond, and T-bill annual returns data downloaded from http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/histretSP.html | ||||
| 3 - Annual CPI (inflation) data from U.S. Dept. of Labor, Bureau of Labor Statistics |
Table 11.1
28
Estimating the Market Risk Premium
The table shows arithmetic average returns since 1928 and since 1960 and the respective standard deviations of returns
It also shows the geometric averages calculated based on the beginning and ending values from 1928 through 2017 and 1960 through 2017
To estimate the expected market risk premium, use the difference between the historical average return on the S&P 500 (the market) and the historical average risk-free rate
For valuing a near-term cash flow, use the historical short-term risk-free
For valuing a longer-term cash flow, such as five years, use the historical long-term rate, represented by U.S. Treasury bonds
29
Estimating the Market Risk Premium
Using historical data to estimate the expected risk premium requires making some choices
Over how long of a period should average returns be measured?
Is it better to use arithmetic or geometric averages?
In using historical data, balance the relevance of older data with the statistical unreliability
The prior table includes historical data for two time periods. Other windows could be used
The historical averages over a long period is more reliable as long as the fundamentals that drive the premia are consistent
The period since 1960 is a time when modern portfolio theory was generally accepted so investors would mainly have been concerned with systematic risk
In earlier years the premium could have been affected by total risk
30
Estimating the Market Risk Premium
The table also reports historical inflation rates that can be used to infer the historical real rates for riskless debt
Based on the period since 1960, the apparent short-term real risk-free rate is 0.86% and the real long-term risk-free rate averaged 2.86%
Similarly, the risk premium of the S&P 500 averaged 6.63% above the short-term risk-free rate and 4.63% above the long-term Treasury rate
The historical average may not be the best measure of the market risk premium
Recent forward-looking estimates of the risk premium (derived by discounting forecasts of future dividends) suggest that the long-term historical average overstates the market risk premium somewhat
Recent estimates are generally in the 2.5 to 5.5% range
31
The final variable for the RADR form of the CAPM is the beta of the venture
Equation (11.4) is the formula for computing the asset beta based on its returns and the returns of the market:
The expression implies several different approaches that can be used to estimate βA
Estimating the New Venture Beta
32
Estimating the New Venture Beta
Using the betas of comparable firms
For established public companies, beta is often estimated by regressing historical stock returns on historical returns of a market index
The regression coefficient is the beta term in Eq. (11.4)
For new ventures, there is no publicly traded stock, so the information needed to estimate the regression coefficient is not available
A common solution is to use beta estimates from comparable public firms
33
Estimating the New Venture Beta
The following table contains data on equity betas for a number of different industry (sector) groups
Ranges in the table are based on averages for firms in the sector and are estimated based on two years of weekly stock returns
The beta values vary in a systematic fashion across industries
Sectors with low equity betas include banks and public utilities
High equity beta sectors include cyclical industries
34
Average Beta Estimates for Selected Sectors
Table 11.2
Equity beta estimates and leverage ratios are estimated by Damodaran and are updated regularly. Asset betas are computed as discussed in this section of the text. Sectors reported are the five with the highest and lowest asset betas as well as a number of sectors that are the focus of high levels of entrepreneurial activity.
Source: http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/Betas.html .
35
From the table, public company equity betas are rarely less than 0.5 or greater than 2.0
Eq. (11.4) requires and estimate of the asset beta
The asset beta removes the effect of financial leverage and reflects only the market risk of the venture
If debt is riskless or has no market risk, asset betas can be derived from equity betas using Eq. (11.5)
βE is usually estimated for comparables by regression (or taken from a published source
Estimating the New Venture Beta
36
Estimating the New Venture Beta
If data are available for more than one comparable firm, we would estimate the new venture asset beta and appropriate discount rate as follows:
Step 1. Calculate or collect equity betas and E and V for the comparables
Step 2. Use Eq. (11.5) to convert each equity beta to an asset beta
Step 3. compute a weighted average asset beta for the new venture
Weightings based on your judgment about comparability.
Step 4. If valuing cash flows to all investors, use the weighted average βA in the CAPM to estimate rA, the discount rate
37
Estimating the New Venture Beta
While public-firm comparables for early-stage ventures are uncommon, the late 1990s provide a rare exception
During that period, a large number of “new economy” ventures went public at early stages
Findings are summarized in the following table
Based on a large sample from the last half of the 1990s, the average equity beta is close to 1.0, similar to the total risk of the “market”
Because these firms tend not to use debt financing, their equity betas are approximately equivalent to asset betas
The evidence suggests that reasonable estimates of beta for nonpublic ventures will generally be in the range of about 0.6 to 1.25
38
Beta Estimates for Newly Public, VC-backed Firms
| # of Observations | Mean β | Correlation with the Market | Standard Deviation of Returns | |
| All Observations | 2,623 | 0.99 | 0.195 | 1.20 |
| Industry | ||||
| Biotechnology | 501 | 0.75 | 0.149 | 1.04 |
| Broadcast and Cable TV | 105 | 0.80 | 0.237 | 0.87 |
| Communication Equipment | 247 | 1.16 | 0.215 | 1.20 |
| Communication Services | 407 | 1.02 | 0.241 | 1.04 |
| Computer Networks | 130 | 1.02 | 0.208 | 0.93 |
| Computer Services | 440 | 0.81 | 0.172 | 1.44 |
| Catalog/Mail Order (Internet) | 39 | 1.24 | 0.217 | 1.06 |
| Software | 754 | 1.20 | 0.200 | 1.37 |
| Age (Years After IPO) | ||||
| 0-1 years | 1,263 | 0.93 | 0.162 | 1.35 |
| 2-3 years | 957 | 0.96 | 0.212 | 1.04 |
| >3 years | 403 | 1.27 | 0.259 | 1.14 |
| Financial Condition | ||||
| No Revenue | 102 | 0.82 | 0.165 | 1.19 |
| Revenue, Negative Income | 1,475 | 1.14 | 0.197 | 1.35 |
| Positive Income | 1,033 | 0.82 | 0.200 | 1.00 |
| Employees | ||||
| 0 – 25 | 187 | 0.59 | 0.117 | 1.26 |
| 26 – 100 | 496 | 0.86 | 0.153 | 1.28 |
| Over 100 | 1,661 | 1.14 | 0.231 | 1.13 |
Table 11.3
Beta estimates and market correlations for newly public, VC-backed firms
Source: Kerins, Smith, and Smith (2004).
Beta estimates of recently public, VC-backed firms that went public during the 1995–2000 period. Betas and correlations are computed using the S&P 500 index as the “market.”
39
Estimating the Components of Beta Separately
Components of beta
The standard deviation of cash flows or holding period returns
The standard deviation of market returns
The correlation between project cash flows and the market
40
Estimating the Components of Beta Separately
The standard deviation of cash flows or holding-period returns
Using scenario analysis or simulation, cash flow standard deviation information is generated at the same time and is easily used
It is not easy to go from the cash flow standard deviation to the standard deviation of (equilibrium) holding-period returns
It is not possible to determine the correct standard deviation of holding-period returns without simultaneously determining the value of the cash flows
You can circumvent the problem by using the CEQ form of the CAPM
With the CEQ it is not necessary to determine the standard deviation of holding-period returns and PV of the cash flow simultaneously
41
Estimating the Components of Beta Separately
The standard deviation of market returns
In the following table, we report the standard deviation of a long-run historical average of annual holding-period returns for the S&P 500
| Standard deviation of market returns (S&P 500) | ||
| Holding Period Length | Standard Deviation | Variance |
| One year | 16.69% | 2.79% |
| Two year | 23.60% | 5.57% |
| Three year | 28.91% | 8.36% |
| Four year | 33.38% | 11.14% |
| Five year | 37.32% | 13.93% |
| Six year | 40.88% | 16.71% |
| Seven year | 44.16% | 19.50% |
| Eight year | 47.21% | 22.28% |
| Nine year | 50.07% | 25.07% |
| Ten year | 52.78% | 27.86% |
Table 11.4
Standard deviation of market return (S&P 500)
The 1-year standard deviation is computed over the 50 years ending with 2017, based on the annual holding-period returns of the S&P 500 index. Returns for longer holding periods are calculated assuming time-series independence of annual returns.
42
Estimating the Components of Beta Separately
The one-year standard deviation estimate is 16.7%
For longer holding periods, we assume the returns from one year to the next are independent of each other
Compute the standard deviation for a different holding period by:
multiplying by the square root of the time interval in years, or
by multiplying the annual variance by time, then taking the square root
For example, the two-year standard deviation is:
or
Table previous table reports market standard deviations for holding period returns up to 10 years
43
Estimating the Components of Beta Separately
The correlation between project cash flows and market returns
How can the correlation between venture cash flows and market returns be estimated?
One approach is to base the estimate on judgment, in light of the risks
Alternatively, it may be possible to use stock returns data for public companies to gain perspective on the range
A realistic range of values can be narrowed
A new venture with high idiosyncratic risk and little diversification, is unlikely to have a correlation with the market above 0.3
This is supported by Table 11.3, above, where the highest reported correlation is 0.26 and most correlations are in the 0.15 to 0.25 range
Correlations increase with financial maturity and size
A number around 0.1 is appropriate for early-stage ventures that have characteristics more like lotteries
44
Suppose some mature public companies are comparable to the venture and are expected to pay dividends at a constant rate
If we know the stock price, dividend level, and expected growth of dividends, we can use the dividend discount model:
D1 is the dividend expected next year, g is the expected rate of dividend growth, r is the unknown cost of capital, and P0 is the current stock price
For example, if the expected dividend is $1, expected growth of dividends is 3% per year, and current price is $12, estimated cost of capital is 11.3%:
Shortcuts for Estimating Opportunity Cost of Capital
45
Shortcuts for Estimating Opportunity Cost of Capital
If a public company cannot invest retained cash flows at a rate above cost of capital, then the earnings/price ratio is an estimate of cost of capital
With no growth, an investor is basically purchasing a perpetual stream of risky earnings
For example, if earnings are expected to be $1 and the current price is $9, the estimated cost of capital is 11.1%
If the company has attractive investment opportunities, the E/P ratio will be lower, depending on the value of growth opportunities
For most new ventures, these approaches are unlikely to be of much value as stand-alone methods
They can serve as a reality check on the cost-of-capital estimate you derive from a CAPM-based approach
46
Shortcuts for Application of DCF
Some venture cash flows are low risk and some are high
Ideally, we would discount each at a rate that takes account of its timing and risk characteristics; in practice, this is rarely done
Short of dealing with the unique characteristics of each cash flow in a period, we might use a weighted average discount rate for the aggregate
This would imply different discount rates for different periods and is rarely done
What usually happens in the RADR approach is that a single discount rate is used for all project cash flows
In part this is because the data on comparable public firms do not distinguish among separate cash flows based on risk or timing
In the CEQ, it is easier to take account of risk differences in cash flows
It could still make sense to simplify by using a single risk-free rate and market risk premium for all periods, but this does not automatically result in a single beta
47
Testing the Consistency of Assumptions
If market data for comparables are available, they can be used to see if the assumptions are internally consistent and reasonable
Compared to public companies, a new venture is likely to have higher total risk but more of the risk is likely to be idiosyncratic
Because these differences are offsetting, asset betas of new ventures are similar to those of public companies
New ventures have higher total risk (standard deviations) and lower correlations with the market
If the factors are offsetting, then you can use your assumptions about project risk to make an ex post check
Do the assumptions result in an implied beta that is reasonable in light of what you know about the betas of comparable public companies?
48
Testing the Consistency of Assumptions
If comparable firm data are available, why not just estimate the asset beta from comparables?
First, it is important to assess total risk, not only market risk:
It is unlikely that reasonable estimates of expected cash flows can be made without considering total risk
A reasonable measure of total risk is important for valuing from the perspective of an underdiversified entrepreneur
Total risk is critical for valuing complex financial claims and real options
Second, addressing the question of beta risk from both directions— using comparables for beta and inferring a beta from assumptions in the CEQ valuation—is a way to check the reasonableness of assumptions
49
New Venture Valuation: An Illustration
Micro Components, Inc. (MCI), will supply components to manufacturers of lithium-ion batteries
MCI seeks funds to increase production capacity to commercial scale
Scale-up will take one year, with revenues beginning in Year 2
MCI has prepared financial projections and developed three cash flow scenarios
It has also assigned a probability to each scenario
Micro Components, Inc. (MCI) cash flow forecast ($ thousands)
| Year | Continuing value | |||||||
| Scenario | Probability | 0 | 1 | 2 | 3 | 4 | 5 | |
| Success | 0.25 | ($3,000) | ($1,500) | $1,000 | $3,000 | $5,000 | $9,000 | |
| Likely | 0.50 | ($3,000) | ($1,500) | $500 | $500 | $500 | $500 | |
| Failure | 0.25 | ($3,000) | ($1,500) | $0 | $0 | $0 | $0 | |
| Expected | ($3,000) | ($1,500) | $500 | $1,000 | $1,500 | $2,500 |
50
Using the Relative Value Method
Because MCI is an early-stage venture with no revenue, comparable public companies for relative valuation are unlikely
However, the relative value approach can be used to estimate continuing value at the end of the explicit value period
Under the “success” scenario, we assumed that MCI will go public at the end of Year 5
Under the “likely” scenario that the venture will be sold in a trade sale (acquisition)
51
Using the RV Method to Estimate Continuing Value
MCI is an early-stage venture with no revenue
Use relative value to estimate continuing value at the end of Year 5
The MCI “success” scenario is IPO
Data on comparables for the success scenario
52
Calculate an average or weighted average IPO cash flow multiple
Continuing value = CF multiple x MCI Year-5 CFsuccess
= 12.0 x $9 million = $108 million
Using the RV Method to Estimate Continuing Value
53
Using the Relative Value Method
To estimate value under the “likely” scenario, data shown below are on comparable M&A transactions
Recent M&A transactions
Note: All monetary values in $ thousands.
Price paid is what the purchaser paid for the target firm’s assets and their associated cash flows
54
Using the Relative Value Method
For each comparable, we compute the ratio of price paid to CF to all investors, which is shown in the right-hand column below
Recent M&A transactions
Note: All monetary values in $ thousands.
Assuming the transactions are equally informative, we use the simple average of 8.0 times CF to all investors for the continuing value of MCI if it develops according to the expected scenario
Continuing value = CF multiple x MCI Year 5 CFlikely
= 8.0 x $0.5 million = $4 million
| Target | Acquirer | Price paid | CF to investors | Price/Cash flow |
| Biros Inc. | Kinerion Inc. | $75,650 | $7,200 | 10.5 |
| Viage Ent. | Bantic Networks | $32,500 | 4,710 | 6.9 |
| Mecent Labs | Mercuron Co. | $145,950 | $17,388 | 8.4 |
| Protoscan Inc. | Neurovage, L.V. | $88,275 | $14,240 | 6.2 |
| Average | 8.0 |
55
New Venture Valuation: Cash Flows
Micro Components, Inc. (MCI) cash flow forecast ($ thousands)
| Year | Continuing value | |||||||
| Scenario | Probability | 0 | 1 | 2 | 3 | 4 | 5 | |
| Success | 0.25 | ($3,000) | ($1,500) | $1,000 | $3,000 | $5,000 | $9,000 | $108,000 |
| Likely | 0.50 | ($3,000) | ($1,500) | $500 | $500 | $500 | $500 | $4,000 |
| Failure | 0.25 | ($3,000) | ($1,500) | $0 | $0 | $0 | $0 | $0 |
| Expected | ($3,000) | ($1,500) | $500 | $1,000 | $1,500 | $2,500 | $29,000 |
56
MCI Valuation: Cash Flow Recap
Expected cash flow is a probability-weighted average of scenario cash flows
MCI is entirely equity financed and cash flows to shareholders are shown
This means the appropriate discount rate is the cost of equity
The explicit value period is five years, at the end of which MCI anticipates two possible exits for investors
If the “success” scenario is realized, MCI would go public
The continuing value multiple will be 12 times Year 5 cash flow
Under the “likely” scenario, MCI will be sold by acquisition
The continuing value multiple will be 8 times the Year 5 cash flow
The “failure” scenario results in full loss of the investment, so that continuing value in Year 5 is zero
57
Using the RADR form of the CAPM
Equation (11.8) is the CAPM model in RADR form
PV is the present value of all cash flows to investors
Times 0 to T comprise the explicit value period, where Time T cash flow includes continuing value
Cjt represents a particular expected cash flow, j, at time t
For example, depreciation cash flows are likely to be much less risky than net income or operating cash flow in the same period
βjt is the cash-flow-specific beta estimate
Using the RADR Form of the CAPM
58
Using the RADR Form of the CAPM
Equation (11.8) is very general
βjt, which reflects the riskiness of the cash flow, is specific to both time period t and cash flow j
Within a given period, cash flows can differ in risk
Over time the risk-free rate and market risk premium can vary
So the discount rate can differ from one period to the next and for different cash flows in the same period
The cash flows in Eq. (11.8) are expected cash flows in the statistical sense, including the risk of failure
Continuing value is also an expected value estimate conditional on the various assumptions about exit strategy
When the RADR is used, uncertainty is addressed in the discount rate, so a direct estimate of cash flow uncertainty is not needed
59
Using the RADR Form of the CAPM
Consider the venture’s cash flows at Time 0 and in Year 1
Cash is used to expand capacity, and revenue generation has not begun
If the costs of capacity are certain, there is no cash flow variance across scenarios for the first two years
Given this, the investment cash flows should be valued at the risk-free rate
The important risks start in Year 2, when product sales begin
Revenue-based cash flows are much riskier
This can be seen in the variation by scenario in each of the Years 2–5
Because these cash flows are risky, the discount rates should not be the same as those for the Time 0 and Year 1 investment cash flows
60
Using the RADR Form of the CAPM
It is common when using the RADR approach to apply a single discount rate to all cash flows
Doing so in this case results in underestimating the PV of investment outflows and overestimation of project NPV
To be consistent with standard practice (and illustrate an advantage of CEQ), we first follow the common approach
Suppose we have estimated the following CAPM parameters:
Current rate on long-term Treasury bonds 4.0%
Market risk premium 6.5%
61
Using the RADR Form of the CAPM
The final variable for implementing Eq. (11.8) is the estimate of beta
MCI has identified the following three public firms as being comparable
We have collected data on their equity betas and capital structures
| Comparable | Equity β | Market value of equity | Debt value |
| Genric, Inc. | 1.9 | $12.0 | $4.0 |
| Preces Systems | 1.5 | $24.0 | $3.0 |
| Visania Co. | 1.2 | $7.0 | $0.0 |
Note: All monetary values in $ millions.
62
The first step is to determine the asset beta using Eq. (11.5):
Based on a simple average of asset betas, MCI’s estimated βA is 1.32
Using the estimates of a 4.0% risk-free rate and a 6.5% market risk premium, the required return on assets, rA, is 12.58%
Using the RADR Form of the CAPM
| Comparable | Equity β | MV equity | Debt | Asset value | Equity to asset value | Asset β |
| Genric, Inc. | 1.9 | $12.0 | $4.0 | $16.0 | 0.75 | 1.43 |
| Preces Systems | 1.5 | $24.0 | $3.0 | $27.0 | 0.89 | 1.33 |
| Visania Co. | 1.2 | $7.0 | $0.0 | $7.0 | 1.00 | 1.20 |
63
Valuation Template 1
| Valuation by the RADR method based on discrete scenario cash flow forecast | ||||||||
| Project Information | YEAR | |||||||
| Cash Flows ($000s) | Probability | 0 | 1 | 2 | 3 | 4 | 5 | |
| Success Scenario | 0.25 | -$3,000 | -$1,500 | $1,000 | $3,000 | $5,000 | $117,000 | |
| Expected Scenario | 0.50 | -$3,000 | -$1,500 | $500 | $500 | $500 | $4,500 | |
| Failure Scenario | 0.25 | -$3,000 | -$1,500 | $0 | $0 | $0 | $0 | |
| Expected Cash Flow | -$3,000 | -$1,500 | $500 | $1,000 | $1,500 | $31,500 | ||
| Market Information | ||||||||
| Risk-free Rate | 4.00% | 8.16% | 12.49% | 16.99% | 21.67% | |||
| Market Rate | 10.50% | 22.10% | 34.92% | 49.09% | 64.74% | |||
| Market Risk Premium | 6.50% | 13.94% | 22.44% | 32.10% | 43.08% | |||
| Comparable firm beta | 1.32 | 1.32 | 1.32 | 1.32 | 1.32 | |||
| Estimated Cost of Capital | 12.58% | 26.56% | 42.10% | 59.36% | 78.53% | |||
| Market Value Estimate | ||||||||
| Present Value of Expected CF | -$3,000 | -$1,332 | $395 | $704 | $941 | $17,644 | ||
| Sum of PVs | $15,352 |
Table 11.5
Valuation Template 1: Valuation by the RADR method based on discrete scenario cash flow forecast
Value is estimated using the RADR form of the CAPM. Shaded cells are inputs. All dollar values are in thousands.
64
Using the RADR Form of the CAPM
The “Market information” section of the template provides the data and calculations used to estimate the RADR each year
The table uses the same annual discount rate each period
As a result, beta is assumed to be constant, consistent with the most common use of the RADR approach
Starting values for the relevant assumptions are shown in Year 1 for a one-year holding period
For longer holding periods, the cumulative risk-free rates and cumulative market rates are found by compounding
Summing the PVs of each year’s expected cash flow produces an estimate of $15,352 as the value of MCI
65
Using the CEQ Form of the CAPM
Inputs to the CEQ valuation
We have already estimated RPM and rF (6.5% and 4.0%)
We have also calculated the expected cash flows, Ct
We have the standard deviation of the market from Table 11.4
For correlation with the market, we use the overall mean of 0.195 from Table 11.3 as a reasonable estimate
The main differences from Table 11.5 is that Table 11.6 adds the “Standard deviation of CFs” line
Cash flow standard deviations are computed separately for each year
The cash flows, discount rate calculations, and CEQ PV computation are summarized in Table 11.6, “Valuation Template 2”
66
Valuation Template 2
| Valuation by the CEQ method based on discrete scenario cash flow forecast | ||||||||
| Project Information | YEAR | |||||||
| Cash Flows | Probability | 0 | 1 | 2 | 3 | 4 | 5 | |
| Success Scenario | 0.25 | -$3,000 | -$1,500 | $1,000 | $3,000 | $5,000 | $117,000 | |
| Expected Scenario | 0.50 | -$3,000 | -$1,500 | $500 | $500 | $500 | $4,500 | |
| Failure Scenario | 0.25 | -$3,000 | -$1,500 | $0 | $0 | $0 | $0 | |
| Expected Cash Flow | -$3,000 | -$1,500 | $500 | $1,000 | $1,500 | $31,500 | ||
| Standard Deviation of CFs | $0 | $0 | $354 | $1,173 | $2,031 | $49,398 | ||
| Market Information | ||||||||
| Risk-free Rate | 4.00% | 8.16% | 12.49% | 16.99% | 21.67% | |||
| Market Rate | 10.50% | 22.10% | 34.92% | 49.09% | 64.74% | |||
| Market Risk Premium | 6.50% | 13.94% | 22.44% | 32.10% | 43.08% | |||
| Market Variance | ||||||||
| Market Standard Deviation | 14.50% | 20.51% | 25.11% | 29.00% | 32.42% | |||
| Correlation | 0.195 | 0.195 | 0.195 | 0.195 | 0.195 | |||
| Market Value Estimate | ||||||||
| Present Value of Expected CF | $ (3,000) | $ (1,442) | $ 419 | $ 707 | $ 907 | $ 15,371 | ||
| Sum of PVs | $12,963 | |||||||
| Diagnostic Information | ||||||||
| Annualized Required Return | 4.0% | 9.2% | 12.2% | 13.4% | 15.4% | |||
| Std. Dev. of Returns | 0.00% | 0.00% | 84.39% | 165.76% | 223.82% | 321.36% | ||
| Covariance with Market | 0.00% | 0.00% | 3.37% | 8.12% | 12.66% | 20.32% | ||
| Beta | 0.00 | 0.00 | 0.80 | 1.29 | 1.51 | 1.93 |
Table 11.6
Using the CEQ Form of the CAPM
Consistent with earlier discussion, the Time 0 and Year 1 standard deviations are zero since cash flows are invariant across scenarios
The “Market information” section shows the Year 1 assumptions
In subsequent years, risk-free and market rates are compounded
Values for market standard deviation are from Table 11.4
The correlation of 0.195 is an assumption based on Table 11.3
68
Using the CEQ Form of the CAPM
In the “Market value estimate” panel, each period’s value reflects application of Eq. (11.6) to the data for that year
In Year 3, for example, the calculation is as follows
The numerator in the next-to-last term, $796, is the certainty equivalent CF
It is discounted at the three-year cumulative risk-free rate of 12.49%
Summing each PV gives a total (net) present value for MCI of $12,963
69
Using the CEQ Form of the CAPM
The “Diagnostic information” panel contains information that can be used to help understand the valuation and assess the reasonableness and internal consistency of assumptions
The first line shows the annualized required rate of return for each annual cash flow
Required rates are highly variable, and each is calculated by comparing the expected cash flow to its present value and converting to an annual rate
For example, the risk-adjusted holding-period return for the Year 2 cash flow is:
70
Using the CEQ Form of the CAPM
The “Diagnostic information” panel also shows the beta estimate for each period’s cash flow
The estimate for each year is calculated based on the data in the template and the formula for the cash flow beta
For example, the Year 4 beta estimate is computed as follows:
In the CEQ model, the estimated beta is different for each year
71
Comparing the CEQ and RADR Approaches
The CEQ produced a value estimate of $12,963
The RADR produces a value estimate of $15,352
The most important factor for the difference is that the RADR uses a single beta and the wrong discount rate to value each annual cash flow
The Year 1 cash flow is a good place to start
The standard deviation of the cash flows is zero
But in Table 11.5, the Year 1 cash flow is discounted at the RADR of 12.58%
In Table 11.6, the discount rate applied is the riskless rate of 4%
Rather than the correct PV of −$1,442, the RADR PV of the Year 1 cash flow is −$1,332, which contributes to overvaluation of the venture
In every year the RADR discount rate is incorrect
In Year 5, when the CEQ estimates the cumulative required risk-adjusted return at 108%, but the RADR rate is only 78.5%
72
Comparing the CEQ and RADR Approaches
Strengths of the RADR approach are:
Valuation is based on expected cash flows
The discount rate is intended to be opportunity cost of capital
Market data can be used to estimate cost of capital
It is unnecessary to estimate the total risk or market correlation
The main disadvantages are:
Holding-period returns and cost of capital must be determined simultaneously
Truly comparable firms are unlikely to be available for most new ventures
The appropriate discount rate for valuing a single cash flow cannot normally be determined based on data from comparable firms
If information on total risk is not generated, it is difficult to value complex financial claims
73
Comparing the CEQ and RADR Approaches
The benefits of the CEQ form of the CAPM are:
Valuation is based on expected cash flows
Cost of capital is used to value each annual cash flow
Cash flows that differ in terms of total risk are handled easily
Cash flows at different times can easily be valued separately
Any financial claim can be valued, as long as the CAPM assumptions hold
A measure of the total risk of cash flows is generated and is useful for valuation by an underdiversified entrepreneur
The main disadvantages of the CEQ approach are:
An estimate of the full distribution of cash flow possibilities is required
The correlation between venture cash flows and the market can be difficult to estimate
74
Using the Venture Capital Method
Step 1: Select a final year of the continuing value period for the valuation
Step 2: Use the appropriate P/E ratio or other multiple and the harvest-date cash flow projection to compute continuing value
Step 3: Convert the continuing value estimate to PV by discounting at a hurdle rate high enough to counter the bias in projections
Step 4: Compute the minimum fraction of ownership an investor would require in exchange for a given amount of capital
75
Using the Venture Capital Method
In the example, the final year is Year 5, and under the “success” scenario MCI expects to go public with a continuing value of 12.0 times cash flow to shareholders
We know from the CEQ model that the correct PV is $12,963
In Table 11.7, we compute the present values of MCI’s “success” scenario cash flows at hurdle rates of both 40% and 60%
At 40%, the value is $23,588, and at 60%, it is $12,106, slightly lower than the CEQ valuation of $12,963
In Table 11.7, we also solve for the single hurdle rate that generates the true PV, which works out to be 57.84%
76
Valuation at Various Discount Rates by the VC Method
| Cash Flows | Total | 0 | 1 | 2 | 3 | 4 | 5 |
| Success Scenario | $ (3,000) | $ (1,500) | $ 1,000 | $ 3,000 | $ 5,000 | $ 117,000 | |
| Discount Rate = | 40% | ||||||
| Present Value | $23,588 | -$3,000 | -$1,071 | $510 | $1,093 | $1,302 | $21,754 |
| Discount Rate = | 60% | ||||||
| Present Value | $12,106 | -$3,000 | -$938 | $391 | $732 | $763 | $11,158 |
| Implied Single Rate | |||||||
| Rate | 57.84% | ||||||
| Present Value | $12,963 | -$3,000 | -$950 | $401 | $763 | $806 | $11,943 |
Table 11.7
77
Critiquing the Venture Capital Method
Advantages of the VC method:
Valuation can be driven by a “success” scenario projection
Negotiation may be facilitated by focusing on the entrepreneur’s projections
Investor’s experience may be easiest to apply without formal analysis when comparisons of ventures are made on the basis of “success” scenarios
Easy to use and may be adequate for simple investment decisions
78
Critiquing the Venture Capital Method
Disadvantages of the VC method
Lack of precision due to reliance on unnecessarily limited information and “rules of thumb”
Biases resulting from discounting optimistic cash flow projections at a hurdle rate that is above cost of capital
Lack of information about uncertainty, which would be useful for valuing complex financial claims
79
Using the First Chicago Method
The First Chicago method uses discrete scenarios and probabilities
Calculate expected cash flow based on scenarios
Discount expected cash flows to compute PV
Same as applying the RADR approach to expected CFs from discrete scenarios
80
Critiquing the First Chicago Method
Advantages of the First Chicago method
Discrete scenarios provide a simple method of estimating risk and expected return
Intent is to value expected cash flows
Uses an estimate of the opportunity cost of capital as the discount rate
Because information about total risk is derived, the method provides a basis for valuing complex financial claims
81
Critiquing the First Chicago Method
Disadvantages of the First Chicago method
Discrete scenarios discard information about risk that could be useful, especially for valuing complex claims
No guidance is provided about how to determine the discount rate(s) to be used in the valuation
No basis is provided for assigning probabilities to the different scenarios used in the valuation
82
Cost of Capital for Non-U.S. Investors
Opportunity cost is always the guiding principle of investing
Ability to diversify is a determinant of opportunity cost
In all developed countries and many others, investors retain the opportunity to invest in a diversified market portfolio
Because of currency exchange rates, doing so could subject the investor to somewhat different risks
Except for differences in expected inflation, the cost of capital for U.S. investors is likely to be similar to that of well-diversified portfolios throughout the world
The practical challenge of estimating opportunity cost may be greater in emerging economies
Prohibitions may exist against investments in foreign portfolios; exchange rates may be subject to dramatic swings or artificially constrained; opportunities to diversify domestically may be limited; and investors may face other risks, such as potential expropriation
83
Some Practical Caveats on Implementation
Be cautious in relying on comparable-firm valuations that are based on private transactions
VC funding rounds often include sweeteners that make the shares issued in those rounds more valuable
Do not forget about stock-based compensation
VC-backed private firms often have established large stock-based option pools
Watch for bias in the selection and use of comparable firms
Paleari, Signori, and Vismara (2014) find that underwriters systematically exclude firms that would make a prospective issuer appear to be overvalued
84
Valuation in Practice – Summary
The objective is to value a venture’s future cash flows
The continuing value concept as a simplification
Information requirements for DCF methods
RADR and CEQ approaches
Comprehensive valuation example: MCI
RADR and CEQ methods
Relative Value method
Venture Capital method
First Chicago method
85
Chart1
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
Figure 11.1
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
Data
| Year | Cash Flow Forecast |
| 0 | |
| 1 | -$125 |
| 2 | -$25 |
| 3 | $100 |
| 4 | $400 |
| 5 | $1,000 |
| 6 | $1,050 |
| 7 | $1,103 |
| 8 | $1,158 |
| 9 | $1,216 |
| 10 | $1,276 |
| 11 | $1,340 |
| 12 | $1,407 |
| 13 | $1,477 |
| 14 | $1,551 |
𝑉
𝑡
=
𝐶
𝑡+1
𝑟−𝑔
=
𝐶
𝑡
ሺ
1+𝑔
ሻ
𝑟−𝑔
(11.2)
Chart1
| 1980 |
| 1981 |
| 1982 |
| 1983 |
| 1984 |
| 1985 |
| 1986 |
| 1987 |
| 1988 |
| 1989 |
| 1990 |
| 1991 |
| 1992 |
| 1993 |
| 1994 |
| 1995 |
| 1996 |
| 1997 |
| 1998 |
| 1999 |
| 2000 |
| 2001 |
| 2002 |
| 2003 |
| 2004 |
| 2005 |
| 2006 |
| 2007 |
| 2008 |
| 2009 |
| 2010 |
| 2011 |
| 2012 |
| 2013 |
| 2014 |
| 2015 |
| 2016 |
| 2017 |
Figure 10.2
| 1956 |
| 1957 |
| 1958 |
| 1959 |
| 1960 |
| 1961 |
| 1962 |
| 1963 |
| 1964 |
| 1965 |
| 1966 |
| 1967 |
| 1968 |
| 1969 |
| 1970 |
| 1971 |
| 1972 |
| 1973 |
| 1974 |
| 1975 |
| 1976 |
| 1977 |
| 1978 |
| 1979 |
| 1980 |
| 1981 |
| 1982 |
| 1983 |
| 1984 |
| 1985 |
| 1986 |
| 1987 |
| 1988 |
| 1989 |
| 1990 |
| 1991 |
| 1992 |
| 1993 |
| 1994 |
| 1995 |
| 1996 |
| 1997 |
| 1998 |
| 1999 |
| 2000 |
| 2001 |
| 2002 |
| 2003 |
| 2004 |
| 2005 |
| 2006 |
| 2007 |
| 2008 |
Chart2
| 12.5786163522 |
| 13.2450331126 |
| 12.6742712294 |
| 16.051364366 |
| 17.3010380623 |
| 16.9491525424 |
| 21.645021645 |
| 17.1821305842 |
| 18.1818181818 |
| 18.7969924812 |
| 17.8890876565 |
| 15.0829562594 |
| 17.4520069808 |
| 17.6366843034 |
| 16.4473684211 |
| 15.503875969 |
| 18.4842883549 |
| 18.1818181818 |
| 14.0449438202 |
| 8.6281276963 |
| 10.9289617486 |
| 11.2359550562 |
| 9.2678405931 |
| 8.3125519534 |
| 7.4294205052 |
| 7.8988941548 |
| 8.3612040134 |
| 8.6206896552 |
| 12.4533001245 |
| 9.9800399202 |
| 12.315270936 |
| 16.4203612479 |
| 18.2481751825 |
| 12.4843945069 |
| 13.4770889488 |
| 15.455950541 |
| 20.8768267223 |
| 23.6966824645 |
Sheet17
| SUMMARY OUTPUT | ||||||||
| Regression Statistics | ||||||||
| Multiple R | 0.4999629763 | |||||||
| R Square | 0.2499629777 | |||||||
| Adjusted R Square | 0.2291286159 | |||||||
| Standard Error | 4.190780928 | |||||||
| Observations | 38 | |||||||
| ANOVA | ||||||||
| df | SS | MS | F | Significance F | ||||
| Regression | 1 | 210.710126034 | 210.710126034 | 11.9976306868 | 0.0013927161 | |||
| Residual | 36 | 632.2552123192 | 17.5626447866 | |||||
| Total | 37 | 842.9653383532 | ||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
| Intercept | 8.9192344546 | 2.4843530422 | 3.5901638387 | 0.0009776817 | 3.8807395881 | 13.9577293211 | 3.8807395881 | 13.9577293211 |
| P/E | -0.5703749611 | 0.1646693259 | -3.4637596174 | 0.0013927161 | -0.9043393933 | -0.236410529 | -0.9043393933 | -0.236410529 |
| RESIDUAL OUTPUT | ||||||||
| Observation | Predicted 5 Year Change | Residuals | ||||||
| 1 | 1.744706642 | 2.6258295482 | ||||||
| 2 | 1.3645992082 | 7.0353893243 | ||||||
| 3 | 1.6901474951 | 2.8177118597 | ||||||
| 4 | -0.2360618713 | 2.3665156872 | ||||||
| 5 | -0.9488444572 | 2.4447988762 | ||||||
| 6 | -0.7481377675 | 1.6880728817 | ||||||
| 7 | -3.4265439243 | -3.1355214613 | ||||||
| 8 | -0.8810226091 | 1.1508990057 | ||||||
| 9 | -1.4512193837 | 0.9060855052 | ||||||
| 10 | -1.8020994008 | -0.5475246594 | ||||||
| 11 | -1.2842532217 | -1.1009584658 | ||||||
| 12 | 0.3162938648 | 3.0850382307 | ||||||
| 13 | -1.0349533483 | 1.7647645493 | ||||||
| 14 | -1.1402886689 | -2.4514518142 | ||||||
| 15 | -0.4619326688 | -7.3573080559 | ||||||
| 16 | 0.0762118018 | -4.6511260222 | ||||||
| 17 | -1.6237407969 | -5.6245925018 | ||||||
| 18 | -1.4512193837 | -7.462758205 | ||||||
| 19 | 0.9083501694 | -6.6407420362 | ||||||
| 20 | 3.9979664554 | -5.1966736465 | ||||||
| 21 | 2.6856283223 | -5.7156959161 | ||||||
| 22 | 2.5105270264 | -5.3852780692 | ||||||
| 23 | 3.6330902367 | -4.2802411747 | ||||||
| 24 | 4.1779629575 | -0.0372147864 | ||||||
| 25 | 4.6816790229 | -2.1310596079 | ||||||
| 26 | 4.4139030083 | 0.0024737729 | ||||||
| 27 | 4.1502130407 | 3.9089441939 | ||||||
| 28 | 4.0022089278 | 5.6252765995 | ||||||
| 29 | 1.8161838804 | -1.7850894981 | ||||||
| 30 | 3.2268695733 | 0.2701794553 | ||||||
| 31 | 1.8949122735 | 1.2457673315 | ||||||
| 32 | -0.4465284535 | 4.9029939279 | ||||||
| 33 | -1.4890677553 | 6.9375750373 | ||||||
| 34 | 1.7984484233 | 8.1386817335 | ||||||
| 35 | 1.2322403696 | 2.4433293437 | ||||||
| 36 | 0.103547266 | 0.860863441 | ||||||
| 37 | -2.9883847751 | 1.1955275184 | ||||||
| 38 | -4.5967598844 | 2.0865180979 |
Sheet17
| 12.5786163522 | 12.5786163522 |
| 13.2450331126 | 13.2450331126 |
| 12.6742712294 | 12.6742712294 |
| 16.051364366 | 16.051364366 |
| 17.3010380623 | 17.3010380623 |
| 16.9491525424 | 16.9491525424 |
| 21.645021645 | 21.645021645 |
| 17.1821305842 | 17.1821305842 |
| 18.1818181818 | 18.1818181818 |
| 18.7969924812 | 18.7969924812 |
| 17.8890876565 | 17.8890876565 |
| 15.0829562594 | 15.0829562594 |
| 17.4520069808 | 17.4520069808 |
| 17.6366843034 | 17.6366843034 |
| 16.4473684211 | 16.4473684211 |
| 15.503875969 | 15.503875969 |
| 18.4842883549 | 18.4842883549 |
| 18.1818181818 | 18.1818181818 |
| 14.0449438202 | 14.0449438202 |
| 8.6281276963 | 8.6281276963 |
| 10.9289617486 | 10.9289617486 |
| 11.2359550562 | 11.2359550562 |
| 9.2678405931 | 9.2678405931 |
| 8.3125519534 | 8.3125519534 |
| 7.4294205052 | 7.4294205052 |
| 7.8988941548 | 7.8988941548 |
| 8.3612040134 | 8.3612040134 |
| 8.6206896552 | 8.6206896552 |
| 12.4533001245 | 12.4533001245 |
| 9.9800399202 | 9.9800399202 |
| 12.315270936 | 12.315270936 |
| 16.4203612479 | 16.4203612479 |
| 18.2481751825 | 18.2481751825 |
| 12.4843945069 | 12.4843945069 |
| 13.4770889488 | 13.4770889488 |
| 15.455950541 | 15.455950541 |
| 20.8768267223 | 20.8768267223 |
| 23.6966824645 | 23.6966824645 |
Chart3
| 1955 |
| 1956 |
| 1957 |
| 1958 |
| 1959 |
| 1960 |
| 1961 |
| 1962 |
| 1963 |
| 1964 |
| 1965 |
| 1966 |
| 1967 |
| 1968 |
| 1969 |
| 1970 |
| 1971 |
| 1972 |
| 1973 |
| 1974 |
| 1975 |
| 1976 |
| 1977 |
| 1978 |
| 1979 |
| 1980 |
| 1981 |
| 1982 |
| 1983 |
| 1984 |
| 1985 |
| 1986 |
| 1987 |
| 1988 |
| 1989 |
| 1990 |
| 1991 |
| 1992 |
| 1993 |
| 1994 |
| 1995 |
| 1996 |
| 1997 |
Chart4
| 1955 | 1955 |
| 1956 | 1956 |
| 1957 | 1957 |
| 1958 | 1958 |
| 1959 | 1959 |
| 1960 | 1960 |
| 1961 | 1961 |
| 1962 | 1962 |
| 1963 | 1963 |
| 1964 | 1964 |
| 1965 | 1965 |
| 1966 | 1966 |
| 1967 | 1967 |
| 1968 | 1968 |
| 1969 | 1969 |
| 1970 | 1970 |
| 1971 | 1971 |
| 1972 | 1972 |
| 1973 | 1973 |
| 1974 | 1974 |
| 1975 | 1975 |
| 1976 | 1976 |
| 1977 | 1977 |
| 1978 | 1978 |
| 1979 | 1979 |
| 1980 | 1980 |
| 1981 | 1981 |
| 1982 | 1982 |
| 1983 | 1983 |
| 1984 | 1984 |
| 1985 | 1985 |
| 1986 | 1986 |
| 1987 | 1987 |
| 1988 | 1988 |
| 1989 | 1989 |
| 1990 | 1990 |
| 1991 | 1991 |
| 1992 | 1992 |
| 1993 | 1993 |
| 1994 | 1994 |
| 1995 | 1995 |
| 1996 | 1996 |
| 1997 | 1997 |
Figure 9-3
| 1955 | 1955 |
| 1956 | 1956 |
| 1957 | 1957 |
| 1958 | 1958 |
| 1959 | 1959 |
| 1960 | 1960 |
| 1961 | 1961 |
| 1962 | 1962 |
| 1963 | 1963 |
| 1964 | 1964 |
| 1965 | 1965 |
| 1966 | 1966 |
| 1967 | 1967 |
| 1968 | 1968 |
| 1969 | 1969 |
| 1970 | 1970 |
| 1971 | 1971 |
| 1972 | 1972 |
| 1973 | 1973 |
| 1974 | 1974 |
| 1975 | 1975 |
| 1976 | 1976 |
| 1977 | 1977 |
| 1978 | 1978 |
| 1979 | 1979 |
| 1980 | 1980 |
| 1981 | 1981 |
| 1982 | 1982 |
| 1983 | 1983 |
| 1984 | 1984 |
| 1985 | 1985 |
| 1986 | 1986 |
| 1987 | 1987 |
| 1988 | 1988 |
| 1989 | 1989 |
| 1990 | 1990 |
| 1991 | 1991 |
| 1992 | 1992 |
| 1993 | 1993 |
| 1994 | 1994 |
| 1995 | 1995 |
| 1996 | 1996 |
| 1997 | 1997 |
Sheet1
| Year | e/p | Year | P/E | 5 Year Change | Min | Max | Difference | Expected Terminal P/E | Year | Adjusted for Market Timing | P/E | ||||||||||||||||
| 1955 | 0.0795 | 1955 | 12.6 | 4.3705361902 | 12.5786163522 | 13.2450331126 | 0.6664167604 | 14.3257672956 | 1955 | 16.1257672956 | 12.6 | 12.6 | 16.9 | SUMMARY OUTPUT | |||||||||||||
| 1956 | 0.0755 | 1956 | 13.2 | 8.3999885324 | 12.6742712294 | 16.051364366 | 3.3770931366 | 14.5886953642 | 1956 | 16.3886953642 | 13.2 | 13.2 | 21.6 | ||||||||||||||
| 1957 | 0.0789 | 1957 | 12.7 | 4.5078593548 | 12.6742712294 | 17.3010380623 | 4.6267668329 | 14.3635069708 | 1957 | 16.1635069708 | 12.7 | 12.7 | 17.2 | Regression Statistics | |||||||||||||
| 1958 | 0.0623 | 1958 | 16.1 | 2.1304538158 | 16.051364366 | 17.3010380623 | 1.2496736963 | 15.695905297 | 1958 | 17.495905297 | 16.1 | 16.1 | 18.2 | Multiple R | 0.3496274734 | ||||||||||||
| 1959 | 0.0578 | 1959 | 17.3 | 1.4959544189 | 16.9491525424 | 21.645021645 | 4.6958691026 | 16.1889515571 | 1959 | 17.9889515571 | 17.3 | 17.3 | 18.8 | R Square | 0.1222393702 | ||||||||||||
| 1960 | 0.059 | 1960 | 16.9 | 0.9399351142 | 16.9491525424 | 21.645021645 | 4.6958691026 | 16.0501186441 | 1960 | 17.8501186441 | 16.9 | 16.9 | 17.9 | Adjusted R Square | 0.0971604951 | ||||||||||||
| 1961 | 0.0462 | 1961 | 21.6 | -6.5620653856 | 17.1821305842 | 21.645021645 | 4.4628910608 | 17.9028268398 | 1961 | 19.7028268398 | 21.6 | 21.6 | 15.1 | Standard Error | 4.2328422412 | ||||||||||||
| 1962 | 0.0582 | 1962 | 17.2 | 0.2698763966 | 17.1821305842 | 18.7969924812 | 1.614861897 | 16.1420378007 | 1962 | 17.9420378007 | 17.2 | 17.2 | 17.5 | Observations | 37 | ||||||||||||
| 1963 | 0.055 | 1963 | 18.2 | -0.5451338785 | 17.8890876565 | 18.7969924812 | 0.9079048247 | 16.5364545455 | 1963 | 18.3364545455 | 18.2 | 18.2 | 17.6 | ||||||||||||||
| 1964 | 0.0532 | 1964 | 18.8 | -2.3496240602 | 15.0829562594 | 18.7969924812 | 3.7140362218 | 16.7791654135 | 1964 | 18.5791654135 | 18.8 | 18.8 | 16.4 | ANOVA | |||||||||||||
| 1965 | 0.0559 | 1965 | 17.9 | -2.3852116875 | 15.0829562594 | 17.8890876565 | 2.8061313971 | 16.420960644 | 1965 | 18.220960644 | 17.9 | 17.9 | 15.5 | df | SS | MS | F | Significance F | |||||||||
| 1966 | 0.0663 | 1966 | 15.1 | 3.4013320955 | 15.0829562594 | 17.6366843034 | 2.5537280439 | 15.3138295626 | 1966 | 17.1138295626 | 15.1 | 15.1 | 18.5 | Regression | 1 | 87.3307551486 | 87.3307551486 | 4.8741966902 | 0.0339080034 | ||||||||
| 1967 | 0.0573 | 1967 | 17.5 | 0.729811201 | 16.4473684211 | 17.6366843034 | 1.1893158823 | 16.2485148342 | 1967 | 18.0485148342 | 17.5 | 17.5 | 18.2 | Residual | 35 | 627.0933703552 | 17.9169534387 | ||||||||||
| 1968 | 0.0567 | 1968 | 17.6 | -3.5917404831 | 15.503875969 | 17.6366843034 | 2.1328083344 | 16.321377425 | 1968 | 18.121377425 | 17.6 | 17.6 | 14.0 | Total | 36 | 714.4241255038 | |||||||||||
| 1969 | 0.0608 | 1969 | 16.4 | -7.8192407248 | 15.503875969 | 18.4842883549 | 2.9804123859 | 15.8521447368 | 1969 | 17.6521447368 | 16.4 | 16.4 | 8.6 | ||||||||||||||
| 1970 | 0.0645 | 1970 | 15.5 | -4.5749142204 | 15.503875969 | 18.4842883549 | 2.9804123859 | 15.4798992248 | 1970 | 17.2798992248 | 15.5 | 15.5 | 10.9 | Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | ||||||
| 1971 | 0.0541 | 1971 | 18.5 | -7.2483332987 | 14.0449438202 | 18.4842883549 | 4.4393445347 | 16.6557911275 | 1971 | 18.4557911275 | 18.5 | 18.5 | 11.2 | Intercept | 9.363258387 | 2.6421325819 | 3.5438260939 | 0.0011411385 | 3.9994375315 | 14.7270792426 | 3.9994375315 | 14.7270792426 | |||||
| 1972 | 0.055 | 1972 | 18.2 | -8.9139775887 | 8.6281276963 | 18.1818181818 | 9.5536904855 | 16.5364545455 | 1972 | 18.3364545455 | 18.2 | 18.2 | 9.3 | X Variable 1 | 0.3945396148 | 0.1787059823 | 2.2077582952 | 0.0339080034 | 0.0317467401 | 0.7573324894 | 0.0317467401 | 0.7573324894 | |||||
| 1973 | 0.0712 | 1973 | 14.0 | -5.7323918668 | 8.6281276963 | 14.0449438202 | 5.4168161239 | 14.9042921348 | 1973 | 16.7042921348 | 14.0 | 14.0 | 8.3 | ||||||||||||||
| 1974 | 0.1159 | 1974 | 8.6 | -1.1987071911 | 8.6281276963 | 11.2359550562 | 2.6078273599 | 12.7671415013 | 1974 | 14.5671415013 | 8.6 | 8.6 | 7.4 | ||||||||||||||
| 1975 | 0.0915 | 1975 | 10.9 | -3.0300675938 | 9.2678405931 | 11.2359550562 | 1.968114463 | 13.6749125683 | 1975 | 15.4749125683 | 10.9 | 10.9 | 7.9 | ||||||||||||||
| 1976 | 0.089 | 1976 | 11.2 | -2.8747510428 | 8.3125519534 | 11.2359550562 | 2.9234031027 | 13.7960337079 | 1976 | 15.5960337079 | 11.2 | 11.2 | 8.4 | ||||||||||||||
| 1977 | 0.1079 | 1977 | 9.3 | -0.647150938 | 7.4294205052 | 9.2678405931 | 1.8384200879 | 13.0195338276 | 1977 | 14.8195338276 | 9.3 | 9.3 | 8.6 | ||||||||||||||
| 1978 | 0.1203 | 1978 | 8.3 | 4.1407481711 | 7.4294205052 | 8.3125519534 | 0.8831314482 | 12.6426342477 | 1978 | 14.4426342477 | 8.3 | 8.3 | 12.5 | ||||||||||||||
| 1979 | 0.1346 | 1979 | 7.4 | 2.550619415 | 7.4294205052 | 8.3612040134 | 0.9317835082 | 12.2942035661 | 1979 | 14.0942035661 | 7.4 | 7.4 | 10.0 | ||||||||||||||
| 1980 | 0.1266 | 1980 | 7.9 | 4.4163767811 | 7.8988941548 | 8.6206896552 | 0.7217955004 | 7.97 | 12.4794296998 | 1980 | 14.2794296998 | 7.9 | 7.9 | 12.3 | |||||||||||||
| 1981 | 0.1196 | 1981 | 8.4 | 8.0591572346 | 8.3612040134 | 12.4533001245 | 4.0920961112 | 8.54 | 12.6618294314 | 1981 | 14.4618294314 | 8.4 | 8.4 | 16.4 | |||||||||||||
| 1982 | 0.116 | 1982 | 8.6 | 9.6274855273 | 8.6206896552 | 12.4533001245 | 3.8326104694 | 8.6 | 12.7642068966 | 1982 | 14.5642068966 | 8.6 | 8.6 | 18.2 | |||||||||||||
| 1983 | 0.0803 | 1983 | 12.5 | 0.0310943823 | 9.9800399202 | 12.4533001245 | 2.4732602044 | 12.38 | 14.2763250311 | 1983 | 16.0763250311 | 12.5 | 12.5 | 12.5 | |||||||||||||
| 1984 | 0.1002 | 1984 | 10.0 | 3.4970490286 | 9.9800399202 | 16.4203612479 | 6.4403213278 | 10.08 | 13.3005249501 | 1984 | 15.1005249501 | 10.0 | 10.0 | 13.5 | |||||||||||||
| 1985 | 0.0812 | 1985 | 12.3 | 3.140679605 | 12.315270936 | 18.2481751825 | 5.9329042465 | 12 | 14.2218669951 | 1985 | 16.0218669951 | 12.3 | 12.3 | 15.5 | |||||||||||||
| 1986 | 0.0609 | 1986 | 16.4 | 4.4564654744 | 12.4843945069 | 18.2481751825 | 5.7637806756 | 16.13 | 15.8414893268 | 1986 | 17.6414893268 | 16.4 | 16.4 | 20.9 | |||||||||||||
| 1987 | 0.0548 | 1987 | 18.2 | 5.448507282 | 12.4843945069 | 18.2481751825 | 5.7637806756 | 18.66 | 16.5626350365 | 1987 | 18.3626350365 | 18.2 | 18.2 | 23.7 | |||||||||||||
| 1988 | 0.0801 | 1988 | 12.5 | 9.9371301568 | 12.4843945069 | 15.455950541 | 2.9715560341 | 12.65 | 14.2885930087 | 1988 | 16.0885930087 | 12.5 | 12.5 | 22.4 | |||||||||||||
| 1989 | 0.0742 | 1989 | 13.5 | 3.6755697133 | 13.4770889488 | 20.8768267223 | 7.3997377736 | 13.34 | 14.6802506739 | 1989 | 16.4802506739 | 13.5 | 13.5 | 17.2 | |||||||||||||
| 1990 | 0.0647 | 1990 | 15.5 | 0.964410707 | 15.455950541 | 23.6966824645 | 8.2407319235 | 15.35 | 15.47 | 15.4609907264 | 1990 | 17.2609907264 | 15.5 | 15.5 | 16.4 | ||||||||||||
| 1991 | 0.0479 | 1991 | 20.9 | -1.7928572567 | 20.8768267223 | 23.6966824645 | 2.8198557421 | 25.93 | 20.05 | 17.599743215 | 1991 | 19.399743215 | 20.9 | 20.9 | 19.1 | ||||||||||||
| 1992 | 0.0422 | 1992 | 23.7 | -1.8148443901 | 17.1526586621 | 23.6966824645 | 6.5440238024 | 22.5 | 24.05 | 18.7122890995 | 1992 | 20.5122890995 | 23.7 | ||||||||||||||
| 1993 | 0.0446 | 1993 | 22.4 | 6.4802094404 | 16.4203612479 | 22.4215246637 | 6.0011634157 | 21.34 | 22.44 | 18.2091883408 | 1993 | 20.0091883408 | 22.4 | ||||||||||||||
| 1994 | 0.0583 | 1994 | 17.2 | 14.3930826628 | 16.4203612479 | 19.0839694656 | 2.6636082177 | 14.89 | 18.09 | 16.1304099485 | 1994 | 17.9304099485 | 17.2 | ||||||||||||||
| 1995 | 0.0609 | 1995 | 16.4 | 11.1278481184 | 16.4203612479 | 21.8818380744 | 5.4614768265 | 18.08 | 16.03 | 15.8414893268 | 1995 | 17.6414893268 | 16.4 | ||||||||||||||
| 1996 | 0.0524 | 1996 | 19.1 | 14.8143356191 | 19.0839694656 | 28.901734104 | 9.8177646384 | 19.53 | 18.88 | 16.892389313 | 1996 | 18.692389313 | 19.1 | ||||||||||||||
| 1997 | 0.0457 | 1997 | 21.9 | 12.3647372681 | 21.8818380744 | 31.5457413249 | 9.6639032505 | 24.29 | 21.7 | 17.9962603939 | 1997 | 19.7962603939 | 21.9 | ||||||||||||||
| 1998 | 0.0346 | 1998 | 28.9 | -2.8600674374 | 27.5482093664 | 31.5457413249 | 3.9975319585 | 32.92 | 28.01 | 20.7658901734 | 22.5658901734 | 28.9 | |||||||||||||||
| 1999 | 0.0317 | 1999 | 31.5 | -11.0958435744 | 27.5482093664 | 33.8983050847 | 6.3500957184 | 29.04 | 31.69 | 21.8090567823 | 23.6090567823 | 31.5 | |||||||||||||||
| 2000 | 0.0363 | 2000 | 27.5 | -8.8914929485 | 27.5482093664 | 34.2465753425 | 6.6983659761 | 27.55 | 27.72 | 20.2318705234 | 22.0318705234 | 27.5 | |||||||||||||||
| 2001 | 0.0295 | 2001 | 33.9 | -16.5972670225 | 26.0416666667 | 34.2465753425 | 8.2049086758 | 46.17 | 34.59 | 22.7372372881 | |||||||||||||||||
| 2002 | 0.0292 | 2002 | 34.2 | -15.34298366 | 20.4498977505 | 34.2465753425 | 13.796677592 | 31.43 | 37.28 | 22.8746438356 | |||||||||||||||||
| 2003 | 0.0384 | 2003 | 26.0 | 2.206920904 | 18.6567164179 | 26.0416666667 | 7.3849502488 | 22.73 | 26.95 | 19.6374791667 | |||||||||||||||||
| 2004 | 0.0489 | 2004 | 20.4 | 17.3010380623 | 20.4498977505 | 3.1488596882 | 19.99 | 20.5 | |||||||||||||||||||
| 2005 | 0.0536 | 2005 | 18.7 | 17.3010380623 | 18.9035916824 | 1.6025536201 | 18.07 | 18.9 | |||||||||||||||||||
| 2006 | 0.0578 | 2006 | 17.3 | 17.3010380623 | 28.2485875706 | 10.9475495083 | 17.36 | 17.29 | |||||||||||||||||||
| 2007 | 0.0529 | 2007 | 18.9 | 18.69 | 21.46 | 18.69 | |||||||||||||||||||||
| 2008 | 0.0354 | 2008 | 28.2 | 28.4 | Shiller | 70.91 | 28.39 | ||||||||||||||||||||
| 2009 | 80.6 | 20.7 | 83.6 | ||||||||||||||||||||||||
| 2010 | 17.13 | 17.13 | |||||||||||||||||||||||||
| 2011 | 15.14 | 15.14 | |||||||||||||||||||||||||
| 2012 | 15.79 | 15.79 | |||||||||||||||||||||||||
| 2013 | 17.81 | 17.81 | |||||||||||||||||||||||||
| 2014 | 18.76 | 18.76 | |||||||||||||||||||||||||
| 2015 | 21.94 | 21.94 | |||||||||||||||||||||||||
| 2016 | 23.61 | 23.61 | |||||||||||||||||||||||||
| 2017 | 23.55 | 23.56 | |||||||||||||||||||||||||
| AVG. = | 17.4 | (1956-2008) | 4.5742386126 | ||||||||||||||||||||||||
| AVG. = | 15.5 | (1956-1998) | |||||||||||||||||||||||||
| AVG. = | 25.7 | (1999-2008) | |||||||||||||||||||||||||
| Weighted average = | 22.3 | ||||||||||||||||||||||||||
| SUMMARY OUTPUT | |||||||||||||||||||||||||||
| 2020 | |||||||||||||||||||||||||||
| 24.9659161098 | Regression Statistics | ||||||||||||||||||||||||||
| Multiple R | 0.4748383082 | ||||||||||||||||||||||||||
| R Square | 0.225471419 | ||||||||||||||||||||||||||
| Adjusted R Square | 0.2102845841 | ||||||||||||||||||||||||||
| Standard Error | 5.7639681102 | ||||||||||||||||||||||||||
| Observations | 53 | ||||||||||||||||||||||||||
| ANOVA | |||||||||||||||||||||||||||
| df | SS | MS | F | Significance F | |||||||||||||||||||||||
| Regression | 1 | 493.2503072848 | 493.2503072848 | 14.8465048928 | 0.0003275067 | ||||||||||||||||||||||
| Residual | 51 | 1694.3897471567 | 33.2233283756 | ||||||||||||||||||||||||
| Total | 52 | 2187.6400544415 | |||||||||||||||||||||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | ||||||||||||||||||||
| Intercept | -377.8802067428 | 102.5869907395 | -3.6835100047 | 0.0005566269 | -583.8321800738 | -171.9282334119 | -583.8321800738 | -171.9282334119 | |||||||||||||||||||
| X Variable 1 | 0.1994287737 | 0.0517577878 | 3.8531162574 | 0.0003275067 | 0.0955206811 | 0.3033368663 | 0.0955206811 | 0.3033368663 |
Sheet3
| 12/1/79 | 7.25 | 1980 | 7.97 | ||
| 1/1/80 | 7.39 | 1981 | 8.54 | ||
| 2/1/80 | 7.61 | 1982 | 8.6 | ||
| 3/1/80 | 6.85 | 1983 | 12.38 | ||
| 4/1/80 | 6.79 | 1984 | 10.08 | ||
| 5/1/80 | 7.15 | 1985 | 12 | ||
| 6/1/80 | 7.67 | 1986 | 16.13 | ||
| 7/1/80 | 8.07 | 1987 | 18.66 | ||
| 8/1/80 | 8.38 | 1988 | 12.65 | ||
| 9/1/80 | 8.64 | 1989 | 13.34 | ||
| 10/1/80 | 8.86 | 1990 | 15.47 | ||
| 11/1/80 | 9.19 | 1991 | 20.05 | ||
| 12/1/80 | 9.01 | 7.97 | 1992 | 24.05 | |
| 1/1/81 | 9.02 | 1993 | 22.44 | ||
| 2/1/81 | 8.76 | 1994 | 18.09 | ||
| 3/1/81 | 9.14 | 1995 | 16.03 | ||
| 4/1/81 | 9.13 | 1996 | 18.88 | ||
| 5/1/81 | 8.86 | 1997 | 21.7 | ||
| 6/1/81 | 8.81 | 1998 | 28.01 | ||
| 7/1/81 | 8.55 | 1999 | 31.69 | ||
| 8/1/81 | 8.54 | 2000 | 27.72 | ||
| 9/1/81 | 7.75 | 2001 | 34.59 | ||
| 10/1/81 | 7.83 | 2002 | 37.28 | ||
| 11/1/81 | 8.02 | 2003 | 26.95 | ||
| 12/1/81 | 8.06 | 8.54 | 2004 | 20.5 | |
| 1/1/82 | 7.73 | 2005 | 18.9 | ||
| 2/1/82 | 7.64 | 2006 | 17.29 | ||
| 3/1/82 | 7.48 | 2007 | 18.69 | ||
| 4/1/82 | 7.97 | 2008 | 28.39 | ||
| 5/1/82 | 8.09 | 2009 | 83.6 | ||
| 6/1/82 | 7.74 | 2010 | 17.13 | ||
| 7/1/82 | 7.83 | 2011 | 15.14 | ||
| 8/1/82 | 7.97 | 2012 | 15.79 | ||
| 9/1/82 | 9.03 | 2013 | 17.81 | ||
| 10/1/82 | 10.02 | 2014 | 18.76 | ||
| 11/1/82 | 10.66 | 2015 | 21.94 | ||
| 12/1/82 | 11.03 | 8.6 | 2016 | 23.61 | |
| 1/1/83 | 11.48 | 2017 | 23.56 | ||
| 2/1/83 | 11.75 | ||||
| 3/1/83 | 12.23 | ||||
| 4/1/83 | 12.64 | ||||
| 5/1/83 | 13.1 | ||||
| 6/1/83 | 13.22 | ||||
| 7/1/83 | 13.02 | ||||
| 8/1/83 | 12.43 | ||||
| 9/1/83 | 12.57 | ||||
| 10/1/83 | 12.39 | ||||
| 11/1/83 | 11.98 | ||||
| 12/1/83 | 11.72 | 12.38 | |||
| 1/1/84 | 11.52 | ||||
| 2/1/84 | 10.59 | ||||
| 3/1/84 | 10.31 | ||||
| 4/1/84 | 10.12 | ||||
| 5/1/84 | 9.86 | ||||
| 6/1/84 | 9.45 | ||||
| 7/1/84 | 9.26 | ||||
| 8/1/84 | 10 | ||||
| 9/1/84 | 10.03 | ||||
| 10/1/84 | 9.93 | ||||
| 11/1/84 | 10.01 | ||||
| 12/1/84 | 9.89 | 10.08 | |||
| 1/1/85 | 10.36 | ||||
| 2/1/85 | 10.98 | ||||
| 3/1/85 | 10.95 | ||||
| 4/1/85 | 11.2 | ||||
| 5/1/85 | 11.65 | ||||
| 6/1/85 | 12.1 | ||||
| 7/1/85 | 12.44 | ||||
| 8/1/85 | 12.26 | ||||
| 9/1/85 | 12.09 | ||||
| 10/1/85 | 12.4 | ||||
| 11/1/85 | 13.33 | ||||
| 12/1/85 | 14.19 | 12 | |||
| 1/1/86 | 14.28 | ||||
| 2/1/86 | 15.08 | ||||
| 3/1/86 | 16 | ||||
| 4/1/86 | 16.32 | ||||
| 5/1/86 | 16.28 | ||||
| 6/1/86 | 16.68 | ||||
| 7/1/86 | 16.27 | ||||
| 8/1/86 | 16.55 | ||||
| 9/1/86 | 16.05 | ||||
| 10/1/86 | 16.12 | ||||
| 11/1/86 | 16.79 | ||||
| 12/1/86 | 17.17 | 16.13 | |||
| 1/1/87 | 18.01 | ||||
| 2/1/87 | 18.87 | ||||
| 3/1/87 | 19.37 | ||||
| 4/1/87 | 19.46 | ||||
| 5/1/87 | 19.73 | ||||
| 6/1/87 | 20.9 | ||||
| 7/1/87 | 20.81 | ||||
| 8/1/87 | 21.42 | ||||
| 9/1/87 | 20.09 | ||||
| 10/1/87 | 17.07 | ||||
| 11/1/87 | 14.45 | ||||
| 12/1/87 | 13.77 | 18.66 | |||
| 1/1/88 | 14.03 | ||||
| 2/1/88 | 14.16 | ||||
| 3/1/88 | 14.29 | ||||
| 4/1/88 | 13.38 | ||||
| 5/1/88 | 12.41 | ||||
| 6/1/88 | 12.49 | ||||
| 7/1/88 | 12.22 | ||||
| 8/1/88 | 11.78 | ||||
| 9/1/88 | 11.79 | ||||
| 10/1/88 | 12.02 | ||||
| 11/1/88 | 11.57 | ||||
| 12/1/88 | 11.64 | 12.65 | |||
| 1/1/89 | 11.82 | ||||
| 2/1/89 | 11.97 | ||||
| 3/1/89 | 11.73 | ||||
| 4/1/89 | 12.07 | ||||
| 5/1/89 | 12.49 | ||||
| 6/1/89 | 12.84 | ||||
| 7/1/89 | 13.43 | ||||
| 8/1/89 | 14.32 | ||||
| 9/1/89 | 14.66 | ||||
| 10/1/89 | 14.84 | ||||
| 11/1/89 | 14.7 | ||||
| 12/1/89 | 15.24 | 13.34 | |||
| 1/1/90 | 15.13 | ||||
| 2/1/90 | 14.97 | ||||
| 3/1/90 | 15.62 | ||||
| 4/1/90 | 15.7 | ||||
| 5/1/90 | 16.37 | ||||
| 6/1/90 | 16.95 | ||||
| 7/1/90 | 16.81 | ||||
| 8/1/90 | 15.33 | ||||
| 9/1/90 | 14.51 | ||||
| 10/1/90 | 14.21 | ||||
| 11/1/90 | 14.68 | ||||
| 12/1/90 | 15.41 | 15.47 | |||
| 1/1/91 | 15.35 | ||||
| 2/1/91 | 17.19 | ||||
| 3/1/91 | 17.78 | ||||
| 4/1/91 | 18.58 | ||||
| 5/1/91 | 18.98 | ||||
| 6/1/91 | 19.49 | ||||
| 7/1/91 | 20.14 | ||||
| 8/1/91 | 21.22 | ||||
| 9/1/91 | 21.73 | ||||
| 10/1/91 | 22.49 | ||||
| 11/1/91 | 23.27 | ||||
| 12/1/91 | 24.33 | 20.05 | |||
| 1/1/92 | 25.93 | ||||
| 2/1/92 | 25.6 | ||||
| 3/1/92 | 25.16 | ||||
| 4/1/92 | 24.73 | ||||
| 5/1/92 | 24.75 | ||||
| 6/1/92 | 23.95 | ||||
| 7/1/92 | 23.88 | ||||
| 8/1/92 | 23.6 | ||||
| 9/1/92 | 23.2 | ||||
| 10/1/92 | 22.43 | ||||
| 11/1/92 | 22.56 | ||||
| 12/1/92 | 22.82 | 24.05 | |||
| 1/1/93 | 22.5 | ||||
| 2/1/93 | 22.55 | ||||
| 3/1/93 | 22.69 | ||||
| 4/1/93 | 22.53 | ||||
| 5/1/93 | 22.83 | ||||
| 6/1/93 | 23.18 | ||||
| 7/1/93 | 22.72 | ||||
| 8/1/93 | 22.65 | ||||
| 9/1/93 | 22.5 | ||||
| 10/1/93 | 22.19 | ||||
| 11/1/93 | 21.63 | ||||
| 12/1/93 | 21.29 | 22.44 | |||
| 1/1/94 | 21.34 | ||||
| 2/1/94 | 21.02 | ||||
| 3/1/94 | 20.42 | ||||
| 4/1/94 | 19 | ||||
| 5/1/94 | 18.5 | ||||
| 6/1/94 | 18.05 | ||||
| 7/1/94 | 17.42 | ||||
| 8/1/94 | 17.44 | ||||
| 9/1/94 | 17.09 | ||||
| 10/1/94 | 16.32 | ||||
| 11/1/94 | 15.62 | ||||
| 12/1/94 | 14.88 | 18.09 | |||
| 1/1/95 | 14.89 | ||||
| 2/1/95 | 15.11 | ||||
| 3/1/95 | 15.15 | ||||
| 4/1/95 | 15.31 | ||||
| 5/1/95 | 15.5 | ||||
| 6/1/95 | 15.67 | ||||
| 7/1/95 | 16.07 | ||||
| 8/1/95 | 16.01 | ||||
| 9/1/95 | 16.45 | ||||
| 10/1/95 | 16.76 | ||||
| 11/1/95 | 17.33 | ||||
| 12/1/95 | 18.1 | 16.03 | |||
| 1/1/96 | 18.08 | ||||
| 2/1/96 | 19.1 | ||||
| 3/1/96 | 19.01 | ||||
| 4/1/96 | 18.85 | ||||
| 5/1/96 | 19.1 | ||||
| 6/1/96 | 19.15 | ||||
| 7/1/96 | 18.26 | ||||
| 8/1/96 | 18.6 | ||||
| 9/1/96 | 18.75 | ||||
| 10/1/96 | 19 | ||||
| 11/1/96 | 19.45 | ||||
| 12/1/96 | 19.19 | 18.88 | |||
| 1/1/97 | 19.53 | ||||
| 2/1/97 | 20.09 | ||||
| 3/1/97 | 19.69 | ||||
| 4/1/97 | 18.94 | ||||
| 5/1/97 | 20.6 | ||||
| 6/1/97 | 21.61 | ||||
| 7/1/97 | 22.8 | ||||
| 8/1/97 | 22.83 | ||||
| 9/1/97 | 23.06 | ||||
| 10/1/97 | 23.58 | ||||
| 11/1/97 | 23.46 | ||||
| 12/1/97 | 24.23 | 21.7 | |||
| 1/1/98 | 24.29 | ||||
| 2/1/98 | 25.85 | ||||
| 3/1/98 | 27.23 | ||||
| 4/1/98 | 28.26 | ||||
| 5/1/98 | 28.3 | ||||
| 6/1/98 | 28.44 | ||||
| 7/1/98 | 29.9 | ||||
| 8/1/98 | 28 | ||||
| 9/1/98 | 26.8 | ||||
| 10/1/98 | 27.2 | ||||
| 11/1/98 | 30.25 | ||||
| 12/1/98 | 31.56 | 28.01 | |||
| 1/1/99 | 32.92 | ||||
| 2/1/99 | 32.67 | ||||
| 3/1/99 | 33.39 | ||||
| 4/1/99 | 34 | ||||
| 5/1/99 | 33.19 | ||||
| 6/1/99 | 32.24 | ||||
| 7/1/99 | 32.88 | ||||
| 8/1/99 | 30.89 | ||||
| 9/1/99 | 29.99 | ||||
| 10/1/99 | 28.66 | ||||
| 11/1/99 | 29.74 | ||||
| 12/1/99 | 29.66 | 31.69 | |||
| 1/1/00 | 29.04 | ||||
| 2/1/00 | 27.76 | ||||
| 3/1/00 | 28.31 | ||||
| 4/1/00 | 28.5 | ||||
| 5/1/00 | 27.49 | ||||
| 6/1/00 | 28.16 | ||||
| 7/1/00 | 28.05 | ||||
| 8/1/00 | 27.97 | ||||
| 9/1/00 | 27.34 | ||||
| 10/1/00 | 26.5 | ||||
| 11/1/00 | 26.9 | ||||
| 12/1/00 | 26.62 | 27.72 | |||
| 1/1/01 | 27.55 | ||||
| 2/1/01 | 27.81 | ||||
| 3/1/01 | 26.1 | ||||
| 4/1/01 | 27.96 | ||||
| 5/1/01 | 32.02 | ||||
| 6/1/01 | 33.67 | ||||
| 7/1/01 | 35.46 | ||||
| 8/1/01 | 37.85 | ||||
| 9/1/01 | 36.9 | ||||
| 10/1/01 | 39.72 | ||||
| 11/1/01 | 43.62 | ||||
| 12/1/01 | 46.37 | 34.59 | |||
| 1/1/02 | 46.17 | ||||
| 2/1/02 | 44.57 | ||||
| 3/1/02 | 46.71 | ||||
| 4/1/02 | 43.81 | ||||
| 5/1/02 | 41.41 | ||||
| 6/1/02 | 37.92 | ||||
| 7/1/02 | 32.46 | ||||
| 8/1/02 | 31.53 | ||||
| 9/1/02 | 28.89 | ||||
| 10/1/02 | 29.24 | ||||
| 11/1/02 | 32.03 | ||||
| 12/1/02 | 32.59 | 37.28 | |||
| 1/1/03 | 31.43 | ||||
| 2/1/03 | 28.46 | ||||
| 3/1/03 | 27.92 | ||||
| 4/1/03 | 28.05 | ||||
| 5/1/03 | 28.24 | ||||
| 6/1/03 | 28.6 | ||||
| 7/1/03 | 27.65 | ||||
| 8/1/03 | 26.57 | ||||
| 9/1/03 | 26.42 | ||||
| 10/1/03 | 24.75 | ||||
| 11/1/03 | 23.15 | ||||
| 12/1/03 | 22.17 | 26.95 | |||
| 1/1/04 | 22.73 | ||||
| 2/1/04 | 22.46 | ||||
| 3/1/04 | 21.62 | ||||
| 4/1/04 | 21.23 | ||||
| 5/1/04 | 20.14 | ||||
| 6/1/04 | 20.17 | ||||
| 7/1/04 | 19.51 | ||||
| 8/1/04 | 19.03 | ||||
| 9/1/04 | 19.35 | ||||
| 10/1/04 | 19.25 | ||||
| 11/1/04 | 20.05 | ||||
| 12/1/04 | 20.48 | 20.5 | |||
| 1/1/05 | 19.99 | ||||
| 2/1/05 | 20.11 | ||||
| 3/1/05 | 19.84 | ||||
| 4/1/05 | 19.02 | ||||
| 5/1/05 | 18.93 | ||||
| 6/1/05 | 19 | ||||
| 7/1/05 | 19 | ||||
| 8/1/05 | 18.72 | ||||
| 9/1/05 | 18.44 | ||||
| 10/1/05 | 17.64 | ||||
| 11/1/05 | 18.01 | ||||
| 12/1/05 | 18.07 | 18.9 | |||
| 1/1/06 | 18.07 | ||||
| 2/1/06 | 17.8 | ||||
| 3/1/06 | 17.8 | ||||
| 4/1/06 | 17.77 | ||||
| 5/1/06 | 17.46 | ||||
| 6/1/06 | 16.82 | ||||
| 7/1/06 | 16.61 | ||||
| 8/1/06 | 16.67 | ||||
| 9/1/06 | 16.77 | ||||
| 10/1/06 | 17.14 | ||||
| 11/1/06 | 17.24 | ||||
| 12/1/06 | 17.38 | 17.29 | |||
| 1/1/07 | 17.36 | ||||
| 2/1/07 | 17.49 | ||||
| 3/1/07 | 16.92 | ||||
| 4/1/07 | 17.48 | ||||
| 5/1/07 | 17.92 | ||||
| 6/1/07 | 17.83 | ||||
| 7/1/07 | 18.36 | ||||
| 8/1/07 | 18.02 | ||||
| 9/1/07 | 19.05 | ||||
| 10/1/07 | 20.68 | ||||
| 11/1/07 | 20.81 | ||||
| 12/1/07 | 22.35 | 18.69 | |||
| 1/1/08 | 21.46 | ||||
| 2/1/08 | 21.74 | ||||
| 3/1/08 | 21.81 | ||||
| 4/1/08 | 23.88 | ||||
| 5/1/08 | 25.81 | ||||
| 6/1/08 | 26.11 | ||||
| 7/1/08 | 25.37 | ||||
| 8/1/08 | 26.83 | ||||
| 9/1/08 | 26.48 | ||||
| 10/1/08 | 27.22 | ||||
| 11/1/08 | 34.99 | ||||
| 12/1/08 | 58.98 | 28.39 | |||
| 1/1/09 | 70.91 | ||||
| 2/1/09 | 84.46 | ||||
| 3/1/09 | 110.37 | ||||
| 4/1/09 | 119.85 | ||||
| 5/1/09 | 123.73 | ||||
| 6/1/09 | 123.32 | ||||
| 7/1/09 | 101.87 | ||||
| 8/1/09 | 92.95 | ||||
| 9/1/09 | 83.3 | ||||
| 10/1/09 | 42.12 | ||||
| 11/1/09 | 28.51 | ||||
| 12/1/09 | 21.78 | 83.6 | |||
| 1/1/10 | 20.7 | ||||
| 2/1/10 | 18.91 | ||||
| 3/1/10 | 18.91 | ||||
| 4/1/10 | 19.01 | ||||
| 5/1/10 | 17.3 | ||||
| 6/1/10 | 16.15 | ||||
| 7/1/10 | 15.72 | ||||
| 8/1/10 | 15.47 | ||||
| 9/1/10 | 15.61 | ||||
| 10/1/10 | 15.9 | ||||
| 11/1/10 | 15.88 | ||||
| 12/1/10 | 16.05 | 17.13 | |||
| 1/1/11 | 16.3 | ||||
| 2/1/11 | 16.52 | ||||
| 3/1/11 | 16.04 | ||||
| 4/1/11 | 16.21 | ||||
| 5/1/11 | 16.12 | ||||
| 6/1/11 | 15.35 | ||||
| 7/1/11 | 15.61 | ||||
| 8/1/11 | 13.79 | ||||
| 9/1/11 | 13.5 | ||||
| 10/1/11 | 13.88 | ||||
| 11/1/11 | 14.1 | ||||
| 12/1/11 | 14.3 | 15.14 | |||
| 1/1/12 | 14.87 | ||||
| 2/1/12 | 15.37 | ||||
| 3/1/12 | 15.69 | ||||
| 4/1/12 | 15.7 | ||||
| 5/1/12 | 15.22 | ||||
| 6/1/12 | 15.05 | ||||
| 7/1/12 | 15.55 | ||||
| 8/1/12 | 16.14 | ||||
| 9/1/12 | 16.69 | ||||
| 10/1/12 | 16.62 | ||||
| 11/1/12 | 16.12 | ||||
| 12/1/12 | 16.44 | 15.79 | |||
| 1/1/13 | 17.03 | ||||
| 2/1/13 | 17.32 | ||||
| 3/1/13 | 17.68 | ||||
| 4/1/13 | 17.69 | ||||
| 5/1/13 | 18.25 | ||||
| 6/1/13 | 17.8 | ||||
| 7/1/13 | 18.12 | ||||
| 8/1/13 | 17.91 | ||||
| 9/1/13 | 17.88 | ||||
| 10/1/13 | 17.86 | ||||
| 11/1/13 | 18.15 | ||||
| 12/1/13 | 18.04 | 17.81 | |||
| 1/1/14 | 18.15 | ||||
| 2/1/14 | 18.06 | ||||
| 3/1/14 | 18.48 | ||||
| 4/1/14 | 18.35 | ||||
| 5/1/14 | 18.46 | ||||
| 6/1/14 | 18.88 | ||||
| 7/1/14 | 18.96 | ||||
| 8/1/14 | 18.68 | ||||
| 9/1/14 | 18.81 | ||||
| 10/1/14 | 18.5 | ||||
| 11/1/14 | 19.75 | ||||
| 12/1/14 | 20.08 | 18.76 | |||
| 1/1/15 | 20.02 | ||||
| 2/1/15 | 20.77 | ||||
| 3/1/15 | 20.96 | ||||
| 4/1/15 | 21.42 | ||||
| 5/1/15 | 21.92 | ||||
| 6/1/15 | 22.12 | ||||
| 7/1/15 | 22.4 | ||||
| 8/1/15 | 22.15 | ||||
| 9/1/15 | 21.45 | ||||
| 10/1/15 | 22.68 | ||||
| 11/1/15 | 23.67 | ||||
| 12/1/15 | 23.74 | 21.94 | |||
| 1/1/16 | 22.18 | ||||
| 2/1/16 | 22.02 | ||||
| 3/1/16 | 23.39 | ||||
| 4/1/16 | 23.97 | ||||
| 5/1/16 | 23.81 | ||||
| 6/1/16 | 23.97 | ||||
| 7/1/16 | 24.52 | ||||
| 8/1/16 | 24.57 | ||||
| 9/1/16 | 24.22 | ||||
| 10/1/16 | 23.57 | ||||
| 11/1/16 | 23.35 | ||||
| 12/1/16 | 23.76 | 23.61 | |||
| 1/1/17 | 23.59 | ||||
| 2/1/17 | 23.68 | ||||
| 3/1/17 | 23.6 | ||||
| 4/1/17 | 23.23 | ||||
| 5/1/17 | 23.24 | ||||
| 6/1/17 | 23.36 | ||||
| 7/1/17 | 23.15 | ||||
| 8/1/17 | 23.36 | ||||
| 9/1/17 | 23.11 | ||||
| 10/1/17 | 23.55 | ||||
| 11/1/17 | 24.09 | ||||
| 12/1/17 | 24.7 | 23.56 |
Figure 11.2
| 1980 |
| 1981 |
| 1982 |
| 1983 |
| 1984 |
| 1985 |
| 1986 |
| 1987 |
| 1988 |
| 1989 |
| 1990 |
| 1991 |
| 1992 |
| 1993 |
| 1994 |
| 1995 |
| 1996 |
| 1997 |
| 1998 |
| 1999 |
| 2000 |
| 2001 |
| 2002 |
| 2003 |
| 2004 |
| 2005 |
| 2006 |
| 2007 |
| 2008 |
| 2009 |
| 2010 |
| 2011 |
| 2012 |
| 2013 |
| 2014 |
| 2015 |
| 2016 |
| 2017 |
Sheet2
| 1-Jan-04 | 22.73 | 1899 | 12.71 | ||
| 1-Feb-04 | 22.46 | 1900 | 14.73 | ||
| 1-Mar-04 | 21.62 | 1901 | 15.92 | ||
| 1-Apr-04 | 21.23 | 1902 | 13.65 | ||
| 1-May-04 | 20.14 | 1903 | 12.6 | ||
| 1-Jun-04 | 20.17 | 1904 | 16.53 | ||
| 1-Jul-04 | 19.51 | 1905 | 14.51 | ||
| 1-Aug-04 | 19.03 | 1906 | 12.75 | ||
| 1-Sep-04 | 19.35 | 1907 | 10.54 | ||
| 1-Oct-04 | 19.25 | 1908 | 15.1 | ||
| 1-Nov-04 | 20.05 | 1909 | 13.26 | ||
| 1-Dec-04 | 20.48 | 1910 | 12.88 | ||
| 1-Jan-05 | 19.99 | 1911 | 15.2 | ||
| 1-Feb-05 | 20.11 | 1912 | 13.48 | ||
| 1-Mar-05 | 19.84 | 1913 | 13.5 | ||
| 1-Apr-05 | 19.02 | 1914 | 13.6 | ||
| 1-May-05 | 18.93 | 1915 | 10.03 | ||
| 1-Jun-05 | 19 | 1916 | 6.34 | ||
| 1-Jul-05 | 19 | 1917 | 5.72 | ||
| 1-Aug-05 | 18.72 | 1918 | 7.93 | ||
| 1-Sep-05 | 18.44 | 1919 | 9.6 | ||
| 1-Oct-05 | 17.64 | 1920 | 9.36 | ||
| 1-Nov-05 | 18.01 | 1921 | 22.81 | ||
| 1-Dec-05 | 18.07 | 1922 | 12.54 | ||
| 1-Jan-06 | 18.07 | 1923 | 9.01 | ||
| 1-Feb-06 | 17.8 | 1924 | 11.02 | ||
| 1-Mar-06 | 17.8 | 1925 | 10.12 | ||
| 1-Apr-06 | 17.77 | 1926 | 10.89 | ||
| 1-May-06 | 17.46 | 1927 | 15.51 | ||
| 1-Jun-06 | 16.82 | 1928 | 17.76 | ||
| 1-Jul-06 | 16.61 | 1929 | 13.92 | ||
| 1-Aug-06 | 16.67 | 1930 | 17 | ||
| 1-Sep-06 | 16.77 | 1931 | 14.07 | ||
| 1-Oct-06 | 17.14 | 1932 | 17.29 | ||
| 1-Nov-06 | 17.24 | 1933 | 23.95 | ||
| 1-Dec-06 | 17.38 | 1934 | 16.25 | ||
| 1-Jan-07 | 17.36 | 1935 | 17.87 | ||
| 1-Feb-07 | 17.49 | 1936 | 16.75 | ||
| 1-Mar-07 | 16.92 | 1937 | 10.47 | ||
| 1-Apr-07 | 17.48 | 1938 | 18.94 | ||
| 1-May-07 | 17.92 | 1939 | 13.23 | ||
| 1-Jun-07 | 17.83 | 1940 | 10.05 | ||
| 1-Jul-07 | 18.36 | 1941 | 7.97 | ||
| 1-Aug-07 | 18.02 | 1942 | 9.7 | ||
| 1-Sep-07 | 19.05 | 1943 | 12.61 | ||
| 1-Oct-07 | 20.68 | 1944 | 14.35 | ||
| 1-Nov-07 | 20.81 | 1945 | 19.17 | ||
| 1-Dec-07 | 22.35 | 1946 | 13.46 | ||
| 1-Jan-08 | 21.46 | 1947 | 9.04 | ||
| 1-Feb-08 | 21.74 | 1948 | 6.62 | ||
| 1-Mar-08 | 21.81 | 1949 | 7.21 | ||
| 1-Apr-08 | 23.88 | 1950 | 7.47 | ||
| 1-May-08 | 25.81 | 1951 | 9.95 | ||
| 1-Jun-08 | 26.11 | 1952 | 10.86 | ||
| 1-Jul-08 | 25.37 | 1953 | 10.1 | ||
| 1-Aug-08 | 26.83 | 1954 | 12.58 | ||
| 1-Sep-08 | 26.48 | 1955 | 12.13 | ||
| 1-Oct-08 | 27.22 | 1956 | 13.32 | ||
| 1-Nov-08 | 34.99 | 1957 | 12.5 | ||
| 1-Dec-08 | 58.98 | 1958 | 18.79 | ||
| 1-Jan-09 | 70.91 | 1959 | 17.12 | ||
| 1-Feb-09 | 84.46 | 1960 | 18.6 | ||
| 1-Mar-09 | 110.37 | 1961 | 21.25 | ||
| 1-Apr-09 | 119.85 | 1962 | 17.68 | ||
| 1-May-09 | 123.73 | 1963 | 18.78 | ||
| 1-Jun-09 | 123.32 | 1964 | 18.76 | ||
| 1-Jul-09 | 101.87 | 1965 | 17.81 | ||
| 1-Aug-09 | 92.95 | 1966 | 15.3 | ||
| 1-Sep-09 | 83.3 | 1967 | 17.7 | ||
| 1-Oct-09 | 42.12 | 1968 | 17.65 | ||
| 1-Nov-09 | 28.51 | 1969 | 15.76 | ||
| 1-Dec-09 | 21.78 | 1970 | 18.12 | ||
| 1-Jan-10 | 20.7 | 1971 | 18 | ||
| 1-Feb-10 | 18.91 | 1972 | 18.08 | ||
| 1-Mar-10 | 18.91 | 1973 | 11.68 | ||
| 1-Apr-10 | 19.01 | 1974 | 8.3 | ||
| 1-May-10 | 17.3 | 1975 | 11.83 | ||
| 1-Jun-10 | 16.15 | 1976 | 10.41 | ||
| 1-Jul-10 | 15.72 | 1977 | 8.28 | ||
| 1-Aug-10 | 15.47 | 1978 | 7.88 | ||
| 1-Sep-10 | 15.61 | 1979 | 7.39 | ||
| 1-Oct-10 | 15.9 | 1980 | 7.97 | 7.97 | |
| 1-Nov-10 | 15.88 | 1981 | 8.54 | 8.54 | |
| 1-Dec-10 | 16.05 | 1982 | 8.6 | 8.6 | |
| 1-Jan-11 | 16.3 | 1983 | 12.38 | 12.38 | |
| 1-Feb-11 | 16.52 | 1984 | 10.08 | 10.08 | |
| 1-Mar-11 | 16.04 | 1985 | 12 | 12 | |
| 1-Apr-11 | 16.21 | 1986 | 16.13 | 16.13 | |
| 1-May-11 | 16.12 | 1987 | 18.66 | 18.66 | |
| 1-Jun-11 | 15.35 | 1988 | 12.65 | 12.65 | |
| 1-Jul-11 | 15.61 | 1989 | 13.34 | 13.34 | |
| 1-Aug-11 | 13.79 | 1990 | 15.47 | 15.47 | |
| 1-Sep-11 | 13.5 | 1991 | 20.05 | 20.05 | |
| 1-Oct-11 | 13.88 | 1992 | 24.05 | 24.05 | |
| 1-Nov-11 | 14.1 | 1993 | 22.44 | 22.44 | |
| 1-Dec-11 | 14.3 | 1994 | 18.09 | 18.09 | |
| 1-Jan-12 | 14.87 | 1995 | 16.03 | 16.03 | |
| 1-Feb-12 | 15.37 | 1996 | 18.88 | 18.88 | |
| 1-Mar-12 | 15.69 | 1997 | 21.7 | 21.7 | |
| 1-Apr-12 | 15.7 | 1998 | 28.01 | 28.01 | |
| 1-May-12 | 15.22 | 1999 | 31.69 | 31.69 | |
| 1-Jun-12 | 15.05 | 2000 | 27.72 | 27.72 | |
| 1-Jul-12 | 15.55 | 2001 | 34.59 | 34.59 | |
| 1-Aug-12 | 16.14 | 2002 | 37.28 | 37.28 | |
| 1-Sep-12 | 16.69 | 2003 | 26.95 | 26.95 | |
| 1-Oct-12 | 16.62 | 2004 | 20.5 | 20.5 | |
| 1-Nov-12 | 16.12 | 2005 | 18.9 | 18.9 | |
| 1-Dec-12 | 16.44 | 2006 | 17.29 | 17.29 | |
| 1-Jan-13 | 17.03 | 2007 | 18.69 | 18.69 | |
| 1-Feb-13 | 17.32 | 2008 | 28.39 | 28.39 | |
| 1-Mar-13 | 17.68 | 2009 | 83.6 | 83.6 | |
| 1-Apr-13 | 17.69 | 2010 | 17.13 | 17.13 | |
| 1-May-13 | 18.25 | 2011 | 15.14 | 15.14 | |
| 1-Jun-13 | 17.8 | 2012 | 15.79 | 15.79 | |
| 1-Jul-13 | 18.12 | 2013 | 17.81 | 17.81 | |
| 1-Aug-13 | 17.91 | 2014 | 18.76 | 18.76 | |
| 1-Sep-13 | 17.88 | 2015 | 21.94 | 21.94 | |
| 1-Oct-13 | 17.86 | 2016 | 23.61 | 23.61 | |
| 1-Nov-13 | 18.15 | 2017 | 23.56 | 23.56 | |
| 1-Dec-13 | 18.04 | ||||
| 1-Jan-14 | 18.15 | ||||
| 1-Feb-14 | 18.06 | ||||
| 1-Mar-14 | 18.48 | ||||
| 1-Apr-14 | 18.35 | ||||
| 1-May-14 | 18.46 | ||||
| 1-Jun-14 | 18.88 | ||||
| 1-Jul-14 | 18.96 | ||||
| 1-Aug-14 | 18.68 | ||||
| 1-Sep-14 | 18.81 | ||||
| 1-Oct-14 | 18.5 | ||||
| 1-Nov-14 | 19.75 | ||||
| 1-Dec-14 | 20.08 | ||||
| 1-Jan-15 | 20.02 | ||||
| 1-Feb-15 | 20.77 | ||||
| 1-Mar-15 | 20.96 | ||||
| 1-Apr-15 | 21.42 | ||||
| 1-May-15 | 21.92 | ||||
| 1-Jun-15 | 22.12 | ||||
| 1-Jul-15 | 22.4 | ||||
| 1-Aug-15 | 22.15 | ||||
| 1-Sep-15 | 21.45 | ||||
| 1-Oct-15 | 22.68 | ||||
| 1-Nov-15 | 23.67 | ||||
| 1-Dec-15 | 23.74 | ||||
| 1-Jan-16 | 22.18 | ||||
| 1-Feb-16 | 22.02 | ||||
| 1-Mar-16 | 23.39 | ||||
| 1-Apr-16 | 23.97 | ||||
| 1-May-16 | 23.81 | ||||
| 1-Jun-16 | 23.97 | ||||
| 1-Jul-16 | 24.52 | ||||
| 1-Aug-16 | 24.57 | ||||
| 1-Sep-16 | 24.22 | ||||
| 1-Oct-16 | 23.57 | ||||
| 1-Nov-16 | 23.35 | ||||
| 1-Dec-16 | 23.76 | ||||
| 1-Jan-17 | 23.59 | ||||
| 1-Feb-17 | 23.68 | ||||
| 1-Mar-17 | 23.6 | ||||
| 1-Apr-17 | 23.23 | ||||
| 1-May-17 | 23.24 | ||||
| 1-Jun-17 | 23.36 | ||||
| 1-Jul-17 | 23.15 | ||||
| 1-Aug-17 | 23.36 | ||||
| 1-Sep-17 | 23.11 | ||||
| 1-Oct-17 | 23.55 | ||||
| 1-Nov-17 | 24.09 | ||||
| 1-Dec-17 | 24.7 | ||||
| 1-Jan-18 | 25.06 |
Sheet2
Industry NameBeta D/E Ratio
Unlevered
beta
Utility (General)180.2930.6720.175
Bank (Money Center)110.6381.5730.248
Utility (Water)230.3420.3810.248
Power610.5050.7640.286
Banks (Regional)6120.5020.5870.316
Telecom (Wireless)181.3021.2000.592
Green & Renewable Energy221.2020.9820.606
Environmental & Waste Services870.8770.3490.650
Investments & Asset Management1650.9870.4210.695
Healthcare Support Services1150.8980.2480.719
Information Services610.8820.1570.762
Electronics (General)1670.9380.1500.816
Heathcare Information and Technology1120.9790.1930.821
Software (Entertainment)130.8910.0650.837
Computer Services1111.1010.3080.842
Computers/Peripherals581.0100.1820.854
Telecom. Equipment1041.0340.2070.857
Entertainment901.1520.3370.861
Semiconductor Equip450.9820.1150.880
Business & Consumer Services1691.1690.2740.917
Software (System & Application)2551.0880.1410.953
Electronics (Consumer & Office)241.0920.0691.021
Semiconductor721.1720.1311.037
Drugs (Pharmaceutical)1851.2090.1461.055
Retail (Online)611.1820.1141.061
Software (Internet)3051.2030.0331.165
Oil/Gas (Integrated)51.3720.1531.190
Drugs (Biotechnology)4591.4400.1581.243
Food Wholesalers151.7860.3751.299
Steel371.8170.3621.334
Chemical (Diversified)72.0340.2721.599
Total Market72471.0000.5900.629
Total Market (without financials)60571.0690.3070.817
Average beta estimates for selected sectors
Number of
firms
𝑃𝑉
𝑡
=
𝐶
𝑡
−
𝜌
𝐶
𝑡
,𝑟
𝑀
𝜎
𝐶
𝑡
𝜎
𝑀
×(𝑟
𝑀
−𝑟
𝐹
)
1+𝑟
𝐹
=
$1,000−
0.195×$1,173
0.2511
×0.2244
1+0.1249
=
$796
1.1249
𝑃𝑉
𝑡
=$707