Simple Mortgage Calculator With Python and Excel
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.
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.
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.
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.
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.
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.
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.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.
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 4% Rule of Retirement Is Now Obsolete
Here’s How to Prepare for Your Retirement and Why You Need Additional Sources of Income.
Fundamental Analysis of Stocks for Programmers and Beginners
The crucial accounting and finance topics you need to know to invest like a seasoned professional