Mortgage payment, interest rate, amount borrowed, and the number of payments are all related by an equation. Knowing any three allows the calculation of the remaining factor. This article shows how to use the equation that is used to calculate the number of payments. Although monthly payments are most common, the method shown here may be applied to any payment period.
Mortgage and Loan Formula – Definitions of Terms Used
P – the Principal, or amount borrowed
r – the Interest Rate for the specified time period
a – the amount repaid each month (or other repayment period)
n – the number of payments to be made
It is assumed that the first three values are known, and that it is desired to calculate the number of payments.
Why Calculate the Number of Mortgage Repayments?
Mortgages and loans are based on Compound Interest, so that the borrower pays interest not just on the amount borrowed, but also pays interest on the interest. This means that every dollar extra paid off the loan near the beginning of the mortgage, reduces the total amount paid by much more than one dollar. Put another way, paying off one month’s mortgage extra at the beginning can mean that the mortgage ends two months early.
Formula To Calculate Number of Loan Payments
The formula for a fixed payment on a loan at a fixed interest rate is
a = P.r.(1 + 1 ÷ ( (1 + r )^n – 1) )
This is the formula used by functions such as the Microsoft Excel function “PMT”. This formula can be re-arranged to
n = log( a / (a – P.r ) ) ÷ log( 1 + r )
It does not matter if the logarithm function is base 10 or natural (base e), so long as the same type is used each time. Since the formula is fairly complex, it is easier to set up in a spreadsheet like Excel. If spreadsheet cells are filled thus:
Cell A1: Interest Rate
Cell A2: Principal
Cell A3: Monthly Repayment
… then the following formula will return the number of repayments:
=LOG10((A3 / (A3 – A1*A2)) / LOG10(1 + A1)
A mortgage of $100,000 paid over 25 years (300 payments), at an annual interest rate of 5% (0.4074% monthly) will cost $584.59. Increasing the loan payments by 10% to $643.05 will give the new number of payments as:
=log( 643.05 / (643.05 – 0.004074 x 100000)) ÷ log( 1 + 0.004074)
=246.9 payments. The number of payments reduces from 300 to 247, so an increase in the monthly payments of 10% gives an 18% reduction in the number of payments. The total amount paid is also substantially reduced:
300 payments of $584.59 = $175,377
247 payments of $643.05 = $158,833, which is a saving of over $16,500
Mortgage Payment Formula Errors
There are several reasons why an error may occur.
- A1 x A2 is larger than A1. This means that it is impossible to pay off the mortgage because the monthly interest is larger than the amount repaid.
- The interest rate is incorrect, because the user has entered the interest rate per year, instead of per month.
- The interest rate is incorrect because the user has entered a number instead of a percentage. E.g. user enters 5 instead of 0.05
Number of Mortgage Payments – Summary
It is useful to be able to calculate the number of payments needed to clear a loan or mortgage. Mortgage payments and periods are influenced by interest rates and the term of the loan, so it is useful to check how much more quickly the mortgage can be paid off when regular extra payments are made.
This article is intended to be used as a guide only. Many mortgage and loan providers provide automatic calculators on their websites to estimate mortgage payments by term and interest rate.