Optimal portfolios with Excel Solver

This is an instuction video on how to use Excel's solver for calculating efficient portfolios

---

Closed Caption:

this is alkyl planta and i'm going to
show you in this movie how to calculate
the portfolio and short sales constraint
and i'm using an example with five
different assets
ABC and D and E and 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 an effective
portfolio weights for each of my five
assets and for simplicity
I and put that equal to and equally
weighted portfolio
so where we go a bit of messing around
with 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 portfolios 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
common factor of weights and then i
multiply this transposed factor with the
expected returns of the individual
assets and press shift ctrl enter and
here is the expected return of the
portfolio standard deviation is a bit
more tedious again we use matrix
multiply and again we transpose the
factor of portfolio weights
we need to multiply this transposed
factor of milk of portfolio wait with
the covariance matrix and eventually
we're going to multiply that again with
the column vector
of portfolio weights and so then we are
basically there we press shift ctrl
enter and mind you we now have an
expression that 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 honor to get
the portfolio standard deviation so this
now it becomes time to introduce to
solve
so now we're going to calculate optimal
portfolios using the solver and applause
ur as in this color next and this over
needs a objective function and we are
going to use the sharp edge for 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 and it's simple way but
this is enough body example here
and so here we have basically the
problem and we're going to put the
weight again to equal weighted as a
starting point and we see the Sharpe
ratio of x 20
now we're going to start the solver to
see what is going on what we want to
have an optimal portfolio as you can see
a lot of stuff has already been filled
out so the Sharpe ratio sir
selected as the objective function
portfolio weights these Hardy inputs of
the problem
the variables that i can change and
finally I need to have
a condition namely that some of our port
for your weight should add up to 1 and
this is actually already this this this
button here is already indicating that
they can take short positions
so if i get is unmarked and then I allow
the problem to create unlimited short
positions and let's see what that brings
us
it brings us indeed your positions and
i'm going to copy this for future
reference
and I do that with a special in order to
make sure that it doesn't matter
mess up later and as you can see that
results in the Sharpe ratio of . 29 now
again I'm going to do the solver but
this time I'm going to exclude your
positions
I can do it 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 0 and here we have to
complete problem and now i press solve
again and it will give me a solution
that says here
this also found a solution all
conditions and and optimization
conditions have been satisfied
so that's good news and indeed we see
here that
stop being which previously had a big
short position now ends up with a minor
one
and similarly for stock d and it's all
results initial operation of what 27
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 or at least you
found it useful and hope to see you next
time