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.

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.

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.