Sharpe Ratio Maximization with Excel Solver

This video demonstrates the use of Excel Solver to arrive at portfolio weights that maximize the Sharpe Ratio

Narrator: Hello everyone.

In a previous video we used this data set here to maximize the sharp ratio with respect to weights.

We used some matrix operations in Excel to do that.

In this video we are going to use the same data set and arrive at the same result

as the previous video by using the solver add-in in Excel.

First we are going to do it by assuming that short sales are allowed

which means that one or more portfolio weights can be negative.

Then we will resolve by assuming that no short sales are allowed,

which implies that all portfolio weights must be greater than or equal to zero

so let us have a look at the data set one more time.

We have information about these three companies,

Homestake Mining, Kaiser Aluminum, and Texas Instruments.

Their excess returns are provided in this column,

the variance of their returns are provided in this column,

and this column here, these three cells give us the covariances between the three assets.

We also have an additional column for weights here at this time

and I have prefilled some random values into these cells,

0 for Homestake MIning which we are going to call our asset 1,

0 for Kaiser Aluminum which we are going to call our asset 2,

and 1 for Texas Instruments which we are going to call asset 3.

In this cell I have taken the sum of the above three values to check that the sum of weights is equal to 1.

What we are going to do now is to prepare a bordered variance covariance matrix.

I'm not going to talk too much about the bordered variance covariance matrix in this video

because I've covered it in our previous one

so if you want a reference you can watch the previous video on portfolio variance

and see how a bordered variance covariance matrix is prepared

and the variance is calculated in Excel.

So what I'm going to start with now here is to write the top border here

in these three cells and the left border in these three cells.

For the top border in this cell I'm going to write my W1,

that is the weight on my first asset which is this cell here F3

so I'm going to write here F3.

Then here in this cell I'm writing F2 which is the second weight here.

Pardon me, not F2, it's going to be F4...

so this is done

and in this cell I'm going to write the third weight which occurs in this cell here F5.

I'm going to do the same thing on the left border.

Weight 1, then weight 2, and then weight 3.

Inside these borders we are going to write our variance covariance matrix

so let me fill it out quickly.

On the diagonal elements first I'm going to write the variances of returns of the assets,

.1 for the first one, .4 for the second one, and .7 for the third one.

The rest of the cells are the covariances.

This cell here is the covariance between 1 and 2, which is minus .1

then in this cell we have covariance between 1 and 3 which is 0.

Here in this cell we have covariance of 2 and 1 which is minus .1

and in this cell we have covariance between 2 and 3 which is .3.

The last row now covariance between 3 and 1 is 0.

Covariance between 3 and 2 is .3

so we have our bordered variance covariance matrix ready so let me make this one bold

so that you know that these are the borders.

Now what I'm going to do is to use the sum product function in here

to do the operation that we covered in a previous video.

This cell multiplied by this cell multiplied by this cell

plus this cell multiplied by this cell multiplied by this cell

and then this cell multiplied by this cell and multiplied by this cell.

So what I'm going to write here is cell number B9 times sum product of these two arrays.

This is my first array and this is my second array.

Close brackets hit enter and I see a value of zero here.

I have to do the same operation for this column too and for this column too

but in doing those operations I have to keep this reference the same.

So since I've already done it once here I can just drag the formula for

the other two cells but because I want to keep this reference

the same I can make a slight adjustment here.

With letter a on either side of that letter I can write a dollar sign

so that cell's A10 to A12 do not change.

The reference remains the same, this one,

and then I can drag and drop the formula in the other two cells as well.

I want to calculate my mean of the average portfolio return here so let us do that.

I can do that here by using again the sum product function.

The first array is going to be my weight vector

and the second array is going to be my excess return vector.

So this is my mean return or average return at the moment with these portfolio weights.

Now I need to compute the variance.

The variance, I've already set up the bordered covariance variance covariance matrix

and I can use now the sum of these three values here to find my variance

so I can just say sum and these three numbers here so this gives me my variance at the moment.

I can also calculate the standard deviation by taking the square root of this variance here

and I can calculate the sharp ratio also very easily by dividing the mean

excess return by the standard deviation of this portfolio so this is my sharp ratio at the moment

which may not be optimum because we don't have the optimum weights as yet.

These are just random weights that we entered.

What we are going to do now is to break, is to go to the data tab

and invoke the solver by clicking on it.

So now we have to provide the parameters to the solver.

Our target cell is going to be the sharp ratio

and we want to maximize it so here the correct option is already checked,

the maximization option.

And we want to maximize the sharp ratio by changing our weights so here Excel is asking

us by changing weight cells.

We know that we want to change our weights to maximize the sharp ratio

and the weights are here so we can select this portion here, these three cells.

We also have a constraint that the sum of weights must be equal to one

so we can add the constraint here, this is the constraint box

so we click add and then we want to tell excel that this cell F6 must always be equal to one

so we write here 1 and we click OK.

We are now to click the solve button and we should get our desired result.

Let us see.

Now we see it says solver found a solution and all constraints and optimality conditions

are satisfied, let's click OK and look at these weights.

52.9% money in the first asset.

This asset probably should be short sold and another 52.9% money in the third asset.

This is going to give us the maximum sharp ratio in this case for this portfolio of .3924

with a mean return of .1812 and a standard deviation of .4617.

These are the same results that we arrived at in the previous video.

Now let us change the scenario a little bit.

In this situation what we are saying is that, what we're observing is that

this weight here is a negative weight

which means that we have to short sell this asset.

But not all countries, not all nations, not all markets especially the markets which have not yet

developed so much, they don't allow short sales so if we don't have the provision for

short sales in a particular capital market then we still need to find the optimum portfolio weights.

We are going to have some additional constraints then.

Let us invoke the solver once again.

This is our previous data entered into the solver but we are going to have

to provide some additional parameters to solver.

What we want to tell the solver...

We want to tell solver that none of these portfolio weights should be below zero,

that is all of them should be either equal to or greater than zero

because no short selling is allowed.

So this cell here, F3 which is weight one should be greater than or equal to zero.

This is another constraint that we have now.

Likewise we can add the other two constraints for the other two weights.

Cell F4 which is weight two is also not allowed to go below zero so that also must be

equal to or greater than zero

and the same thing we are going to do with the third asset and its weight.

This item here should be greater than or equal to zero

so we have added no short selling constraints here and we click solve.

Again we see this message solver found a solution and all constraints

and optimality conditions are satisfied.

Click OK to get rid of this dialogue and let us see.

What it is telling us now is that 53.85% of our money should be invested in the first asset.

We should not invest anything in Kaiser Aluminum and 46.15% of the money

should be allocated to Texas Instruments.

And in this column we see that our result is correct because the sum of all weights is still one.

What affect does this have on the sharp ratio?

The sharp ratio has now changed to .3919.

You also see that the mean return has fallen a little bit from 181176 to .1654

and the standard deviation has risen a little bit to .42

or it might have fallen, I don't remember

actually the previous standard deviation that we got.

You can rewind the video and see what was the previous standard deviation

but I have, I do have a recollection of the mean value.

It was .18 so the mean has definitely fallen.

This is all I wanted to tell you in this video.

In the next one we are going to try to arrive at the same result using the solver add-in

but in that video we are going to use the Markowitz Scheme

for minimizing the risk subject to a given level of return.

See you next time.