Financial Modeling Tutorials

- Financial Modeling Basics
- Excel Modeling
- Financial Functions in Excel
- Sensitivity Analysis in Excel
- Time Value of Money
- Future Value Formula
- Present Value Factor
- Perpetuity Formula
- Present Value vs Future Value
- Annuity vs Pension
- Present Value of an Annuity
- Doubling Time Formula
- Annuity Formula
- Annuity vs Perpetuity
- Annuity vs Lump Sum
- Internal Rate of Return (IRR)
- NPV vs XNPV
- NPV vs IRR
- NPV Formula
- PV vs NPV
- IRR vs ROI
- Break Even Point
- Payback Period & Discounted Payback Period
- Payback period Formula
- Discounted Payback Period Formula
- Profitability Index
- Cash Burn Rate
- Simple Interest
- Simple Interest vs Compound Interest
- Simple Interest Formula
- CAGR Formula (Compounded Annual Growth Rate)
- Effective Interest Rate
- Loan Amortization Schedule
- Mortgage Formula
- Loan Principal Amount
- Interest Rate Formula
- Rate of Return Formula
- Effective Annual Rate
- Effective Annual Rate Formula (EAR)
- Daily Compound Interest
- Monthly Compound Interest Formula
- Discount Rate vs Interest Rate
- Rule of 72
- Geometric Mean Return
- Real Rate of Return Formula
- Continuous compounding Formula
- Weighted average Formula
- Average Formula
- Average Rate of Return Formula
- Mean Formula
- Weighted Mean Formula
- Harmonic Mean Formula
- Median Formula in Statistics
- Range Formula
- Expected Value Formula
- Exponential Growth Formula
- Margin of Error Formula
- Decrease Percentage Formula
- Percent Error Formula
- Holding Period Return Formula
- Cost Benefit Analysis
- Cost Volume Profit Analysis
- Opportunity Cost Formula
- Mortgage APR vs Interest Rate
- Regression Formula
- Correlation Coefficient Formula
- Covariance Formula
- Coefficient of Variation Formula
- Sample Standard Deviation Formula
- Relative Standard Deviation Formula
- Volatility Formula
- Binomial Distribution Formula
- Quartile Formula
- P Value Formula
- Skewness Formula
- Regression vs ANOVA

**Formula of Regression (Table of Contents)**

## What is Linear Regression Formula?

Regression analysis widely used statistical methods to the estimate of the relationships between a one or more independent variables and dependent variable. Regression 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.

** **The Formula for Linear Regression is as below:

**Y=a + bX + ∈**

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)(Σx^{2}) - (Σx)(Σxy)/ n(Σx^{2}) - (Σx)^{2}b= n(Σxy) - (Σx)(Σy)/n(Σx^{2}) - (Σx)^{2}

### Explanation of the Regression Formula

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 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 of Linear Regression Formula (with Excel Template) **

Let’s see some simple to advanced excel examples of linear regression formula to understand it better.

#### Example #1

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

Solution:

Using the above formula, we can do the calculation of linear regression in excel as follows.

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

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

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 equation.

4.9 (927 ratings)

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 are been changes whenever there have been changes in the Repo rate. Following is the summary of 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 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 Repo rate was changed?

Solution:

Using the formula discussed above, we can do the calculation of linear regression in excel. Treating Repo rate as 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 equation.

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 repo rate as there is some slope value which signals a relationship between repo rate and 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 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 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 equation.

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

When a correlation coefficient 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. Regression analysis has many applications in the field of finance like its used in CAPM that is the capital asset pricing model 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 discussed how to calculate linear regression equation along with practical examples and downloadable excel template. You can learn more about excel modeling from the following articles –

## Leave a Reply