Mortgage Formula

Mortgage Formula (Table of Contents)

• Formula
• Examples

What is Mortgage Formula?

The term “mortgage” refers to the debt instrument against which the borrower is obligated to pay a predetermined set of payments. Typically, a mortgage is secured by collateral in the form of real estate property, equipment, etc. The formula for a mortgage primarily includes the fixed periodic payment and the outstanding loan balance. The formula for fixed periodic payment can be expressed using the outstanding loan amount, rate of interest, tenure of the loan and number periodic payments per year. Mathematically, it is represented as,

where,

• P = Outstanding Loan Amount
• r = Rate of interest (Annual)
• t = Tenure of Loan in Years
• n = Number of Periodic Payments Per Year

On the other hand, the formula for outstanding loan balance at the end of m years can be derived as below,

Examples of Mortgage Formula (With Excel Template)

Let’s take an example to understand the calculation of Mortgage in a better manner.

Mortgage Formula – Example #1

Let us take the example of XYZ Ltd that has availed a $2,000,000 term loan to set-up a technology-based company. As per the terms of sanction, the annualized rate of interest is 8%, the tenure of the loan is of 5 years and the loan has to repay on a monthly basis. Calculate the fixed monthly payment based on the given information.

Solution:

Fixed Monthly Payment is calculated using the Formula given below.

Fixed Monthly Payment = P *[(r/n) * (1 + r/n)^n*t] / [(1 + r/n)^n*t – 1]

• Fixed Monthly Payment = $2,000,000 * (8%/12) * (1 + 8%/12)^12*5 / [(1 + 8%/12)^12*5 – 1]
• Fixed Monthly Payment = $40,553

Therefore, the Fixed Monthly Payment for XYZ Ltd is $40,553.

Mortgage Formula – Example #2

Let us take another example where the company has borrowed a loan of $1,000,000 that has to be repaid over the next 4 years. The annualized rate of interest is 6% and the payment has to be made monthly. Based on the given information calculate the following:

1. Outstanding Loan Balance at the end of 2 years
2. Principal Repayment made in the 24^th month

#1 – Outstanding Loan Balance at the end of 2 years

Outstanding Loan Balance is calculated using the Formula given below.

Outstanding Loan Balance = P * [(1 + r/n)^n*t – (1 + r/n)^n*m₁] / [(1 + r/n)^n*t – 1]

• Outstanding Loan Balance  = $1,000,000 * [(1 + 6%/12)^12*4 – (1 + 6%/12)^12*2] / [(1 + 6%/12)^12*4 – 1]
• Outstanding Loan Balance  = $529,890

#2 – Principal Repayment made in the 24^th month

The principal to be repaid in the 24^th month can be calculated by subtracting the outstanding balance after 2 years from outstanding balance after 23 months (m₂ = 23 months). Now,

Outstanding Loan Balance is calculated using the Formula given below.

Outstanding Loan Balance = P * [(1 + r/n)^n*t – (1 + r/n)^n*m₂] / [(1 + r/n)^n*t – 1]

• Outstanding Loan Balance = $1,000,000 * [(1 + 6%/12)^12*4 – (1 + 6%/12)²³] / [(1 + 6%/12)^12*4 – 1]
• Outstanding Loan Balance = $550,621

Now, the principal repayment in the 24^th month = $550,621 – $529,890 = $20,731

Therefore, the outstanding loan balance after 2 years and the principal repayment in the 24^th month are $529,890 and $20,731 respectively.

Explanation

The formula  for fixed periodic payment and outstanding loan balance can be derived by using the following steps:

Step 1: Firstly, determine the value of the outstanding loan and it is denoted by P.

Step 2: Next, determine the annualized rate of interest that is charged on the loan and it is denoted by r.

Step 3: Next, determine the tenure of the loan in terms of a number of years and it is denoted by t.

Step 4: Next, determine the number of periodic payments made during a year and it is denoted by n.

Step 5: Finally, the formula for fixed periodic payment can be expressed using the outstanding loan amount (step 1), rate of interest (step 2), tenure of the loan (step 3) and number periodic payments per year (step 4) as shown below,

Fixed Periodic Payment = P * [(r/n) * (1 + r/n)^n*t] / [(1 + r/n)^n*t – 1]

Step 6: On the other hand, the outstanding loan balance after m years is computed by adding the total interest accrued for m*n months and subtracting the total fixed periodic payments from the initial outstanding loan (P) and it is represented as shown below,

Outstanding Loan Balance = P * [(1 + r/n)^n*t – (1 + r/n)^n*m] / [(1 + r/n)^n*t – 1]

Relevance and Uses of Mortgage Formula

From the perspective of both borrowers and lenders, it is very important to understand the concept of mortgage because almost all companies used a mortgage to expand or support their business operations. The formula for a mortgage is used to chalk out the amortization schedule of a loan that provides clear bifurcation of the fixed periodic payment and interest expense incurred during each period.

Recommended Articles

This is a guide to Mortgage Formula. Here we discuss how to calculate Mortgage along with practical examples. We also provide a downloadable excel template. You may also look at the following articles to learn more –

1. How to Calculate Current Yield of Bond?
2. Taxable Income Formula with Excel Template
3. Calculation of Effective Tax Rate
4. Examples of Correlation Formula