Portfolio Optimization in Excel.mp4

This is a video created by Dr. Colby Wright demonstrating how to use the matrix algebra and solver functions in Excel in order to optimize the weights within a portfolio comprised of more than two assets. Please note, this is limited to mean-variance optimization and does not consider higher moments (such as skewness and kurtosis). This method of optimizing portfolio weights also assumes that the distribution of past returns is reflective of the distribution of future returns, which is often a tenuous assumption.

---

Closed Caption:

hello there so I want to show you how
you can actually calculate portfolio
expected return and portfolio standard
deviation and then I want to demonstrate
to you how you can actually use the
solver function in Excel to either
maximize your return in your portfolio
given a set level of standard deviation
you're comfortable with
or how you can minimize the standard
deviation if you have a specific level
of expected returns that you're trying
to achieve
then you can also maximize the Sharpe
ratio which would be the ratio of
expected return to standard deviation
but the first thing that I need to show
you is how you can actually use matrix
algebra in excel in order to calculate
portfolio expected return and portfolio
standard deviation
now just as a brief reminder let me take
you back to what you saw on the power
point earlier and that would be this
right here
the portfolio expected return is going
to be w transpose mu where w is your
vector your K by 1 vector of the weights
in your portfolio and mu is the K by 1
vector of the expected returns to each
of the assets and so if we want to get
expected return we need a weights vector
we need an expected return of a factor
and we need to take w transpose mu
so let's come over here and do that what
i've done is i went and collected some
data on a handful of assets just to try
to demonstrate this to you by no means
is this intended to be comprehensive
but i think it will be informative and
interesting
so what I've done is I've grabbed eight
different assets this S&P 500
exchange-traded fund the spider fund
IM dy which is the S&P midcap
exchange-traded fund
Slyy which is the S&P smallcap
exchange-traded funds so you've got
large cat mid cap small cap equities us
equities so i would also got the fifa
- the US so that's going to be Europe
Far East and Australia so that's
basically you know developed countries
- America
then i also got an emerging markets etf
so these are basically all equity
exchange-traded funds covering large cap
us mid-cap us small-cap us developed
countries excluding the US and in
emerging markets countries
this is a US Treasuries etf so that's US
government bonds
this is a corporate government bonds us
corporate bonds and then this is a gold
etf so really the only thing that I've
left out would be maybe us municipal
bonds and then also global sovereign
debt and global corporate debt but again
this isn't meant to be comprehensive
just want to demonstrate how you can do
this but it's it's fairly broad ranging
in terms of asset classes that we have
represented
so what I did is I got the last 16
months worth of data i calculated the
average monthly return these different
asset classes and you can see that's the
average monthly return to each of the
asset classes
no surprise i'm doing this in january of
2012
gold has been on a tear over the last
five years and then I S&P 500 has really
struggled and so most of the global
equity indices to be honest with you
treasuries have done well corporate
bonds done okay ever since the recovery
coming on the heels of the financial
collapse
I've also calculated the
variance-covariance matrix which I'll
show you at some other point time how to
do that but just take it as a given that
this is the variance covariance matrix
so along the diagonal
I have the variances of these assets and
then on the off diagonals i have the
covariances so what we have is we have
this is armed you
vector and this is our Sigma matrix
the only other thing that we need is our
weight vector and if we have the weight
vector then we can dive right into and
we can calculate the portfolio expected
return and standard deviation
keep in mind that what we're doing
assumes that the returns that we've
observed over the past five years are
indicative of the returns we expect to
earn over the next five years so just a
big assumption going into it now that we
said that let's do it let's go ahead and
calculate are expected return to our
portfolio i'm going to start off with a
portfolio
that's just equally weighted so I've got
18 of my portfolio and each of these
assets
this is going to be a check figure for
me just to make sure that I've done my
weights correctly so the weights have to
add up to a hundred percent right
ok so what we want to do right here to
get portfolio expected return is we want
to just do w transpose mu so here's our
w and we're going to transpose it i'm
going to multiply it by mu over here and
that should give us are expected return
so M malt matrix multiplication
we're going to transpose the w vector
we're going to multiply it against him
you always always always when you're
doing matrix multiplication and Excel
you hold down ctrl shift and enter and
it spits out the result now to get our
portfolio standard deviation
what we need to do is we need to take w
transpose multiplied it by Sigma and
then multiply that by W so that's coming
from our powerpoint slide as well you
can see over on our powerpoint slide
that our portfolio standard deviation is
w transpose Sigma W and we need to take
the square root of that so we'll do that
over here
all right here we go we're going to take
the square root of matrix multiply and
what are we going to multiply well first
off it's going to be the transpose of
that x sigma
ok so now think of that think of that
matrix multiply as its own vector and we
need to multiply that by w again so i'm
going to come back inside here and we're
going to do em malt and so we're going
to take remember this right here on the
on the interior that represents w
transpose W that needs to be matrix
multiplied against w naught transpose
this time
close up all the parenthesis parenthesis
i should say and I think I didn't want
too many hold down control shift enter
and it should spit out the portfolio
standard deviation and you can see we
get a portfolio standard deviation of
4.46 two percent then we can calculate
Sharpe ratio for fully expected return
over standard deviation technically
speaking we should subtract out the
risk-free rate in the numerator but it's
not a big deal
we'll just call this the Sharpe ratio
even though we haven't taken into
consideration the the risk-free rate so
there's our Sharpe ratio not really all
that impressive but now what we can
start to do is we can start to
manipulate things a little bit and the
first thing that I want to do is I want
to say okay if I look over here I see
that the smallest standard deviation so
the single asset in our portfolio with
the smallest standard deviation is this
corporate bond etf got a monthly
standard deviation of two point eight
five eight percent so what I'm
interested to know is could we construct
a portfolio that has a standard
deviation equal to or less than that but
that gets us an average return higher
than the monthly average return of . 539
percent
so in other words what we're going to
say is could we hit this minimum
standard deviation but somehow get a
higher expected return than what this
asset by itself is doing in order to do
that
first off i'm just going to actually
slide those weights over to begin with
don't worry those will change
I'm going to slide this formula over as
well as just to check figure for us it's
also going to be used as a constraint
and then these formulas can almost be
slid over but i need to lock in some
things specifically i need to lock in
the sea column which is my new column
remember you still got a hold down ctrl
shift when you hit enter now i can copy
and paste that over and then over in
this formula which is the standard
deviation
I need to lock in my Sigma matrix and my
Sigma matrix is this
n through you control shift enter i can
copy and paste that over a copy and
paste that over ok so now what we're
going to do is we're going to go into
the solver function and again what I
want to accomplish is I want to try to
max out my return
but keep the standard deviation at or
below this number which happens to be
the lowest standard deviation of all the
assets in our portfolio so I go to the
solver function and the target cell and
for those of you didn't see the way that
you get to the solver function as you go
up to the data the data tab at the top
and you click on solver
the target cell what we want to do is we
want to max out our expected returns so
we click on the expected return and we
tell it to max it out by changing all of
the weights but we have to give it some
constraints
the first constraint is that the sum of
all of the weights has to equal one or
one hundred percent so when we add up
all the weights in our portfolio have to
have a hundred percent the next
constraint is our portfolio standard
deviation has to be less than or equal
to this two point eight five eight which
again is the lowest standard deviation
of any of the a single individual assets
in our portfolio so we click on that and
I'm gonna hit the solver button and
we'll probably get a an interesting
solution to this and then we'll probably
come back in and add another constraint
ok so let's take a look at what we get
here for the answer and you can see here
are the weights that Excel has spit out
for us
you can see they add up to a hundred
percent so we have a hundred percent of
our portfolio invested and it gives us
an expected return of 1.31 to seven
percent and it gives us a standard
deviation of two point eight five eight
percent so this is really quite
extraordinary this is the power of
portfolios and diversification
what do I mean by that well over here
and we look at all these standard
deviations and clearly one asset have
much lower standard deviation than any
of the others and that was this
corporate bond etf
however it's expected return really
wasn't that impressive it wasn't bad but
it wasn't through the roof
so by putting together a portfolio were
able to come up with same standard
deviation is that asset so you know
we've achieved this really low level of
risk and volatility but look at the
expected return that we are now
achieving just by diversifying our
portfolio with these optimal weights but
what I wanted so that's powerful and
oppressive and useful and let you know
here's the Sharpe ratios to all the
individual assets over here
look at the Sharpe ratio that were able
to achieve by employing these optimal
weights so then we blow all of these
Sharpe ratios out of the water there is
no single asset in our portfolio that
can come close to competing with the
Sharpe ratio that we generate
over here notice however we are short
selling in this portfolio specifically
we are short selling this
fifa exchange-traded fund remember
that's the Europe Far East and Australia
exchange-traded fund so it's basically
developed countries - the US and what
the optimal weights are telling us is we
should basically short sell one hundred
percent of our portfolio in that in in
that particular exchange-traded funds so
in other words these ideal weight
optimal weights are saying whatever the
value of your portfolio is go short sell
exactly that amount and then you're
doubling down and everything else is
essentially was telling you what you
might be able to do that it's possible
but there are also a lot of situations
where you won't be able to short sell or
you might not be able to short sell that
magnitude
so another thing you can do is you go
back into the solver you can say well
let me just add a constraint and the
next constraint is I'm going to say
let's make it so that all these weights
actually have to be greater than or
equal to 0 so in that case we're not
going to allow any short selling
so now same constraints except we've
added the constraint of no short selling
you saw that you're gonna get a much
different solution here ok so all right
now look at the solution he's he's very
very different
it's telling you to put zero percent in
quite a few of these assets and it's
telling you to put the lion's share of
your money into this corporate bond
portfolio which makes perfect sense
because we're trying to get this low
standard deviation
it's telling you to put another fair
chunk into the gold and then into the
Treasury's with just a little bit into
the small-cap etf with US stocks if you
do that you still hit this standard
deviation of two point eight five eight
percent but now your expected return has
gone down Peter pretty considerably now
this is still demonstrating the power of
portfolios because this corporate bond
ETF is only getting . 539 percent return
on average per month
however the portfolio's getting . 849
five percent which is quite a
significant jump and you can see in the
Sharpe ratio
we're certainly beating the corporate
bond ETF and we're also beating gold
which happens that the highest sharper a
servant of the assets in our portfolio
sis
still demonstrating the power of
portfolio optimization and
diversification we're still getting a
much higher Sharpe ratio then we could
with any of the individual assets but
certainly if you can't short sell both
the expected return and the Sharpe ratio
is taking a hit
ok next thing I want to accomplish
maybe instead of wanting to minimize the
variance in our portfolio what we're
wanting to do try to get this high
return
well you're going to struggle to get
anything higher than 1.4 6 2 percent but
what we can do is we can see all right
if we want to hit 1.46 two percent if
you try to do it without a portfolio if
you try to do with out diversification
then you would just invest in gold and
on average you get this 1.46 two percent
per month but you'd be bearing standard
deviation or a risk of six percent
basically per month so we're interested
to know
could we get that kind of expected
return but somehow lower our risk
so I'm just going to copy and paste this
over just to get us started just like I
did last time
now i'm going to go back into the solver
i'm going to reset everything
so now our target cell is actually going
to be our standard deviation we don't
want to maximize that we want to
minimize it by changing again we're
going to change the weights and let's
add in our constraints so the first
constraint is some of the portfolio
weights has to equal one
our next constraint is going to be that
now we want our expected return that
number to be equal to or greater than
this number right here which is the
average return per month the Gold's been
achieving i'm also going to put a short
selling constraint on well you know what
let's do it without the shorts only
constraint at first and see what happens
so go ahead and click solve see what
kind of output we get
and you can see that once again I mean
who there is the power of
diversification and portfolio
optimization
we're still hitting this 1.46 two
percent average monthly return but look
how much we lowered the standard
deviation
we've almost cut it in half if you were
going to try to do this just with gold
your bearing a six percent standard
deviation per month that's the risk
through the portfolio down a three
percent risk gives you a massive Sharpe
ratio but we kind of get a similar
result of what we got earlier which was
it's telling you got a short sell evil
like a madman and again you might be
able to do that you might not be able to
do it you might not want to do it you
know
mutual funds and certain other
investment pools can't short sell and so
it's useful to know that something you
can go in and you can establish one more
constraint so let's add the no short
selling constraints so we're going to
say that all the weights have to be
greater than or equal to 0
throw that in there solve it and see
what we get now and we get a solution
that frankly is not all that interesting
you get a solution that says alright
invest all your money and gold
except put just a little bit in this TLT
and that keeps you at the 1.46 two
percent return but it's slightly reduces
your standard deviation so you can see
that if you put in the nose
short-selling constraint
it definitely changes the outcome that
you get all right let's do one more
thing
let's just maximize the Sharpe ratio
let's just say what can we do what we do
in terms of portfolio optimization to
just get the highest Sharpe ratio
possible
so we're going to go back into the
solver i'm going to reset everything
click OK and now what I'm trying to do
is I'm trying to maximize that Sharpe
ratio i'm just going to max it out and
we're going to do it by changing all of
the portfolio weights here and the only
constraint i'm going to put in is that
got to add up to a hundred
so all the portfolio way to have to
equal one click ok I'm going to solve it
we might get a crazy solution that's ok
if we do let's just see what happens
ok so we actually get something
relatively similar to what we got back
in this column when we didn't put the
short selling constraint in there and
you can see that the outcome we get is
simply you want to load up on on the
corporate bond etf you want to load up
on gold you want to load up on the
emerging markets and the Treasury's load
up on the SNP large cap and mid-cap and
you just want to short sell like crazy
the Eva
let's go back in and if you do that you
get a sharpe ratio . 46 which is almost
twice as high as anything you can get
with an individual asset
let's go in add the short selling
constraints see what happens
so going to highlight all of our
portfolio weights and we're going to say
that they have to be greater than or
equal to 0
click OK and we're going to solve it and
see what we get
and looks like if you want to max this
out without short selling that what it
tells you to do is basically put thirty
five percent and gold
thirty-eight percent and the corporate
bond ETF and another twenty-four
twenty-five percent in us treasuries and
a little bit into us small-cap stocks if
you do that you have average return of .
9 27 - and a standard deviation of three
. 086 and sharpe ratio . 3
so I just want to reiterate and
re-emphasize the power of
diversification diversification is going
to allow you to achieve a much higher
Sharpe ratio then you could get by using
any of the individual assets by
themselves
you get there one of two ways mostly the
way that you get there is by men are
minimizing the standard deviation of
your portfolio given a certain level of
expected returns
you can see we're getting . 97 two
percent here on average per month over
here
that's probably the closest so you can
see we're actually getting higher
average return than that
asset with a lower risk and so there it
is
portfolio optimization optimization in
Excel