Mortgage Calculator 2.0

Photo by Zofia Sarnowska on Unsplash

This is the first article in a series about “down-to-earth” analytics.

Introduction

A typical mortgage calculator provides an answer to a simple question “what will be your monthly payment given…”

• Loan amount (L in currency)
• Loan term length (n months)
• Interest rate (i annual)

However, a few further questions remain unanswered:

• How much should I put as a deposit? Why it matters: the interest rates differ depending on the loan-to-value ratio
• For how long should I take a mortgage? Why it matters: the longer the term, the smaller is the monthly payment; but total interest payment rises
• What is the maximum property value could I afford? Why it matters: if you are looking at a “dream home” and think you could not afford it, maybe you actually could

This article a) helps to understand the magic behind the basic calculations, and b) provides a decision support approach, based on which you could make a data-driven choice.

Note: decision support means that one still needs to apply her critical judgment.

Mortgage calculation basics

First, let’s review the logic of conventional mortgage calculators (with fixed monthly payments).

Let L be loan amount, n — loan term length in months (e.g. for 25 years it will be 25*12 = 300 months), i — annual interest rate.

Monthly interest rate is j = (1+i)^(1/12)-1 (or quick and dirty i/12)

And monthly payment is M = L [ j(1 + j)^n ] / [ (1 + j)^n — 1]

Where does this M come from? To see what happens after each payment, let’s take a look at the table below:

Remaining debt after each payment

M is calculated based on the assumption that after the final payment the remaining debt is equal to zero.

0 = L(1+j)^n — M(1+(1+j)+(1+j)^2 + … (1+j)^(n-1))

The expression 1+(1+j)+(1+j)^2 + … (1+j)^(n-1) looks familiar, right?

Applying the sum of geometric series formula we get

0 = L(1+j)^n — M[(1+j)^n-1)/j]

and

M = L [ j(1 + j)^n ] / [ (1 + j)^n — 1]

In Excel — it is implemented as PMT() function. In Python — you can implement the formula yourself. In addition, I am using a loop over all time periods to record the outputs for further analysis and decision-making.

Monthly_Payment = Loan_Amount*j*((1+j)**n)/((1+j)**n-1)
Monthly_Interest = []
Monthly_Balance  = []
for i in range(1,n+1):
    Interest = Loan_Amount*j
    Loan_Amount = Loan_Amount -(Monthly_Payment-Interest)
    Monthly_Interest = numpy.append(Monthly_Interest,Interest)
    Monthly_Balance = numpy.append(Monthly_Balance, Loan_Amount)

Broader perspective

Now we can calculate the monthly payment for each combination of inputs (Loan amount, Loan term length, Interest rate), so let’s try to bring this a bit down to reality.

Financial organizations typically differentiate interest rates based on the so-called “loan-to-value” (LTV) ratio, and particularly during COVID many UK banks stopped offering loans with LTV ≥ 90%.

Illustration of how the rates could look like

For example, if today I search for the UK deals (COVID, 22/09/2020):

• LTV = 90% gives only three deals with the best rate of 3.29%
• LTV = 80% gives quite a few deals (18 pages) with the best rate of 1.8%
• LTV = 75% gives even more deals (37 pages) the best rate of 1.65%

Examples of requests — search for the deals

So if you have some choice in the amount of money to put as a deposit and a choice for loan term length, you take your decision based on the following:

• Monthly payments (in currency) — can you afford it given your income?
• Total interest (in currency and % of the loan amount) — would you feel comfortable to overpay e.g. 50%+ of loan value?
• Alternative earnings for this money (outside of this model, but worth considering) — how much could you earn if put this money elsewhere?
• Qualitative assessment of “to what extent are you willing to move in now or can wait” (outside of this model — very subjective, probably cannot be modeled, but I will talk about it in one of the next articles about Help-to-Buy program)

Decision support example

Let’s now set up a few lists with the values at which we will be looking, namely:

• Property value rang — 600K-700K with a step 20K
• Deposit range — 50K–160K with a step 10K
• Loan term length — 25, 30, 35 years
• Interest rate — for this exercise I use 1.5%–4.0% with step 0.5 p.p.

Sales_price_min = 600000 # property value min
Sales_price_max = 700000 # property value max
Sales_price_step = 20000
Deposit_min = 50000 # deposit value min
Deposit_max = 160000 # deposit value max
Deposit_step = 10000
Sales_Price_list = list(range(Sales_price_min, Sales_price_max+Sales_price_step, Sales_price_step))
Deposit_list = list(range(Deposit_min, Deposit_max+Deposit_step, Deposit_step))
Loan_Term_list = [25, 30, 35]
Interest_Rate_list = [1.5, 2.0, 2.5, 3.0, 3.5, 4.0] 

Using the approach from “Mortgage calculation basics”, I calculate the output for all possible combinations. I run this in a loop and save all outputs to a pandas data frame, filter it a bit (e.g. drop rows with LTV > 90%, or rows with high LTV and low-interest rates, based on subjective hard-coded thresholds) and export to .xlsx or .csv.

An output table imported to Excel — looks quite messy, what to do with it?

Now, what should we do with this? Excel is often underestimated and even contemned (especially in the computer science community), but it provides a great interface for data analysis and visualization.

Using pivot-tables and conditional formatting we can see that (as expected):

• Longer loan term length leads to smaller monthly payment, but larger total interest paid; with a more substantial difference at higher interest rates
• Higher interest rate leads to higher monthly payment, and substantially higher total interest paid
• Larger deposit allows to reduce monthly payment and total interest paid, but the difference in total interest paid is smaller than the difference in the marginal amount of money added as a deposit — so larger deposit serves more as a tool to get a lower interest rate.

How to read the tables:

• 600K, 620K … 700K — options for property value
• 100K, 110K … 160K — options for deposit
• 1.5, 2.0 … 4.0 — options for interest rate
• 25, 30 — options for the loan term

Monthly payment depending on property value, deposit, and interest rate

Total interest payments depending on property value, deposit, and interest rate

Now to make it even more tangible, assume one does not want to pay more than 2500 (GBP) per month; and will be happier if she has to pay not more than 2000 (GBP) per month. The table below shows that if a deposit of 25% is available, one could get quite acceptable conditions even with higher property value (of course, a cross-check with the total interest in a table above and willingness to spend that much extra is required)

Monthly payment depending on property value, deposit, and interest rate

Conclusions

• If you could do some analysis to help you in decision-making — do it!
• Monthly payment and total interest payment are very sensitive to interest rates, so try to get the best deal
• There is a trade-off between monthly payments and total interest payments, so you have to balance short- vs long-term perspectives (more on this in one of the next articles)
• There is no need to put more as a deposit than necessary to get a good deal, i.e. use the lowest possible amount that gives a certain interest rate
• Of course, there is a qualitative element, e.g. value of living in a “home of a dream” already today, and it will influence the decision