A stock’s beta is a measurement of the stock’s volatility compared to the market or index with which you are comparing it. Investors use the comparison to try to gauge how risky the stock might be. For purposes of comparison, the market is assigned a beta value of one. When a stock’s beta is analyzed, its value is an indicator of whether the stock or the market is more volatile. A low beta is considered to represent a less risky stock than one with a high beta.
Assemble Historical Data
Open a new workbook in Excel. Click on the Excel icon or choose the program from your “Start” menu to launch Excel. This should open a new workbook.
Locate the data for your benchmark. The benchmark is the data with which you will compare the individual stock. For stocks traded in the U.S., the Standard and Poors 500 index is typically a suitable benchmark. You can find the information on a number of sites that offer financial news.
Download the data from the website, copy and paste the data on the website or enter it manually into your Excel worksheet. Data should be in two columns, one containing the date and the other containing the closing price.
Sort the data by date. As a rule, you want to sort in ascending order so that the most recent date is at the top of the column. To sort your data, select the data you have just entered, click “Sort” and then choose the icon or dialog box, depending on which version of Excel you have.
Locate the data for the stock you want to analyze. You can typically find the information for the individual stock at the same site where you found your benchmark. Enter the date and closing price for the stock in the next two columns and repeat the “Sort” operation for just these two columns.
Verify that you are comparing the same time periods. The dates in your benchmark’s date column should correspond to the dates in your stock’s date column. Make any adjustments needed to ensure you are comparing information for the same dates.
Find the periodic changes in percentage for both the benchmark and the stock. Percentage changes are represented by the formula:
(Current Value minus Previous Value) divided by Previous Value
Therefore, if your benchmark data is in column B with the most recent data on line 3, your formula would be:
\=(B3 – B4)/B4
Copy and paste that formula into the rest of the cells in that column until you reach the end of your data.
Create a formula to determine the percentage changes for your stock. Moving into the sixth column, use the same basic formula as you used for your benchmark, but replace the cell numbers to correspond to your stock data. If, as in the example for your benchmark, your most recent data is on line 3 and your stock’s closing prices are in column D, the formula would be:
Copy and paste the formula into the remaining cells in column D until you reach the end of your data.
Enter the formula to calculate the beta. Using the data supplied in earlier examples, your percentage changes for your benchmark should be in column E and your percentage changes for your stock should be in column F. Assume you have 12 entries for each and the data is on lines 3 through 14. Your formula would be:
COVAR(F3:F14, E3:E14) / VAR(E3:E14)
You can use Excel's built-in "Slope" function to produce a graph estimating beta.
Remember that calculations for beta involve regressive analysis. As such, it is far less reliable for new companies without a lengthy historical record.