Generating the Variance-Covariance Matrix

hello everyone this is dr. Wright 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 who've 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 four point four six 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 a 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 a K by K matrix so if we have K assets and we have n monthly returns of those K assets then 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 purpose is to use matrix algebra and the array functions to try to create the variance covariance matrix now recall that the covariance between a set X an asset why mathematically is defined thusly you take each X observation and subtract out the average return to asset X you take each corresponding Y observations and subtract out the average return to asset 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 to Y minus its average so every month you'll have the product of those differences and then you need to add all those up and then divide it 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 N 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 8 assets this is simply the monthly returns from December of 2008 or sorry I should say it's 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 and now I have all of 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 to the S&P 500 - its 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 gonna 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 data 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 returned to every asset in every month and I've subtracted out the average return once you do that and I'm going to find that define that matrix as 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 n 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 pair's okay so the X transpose X is taking X I minus X bar times 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 okay 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 8x8 region because again X transpose X is going to result in a K by K matrix I'm going to hit M multiply 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 anytime you're doing matrix multiplication you always hit control 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 gonna do is this I'm going to highlight the 8x8 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 control 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 eyeball 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 of airy 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 a set X and a set Y which we represent using Rho 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 and the variances what we don't have is a matrix that 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 or variance covariance matrix by 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 little D 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 asset 1 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 1 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 1 and asset 3 I'm not going to go through the tedium of the rest of this matrix but you can see that DD transpose indeed contains all of the products of the pair's in terms of their standard deviations 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 divide it 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 paired 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 gonna do is element by element math in other words we're going to take the first element the element in Row one column one in the variance covariance matrix and we're going to divide it by that element right there and 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 and asset two we're going to divide it by the product of the standard deviation of asset one an asset 2 you will end up with the correlation so literally if we take the variance covariance matrix and we element by element divide it by everything in this matrix we'll get the correlation matrix okay so we begin by defining our K by 1 vector of the standard deviations and you can see here we have our 8 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 8 by 8 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 control 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 paired 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 8 by 8 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 okay 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 anytime 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 1 in other words any asset that is perfectly positively correlated with another asset should have a correlation of 1 well we can see that when we take the correlation of spider with spider it has a correlation of 1 that makes perfect sense because if you look at the formula we're taking the covariance 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 its variance you wouldn't have X I or Y I it would be s py and s py so if you're doing the same asset covariance becomes variance so in the numerator you would have the variance of s py and also in the denominator you would have the product of the standard deviations which would be the standard deviation of s py times the standard deviation of s py which would be the variance hence you'd have the variance of s py over the variance of s py which gives you a 1 so we see along the diagonals we see the ones since we see the ones we know that we've done this right or 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 s py 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 correlation is between 1 and negative 1 and a correlation approaching 0 means there is no correlation between the two assets we can see that the correlation between s py and GLD is 0.1 1 that's pretty close to 0 there's almost no correlation between gold in the S&P 500 interestingly enough this is the emerging markets ETF it's got a correlation of 0.88 with the s py so what that tells you is over the least of the last 5 years or the 5 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 essent keep SP mid-cap and the S&P small cap and those are all highly correlated with the ephah which is Europe Far East and Australia if you really want to get diversification and you have your money in the S&P 500 you can see quite clearly that Treasury's corporates and gold at least in the data set that I'm analyzing are 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 but at least thought I would throw this out there to try to help you

Channel: Colby Wright

Views: 230,729

Rating: 4.9242797 out of 5

Keywords: portfolio optimization, variance-covariance matrix, dr. wright

Id: ZfJW3ol2FbA

Length: 18min 42sec (1122 seconds)

Published: Thu May 31 2012