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