You can calculate a historical beta for your stock portfolio to estimate future returns, predict trends, and calculate risk on your stocks.
- Step 1: Chart the data for the returns by selecting both return columns, and use your spreadsheet program's chart function to create a scatter chart, also known as an "x y" chart. Label the Y-axis "Stock Returns" and the X-axis "Benchmark Returns."
- Step 2: Use your spreadsheet function to add a linear trendline in your chart, and then to display the equation on the chart and display R-squared value. The coefficient result for the X variable is the beta.
- FACT: As of 2010, the New York Stock Exchange's largest volume day on record was Friday, June 22, 2007, with nearly 5.2 billion shares traded.
- Step 3: Repeat the same process, subtracting and dividing the closing prices for the stock to determine the stock's returns, and enter the returns in the next column to the right.
- TIP: Use the formula and copy functions in a spreadsheet program to save time.
- Step 4: Determine which stock you will be valuing and collect data on the stock's closing price to use as the dependent variable. Enter the monthly returns for the stock in the next column of your spreadsheet. Use your program's formula function to perform calculations and enter the values.
- TIP: Obtain stock data online at Yahoo! Finance, Bloomberg, or the Wall Street Journal websites.
- Step 5: Calculate the return for the first month by subtracting your index level for the first month from the index level for the second month. Then divide the difference by the index level for the first month. The quotient is the return. Skip a column in your spreadsheet and enter the returns for each month in the next column.
- Step 6: Determine a benchmark to use for your first data point. Common benchmark indexes include the S & P 500, the Dow composite, NASDAQ, and the Major Market Index. Collect data for the closing price of the benchmark and enter the data in the left column of a spreadsheet.