Task: Download the most recent 5 years of monthly data for VTI, Proctor and Gamble (PG), Exxon Mobil (XOM), Apple (AAPL), Alcoa (AA), Century Aluminum (CENX) and the 3-month T-Bill (^IRX) using Yahoo Finance. VTI is a low-cost ETF that tracks the Wilshire 5000 index and is our proxy for the market return.
For the stocks and the ETF, enter the ticker and then on the left-side menu, click on historical prices.
Use the adjusted closing prices. For the 3-month T-Bill also use the adjusted close. Note that the TBill returns are annual rates that must be converted to monthly rates.
Q1. Calculate the monthly return for each stock or ETF. For stocks and the ETF, do this the normal way, but make sure you are moving the correct way through time! For the T-Bill, the rate you download is the annualized percentage return. Convert the annualized return you have downloaded to a monthly return as follows: if you download 1.69, then the monthly return would be (1+0.0169)^(1/12) - 1, or 0.001398.
Q2. Using the data for the stocks and the stock index, calculate the mean and standard deviation for each return series you have calculated, and the correlation coefficients between each combination of PG, XOM, AAPL, AA, CENX and VTI. (Use the formulas that are built into Excel).
Q3. Calculate the betas of PG, XOM, AAPL, AA and CENX and graph the results using the "Line fit plot" feature in Excel. Do this by regressing the monthly excess return of each stock on the excess return of the index.
Q4. What is the R2 of each regression? What does this mean?
Q5. Is the beta relationship significant? Explain how you know this.
Q6. Can you tell how large the firm specific risk is for each stock? Explain.
Q7. If VTI increases by 1%, what is your best guess for the increase in PG? Explain your answer.