Optimization Solutions Using Microsoft Excel – Solver

 

There is a useful optimization tool available with Microsoft Excel. Many users are not aware of the existence or of the utility of this Excel Add-in feature, Solver (tips for setting up Solver are given at the end of this post).

Here is an example of a typical optimization problem. A financial advisor uses a questionnaire to determine the appropriate risk level for a client. Based on the client’s answers, age, and other factors, a risk level of 1 to 10 is assigned to the client. Each investment offered by the advisor has a potential rate of return and is assigned a risk score based on its volatility. The advisor’s firm may also have various rules such as: no more than 10% of the portfolio may be in one investment; no more than 30% of the portfolio may be in one sector, etc.

Now the question becomes what is the best mix of investments to maximize the potential return while meeting not exceeding the risk tolerance of the client and meeting the other investment guidelines of the advisor’s firm?

Maximizing the potential return is called the objective function of the problem. The total investment amount, the risk tolerance, and the investment guidelines are called the constraints of the problem. The amounts to place in each investment are called the decision variables. It is these decision variables that we are trying to determine to reach the optimal solution to the problem.

Without good analytical tools, a solution could be found by trial-and-error (not very efficient) or by setting up a matrix of linear equations and solving for an optimal solution (tedious and difficult). However, using Excel, one just needs to be able to express objective function and constraints as functions of the decision variables.

Here is a specific example:

Total amount to invest: $1,000,000
Client’s Risk Tolerance: 5

Available Investment Choices (with potential return and risk scores)

Investment   Symbol Potential Return             Risk Score

Cash                   C               1%                                    1
MuniBond        M              3%                                    2
TechStock1       TS1           10%                                  9
TechStock2       TS2           7%                                   8
Utility1               U1             5%                                   4
Utility2              U2             5%                                   6

(Realistically, there would be many more investment choices. However, the same method works for any number of choices.)

The objective function becomes:
Maximize Return = .01C + .03M + .10TS1 + .07TS2 + .05U1 + .05U2

The Total Investment Constraint is:

1C + 1M + 1TS1 + 1TS2 + 1U1 + 1U2 = 1,000,000

The Risk Constraint is:
1C + 2M + 9TS1 + 8TS2 + 4U1 + 6U1 <= 5,000,000 (the limit is the client’s risk tolerance x total inv)

The constraints for the maximum allowed in each investment are (we only have a choice of 6 investments, so use a max of 30% for any one investment – this is higher than normal investment advice, but that is when you have a much larger selection of possible investments).

C <= 300,000 (30% of 1,000,000)
M<= 300,000
TS1 <= 300,000
TS2 <= 300,000
U1 <= 300,000
U2 <= 300,000

The constraints for the maximum invested in each sector are (this example uses 50%. Again, this is higher than normal conventions, but we only have four sectors in our example – cash, bonds, tech, utilities).

C <= 500,000 (50% of 1,000,000)
M <= 500,000
TS1+TS2 < 500,000
U1 + U2 < 500,000

The complete formulation of the problem now has one objective function, six decision variables, and twelve constraints.

In Excel, the problem is set up as shown below:

To complete the solution of the problem, pull up the Solver dialogue box. The Total Potential Return in cell H13 is the objective function. The functions for the constraints are given in column G and the limits of the constraints are given in column H. Each constraint uses the sumproduct(array1,array2) function, where array 1 is the group of cells A10:F10 (the decision variables) and array 2 is group of constraint coefficients in the rows 18 through 32. The completed dialogue box for Solver is:

Clicking the Solve box and the spreadsheet is updated with the optimal solution:

The Solver solution says to invest $42,587 in Cash, $300,000 in Muni Bond, $300,00 in Tech Stock 1, $57,143 in Tech Stock 2, $300,000 in Utility 1 and $0 in Utility 2. The potential annual return is projected to be $58,429.

Viewing column G, we see all the constraints are met.

Once a user has completed a modest size problem as the above, it is just a matter of adding additional decision variables (columns) and constraints (rows) to solve much larger problems.  

Solver Set-up:
In Excel, from the top ribbon click on Data then look for Solver. If it is not there, go to File and click on Options. Select Add-ins then click on Go. In the dialogue box, click Solver then OK. Now, when you return to Data on the ribbon, Solver should be there. For your version of Excel, the steps may be different, so review this link from Frontline for your version of Excel: Search | solver.