How to Do Portfolio Optimization Using Excel Solver

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 achieve diversification goals. Without some constraints, a portfolio becomes a no-brainer.If you are curious to know how you can optimize an investment Portfolio using Excel solver, then this article may come in handy for you. In this article, we discuss how you can complete the optimization of an investment Portfolio using an Excel solver with elaborate explanations.

In our example, we have involved a hypothetical credit union. A 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.

This article is part of my series: Excel Solver Guide (Easy and Step by Step).


Download Practice Workbook

Download this practice workbook below.


How to Optimize Portfolio in Excel

The aim of portfolio optimization is to identify the optimal portfolio (asset distribution) among those that are portfolios given a certain objective. In most cases, the objective is to maximize benefits, like predicted return, while minimizing liabilities, such as financial risk.

In order to achieve this, you should build a diversified portfolio that evenly allocates your money between different types of investments. The next step is to strike the right balance between taking risks and reaping rewards with your portfolio’s assets.

An “optimal portfolio” is the goal of portfolio optimization for investors. This indicates it’s producing the maximum feasible return for the level of risk you’re willing to take. (In another, less frequent use, this term can describe a portfolio that takes the fewest possible risks in order to achieve the desired rate of return.

One way to reduce exposure to potential losses is to invest in multiple types of assets. Securities, debt, and equity are all examples of financial asset classes. The potential for return and loss associated with certain assets can also vary widely.

To put it another way, the term “systemic risk,” which characterizes how an asset class reacts to market conditions as a whole, varies significantly among asset categories. Whenever the stock market rises, for instance, commodity and bond prices typically fall. When stock prices fall, however, commodity and bond prices tend to climb.

Since commodities and bonds are high-risk asset classes, most average investors stay away from them entirely. Other options include mutual funds and exchange-traded funds (ETFs) that invest in such assets.


How to Create an Optimal Portfolio in Excel

The problem statement is described below,

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

As we know all the necessary information related to the calculation, we can dive into optimizing the problem. But there is still another important to consider. We need to choose the solver method using which we are going to optimize the problem.

As our problem involves non-linear equations, we need to choose the method that can deal with the non-linearity of the problem. We, therefore, choose the GRG Nonlinear. There is another method to consider, which is known as the Simplex LP. But this method cannot work with non-linear problems. When comparing the two nonlinear solver algorithms, GRG Nonlinear provides the most time-efficient option. So it’s better to go with the GRG Nonlinear for this problem.

The Downside of GRG Nonlinear

  • The problem with this approach is that its output is highly sensitive to the initial variables and could not be the best possible answer everywhere. A solution that is not necessarily optimal globally may be found in the solver stops at the locally optimal value close to the initial conditions.
  • Also, the function needs to be smooth for GRG nonlinear algorithm to be able to find a decent solution. The IF, VLOOKUP, and ABS functions, among others, introduce discontinuities that will disrupt the operation of this algorithm.

2 Suitable Examples to Do Portfolio Optimization Using Excel Solver

The following figure shows a workbook set up for this problem. The problem has an investment in various sectors, with all the necessary formulas. Based on this problem, we will try to maximize or minimize the Total Yield or Auto Loans values.

portfolio optimization excel solver


Example 1: Optimizing Investment Portfolio Based on Total Yield

The credit union is going to invest $5 million in various sectors. How this amount will be allocated is subject to the following constraints.

Steps

  • Use the following steps to set up the Solver Parameters dialog box. This dialog box appears when you choose Data ➪ Analysis ➪ Solver.

Optimizing Investment Portfolio Based on Total Yield

Read More: Excel Solver – Introducing you with a simple example

  • Fill the Set Objective field with this value:$E$13.
  • Select the radio button for the Max option in To Control.

  • Select cell $D$6 to $D$10 to fill the field By Changing Variable Cells. This field will show then $D$6:$D$10.

  • Add constraints one by one. The constraints are: $D$11= $C$4 $D$6>= $D$7*3, $E$15>= 0.15, $F$9<= 0.25, $F$10>= 0.1. These constraints will be shown 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.
  • There will be another dialog box in which you need to select the result types.
  • Meaning you need to select Keep Solver Solution. Otherwise, the values will revert back to the original values.
  • Then from the right side of the dialog box, select all the options in the Reports.
  • Then click OK after this.

  • 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%.
  • The Auto Loans values also changed to 15%.
  • And this is how we got the highest optimization value of Total Yield considering all the constraints.

Maximized output of Total yield by optimizing the investment portfolio using solver

  • and this is how we complete the optimization of an investment portfolio using the Excel solver.

Example 2: Optimizing Investment Portfolio Based on Auto Loans

Now we have solved a problem related to optimizing the Investment amount while trying to maximize the Total Yield value. In the next example, we will try to minimize the Auto Loan in contrast to the Total Yield in the previous example.

Steps

  • Our main goal would be to try to minimize the Auto Loans.
  • For this, we will select cell E15. the location of the Auto Loan value as the Objective value.
  • Next, we will set Min in the To option.
  • Doing this will make sure that the solution will try to minimize the Auto Loan value while finding a solution.
  • Keep all the constraints the same as before.
  • Click OK after this.
  • Clicking OK will run the Solver.

Optimizing Investment Portfolio Based on Auto Loans

  • After clicking Solver, the solver will Run.
  • After the Solver had finished the calculation, we can see that the minimized Loan value based on all the other criteria is present.
  • And it also affected the Total Yield value in E13.

Minimized auto loans by optimizing the investment portfolio using solver


Conclusion

To sum it up, the issue of how we can complete the optimization of an investment Portfolio using an Excel solver is answered here by 2 different examples.

For this problem, a macro-enabled workbook is available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

1 Comment
  1. Reply
    Harrison Delfino May 2, 2017 at 8:47 AM

    I am using MarketXLS. It works great for me.

Leave a reply

ExcelDemy
Logo