COMPUTING THE BETA FOR INTEL monthly returns for Intel and S&P 500, 2001-2006

Intel Returns vs SP500, 2001-2006

y = 2.2516x - 0.0029

Here’s what we can learn from this regression:

• Intel’s beta, b_Intel, shows the sensitivity of its stock return to the market return. It is calculated by the following formula.

βIntel

Covariance(SP500 returns, Intel returns) Variance(SP500

= rreturns)

We can compute b either by using this formula directly (cell B5) or by using the Excel Slope function (cell B4). Over the period covered, a 1 percent increase or decrease in the monthly returns of the S&P 500 was accompanied by a 2.25 percent increase or decrease in Intel’s returns. The statistic TSlope (cell B8) shows that the b_Intel is highly signifi cant (see section 2.5.2 for how this function was constructed).⁸

• Intel’s alpha, a_Intel, shows that irrespective of changes in the S&P 500, the monthly return on Intel over the period was aIntel = −0.29 percent. On an annual basis, this is 12 * −0.29 =−3.5 percent; this seems to indicate that, in the jargon of fi nancial markets, Intel had negative performance over the period. Note, however, the TIntercept (cell B7):

This function (its construction in Excel is discussed in section 2.5.2) shows that the negative intercept is not signifi cantly different from zero.

• The R² of the regression shows that 53 percent of the variation in Intel’s returns is accounted for by variability in the S&P 500. An R² of 53 percent may seem low, but in the CAPM literature this is actually quite a respect-able number. It says that roughly 53 percent of the variation in Intel’s returns is explicable by the variation in the S&P 500 return. The rest of the variability in the Intel returns can be diversifi ed away by including Intel’s shares in a diversifi ed portfolio of shares. The average R² for stocks is approximately 30–40 percent, meaning that market factors account for approximately this percentage of a stock’s variability, with factors idiosyncratic to the stock accounting for the rest.

The spreadsheet shows three ways of doing the regression: One way is to use the functions Intercept, Slope, Rsq. A second method involves

8. For the precise meaning of a t-statistic, you should refer to a good statistics text. For our purposes, a t-statistic over 1.96 indicates that with 95 percent probability the vari-able under discussion (the intercept when using TIntercept or the slope when using TSlope) is signifi cantly different from zero. Thus the t-statistic for the intercept of

−0.2438 indicates that the intercept is not signifi cantly different from zero, whereas the t-statistic for the slope of 8.0942 indicates that the slope is signifi cantly different from zero.

Figure 2.1

The sequence of commands for producing regression results from the XY Scatter Plot in Excel. Having marked the points, we right-click to select Add Trendline (left panel). We choose the linear regression (middle panel) and then click on the Options tab to indicate that the regression equation and the R² should be indicated on the graph.

using the Excel functions Covar and VarP. A third way involves Excel’s Trendline function. Having graphed the returns of Intel and the S&P 500 on an XY Scatter plot, we then follow the procedure described in Figure 2.1.

2.5.2 The Homemade Functions TIntercept and TSlope

The preceding spreadsheet uses two functions to compute the t-statistics for the intercept and slope. These functions are built on the Linest func-tion discussed in Chapter 33. Applying Linest to the return data, we get the following:

10 11 12 13 14 15 16 17 18

L K

J I

Slope Intercept

Slope --> 2.2516 -0.0029 <-- Intercept

Standard error of slope --> 0.2782 0.0120 <-- Standard error of intercept R-squared --> 0.5304 0.0927 <-- Standard error of y values

F statistic --> 65.5155 58.0000 <-- Degrees of freedom

SSxy --> 0.5627 0.4982 <-- SSE = Residual sum of squares Cells J14:K18created with the

formula

=LINEST(E12:E71,F12:F71,,1)}

By using the Excel function Index we defi ne a function TIntercept that divides the value of the intercept term produced by Linest (fi rst row, second column of Linest output) by the standard error of the intercept (second row, second column):

Function tintercept(yarray, xarray) tintercept = Application.

Index(Application.

LinEst(yarray, xarray, , 1), 1, 2) / _ Application.Index(Application.

LinEst(yarray,

xarray, , 1), 2, 2) End Function

Similarly we can defi ne a function TSlope that gives the t-statistic for the slope:

