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:
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 alpha, the second (bottom) is 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:
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.