Formula to Calculate Regression

Regression analysis widely used statistical methods to estimate the relationships between one or more independent variables and dependent variables. RegressionRegressionRegression Analysis is a statistical approach for evaluating the relationship between 1 dependent variable & 1 or more independent variables. It is widely used in investing & financing sectors to improve the products & services further. read more  is a powerful tool as it assesses the strength of the relationship between two or more variables. Then one would use it to model the future relationship between those variables.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Regression Formula (wallstreetmojo.com)

Where:

  • Y – is the dependent variableX – is the independent (explanatory) variablea – is the interceptb – 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. For example, the equation for a line is y = a + bX. Y is the dependent variable in the formula, which one tries to predict what will be the future valueFuture ValueThe Future Value (FV) formula is a financial terminology used to calculate cash flow value at a futuristic date compared to the original receipt. The objective of the FV equation is to determine the future value of a prospective investment and whether the returns yield sufficient returns to factor in the time value of money.read more if X, an independent variable, changes by a certain value. The “a” in the formula is the intercept. It means that the value remains fixed irrespective of changes in the independent variable. The term ‘b’ in the formula is the slope which signifies how much the dependent variable is upon the independent variable.

Examples

Example #1

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

Solution:

Using the above formula, we can calculate linear regression in excelLinear Regression In ExcelLinear Regression is a statistical excel tool that is used as a predictive analysis model to examine the relationship between two sets of data. Using this analysis, we can estimate the relationship between dependent and independent variables.read more as follows.

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 linking savings account interest rates to Repo rates. Therefore, the auditor of the State Bank of India wants to conduct an independent analysis of the decisions taken by the bank regarding interest rate changes and whether those have been changed whenever there have been changes in the Repo rate. Therefore, the following is the summary of the Repo rate and Bank’s savings account interest rate that prevailed in those months are below.

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

Using the formula discussed above, we can calculate linear regression in Excel. Treating the RepoRepoA repurchase agreement or repo is a short-term borrowing for individuals who deal in government securities. Such an agreement can happen between multiple parties into three types- specialized delivery, held-in-custody repo and third-party repo.read more 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.

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

a = 4.28

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

b= -0.04

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

Hence, the regression line Y = 4.28 – 0.04 * X.Analysis: The State Bank of India is indeed following the rule of linking its saving rate to the repo rate, as some slope value signals a relationship between the repo rate and the bank’s saving account rate.

Example #3

ABC laboratory is researching height and weight and wanted to know if there is any relationship, like as the height increases, the weight will also increase. So, they gathered a sample of 1,000 people for each category and found 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.

Using the formula discussed above, we can calculate linear regression in Excel. Treating height as an independent variable, i.e., X, and weight as the dependent variable as Y.

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

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

a = 68.63

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: There is a significant, less relationship between height and weight, as the slope is very low.

Relevance and Uses of Regression Formula

When a correlation coefficientCorrelation CoefficientCorrelation Coefficient, sometimes known as cross-correlation coefficient, is a statistical measure used to evaluate the strength of a relationship between 2 variables. Its values range from -1.0 (negative correlation) to +1.0 (positive correlation). read more depicts that data can predict future outcomes. Along with that, a scatter plot of the same dataset appears to form a linear or a straight line. 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 finance as it is used in CAPM, the capital asset pricing modelCapital Asset Pricing ModelThe Capital Asset Pricing Model (CAPM) defines the expected return from a portfolio of various securities with varying degrees of risk. It also considers the volatility of a particular security in relation to the market.read more a method in finance. One can use it to forecast the revenue and expenses of the firm.

This article is a guide to Regression Formula. Here, we learn how to calculate regression using its formula, practical examples, and a downloadable Excel template. You can learn more about Excel modeling from the following articles: –

  • Gini Coefficient FormulaGini Coefficient FormulaGini Coefficient or Gini Index is statistical dispersion depicting the income dispersions amongst the population of a country i.e. it represents the wealth inequalities of the citizens of a particular country. read moreFormula  of Correlation Calculate Coefficient of VariationCalculate Coefficient Of VariationThe coefficient of Variation is the systematized measure of a Probability Distribution’s or Frequency Distribution’s dispersion. It is determined as the ratio of Standard Deviation to the Mean. read moreRegression vs ANOVA