by James Earnshaw on July 14, 2023

Mortgages are an example of an EMI (equated monthly installment).

The formula for calculating an installment $I$ is: $$I=P \cdot i \cdot {(1+i)^n\over (1+i)^n-1} \tag{1}$$

Where:

• $P$ is the principal amount borrowed
• $i$ is the interest rate per month and is usually found by dividing the annual equivalent rate (AER) by 12
• $n$ is the number of payments which is found by the multiplying the mortgage term (in years) by 12

This is actually the formula used by the PMT function in Excel and Power BI which works out the repayments for a loan based on regular payments and a fixed interest rate.

How to derive the formula

Suppose an amount $P$ is borrowed and is to be paid back over $n$ monthly payments ($n$ / 12 years) at a fixed interest rate $i$ per month. Let $A_{k}$ be the amount owed to the lender at the end of payment $k$.

At the start of the loan, when $k=0$, the amount owed is the full amount $P$, so $A_{0}=P$.

At $A_{1}$ the principal $P$ is still owed but has accrued interest of $Pi$, and the installment $I$ is deducted: $$A_{1}=P+Pi - I$$ $$\implies A_{1}=P(1+i)-I$$ At $A_{2}$ the amount $A_{1}$ is still owed but has also accrued interest of $A_{1}i$ and again the installment $I$ is deducted: $$A_{2}=A_{1}+A_{1}i-I$$ $$\implies A_{2}=A_{1}(1+i)-I$$ $$\implies A_{2}=(P(1+i)-I)(1+i)-I$$ $$\implies A_{2}=P(1+i)^2-I(1+i)-I$$ $$\implies A_{2}=P(1+i)^2-I(1+(1+i))$$ At this point it becomes convenient to let $t=(1+i)$: $$A_{2}=Pt^2-I(1+t)$$ Repeating the above we see that $A_{3}$ becomes: $$A_{3}=Pt^3-I(1+t+t^2)$$ Now it's possible to see that the amount owed at payment $k$ is: $$A_{k}=Pt^k-I(1+t+...+t^{k-1})$$ The bracketed term $(1+t+...+t^{k-1})$ forms a geometric series, the sum of which is given by the formula: $${t^k-1}\over {t-1}$$ So substituting it in: $$\implies A_{k}=Pt^k-I{{t^k-1}\over {t-1}}$$ At the final payment $n$ the amount owed should be zero because the loan will have been paid off in full with interest, so setting $A_{n}=0$ and substituting back $t=1+i$ yields: $$P(1+i)^n=I{{(1+i)^n-1}\over{(1+i)-1}}$$ $$\implies Pi(1+i)^n=I((1+i)^n-1))$$ $$\implies I=P \cdot i \cdot {(1+i)^n\over (1+i)^n-1} $$ Which is equation $(1)$ at the top.

Example

Now we can use it in a real world example. I went on Compare The Market and found this deal:

This is a quote for a house price of £200,000, a deposit of £20,000, and a 30 year mortgage of £180,000. The interest rate is 5.88%.

Before using the formula we need to define the variables:

• $P=180000$
• $i=5.88\%/12=0.0049$
• $n=30\cdot12=360$

So: $$I=180000\cdot0.0049\cdot{(1+0.0049)^{360}\over(1+0.0049)^{360}-1}$$ $$\implies I=1065.34$$ And we see that £1,065.34 is the figure quoted in the advert.

You could skip the maths and use Excel's PMT function:

This is a simple example. It doesn't include the booking fee, but you could add it to the principal. It only works for mortgages on a fixed interest rate.

Further reading

• Calculating EMIs