For the first post of 2021 I though about documenting a subject on which I delve on a regular basis each time the need to scratch the housing market itch appears. When to buy? What to buy? Where to buy? For those owning one or multiple properties, the market seems "good" when it rises. However, for first time buyer, the housing market is some kind of a dread, especially in France where prices has been climbing way faster than wages for years.
So regularly I asm myself how much a loan would cost, what would be the monthly payment and what if interest rates were to raise, how much the housing market would move. To answer all this, you need a little bit of background of financial math that I always struggle to recover from my last scratching session. So here is a simple explanation of the compound interest with monthly payment formula.
You learn at school that when you put money in the bank, the money work for you (well it actually doesn't, people do). So if you were to put 1000€ at an interest rate of 10%, at the end of the year, you would end up with 1000€ + 10% of 1000€ = 1100€. The simple form of this equation is:
If we generalize with a being the capital (here 1000) and r being the interest rate of 10% (meaning 0.10):
On the second year:
So we can see a pattern appear here. As interests will have to be paid on interests received the preceding year, interests are compounded. Series are not the best way to represent this, especially if we want to use a computer to do the hard work for us. A polynomial form will be way better. This polynomial will take the initial capital, the interest rate and the number of years and will output the amount with interests:
Baby stepping to the loan side of this now. When you put money in the bank, the bank will loan it to someone who need a loan to buy a house. And the dynamic will be exactly the same but in reverse. If you are the borrower, the amount a is the the money given to you by the bank at interest rate r and if that loan lasts n years, then you end up with the same formula.
Let's rearange it this way (you will see later why)
But you will need to pay back some of that at some point right? Usually on the very first month, you start paying a monthly amount m. But if you start paying back on the first month, you should not have to pay interests on the amount you already have paid back. Because of this, banks will usually compute compound interests not by year but by month:
For the sake of readability, from now on, let's rewrite (1 + r/12) to R and n becomes months and not years. Every month the bank computes the interest rate for the month (r/12). So if you were to pay every months, the formula for the first months would look like this:
Again we can see a pattern here:
If we develop this equation:
It's better but still a little awkward. The ellipsis means a loop, we can do metter than that, especially if we know that:
With this formula, we simplify to:
What does this formula gives us? On the left side, it will accumulate the amount of money that you still owe to the bank and on the right side the amount you already paid back. Once a reaches 0, you have paid back you loan in full, capital plus interest.
From that equation, we can then do some simulation. Let's assume you have a property in mind that costs 100000€, you are ready to spend no more than 600€ per months and the bank is asking for a 1% rate, how long would it take for you to pay back that loan? (we assume the rate does not depend on the loan term).
First, we have to turn the amount owed to 0:
And to solve the equation with unknown n. For this, we will be using the logarithm function. As you may know, the logarithm of a power will decompose the radical and the power as two factors:
We also get the following nice properties:
So let I = R - 1 then do some weird algebra gymnastic to isolate n:
So:
With the logarithm:
If we plug a=100000, m=600 and R=(1 + 0.01 / 12), then we get a little more than 179 months (almost 15 years).
You can manipulate that equation around to define different parameters as unknown, for example if you want to know how much you can borrow at a particular rate, with a predefined monthly payment on 20 years. It just requires a little bit of algebra.
The best is to use a spreadsheet for that. Set the initial equation and use the "goal seek" function to find 0 by varying a cell or another depending on what you want to know.
That's it! You are all set to become a real estate mogul, or at least a first time buyer... or remain a renter and watch price keep going up while playing around with your spreadsheet ! Fun !