Build My First Mortgage Calculator by Numpy in Colab
Mortgage repayment amount is hard to calculate by simple arithmetic as it is not based on a simple interest formula. Nowadays, almost all banks provide free Mortgage Calculators for people to know in advance how much they have to pay every month if they borrow mortgage loans to buy a property. The Mortgage Calculator can also act as a tool for stress tests (i.e. sensitivity analysis) as you can adjust the mortgage rate and the total number of repayment period, and the loan amount. Figure 1 shows a typical example which provides a slider for choosing the three parameters. When the cost of home is $876,000, with a downpayment (Deposit 20%) of $176,000, i.e. loan amount is $700,000. If the mortgage rate is 2.55% and the repayment period is 30 years, then the monthly repayment amount is $2784. If you think 30-year repayment is too long and don’t want to be a “mortgage-slave”, then, how about if we repay $4000 per month, how many months of repayment can be shortened? Or if interest rate is increased to 5%, how much more you will have to pay? These type of What-If questions is very common, and a Mortgage Calculator can be very helpful.
If there are many free Mortgage Calculators available on web, why we still have to build one? First, it may not provide the necessary What-if answers, for example, very few mortgage calculators will provide full amortization schedule which shows monthly interest and principle repayment amounts. Thus, this article tries to build a Mortgage Calculator using Numpy in Colab.
First, we install and import numpy and numpy-financial which provides pmt, ipmt, ppmt, pv and nper functions, details can be found at https://numpy.org/numpy-financial/.
!pip install numpy-financialimport numpy as np
import numpy_financial as npf
Slider Inputs
We try using a Slider for adjusting the 3 parameters, viz. (1) mortgage rate, (2) loan amount, and (3) repayment period, as shown in Figure 2. For more details on how to use Sliders to input data, please read https://colab.research.google.com/notebooks/forms.ipynb#scrollTo=eFN7-fUKs-Bu
#@title MORTGAGE CALCULATOR - INPUT DATA
mort_rate = 2.55 #@param {type:"slider", min:0.00, max:10.00, step:0.05}
loan_amt = 700000 #@param {type:"slider", min:100000, max:1500000, step:10000}
repay_yr = 30 #@param {type:"slider", min:1, max:40, step:1}
We keep the same mortgage rate (2.55%), loan amount ($700,000), and repayment period (30-year). Let’s see whether our Mortgage Calculator can get the same result.
We first convert the annual interest rate in % to a monthly interest rate in two decimal places (rate_x), convert the number of repayment years to number of repayment months (nper_x), convert the loan amount to a negative sign (pv_x) to indicate that it is a cash outflow. The monthly repayment amount (pmt_x1) can easily be calculated by npf.pmt(rate_x, nper_x, pv_x) [If you prefer using Excel, all these functions are also available in Excel, and there are Mortgage Calculator templates in Excel available at https://templates.office.com/en-nz/mortgage-loan-calculator-tm10000110]
Calculate Monthly Repayment Amount
#Mortgage Calculator - monthly repayment amount
rate_x = mort_rate/1200 #monthly mortgage rate in 2 decimal places, 2% -> 0.02
nper_x = repay_yr*12 #duration of loan in number of months
pv_x = -1*loan_amt #loan principle amount in NZD as negative cash flow
pmt_x1 = npf.pmt(rate_x, nper_x, pv_x) #monthly mortgage repayment amount
print("Monthly Repayment Amount: NZD", np.round(pmt_x1,2))The answer shown below shows the same monthly repayment amount as the one estimated by the Mortgage Calculator in Figure 1.
Monthly Repayment Amount: NZD 2784.08Amortization Schedule:
Simply using ipmt and ppmt functions and a “for loop” to repeat 360 times will produce a full amortization schedule as shown in Figure 3.
#Amortization Table - mortgage interest and principle repayment schedule
per_x = np.arange(nper_x) + 1
ipmt_x = npf.ipmt(rate_x, per_x, nper_x, pv_x) #monthly mortgage interest amount
ppmt_x = npf.ppmt(rate_x, per_x, nper_x, pv_x) #monthly mortgage principle amount
fmt = '{0:2d} {1:8.2f} {2:8.2f} {3:8.2f} {4:8.2f}'#format
for paymentno in per_x:
index = paymentno - 1
pmt_x = ipmt_x + ppmt_x
pv_x = pv_x + ppmt_x[index]
print(fmt.format(paymentno, ppmt_x[index], ipmt_x[index], pmt_x[index], pv_x))
Total Amount of Interest Paid can also be calculated by using np.sum(ipmt_x):
#Total Amount of Interest
totint_x = np.sum(ipmt_x)
print("Total Amount of Interest Paid: NZD", np.round(totint_x, 2))The amount of interest paid is shown:
Total Amount of Interest Paid: NZD 302268.02What-if Question
How can we answer the what-if question of how many repayment months will be if we pay $4000 per month? Since the question is asking the number of repayment period, we shall use the function np.nper.
The only parameter to be changed is the monthly repayment amount, pmt. So if we replace the original pmt_x by whatif_pmt_x = $4000, nper function will produce the whatif_nper = 219 months. In other words, we can reduce repayment period from 30 years to about 19 years.
whatif_pmt_x = 4000
whatif_nper = npf.nper(rate_x, whatif_pmt_x, pv_x)
print("New Repayment Period - Number of Months:", np.round(whatif_nper,0))New Repayment Period - Number of Months: 219.0
I have uploaded My1stMortgageCalculator at the following Github: https://github.com/Chung-collab/great/blob/master/my1stAVM.ipynb for knowledge co-creation.
I also produce a Youtube at Yiu (2021) to explain in more details.
[Caveat: this is just my first trial and the estimate is not sophisticated enough to discuss for real life estimations. Please do not consider the results as any advice on mortgage repayments.]
Reference
Yiu, C.Y. (2021) Build a Mortgage Calculator by Numpy in Colab, Youtube, July 14. https://youtu.be/VH4FTAJruqk
