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.