Message Font: Serif | Sans-Serif
 
No. of Recommendations: 14
VS,

Forgetting the holes that one can drive a truck through with Beta, why not calculate it yourself?

Beta is the regression of stock returns against market returns.

A fairly quick way to do this:

* From Yahoo! - get the last 60 month-end prices for the stock, and month-end prices for the S&P500.
Put into two columns in Excel.

* Calculate monthly stock returns: (P1 - P0)/P0. You'll have 59 data points.

* Calculate monthly market returns: (M1 - M0)/M0. Again, you'll have 59 data points.

* From Excel's 'Tools' select 'Data Analysis'. Then select the 'Regression'

* The pop-up window will ask you for an 'Input Y-Range'. Select the spreadsheet cells where
your Stock Returns are listed.

* The next line asks you for an 'Input X-Range'. Select the spreadsheet cells where your
Market Returns are listed.

* Then, click the radio button under output options for 'Output Range' Choose a blank cell
in your spreadsheet where you'd like the output to go.

Below, is the relevant data for Quality Systems (QSII), which you can cut, paste, and play with:

Month Stock Market Stock Market
End Price Level Return Return
--------------------------------------------------
Oct-00 $3.72 1,429.40
Nov-00 $3.97 1,314.95 0.0672 -0.0801
Dec-00 $3.88 1,320.28 -0.0239 0.0041
Jan-01 $4.72 1,366.01 0.2181 0.0346
Feb-01 $5.25 1,239.94 0.1123 -0.0923
Mar-01 $5.50 1,160.33 0.0476 -0.0642
Apr-01 $5.22 1,249.46 -0.0518 0.0768
May-01 $7.13 1,255.82 0.3672 0.0051
Jun-01 $6.50 1,224.42 -0.0884 -0.0250
Jul-01 $6.80 1,211.23 0.0462 -0.0108
Aug-01 $5.95 1,133.58 -0.1250 -0.0641
Sep-01 $5.23 1,040.94 -0.1218 -0.0817
Oct-01 $5.88 1,059.01 0.1244 0.0174
Nov-01 $7.43 1,139.45 0.2638 0.0760
Dec-01 $8.16 1,148.08 0.0983 0.0076
Jan-02 $8.44 1,130.20 0.0349 -0.0156
Feb-02 $8.40 1,106.73 -0.0047 -0.0208
Mar-02 $7.62 1,147.39 -0.0935 0.0367
Apr-02 $7.71 1,076.64 0.0125 -0.0617
May-02 $8.05 1,067.14 0.0441 -0.0088
Jun-02 $8.43 989.81 0.0466 -0.0725
Jul-02 $8.09 911.62 -0.0398 -0.0790
Aug-02 $8.69 916.07 0.0742 0.0049
Sep-02 $8.45 815.29 -0.0276 -0.1100
Oct-02 $10.12 885.77 0.1976 0.0864
Nov-02 $11.98 936.31 0.1833 0.0571
Dec-02 $10.05 879.82 -0.1608 -0.0603
Jan-03 $12.48 855.70 0.2413 -0.0274
Feb-03 $12.66 841.15 0.0148 -0.0170
Mar-03 $12.76 848.18 0.0075 0.0084
Apr-03 $15.19 916.92 0.1905 0.0810
May-03 $15.63 963.59 0.0290 0.0509
Jun-03 $13.63 974.51 -0.1277 0.0113
Jul-03 $17.80 990.31 0.3059 0.0162
Aug-03 $21.51 1,008.01 0.2084 0.0179
Sep-03 $20.10 995.97 -0.0656 -0.0119
Oct-03 $23.98 1,050.71 0.1930 0.0550
Nov-03 $21.40 1,058.20 -0.1076 0.0071
Dec-03 $22.30 1,111.92 0.0418 0.0508
Jan-04 $28.33 1,131.13 0.2705 0.0173
Feb-04 $22.53 1,144.94 -0.2048 0.0122
Mar-04 $22.72 1,126.21 0.0084 -0.0164
Apr-04 $21.50 1,107.30 -0.0535 -0.0168
May-04 $23.36 1,120.68 0.0865 0.0121
Jun-04 $24.55 1,140.84 0.0507 0.0180
Jul-04 $23.88 1,101.72 -0.0273 -0.0343
Aug-04 $23.82 1,104.24 -0.0023 0.0023
Sep-04 $25.26 1,114.58 0.0602 0.0094
Oct-04 $25.32 1,130.20 0.0026 0.0140
Nov-04 $30.71 1,173.82 0.2127 0.0386
Dec-04 $29.90 1,211.92 -0.0262 0.0325
Jan-05 $33.85 1,181.27 0.1319 -0.0253
Feb-05 $40.25 1,203.60 0.1892 0.0189
Mar-05 $42.34 1,180.59 0.0519 -0.0191
Apr-05 $47.58 1,156.85 0.1238 -0.0201
May-05 $60.13 1,191.50 0.2638 0.0300
Jun-05 $47.38 1,191.33 -0.2120 -0.0001
Jul-05 $57.92 1,234.18 0.2225 0.0360
Aug-05 $65.00 1,220.33 0.1222 -0.0112
Sep-05 $69.09 1,228.81 0.0629 0.0069

