Portfolio Management
Problem Set 2 - Fall 2025
Johnson Mo
Q1 - Just as in Problem Set 1, generate parameter inputs for your portfolio optimization
(note that the numbers for the US and EAFE should be identical to the correct solutions
from Problem Set 1):
a) Calculate monthly excess returns (in excess of the risk-free rate) for each index. Put
these numbers in the three blue-highlighted columns.
b) Calculate the monthly average and standard deviation for the three series of excess
returns. Also, calculate the pairwise correlations between these three series.
c) Calculate the corresponding annualized numbers. Also compute the annualized
Sharpe ratios assuming an annualized risk-free rate of 4.2% (given in cell Q19).
d) Add the current annualized risk-free rate of 4.2% to the annualized average excess
return to get an estimate of the current expected total return. Note that the volatilities
and correlations of the excess returns will be used as the parameter estimates for the
total returns.
Q2 - Consider all three series (US, EAFE, EM).
a) Calculate the portfolio weights for the minimum variance and maximum Sharpe ratio
portfolios. If you do this correctly the optimal CAL will appear in the graph.
b) Is the weight in EM positive or negative in the maximum Sharpe ratio portfolio? To
see whether this is correct, check the condition given in the lecture slides. Specifically,
compute the covariance and then the correlation between EM and the old 2-asset (US
and EAFE) maximum Sharpe ratio portfolio. (For your convenience, the weights in
this portfolio and the corresponding expected return, volatility and Sharpe ratio are
given in the range M28:R28.) Check if SR 𝐸𝑀 > 𝜌𝐸𝑀,𝑇𝑃 SR 𝑇𝑃 , where TP refers to the old
2-asset tangency portfolio.
c) Calculate the covariance between the minimum variance and maximum Sharpe ratio
portfolios from part a above. Use either the long (9-term) formula or the short (linear
algebra) version. Note that the covariance matrix (Σ) will automatically be calculated
for you from the numbers you compute in Q1 and appears in the range S16:U18.
d) Calculate the correlation between these 2 portfolios.
e) Calculate expected returns and standard deviations of the portfolios on the 3-risky
asset frontier by computing these numbers for combinations of the minimum variance
and maximum SR portfolios. (The weight in the minimum variance portfolio varies
from - 100% to 200%.) If you do this correctly the IOS will appear in the graph. Also
compute the Sharpe ratio for each portfolio.
f) Now assume short selling is prohibited, i.e., all portfolio weights must be nonnegative.
Calculate the constrained portfolio weights for the minimum variance and maximum
Sharpe ratio portfolios. (In order to ensure sufficient accuracy, make sure to change
the “Constraint Precision” to 0.00000001.) If you do this correctly the new constrained
optimal CAL will appear in the graph.
g) Finally, calculate the 2-asset IOS for the US and EM. (The weight in the US varies from
- 100% to 200%.) If you do this correctly the 2-asset IOS will appear in the graph. Is
the CAL from part f) tangent to this 2-asset IOS? Should it be?