# CAPM portfoio

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.

a. 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.

b. 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).

c. 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.

d. What is the R2 of each regression? What does this mean?

e. Is the beta relationship significant? Explain how you know this.

f. Can you tell how large the firm specific risk is for each stock? Explain.

g. If VTI increases by 1%, what is your best guess for the increase in PG? Explain your answer

https://brainmass.com/business/capital-asset-pricing-model/capm-portfoio-612568

#### Solution Summary

The expert determines how to calculate the excess return and practically use CAPM method.