You can download data for questions 1 and 2 from finance.yahoo.com. Refer to the Useful Spreadsheet Functionsbox near the end of Chapter 9 for information on Excel functions.
1. Download to a spreadsheet the last three years of monthly adjusted stock prices for Coca-Cola (KO), Citigroup (C),
and Pfizer (PFE).
a. Calculate the monthly returns.
b. Calculate the monthly standard deviation of those returns (see Section 7-2). Use the Excel function STDEVP to check your answer. Find the annualized standard deviation by multiplying by the square root of 12.
c. Use the Excel function CORREL to calculate the correlation coefficient between the monthly returns for each pair of stocks. Which pair provides the greatest gain from diversification?
d. Calculate the standard deviation of returns for a portfolio with equal investments in the three stocks.
2. Download to a spreadsheet the last five years of monthly adjusted stock prices for each of the companies in Table
7.5 and for the Standard & Poors Composite Index (S&P 500).
a. Calculate the monthly returns.
b. Calculate beta for each stock using the Excel function SLOPE, where the y range refers to the stock return (the
dependent variable) and the x range is the market return (the independent variable).
c. How have the betas changed from those reported in Table 7.5?
3. A large mutual fund group such as Fidelity offers a variety of funds. They include sector funds that specialize in particular industries and index funds that simply invest in the market index. Log on to www.fidelity.com and find first the standard deviation of returns on the Fidelity Spartan 500 Index Fund, which replicates the S&P 500. Now find the standard deviations for different sector funds. Are they larger or smaller than the figure for the index fund?
How do you interpret your findings??