Function tslope(yarray, xarray)

tslope = Application.Index(Application.

LinEst(yarray, xarray, , 1), 1, 1) / _ Application.Index(Application.

LinEst(yarray,

xarray, , 1), 2, 1) End Function

2.5.3 Using Excel’s Data Analysis Add-in

There’s a fourth way to produce the regression output: By clicking on Tools|Data Analysis|Regression, we can use a sophisticated Excel routine that computes more statistics, including the t-statistics. The output for this routine is illustrated as follows:

Rows 73–90 were produced from Tools|Data Analysis|Regression using the following values:

1

Alpha -0.0029 <-- =INTERCEPT(E11:E70,F11:F70) Beta 2.2516 <-- =SLOPE(E11:E70,F11:F70) R-squared 0.5304 <-- =RSQ(E11:E70,F11:F70) t for alpha -0.243762 <-- =tintercept(E11:E70,F11:F70) t for beta 8.094164 <-- =tslope(E11:E70,F11:F70)

Date Intel SP500 Intel SP500

9-Jan-01 35.38 1366.01

1-Feb-01 27.32 1239.94 -0.2585 -0.0968 <-- =LN(C11/C10)

1-Mar-01 25.17 1160.33 -0.0820 -0.0664 <-- =LN(C12/C11)

2-Apr-01 29.57 1249.46 0.1611 0.0740 <-- =LN(C13/C12)

1-May-01 25.86 1255.82 -0.1341 0.0051

1-Jun-01 28 1224.38 0.0795 -0.0254

2-Jul-01 28.54 1211.23 0.0191 -0.0108

1-Aug-05 25.24 1220.33 -0.0506 -0.0113

1-Sep-05 24.19 1228.81 -0.0425 0.0069

3-Oct-05 23.06 1207.01 -0.0478 -0.0179

1-Nov-05 26.27 1249.48 0.1303 0.0346

1-Dec-05 24.57 1248.29 -0.0669 -0.0010

3-Jan-06 25.9 1285.45 0.0527 0.0293

SUMMARY OUTPUT

Regression Statistics

Multiple R 0.728302

R Square 0.530423

Adjusted R Square 0.522327 Standard Error 0.092677

Observations 60

ANOVA

df SS MS F Significance F

Regression 1 0.5627 0.5627 65.5155 0.0000

Residual 58 0.4982 0.0086

Total 59 1.0609

Coefficientstandard Erro t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%

Intercept -0.0029 0.0120 -0.2438 0.8083 -0.0269 0.0210 -0.0269 0.0210

X Variable 1 2.2516 0.2782 8.0942 0.0000 1.6948 2.8084 1.6948 2.8084

COMPUTING BETA FOR INTEL Monthly returns for Intel and SP500, 2001-2006

Prices Returns

While Tools|Data Analysis|Regression produces a lot of data, it has one major drawback: The output is not automatically updated when the underlying data changes. For this reason we prefer to use the other methods illustrated.

2.6 Using the Security Market Line to Calculate Intel’s Cost of Equity

In the capital asset pricing model, the security market line (SML) is used to calculate the risk-adjusted cost of capital. In this section we consider two SML formulations. The difference between these two methods has

to do with the way taxes are incorporated into the cost-of-capital equation.

2.6.1 Method 1: The Classic Security Market Line

The classic CAPM formula uses an SML equation that ignores taxes:

Cost of equity, r_E = +r_f β[ (E r_M)−r_f]

Here r_f is the risk-free rate of return in the economy, and E(R_M) is the expected rate of return on the market. The choice of values for the SML parameters is often problematic. A common approach is to choose

• r_f equal to the risk-free interest rate in the economy (for example, the yield on Treasury bills).

• E(rM) equal to the historic average of the market return, defi ned as the average return of a broad-based market portfolio. There is an alternative approach based on market multiples; both of these are discussed in section 2.7.

The following spreadsheet fragment illustrates the classic CAPM cost of equity:

1 2 3 4 5

B

A C

Intel beta 2.2516

Risk free rate, rf 4.93%

Expected market return, E(rM) 9.88%

Intel cost of equity, rE,Intel 16.31% <-- =B3+B2*(B4-B3)

COMPUTING THE COST OF EQUITY FOR INTEL