EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Finance Finance Resources Finance Formula Mortgage Formula
 

Mortgage Formula

Madhuri Thakur
Article byMadhuri Thakur

Updated July 27, 2023

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 of periodic payments per year. Mathematically, it is represented as,

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

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,

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

Examples of Mortgage Formula (With Excel Template)

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

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

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.

Example -1.1

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 -1.2

  • 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 24th month

Mortgage Formula-2.1

#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*m1] / [(1 + r/n)n*t – 1]

Mortgage Formula-2.2

  • 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 24th month

The principal to be repaid in the 24th month can be calculated by subtracting the outstanding balance after 2 years from the outstanding balance after 23 months (m2 = 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*m2] / [(1 + r/n)n*t – 1]

Mortgage Formula-2.4

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

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

Therefore, the outstanding loan balance after 2 years and the principal repayment in the 24th 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 loan, and it is denoted by r.

Step 3: Next, determine the tenure of the loan in terms of the 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 of 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 Use 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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Investment Banking Course

Corporate Valuation, Investment Banking, Accounting, CFA Calculator & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Mortgage Formula Excel Template

EDUCBA Login

Forgot Password?

EDUCBA

डाउनलोड Mortgage Formula Excel Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW