A stock's beta is a measure that lets you know how much a stock moves relative to a benchmark such as the Standard & Poor's 500 index. The benchmark, a well-diversified basket of stocks, represents the stock market as a whole. Finding the beta of a stock lets you know what its tendencies are, and how it has acted historically relative to major stock market indexes. This helps determine whether that stock is too conservative or too risky for your portfolio.
Step 1
Gather historic daily closing prices for the S&P 500 and paste or download them into a spreadsheet. Place the dates in Column A and the closing prices in Column B. Historical closing prices are available from various sources online. Use at least 50 historical closing prices, preferably 100 or more.
Step 2
Gather historic daily closing prices for a specific stock and paste or download them into your spreadsheet. Place the dates in Column C and the closing prices in Column D.
Use the same number of closing prices for the stock as you used for the S&P 500. For example, if you pasted 100 dates and closing prices for the index, you will also have 100 dates and closing prices for your stock.
Step 3
Calculate the daily percentage change of the index and the stock in Column E and F respectively. Daily percentage change is calculated by first taking the Current Value minus Prior Value then dividing it by the Prior Value.
To set up a formula in your spreadsheet, in cell E2 type "=(B2-B1)/B1". Do the same in Column F, using values from Column D, to calculate the stock's daily percentage changes.
Step 4
Copy the formulas all the way down to the bottom of your historic closing prices for both the the index and the stock. The spreadsheet will automatically calculate the daily percentage changes. Manually calculate one of the daily percentage changes to make sure the formula was typed in and copied correctly.
Step 5
Use the spreadsheet to calculate the covariance of the daily percentage changes between the index and the stock.
For example, if you have 100 historic closing prices, in an unused cell type "=COVAR(E1:E100,F1:F100)" and hit enter.
Step 6
Calculate the variance of the index's percentage moves in an unused spreadsheet cell. Variance is calculated by typing the formula "=Var(E1:E100)" assuming you have 100 historic prices.
Step 7
Determine the stock's beta. Divide the covariance number by the variance figure of the index. The result is the stock's beta. Beta is therefore the covariance of stock versus index returns, divided by the variance of the index.
Step 8
Interpret the stock's beta. Beta values range from negative to positive, with a negative beta indicating that the stock moves in the opposite direction of the benchmark index. Stocks with a positive beta move in the same direction as the index. The index always has a beta of 1, and betas higher or lower than 1 reflect higher or lower volatility.
A stock with a beta of 2 moves in the same direction as the benchmark, but it has twice the volatility. A stock with a beta of negative 0.5 moves in the opposite direction of the index, but it is half as volatile.
References
Resources
Tips
- R-squared is a statistical measure that determines the reliability of a stock's beta. An R-squared reading close to 1 indicates the beta is reliable. An R-squared of 0.8 or less means the beta might not accurately represent the stock's performance.
- In your spreadsheet, calculate R-squared using the formula RSQ(E1:E100,F1:F100)
- Use formulas as much as possible; if you add data or make changes, formulas are easily copied and quicker than calculating manually.
Warnings
- Beta is based on historic price data, and it does not necessarily predict how a stock will perform or move in the future.
Writer Bio
Cory Mitchell has been a writer since 2007. His articles have been published by "Stock and Commodities" magazine and Forbes Digital. He is a Chartered Market Technician and a member of the Market Technicians Association and the Canadian Society of Technical Analysts. Mitchell holds a Bachelor of Management in finance from the University of Lethbridge.