Excel tutorial: calculating covariance and correlation of stock returns

hello and welcome in this video we'll take a look at how to compute the correlation and covariance for two different stock returns so let's say we have one company Tesla Motors and another company Exxon Mobil Corporation so the reason I chose these two companies is because you would expect that their stocks would give returns in negative correlation that means if Exxon does well maybe Tesla won't do well and if Tesla does well maybe Exxon won't do well so I'm curious to see if that is reflected in the stock prices for the last one year so what I do is I go to Yahoo Finance and I if you just go to TSLA just enter TSLA in this box here and you'll be brought to the Tesla Motors web page and if you go to historical prices you can actually select the prices for the last one year I am choosing January 1st 2014 through January 1st 2015 and I'm choosing monthly returns and if you click get prices you will get the monthly returns data which you can download to a spreadsheet and you can do the same thing with Exxon as well code historical prices choose the dates choose monthly and get prices and you'll get the prices downloaded here is a Exxon data downloaded into a comma separated value or CSV file here is a Tesla data downloaded to a CSV file and you can copy/paste both these data sets into a common file for analysis now what we will do is we'll use the adjusted closed which is the closing price of the stock Exxon stock in this case after accounting for any dividends and stock splits and so on and likewise for Tesla as well we'll use adjusted to close so what I'll do is I'll first hide all these other columns so that we can have some more space the first thing I want to compute is the returns and the way I compute the return is if you look at this this is in reverse chronological order so December first followed by November 3rd and then October first so what I can do is I can take this start price divided by the previous start price minus 1 and that will give me the return over the previous month I can convert this to a percentage if I want and increase the number of decimal places to 2 so it shows up as a nice percentage I'm just going to increase the screen area here by minimizing the ribbon and now if you drag this down all the way here you will get the returns I you can drag it even further down to this column here but then because there is no return available here it is going to just show 0 I am going to delete this for now and likewise do the same thing here this divided by this minus 1 and then you can convert that into a percentage increase the decimal places and double click here and delete the last item so now that you have the returns the first thing I want to calculate is average returns the average returns are the average of all these returns here and likewise the average of all these returns so on average exxon has returned 0.34% on a monthly basis last year the monthly average return for Exxon is 0.34 percent and the monthly average return for test lies 2.8 percent the next thing I want to compute is the variance of these returns the variance is computed by the formula VAR p within paranthesis all these values and likewise here all these values here so the variance is basically a measure of the spread of the returns how widely spread the returns are another way to compute variance is to actually find out the spread and find out the square root of the spread so you can just do diff - SQ d just and four different squared so what you can do is can take the return here and press f2 here I'm just going to put it in parentheses minus the average return and I'm going to put this in f4 so that there's that value is always there and then you can do a square of that and you can do that for all these returns here break into an average of these squares so the sum of all of these divided by by eleven and that should give you another way to measure your variance so with that out of the way the next thing we want to compute is the standard deviation it's basically the square root of the variance and you can think of that as a way to kind of reduce the variance to a comparable percentage like your average so here what we're doing is we're squaring it right we are taking this difference between this return and the average return and squaring that now yes adding up all the squares and we are a virgin squares but since there's already a square here we want to find the square root to kind of reduce the variance to a more comparable figure and that's what standard deviation is so it can be done using as te-de VP within parentheses all these values here another way to compute the standard deviation is just to find the square root of the variance so you get the exact same value again let's compute the standard deviation which is nothing but the square root of variance and here you can actually indicate the standard deviation as a percentage and now it gives you a measure of the extent to which the stock returns deviate from their mean from time to time now we are ready to compute the covariance between the stock returns for Exxon and Tesla the covariance is a measure of the extent to which the stock returns between these two companies very together so let's compute the covariance between the two Starks take these returns and these returns here so you provide a ch3 2 H 13 and P 3 2 P 13 you provide both the ranges complete the paranthesis press enter so that's the covariance between the two now it's also possible to reduce covariance to a percentage fugle correlation of returns is that percentage the first array is this and the second array is this so there's actually a positive correlation between the returns for Exxon and Tesla and that is 39% so in fact you can take add a percentage sign to correlation thirty nine point five three percent another way to derive the correlation is by dividing the covariance by each of the standard deviations if you do this you will get the exact same figure and there it is so that's another way to arrive at the correlation so that's it for now I hope you like this thank you for watching

Channel: Codible

Views: 256,689

Rating: 4.8829374 out of 5

Keywords: Microsoft excel, tutori, Excel tutorial, stock returns, correlation, covariance, finance, Covariance And Correlation, Stock

Id: bpnOP0oFSpQ

Length: 8min 0sec (480 seconds)

Published: Mon Jan 05 2015