I will try to use as little math and jargon as possible…

* Beta *is one way to look at a stock’s behavior relative to the rest of the stock market. The most common way to compute beta for a stock is to compare its price over a 3 year period versus the S&P 500. The beta for a stock can be computed with daily, week, monthly or other data. Generally the difference in the final answer is small between these. Personally, I favor a beta based on daily closing values, but for this blog post I’ll stick with a monthly beta computation.

Lets compute beta for CSX versus SPY (an S&P500-based index EFT) using Microsoft Excel 2010:

- Go to Yahoo Finance and type in ticker symbol CSX.
- Click on “Historic Prices” and set the range from Oct 18, 2007 to Oct 18, 2010. Select the “Monthly” radio button.
- Scroll to the bottom of the page and click “Download to Spreadsheet.”
- When prompted select “Open With -> Microsoft Excel”.
- Cut and paste the data in the “Adj Close” column to a new spreadsheet.
- Repeat the above process for SPY. Put the SPY data in a column adjacent to the adjusted CSX closing price data.
- Compute the variance of SPY for example SPY data points e.g. “=VAR.S(C4:C40)”
- Compute the covariance of CSX with respect to SPY e.g. “=COVARIANCE.S(B4:B40,C4:C40)”
- Beta is, by definition, the value in step 8 divided by the value in step 7. However I have found this not the case when using the MS Excel 2010 formulas above. The next steps tell how I “fix” this beta.
- Compute average values for CSX and SPY: e.g “=AVERAGE(B4:B40)” and “=AVERAGE(C4:C40)”
- The fixed value is the result of step 9 * the SPY average/the CSX average. This is CSX’s 3-year, monthly beta.

Using this method, I compute a beta for CSX of 1.00. This is a fair bit different that the value of 1.24 reported by Yahoo Finance. I used the same process for MSFT and compute a beta of 0.93 versus Yahoo Finance’s 1.03 for Microsoft stock. Looking for a more out-there beta, I repeated the process for C (Citigroup). I computed a beta of 4.39 versus Yahoo Finance’s 2.65. For another comparison Google Finance reports betas for CSX, MSFT, C of 1.2, 1.05, and 2.54. Finally, MSN Money reports betas of 1.21 ,1.06, and 2.55.

It irks me that 1) these 3 finance sites don’t detail their beta-computation methods, 2) They produce different results, 3) My method produces different results, 4) MS Excel doesn’t [I don’t believe] offer a beta or beta.finance function, 5) I have to tweak MS Excel data to get a more reasonable beta computation.

Be that as it may, I managed to explain one way of computing beta, and did so with a minimum of math. Please feel free to flame this post and tell me a better way. Until then feel free to try my method, or create your own modified method.

P.S. — I did some web searching and found an alternate method that is pretty decent:

http://faculty.babson.edu/academic/Beta/CalculateBeta.htm

They also perform a monthly 3-year beta computation. I like that it is clear, correct, and easy to follow. I don’t like that it uses an older version of Excel and that it requires graphing and essentially reading the numbers off of the graph.

thanks for the post

LikeLike