WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 250+ Courses All In One Bundle
  • Login
Home » Financial Modeling Tutorials » Excel Modeling » Mortgage Formula

Mortgage Formula

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]
Mortgage Formula

Explanation

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.

Popular Course in this category
Sale
All in One Financial Analyst Bundle (250+ Courses, 40+ Projects)
4.9 (1,067 ratings)
250+ Courses | 40+ Projects | 1000+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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 a principal component. Therefore, the outstanding loan amount is derived by adding the interest accrued form months and deducting fixed monthly payments from the loan principal, and it is represented as above.

Examples

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

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

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.

mortgage formula example 1.1

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,

mortgage formula example 1.2

  • 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 –

mortgage formula example 1.3

  • 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 18th 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-

mortgage formula example 2.1

  • = 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-

mortgage formula example 2.2

  • Outstanding loan = $529.82

#2 – Principal Repayment in the 18th Month

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

Loan Outstanding after 17 Months

example 2.3

  • 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 

example 2.4

  • 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 18th month  will be

example 2.5

  • Principal Repayment in 18th Month= $43.91

Relevance and Uses

It is of great importance for a business to understand the concept of a mortgage. 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 make 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.

Mortgage Calculation (with Excel Template)

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

 example 2.6

Example 2.7

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 –

  • Mortgage Calculator in Excel
  • Mortgage APR vs Interest Rate
  • Secured Loans
  • Mortgagee vs Mortgagor
14 Shares
Share
Tweet
Share
All in One Financial Analyst Bundle (250+ Courses, 40+ Projects)
  • 250+ Courses
  • 40+ Projects
  • 1000+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

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

Book Your One Instructor : One Learner Free Class
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

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

Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Login

Forgot Password?

WallStreetMojo

Download Mortgage Formula Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More