Estimating Betas

A firm's beta can be estimated from the slope of the characteristic line. The first step is to plot the return on the firm's stock (Y axis) vs. the return on a broad market index (X axis). Next, a regression line is estimated to find the slope.

1. Go to finance.yahoo.com, enter the symbol for a company of your choice, and click on "Get Quotes". On the left-side menu, click on "Historical Prices", then enter starting and ending dates that correspond to the most recent two years. Select the "Daily" option. Save the data to a spreadsheet.

2. Repeat the process to get comparable data for the S&P 500 Index (symbol ^GSPC). Download the data and copy it into the same spreadsheet as your firm's data with dates aligned.

3. Sort the data from earliest to latest. Calculate the return on the stock and the return on the index for each day using the adjusted closing prices.

4. Prepare an XY scatter plot with no line inserted. Be sure that the firm's returns represent the Y variable and the market's returns represent the X variable.

5. Select one of the data points by pointing to it and clicking the left mouse button. While the point selected, right-click to pull up a shortcut menu. Select "Add Trendline", choose the linear type, then click on the Options tab and select "Display equation on chart". When you click on "OK" the trendline and the equation appear. The trendline represents the regression equation. What is the firm's beta?

The Three-Factor Model

Calculate the expected return on a stock of your choice using the Fama/French three-factor model by following the directions below.

1. Go to Kenneth R. French's website mba.tuck.dartmouth.edu/pages/faculty/ken.French/data_library.html and locate the data for the daily Fama/French factors. After you download the file, copy the data for the most recent two years into an Excel spreadsheet. (You may have to use the Data, Text to Columns menu to get the data into a useable format.)

2. Download closing price data for a stock of your choice, with dates that correspond to the Fama/French data, from finance.yahoo.com or moneycentral.msn.com/investor/research/welcome.asp. Calculate the stock's daily returns.

3. Estimate the three factor model for your stock:

r_S ? r_f = ?_s + ?_M(r_M-r_f) + ?_HMLrHML + ?_SMBrSMB + ?_S

To do this, line up the data in the order specified by the model, then use Excel's Tools, Data Analysis, Regression menu. (Use r_s-r_f as the Y variable, r_M -r_f as the first X variable, r_HML as the second X variable, and r_SMB as the third X variable.)

4. Use the resulting regression coefficients to estimate the expected return on your stock based on the most currently available Fama/French factors from the French website.