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.

image

 

Sets

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

image

 

Parameters

Three parameters are used in this model and they are:

image

  • RequiredReturn – Required return rate for the future investment.

image

  • Mean – Mean return for each investment.

image

  • Cov – Covariant matrix based on the historic data.

image

 

Decisions

There are only two decisions and they are:

  • Allocation – Allocation for each investment in the portfolio.

image

  • Yield – Yield of the portfolio allocations.

image

 

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.

image

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

Constraints

We only find 3 simple constraints in this model.

image

  • 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.

image

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

imageimage

 

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!

About Andrew Chan
Andrew Chan is an Business Consultant who gives you accurate, consistent and timely information so that you can make better and faster decisions. He is an Associate of Society of Actuaries with over 20 years of IT experience. Apart from strong analytical skills and proven technical background, he was also a former system director at Manulife who had extensive project management experience. If you are looking for someone to gather, consolidate, validate, visualize and analyze data, look no further! Andrew can provide the most cost effective business analytics solution so that you can explore, optimize, predict and visualize your business. Don’t guess on any decision, no matter it is finance, operation, marketing or sales! Always ask for evidence!

3 Responses to Solver Foundation for Excel – Investment Portfolio Allocation

  1. Pingback: Investment Portfolio Allocation–Excel Solver « Better and Faster Decisions

  2. Ty says:

    Great stuff Andrew, do you know where i can find a copy of the Quadratic Portfolio.xlsx file?

    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: