Consider the 84 monthly returns on market index ETFs for Europe and China that are provided on Moodle
in the first tab of the Excel spreadsheet for this assignment.
(a.) Using the AVERAGE and STDEV commands in Excel, what are the historical values of the average
rate of return on each ETF and the standard deviation of the returns on each ETF? Which ETF has the
highest average return? Which ETF has the lowest standard deviation?
(b.) Using the COVAR.S command in Excel, what is the historical covariance of the Europe and China
(c.) Using the historical values from parts (a) and (b) as estimates of expected returns, standard deviations,
and covariances, what is the expected return and standard deviation of a ‘blended’ portfolio that had
proportions of 1/2 in Europe and 1/2 in China?
(d.) Consider evaluating just three portfolios for investment – putting all of your money in either only the
Europe ETF or only the China ETF or the ‘blended’ portfolio from part (c). Assume that you have constant
risk tolerance with tau (τ) equal to .80. What is the certainty equivalent return of each portfolio? Which one
of these three portfolios should you select (i.e., which portfolio is optimal for you)?
(e.) Now assume that the riskfree borrowing and lending rate is .00020, i.e., .02% (remember, these are
monthly rates). Calculate the slope of the line that goes from the riskfree rate to the ‘blended’ portfolio
described in part (c). What is the equation of the line?
(f.) Again assume that your risk tolerance tau (τ) is equal to .80. Using the line in part (e), what proportion
of your investable funds would you invest in each ETF and in the riskfree rate? Is riskfree borrowing or
(g.) What is the expected return, standard deviation, and certainty equivalent return of the optimal portfolio
that you identified in part (f)? Is it more or less attractive than the optimal portfolio identified in part (d)?
Consider the 48 monthly data returns in the second tab of the Excel spreadsheet for this assignment
regarding the Magellan Fund (a large mutual fund actively managed by Fidelity, denoted ‘rp’), the return
on a market index (denoted ‘rm’) and the riskfree rate (denoted ‘rf’) to answer the following, rounding your
answers to four decimal places (do not express your answers in percentages).
NOTE: See p. 3 of the Lec14 Addendum for directions on how to use Excel to perform regression analysis.
The only inputs needed are for “Input Y Range” (for #2 below it would be the location of the values of rpt
– rft), “Input X Range” (for # 2 below it would be the location of the values of rmt – rft; and “Output Range”
(enter the upper left cell location where the output will appear, e.g., B60 means the output will appear in as
many columns as needed starting with B and as many rows as needed starting with row 60). Excel will
default to a confidence level of 95%, and thus should not be changed.
1. Calculate the unadjusted measure of portfolio performance, arp – arm, where arp denotes the average
return on the actively managed Magellan Fund (whose returns and excess returns are denoted ‘rp’
and ‘rp – rf’, respectively, in the spreadsheet) and arm denotes the average return on a broad-based
market index (whose returns and excess returns are denoted ‘rm’ and ‘rm – rf’, respectively, in the
spreadsheet). By this measure did the Magellan Fund outperform or underperform the index?
2. Run the regression equation rpt – rft = p + (rmt – rft)p + ept for the Magellan Fund. Based on alpha,
did the Magellan Fund ‘beat the market’? (In interpreting ‘alpha’, base your answer on its sign
(positive or negative), meaning that you do not need to indicate if the coefficient was statistically
significant at the .05 level.
3. Calculate the Sharpe Ratio for the Magellan Fund. Based on this measure, did it ‘beat the market’?
Be sure to indicate the value of the ‘benchmark’ that you used as the basis for your answer.
(Remember to use STDEV Excel command when calculating standard deviation.)
4. Calculate the Treynor Ratio for the Magellan Fund (note the Beta for the Magellan Fund can be
found in the output for #2 above). Based on this measure, did it ‘beat the market’? Be sure to
indicate the value of the ‘benchmark’ that you used as the basis for your answer. (Note that the
Magellan Fund beta can be observed from the regression used earlier in answering #2.)