- 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.
previous post
next post