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.
|Sl No||Stock Name||Pre-optimized weight||Optimized to minimize variance|
The expected portfolio return is based on the following -
|Pre-optimized||Optimized to minimize variance|
|Expected Portfolio return||55.14%||36.35%|
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%
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
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:
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.
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:
This is the constraints section.
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:
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.
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.
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.
Recall that P1 is the minimum risk portfolio, P2 max risks @17% and P3 min risk @17%.
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
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.
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.
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.
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.