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

**Mortgage Formula (Table of Contents)**

## What is Mortgage Formula?

The formula for mortgage basically revolves around the fixed monthly payment and the amount of outstanding loan.

The fixed monthly mortgage repayment calculation is based on the annuity formula and it is mathematically represented as,

**Fixed Monthly Mortgage Repayment Calculation = P * r * (1 + r)**

^{n}/ [(1 + r)^{n}– 1]where P = Outstanding loan amount, r = Effective monthly interest rate, n = Total number of periods / months

On the other hand, the outstanding loan balance after payment m months is derived by using the below formula,

**Outstanding Loan Balance=**

**P * [(1 + r)**

^{n}– (1 + r)^{m}] / [(1 + r)^{n}– 1]### Explanation of the Mortgage Formula

The formula for fixed monthly mortgage repayment calculation and outstanding loan balance can be derived by using the following steps:

**Step 1:** Identify the sanctioned loan amount which is denoted by P.

**Step 2:** Now figure out the rate of interest being charged annually and then divide the rate of interest by 12 to get the effective interest rate which is denoted by r.

**Step 3:** Now determine the tenure of the loan amount in terms of a number of periods/months and is denoted by n.

4.9 (927 ratings)

**Step 4:** On the basis of the available information, the amount of fixed monthly payment can be computed as above.

**Step 5:** The fixed monthly payment comprises of interest and principal component. Therefore, the outstanding loan amount is derived by adding the interest accrued for m months and deducting fixed monthly payment from the loan principal and it is represented as above.

### Examples of Mortgage Formula

Let’s see some simple to advanced examples of fixed monthly mortgage payment calculation.

#### Mortgage Payment Calculation – Example #1

**Let us take the simple example of a loan for setting up a technology-based company and the loan is valued at $1,000,000. Now the charges annual interest rate of 12% and the loan has to be repaid over a period of 10 years. Using the above-mentioned mortgage formula calculate the fixed monthly payment.**

where,

No. of periods, n = 10 * 12 months = 120 months

Effective monthly interest rate, r = 12% / 12 = 1%

Now, the calculation of fixed monthly payment is as follows,

- Fixed Monthly Payment = P * r * (1 + r)
^{n}/ [(1 + r)^{n}– 1] - = $1,000,000 * 1% * (1 + 1%)
^{120}/ [(1 + 1%)^{120}– 1]

**Fixed Monthly Payment will be –**

- Fixed Monthly Payment= $14,347.09 ~ $14,347

Therefore, the fixed monthly payment is $14,347.

#### Example #2

**Let us assume that there is a company which has $1,000 of loan outstanding which has to be repaid over the next 2 years. The EMI will be computed at an interest rate of 12%. Now based on the available information calculate**

**Loan outstanding at the end of 12 months****Principal Repayment in the 18**^{th}month

Given,

Loan principal, P = $1,000

No. of periods, n = 2 * 12 months = 24 months

Effective interest rate, r = 12% / 12 = 1%

#### #1 – Loan Outstanding after 12 Months

The calculation of loan outstanding after 12 months will be as follows-

- = P * [(1 + r)
^{n}– (1 + r)^{m}] / [(1 + r)^{n}– 1] - = $1,000 * [(1 + 1%)
^{24}– (1 + 1%)^{12}] / [(1 + 1%)^{24}– 1]

**Outstanding Loan after 12 Months will be-**

- Outstanding loan = $529.82

#### #2 – Principal Repayment in the 18^{th} Month

The principal repayment in the 18^{th} month can be computed by deducting the outstanding loan balance after 18 months from that of 17 months. Now,

**Loan Outstanding after 17 Months**

- Loan outstanding after 17 months = P * [(1 + r)
^{n}– (1 + r)^{m}] / [(1 + r)^{n}– 1] - = $1,000 * [(1 + 1%)
^{24}– (1 + 1%)^{17}] / [(1 + 1%)^{24}– 1] - = $316.72

**Loan Outstanding after 18 Months **

- Loan outstanding after 18 months = P * [(1 + r)
^{n}– (1 + r)^{m}] / [(1 + r)^{n}– 1] - = $1,000 * [(1 + 1%)
^{24}– (1 + 1%)^{18}] / [(1 + 1%)^{24}– 1] - = $272.81

Therefore, the principal repayment in the 18^{th} month will be

- Principal Repayment in 18
^{th}Month= $43.91

### Relevance and Uses of Mortgage Repayment Calculation Formula

It is of great importance for a business to understand the concept of mortgage formula. The Mortgage Equation can be used to design a loan amortization schedule which shows in detail how much is being paid in interest instead of focusing just on the fixed monthly payment. Borrowers can take decisions based on the interest costs which is a better way to measure the real cost of the loan. As such, a borrower can also decide based on the interest savings that which loan to choose when different lenders offer different terms.

### Formula (with Excel Template)

Now let us take the case mentioned in example 2 to illustrate the concept of mortgage formula calculation in excel template. The table gives a snapshot of the amortization schedule.

** **

### Recommended Articles

This has been a guide to Mortgage Formula. Here we discuss how to calculate Monthly Mortgage Repayment and outstanding Loan Balance with the practical examples and downloadable excel sheet. You can learn more about accounting from the following articles –

## Leave a Reply