When I wrote about computing stock betas in 2010, I had no idea it would be this blog’s third most popular topic. I wrote a handful of blog posts about stock beta, but my heart wasn’t in them. Today, driving home from the airport, I was inspired to blog about beta for perhaps the last time. Previously I held back and focused on the mechanics of beta computation, and the discrepancies I was seeing between various website’s beta values. This time I provide an example beta-computation spreadsheet and don’t hold back on the math or the theory. Before I launch into this final word on beta, here a few highlights.

- Beta is easy to find online. Not all sites agreed on value, but the delta seems less than it was 2 years ago. Why compute beta when you can simple look it up?
- Beta is less useful if it has a low R-squared. Luckily, sites like Yahoo! Finance provide R-squared values.
- Even with a high R-squared, beta is not a very useful risk measure. Standard deviation is better in many ways.
- In theory high-beta stocks (>3) should go up dramatically when the market goes up. In practice this is often not the case.
- In theory low-beta stocks (<0.5) should be “safer” than the market. Again not so true.
- In theory low-beta stocks (<0.5) should “under-perform.” Not necessarily.

If you are still interested in beta, simply click to read the full-beta blog.

The beta computation saga continues. I came up with a modified version of the example beta computation method from:

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

I incorporated a couple modifications (specific to Excel 2010):

- Install the “Analysis Toolpak” Add-in:
- File->Options->Add-ins->”Go…”->”Analysis Toolpak”

- Data->”Data Analysis”->Regression
- You will have the option of “R Square”. You will have a couple coefficients, the first (top) is
, the second (bottom) is*alpha*.*beta*

The “Babson Method” is equally effective. Take your pick. Beta and “R square” together are more useful than beta alone. Remember that a low R-square (say <0.5) means that (historic) beta is not particularly useful for explaining the movement of the stock or asset in question. Moreover either method also supplies a (historic) alpha… a measure of that assets excess return versus the benchmark.

Like any backward-looking analysis, historic alpha, beta, and R-square provide ways to look a that asset’s past. One hopes that they provide some measure of an asset’s future… this may or may not prove to be the case.

I still see a minor factor that makes either method slightly imperfect…. the lack of accounting for total return. The basic method don’t account for (re-invested) dividends. However this is fairly easily remedied by factoring in dividend payments into the asset returns. It is likely that there are other refinements to be found.

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.