Optimum Portfolio Weights for Maximum Sharpe Ratio: Excel

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

---

Closed Caption:

hello everyone in this video we are
going to find optimal portfolio weights
that will maximize the Sharpe ratio for
three asset portfolio the method can be
used for a portfolio of any size what
we're going to do is to use some excel
functions to do the job for us in a
later video we are also going to use the
solver add-in in Excel to do the same
thing for us let us get started by
looking at some data here we have three
assets
these are three securities of these
three companies Homestake mining cancer
aluminum and texas instruments their
excess return is given to us in this
column the variance is given to us in
this column and the covariance between
the assets are provided to us here so
the first thing that we need to do is to
write the variance-covariance matrix s i
have prefilled the values here for you
and we'll talk about them
this cell cell 11 of this matrix gives
us the variance of the first asset which
is this on the diagonal you will
remember we have the variances so in
this cell we have the variance of the
second asset which is this on this cell
we have the variance of the third asset
which is this on all other cells of this
matrix are the various various
covariances this one is the covariance
between asset 1 and a 2 which is this
value here this in this cell is the
covariance between asset 1 and 3 which
is this cell here this value
this value here is again the covariance
between assets 211 which is this value
this cell is the covariance between
assets 2 and 3 which is this value . 3
this cell is again the covariance
between assets three and one which is
this 0 and on this cell we have the
covariance between assets 2 and 3 which
is . 3 this value here
so what we need to do next is to find
out the inverse of the s-matrix of the
variance-covariance matrix if you look
at this picture we are wanting to find
the value of said first so we need to
have s inverse and then we also need to
have the vector for excess returns the
vector for excess returns is here but we
need to figure out what is s inverse so
we can very easily do that in Excel
because there are three assets and the
variance-covariance matrix is a
three-by-three matrix we are going to
have a 3 by 3 s inverse matrix so let us
select a three-by-three region once we
do that we are going to use the M
inverse function keeping the selection
active
we're going to now provide the array
which is this close brackets and hit
ctrl shift and enter all at the same
time so we have the s inverse matrix
here now what we need to do according to
our methodology is to multiply the s
inverse with the excess return matrix or
better so we are going to do that in
this space here it's going to be a 3 by
1 vector so we select this three by one
space and we use the M Walt function
Excel is now asking us to provide the
two areas for multiplication what is the
first array the first story has to be
the s inverse which is this so we select
that comma and then we want to provide
the second array which is the excess
return vector which is this close
brackets and hit ctrl shift and enter
all at the same time so we have our said
vector here this is said one this is
said to and this is said three
we can write the value of total said
here because we're going to need it when
we find the individual weights so let us
use the sum function to take a sum of
all the values
these are our numbers so we select close
brackets and hit enter my phone is
ringing so I'm going to have to pause
this video for a while take the call and
come back to you in a moment
alright guys so I'm done with my call so
let us pick a bigger threat again so we
had found out the total said here and
now we can very easily find out the
individual weights for asset one two and
three
let's do that in this column here in
this cell we are going to find the wait
for asset one the definition is here we
looked at it in the previous video what
we're going to do is to divide the said
I by the sum of said is so in this cell
we are going to now pick up that one
which is this and divided by the sum of
all sets which is this
this gives us the weight on the first
asset that we should invest 50
two-point-nine percent of our money in
the first asset we can do the same thing
for the remaining two cells as well we
can drag down the formula but at the
same time we want to ensure that the
denominator doesn't change the sum of
all Zeds should not change so we can
make a slight adjustment here we can
write a dollar sign on either side of
the letter B and then we can drag the
formula
these are our portfolio weights let us
see if they are correct we know that
some of always should equal one so let
us take the sum of these weights and see
if we got the right result we see that
the total of always is equal to one so
our results are correct they tell us
that this is w1 the proportion of money
to go to asset one this is W to which is
a negative weight that means the
mechanism is asking us to short-sell
probably the second asset and this is w
3 which tells us that we need to invest
another 50 two-point-nine percent of our
money in a three
let us now find out the expected return
standard deviation and sharpe ratio for
finding the Sharpe ratio we need the
excess return of the portfolio and we
also need the standard deviation of the
portfolio so in this cell here let us
find out the excess return of the
portfolio which is going to be equal to
the weights x the return vector so we
can use the sum-product function also to
do this so we write some product
and XL is asking us for the Rays these
are our weights so this is our first
array and we also need the array for
excess returns which is here so we can
select that close brackets and hit enter
so the excess return on the portfolio is
going to be 18.1 2% let us find out the
standard deviation of this portfolio
since we didn't set up a bordered
variance-covariance matrix we are going
to use the actual formula to find out
the standard deviation i'm doing this so
that you can see how to use the long
formula as well so what we need to do we
need to write down the first weight
which is here and we have to square it
we have to multiply the square of the
first wait with the variance of the
first asset which is this then we need
to add to it the product between the
second wait squared so let's square up
the second weight and multiply this with
the variance on the second asset which
is in this cell here c 4 plus we need to
square up the third assets with which is
this so let's square it and multiply
that with the variance of the third
asset which is in cell c5
now we need to add two times w1 which is
this times W to which is this times the
covariance between one and two
let us see where the covariance between
one and two is it's here in cell b3 so
we select that plus 2 times w1 which is
this x w 3 which is this times the
covariance between one and three which
is here in this cell e4 plus 2 times the
weight on the second asset x the weight
on the third asset x the covariance
between two and three
where is the covariance between two and
three
it's here in cell e5 so we select that
close the bracket if we leave it here we
have the variance but because we want
the standard deviation we are going to
raise this to the power of 0.5 and hit
enter so we have the standard deviation
of the portfolio now 46.1 seven percent
and we can very easily find out the
Sharpe ratio by dividing the excess
return of the portfolio by the standard
deviation of the portfolio so this is
the Sharpe ratio so given these weights
the Sharpe ratio is going to be
maximized and it's going to be equal to
$OPERAND . 392 fall hope this was
helpful for you
see you next time