Simple Mortgage Calculator With Python and Excel

Automate your real estate analysis with Pandas and Numpy

Pendora
Pendora
May 23, 2020 · 7 min read

Real estate analysis isn’t complete without automating the calculation of mortgage payments. We’ll look to create our mortgage calculator from scratch in both Excel and Python. By the end of this article, you’ll know exactly how to conduct your analysis for different types of loans and mortgages when you’re confronted by advisers who are compensated based on your business with them. Choose the best deal for you and your family, not the best deal for the bank providing the mortgage.

For business owners looking for mortgages to take on for business purposes, this may be helpful in your search for the best property. Feel free to take this calculator and add it to your real estate analysis toolkit. If you follow along for both, you’ll have a deep understanding of how things work, but feel free to jump to the Excel or Python sections however you please.

Photo by Vita Vilcina on Unsplash

Excel Mortgage Amortization Schedule

Starting with our assumptions, we need to set up cells with every variable in the mortgage calculation. The interest rate, amortization period, payments per year, and the mortgage amount (principal). We’ll use a mortgage to be paid off in 30 years, and monthly payments (12 per year). Sometimes mortgages will need to be paid bi-weekly (24 per year), and the compounding would look a little differently. Usually, lenders will provide the interest rate as an annual rate, so all we need to do is divide by the number of payments per year.

Next, let’s set up our Excel table, including all of the aspects of the calculation.

Table 0: Beginning Amortization Table Template

Keep in mind that each period represents one month, and although we’ll only be showing the first 5 periods, we need to copy the formulas down to 360 months (30 years). The first row (period 0) has no payments because we just took out the mortgage, the first payment occurs in period 1.

The beginning balance of period 1 should be linked to the ending balance of the period before.

Table 1: Linking Beginning Balance to Previous Ending Balance

Next, we can use a powerful Excel function PMT to simplify the calculation of monthly payments. A bit more finance knowledge is required, but the mathematical formula to do so would be:

Feel free to dig into this formula more if you like, but the PMT function reduces all of the calculating for us.

Payment = PMT(Monthly Interest Rate, Periods, Mortgage)

In our case, monthly interest is 4% divided by 12 payments per year, 360 periods, and $400,000 mortgage. Since these things don’t change, we need to use absolute cell references to ensure the proper cells are referenced.

I’ve also calculated the interest paid using the monthly interest rate multiplied by the beginning balance. To see exactly how cumulative interest we’re paying overtime, I’ve also added a column for cumulative interest, which just takes the interest paid for the current period and cumulative balance the period before.

Remember that each monthly payment includes a split between the principal paid off, as well as the interest. Lock the interest rate in place so things don’t change when we copy the formula down. If we know interest paid is $1,333 in month 1, we subtract the interest paid from the monthly payment to get the principal paid. Lastly, the ending balance should just be the beginning balance minus the principal paid:

In the end, the formulas for the first period should look like:

Now we just need to copy the row downward to complete the table.

Completed Amortization Table with First and Last 5 Rows Shown

Perfect, now we’ve completed our very own mortgage amortization calculator. If we add the sum of every amount in the principal paid column, we should arrive at $400,000. Looking at our cumulative interest column, we see that the total cumulative interest paid on the $400,000 mortgage is $287,478 over 30 years. Notice that interest paid every period declines, since the balance interest is charged on is slowly repaid over time. Near the end of the mortgage, nearly all of the payment goes to pay off the actual mortgage.

Python Mortgage Calculator

To begin, let’s first see how we can use a simple Python script to check our answer. Notice that this code is stinky, and is pretty difficult to follow. We’ll be using a much better library and function later.

Expected Code Output

This code puts the mortgage payment calculus into Python, and we can see that our output matches our own Excel. Numpy helps us with this calculation in a similar way that Excel did, so let’s get fancy.

Importing Libraries

As you can probably tell based on our Excel calculator, to recreate the same thing in Python means we should probably use Pandas.

import pandas as pd
import numpy as np
from datetime import date

Numpy helps us with the calculations, and Pandas will help us with creating the row-by-row calculator. Let’s first start by setting up the same assumptions. Assume that we take on the mortgage on January 1, 2021.

interest = 0.04
years = 30
payments_year = 12
mortgage = 400000
start_date = (date(2021, 1, 1))

Here’s the Numpy equivalent of the PMT formula in Excel.

Since we have two anchor points to check this answer with, it’s good that it matches. Let’s also calculate the first interest payment, as well as the first principal payment. We didn’t use these functions in Excel earlier (IPMT and PPMT), because we wanted to get a foundation of how things work, but now that we know how they work, we can use shortcuts instead.

Double-check with our Excel calculator’s first row and we’ll see that things match perfectly.

Awesome. Let’s now start to build our DataFrame which will house all of this data.

rng = pd.date_range(start_date, periods=years * payments_year, freq='MS')
rng.name = "Payment Date"
df = pd.DataFrame(index=rng, columns=['Payment', 'Principal Paid', 'Interest Paid', 'Ending Balance'], dtype='float')
df.reset_index(inplace=True)
df.index += 1
df.index.name = "Period"

We take our start date and increment each by one month. We also have an index called “Period” which matches the month of payment of our Excel calculator. Note that there is no 0th period in this code.

Not bad, and we can easily populate a few of the columns right away with the power of Numpy. The only hard column will be Ending Balance because I haven’t found a formula to calculate this for us yet and we’ll need to use subtraction similar to Excel.

The current DataFrame should look like:

Using the Numpy formulas from above, we use df.index to quickly populate the DataFrame. As mentioned, it takes some manual subtraction to figure out the Ending Balance, and the first period’s Ending Balance can be calculated using code below:

The df.loc property allows us to access a group of rows and columns. We know that the Ending Balance is our initial mortgage balance subtract the period’s Principal Paid. You can probably see already that a simple for loop will be able to do this repetition for us. We just have to keep in mind that the program needs a place to end, and this happens when the Ending Balance is 0.

Implementation of For Loop for Ending Balance Calculation

We didn’t include a Beginning Balance column, but we include it in the code to calculate Ending Balance.

At last, our Python calculator is done and our finished product should look like above. Sense check with the Excel one, and we’re now ready to tackle other real estate analysis. I like Python for the fact that we don’t need to manually count down to 360 rows in Excel, and it will do that for us. Play around with different mortgages, and watch your work come to life.

Saving time and energy is a must, and with this Python code, we can combine it with other similar projects, whereas Excel is more isolated and standalone. Congratulations on making it to the end.

For other Python and Personal Finance projects:

The Startup

Get smarter at building your thing. Join The Startup’s +794K followers.

Sign up for Top 10 Stories

By The Startup

Get smarter at building your thing. Subscribe to receive The Startup's top 10 most read stories — delivered straight into your inbox, once a week. Take a look.

By signing up, you will create a Medium account if you don’t already have one. Review our Privacy Policy for more information about our privacy practices.

Check your inbox
Medium sent you an email at to complete your subscription.

Pendora

Written by

Pendora

Investment banker, global citizen interested in the pursuit and sharing of knowledge. Inquiries to pendorapubs@gmail.com

The Startup

Get smarter at building your thing. Follow to join The Startup’s +8 million monthly readers & +794K followers.

Pendora

Written by

Pendora

Investment banker, global citizen interested in the pursuit and sharing of knowledge. Inquiries to pendorapubs@gmail.com

The Startup

Get smarter at building your thing. Follow to join The Startup’s +8 million monthly readers & +794K followers.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store