Regression Formula Article byHarsh Katara Formula to Calculate Regression

Regression formula is used to assess the relationship between dependent and independent variable and find out how it affects the dependent variable on the change of independent variable and represented by equation Y is equal to aX plus b where Y is the dependent variable, a is the slope of regression equation, x is the independent variable and b is constant.

Regression analysis widely used statistical methods to estimate the relationships between one or more independent variables and dependent variables. is a powerful tool as it is used to assess the strength of the relationship between two or more variables, and then it would be used for modeling the relationship between those variables in the future.

Y=a + bX + ∈

For eg:
Source: Regression Formula (wallstreetmojo.com)

Where:

• Y – is the dependent variable
• X – is the independent (explanatory) variable
• a – is the intercept
• b – is the slope
• ∈ – and is the residual (error)

The formula for intercept “a” and the slope “b” can be calculated per below.

a= (Σy)(Σx2) - (Σx)(Σxy)/ n(Σx2) - (Σx)2
b= n (Σxy) - (Σx)(Σy) /n(Σx2) - (Σx)2

Explanation

Regression analysis, as mentioned earlier, is majorly used to find equations that will fit the data. Linear analysis is one type of regression analysis. The equation for a line is y = a + bX. Y is the dependent variable in the formula which one is trying to predict what will be the future value if X, an independent variable, change by a certain value. “a” in the formula is the intercept which is that value which will remain fixed irrespective of changes in the independent variable and the term ‘b’ in the formula is the slope which signifies how much variable is the dependent variable upon independent variable.

Examples

You can download this Regression Formula Excel Template here – Regression Formula Excel Template

Example #1

Consider the following two variables x and y, you are required to do the calculation of the regression.

Solution:

We have all the values in the above table with n = 5.

Now, first, calculate the intercept and slope for the regression.

Calculation of Intercept is as follows,

a = ( 628.33 * 88,017.46 ) – ( 519.89 * 106,206.14 ) / 5* 88,017.46 – (519.89)2

a = 0.52

Calculation of Slope is as follows,

b = (5 * 106,206.14) – (519.89 * 628.33) / (5 * 88,017.46) – (519,89)2

b = 1.20

Let’s now input the values in the regression formula to get regression.

Hence the regression line Y = 0.52 + 1.20 * X

Example #2

State bank of India recently established a new policy of linking savings account interest rate to Repo rate, and the auditor of the state bank of India wants to conduct an independent analysis on the decisions taken by the bank regarding interest rate changes whether those have been changes whenever there have been changes in the Repo rate. Following is the summary of the Repo rate and Bank’s savings account interest rate that prevailed in those months are given below.

The auditor of state bank has approached you to conduct an analysis and provide a presentation on the same in the next meeting. Use regression formula and determine whether Bank’s rate changed as and when the Repo rate was changed?

Solution:

Using the formula discussed above, we can do the calculation of linear regression in excel. Treating the rate as an independent variable, i.e., X, and treating Bank’s rate as the dependent variable as Y.

We have all the values in the above table with n = 6.

Now, first, calculate the intercept and slope for the regression.

Calculation of Intercept is as follows,

a = ( 24.17 * 237.69 ) – ( 37.75 * 152.06 ) / 6 * 237.69 – (37.75)2

a = 4.28

Calculation of Slope is as follows,

b = (6 * 152.06) – (37.75 *24.17) / 6 * 237.69 – (37.75)2

b= -0.04

Let’s now input the values in the formula to arrive at the figure.

Hence the regression line Y = 4.28 – 0.04 * X

Analysis: It appears State bank of India is indeed following the rule of linking its saving rate to the repo rate as there is some slope value that signals a relationship between the repo rate and the bank’s saving account rate.

Example #3

ABC laboratory is conducting research on height and weight and wanted to know if there is any relationship like as the height increases, the weight will also increase. They have gathered a sample of 1000 people for each of the categories and came up with an average height in that group.

Below are the details that they have gathered.

You are required to do the calculation of regression and come up with the conclusion that any such relationship exists.

Solution:

Using the formula discussed above, we can do the calculation of linear regression in excel. Treating Height as an independent variable, i.e., X, and treating Weight as the dependent variable as Y.

We have all the values in the above table with n = 6

Now, first, calculate the intercept and slope for the regression.

Calculation of Intercept is as follows,

a =  ( 350 * 120,834 ) – ( 850 * 49,553 ) / 6 * 120,834 – (850)2

a = 68.63

Calculation of Slope is as follows,

b = (6 * 49,553) – (850 *350) / 6 * 120,834 – (850)2

b = -0.07

Let’s now input the values in the formula to arrive at the figure.

Hence the regression line Y = 68.63 – 0.07 * X

Analysis: It appears that there is a significant very less relationship between height and weight as the slope is very low.

Relevance and Uses of Regression Formula

When a depicts that data can predict the future outcomes and along with that, a scatter plot of the same dataset appears to form a linear or a straight line, then one can use the simple linear regression by using the best fit to find a predictive value or predictive function. The regression analysis has many applications in the field of finance as it is used in CAPM that is the a method in finance. It can be used to forecast revenue and expenses of the firm.

Recommended Articles

This has been a guide to Regression Formula. Here we learn how to calculate regression using its formula along with practical examples and a downloadable excel template. You can learn more about excel modeling from the following articles –