Intuitively, the smaller the individual observed values of ϵ, even though adding up to zero, the better is our regression model. (If it weren’t zero, the model would be biased in the sense it was likely to either overstate or understate the value of y.) Some observations are farther away from the predicted value than others, but the sum of all the differences will add up to zero. This error is ‘random’ and not biased, which means that if you sum up ϵ across all data points, you get a total of zero. This is a random ‘error’ – error not in the sense of being a mistake – but in the sense that the value predicted by the regression equation is not equal to the actual observed value. The difference between the two is explained by the error term – ϵ. This is unlikely to be exactly equal to the actual observed value of y. The ‘predicted’ value of y is provided to us by the regression equation.
#Excel linear regression model how to#
Now that we have seen how to calculate α and β(ie, either using the formulae, or using Excel), it is probably possible to say that we can ‘predict’ y if we know the value of x. Now let us go back to the initial equation: Α = mean of the dependent variable (ie y) – β * mean of the independent variable (ie x) Beta and correlationĪt this point it is important to point out the relationship between beta and correlation.
Once beta is known, alpha can be calculated as Β = covariance of the two variables / variance of the independent variable Given a set of data points, it is fairly easy to calculate alpha and beta – and while it can be done manually, it can be done using Excel using the SLOPE (for calculating β)and the INTERCEPT ( α) functions. x is the independent variable and ϵ is the error term (more on the error term later). Regression with a single dependent variable y whose value is dependent upon the independent variable x is expressed as (For this article, I am going to limit myself to one independent variable, but the concepts discussed apply equally to regressing on multiple independent variables.) What we are going to do next is go deeper into how regression calculations work. What we also see above in the Novartis example is the fitted regression line, ie the line that expresses the relationship between the y variable, called the dependent variable, in this case the returns for the Novartis stock with the x variable, in this case the S&P 500 returns that are considered ‘independent’ or the regressor variable. Here is the spreadsheet with this data, in case you wish to see how this graph was built.Ī regression model expresses a ‘dependent’ variable as a function of one or more ‘independent’ variables, generally in the form:
This is a scatter plot of Novartis’s returns plotted against the S&P 500’s returns (data downloaded from Yahoo finance).
That provides a visual representation of the data.Ĭonsider the figure below.
How do you figure out how related they are? And what can you do with the data in a practical sense? The first thing to do is to create a scatter plot. Imagine you have data on a stock’s daily return and the market’s daily return in a spreadsheet, and you know instinctively that they are related. We will also look at how regression is connected to beta and correlation. In particular, we will look at the different variables such as p-value, t-stat and other output provided by regression analysis in Excel. We will discuss understanding regression in an intuitive sense, and also about how to practically interpret the output of a regression analysis. This article provides an overview of linear regression, and more importantly, how to interpret the results provided by linear regression. It is also used extensively in the application of data mining techniques. Linear regression is an important concept in finance and practically all forms of research.
0 kommentar(er)
