Tutorial: Constructing Efficient Frontier using Markowitz model

This tutorial shows how to calculate portfolio efficient frontier comprised of common stocks. It relies on Markowitz model and mean-variance optimization. All calculations are performed in Microsoft Excel with the help of Solver add-in in MS Excel. The data and examples are posted here: http://valexeev.yolasite.com/teaching.php

---

Closed Caption:

hello everyone the tutorial on
constructing the portfolio efficient
frontier using the workers model the
template or for construction division
frontiers available on website in a tad
markets
the problem is already outlined and some
of the inputs have already been entered
and some of the formulas have already
been presented in this template as well
all you have to do is fill out the green
shaded areas for the annualized expected
returns and the annualized
variance-covariance matrix in here are
the steps to arrive to the efficient
frontier are outlined to your right
so the first step we have to make sure
that the formulas are correct and the
expected returns and the variance matrix
are in annualized forms so you can see
that here we'll put our percentage
expect returns and he will be our
annualized variance-covariance matrix
all variants terms are simply the
weighted sum product of from the weights
determined for each stock which will be
determined by the software times all the
covariances of stock one for every other
asset if you look at the second term
similarly it's the way that some product
of the weights for each stock by each of
its covariance terms both all other
assets the variance of the portfolio is
simply the sum of the various terms and
the standard deviation of the portfolio
is the square root of the variance of
the portfolio for the return terms it's
a little bit easier is just the the
weighted expected return for each of the
stock with the return of the portfolio
equal to the sum of these return terms
essentially the return on the portfolio
is the weighted average of its
individual returns where the weights are
determined for each of the asset in the
petroleum so our next step is to
generate the expected returns and to
generate the various securus matrix the
data for this example I'm going to use
the data that really have I have five
stocks the data runs from fourth of
generator 2011 and until March 2014
these are the daily return data so first
thing I will calculate my average daily
returns and to do so i'm going to use
the formula average in Excel and select
the stocks notice that we don't need to
use the market index for the markets
optimization model so this is my average
daily return i can simply copy the
formula reference for other stops now
the requirement is that we have the
annualized returns so my annualized
returns simply going to be the daily
return times the annual ization factor
and i'm going to use 215 as the number
of trading days in any particular year
on average these are my annual average
returns for the five stocks you can see
that over the period 2011 2014 on
average every year each of my five
stocks have given me quite nice returns
the lowest was fourteen percent and the
highest was 18 . seven percent
note that my data starts from $MONTH
2011 the reason for that is that i could
obviously get the longer time series of
my data but i wanted to avoid the years
2007 2008 2009 partially 2010 as the
years mark but the global financial
crisis and the consequence of that
crisis the volatility in the markets was
quite high and I do not believe that
this successful ability will be evident
in the forthcoming years so this is the
reason why exclude the years from my
analysis when constructing the
historical averages for the markets
these average returns could now be
copied into my markets model set up
sorry
ok
yeah
I can actually make them in that
percentage form with a few decimal
points
this is a much better presentation of it
now we're off to constructing
variance-covariance matrix and the
quickest way to construct the
variance-covariance matrix is through
the use of the data analysis plug-in and
there's a option for the covariance
clicking ok and for the input range
I just input all of my columns with the
returns for all of my five stocks that
are analyzing you could include the
labels if you wish but if you do include
the labels make sure that you click on
the labels in the first row in your data
since my selection does not contain the
labels i'm going to school that for now
the output well output will be just
below my average returns that I had the
beginning so i'm going to put it here
click ok this is my variance covariance
matrix again this matrix is in its daily
form first thing you have to do is to
make sure that this data is converted
annual presentation again I'm using a
factor of 215 number of trading days in
any given year on average and i'm going
to copy it for all of my stocks that i'm
considering now you can see that this
matrix is not complete as there are some
missing elements in the upper right
corner so I have to fill out these
values and since the variance matrix is
symmetric matrix i'm simply going to
make sure them copy the correct numbers
as I go along
yeah
yeah
ok so the matrix below the selection
that I've you see on your screens is the
annualized variance-covariance matrix
i'm going to copy this message back into
my various screens template is only you
actually don't need to see all the
numbers and so we can reduce the amount
of zeros that we observe by making it 64
for decimals whatever you prefer
now we actually ready to run solver for
the first time and get our first point
on the efficient frontier let me make it
a little bit smaller so i can see the
graph perhaps move the graph a little
bit up i guess the first . that could be
plotted is well sorry let me just go in
solver and explain what is going on so
the markets model set up sets the
objective and my objective is the cell
I 14 which refers to the variance of the
portfolio you can as easily set the
objective equals to the i-15 which is
the standard deviation of the portfolio
it wouldn't make any difference
the objective is the variance and i
would like to minimize the variance by
changing the cells b3 f3 if you look at
the b 33
these are my weights so the software
will alternate and attempt to try
different numbers in this particular
range of cells which are the weights of
the assets in the portfolio and come up
with a solution will give you the
minimum value in I 14 which is the
variance of the petroleum
I didn't pose some additional
constraints my first constraint is I 16
equals 2 j16 the reason for that
constraint is that the return on the
portfolio that turns out to be will have
to be equal to the target return that I
wanted to be so in order to trace the
efficient frontier i will have to set a
return to 16 15 14 13 and so forth and
get the portfolio for that level of
return with the minimum variance so this
is why we have that particular
constraint so as you can see will be
changing the target quite often in the
range from the minimum return that we
have which is 14 . sixteen percent to
the maximum return on the asset which is
18 . seventy-seven percent so these are
the limits of what we can get in the
portfolio constructed with our short
sales based on the five stocks of this
return collector sticks my other
constraints are that waits cannot be
greater than 1 and weights cannot be
negative so essentially this constraint
limits me from short sales my last
constraint is h 3 which is the total for
the sum of all the ways the assets in
the performance have to sum up two
hundred percent these are the only
constraint that make so before running
sober let me set the target first i'll
set the target to the lowest return
available among all of my stocks the
answer that i would expect solver to
give me will be hundred percent in the
stock 5 which has 14 points six percent
that will be the lowest point on the
efficient frontier so i will go back to
silver run this problem now
and the sport says that it found a
solution and all constraints and
optimality conditions are satisfied i
will keep the solution agree with silver
that this is the correct solution press
ok as you can see that the answer that
we got is actually what we expected in
the beginning all other stocks will be 0
except the stock 5 which would invest
$MONEY hundred-percent it has expected
return of 14 . sixteen percent and thus
hundred percent that star equals to 14.6
percent for the target portfolio that we
achieved i've already created a template
for you that you can simply copy and
paste special into the output and your
first point appears on the graph already
so i will try the point with the highest
return 18.7 again the solution that I
expect from the soldiers to have hundred
percent invest in stock for $MONEY and
zero percent every other step running
silver with exactly the same set of
constraints clicking on solve make sure
that solution
can be established and the solution is
as expected hundred percent invested in
stock for a paste special and I got my
highest point so this is the point with
standard deviation 19.7 line and
expected return of 18.7 where we
invested $MONEY hundred-percent stock
for this is the one where we have
hundred percent stock five so what you
can do now you have to populate your
graph with the intermediate points i
guess the next . we can try his
seventeen percent because it looks like
it's somewhere in the middle between
these two points so i'm going to put
70-percent go into the silver run the
solar get the solution copy my solution
into
sure
so we got an intermediate . so now we
can find intermediate point between this
intermediate point and the lowest one
and it looks like it's going to be 15
and a half percent so i'm going to
change my target to 15.5 percent runs
over again to get the point
the optimal our efficient particular
case I got the solution copy solution
and paste special values only got
another point here we're going to keep
repeating this and change the target
every time we need a new . for example
it looks like there's a lot of empty
space here so we would want to have a
point somewhere here around eighty
percent let's see what happens when we
set the target for the portfolio equals
to the eighteen percent solar found
solution we copy the output percentage
breakdown and statistics for our
portfolio our plotting area and we have
achieved that particular point I could
try . 16.3 it looks like between these
two so let's try 16.3 go to data silver
around the solution take the output from
this silver run paste that into our
spreadsheet and we got another .
so all we have to do is you you continue
to populate your graph with more points
and once you have sufficient number of
points perhaps 15 or 20 points you will
be able to perform further analysis