Problem 2: Optimizing a Portfolio of Assets

One of the most common applications of optimization in finance involves selecting optimal portfolios. An optimal portfolio is one that maximizes overall return while satisfying constraints that represent the investors attitude toward risk.

The accompanying spreadsheet Assets.xlsx contains data on the annual percentage return on stocks, gold, and Treasury bills over the period from 1987 to 2007. A certain investor would like to determine the percentages of her portfolio to invest in these three asset categories. Her objective is to maximize the expected return on the portfolio while not investing less than 20% or more than 50% in each asset class. You may estimate the expected return on each asset with its average return over the period 1987-2007.

The risk index of an asset is measured by the average absolute percentage change over time. You may estimate the risk indices for stocks, gold, and Treasury bills over 1987-2007. The risk index for a portfolio is the weighted average of the risk indices of its component assets, where the weights are the percentages invested in each asset. For example, in the data we see that the risk index for stocks is 16.9. Our investor would like the risk index of her portfolio to equal 15.

(1) In the below space, write out the mathematical formulation for the above optimization problem. Clearly define the decision variables, objective function and all constraints (3 points).

(2) Implement the above model in Excel. Label your Workbook/Worksheet for this model as Problem 2 (3 points)

(3) Answer the following questions below:

What are the optimal percentages to invest in each asset class and what is the maximum return? (1 point)?

Stock______% Gold ______% Treasury Bills ______% Maximum return ______%

(4) Complete the table below to show how the investors optimal average return and the percentages invested in each asset change as she increases her acceptable risk index from 15 to 17 (with 0.5 as the increment). You can fill in the numbers in the table below or create the table in your Workbook with the Worksheet named Problem 2.4 (2 points)

Risk Index	Average Return	Change	Stock	Gold	Treasury Bills
15.0
15.5
16.0
16.5
17.0