Simple Mortgage Calculator

In what might be my most boring post to date, here is a small piece of ‘software’ I wrote when trying to better understand mortgage options. I guess it does at least illustrate how coding can be a ’life skill’ to make sense of things, and allowed me to reflect on ‘good enough’ solutions.

Recently I was looking at mortgages (though everything here could be applied equally well to any loans of a similar nature). I encountered a few options, some of which were had a lower interest rate but involved an arrangement fee, and others which were higher interest, but involved no fee. On top of this the interest could be fixed from anywhere between 2 to 5 years.

It was not immediately clear to me up-front which of these was better. Mortgage calculators exist on other websites, with the MoneySavingExpert suite being perhaps the most reliable. However, they are all ‘put numbers in, get numbers out’ web apps. They do not make it easy to tweak and compare results, probe intermediate data in detail, or understand how things work.

So I decided to write my own. Bender_BlackJack.gif

You can find it on GitHub .

Setup

The first step was to initialise something with all the typical parameters from a loan:

• Value
• Arrangement fees (added to loan at day 1)
• Annual interest rate
• Length of loan (term)

Based on these parmeters I want to be able to calculate the regular repayments and step through year-by-year examining the interest, repayments and balance at each stage.

I want a clear output of this that will allow me to rapidly tweak the parameters and easily compare different options.

Some maths

Firstly, we need to add any arrangement fees to the value of the loan. Whilst fees often entail a lower interest rate, they will increase the interest paid by increasing the up-front value of the loan. This creates a trade-off with it not being immediately clear which is more economical to the borrower.

Onto the meat tempeh of the matter – the annuity formula.¹ I won’t undergo a full derivation/proof, just enough as I required to get my head around the internal workings. If we have a loan of present value \(V_0\) at interest rate \(r\) then after \(n\) intervals the future value is calculated from a geometric progression:²

\[ V_n = V_{n-1} \cdot (1 + r) = V_0 \cdot (1 + r)^n \]

But we need to add into this the effect of regular fixed repayments \( A \):

\[ V_n = V_{n-1} \cdot (1 + r) - A \]

which comes out with the same term for interest, and the sum of a geometric series for the repayments:

\[ V_n = V_0 \cdot (1 + r)^n - A \sum_{j=0}^{n-1} (1 + r)^j \]

which using the term for the sum of a geometric series can be written:

\[ V_n = V_0 \cdot (1 + r)^n - A \frac{1 - (1 + r)^n}{1 - (1 + r)} \]

and simplifies to:

\[ V_n = V_0 \cdot (1 + r)^n - A \frac{(1 + r)^n - 1}{r} \]

Based on the assumption that we want the loan to be zero after period \( n \) we can set \( V_n = 0 \) and rearrange to get an equation for required fixed repayments:

\[ A = V_0 \cdot r \frac{(1 + r)^n}{(1 + r)^n - 1} \]

As a final point I have been purposefully vague about “applying over period \( n \)”. This is because we need to work in months rather than years. Whilst interest rates are quoted on an annual basis, repayments and interest are added on a monthly basis. Therefore we need to convert from the annual rate. For compound interest³ this is as simple as dividing the annual rate by the number of sub-periods e.g. an annual rate of 6% is a monthly rate of 0.5% and a daily rate of ~0.016%. So when deploying the above formula in code the values we substitute in for \( r \) will be the monthly rate, and \( n \) will be the length of the term in months.

Implementation

I chose to do this in Python, with a sprinkling of numpy. I created a Mortgage class that takes the key parameters, with a run_estimate() method that performs the above calculations and then steps forward through the loan period to generate data of interest. This has been packaged so that it can be used as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

import mortgage

my_mortgage = mortgage.Mortgage(
    loan=10000.00,
    fee=999.00,
    interest_rate=4.5,
    fixed_term=3,
    full_term=10,
)

my_mortgage.run_estimate()

for a loan of £10,000.00 with arrangment fee of £999.00 at an interest rate of 4.5% (fixed for 3 years) over a term of 10 years.

The summary information is output to screen for easy interpretation:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

================================================================================
Analysis for loan=10000.00, fee=999.00, interest rate=4.50%, term=10y:
================================================================================

===================================
Monthly repayments are: 113.99
===================================

