Supplemental Homework

1
Supplemental Homework

• Multi-asset Portfolio Optimization

2
Scenario Manager

• Tools-gtScenarios
• Summary
• Scenario Summary

3
Homework Instructions

• Download the data file (index data.xls) from the
  course website.
• Compute the historic monthly returns for each
  index
• Monthly return (assume continuous compounding)
  returnt ln(Pt / Pt-1).

4
Compute Historic Sample Statistics

• Compute the arithmetic average returns,
  population standard deviations, sample size, the
  variance-covariance matrix and the correlation
  matrix for T-Bond, SP 500 Index, SP 600 Index,
  Japan Index, and German Index.
• Excel functions AVERAGE(), STDEVP(), COUNT().
• For the correlation matrix, use Excels Data
  Analysis Command (located under Tools)

5
More on the Covariance Matrix

• For the covariance matrix, you may use any of the
  following approaches.
• Excel command Tools (Add-in), Data Analysis,
  Covariance. Complete the upper half of the
  covariance matrix.
• Following the directions in Benninga 8.3 and
  compute the covariance matrix using the excess
  returns approach.
• Create the user-defined function, VarCovar, in
  Benninga 8.4

6
Compute Portfolio Return and Standard Deviation

• Portfolio Expected Return E(rp) wTr
• Excel function SUMPRODUCT(weight,returns)
• Portfolio Variance s2p wTS w
• Excel function SUMPRODUCT(weight,MMULT(covariance
  ,weight))
• Portfolio Standard Deviation Excel function
  SQRT(Portfolio variance)

7
Reward-to-Risk (Sharpe) Ratio

• Assume that the risk-free rate is 0.25 per month
  (3 per year).
• Reward-to-standard deviation ratio
• (E(rp) rF)/ sp

8
Finding the Optimal Risky Portfolios

• Assign equal weights to the initial portfolio.
• Use Solver in Excel to find the optimal portfolio
  under each of the following cases.
• Save the weights for each case as a scenario.
• There is always at least one constraint sum of
  weight must equal to 1.
• Generate a scenario summary report containing the
  portfolio return and standard deviation and
  reward-to-standard deviation ratio for all cases.