Generating the Variance-Covariance Matrix

This is a follow-up video to a video posted previously by Dr. Colby Wright explaining how to execute mean-variance portfolio optimization in Excel. This video demonstrates how to generate the variance-covariance matrix, which is necessary in order to calculate the portfolio standard deviation. Dr. Wright also demonstrates how to transform the var-cov matrix into the correlation matrix.

---

Closed Caption:

hello everyone this is dr. right again a
couple months ago I posted a video on
youtube about how to execute portfolio
optimization in Excel
the video was intended to be helpful but
by no means comprehensive I've been
surprised there have been a lot of
people have looked at the videos most
people have commented that they have in
fact been helpful
however I've had several people email me
and ask me how exactly i created the
variance-covariance matrix which you can
see right here on the screen now
remember the variance-covariance matrix
was critical in estimating the portfolio
standard deviation
it turned out that in order to estimate
the portfolio standard deviation for
instance this is the equally weighted
portfolio
we estimated a portfolio standard
deviation of 4.46 two percent the way
that we did that was to take the weight
matrix or i should say the weight vector
we took the transpose of that multiplied
it by the variance-covariance matrix and
then multiplied it again by the weight
vector and that's how we got the
portfolio standard deviation
everybody seems to be pretty clear on
that process now people would like to
know how exactly we created the
variance-covariance matrix and so I'm
here today with the Supplemental video
simply hoping to show you how to create
the variance-covariance matrix and
perhaps also show you how to create the
correlation matrix
let me begin with just a little bit of
instruction here
let's just start with some powerpoints
if you don't mind
we know that the Sigma matrix which is
our variance covariance matrix is AK by
K matrix so if we have k assets and we
have n monthly returns of those k assets
in the variance-covariance matrix is
going to be a K by K matrix now there
are at least two ways to generate the
variance-covariance matrix in Excel you
can create it using the data analysis
function in Excel
I don't recommend this my persistence
use matrix algebra and the array
functions
- try to create the variance-covariance
matrix now recall that the covariance
between a set X and asset why
mathematically is defined thusly you
take each X observation and subtract out
the average return to a set X you take
each corresponding Y observations and
subtract out the average return to a set
Y so literally for every month of your
data set
you have to take the return to X and
subtract out its average and multiply
that by the return - why - its average
so every month you'll have the product
of those differences and then you need
to add all those up and then divided by
the number of months that you have in
your data set so to generate the
variance-covariance matrix what we want
to do is we want to begin with the end
by K data of monthly returns
what i mean by that is if you come over
here to my excel file i began with raw
data that looked like this you can see I
have the returns to all of my assets
here to my eight assets
this is simply the monthly returns from
december of 2008
oh sorry i should say the monthly
returns starting in january of 2007
going through december of 2011
if we add all those up so I'm just going
to count the number of returns that i
have here and you can see that i have 60
returns for each of these assets
so this right here
that represents my n by K data set of
monthly returns
so the first thing that we need to do is
we need to calculate the average return
for each of the k assets
so you can see down here at the bottom
what i've done is i've simply calculated
the average very easy to do in Excel
obviously you can just use the average
function
copy and paste that across the now I
have all my averages
the next step is to create what I'm
going to call the excess return matrix
the way that you create the excess
return matrix is for each monthly return
subtract the average return for the
respective asset for instance to create
the excess return matrix i'm going to
take this which is the return to the S&P
500 the spyder etf and that was the
return in january of 2007 you can see
over here
what we need to do is subtract from that
the average so right there is the
average we need to subtract that away
so over here i'm going to create the
excess return matrix now I've already
created it but i'm going to go ahead and
delete it out just so i can show you how
to do it
so what I'm going to do is I'm going to
take the return of the S&P 500
- it's average return which is right
there
I'm going to lock in that row number and
i should be able to just copy and paste
that across and down
and i'm not going to worry about the
formatting but this right here now
represents our n by K excess return
matrix and I don't need these things at
the bottom because my dad actually ends
right there
so again this right here is an N by K
excess return matrix where I've taken
every monthly observation the return to
every asset in every month and I'm
subtracted out the average return once
you do that and I'm going to find that
define that matrix is simply capital X
state which represents excess return
then in order to get the
variance-covariance matrix what you need
to do is take X transpose X and then
divide by the scalar and which in our
case is going to be 60 because we have
60 observations
if you look at this formula right here X
transpose X is actually doing this part
of the formula for each of the pairs
ok so the X transpose X is taking X i
minus x bar x y i minus y bar for every
month and adding it up and then the last
thing we need to do is divide by n so
ultimately in order to get our variance
covariance matrix we need to take X
transpose X and then divide every
element in that matrix by n so let's go
do this in our data set
ok so we're going to execute this in two
steps first off I'm going to take X
transpose X so i'm going to highlight an
eight-by-eight region because again X
transpose X is going to result in a k by
k matrix
I'm gonna hit em multiplied we're going
to transpose and what are we going to
transpose we're going to transpose the
excess return matrix
close the parentheses and then we're
going to multiply it by itself again
come up to the top highlight the N by K
excess return matrix close the
parentheses and remember any time you're
doing matrix multiplication
you always hit ctrl shift enter now
that's X transpose X what we then need
to do is divide all of these elements by
the scalar n which in our case is 60
months that's how many months worth of
returns we have now this is not
necessarily matrix multiplication this
is just element by element division
so all I'm going to do is this i'm going
to highlight the eight by eight region
then I'm going to come up here and
highlight this and i'm going to divide
it by 60 so I highlighted my K by K X
transpose X matrix and I'm simply
dividing it by the scalar 60 i am going
to hit ctrl shift enter to tell it to do
array math and you can see we now have
our variance covariance matrix which we
used up here to determine our portfolio
standard deviation you can do just a
quick
I ball check of the first few numbers in
our variance covariance matrix here and
see that it is in fact identical
now one other thing that I wanted to
show you the variance-covariance matrix
is necessary for estimating portfolio
standard deviation so we need it and
it's helpful
unfortunately it's very hard to make any
useful information TSA's from the
variance-covariance matrix
in other words it's very hard to look at
this variance covariance matrix and
determine what the relationship is
between the S&P 500 and gold that number
simply isn't very meaningful
it would be better if we could have a
correlation matrix so i also want to
show you how to do the correlation
matrix
now remember
that the correlation between asset axe
and a set Y which we represent using row
is determined by taking the covariance
between the two assets and dividing it
by the product of the standard
deviations
now we have the variance-covariance
matrix
in other words we have a matrix that's
filled with all of the covariances in
the variances
what we don't have is a matrix that has
the products of the standard deviations
so transforming the variance-covariance
matrix into the correlation matrix
merely entails figuring out how to
create a matrix that has all the
products of the standard deviations and
then just dividing our variance
covariance matrix buy those products of
the standard deviations
we begin by defining a K by 1 vector of
standard deviations thusly and i'm going
to call it'll be just to stand for
deviations
if you take d D transpose you would have
K by 1 multiplied by a 1 by K which
would actually give you a K by K matrix
and the matrix would look like this
the first element would be Sigma 1 Sigma
1 the product of the standard deviations
in other words that's the variance for a
set one
the second element in the first row
would be Sigma 1 Sigma 2 in other words
that's the product of the standard
deviation of asset one and asset to the
third element in the first row would be
Sigma 1 Sigma 3 which is the product of
the standard deviation of asset one
asset 3 i'm not going to go through the
tedium of the rest of this matrix but
you can see that D D transpose indeed
contains all of the products of the
pairs in terms of their standard
deviation so it pairs all of our assets
and multiplies their standard deviations
well that matrix is going to be very
helpful because remember in order to get
correlations we have to take covariance
and divided by the product of the
standard deviations well now we have a
matrix that is filled with the products
of the standard deviations
so if Sigma is our K by K variance
covariance matrix and if capital D is
our K by K matrix containing the
products of the pair of standard
deviations
then the correlation matrix is simply
Sigma divided by D important note this
is not matrix multiplication
you would never use that symbol if it
was matrix multiplication in fact what
we're going to do is element by element
math in other words we're going to take
the first element the element in Row 1
column 1 in the variance-covariance
matrix and we're going to divide it by
that element right there
we're going to take the element in the
first row second column of the variance
covariance matrix and we're going to
divide it by that same element in this
matrix if we do that
think about what that will do the the
second element in the first row of the
variance covariance matrix will be the
covariance between asset one an asset to
we're going to divide it by the product
of the standard deviation of asset what
an asset to you will end up with the
correlation so literally if we take the
variance-covariance matrix and we
element by element / everything in this
matrix will get the correlation matrix
ok so we begin by defining our K by 1
vector of the standard deviations and
you can see here we have our eight by 1
vector and what we're going to do is
we're going to take that and multiply it
by its transpose so down here i'm going
to highlight an eight-by-eight area and
i'm going to do matrix multiplication
I'm going to take the vector and
multiply it by its transpose
closing the parentheses and then one
more reminder that any time you do
matrix multiplication
you need to hit ctrl shift enter
so we do that so we now have this matrix
that i showed you in the PowerPoint
slide this is the matrix that contains
all of the products of the parents
standard deviations
so that's going to become the
denominator as we try to figure out the
correlations
so what we need to do is take each
element of the variance covariance
matrix that will be the numerator and
divide it by the corresponding element
in this new capital D matrix that we've
created
so here's our variance covariance matrix
again we're going to take every element
and we're going to divide it by the
corresponding element in this capital D
matrix remember this isn't going to be
matrix multiplication this is element by
element division
it's going to be an 8 by 8 matrix when
all is said and done so we highlight an
area that's eight by eight
and all I'm going to do is highlight
that divide it by this capital D matrix
and even though it's not matrix
multiplication it's still array
it's still an array function in excel so
anytime we're doing an array function
you still have to get control shift
enter now once you do that and i'm just
going to put some labels down here just
so it's easier for us to read
ok now i can tell you that we did this
right just by eyeballing it
and the reason that I know we did this
right is that any time you do a
correlation matrix you should get ones
along the diagonal
we know that correlation we know that
row is bounded by negative 1 and 1
positive perfect positive correlation
would be represented by the number one
in other words any asset that is
perfectly positively correlated with
another asset should have a correlation
of one
well we can see that when we take the
correlation of spider with spider it has
a correlation of one that makes perfect
sense because if you look at the formula
we're taking the co variance Sigma X X
in this case it would be the covariance
of spider with itself
well the covariance of spider with
itself if you look at the formula for
covariance up here
the covariance of an asset with itself
is just it's variants you wouldn't have
X I or why I it would be SP why and SP
why
so if you're doing the same asset
covariance becomes variance so in the
numerator you would have the variance of
SP why
and also in the denominator you have the
product of the standard deviations which
would be the standard deviation of SP y
times the standard deviation of SP y
which would be the variance hence you
have the variance of SP y over the
variance of SP y which gives you one
so we see along the diagonals we see the
ones since we see the ones we know that
we've done this writer at least we can
have some level of assurance that we've
done it right now why do I care why am i
showing you how to do the correlation
matrix
like I said you can make a lot better
inferences you see up here for instance
we looked at the covariance between SP
why and gld you cannot make any sense
out of that number it's a small number
but you can't make any inferences from
it
however with the correlation we know
what the bounds are we know what the
parameters are
we know that the correlations between 1
and negative 1 and a correlation
approaching zero means there is no
correlation between the two assets we
can see that the correlation between
spui and gld is . 11 that's pretty close
to zero almost no correlation between
gold and the S&P 500
interestingly enough this is the
emerging markets etf
it's got a correlation . 8 with the SP
why
so what that tells you is over the least
the last five years for the five years
that i'm studying
you may have thought that by putting
your money into the emerging markets etf
you are broadly diversifying away from
the S&P 500 and US stocks not so you can
see that the emerging markets etf is
highly correlated with the S&P 500 the
S&P S&P midcap and the S&P smallcap and
those are all highly correlated with the
Eva which is Europe Far East and in
Australia
if you really want to get
diversification and you have your money
in the S&P 500 you can see quite clearly
that treasuries corporates and gold at
least in the data set that I'm analyzing
our where you are going to get that
diversification
so I show you this not because you need
it you don't need it to figure out the
portfolio standard deviation
but it sure helps you to intuitively
interpret the results that you got here
well i hope this is helpful if you have
any more questions feel free to email me
I can't promise that i can make more
videos by at least thought i would throw
this out there to try to help you