Loans are terrifying and confusing and downright frustrating. To this day, even after navigating the treacherous waters of taking out a mortgage, I still find myself a little confused about how it all fits together. One of the tools I came across that really helped clarify how my payments actually affected my loan balance, and the total interest I will ultimately pay, was an Amortization Table.
While playing with a free amortization table creator, I thought, “Hey, I’m in a pretty good mood, why not ruin it by trying to build this in Python??”. The good news is my mood is far from ruined and I actually plan on expanding and formalizing this code a bit in the future. The bad news is 18 year-old Tim would probably pretty disillusioned by seeing his future self write code and an article about a common financial table. It’s OK though, he won’t find out for a while yet ;).
Initial Variables
We’re going to need a few pieces of information to get started; namely, the loan_amount, the apr and the loan_term, in months. Here’s the variables initialized:
loan_amount = 18000 apr = 5.29 loan_term = 60
Calculating the Monthly Payment
The first step in understanding loan mechanics is calculating the monthly payment, based on the given loan amount, APR and term. We can use the formula below to calculate the monthly payment on our loan:
While we already have the loan_amount and loan_term defined, we haven’t encountered mpr yet.
APR Explained
APR stands for Annual Percentage Rate, and is used to calculate the interest accrued on a loan over the term of a year. Since we are making monthly payments, and the amount of the loan changes over the course of the year, we need to calculate the interest on a monthly basis. We’ll do this by converting the percentage rate to a decimal number, and then dividing that number by 12. This gives us our APR rate on a monthly basis, or, as I very unofficially refer to it, mpr.
apr /= 100 mpr = apr / 12
Now that we’ve got our variables correctly defined, we can go ahead and recreate the equation above in our Python code.
monthly_payment = (loan_amount * mpr)/(1-(1+mpr) ** -loan_term)
print(f'The monthly payment comes to ${monthly_payment}')
The monthly payment comes to $342.0789050954898
Not bad; a quick check against Bankrate.com confirms that our calcualtions are correct!
Now that we have the monthly payment, we need to figure out how much of that payment goes towards the balance of the loan, or principal (the money you actually borrowed) and the how much is paid in interest (the money you pay for borrowing money).
The remaining principal multiplied by the mpr gives us the amount of interest paid for a month; therefore, the paid directly to the principal is
To put this all together, and update our principal balance, we use the equation
Creating a Python Function for Monthly Payments
Now that we have the formulas figured out, we can put it all together in a function that updates the principal balance of a loan, given the loan amount, term, and monthly payment amount.
def make_payment(principal, mpr, monthly_payment):
'''Makes a 'payment' by subtracting and updated payment amount from the
principal. Returns the principal remaining, and the remaining loan term
'''
current_interest_payment = principal * mpr
current_principal_payment = monthly_payment - current_interest_payment
principal -= current_principal_payment
return round(principal, 2)
Now that we have our payment function written, we can simulate paying off the loan! If our calculations were correct, we should end up with a $0.00 balance after our 60th payment.
Using While Loop
The easiest way to implement our payment function is within a while loop. In Python a while loop will continue to execute as long as certain conditions are met; in our case, that condition is owing the bank money, or principal > 0.
Think of it this way: we’ll continue to make_payment() while our principal > 0.
Before implementing the while loop, we’ll declare two variables that will be updated as it runs. The principal variable is equal to the initial loan amount, and the term_remaining variable is set to the initial loan term. These variables will be updated in our while loop, leaving the original values untouched for future use.
Finally, we’ll add a print() statement so we can see the principal balance being updated in real time!
term_remaining = loan_term
principal = loan_amount
while principal > 0 and term_remaining > 0:
principal = make_payment(principal, mpr, monthly_payment)
term_remaining -= 1
print(f'Principal remaining: ${principal}')
Principal remaining: $17737.27 Principal remaining: $17473.38 Principal remaining: $17208.33 Principal remaining: $16942.11 Principal remaining: $16674.72 ... Principal remaining: $1353.36 Principal remaining: $1017.25 Principal remaining: $679.66 Principal remaining: $340.58 Principal remaining: $0.0
It appears everything is working as it should be; after the final payment we ended up with a $0 balance, and the loan fully paid off.
Time to start keeping track of our payments; before starting the while loop we’ll initialize a list to store he record of payments, and the balance remaining at each subsequent month.
term_remaining = loan_term
principal = loan_amount
# Iinitialize the payments list with the starting balance and term
payments = [[(loan_term - term_remaining), principal]]
while principal > 0 and term_remaining > 0:
principal = make_payment(principal, mpr, monthly_payment)
term_remaining -= 1
payments.append([(loan_term - term_remaining), principal])
payments
[[0, 18000], [1, 17737.27], [2, 17473.38], [3, 17208.33], [4, 16942.11], ... [56, 1353.36], [57, 1017.25], [58, 679.66], [59, 340.58], [60, 0.0]]
Formatting with Pandas DataFrames
This, in essence, is the amortization table. However it’s not that difficult to add some features to it to make it both more legible and more useful. For instance, we can format the table itself using the DataFrame object pandas library, as well as tracking the principal, interest, and total interest paid over the course of the loan.
import pandas as pd
amortization_table = pd.DataFrame(data=payments,
columns=['Month No', 'Principal Remaining'])
amortization_table.tail()
| Principal Remaining | Month no | |
|---|---|---|
| 56 | 56 | 1353.36 |
| 57 | 57 | 1017.25 |
| 58 | 58 | 679.66 |
| 59 | 59 | 340.58 |
| 60 | 60 | 0.00 |
Our table looks a little better, but it’s not exactly bursting with financial insight at the moment. We can beef it up quite a bit by modifying our make_payment() function to return more of the information we already have calculated within th function itself.
The astute observer will also see that the Month No column is no longer necessary; the DataFrame index can track that for us!
All we have to do is return the current_interest_payment and current_principal_payment that are calculated earlier in the function. We’ll do this as a list, so we can easily index or iterate through the different elements that comprise one payment.
def make_payment(principal, mpr, monthly_payment):
'''Makes a 'payment' by subtracting and updated payment amount from the
principal. Returns the principal remaining, and the remaining loan term
'''
current_interest_payment = principal * mpr
current_principal_payment = monthly_payment - current_interest_payment
principal -= current_principal_payment
return [round(principal, 2), round(current_principal_payment, 2), round(current_interest_payment, 2)]
Now to edit the while loop to accommodate the updated function. The payments list will be initialized with three numbers, for principal,current_principal_payment, and current_interest_payment. We also have to update the principal from the values returned from the make_payment() function. Now for a test run:
term_remaining = loan_term
principal = loan_amount
payments = [[principal, 0, 0]]
while principal > 0 and term_remaining > 0:
payment = make_payment(principal, mpr, monthly_payment)
principal = payment[0]
term_remaining -= 1
payments.append(payment)
[[18000, 0, 0], [17737.27, 262.73, 79.35], [17473.38, 263.89, 78.19], [17208.33, 265.05, 77.03], [16942.11, 266.22, 75.86], ... [1353.36, 334.64, 7.44], [1017.25, 336.11, 5.97], [679.66, 337.59, 4.48], [340.58, 339.08, 3.0], [0.0, 340.58, 1.5]]
Looks good! We’ll put this into a DataFrame for a more legible format:
term_remaining = loan_term
principal = loan_amount
# Iinitialize the payments list with the starting balance and term
payments = [[principal, 0, 0]]
total_interest = 0
while principal > 0 and term_remaining > 0:
payment = make_payment(principal, mpr, monthly_payment)
principal = payment[0]
term_remaining -= 1
payments.append(payment)
amortization_table = pd.DataFrame(data=payments,
columns=['Principal Remaining',
'Current Principal Payment',
'Current Interest Payment'])
amortization_table
| Principal Remaining | Current Principal Payment | Current Interest Payment | |
|---|---|---|---|
| 0 | 18000.00 | 0.00 | 0.00 |
| 1 | 17737.27 | 262.73 | 79.35 |
| 2 | 17473.38 | 263.89 | 78.19 |
| 3 | 17208.33 | 265.05 | 77.03 |
| 4 | 16942.11 | 266.22 | 75.86 |
| … | … | … | … |
| 56 | 1353.36 | 334.64 | 7.44 |
| 57 | 1017.25 | 336.11 | 5.97 |
| 58 | 679.66 | 337.59 | 4.48 |
| 59 | 340.58 | 339.08 | 3.00 |
| 60 | 0.00 | 340.58 | 1.50 |
Adding the Total Interest Paid
Not too shabby, be there’s a few more tweaks to make to the table to bring out even more info; adding the total interest paid, a fairly important number when comparing different loan options. To do so, we just have to initialize a total_interest variable and update it for each iteration of the while loop.
term_remaining = loan_term
principal = loan_amount
# Iinitialize the payments list with the starting balance and term
payments = [[principal, 0, 0, 0]]
total_interest = 0
while principal > 0 and term_remaining > 0:
payment = make_payment(principal, mpr, monthly_payment)
principal = payment[0]
term_remaining -= 1
total_interest += payment[2]
payment.append(total_interest)
payments.append(payment)
amortization_table = pd.DataFrame(data=payments,
columns=['Principal Remaining',
'Current Principal Payment',
'Current Interest Payment',
'Total Interest Paid'])
amortization_table
| Principal Remaining | Current Principal Payment | Current Interest Payment | Total Interest Paid | |
|---|---|---|---|---|
| 0 | 18000.00 | 0.00 | 0.00 | 0.00 |
| 1 | 17737.27 | 262.73 | 79.35 | 79.35 |
| 2 | 17473.38 | 263.89 | 78.19 | 157.54 |
| 3 | 17208.33 | 265.05 | 77.03 | 234.57 |
| 4 | 16942.11 | 266.22 | 75.86 | 310.43 |
| … | … | … | … | … |
| 56 | 1353.36 | 334.64 | 7.44 | 2509.79 |
| 57 | 1017.25 | 336.11 | 5.97 | 2515.76 |
| 58 | 679.66 | 337.59 | 4.48 | 2520.24 |
| 59 | 340.58 | 339.08 | 3.00 | 2523.24 |
| 60 | 0.00 | 340.58 | 1.50 | 2524.74 |
Now that we’ve got the table looking as it should, we can wrap all of this in a function for consistency. I’ve created a Github Gist for this that you can download and run as a CLI program, but you can assemble the code in any way you see fit. For a more in depth version of amortization using python, I have a repository that implements classes and some deeper analysis functions. Stay tuned for more, or check out some other tutorials!
