Paper , Order, or Assignment Requirements

Part I

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

ETFs?

(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

lending involved?

(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)?

2

Part II

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