Calculating the Variance Covariance Matrix using stock Prices

okay we want to estimate a variance covariance matrix variance covariance matrix useful for portfolio calculations also very useful for making valued risk estimations and first thing we need to do here is consider the Depot T assets so we have stock prices for General Electric Microsoft Johnson Johnson Kellogg's Boeing IBM and we have annual data between 93 and 2004 so we have 12 observations and we want to estimate the returns first thing we can do take log difference so the the next year divided by the former year the previous year gives us the return from 2000 and 1993 to 1994 and we pulled that down this approach I took from the Simon Dunne spreadsheet I took from Simon Venegas textbook financial modelling it's a really good book and he had lines and different ways of estimating variance-covariance portfolio option Terry's are a very good book okay we then proceed to estimate the average return over the nine eleven observations so state average and the average value for average return continuously compounded return for general electric's 23.6% we can fold out a pulse likewise standard deviation you can work out for the single assets eventually want to set aside o for the portfolio standard deviation 31 32 point 1 7 variance likewise and we can just drive across ourselves change this to percentage perhaps okay now below we need to press to estimate the excess returns we take to return it under given here and we subtract away the average average observation which is this and we do f4 one can leave and we can pawn this cross and then if we put an f4 if he's hit f4 in each of these references we can lock this our reference so f4 means that when we pull the row down in a moment we can in each instance subtract lock deserve reference to the average put the of the returns so we take these six cells in the roll and just pull down and if you go back and have a look we can verify that this is to return from in January 1996 and we're subtracting my the average value likewise we take the observation here it's IBM return 1994 subtract away the average likewise here it's the General Electric return subtract away the average value so we have the excess returns they go from excess returns them to calculating to calculate the variance covariance matrix we take our excess returns and we take the transpose so initially we say take the transpose open bracket and mold transpose open bracket again transpose it the matrix closed brackets and we're multiplying by the matrix again closed brackets and we're dividing by n minus we're dividing by n minus 1 observations so we have 11 excess returns we divide by 10 and shift control return then we take the entire range to 6 by 6 matrix and back up again control shift return and we have our values we can convert to percentages and we can examine we can look at the values here and compare and what we notice is and they are the same so in fact if we take this range of values to 3.6 3.6 to five point seven to five point seven to eight point nine six to eight point nine six to one point eight four to one point eight for what becomes clear is that we have on the diagonal diagonal of this matrix we have two variants for the asset returns and on the off diagonal we have two covariances which are symmetric so this value corresponds to this value the covariance between Microsoft and GE seven point five eight but likewise the covariance here is captured in this symmetric matrix likewise this will be equal to this and so on so this is our variance covariance matrix and it can be used for working out details a portfolio construction working out the variance the portfolio useful for and valued risk type calculations

Channel: Brian Byrne

Views: 178,750

Rating: 4.8624077 out of 5

Keywords: Variance Covariance matrix, Portfolio Management, Portfolio Theory, Matrices in Excel, Variance (Literature Subject), Matrix (Literature Subject), Covariance Matrix, Statistics (Field Of Study), Finance Theory, Simon Benninga, Financial Risk Manager, FRM, CFA, Microsoft Excel (Software), Finance (Industry), Market

Id: V10ZoFvhy6s

Length: 7min 33sec (453 seconds)

Published: Fri Mar 06 2015