This article is part of my series: Excel Solver Guide (Easy and Step by Step).
In this example, we’re going to demonstrate how to use Solver to get maximum return from an investment portfolio. A portfolio consists of several investments. Each investment category has its own yield. A portfolio manager can involve some constraints to reduce risk and to achieve diversification goals. Without some constraints, a portfolio becomes a no-brainer.
In our example, we have involved a hypothetical credit union. The credit union is a financial institution that takes deposits from members and invests them in loans to other members, bank CDs, and other types of investments. The credit union also distributes part of the return on these investments to the members in the form of dividends, or interest on their deposits.
This hypothetical credit union must follow some regulations regarding its investments, and the board of directors has imposed some other restrictions. These regulations and restrictions are this problem’s constraints. The following figure shows a workbook set up for this problem.
Optimizing the investment portfolio using Solver
The credit union is going to invest $5 million in various sectors. How this amount will be allocated is subject to the following constraints:
- The amount that the credit union will invest in new-car loans must be at least three times the amount that the credit union will invest in used-car loans. The reason is: used car loans are riskier investments. This constraint is represented as C5>=C6*3
- Car loans should make up at least 15% of the portfolio. This constraint is represented as D14>=.15
- Unsecured loans should make up no more than 25% of the portfolio. This constraint is represented as E8<=.25
- At least 10% of the portfolio should be in bank CDs. This constraint is represented as E9>=.10
- The total amount invested is $5,000,000.
- All investments should be positive or zero.
Use the following steps to set up the Solver Parameters dialog box. This dialog box appears when you choose Data ➪ Analysis ➪ Solver.
Fill Set Objective field with this value: $D$12.
Select the radio button for the Max option in To control.
Select cell $C$5 to $C$9 to fill the field By Changing Variable Cells. This field will show then $C$5:$C$9.
Add constraints one by one. The constraints are: $C$10= $B$1, $C$5>= $C$6*3, $D$14>= 0.15, $E$8<= 0.25, $E$9>= 0.1. These constraints will be showed in the Subject to the Constraints field. Click this article to know more about adding constraints.
Select the Make Unconstrained Variables Non-Negative check box.
Select GRG Nonlinear from the Select a Solving Method drop-down list.
Now click the Solve button. Click OK, you are done with your result.
We’ve entered 1,000,000 in the changing cells as the starting values. When you run Solver with these parameters, it produces the solution shown in the following figure which has a total yield of 9.25%.
Happy Excelling 🙂
Download Working File
Download the working file from the link below: