Implementing the Single Index Model in Excel

hello everyone in the previous video we discussed the theory of the single index model in this one we are going to use Excel to implement it for doing that I downloaded the monthly price data for Google and the S&P 500 index from Yahoo Finance this was done for a period of five years from December 2006 to December 2011 I then computed the raw returns for both series which are here in these two columns Google's raw returns are R stands for raw returns and S&P 500 indexes raw returns when we say raw returns we mean that the risk-free rate has not yet been subtracted from them we are assuming the risk-free rate to be point zero zero four four over this five-year period and this column has the risk-free rate in order to implement the single index model we need excess returns which we are going to have in these two columns here but if I have to do that cell by cell this is going to take an awfully long period of time instead what we are going to do is to select some data ranges and quickly find out the excess returns for Google and S&P 500 index so I am going to select my data range for raw returns first once the selection is done I'm going to name my data range this here is the name box but before I can enter a name into the name box I need to make sure that there are no other names or there are no other data ranges that have been given some names already which might interfere with our new names the way we can check this is by going to the formulas tab here and then clicking on the name manager once you see this box is empty it tells you that there are no data ranges that have been given name in this worksheet so this is fine I can click it close and I can now give my selection a name this is the name box so I'm going to write here our aw raw underscore our et so this data range is my raw returns I can do the same thing with this data series here this is my risk free rate so I can give this series a name or F once I have done that I can very easily and quickly find out the excess returns for Google and the S&P 500 index this is the region in which I want my excess returns to go so I'm going to select this region keeping the selection active I'm going to type in this cell or aw underscore R et minus RF and so that the entire selection of my cells gets filled with the excess returns I'm going to press ctrl shift and enter at the same time so have very quickly found out the excess returns for the entire length of time for Google and for the S&P 500 index let us now look at some of the statistics for these two returned series we are interested in the standard deviation so let us calculate that here we are using the STD we function to calculate the standard deviation of excess returns for Google so this is our standard deviation for excess returns for Google 0.1 zero one seven and this is a monthly standard deviation because we are using monthly data we can drag this formula across to get the monthly standard deviation for the indexes excess returns we can also have a look at the annualized standard deviation in order to find out the annualized standard deviation we can multiply the monthly standard deviation by the square root sq RT of twelve twelve being the number of months in a year so this is our annualized estimate of standard deviation for Google's excess returns and we can drag the formula across again to get the annualized standard deviation for the indexes excess returns as well we can also find the variance of returns so in this cell we are finding the monthly variance of Google's returns by using the VAR function so here we have the monthly variance for Google's excess returns and we can drag the formula across to get an estimate of to get the estimate of the monthly variance rather I was about to say annualized variance so this is the monthly variance for the excess returns of the index now we can analyze the variance in this cell for Google what we do is we multiply the variance by 12 and we can drag this across to do the same thing for the index now let us proceed to implement the single index model we know from before from a previous video that the single index model is arrived at when we regress the excess returns on the market on the securities excess returns so we are going to have to run a regression for doing that we need to go to the data tab and select data analysis the data analysis add-in should be installed on your machine if it is not you should install it first to proceed further so once this item is active here we could select this and then from this dialog box we could select regression and Excel is going to ask us to provide our wide range of series and the X range of series what is supposed to be our Y range of series what is the dependent variable the dependent variable is Google's excess returns and it has already been entered here in this field already this is because I ran this regression a little while ago you will see that Google's excess returns are in column E and the first value is in cell e2 and if you scroll down you're going to find that this series goes on till cell e16 so that is what has been entered here in this field the x range or our x series is the markets excess return and the market success returns are in column F the first value is in cell f2 and it is going to go on down till F 61 so that is what has been entered in this wheel here I want my output to be returned in the same worksheet so I have to specify an output range which is cell h1 this cell here this is where I want my output I also want Excel to return the residual output to me and that is why this box here has been checked so when you are running the regression make sure that you have checked the box for residual output once we are satisfied we can click OK and now here we have our regression results let us take some time to look at these results and interpret their meaning we are going to focus first of all on this panel here regression statistics the first item that we see here is the multiple R which is 0.6 0 for 9 this is telling us that Google tracks the S&P 500 index fairly closely the next item here is R squared which is our coefficient of determination this is equal to 0.36 five nine or 0.366 telling us that about 37 percent of the variation in Google's success returns is explained by the S&P 500 index if we move down we see this value for adjusted r-square and you will notice that this is a little bit lower than the value for R squared on top here why is this lower this is because we are working with estimated values for intercept and slope in our regression we would have liked to work with the true values but alas they are unknown so we have to work with estimates and adjusted r-square is simply adjusting for the estimation error in the coefficient of determination that is why it is a little bit lower the next item that we see is the standard error of regression in this case it is Oh point zero eight one six seven or roughly point zero eight one seven in terms of the single index model this is the standard deviation of the residual return let us go down to the residual series and check this this is where we have our residual output in this column we have our predicted Y values which means that these are the values for predicted excess return for Google and in this column here we have the residual term or the error term from the regression let us work with our residuals and see a few statistics about it we want to see the standard deviation of the residual so we can calculate that in this cell stdev and we are going to provide the series let us see what we get we get point zero eight one zero which is not very different from what we see at the top here point zero eight one six so this is roughly the same while we are working with the residual series let us also have some more statistics let's also calculate the variance of this series the variance of the residuals you will remember from the single index model is going to be the unsystematic part of our risk so we can calculate it here one time so we can say var for variance and we can provide the series again let us see what we get this is point zero zero six six this is our unsystematic risk for google basically we are also going to calculate the unsystematic risk by using our ANOVA results in a little while we also saw in the previous video that the mean of the error term or the mean of the residual term was assumed to be zero let us check this out we can find the average of the residuals let's hope it is equal to zero let us see what we get we see that in fact the mean of the error term is zero so on an average there is no residual return now let us scroll up and see what else we can find out let us look at this section of our regressions results which give us the ANOVA results from here we can find out the variance of Google's return we have already found out the variance if you will recall down here it is equal to 0.03 zero well this is for the market and this is for the security for Google so the variance of Google's excess returns is point zero one zero three and we are trying to get the same result from our ANOVA numbers how do we do that let us do it here in this cell if we want to find out the variance of Google's excess returns from our ANOVA result what we need to do is to divide the total sum of squares which is this number here 0.6 1 0 2 by the degrees of freedom which is 59 here so we select this let us divide and hopefully we should get the same result so this is the variance for Google's excess returns point 0 1 0 3 the same as we saw a little while ago we can also find the systematic risk for Google let us do it by using the index model first according to the index model the systematic risk is equal to beta squared times the variance of the market so let us do that here what is our beta this number here the coefficient for the X variable is our beta so we are going to select this and square it up after that what we need to do is to multiply this by the variance of the market we had calculated the variance of the market a little while ago let's go there this was the variance of the market point zero zero three zero so we select this self and hit enter this is the systematic risk for Google we can also find the systematic risk by using our ANOVA results let us do that in this cell what we need to do is to divide the sum of squares for the regression which is this item here by the degrees of freedom which is 59 when we hit enter we see the same result returned again so this is the systematic risk for Google we can also find the unsystematic risk well what is the unsystematic risk the unsystematic risk is the variance of the residual we already found that out here let me scroll down and show that to you this was the variance of the residual point 0 0 6 6 but we can find the same thing from our ANOVA results as well let us try to do that in this cell here the unsystematic part of the risk this is going to be equal to the sum of squares of the residuals because we know that the unsystematic risk arises from the residuals this is the sum of squares here for the residual and we can divide this by the degrees of freedom 59 so this is the unsystematic part of Google's risk which if I scroll down you will see it's going to be the same as this point 0 0 6 6 what else we can do with our regression results we can check for R square R square or the coefficient of determination can also be determined from our ANOVA results in order to find out R square what we need to do is to divide the sum of squares of the regression which is this by the total sum of squares which is this hit enter and we see the same value as this so even if you didn't have this value before if you had only the ANOVA results provided to you you could still find out the coefficient of determination by dividing the sum of squares of the regression by the total sum of squares you could also do this through an alternative way you could say 1 minus the sum of squares of the residuals which is this and you can divide this by the total sum of squares which is this closed bracket and hit enter we see again the same value which is the coefficient of determination alright so let us now move on to our actual regression results which are going to tell us about the single index model this is where we have our results this item here is the intercept which is the Alpha from the single index model this is the standard error this is the T statistic and this one is the p value or the probability value for obtaining the alpha that we have obtained we can immediately see by looking at the p value that alpha is not significant at least not at the 5% level of significance in order to understand this you need to know that we are working here with the null hypothesis that alpha is equal to 0 what we see from our results is that there is a 27% chance of obtaining the Alpha that we have obtained this one and this 27% is pretty substantial this tells us that probably our null hypothesis is true let us go over it one more time if our null hypothesis is true we have a 27% probability of getting this value for alpha we would have rejected our null hypothesis if this probability was 5% or less which we call the 5% level of significance since this is not so we cannot reject our null hypothesis that alpha is equal to zero therefore then this value of alpha is not statistically significant and we can say that in this case alpha is basically zero what about the beta or the slope coefficient this is where our beta is the coefficient on the X variable what we see that there is hardly any probability for obtaining such a beta this is almost zero if I change this by formatting these cells to display zero decimal places you will see that this probability is zero what does this mean this means that under the assumption of our null hypothesis which is again that beta is equal to zero we can reject the null this means that this beta estimate is statistically very significant we are rejecting the null hypothesis that beta is equal to zero here we have the lower and upper 95% values for our coefficients what does this tell us this cell here tells us or this range of cells tells us that the actual alpha may be somewhere between minus point zero zero nine to plus point zero three so there is some uncertainty about the intercept or the Alpha we said before that the adjusted r-square is lesser than the R square because we are working with estimates and estimates always have some degree of uncertainty about them so this is telling us the range between the between which the actual alpha could lie and similarly for beta it could range from positive 0.73 to positive 1.5 one where this is our estimate so there is some uncertainty about our estimates that is why the adjusted r-square was a little lesser than the R square so what we can say in totality now we can say that Google's excess return is obtained mostly from its sensitivity to a change in the excess market returns and though we love to have alpha there is no significant alpha on offer here which means that there was no non market premium for Google for this time range we can also plot our results and see them graphically we can plot a characteristic line actually let us do that here in this space for plotting our results we are going to go to this insert tab here and select this scatter plot and I'm going to go with this one so I have a graphing space which I can move here so that it doesn't overlap with our data results inside this space I'm going to right click and then select data we see this dialog box here we are going to add our series that we want to have on the graph so I'm clicking add and Excel is asking us to provide a series name so let us first of all plot the actual returns or actual excess returns for google for doing that we need an x series and a y series what is going to be our x series the x-series is going to be our markets access return so we are going to select that here looks like I selected these parameters also so I will do that one more time the series actually ends here so I'm going to scroll up and provide the values what is going to be our y series the y series is going to be the actual excess returns on Google which are here in this column column e so let us select column E and be careful not to select the statistical parameter parameters that we calculated before so we have made our selection we can click OK and see how it looks like let us see how the graph is looking these are our actual returns in a scatter plot here we can superimpose our predicted returns this column here gives us the predicted value of y where Y is our excess return for Google so let us plot these returns as well so we go into the graphing space select data and we are going to add another series and we are going to call this the predicted excess returns the x series is going to be again or excess returns on the market which are here actually let me begin from this point onwards upwards so that I don't select by mistake the statistical parameters that we calculated so this is our x series S&P 500's excess returns and now we want the predicted excess returns for Google which are in this series here so we can select this click okay and let us see how it looks like well this is our predicted returns pretty much a linear relationship as expected with a regression line if you wish you could plot a trend line and that would be called the characteristic line where on the x axis we have the market success returns and on the y-axis we have the Securities excess returns so you could click on the right you could click on any of the points here on this red series we are right-clicking let's say on this point and we can say add trendline let us format our trendline a little bit I want a solid line so that it can be seen distinctly it's asking us which color we want well green is fine good enough the size of the line is pretty thin so we can make it a little thicker line style we can increase the width of this line so let's say it is equal to three points so it's a little wider and we can see it properly so we have our characteristic line here what do we see from this graph what we see is that this line here and the returns that plot on it are our predicted values from the regression model or the single index model and the blue dots that we see are the actual excess returns the distance between this green line and the blue dots are is the residual basically so these residuals are the distances between the blue dots and the green line this is all I wanted to tell you in this video see you later

Channel: Friendly Finance with Chandra S. Bhatnagar

Views: 55,119

Rating: 4.8324022 out of 5

Keywords: Finance, Investments, Portfolio Management, Single Index Model, Excel

Id: M39LPrz2gr8

Length: 27min 23sec (1643 seconds)

Published: Fri Mar 02 2012