Estimating Beta and Alpha of a Stock using Regression Analysis

Project: Determine beta and alpha

In this project, we will estimate a stock’s beta using historical data as well as determining whether the stock offers alpha in excess of return predicted by beta.

I suggest that you choose the company from the S&P 500 list, the list of companies is available in the following link, you can also find the company’s ticker symbol there:

http://en.wikipedia.org/wiki/List_of_S&P_500_companies

We will estimate a firm’s beta and alpha using the regression technique that uses the firm’s excess stock return and the market excess stock return. This technique essentially measures how sensitive a firm’s stock return is to market return. The estimation can be performed in excel.

Once the company that you’d like to analyze is determined, you can find the stock price information of the company at Yahoo Finance. Simply go to http://finance.yahoo.com/, at the left upper corner, enter the ticker symbol of the company’s stock then press “get quotes”, that should lead you to the page containing information about the company’s stock.  

To find the company’s historic price information, click “historical prices” on the left side of the page. This should lead you to a page similar to this (using Yahoo company as an example):

http://finance.yahoo.com/q/hp?s=YHOO

To estimate a firm’s beta, we usually need at least 36 months of stock return data, so please download monthly stock price data from 12/31/2007 to 12/31/2010 (you can choose the frequency of data at right side of the set date range box, use the adjusted close price). The download to spreadsheet option is at the bottom of the page.

To estimate beta, we also need the market return data. We usually can use the S&P 500 as a proxy for the market. To find the S&P 500 price, enter ^GSPC or S&P 500 on the “get quotes” box on the left upper corner of the page and find the historical price for S&P 500 just like you did for the company’s stock price. Download the information as well.

Lastly, you will need the risk free rate. For risk free rate, we usually use the 1-month Treasury bill rate as proxy. The monthly 4-week Treasury bill rate can be found at Federal Reserve website:

http://research.stlouisfed.org/fred2/series/TB4WK?cid=116. Download the data for the appropriate time period. The risk free rate is in percentage terms, so you will need to divide that by 100 to convert it to decimals and make it comparable with yahoo data.

Now you have the monthly price of the company and the market, first step is to calculate the monthly return for both. The return formula at time t is simply (P_t-P_t-1)/P_t-1, so the return for February will be the price of February minus the price of January, then divided by the price of January, using this formula to calculate the monthly return for both the company and the market from 1/1/2008 to 12/31/2010. Once you have the monthly return data for both company and the market, you need to calculate the excess return for both by deducting the monthly risk free rate from the monthly return, this should give you the 36 months of excess return data that you need for estimating beta.

To use excel to estimate beta, two methods can be used, the standard method is to use the regression analysis offered by the excel data-analysis add-in.

Once you find the data analysis tool, select regression, a window like below will pop up.

For input Y range, choose the excel range that contains the company’s excess stock return information, for input X range, choose the excel range that contains the market excess return information. Also choose the output range to where you want the results to be shown, and then click OK. This should give you results looking like this:

The coefficient value for STUDENTS (in your case, “students” is replaced by whatever name you give to the column that contains market return data, or simply just X if you don’t give any name) is the beta for the company.

The coefficient value for intercept is the alpha of the company. However, the alpha is only statistically significant if t Stat is bigger than 1.64. Since in our example the t Stat for alpha is only 1.294, it’s not significant.

Pick a different company, using the same technique to estimate the firm’s beta at the end of January 2020. Remember you need at 36 months of the most recent past stock return data to estimate it. Compare the estimate with the current beta of the stock as shown in Yahoo Finance (you can find it under key statistics), is it close?

What is the alpha of the company, is it statistically significant?