After you've run your regression, you'll end up with an output as per below. The important numbers are bolded for discussion:

SUMMARY OUTPUT

Regression Statistics
-----------------------------
Multiple R 0.373
R Square 0.139
Adjusted R Square 0.124
Standard Error 0.122
Observations 59
-----------------------------

ANOVA
----------------------------------------------------------------
df SS MS F Significance F
----------------------------------------------------------------
Regression 1 0.139 0.139 9.235 0.004
Residual 57 0.855 0.015
Total 58 0.994
----------------------------------------------------------------
Standard
Coefficients Error t-Stat P-value Lower 95% Upper 95%
-----------------------------------------------------------------------------
Intercept 0.060 0.016 3.791 0.000 0.029 0.092
X Variable 1 1.102 0.363 3.039 0.004 0.376 1.829
-----------------------------------------------------------------------------

Okay, first-off, your calculated beta is listed in the bottom table under Coefficients, in the row for X Variable 1.

So your calculated Beta is 1.102.

I should point out that this is where the 'free' betas available from Yahoo! or MSN end. They calculate it as per the above method (although they might use weekly returns, or 24 months, rather than 60 months, but they'll never tell you). However, what they don't tell you is the quality of the calculation. For example, I can tell you that the reported beta for Hidden Gem: Portfolio Recovery is statistically invalid. Yet, Yahoo! still lists it. If you use it for valuation purposes, you'll erroneously value the company too high.

So, you need to assess how statistically 'good' this beta is (and this should also illustrate the glaring problems inherent in beta). First, we ask, is our estimate of beta statistically different from zero? To do this, we run a 't-test', and you can see the regression program has considerately provided the calculated t-statistic for beta, which is 3.039.

Now, to have confidence that our estimate of beta is statistically different from zero, we need to compare the calculated t-stat for beta agains the expected statistical value. Rather than more calc's, a quick and dirty way, is just to make sure that the calculated t-stat is greater than 2. (Note, right beside the calculated t-stat, you'll see a P-Value of 0.004, which means the probability of our beta being a result of chance and chance alone is 0.4%, or, put another way, our calculated beta is good at the 99.6% significance level). Since all this is good, we move on.

At the top of the table, you see a value for R-Squared of 0.139. This means that 13.9% of the variability in stock returns is attributable to the variability in the underlying market. Meaning that 86.1% of the stock return variability is attributable to other factors (boy, bet you're feeling really good about beta right about now!)

Finally, back to the bottom table, you'll see under Lower 95% and Upper 95%, two values: 0.376 and 1.829. What these represent is a 95% confidence interval for our beta. In other words, we're 95% confident that the 'TRUE' value of beta is somewhere between 0.376 and 1.829, as opposed to our calculated estimate of beta of 1.102. Again, you should be thinking that beta is a rather sketchy measure, right about now.

Hope that helps. Sorry about the length.

Cheers,

Jim
Print the post  

Announcements

What was Your Dumbest Investment?
Share it with us -- and learn from others' stories of flubs.
When Life Gives You Lemons
We all have had hardships and made poor decisions. The important thing is how we respond and grow. Read the story of a Fool who started from nothing, and looks to gain everything.
Contact Us
Contact Customer Service and other Fool departments here.
Work for Fools?
Winner of the Washingtonian great places to work, and Glassdoor #1 Company to Work For 2015! Have access to all of TMF's online and email products for FREE, and be paid for your contributions to TMF! Click the link and start your Fool career.