=========================
Short form output:
=========================
Start of Loan:  balance = 10999.00,     repaid = 0.00,  interest = 0.00
After year 1:   balance = 10107.84,     repaid = 1367.88,       interest = 476.72
After year 2:   balance = 9175.75,      repaid = 1367.88,       interest = 435.79
After year 3:   balance = 8200.83,      repaid = 1367.88,       interest = 392.96
==== End of fixed-term ====
After year 4:   balance = 7181.11,      repaid = 1367.88,       interest = 348.16
After year 5:   balance = 6114.56,      repaid = 1367.88,       interest = 301.33
After year 6:   balance = 4999.02,      repaid = 1367.88,       interest = 252.34
After year 7:   balance = 3832.22,      repaid = 1367.88,       interest = 201.08
After year 8:   balance = 2611.83,      repaid = 1367.88,       interest = 147.49
After year 9:   balance = 1335.36,      repaid = 1367.88,       interest = 91.41
After year 10:  balance = 0.25,         repaid = 1367.88,       interest = 32.77

There are some options for more detailed output if dersired.

You can find the code on GitHub here: GitHub/jatkinson1000/simple-mortgage-calculator along with basic instructions and documentation.

Usage

So, did it work?

Yes, based on a current mortgage the monthly repayments are calculated correctly. Further comparisons to the MoneySavingExpert calculator also produce consistent numbers.

It allowed me to answer questions like “is a fixed rate of 4.5% with arrangement fee £99 better or worse than a fixed rate of 5% and no fees on a 2-year fixed term loan of £10,000 over 10 years?”:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

================================================================================
Analysis for loan=10000.00, fee=99.00, interest rate=4.50%, term=10y:
================================================================================
===================================
Monthly repayments are: 104.66
===================================
=========================
Short form output:
=========================
Start of Loan:  balance = 10099.00,     repaid = 0.00,  interest = 0.00
After year 1:   balance = 9280.78,      repaid = 1255.92,       interest = 437.70
After year 2:   balance = 8424.99,      repaid = 1255.92,       interest = 400.13
==== End of fixed-term ====
...

compared to

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

================================================================================
Analysis for loan=10000.00, fee=0.00, interest rate=5.00%, term=10y:
================================================================================

===================================
Monthly repayments are: 106.07
===================================

=========================
Short form output:
=========================
Start of Loan:  balance = 10000.00,     repaid = 0.00,  interest = 0.00
After year 1:   balance = 9209.20,      repaid = 1272.84,       interest = 482.04
After year 2:   balance = 8377.94,      repaid = 1272.84,       interest = 441.58
==== End of fixed-term ====
...

we see that despite having lower repayments the lower interest rate with a fee actually leaves us around £50 worse off after the 2-year fixed term, at which point we move onto variable rates or negotiate a new deal.

Is it perfect?

No. Comparing to an existing loan the interest calculated can be off by pence at some intervals. However, this is an error of order 0.01% so doesn’t impact the broader usage. It typically results in the final value at the end of the term (should be 0) being off by £1-10 – again within 0.01% so not a concern for my uses.

One change I did make was to allow interest calculations on a daily basis rather than monthly. My lender recently changed and I noticed in new statements that interest began to fluctuate more than it previously had, and was noticeably lower in February. With this quick addition I was able to see that it is indeed because the new lender is calculating interest on a daily basis rather than monthly.

Overall I’m satisfied with the code I wrote; it allowed me to answer some questions, improve my understanding, and experiment with rates, fees, and terms.

Improvements

There are various mortgage calculators on GitHub and beyond, all of which have various bells and whistles becoming bloated and with shiny web/app interfaces.

I just wanted something simple that was good enough to answer the questions I had in a clear manner. That being said, there are some small additions that I feel could be made:

• The daily rate doesn’t currently account for leap-years,
• There is no accounting for interest addition and repayment occuring on different dates of the month as is often the case in reality,
• There could be more attention to the rounding used (when to go up/down, what do banks do?) to improve consistency,
• Currently no incorporation of overpayments,
• Some plotting could be nice, but was not neccessary for what I needed,
• Simulate the effect of changing rates in future to help decide on fixed-rate terms. I feel this is non-trivial, however.

I may come back to implement some of these in the future if I have need, but this is open-source code , so I’m also happy for people to make additions themselves provided the overall tenets of simple and transparent are preserved.

References

• Deriving the mortgage payment formula by Jessy M. The best ‘basic’ explanation for the formula I found.
• I should shoutout mortgage by jlumbroso on GitHub which performs a similar function with a similar approach. However, not quite in the way I wanted here.