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 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.
Previously I started blogging about the very different approaches my parents took with respect to money and investing. In this blog post I continue that discussion with a story of how I became even more passionate about investing.
My parents divorced not long after I started attending college. Because of the way divorce law works, my Mom received the majority (perhaps two-thirds) of the family assets plus a fairly significant monthly alimony payment. Over the next ten years Dad rebuilt his financial life, benefiting from the remarkable 90′s bull market and intelligent investing. Over that same period, Mom’s financial fortunes floundered. I witnessed both financial journeys as a powerless spectator.
The sad irony is that Dad, the savvy investor, was willing to listen to my investing ideas, whereas Mom stubbornly refused almost all of my investing advice. I saw Mom make one bad investing decision after another. She put the house on the market but could not sell it because her asking price was about $100K too high. She loaned money to business partners without a written contract… money that was never paid back. Most upsetting to me: She let her investment adviser, Sam W., manage her IRA, losing money with highly under-diversified utilities stocks and funds in the midst of this tremendous bull market. The contempt and disappointment I feel towards Sam still lingers with me to this day. That Mom blindly trusted this man, who likely had little interest in her well-being, and shunned her son’s financial advise left me with stunned disbelief.
I was interested in investing from the time I learned about compound interest at around the age of 9. I was fascinated by the math of computing compound interest monthly, daily, hourly, continuously. I was intrigued by the concept of companies, shareholders, stock exchanges, and business. But it was in watching and living the real-world consequences of my parent’s good and bad investing actions, that my lifelong passion for investing was forged.
These experiences are probably why I am so driven to help people avoid making big financial blunders. I’ve seen and felt the effects of load funds and self-serving financial advisers. I’ve seen the impact of poor diversification. I’ve seen the tears of losing a home, losing a business… due to poor financial choices.
I’m often looking for ways and words to become more persuasive. I’m looking for ways to help people build interest and confidence in shaping their own financial destinies. I’m working to develop tools to simply and explain the financial world. I’m working to create this financial education blog which will someday become part of a personal finance book.
Finance is my passion. This passion is often hard for people to understand. Perhaps this blog article will help people understand. Probably some of my readers share a passion for personal finance and investing. If you have a similar passion, I hope you will consider sharing your financial stories that shaped your financial lifestyle.
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.
Call it a whim, but I’m short term bullish. I just put in a market order for 100 shares of TOT. It will execute the trade at market open tomorrow. I’m also very happy about my JNJ buy-write. It has worked nicely so far; I collected my dividend, and I’m about 50/50 to close out the option+position on Friday for a modest profit.
These are Crazy Ivan Account (CIA) trades. My “sane and steady” portfolio is much unchanged. I reallocated about 2% from equities to TIPS – a relatively big move by my standards. I also moved about $13K from equities to the newly re-opened Vanguard Convertible Securities Fund. I viewed this latter move as largely an equities to equities move. Both of these transitions are in my tax-differed accounts. My taxable accounts are largely unchanged except for certain modest real-estate-related actions.
The common theme of my recent moves is pursuit of yield. I’ve typically had a larger than typically overall cash holding in my portfolio. I’ve preferred cash yielding 3-4% to bonds yielding 4-5% simply because of the flexibility. In the same vein I’ve been pretty dogmatic about using any “spare cash” to pay down even my personal and company ~4-5% mortgage(s)/HELOC(s). But with long bonds yielding 3% and cash yielding next to 0%, I’ve had to reinvent my investing posture. Convertibles, munis, real estate, and modestly high-yield value stocks are gradually creeping into my investment mix.
I’ve now got a spreadsheet where I can enter 3-year daily stock price data and get a beta versus the S&P500 as modeled by the ETF SPY. There are still a few finer points I don’t like about the modeling. The biggest remaining gap in the computation is that the model doesn’t account for dividends on either the stock or the S&P. Ideally it would add the dividend into the price on the ex dividend date. If I could find a source where that data is built in or I took the time to merge the data it I would be set… but I haven’t bothered to do so. Another nice feature would be an R-squared computation.
So now what? Probably nothing for a while. I’m already thinking about different things. Most are things I wish other people would do 
.
- Create a low expense-ratio ETF that tracks a passive covered-call index like BXM. (No, and not an ETN… I want collateral!)
- Create an open-source format for storing and sharing stock, index, portfolio, bond, ETF data. Perhaps XML-based. Nice features would be handling of splits, ticker symbol changes, dividend and ex-dividend dates, and market holidays. Support for different time periods would be a must. Support for earnings, book values, revenue and other supporting data would be nice. Perhaps such a format already exists?
- Glue together this format with cool graphing software like Open Flash Charts and/or something HTML5 based.
- Open source statistical tools to work with this format to compute volatility, beta, R-squared, P/E ratios, etc.
Until next time, happy financial modeling.
I’ve crunched my first set of numbers. Specifically I’ve computed the beta of XOM (Exxon Mobil) vs. SPY (SPDR S&P 500 ETF) for 365 days ending Feb 4, 2010. My computed beta is 0.125. This is based on daily sampling of closing prices for a 365-day period. Not content with non-uniform sampling (e.g. discarding holiday and weekend data when the markets are not open), I recomputed beta over the same period with interpolated weekend/holiday data and came up with a beta of 0.117. I have not yet bothered to compute R-squared.
These are surprisingly low betas. Also interesting is the difference data interpolation can make… a not insignificant difference of 8.6%
Next I checked out reported betas from other sources. Yahoo Finance reports a beta of 0.35 for XOM (without specifying a time period, sampling method/frequency, or even reference index). MSN Money reports a beta of 0.43. This is a difference of about 23%. This could probably be accounted for by different time periods, etc. But what is most annoying is that these betas are presented without any such context.
I’ve only just started to explore this topic, but I think I’ve started to show that there is significant room for improvement in computing beta. And because beta underlies CAPM and modern portfolio theory, I think this is a big deal.
I’ve already got some more ideas for part III of this series, I just have to crunch some more numbers.
I’ve been blogging a lot recently to the lay investing community. I feel it is time to geek out a bit and exercise my inner quant (Quantitative analyst). I was in the shower thinking about beta and expected return. My mind came back to something that has bothered me for years… that beta is not rigorously defined. It occurred to me, strikingly, that if beta is poorly defined then so is alpha!
Now this is somewhat unsettling since CAPM is highly wedded to the concept of beta, and alpha [which some scholars believe is approximately 0]. Let me be clear, the sampling frequency and sampling period of beta are not consistently defined! One common definition of beta is based on monthly sampling over a period of one year. Another definition I’ve seen is monthly sampling over a three year period. I’ve also seen daily (trading days) sampling over periods of about 1-3 years. Investments 6th Edition (Bodie, Kane, and Marcus) even mentions the Merrill Lynch concept of adjusted beta (= 2/3 sample beta + 1/3).
These fast and loose definitions of beta are in sharp [no pun intended] contrast to the more rigorous definitions of maturity, duration, coupon rate, yield to maturity, etc. in the study of bonds.
The net effect of “beta sloppiness” is that a given given security, on the same day can get different betas from different investing houses even though they are all using the same data! To put it mildly, I think this is kind of a big deal. Beta, alpha, efficient frontiers, “risk free assets”, and CAPM are all interesting and useful concepts. I think that after 50+ years, it is finally time to put a bit more rigor into the fundamental building blocks of modern portfolio theory. I plan to crunch a few numbers and refine and test a few ideas, and I intent to help start doing just that (or at least help encourage others to) in the weeks ahead.
My employer has generously given our lab the day off to celebrate some of our recent successes, giving me a four-day weekend. What a precious gift — time. Time to relax and see a concert, time to clean the house, time to blog at balhiser.com. This gift off one day plus the Labor Day Weekend got me thinking about time and investing and how the two relate.
Time is a critical factor in investing. Interest and dividends are paid out over time. Inflation adds up over time, as does compounding. Annuities pay out over a lifetime. And, of course, pundits debate whether it is worth it to try to time the markets.
Time shows up in just about every financial formula, starting with the basic time value of money computations.
And, naturally, savings accumulates (or debts) over a period of time.
Putting this all together time is generally on your side as an investor. Especially if you are in your 20′s, 30′s, 40′s or even 50′s and investing in retirement. Having a 20-year plus time horizon is very likely to help smooth out the huge market ups and downs.
The biggest gotcha about time is inflation. Certainly inflation is my greatest concern. A dollar today is not worth what it was 10 years ago. I paid $1.00 for a Hershey Bar this week. 10 years ago I could have bought 2 for $1.oo. The other big gotcha about time is uncertainty. What will taxes be for IRA and 401(k) withdrawals? What will the economy look like? Will I have a job and will my pay keep up with inflation and taxes?
That said, I still believe time is on the side of long-term investors. Tax-deferred compounding of IRA and 401(k) assets is very helpful. The tax deferral of unrealized capital gains in taxable accounts is another investing boon. Finally the tax-free advantages of Roth IRA and 401K assets is another helpful option to help leverage the power of time.
Enough about that. A quick Crazy Ivan Account update: $21,750. Nice appreciation of late. Even my Barclays ADR (BCS) is up from the initial purchase price. Quite wild ride on the banks. The covered call I sold on SPY is in the red, but the 100 shares of SPY are in the black by more. That how covered calls generally work out — smoothing the volatility out of both the ups and the downs.
This week I bought back into the S&P500 Index via 100 shares of SPY. I didn’t intend to get out of SPY in the first place, but my call option was exercised right before the ex-dividend date. [No soup for you!]
SPY is my second favorite ETF after VTI. VTI is slightly better in terms of having an ultra low expense ratio of 0.07% and broader exposure to the whole US stock market. SPY with its not too shabby 0.1% expense ratio is comprised of 500 large companies from the S&P 500 Index.
I dabble with SPY in my play money “crazy Ivan” account because it supports my buy-write strategy. I buy 100 shares of SPY and then write (sell) a covered call option. SPY has a very robust and liquid options market around it. This tends to keep the spreads low.
Another convenient feature of SPY is that it was constructed to be priced at about one tenth of the S&P 500 Index. Simply put if the index is at 888 then SPY would cost about $88.80 per share.
So far the CIA has been weathering the market storm pretty well. It closed Friday at $20,427. It is currently comprised of BCS, SPY, and cash.
As an aside: Wow what a ride on BCS. I rode it down to $3.07 and back up to Friday’s close of $18.64. That’s a 6x run up and a six-bagger as Peter Lynch might say for those smart or lucky enough to have bought at the $3.07 low.
Twitter
Feed