You are an investment analyst at Fund ABC. An investor is interested in investing $100m into your fund. After discussions with the investor, you have agreed to build an investment portfolio out of three assets: Bond Index, Stock Index, and Crude Oil Futures.

Your portfolio manager has provided you with the expected annual returns of these three assets and their variance-covariance. See tab \Asset/ in the attached spreadsheet “Investment Portfolio model.xlsx”.

The investor’s risk tolerance is 10% p.a., i.e. the maximum volatility of annual returns should not exceed 10%. According to the current investment environment, you have observed the following requirements:

• The weight of your portfolio that is invested into Bond Index should not exceed 100%.

• The weight of your portfolio that is invested into Share Index should not exceed 100%.

• You cannot short Bond Index or Stock Index; but you can have short positions on Crude Oil Futures.

• You are not allowed to borrow additional money to invest into the portfolio.
Your portfolio manager has provided you with 1,000 portfolios. See tab \Portfolio/ in the attached spreadsheet “Investment Portfolio model.xlsx”. You have been asked to build the optimal portfolio (out of these 1,000 portfolios) that meets the investor’s objective.

(Note these portfolio weights are randomly generated, so results may change every time you open or refresh the spreadsheet. You can copy the 1,000 portfolio weights and paste them into hard-coded numbers.)

In addition, your portfolio manager asks you to propose a hedging strategy to further reduce the risk of this optimal portfolio. You have identified a potential hedging asset H. See Asset H’s expected return and its covariance with other three assets in tab \Asset/ in the attached spreadsheet “Investment Portfolio model.xlsx”.

Write an analysis report to the portfolio manager, explaining your proposed investment strategy. Your report should include the following:

• Determine and interpret the correlations between each pair of the three assets (Bond Index, Stock Index, and Crude Oil Futures).

• Show and explain the risk-reward chart of different portfolios built out of the three assets.

• Identify and interpret the efficient frontier in the risk-reward chart.

• Propose the optimal portfolio that meets the investor’s objective, including optimal weights into each asset, expected portfolio return and risk.

• Determine the 95%- and 99%-Value at Risk (in dollar amount) of the optimal portfolio. Explain them in words.

• Explain the rationale of hedging strategy and determine the optimal hedging strategy, including the optimal hedge ratio and effectiveness of this hedging strategy.