Purchase Solution

Standard Deviation, correlation coefficient

Not what you're looking for?

Ask Custom Question

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.

Attachments
Purchase this Solution
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.

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

Purchase this Solution
Free BrainMass Quizzes
Production and cost theory

Understanding production and cost phenomena will permit firms to make wise decisions concerning output volume.

Lean your Process

This quiz will help you understand the basic concepts of Lean.

Academic Reading and Writing: Critical Thinking

Importance of Critical Thinking

Cost Concepts: Analyzing Costs in Managerial Accounting

This quiz gives students the opportunity to assess their knowledge of cost concepts used in managerial accounting such as opportunity costs, marginal costs, relevant costs and the benefits and relationships that derive from them.

Learning Lean

This quiz will help you understand the basic concepts of Lean.

View More Free Quizzes
Related BrainMass Solutions

  • tocks

    Expected Standard Return Deviation A 8.00% 20.00% B 12.00% 30.00% The correlation coefficient between the returns of A and B is 0.3. Short selling is allowed. a.

  • Expected Return and Standard Deviation of Ebenezer Scrooge's Portfolio

    Correlation coefficient = 0 = Standard deviation = 14.88% Correlation coefficient = -0.5 = Standard deviation = 10.76% c.

  • Risk of a security - stand alone or as part of portfolio

    Security A has an expected rate of return of 6%, a standard deviation of returns of 30%, a correlation coefficient with the market of -0.25, and a beta coefficient of -0.5.

  • Stock valuation/standard deviation/beta

    of b= 50%, Sd(b) = Standard deviation of B = 20% We are given the covariance and we need the correlation coefficient.

  • Standard deviation of a portfolio

    You put the rest of your money in another stock that has an expected return of 6% and a standard deviation of 12%. The two stocks have a correlation coefficient of 0.55.

  • Calculating security risk

    501817 Calculating security risk Security A has an expected return of 7 percent, a standard deviation of expected returns of 35 percent, a correlation coefficient with the market of -0.3 and a beta coefficient of -0.5.

  • Portfolio Return and Standard Deviation

    Calculate the standard deviation of a portfolio that is composed of 35 percent A and 65 percent B when the correlation coefficient between the returns on A and B is -0.6. c.

  • What would the correlation coefficient, r, be

    If we have the value of standard deviation for Y and X then we can calculate the value of r by using the formula r =coeffient of X*standard deviation of X / standard deviation of Y = 1.95*sigmaX/SigmaY 4.

  • Expected return and Standard Deviation of a portfolio

    Stock Weights (x) Expected return ( r ) Standard deviation Correlation A 0.4 16% 10% 0 B 0.6 20% 10% Expected return = xA rA + xB rB= 18.40% =0.4*0.16+0.6*0.2 Standard deviation = 7.21% = square root of (0.4^2*0.1^2+0.6^

  • Risk Investments

    Company A has an expected rate of return of 15% and a standard deviation of 3% Company B has an expected rate of return of 10% and a standard deviation of 2% Company C has an expected rate of return of 25% and a standard deviation of 5% If the

View More Related Solutions