Risk Management: How to optimize your portfolio (Part 2)

Simple steps to use MS Excel in optimizing your investment portfolio!

In our previous article, we learned the key mechanisms of portfolio optimization. To sum it up, portfolio optimization is where you adjust the weight of each stock in your portfolio to get the maximum possible return given the risk you’re taking.

Now that you know why we need to do it, let’s take a look at how. This article will introduce you to the tools that you need for the optimization process — Microsoft Excel’s Solver!

(P/s: The Solver function is not limited to just MS Excel. Other software might have it too).

How to enable Solver?

(This is based on MS Excel; it may not be the same for other software)

The Solver function is one of the built-in functions in MS Excel. You can find it by heading to the “Data” tab below.

Once you’re there, you can find it in the “Analyze” section:

What if you can’t find it?

Fret not, it’s just a matter of enabling it in the settings. So here’s what you’ll have to do:

1: Click on the “File” tab

2: Click on “Options” in the sidebar.

3: Once the Options pop up appears, click on “Add-ins”, on the “Manage” drop-down menu, select “Excel Add-ins”, and click “Go”.

4: Check the box on “Solver Add-in” and then click “OK”.

Brilliant! Now we’re good to go.

How do you do portfolio optimization using Solver?

Before we get to the steps, it’s good to know what we are actually on about. The whole point of optimization is to try and reduce the risks while minimizing profit by adjusting the weights of each stock in the portfolio.

In some sense, you can try and tweak the weights of each stock in the portfolio manually, like, try it one by one — but how long would that take, right? Once you’ve found the ideal combo it might already be irrelevant. What Solver does for you is essentially to tweak it one by one in matters of seconds (or minutes, depending on your device and data size — or it can also crash, idk).

Let’s get down to it!

Before we start:

We’d recommend you download our worksheet here; this is so that you can practice with it as you learn.
This is just an example of how you can use Solver for portfolio optimization. We haven’t yet moved into any specific strategy.

Do note that the data and examples we are using are from our previous articles, you can read them here:

Or if you already know the most of it, you can just download our workbook and tinker around with it.

Step 1: Look into the worksheet and make sure the data are well interconnected.

The whole point of those arrows is to say that each of the numbers is a result of the corresponding part. So, if you change any of it, the corresponding cell will change.

Why is this important? Because when you play with Solver, you want the changes made by the machine to correlate with the relevant variables — there’s no break in the chain of thoughts.

Step 2: Open Solver

Just click on the “Data” tab up there and click “Solver” which is usually on the right-most side of the banner. This will pop up. Now, in this example, we want the portfolio to go with the lowest risk possible.

Step 3: Set the objective.

As we mentioned above. Our goal currently is to get the lowest risk possible. In our portfolio, the overall portfolio risk would be the Annual Portfolio Variance.

Here’s what you need to do:

Click on the arrow-looking thing beside the “Set Objective” bar.

Now click on the cell where the value of the Annual Portfolio Variance is in. Click enter.

So, what do we want the risk to be? Minimum, right? Now tick the circle for “Min”.

Step 4: Set the variables.

If we want the outcome to change, we surely have to change the things that created that outcome, right? Much like everything else in life, it’s foolish to walk the same road hoping to one day reach a different land.

Now click the same arrow-looking thing beside the “By Changing Variable Cells” tab to select the variables.

Then, select the cells that contain the weights of the stocks in your portfolio. Why? Because portfolio optimization requires you to change the weight of your portfolio to achieve your goal, as we learned before, right?

Step 5: Set the conditions.

If there’s anything that ChatGPT taught us, it’s that you can’t give a general instruction to a machine and expect it to come with the most accurate answer. You have to set the perimeters by adding “constraints” or rules so that they won’t stray.

On the “Subject to the Constraints” section, click on “Add” to add a rule. You can add as much as you wish.

Since this is just an example, we’ll just tell you one important rule — that the sum of all the weight must be 100%.

Why? Because you want to utilize all of your money. The machine can avoid investing all of your money, that can happen.

Click “Add” > Click the arrow on “Cell Reference” > Select the cell where the sum of stock weights is located in > Choose equal symbol (=) in the middle dropdown choice > On the constraint, click on the arrow and write 100% (don’t forget that it’s in percentage).

The whole instruction is that the sum of all weights must be equal to 100%.

IMPORTANT: Another condition that you might want to add is that the individual weight of each stock cannot be 0, because sometimes the machine will avoid allocating any money in some of these stocks altogether.

You can put the condition that the weight must be more than 1% for example. The value will depend on what is the minimum weight you’d allocate to each stock.

Step 6: Click Solve

Next, all you need to do is solve it, and see how the machine will adjust the weight for you.

In our case, we put the constraint that the minimum weight of each stock cannot be less than 1%, so the machine still tries to pull it as low as possible for the stocks it doesn’t like. That’s why you can see ADBE and NVDA got 1%. Just in case you didn’t, here’s how it’s going to look like:

MS Excel decided to achieve the lowest possible risk, it’s throwing ADBE and NVDA. That’s why we recommend putting a minimum weight as the conditions because you’d probably have already done your research and filtration to finally arrive at these stocks.

However, the result above is not at all in vain, because as you can see, we can now estimate the lowest the volatility can be (21.81%) given your stock selection — i.e., how volatile your stock selections are overall.

That’s it!

Bottom Line

As far as this tutorial is concerned, we hope that you learned these few things:

The purpose of portfolio optimization.
How to set up and use Solver.
The variables that you need to change using Solver.
The end result that you need to get.
The precautions you need to take.

The key takeaways/market update is a series by AxeHedge, which serves as an initiative to bring compact and informative In/Visible Talks recaps/takeaways on leading brands and investment events happening around the globe.

Do keep an eye out for our posts by subscribing to our channel and social media.

None of the material above or on our website is to be construed as a solicitation, recommendation or offer to buy or sell any security, financial product or instrument. Investors should carefully consider if the security and/or product is suitable for them in view of their entire investment portfolio. All investing involves risks, including the possible loss of money invested, and past performance does not guarantee future performance.