How To Find Portfolio Weights | Optimal Portfolios With Excel Solver

Auke Plantinga

Subscribe Here





Optimal Portfolios With Excel Solver


This is our code blunder, huh? And I’m going to show you in this movie. How to calculate a portfolio and range short sales constraint and I’m using an example with five different assets, A B C and D and E and a have the expected returns and standard deviations in the columns B and C. And here we have the covariance matrix. So the first thing that I have to do is to create a portfolio of a vector of portfolio weights for each of my five assets and for simplicity, I had put that equal to an equally weighted portfolio So here we go a bit of messing around with the formatting, but apart from and everything is fine and so now we’re going to let the solver decide on what the optimal composition of the portfolio is and in order to do that. I first need to calculate the expected return of the portfolio and I can do that. With the matrix multiply function, then I have to transpose first, the column vector of weights, and then I multiply this transposed factor with the expected returns of the individual assets. I press shift. Ctrl enter. And here is the expected return of the portfolio. Standard deviation is a bit more tedious, but again we use matrix multiply and again we transpose the vector of portfolio weights. We need to multiply this transposed factor of milk of portfolio weight with the covariance matrix and eventually we’re going to multiply that again with the column factor of portfolio weights. And so then we are basically there. We press shift, ctrl. Enter and mind you. We now have an expression, but look here. This is actually the variance, so we’re going to change that, and so we are going to do the square root of this in order to get the portfolio standard deviation, so this now it becomes time to introduce the solve. So now we’re going to calculate optimal portfolios using the solver or an uploader as it is called indexed and the solver needs a objective function and we are going to use the Sharpe ratio for that. The Sharpe ratio is the excess return, and since we use a monthly data, we have to convert a risk-free rate to a monthly rate. We do that in a bit simple way, but this is enough for T example here, and so here we have basically the problem. Are we going to put the weights again to equal weight it as a starting point, and we see a Sharpe ratio of Point 20 now we’re going to start the solver to see what is going on where we want to have an optimal portfolio as you can see. A lot of stuff has already been filled out. So the Sharpe ratio so selected as the objective function portfolio weights. These are the inputs of the problem, the variables that I can change, and finally, I need to have a condition, Namely that some of our portfolio weights should add up to one. This is actually already this this. This button here is already indicating that. I can’t take short positions, So if I get is unmarked, then I allow the problem to create and limited short positions and let’s see what that brings us. It brings us indeed short positions, and I’m going to copy this for future reference, and I do that with very special in order to make sure that it doesn’t matter mess up later and as you can see that results in a Sharpe ratio of 0.29 now again. I’m going to do the solver, But this time I’m going to exclude short positions. I can do that in two ways. I can just check this box or I can add a condition, so I’m used to do that by adding a condition, so I’m going to do that this time as well, so I’m going to say these things. The portfolio weights should be larger or equal than zero and here. We have the complete problem, and now I press solve again and it will give me a solution that says here. The solver found a solution or conditions and and optimization conditions have been satisfied. So that’s good news, and indeed we see here that start B, which previously had a big shore position, now ends up with a minor one and similarly for Star D and this all results in the Sharpe ratio of one to seven, which is still higher than the one that I had with equal weighting. So this is the end of this video. I hope that you enjoyed it early. Found it useful and hope to see you next time.

0.3.0 | Wor Build 0.3.0 Installation Guide

Transcript: [MUSIC] Okay, so in this video? I want to take a look at the new windows on Raspberry Pi build 0.3.0 and this is the latest version. It's just been released today and this version you have to build by yourself. You have to get your own whim, and then you...

read more