Beta measures the volatility of a company security against a benchmark, such as the S&P 500. A beta of 1 means the security price and the market fluctuate at the same rate. A beta higher than 1 indicates that the stock is more volatile than the overall market, whereas a beta lower than 1 means the stock is less volatile. You can calculate the beta of a security in Microsoft Excel by using the SLOPE function.
Step 1
Open a new worksheet in Excel. Enter historical data for the stock and the benchmark in two columns. As an example, enter the stock data in cells A1 through A10 and the corresponding benchmark data in cells C1 through C10.
Video of the Day
Step 2
Click on the empty cell B2 and calculate the percentage change of the stock price data by typing in the formula "=((A2-A1)/A2)*100". Move your mouse to the lower right corner of cell B2 until you see a "+" sign. Drag the mouse down to cell B10 to copy the formula to the other cells.
Step 3
Repeat Step 2 to calculate the percentage change for the benchmark data. This time, type the formula "=((C2-C1)/C2)*100" in cell D2 and copy it down the D column to cell D10.
Step 4
Calculate beta using the SLOPE function to compare the range of the percentage changes in both the stock price and the benchmark index. For the example above, click on an empty cell and type the formula "=SLOPE(C2:C10,D2:D10)". The beta result will appear in the cell you selected.
Video of the Day