Explore BrainMass
Share

Explore BrainMass

    Standard Deviation, correlation coefficient

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

    Please see attached documents.

    Use the attached spreadsheets for Coca-Cola, Citigroup, and Pfizer.

    a) Calculate the annual standard deviation of returns for each company, using the most recent three years of monthly returns. Use the Excel function STDEV. Multiply by the square root of 12 to convert to annual units.

    b) Use the Excel function CORREL to calculate the correlation coefficient between the monthly returns for each pair of stocks.

    c) Calculate the standard deviation of returns for a portfolio with equal investments in each of the three stocks.

    © BrainMass Inc. brainmass.com October 9, 2019, 10:47 pm ad1c9bdddf
    https://brainmass.com/business/business-management/standard-deviation-correlation-coefficient-232159

    Attachments

    Solution Preview

    Please see attached file:

    Use the attached spreadsheets for Coca-Cola, Citigroup, and Pfizer.
    a) Calculate the annual standard deviation of returns for each company, using the most recent three years of monthly returns. Use the Excel function STDEV. Multiply by the square root of 12 to convert to annual units.

    Standard deviation of Monthly return using STDEV Annual standard deviation of returns

    Coca-Cola 4.7824% 16.57% =4.7824%x square root of 12
    Citigroup 13.9445% 48.31% =13.9445%x square root of 12
    Pfizer 5.8768% 20.36% =5.8768%x square root of 12

    b) Use the Excel function CORREL to calculate the correlation coefficient between the monthly returns for each pair of stocks.

    Correlation Coefficient using CORREL

    Coca-Cola and Citigroup 0.3756
    Coca-Cola and Pfizer 0.2389
    Citigroup and Pfizer 0.4986

    Correlation matrix

    Coca-Cola Citigroup Pfizer
    Coca-Cola 1 0.3756 0.2389
    Citigroup 0.3756 1 0.4986
    Pfizer 0.2389 0.4986 1

    c) Calculate the standard deviation of returns for a portfolio with equal investments in each of the three stocks.

    There are two methods to calculate the ...

    Solution Summary

    Calculates Standard Deviation of return on three stocks, correlation coefficient between the montly returns for each pair of stocks, and Standard Deviation of a portfolio composed of these three stocks using excel spreadsheet functions STDEV, CORREL.

    $2.19