9. Download the “Monthly Adjusted Prices” spreadsheets for Coca-Cola, Citigroup, and
Pfizer from the Standard & Poor’s Market Insight Web site (www.mhhe.com/
edumarketinsight).
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.
Coca-Cola: 12.64464254 * 3.464101615 = 43.80232663 annual units
Citigroup: 24.2203633 * 3.464101615 = 83.90179961 annual units
Pfizer: 6.737919421 * 3.464101615 = 23.34083755 annual units
b. Use the Excel function CORREL to calculate the correlation coefficient between the
monthly returns for each pair of stocks.
Coca-Cola: 0.375287597
Citigroup: 0.618429618
Pfizer: 0.285834588
c. Calculate the standard deviation of returns for a portfolio with equal investments in
each of the three stocks.
This is where I am stuck. Should I answer this portion by assuming say, $100 investment in each company? Any suggestions would be greatly appreciated.
Flora
Pfizer from the Standard & Poor’s Market Insight Web site (www.mhhe.com/
edumarketinsight).
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.
Coca-Cola: 12.64464254 * 3.464101615 = 43.80232663 annual units
Citigroup: 24.2203633 * 3.464101615 = 83.90179961 annual units
Pfizer: 6.737919421 * 3.464101615 = 23.34083755 annual units
b. Use the Excel function CORREL to calculate the correlation coefficient between the
monthly returns for each pair of stocks.
Coca-Cola: 0.375287597
Citigroup: 0.618429618
Pfizer: 0.285834588
c. Calculate the standard deviation of returns for a portfolio with equal investments in
each of the three stocks.
This is where I am stuck. Should I answer this portion by assuming say, $100 investment in each company? Any suggestions would be greatly appreciated.
Flora