Explore BrainMass

Explore BrainMass

    CAPM portfoio

    Not what you're looking for? Search our solutions OR ask your own Custom question.

    This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

    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

    © BrainMass Inc. brainmass.com March 5, 2021, 1:42 am ad1c9bdddf

    Solution Summary

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