stock returns regression in excel

hello and welcome in this video we'll take a look at how to use Microsoft Excel to create a regression between two variables using an example from the stock market so we'll take a look at S&P 500 that will be one of our variables and we'll look at IBM which will be the other variable and we'll plot the returns between standard Poor 500 and I BM in a graph and we'll see how to interpret this result to get started we'll take a look at data from Yahoo that sir will download the data so if you go to Yahoo and type IBM you can go to the IBM you know information page and go to historical prices and you can select the appropriate time interval I'm going to choose January 1st 2013 to January 1st 2015 and I'm going to choose monthly returns get prices download to spreadsheet and you can save your file next go back to Yahoo and Yahoo Finance and this time enter S&P 500 and click on that go ahead and download historical prices for that as well for the exact same time period which is January 1st 2013 through January 1st 2015 for monthly returns get prizes scroll to the bottom and download to spreadsheet now you'll see a number of columns you'll see the date and you'll see the adjusted close and a bunch of other prices like open high low close average volume all you really need is the date and the adjuster close and you can delete all the other columns I have just copied and pasted the data from S&P 500 and IBM into a common spreadsheet and I've dated all the other columns other than the adjusted closing prices and so what I will next do is I need to compute the monthly returns for each of these so I am just going to say to compute the monthly return for a simplify hundred what I do is just notice that the dates here are organized in Reverse chronological order that means the latest date is showing up at the top and the earlier dates are showing up at the bottom so the way you would compute monthly returns is to take the latest months return and divide that by the previous month's return so B 3 divided by B 4 and subtract 1 from that so you will get a return here and you can convert this to the percentage format and include the few decimal places a couple of decimal places and you can just copy this formula by dragging and the fill handle at the bottom right to the right and then you can drag this fill handle all the way to the bottom and please notice that if you drag it to the very last flow you will get an error that's because there is no further column I mean there is no further data in these cells here so you can just delete this for now and work with the remaining data that's available so if you look at this the S&P 500 had a return of minus 4 2% in ass on December 1st 2014 compared to the first week of November 2014 okay so and and so on this having computed the returns we will now draw a graph a scatter plot of the IBM returns plotted against S&P returns so let me just insert scatter and click on this button scatter with only markers and click on select data and you can click on add the X values are SNP data so you can just select all the SNP data here and click on this button again and the Y values are all the IBM data and you can see that the chart is kind of coming in the way of the data tale data cells but that's okay if you know the general location you can select the data and click OK and for the series name you can just give it as np-500 up S&P 500 - IBM and click OK and click ok once again so you now have a basic graph of the returns of IBM plotted against the returns of S&P 500 now I just want to make a modification I just want to make this into a plain graph so there are no lines here and you can also insert some descriptive text you can go to layouts text box and you can say here S&P 500 and move this a little bit to the top and you can insert another one and call that IBM and I'm just going to tilt it so now you have a graph plotting the returns of IBM versus S&P 500 now the next thing you need to do in order to plot a regression line is you just need to click on any one of these points here if you do that all the points will be selected not all points will be highlighted some will be highlighted like this with a four square dots around others will not be highlighted but ignore that and now right-click anywhere on one of these highlighted points and click on add trendline and choose linear in the trendline options and be sure to display equation on chart and display r-squared value on chart as well so if you do that and click close you will see this line here I'm going to rearrange this graph a little bit so that it's a more appropriate and you can see there is some information that's provided here you can drag it to a place where there are no dots so that you can see it better so what you have here is a regression line that kind of gives you a sense for how IBM varies with S&P 500 returns just visually this regression line is sloping upward so that tells us that as S&P 500 returns increase along the x-axis the IBM returns also tend to increase along the y-axis but you can see there's a huge variation in these returns some points are way further away from this line others are really close to the line so that tells us that IBM varies for reasons other than just S&P 500 is variation so all that information is given by this equation right here so here Y represents IBM returns the y-axis and X here represents S&P 500 along the x-axis and what this equation tells us here is that for every increase of one percentage point of S&P 500 returns which is X there is a point 6 7 percent increase in the return for y so that is also the slope of this line here so for every increase in one percentage point of S&P returns there is a point 6 7 percentage point increase in IBM returns what if SNP 500 returns where zero in a month that means if S&P did not move at all in a month what would happen to IBM stock according to this equation then X would be 0 if X is zero then Y is nothing but negative 0.0167 so IBM stock would decline by about one point six seven percent if S&P 500 stayed completely flat between any two months finally you have this r-squared zero point one zero five six so it basically means that about 10 percent or ten point five six percent of the variation in Y which is iBM is explained by S&P 500 the fact that IBM stock is fluctuating is partly explained because the S&P 500 itself is fluctuating and partly because they may be specific things happening within IBM or other factors so what is that percentage that you can attribute to S&P 500 that percentage is 10% so that's it for now I hope you like this thank you for watching

Channel: Codible

Views: 131,398

Rating: 4.8631792 out of 5

Keywords: regression, Microsoft Excel (Software), stock returns

Id: E4BGGpsQrOM

Length: 9min 24sec (564 seconds)

Published: Mon Feb 23 2015