that the higher the correlation coefficient, the closer the points lie to the regression
line, and the smaller the errors.
In actual practice, one would use the least squares methodfor finding the regression
coefficients a and b. This procedure minimizes the squared values of the error terms,
and it is discussed in statistics courses. However, the least squares value of beta can be
obtained quite easily with a financial calculator.^11
Although it is possible to calculate beta coefficients with a calculator, they are usu-
ally calculated with a computer, either with a statistical software program or a spread-
sheet program. The file Ch 03 Tool Kit.xls on your textbook’s web site shows an ap-
plication in which the beta coefficient for Wal-Mart Stores is calculated using Excel’s
regression function.
The first step in a regression analysis is compiling the data. Most analysts use four
to five years of monthly data, although some use 52 weeks of weekly data. We decided
to use four years of monthly data, so we began by downloading 49 months of stock
prices for Wal-Mart from the Yahoo!Finance web site. We used the S&P 500 Index as
the market portfolio because most analysts use this index. Table 3-5 shows a portion
of this data; the full data set is in the file Ch 03 Tool Kit.xls on your textbook’s
web site.
The second step is to convert the stock prices into rates of return. For example, to
find the August 2001 return, we find the percentage change from the previous
month: –14.0% –0.140 ($47.976 – $55.814)/$55.814.^12 We also find the percent
change of the S&P 500 Index level, and use this as the market return. For example, in
August 2001 this is –3.5% –0.035 (1,294.0 1,341.0)/1,341.0.
As Table 3-5 shows, Wal-Mart stock had an average annual return of 31.4 percent
during this four-year period, while the market had an average annual return of 6.9 per-
cent. As we noted before, it is usually unreasonable to think that the future expected re-
turn for a stock will equal its average historical return over a relatively short period,
such as four years. However, we might well expect past volatility to be a reasonable esti-
mate of future volatility, at least during the next couple of years. Note that the standard
deviation for Wal-Mart’s return during this period was 34.5 percent versus 18.7 percent
for the market. Thus, the market’s volatility is about half that of Wal-Mart. This is what
we would expect, since the market is a well-diversified portfolio, in which much risk has
been diversified away. The correlation between Wal-Mart’s stock returns and the mar-
ket returns is about 27.4 percent, which is a little lower than the correlation for an aver-
age stock.
Figure 3-11 shows a plot of Wal-Mart’s stock returns against the market returns.
As you will notice if you look in the file Ch 03 Tool Kit.xls, we used the Excel Chart
feature to add a trend line and to display the equation and R^2 value on the chart itself.
Alternatively, we could have used the Excel regression analysis feature, which would
have provided more detailed data.
Figure 3-11 shows that Wal-Mart’s beta is about 0.51, as shown by the slope coef-
ficient in the regression equation displayed on the chart. This means that Wal-Mart’sCalculating Beta Coefficients 129(^11) For an explanation of calculating beta with a financial calculator, see the Chapter 3 Web Extension on the
textbook’s web site, http://ehrhardt.swcollege.com.
(^12) For example, suppose the stock price is $100 in July, the company has a 2-for-1 split, and the actual price
is then $60 in August. The reported adjusted price for August would be $60, but the reported price for July
would be lowered to $50 to reflect the stock split. This gives an accurate stock return of 20 percent: ($60
$50)/$50 20%, the same as if there had not been a split, in which case the return would have been
($120 $100)/$100 20%.
Or suppose the actual price in September were $50, the company paid a $10 dividend, and the actual price
in October was $60. Shareholders have earned a return of ($60 $10 $50)/$50 40%. Yahoo reports an
adjusted price of $60 for October, and an adjusted price of $42.857 for September, which gives a return of ($60
- $42.857)/$42.857 = 40%. Again, the percent change in the adjusted price accurately reflects the actual return.