9.1 - Working with weights

The previous chapter introduced the idea of portfolio optimization with excel's solver. This chapter will continue to build on that concept and explain an important portfolio concept often called the "Efficient Frontier".

Remember that we talked in the previous chapter about how a portfolio can generate multiple return series with a fixed portfolio variance. Let's now see how it works. This concept will ultimately help us understand portfolio optimization better.

The previous chapter focused on optimizing the portfolio for the lowest variance. These were the results in terms of stocks' weights.

The expected portfolio return is based on the following -

This is where things get interesting. Portfolio optimization has been limited to a minimal variance portfolio. As we have discussed, there can be more than one portfolio for each fixed risk level. Each portfolio could have different return characteristics. This will be explored in more detail.

At 15.57% portfolio variance, we know that the expected return is 36.35%. We will now increase the risk to 17% and calculate the best and worst possible returns. We are trying to find the best and most profitable return for a 17% fixed portfolio variance. Pay attention, however, that when I say "increase the risk", I mean to reduce the risk to a desired level. For now, 17%

9.2 - More optimization

We are heading in the right direction. We know that this portfolio has a minimum risk of 15.57%. We also found the maximum return at this level of risk, which is 36.35%. As I said, we will increase the risk by a bit and reduce the maximum and minimum returns for this risk. We will also note the investment weights. The risk will be increased further and we will note again the maximum and minimum returns as well as the weights. We will make a few of these iterations, and take down all observations.

I will then plot a scatterplot containing fixed risk data points with their respective max and minimum returns. This scatterplot will be further explored. This scatter plot will help us to understand portfolio optimization.

Let's get started fixing the risk at 17%. Note that I have opted 17%, but it could be 16% or 18%.

Step 1 Invoke solver

Image 1

As I mentioned in the previous chapter I have invoked the solver calculator clicking on the data ribbon. This is just to show you the optimized weights of the portfolio with the lowest variance.

Step 2: Set the parameters

Let's first determine the maximum return that one can get for a 17% risk. To do this, we must set an objective to maximize our 'expected portfolio returns'. This is illustrated in the following:

Image 2

Step 3: Select the weights

Next, we need to tell the solver that we want to maximize the portfolio's return by changing the weights. This is very similar in concept to the previous chapter.

Image 3

Note that the weights are for variable cells.

Step 4: Set the constraints

Here's the crucial part of optimization. We set the constraints. Solver will now know that we want to maximize returns at 17% risk by changing the investment weights. These are done while complying with the following two restrictions:

1. The sum of all the weights is approximately 100%
2. The Portfolio risk is fixed at 17%

This is the constraints section.

Image 4

Once we have all the constraints and other parameters set, click on "solve" to calculate the maximum return @ 17%.

The optimization results are as follows:

Image 5

55.87% is the portfolio variance that yields the highest possible returns at 17%. The weights for this portfolio are as follows. It is obvious that the portfolio's weights have changed when compared with the portfolio with the lowest variance.

Now, we will calculate the minimum return for the same risk level. In our case, it is 17%. Here is a table I am compiling that shows all of the portfolios we are creating, as well as their respective weights, risk return characteristics, before we move on.

Image 6

Portfolio 3 (P3) is what we are currently working on. This portfolio represents the lowest risk for 17% fixed risk. The solver tool is fully loaded and ready for optimization.

Image 7

While other variables are the same, the goal is to minimize rather than maximize. After optimization, the return has been reduced to 18.35%. We have created two distinct portfolios for the same risk. All this while changing only the stock weights.

These are the unique portfolios we have created so far.

Image 8

Recall that P1 is the minimum risk portfolio, P2 max risks @17% and P3 min risk @17%.

9.3 - Efficient Frontier

As we discussed, we can increase the risk by a bit to 18%, 19% and 21%, and determine the maximum and minimal risk at each of these risk levels. Our ultimate goal is to create a scatter plot of risk and return profiles and analyze its characteristics. I have optimized portfolios for each risk point and identified the maximum and minimal return at each point. Note that I have rounded the decimal values in this table to make it look pretty

image 9

You'll notice that I highlighted the return and risk values for each portfolio. Now, I will plot a scatterplot of these data points to see what I can find.

Simply select the data points to plot a scatterplot. Then, click on the insert ribbon to choose the scatter plot. This is how it looks.

Image 10

Click on the scatter plot to view the plot. This is how the plot looks. I have tried to make it more appealing by formatting it.

Image 11

The 'efficient frontier' in this portfolio is the curve you see above, my friend. What can we learn from this curve? And why is it important? There are many things to be aware of, so let's take a look at each one.

1. As you can see the X-axis is for risk, and the Y-axis for returns
2. The minimum variance portfolio is the one that starts at the leftmost point. This seems to be the one that seems to be the most isolated from the rest. This portfolio is known to have a risk of 15.57% and a return of 36.25%.
3. Now, we will be focusing on 17% risk (notice that x axis). You can now find two plots at 18.35% and 55.87%. What does this tell us?
   1. It shows us that a portfolio with 17% risk (or if we are very careful about fixing risk at 17%) can earn a return of 55.87%
   2. 18.35% is the worst portfolio in terms of return.
   3. Simply put, you aim to maximize your return if you have a comfortable level of risk.
   4. Multiple other portfolios are possible between 18.35% to 55.87% (when risk is fixed at 17%). These would be represented by plots between the minimum return and maximum return. These portfolios are all considered inefficient with the lowest return portfolio being the most efficient.
   5. As an investor, your goal should be to maximize the return. This is especially true if you know how much risk you are willing and able to take.
4. The same behavior can be observed for risks at 18%, 19.%m, and 21%
5. Portfolios that are the best, or more simply, efficient, will always be those with a line above the minimal variance portfolio. Below is a highlighted example of this line.

Image 12

As an investor, it is important to aim for a portfolio that lies on the efficient frontier. You may also realize that creating such a portfolio is simply a matter of rearranging weights according to the results of portfolio optimization.

You can think about it this way: When you put your money at risk, you want the highest return. This is what the curve is trying to communicate to us. This is what prompts us to make portfolios more efficiently.

In the next chapter, we will examine the concept of "Value at Risk". Then we will look at how traders can understand risk.

To Summarize

1. Uniqueness is defined as a portfolio that has certain weights for each stock.
2. We can optimize portfolios to achieve the best return by setting the desired risk level.
3. We can have multiple portfolios that are different from the max and min return portfolios (depending on the level of risk).
4. The efficient frontier is the scatter plot of return and risk.
5. The most efficient frontier portfolio is the best one you can build for a given risk level. All other portfolios are inefficient.