Estimating Beta

1. Estimating Beta
   1. Using Yahoo!Finance:
      To access the beta data, type your ticker symbol (for the 10 stocks shown on “Stock Portfolio” attachment), and Beta is shown in firm summary. Bloomberg will automatically default to a 5-year, monthly beta estimate. Then compute the portfolio beta (weighted average of individual 10 stocks’ betas).
   1. Using Excel Regressions:
      Begin by downloading historical prices for S&P 500 (Ticker AGSPC on Yahoo!Finance) into Excel. Make sure that you request the same set of dates for both the index and your securities (09/13/21-10/06/21). Once you download the historical prices into Excel, compute the returns for index also.

To calculate beta, you need to to run the following regression model:

𝑅𝑖 = 𝑎 + 𝑏𝑅𝑚 + 𝑒

Where Ri = the daily return on stock i in your portfolio

a = the regression intercept

b = beta

Rm = the daily return on the S&P 500 index

e = the regression error component

This looks complicated, but it is actually a very simple procedure to do in Excel. First, you will need to turn on the regression analysis package in Excel.

1. Click on “Tools”, “Add-Ins”.
2. When the dialogue box appears, check the two boxes for the add-ins: labeled “Analysis Toolpak” and “Analysis Toolpak-VBA”, then click “OK”.
3. Click on “Tools”, “Data Analysis”
4. Another dialogue box will appear asking you which data analysis package you want to use. Scroll down and click on “Regression”. Click “OK”.
5. You will then need to fill in the regression parameters. Choose X and Y variable data ranges:
   1. X Variable – cell range for S&P 500 returns
   1. Y variable – cell range for your stock i’s returns
6. After entering the data ranges, click “OK” and your regression results should appear in a separate sheet in your Excel worksheet. Beta will be the numbers labeled the coefficient on the X variable.
7. Summarize the beta results of each regression separately.
8. Compute the equally weighted beta for your portfolio and conclude the relation between the risk and return for individual stocks and portfolio.