Portfolio Project - Calculating alpha and beta

A Comment about Ex Post versus Ex Ante returns

There is an important distinction between what is expected and what actually happens in the market. The capital asset pricing model (CAPM) states that the EXPECTED return on a stock (E(r_i)) equals the risk free rate of return (r_f) plus the stock beta times the expected market return minus the risk free rate of return ([E(r_m) - r_f]). So the CAPM says:

E(r) = r_f + b[E(r_m) - r_f]

In reality, we cannot directly observe the expected returns of a stock. But what we can observe is the actual stock return from last Wednesday or last Thursday or last Friday. The actual return from last Wednesday is r_wednesday which is not the same thing as the expected return last Wednesday. So for the sake of this assignment, we are regressing the ACTUAL stock returns (left hand side) onto the actual market returns. The regression model looks like this:

r - r_f  = a + b[r_m - r_f] + e

Notice in this regression model, you are regressing excess stock returns (r - r_f ) onto excess market returns ([r_m - r_f]) to calculate the alpha and the beta of the stock.

The CAPM makes a prediction about ex ante (ahead of time) expectations. The regression model uses ex post (after the fact) data for the calculations in this assignment.

Calculating alpha and beta for a stock:

The purpose of this assignment is to show students how to calculate the alpha and beta of three stocks in the student portfolio using data readily available from the Internet and the regression function in Excel. To make these calculations, students will need to begin by picking three stocks (this was homework #2). Any stock that is a recent IPO will not have sufficient data to calculate the firm's alpha and beta. The instructions below related to the tests using one particular stock. But the student should do the same thing for all three stocks PLUS AND EQUALLY-Weighted PORTFOLIO OF THE THREE STOCKS.

Begin by downloading the stock prices for a particular stock. You will need to pick a stock that your professor approves in class. To get stock information, you will need the ticker symbol for the firm you are investigating. The ticker symbol is the unique 1-4 letter code under which the stock trades on the stock exchange. You can then download the needed data either on yahoo finance or using Google finance in spreadsheets. Using either one of these methods is fine for the sake of this class.

Getting data using Yahoo finance:

Go to www.yahoo.com and click on the finance section of the web portal. This should take you to http://finance.yahoo.com/. This site allows you to look up the prices of the firm based on its ticker symbol. On the left side of the screen, type in your firm's ticker symbol and hit enter. This should bring up the firm and general information about the stock. Specifically, this should bring up a screen with the firm name, current stock price, most recent stock return, and a chart of the stock price over time.

One of the important characteristics listed on the first page in Yahoo Finance is the firm's beta. Record this information as you will need this later for your report. In addition, you should note the general characteristics of the firm for your report. The purpose of this assignment is to learn how to calculate the beta rather than just look this information up on the Internet. However, you will need to compare your calculated beta to the firm beta as reported on Yahoo Finance.

Download Stock Prices

On the left hand side of the page, click on "Historical Prices." This will lead you to a window where you can enter the dates over which you can get the stock prices. You should enter dates to get data for at least the past one year. For this particular project, you will need to download data for daily prices.

Once you have entered the past year of data and the time period for the stock prices you wish to have, download this data directly to Excel. You should have data that includes the date of the price, the volume of shares, the daily high price, the daily low price, the closing price, and the adjusted close. For the calculations you are doing, you will need to use the adjusted close price. This particular price is adjusted for dividends. It assumes that if you are paid a dividend you re-invest the dividend into the stock. Therefore, you can effectively ignore the dividends that the firm pays.

Calculating Stock Returns

The capital asset pricing model makes a prediction about the relationship between a stock RETURN and the market RETURN.

E(r) = r_f + b[E(r_m) - r_f]

There are no prices in the capital asset price model. As such, you will now need to calculate returns for the stock you are examining and the market. To get returns, you will need to use the adjusted price for one day and the previous day. For instance, suppose a stock has a price of 18.25 on a Thursday and a price of 18.75 on the next day, Friday. This would imply that the return of the stock is

Return = (18.75 - 18.25) / 18.25 = 1.37%

So the return for that Friday would be 1.37%.  Suppose that the stock price on the next Monday goes down to $18.12. This would imply that the stock return on Monday is

Return = (18.12-18.75) / 18.75 = -3.36%

Use the Excel spreadsheet to calculate the return each day for the stock. So you will convert the daily stock prices into daily returns using the Excel worksheet. Be careful about what is a lagged day and what is today!  It matters if you use yesterday's price as your initial investment price (correct) or you use tomorrow's price as your initial investment price (incorrect).

Calculating Market Returns

Follow the exact same procedure for the market return as you did for calculating the stock's return - except - what is the market's ticker symbol? You need to use a proxy for the market. There are several that are commonly used. For the sake of this class, I recommend that you use the S&P 500 since it is a good representation of the overall market. There are many ETFs for the S&P 500, but using the S&P 500 Spider (ticker symbol SPY) will give a very close approximation to the S&P 500 stock returns.

Once again, calculate returns for the market proxy following the above procedure of converting adjusted prices into returns. Once you have market returns, you should copy the market returns into the same spreadsheet with your stock returns. Please be sure that you have the same date for your stock returns as for your market returns.

Adjusting by the Risk Free Rate of return

For the sake of this project, we will assume that the risk free rate of return is zero. This assumption is okay for several reasons. First, the risk free rate of return is much lower than the expected rate of return on a stock anyway. Second, even if the annual expected risk free return is several percent, the daily expected risk free return is still quite close to zero. Therefore, you will adjust your stock returns by subtracting zero and your market returns by subtracting zero.

Running a Regression in Excel

Once you have two columns of returns, one column for daily stock returns and one column for daily market returns, you are ready to run your regression. In Excel, go to Tools -> Data Analysis -> Regression. You need to be sure that the Data Analysis Toolpack is available in Excel before you can use Data Analysis. You then enter your Y as your excess stock returns and your X as your excess market returns. (it is very important that your market returns are your X. Otherwise, you are assuming that your stock is driving the entire stock market). Once you have entered your data range for your X and Y you can hit the okay button and Excel will run your regression.

What to Hand In

Once you have completed your regression, you will get a summary output from your regression. This will include information about your alpha (Intercept coefficient), your beta (X variable coefficient), and other information about the statistical significance of your regression coefficients (such as the t-statistic).

FOR ALL THREE STOCKS PLUS THE EQUALLY_WEIGHTS PORTFOLIO (FOUR TIMES)

You should hand in 1) a labeled regression Summary Output using daily stock returns to calculate alpha and beta (please make sure these print on only one page - otherwise, they are difficult to read), 2) a labeled regression Summary Output using monthly stock returns to calculate alpha and beta, and a discussion of these variables. You should also include 3) a scatter plot with daily market return on the X axis and stock return on the Y axis. It is helpful to print out this plot with a best fit line. Your discussion should include answers to the following questions.

4) What is does Yahoo Finance report as the beta of this stock? What are your beta estimates using daily data and monthly data?

5) What is the alpha of your stock? Is this statistically significant? How do you know? What does a significant alpha mean? What does an insignificant alpha mean?