bre44380_ch07_162-191.indd 191 09/02/15 04:11 PM
Chapter 7 Introduction to Risk and Return 191You can download data for questions 1 and 2 from finance.yahoo.com. Refer to the Useful
Spreadsheet Functions box near the end of Chapter 9 for information on Excel functions.- 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 mul-
tiplying 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. - 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 & Poor’s 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? - 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 mar-
ket index. Log on to http://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?
● ● ● ● ●FINANCE ON
THE WEB