The beta of a stock is a measure of the stock's price volatility in relation to price changes in the overall market. The generally accepted convention calculates a stock's beta in relation to the returns of the S&P 500 stock index. The stock index has a beta of one, so a stock with a beta greater than one is more volatile than the overall market as indicated by the S&P 500, and a beta less than one indicates a stock with lower volatility. If you calculate your own beta numbers you can also use the S&P 500 as a benchmark or another index you believe is more appropriate for your purposes.
1. Enter price data for your selected stock and the selected market index into a new spreadsheet of a spreadsheet program such as Microsoft Excel or OpenOffice Calc. You need the opening and closing prices for a period of time. For example you could set up columns for the weekly opening and closing prices for one year, giving you 52 data sets for both the individual stock and the stock index.
2. Calculate the return for each data period in two additional columns on the spreadsheet. The periodic return is the closing price minus the opening price for the period. If, for example, you are using weekly price data, you will have two columns with the weekly returns for the stock and the index.
3. Calculate the covariance of the calculated stock returns and the index returns using the covariance function of the spreadsheet. Covariance is COVAR in the functions list. The function allows you to select two sets of data and calculates the covariance between the two sets. Use your stock return column data as the first set and the index return column data as the second set. The covariance calculation can be set up in any spreadsheet cell outside of your price and return columns.
4. Calculate the variance of the stock index return column using the variance or VAR spreadsheet function. The variance function works in a similar manner to the covariance function but only uses one set of data, in this case the stock index data. Set up the variance calculation in a cell next to or below the covariance calculation.
5. Calculate the stock beta by dividing the results from the covariance cell by the results from the variance cell. A new cell should hold the calculation and results of the division operation.
- Set up your beta calculation spreadsheet with just a few sets of price data -- three or four is sufficient -- to make sure the calculations all work and the results make sense.
- John Foxx/Stockbyte/Getty Images