Efficient Frontier

In this video, Dr Paul Docherty from The University of Newcastle (Australia) describes the portfolio optimisation and the efficient frontier. The video includes a demonstration of how Microsoft Excel's Solver addin can be used to derive the efficient frontier.

---

Closed Caption:

I'm going to this instructional video on
how to derive the efficient frontier
using Microsoft XO this video has been
created for students and portfolio
management now as we know the efficient
frontier consists of all efficient
portfolios that is those portfolios that
earn the highest return for a given
level of risk that we can actually
derive the efficient frontier for our
souls for a set of portfolios by
identifying the group of portfolios that
actually have the maximum Sharpe ratio
what we're going to do in this video is
use some data in excel to demonstrate
how this can actually take place so
first of all the spreadsheet that I've
got open on the screen in front of me
comprises the data 47 stocks listed on
the australian securities exchange I've
also got historical price start of each
of those seven stocks for the period
from the thirty-first of $MONTH december
two thousand four through to the
thirty-first of $MONTH december 2009 and
the first step in this process is to
convert those process to returns and you
can see here in column D I've done that
by taking the change in price plus any
dividend divided by the original price
as being the return for that day so
comedy about daily returns for PHP
stocks eh . within the sample time and
I've done the same for each of the
different stocks that we're analyzing
key within a feasible set of seven
different securities now if I go into
the bottom of this time series what I'm
also interested in is identified first
of all what was the average daily return
and i can calculate that simply by
taking the average of all the values
within conde above obviously as
portfolio theory is based on the concept
of mean variance efficiency i'm also
interested in the standard deviation of
those returns so use the standard
deviation formula in excel to be able to
calculate the historic standard
deviation of daily returns now one thing
to note about this particular exam
the returns I've got he had daily
returns
however by convention we normally think
about mean and variance over either
monthly or annual periods in financial
markets just by convention so what I'm
actually going to do here is I'm going
to take this daily standard deviation
and convert it to an annual value and
the way we convert a daily standard
deviation to an annual standard
deviation is by multiplying by the
square root of number of days within the
the reason why we multiplied by the
square root of number of days will to
change a daily variance to an annual
variance we x number of days in the year
as standard deviation is the square root
of variance to move from a daily
standard deviation to an annual standard
deviation we have to multiplied by the
square root of the number of days now
cross the time . i had here the average
trading days each year in my sample was
actually 260 . 8 that came from the fact
that the sample i was covering comprised
1304 days and it covers the period of
five years Soviet 1304 and / 5 you'd
find this 260 . 85 converted that daily
standard deviation to an annual standard
deviation accordingly so we're now left
with these up with a daily average
historical return at daily standard
deviation of returns an annual
annualized standard deviation of returns
and i can calculate that for each of the
stocks within my feasible set of
investments now when we think about
portfolio theory what we know is that
when it comes to calculating the
variance of a portfolio of stocks
what is important is not only the
standard deviations of each of the
stocks within the portfolio but also the
covariances or the relationships between
those stocks so what I've got down the
bottom here is actually a correlation
matrix and then under a covariance
matrix now remembering correlation and
covariance they're both measures of
relationship between two series
correlation is just a standardized
covariance that's measured on a scale
between
it's 1 and +1 I'm gonna focus on the
covariance because we tend to talk in
terms of covariances and variances in
financial markets and in our
calculations so what i can do is that
4-h a set of returns each of the time
series returns for different stocks i
can calculate the covariance between
those two series using the covariance
function equals C 0 VAR and then I would
select the two series that i want the
covariance between now once again as i
did before with standard deviation
because by convention we often think in
financial markets in annualized terms
i'm going to analyze these values now to
analyze a covariance term and indeed
also to analyze a variance term we
multiplied by the number of days within
the e so I've taken that covariance of x
260 . 8 to get the annualized covariance
between bhp and fosters groups talks to
be . 024 279 equally if i move
throughout this matrix I can think of
each of these boxes as being the
covariance between the two stocks that
intersect at that point within the
matrix now point to note here along the
diagonal these observations for example
if I take the top square here this is
the covariance between the HP stocks and
BHP stocks now the covariance between
bhp and itself is actually the variance
of $OPERAND bhp so in fact what we find
on the diagonal he are variants terms
this is the variance of each of those
particular stocks couple of the
important things to consider yourself my
covariance matrix built he using my
historical data now what we know is that
when we're coming to forecast expected
returns we don't just want to rely on
historic returns we can't just say the
average returns from the past and
necessarily going to be the returns we
say in the future
therefore what we tend to do is use a
model of expected returns or an asset
pricing model so to calculate expected
returns using the cap him what we need
to be able to do is estimate beta so
what I've got
here is he's actually estimated a beta
for each of those stocks two ways to do
that number one is I could estimate the
regression where I take excess returns
of the stock and excess returns of the
market and are regressive them on each
other the coefficient would be the
beater
alternatively and you actually
mathematically get exactly the same
answer you can actually use a formula to
calculate beta that formula is what I've
used to be the formula to calculate beta
is actually that BTW equals the
covariance between the stock returns and
the market returns / the variance of the
market so for example for PHP because
this column if i go right on the top e
this is the column with with PHP returns
the beating for the HP is 1.55 and that
makes sense because BHP's and mining
stock mornings quite a cyclical industry
so we would actually expect the HP has a
higher exposure to systemic risk than
the market as a whole what I can then do
therefore is use my cap him to calculate
the expected return so in this
particular example i'm going to assume
that the expected market return is nine
percent and the expected risk free rate
is five percent based on the input from
above
I can say that beta of 1.5 times
expected return on the market- the
risk-free right what's the return on the
market is nine percent and the risk-free
rate is five percent the excess return
on the market must be four percent so
beta times excess return on the market
plus the risk-free rate gets me an
expected return for bhp stop stocks of
11.2 percent and expected excess return
given the risk-free rate in this example
was five percent that's just expect to
return list the the risk-free rights are
less five percent now when it comes to
reality in practice what we use for the
expected market return and the expected
risk free rate will be some form of
analysts forecast so what I've created
on this spreadsheet he is given a
overview of how we can calculate the
inputs the starting point
well efficient frontier what we're going
to be doing is taking those starting
points and actually using them to
develop that afraid that the frontier
for assholes
so if we move across to our second
spreadsheet what we can see is at the
top of the spreadsheet I've got two
cables the first cable is listed the
seven stocks that I identified before
they are my feasible set of investments
in this particular example one of them
got is my historic standard deviation of
returns these are those annualized
values from the previous spreadsheet
that that we calculated and my expected
excess return so again these are
expected returns calculated using an
asset pricing model and we look back to
our earlier spread shape these are those
values along the bottom here that there
are calculated based on those better
estimates from earlier so these cells in
yellow in the spreadsheet they're all
imports and then they're going to form
the basis of our main variants
calculations that we come to in a moment
want me to arrive that efficient
frontier a second set of inputs is a
covariance matrix so a covariance matrix
is quite important because we know that
when we calculating the variance of the
portfolio we need to consider not only
the individual variants of stocks but
also the covariance between each pair of
socks so what I've actually done is it
will come back to my previous Pritchett
once again are taking these annualized
covariance between each of the pairs of
socks and i've pasted them within this
spreadsheet
ok but what you can see is that the
calculator covariances from before that
was only the triangle that that was only
the top half of the matrix in my
calculations the bottom half of the
matrix was blank
however that's not a problem because
what we know is that the bottom half of
the matrix is just a replication of the
top half
so for example we've got this still hear
that i'm going to highlight in blue
these two cells are both the covariance
between the HP shares and fosters groups
is therefore we would actually expect
that those two covariance values are
exactly the same
it's exactly the same covariance is
doing the same calculation
so I'm highlighting and eli now this is
out second important set of inputs
because again if we think about
portfolio theory if we think about main
variants efficiency and if we think
about calculating Sharpe ratios with
measures of expected returns and to
calculate expected returns all we need
to know is the weight of a stock and
expected return of age stock and we need
more importantly a measure of portfolio
variance and to get portfolio variants
that's why these this covariance matrix
is actually quite important
so what I'm actually doing here what
this covariance matrix pool to what
exhales going to calculate for us is
along the side here about whiting so
that's going to be my exposure to each
of the seven stocks within my feasible
set of investments and the calculation
along the bottom he if we actually added
all those values together what it's
actually doing is that longhand
portfolio variance formula whereby we've
got seven saw stocks so we would have
seven variants terms and we would have
42 covariance terms if you think of that
portfolio variance formula if you think
of expanding it out 27 different assets
and basically what we're doing is that
we're taking for example the weight of
bh piece stocks squared x the variance
of the HP stocks plus he would got
weight of fosters group x weight of bhp
x the covariance between Foster's group
from bhp actually doing that all the way
along and calculating that that variance
formula now obviously if we take the
square root of the variance so the sum
of all these cells actually is the
variance of s7 asset portfolio it's
going to differ as we change these white
exactly seekers as we have different
exposures the various does change and in
this cell here I've got the standard
deviation of that portfolio which is
just simply the square root of the
variance finally my main access return
mean excess return is calculated as the
white of age stock within the portfolio
x the expected return of that stock
and finally down here the slope the
slope is my shot great show now you'll
recall Sharpe ratio is calculated as
excess return divided by the standard
deviation of returns so this formula
here is simply just excess return on the
portfolio divided by the standard
deviation of that portfolio so I've
never got my spreadsheet set up and I'm
ready to drive the efficient frontier if
i move down that's actually what this
our table the efficient frontier table
and enables us to do so what I'm
actually going to do is I'm going to
delete the information is currently
contained within that table and I'm
going to go about calculating it for
myself
ok so the way I do that is i'm actually
going to use a little application in
Excel known as sober so if you click on
data if you've added in Samba it should
appear hehe under the analysis menu in
the data ribbon if you haven't added it
you can simply do that by clicking file
options add-ins let go and make sure
that the solver writing is taped this
boxes to it
ok so when I six of what i'm going to do
is is that if we think about the
efficient frontier we think the
efficient frontier has two really
important points
the first is it starting point and if
you visualize the efficient frontier the
starting point is actually the minimum
variance portfolio so the starting point
of the efficient frontier is that
portfolio that has the lowest risk of
any combination of these seven stocks
that time for my feasible set of
investment so the first thing I actually
want to do is identify what that minimum
variance portfolio looks like so what
security selection gives me that choice
so in order to do that what i'm going to
say here is that I want to make some
changes and what I'm actually going to
be changing is the waiting allocated
across stocks and I want to make changes
such that this still he a 27 which is my
standard deviation of returns is
minimized
ok so my objective minimize variants i'm
going to minimize variance by changing
these values he which are my weightings
and finally last
all amassed whether I've got any
constraints i want to consider now what
I'm actually going to say in this
particular case is I'm going to impose a
constraint where all the weights must be
greater than or equal to 0 this is known
as a short sale constraints i'm actually
not allowing short-selling short-selling
would result in negative positions in
some of these stocks so long only on is
all i'm going to allow here the second
constraint is very important
that's that's still a 25 equals 1 l.a 25
this is the sum of h of the weights
obviously all the weight of a portfolio
have to add to one that's an important
one very necessary to include so if I
select solve what Excel will actually
then do is calculate for me
the whites of investment in HD stocks
that will derive the portfolio that has
the minimum variance so what I'm
actually going to do is I'm going to
copy those weights and place them down
here in the table Excel is also given me
the mean excess return the standard
deviation of returns and the Sharpe
ratio for their portfolios i'm going to
copy that data I'm going to right-click
paste special and values because i'm
actually pasting data that's calculated
viral formula so pay special values and
that information has been transported he
into my efficient frontier table that's
the first point very important one
my minimum variance portfolio which is
basically forms the starting point for
calculating the efficient frontier
obviously the second very important .
along that efficient frontier is the
optimal risky portfolio the optimal
risky portfolio is the portfolio that
has the maximum Sharpe ratio of any
portfolio of stocks that we can actually
create so in theory it should be optimal
risky portfolio we should only really
invest if we're considering a a mean
variance efficiency world if we believe
that the captain is entirely valid as a
measure of expected returns this should
be the security selection decision that
we make so the way I'm going to actually
calculate that optimal risky portfolio
is going by going back in to solve our
but in this case I've got a new
objective what I'm focusing on now is
action
in the slope or the Sharpe ratio and I
want that to be maximized so I'm going
to identify what waiting of stocks can
maximize the Sharpe ratio click solve
and excel is calculated those waiting
for me i'm going to transform transfer
them across into my table and I've got
those values now I've actually got down
here on the rod i'm actually trying to
to build the efficient frontier actually
trying to develop and show what it looks
like
so in order to actually build the shape
of the efficient frontier it's not
enough simply to have the minimum
variance and the optimal risky portfolio
we need to have a have a view of what
the different portfolios across that
efficient frontier look like so in order
to do that what we do is we begin by
filling in values along here where we
give particular mean excess returns
between the minimum variance and the
optimal risky portfolio and we're going
to us all over to calculate what would
be the portfolio that falls on the
efficient frontier that actually has
that expected excess return so the way
to do that is there is no science to
this but what i do i've got four points
here i'm going to create roughly equal
breakpoints i'm going to add at as a as
a rough guideline some somewhere in the
ballpark of about . 3.42 each one so
I've got roughly equally spaced apart he
definitely doesn't have to be precise
though i'll do the same over he ok
and as i said i'm going to fill in the
shape of the efficient frontier by
finding which portfolio is actually fall
on the efficient frontier with these
given main excess return values
ok so what I'm going to do is I'm going
to go back into sore but I'm sorry too
sober now in solver i'm still want to
maximize the shoprite show okay because
the efficient frontier are all those
portfolios that have the highest return
for a given unit of risky
and another way of expressing that he's
actually they are the portfolios that
have the maximum Sharpe ratio forgiven
expected return i'm still maximizing my
shop right now I'm still doing it by
changing my weightings but here I'm
gonna actually add an additional
constraint my constraints going to be
the portfolio has to be constructed such
that the mean excess return equals to
these three percent value of what he
added that can trust right and i'm going
to solve this
behold of got those weightings that
again i can paste and as expected the
main excess return here is three per
cents when it should be placed those
values in and I've identified this
particular point along the efficient
frontier and we're going to show you
really quickly a minute or so I'm going
to generate the arrest of the sufficient
fronty to show you what it's going to
look like so just bear with me as I our
work through this particular process
oops so all i need to do is as I go
along i'm just going to edit this
constraint because on their changing my
constraint expected return to bh of
these points that I've created
ok so there's that portfolio
tonight do it again okay there's the
waiting's of that portfolio change it
again
ok is the wedding that 13 to go
ok there are those Whiting's there
and what I've done in front of your eyes
is actually calculated for you
the efficient frontier now what this
efficient frontier actually represents
is in a main variants world so if we
apply the principles of portfolio here
is a spaz by harry markowitz the Nobel
laureate what we've what we are now
saying what we are creating is actually
what should be theoretically according
to portfolio theory the optimal security
selection decision
ok so if there are no marker frictions
if the captain was able to perfectly
forecast expected future returns then
I'll optimal security selection
decisions should actually be these
optimal risky portfolio and then we
would make an asset allocation decision
by adding or subtracting the risk-free
rate from that so populated my table
whatever whatever is actually done for
me down here is it's actually calculated
and efficient frontier
ok my fishing fronty he is going to be
based on the capital allocation line
along the side there and the standard
deviation of returns
ok so if i wanted to to calculate that i
can just do so by creating each of those
those values
second of all so that's the capital
allocation line then the efficient
frontier is going to be the relationship
whereby I've got the excess returns also
the expected returns of each of these
portfolios on the y-axis and the
standard deviation of returns on the
x-axis that's something we'll save for
later video but in the interest of time
we're going to finish it up there
what we've now shown is how we can
actually can derive the efficient
frontier from first principles using
real financial data