Solver Foundation for Excel – Investment Portfolio Allocation

We can use Solver Foundation to solve many problems.  In my last blog, Solver Foundation for Excel – Supply Chain Planning Part 1, I demonstrated how we can maximize profit.  And I like to use another Microsoft example to demonstrate how we can minimize investment risk using Solver Foundation.

Let’s open worksheet Quadratic Portfolio.xlsx.  It is a very simple worksheet but you need some basic statistical background.  We have historical figures for certain investment vehicles; then we work out the cross correlation and convariants.

Our objective is to determine the portfolio mix that has the lowest investment risk.

 

Sets

We only have two sets for this model, i.e. I, and J.

 

Parameters

Three parameters are used in this model and they are:

• RequiredReturn – Required return rate for the future investment.

• Mean – Mean return for each investment.

• Cov – Covariant matrix based on the historic data.

 

Decisions

There are only two decisions and they are:

• Allocation – Allocation for each investment in the portfolio.

• Yield – Yield of the portfolio allocations.

 

Goals

There is only one simple goal – Given a required return rate for the future investment, the variance (or the risk of being way off the required return) is minimized.  Lower convariant means better diversification.

Sum[
        {iter3, I},
        Foreach[
          {iter4, J},
          Cov[iter3, iter4] * Allocation[iter3] * Allocation[iter4]
        ]
      ]

Constraints

We only find 3 simple constraints in this model.

• Constraint1 – The yield should not less than the required return rate. Yield >= RequiredReturn
• Constraint2 – The allocations for each investment should sum up to 100%. Sum[
        {iter1, I},
        Allocation[iter1]
      ] == 1
• Constraint3 – Yield is calculated from the summation of the products between each investment’s allocation and mean historic return rate.

  Plus[-Yield, Foreach[
        {iter2, I},
        Mean[iter2] * Allocation[iter2]
      ]] == 0

Model

Now, we have defined everything that is required for this model and we can review it.

Model[
  Parameters[
    Sets[Any],
    I,
    J
  ],
  Parameters[
    Reals[-Infinity, Infinity],
    Annotation[RequiredReturn, "description", "Required return rate for the future investment."],
    Annotation[Mean[I], "description", "Mean return for each investment."],
    Annotation[Cov[I, J], "description", "Covariant matrix based on the historic data."]
  ],
  Decisions[
    Reals[0, 1],
    Annotation[Allocation[I], "description", "Allocation for each investment in the portfolio."]
  ],
  Decisions[
    Reals[0, Infinity],
    Annotation[Yield, "description", "Yield of the portfolio allocations."]
  ],
  Constraints[
    Constraint1 -> Annotation[Annotation[Yield >= RequiredReturn, "description", "The yield should not less than the required return rate."], "enabled", False],
    Constraint2 -> Annotation[Annotation[Sum[
      {iter1, I},
      Allocation[iter1]
    ] == 1, "description", "The allocations for each investments should sum up to 100%."], "enabled", False],
    Constraint3 -> Annotation[Plus[-Yield, Foreach[
      {iter2, I},
      Mean[iter2] * Allocation[iter2]
    ]] == 0, "description", "Yield is calculated from the summation of the products between each investment’s allocation and mean historic return rate."]
  ],
  Goals[
    Minimize[
      Variance -> Annotation[Annotation[Sum[
        {iter3, I},
        Foreach[
          {iter4, J},
          Cov[iter3, iter4] * Allocation[iter3] * Allocation[iter4]
        ]
      ], "order", 0], "description", "Given a required return rate for the future investment, the variance (or the risk of being way off the required return) is minimized."]
    ]
  ]
]

Solve the Model

The model is ready and we can click the Solve button on Solver Foundation tag to start the process.

Below is the results and log that were output from the model:

 

Conclusion

We don’t have to be a mathematician to solve such sophisticated mathematical model.  I have no idea what algorithms Solver Foundation used but it delivered the results.  All we have to do is to define our problem, bind the input data from Excel, then Solver Foundation would do everything for us.  We can use it to solve many problems, no matter if it is finance, risk management, operation.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!