Beta is a figure used to judge the risk of a particular stock by comparing its price-volatility to that of a chosen benchmark. Beta values range from 0 to 1, with a value of 1 indicating the highest degree of correlation between the stock and the benchmark. R-Squared is measure that reflects the reliability of a given Beta figure, and should be included in every calculation of a stock's Beta.
Preparation
Choose a benchmark index and a time horizon for your calculations. Use benchmark indexes and time horizons that suit your chosen stock and trading strategies. Your chosen index should generally represent the economy of the nation and sector in which your stock trades. Although the S&P 500 is commonly used for Beta calculations, it will not be the best index for stocks trading outside of the US or listed on other indexes.
Obtain historical price quotes for your stock. Go to financial news websites that provide stock quotes, financial statements, and ratio analysis on publicly traded companies to get this information.
Format the historical price list in your spreadsheet program. If you have downloaded the information, copy the dates and closing price quotes into a new spreadsheet. If you have not downloaded the information, copy and paste the dates and closing prices from the website into a new spreadsheet.
Repeat Steps 2 and 3 for your chosen benchmark index. At this point you should have two rather long tables, each containing dates and their corresponding closing price quotes. Arrange the dates with the most recent on top.
Calculation
Calculate daily percentage changes for your stock and index. Percentage changes are calculated as follows:
(Current Value - Previous Value) / Previous Value
Create a formula for this calculation in another column of your spreadsheet, and copy the formula down as many rows as necessary to cover all dates. You should now have a total of six columns of data. In addition to the dates and closing prices, you should have corresponding percentage changes for each date.
Calculate the stock’s Beta by dividing the covariance of all of percentage change values for both the stock and the index by the variance of the percentage change values for just the stock. In Excel, the formula will appear as follows, assuming that “x1:x2” is the range of cells that contains the percentage changes for your stock, and “y1y2” represents the same for the index:
COVAR(x1:x2,y1:y2)/VAR(y1:y2)
The output from this formula will be your stock's Beta.
Calculate R-Squared. R-Squared is a complicated calculation that your spreadsheet program should be programmed to handle automatically. In Excel, the formula will appear as:
RSQ(y1:y2,x1:x2)
An R-Square value closer to 100 indicates a highly reliable Beta figure, while a value under 70 indicates a generally unreliable Beta.
Tips
Remember that Beta is a backwards-looking measure of risk, and must be measured against fundamental analysis of a company’s future prospects for the most accurate analysis. The backwards-focused nature of Beta also makes it an unreliable figure for new stocks with only a short period of historical information available.
References
Tips
- Remember that Beta is a backwards-looking measure of risk, and must be measured against fundamental analysis of a company's future prospects for the most accurate analysis. The backwards-focused nature of Beta also makes it an unreliable figure for new stocks with only a short period of historical information available.
Writer Bio
David Ingram has written for multiple publications since 2009, including "The Houston Chronicle" and online at Business.com. As a small-business owner, Ingram regularly confronts modern issues in management, marketing, finance and business law. He has earned a Bachelor of Arts in management from Walsh University.