Stock price risk can be measured by a financial ratio called the beta, which compares the rate of return of the investment to the rest of the market. The standard market for equities comparison is the S&P 500, and it is assigned a beta of one. Stocks with a higher volatility than the market have a beta greater than one and those with less volatility have a beta less than one. Volatility is a key measure for options and day traders. Websites like Reuters.com and Yahoo! Finance, finance.yahoo.com, list betas online, but you can calculate your own betas using Microsoft Excel.

- Skill level:
- Easy

### Other People Are Reading

### Things you need

- Microsoft Excel

Show More

## Instructions

- 1
Decide whether you are going to use the capital asset pricing model (CAPM) or linear regression model for your calculation. The CAPM adds the time value of money into the risk equation. If you select the CAPM you need the required rate of return, the risk-free rate and the market-risk premium to complete the equation. If you choose linear regression, you need the rate of return of the investment and the rate of return of the asset class or market.

- 2
Decide whether you are calculating beta for a short or long-term investment and find stock price data and market data. The time period can be daily, weekly or monthly depending on your goals.

- 3
Decide what market you want to use as a benchmark for comparison. For example, if you are measuring the beta for fixed income securities, you would choose Treasury Bills as your index, because it is more representative that the S&P 500. When you calculate beta on Excel, you can display the r-squared equation. R-squared is the movement of your stock compared to the market. If r-squared is higher than the beta that means that your beta is correct because the market is representative of the security you are measuring.

- 4
Do the math for a linear regression. Use column one for the date, use column two for the stock index, and use column three for the stock. Calculate the return in each column by subtracting the amount in the second date from the amount in the first, and then dividing by the amount in the first date. Use the copy function to create a series of returns for each column. Select the data you want to chart and the chart icon. Select the "XY" scatter and click "Next" until you can add the titles for each axis. Remove the legend, and click "Next" until you can place the chart on a separate worksheet. Click "Chart" on the tool bar and add a linear trend line. Go to the options menu. Click to display the equations on the chart for the beta and the r-squared.

- 5
Download a historical volatility calculator from the Internet from a website like optiontradingtips.com. This tool uses the standard deviation method to tell you how rapid the price changes in a stock have been over time. Use Excel and find a number that can be compared against the beta to given an additional measurement of volatility.

#### Tips and warnings

- Many sites have betas based on the S&P; that you can confidently use to assess risk.
- If you have a foreign stock, you will want to calculate beta based on the foreign market, not the S&P.; Choose the asset class that is representative of the stock.
- Remember a beta of one means the stock moves with the market and that can be up as well as down.
- The beta is a useful measurement of short term risk where price volatility is important. For the long-term investor, the beta's historical data means that it cannot be a predictor of future returns.