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 formulaAnnuity FormulaAn annuity is the series of periodic payments to be received at the beginning of each period or the end of it. An annuity is based on the PV of an annuity due, effective interest rate and time period. Annuity = r * PVA Ordinary/[1 – (1 + r)-n]read more, and it is mathematically represented as,

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,

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Mortgage Formula (wallstreetmojo.com)

Explanation

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

Examples

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

  • Identify the sanctioned loan amount, which is denoted by P. 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. Now determine the tenure of the loan amount in terms of a number of periods/months and is denoted by n. On the basis of the available information, the amount of fixed monthly payment can be computed as above. The fixed monthly payment comprises of interest and a principal component. Therefore, the outstanding loan amount is derived by adding the interest accruedInterest AccruedAccrued Interest is the unsettled interest amount which is either earned by the company or which is payable by the company within the same accounting period.read more form months and deducting fixed monthly payments from the loan principal, and it is represented as above.

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 monthsPrincipal Repayment in the 18th month

Given,

Loan principal, P = $1,000

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

Effective interest rateEffective Interest RateEffective Interest Rate, also called Annual Equivalent Rate, is the actual rate of interest that a person pays or earns on a financial instrument by considering the compounding interest over a given period.read more, 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 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

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

  • 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 scheduleA Loan Amortization ScheduleLoan amortization schedule refers to the schedule of repayment of the loan. Every installment comprises of principal amount and interest component till the end of the loan term or up to which full amount of loan is paid off.read more, 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.

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 ExcelMortgage Calculator In ExcelIn Excel, a mortgage calculator is not a built-in feature. The amortization schedule is required in order to create the categories column, into which all of the categories and data can be entered. Then, in one cell, we can use the mortgage calculation formula.read moreMortgage APR vs Interest RateMortgage APR Vs Interest RateThe mortgage APR measures the mortgage cost, including the interest rate and other costs like discount points, broker fees, and closing costs in percentage terms. In contrast, an interest rate is the borrowing cost of the principal, that is, the loan amount, which can be either fixed or variable.read moreSecured LoansSecured LoansSecured loans refer to the type of loans approved and received against a guarantee or collateral. If they fail to do so, the lending institution acquires the collateral to compensate for the amount that the borrowers were allowed.read moreMortgagee vs MortgagorMortgagee Vs MortgagorThe mortgagee is the lender or giver of a secured loan who pays the borrower the entire loan amount in exchange for security or a mortgage. Mortgagor is an individual or an organization who acquires a loan by mortgaging their personal assets and paying interest and a fixed installment.read more