Mortgage/Loan Interest Validator – Is Your Bank Making a Mistake?

Is your bank charging the right interest?

If you have a mortgage, you may have wondered if your bank charges your interest correctly. What if the bank didn’t factor in all your offset account balances? What if they charge incorrect rate? Or perhaps you are just curious.

There can be a few reasons how a bank may miscalculate your interest:

  1. Your offset account(s) aren’t linked properly or not linked in time, such as this event. This could be due to a loss of communication, human error, or system error or anything.
  2. Incorrect interest rate. For whatever reasons, the interest rate is wrong. This could be because the introductory/package/discretionary discounts were not applied properly, miscommunication, or anything.
  3. Incorrect payment types, such as Interest Only or Principal + Interest. This is usually a system error, but can be a human error too.

To do this manually in Excel can be very troublesome and time-consuming. This simple code below will automatically validate every interest charges in your home loan.

After you have followed the procedure below, you will find that validating your interest will take a matter of minutes only. And if you haven’t installed Anaconda already, I would recommend you do that now.

If you ever worried that your data maybe compromised. Fear not, that this code below is completely offline, no data will be uploaded anywhere.

Download the Mortgage and Offset Transactions

First of all, you need to download all your home loan and offset accounts transactions in CSV. This feature usually available in all banks, if you could not find this feature in your online banking, then you should contact your bank.

FYI, if you have already created your own budget manager, you don’t need to do this again.

After you downloaded your CSVs, you need to open them in Excel and remove all columns except description, transaction date and value. In that order. Don’t worry about the column names.

However, please make sure the following:

  1. The transaction values are correctly signed (positive for deposit/negative for interest or redraws).
  2. All interest charges have the word ‘interest’ in it. Case doesn’t matter.
  3. All payments have the word ‘payment’ in it. We may use this in the future.
  4. The first row has to be the initial balance of the account.

Finally, we need the interest rates of the loan. To do that, you need to create a new Excel file, and call it ‘interest_rate.xlsx’.

In that file, there should be only two columns: The interest effective date and the interest rate itself.

The Code – Comments Inside

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

#put all your csv and py files in this location
path='C:/Users/user/OneDrive/Documents/Blogging/Mortgage Validator/'
number_of_offset = 2 #change for the number of offset accounts

if __name__ == "__main__":
def main():
    #import the mortgage file
    mortgage_df = import_file(path+'Mortgage1.csv')
    #prime the mortgage data
    mortgage_df = format_mortgage_data(mortgage_df)
    #add the interest rate data
    mortgage_df = addInterestRate(mortgage_df)
    #add all the offset files
    mortgage_df = merged_offsets(mortgage_df)
    #calculate the daily interest
    mortgage_df = calculateInterest(mortgage_df)
    #export the output to excel
    #import the mortgage file
    mortgage_df = import_file(path+'Mortgage1.csv')
    #prime the mortgage data
    mortgage_df = format_mortgage_data(mortgage_df)
    #add the interest rate data
    mortgage_df = addInterestRate(mortgage_df)
    #add all the offset files
    mortgage_df = merged_offsets(mortgage_df)
    #calculate the daily interest
    mortgage_df = calculateInterest(mortgage_df)
    #export the output to excel
def merged_offsets(df):
    df['total_offset_balance'] = 0

    while i <= number_of_offset:
        #import the offset file
        offset_df = import_file(path+'offset'+str(i)+'.csv')

        #format the offset data
        offset_df = format_offset_data(offset_df)
        #merge the offset data with the mortgage
        df = pd.merge(df, offset_df, on="Date",how='outer')
        #rename the offset balance column
        df = df.rename(columns={"offset_balance": "offset"+str(i)+"_balance"})
        df['total_offset_balance'] = df[['total_offset_balance','offset'+str(i)+'_balance']].sum(axis=1, numeric_only=True)
    return df

def import_file(filename):
    df = pd.read_csv(filename,header=0,names=('Description','Date','Amount'),thousands=",",parse_dates=['Date'],dayfirst=True)
    #first, sort data by date
    df = df.sort_values(by=['Date'])
    return df

def format_mortgage_data(df):
    #then we need to separate each description to each columns
    #Interest columns
    df['Interest'] = np.where(df['Description'].str.contains('interest',case=False), df['Amount'], 0)
    #Repayment columns
    df['Payment'] = np.where(df['Description'].str.contains('payment',case=False), df['Amount'], 0)
    #Other columns
    df['Other'] = np.where(~df['Description'].str.contains('payment|interest',case=False), df['Amount'], 0)

    #Then we need to group duplicate dates in one row
    df = df.groupby(['Date'])[['Interest','Payment','Other']].sum().reset_index()

    #fill the gaps between dates with time series
    r = pd.date_range(start=df.Date.min(),
    df = df.set_index('Date').reindex(r).fillna(0.0).rename_axis('Date').reset_index()
    return df

def format_offset_data(df):
    #then we need to separate each description to each columns
    #Debit columns
    df['Debit'] = np.where(df['Amount'] < 0, df['Amount'], 0)
    #Credit columns
    df['Credit'] = np.where(df['Amount'] > 0, df['Amount'], 0)

    #Then we need to group duplicate dates in one row
    df = df.groupby(['Date'])[['Debit','Credit']].sum().reset_index()

    #fill the gaps between dates with time series
    r = pd.date_range(start=df.Date.min(),
    df = df.set_index('Date').reindex(r).fillna(0.0).rename_axis('Date').reset_index()
    #calculate daily balance
    df['Balance'] = (df['Debit']+df['Credit']).cumsum()
    #drop the Debit and Credit as we no longer needing them    
    df = df.drop(columns=['Debit', 'Credit'])

    #rename the offset balance column
    df = df.rename(columns={"Balance": "offset_balance"})
    #Forward fill the balance until today
    df = df.set_index('Date').reindex(r).fillna(method='ffill').rename_axis('Date').reset_index()
    return df

def addInterestRate(df):    
    rate_df = pd.read_excel(path+'interest_rate.xlsx',header=0,names=('Date','Rate'))

    r = pd.date_range(start=df.Date.min(), end=df.Date.max())
    #Forward fill the rate dataset with the rates for each day
    rate_df = rate_df.set_index('Date').reindex(r).fillna(method='ffill').rename_axis('Date').reset_index()
    #join the table to put the interest rate in the daily data
    df = pd.merge(df, rate_df, on="Date",how='outer')
    return df

def calculateInterest(df):
    #Now we have a daily dataset with Interest and payment amount populated.
    #Then we need to calculate the daily balance
    df['Balance'] = (df['Interest']+df['Payment']+df['Other']).cumsum()
    #Calculate the accrued daily interest
    df['AccruedDailyInterest'] = (df['Balance']+df['total_offset_balance'])*df['Rate']/365
    #Calculate the accumulated interest accrued, and reset the amount each time the interest is charged by the bank
    df['AccumIntAccrued'] = df.groupby((df['Interest']!=0).cumsum()).AccruedDailyInterest.cumsum()
    #Calculate the interest that should be charged on the interest charged date
    df['AccruedMonthlyInterest'] = np.where(df['Interest']!=0, df.AccumIntAccrued.shift(1),0).round(2)
    #Calculate the interest difference
    df['InterestDiff'] = (df['AccruedMonthlyInterest']-df['Interest'])
    return df

def export_output(df):
    #export the file to excel

Final Thoughts

Is your interest charges different than what you have calculated? Try figuring out yourself why, before contacting your bank.

One of the reason may be:

  1. Incorrect interest rate applied. Check your statements and documentations.
  2. Different rounding method. Some banks may round the interest accrued daily. If this is the case the difference should be very small.
  3. Interest charged on a different day: Banks usually charged interest on the monthly anniversary day, however when this falls on weekend/ph, they may charge this on the following Monday. While this may be how it appear on your statement, the balance may factor the interest amount on the anniversary day still. Try adjusting the dates yourself to see if this is why.

In the future, I might expand into analysing how quickly will you pay off your loan based on current parameters.

Interview Tips in the Remote Working Environment

COVID has changed how we do interview. Here is my tips to ace them.

Article: Tips on how to ace a video interview — People Matters

In today’s climate, you are probable going to be invited for interview via video conference. Video interview can be very different and challenging than face-to-face. You may be invited by Skype, Teams, Zoom or other video conferencing applications.

Read more

SAS for SQL Developers

I am writing this guide for SQL developers out there that is learning SAS for the first time.

In case you are wondering, SAS is in the top ten data science programming language. In Australia, major corporations uses SAS in many of its departments. The one reason they do this instead of Python or R is because for its Enterprise Support.

The following guide is written for some of the most common SAS functions.

Read more

Budget Manager: BillSense and BalanceSense

Recently one of the Big 4 Bank in Australia released a functionality in their Mobile Banking app called BillSense. It is a feature that analyses your transactions and predicts your future bills.

As a customer of the said bank, I understand how the BillSense work is by looking at my past transactions, and identify future bills from it. The app then asks my confirmation of which is bills, and which is not. This data then is used by the app to project future balance, and ask myself to set apart some funds for those bills.

While this is great, it is however aimed for the Millennials, in which I am not. For example, it doesn’t predict future income or other regular expenses such as dining out or groceries. Which means it doesn’t estimate future balance.

I know that I am disciplined with my spending and I have a very good idea of my regular bills. What I need therefore is a balance projection to predict when I will be able to achieve my savings or retirement goal.

Read more

The Best Android Auto Device is not what you think it is

For a few years now I have been wanting to upgrade my aging old car’s Head Unit, or otherwise called Radio or Stereo System, to a newer system that has Android Auto or Apple Car Play.

If you haven’t tried Android Auto or Apple Car Play yet, you should try and test drive from newer car models. They will blew your mind compared to old stereo system.

Read more

Creating DIY Budget Manager

Why create your own Budget Manager? Why not just use Mint or Money Brilliant, or YNAB, etc?
Because it’s more fun to create own yourself. Because all the existing ones just don’t quite right for me. Because this is perhaps the most important data that we have. Because it serves as great learning exercise to Python and data analysis. Because I have too much time in lockdown.

I like a simple and to the point solution. I need something that just works, I don’t need a fancy UI, or automatic Sync Online Banking transaction. I like something that can create categories according to my lifestyle, separate income, expense and investment, automate report creation to Excel, and I definitely want to own my data 100%, forever. I keep it with me even if I move banks, countries or planets. I also definitely want it 100% free. After all, we are trying to save money.

Unfortunately off-the-shelf solutions are often comes with subscription fee, and it is often difficult to export/import your data if I want to switch providers. It also requires an Online Banking permissions, and sometimes not compatible with Australian Banks, does not track cash transactions, and not to mention security or privacy concerns.

My DIY Budget Manager will use browser automation tool to download CSV data, import them to SQLite, and categorise them based on your frequently used transaction, and you have all of your data, forever.

Read more

A Short Rant about Mac

It is fair to say that I’m a Windows man through and through. Recently I obtained a Macbook Air 2008 (the very first Air!). Now, first of all, this is a very old Macbook (12 years at the time of writing), however I’m amazed that it holds itself well running some of the modern applications such as Chrome (with 4 tabs open), Safari, Microsoft Office, without crashing. I guess this is where Apple shines. A Windows equivalent spec would simply crash at startup logo. In fact, I doubt a 5-years old Windows laptop would still be usable.

However, this is where my rant began. While Macbook is a very simple user-friendly laptop, it is very difficult to configure otherwise. For example, as the laptop I got is second-hand, I need to factory reset it. The way to do this is simple restart it and press Command+R while it is booting. Then I am presented with the Recovery mode, and then simply choose Disk Utility and Erase the hard drive. So far so good.

Now comes the problem. Back to the main Recovery menu, I choose Reinstall Mac OS X Lion. I connect the laptop to the WiFi, press Agree, and the press Agree again. Finally I need to sign in to Apple. Then it says that this item is temporary unavailable. I tried again and again and it gives the same error.

I quickly searched online, and basically it is a security feature that the Macbook need to be signed in with the same user ID as it was first signed in with. This is very crappy feature that is not helpful at all. Especially when there are so many workaround for this, such as starting Internet Recovery, or reinstall from USB. Even if it works, it should just say that in the message in the first place.

Edit: I found out that the “this item is temporary unavailable” is not a security feature, but a poor implementation. The reason for this is because you need to purchase Lion OS X from the App Store with your Apple ID (using another Mac), then you will be able to reinstall Lion on that Mac using the same Apple ID. This is only needed for Mac that do not have Internet Recovery, so you will unlikely to have this problem.

Simple Mortgage Planner

Survey said that average Australian paid off their mortgage within 10 years, despite the average mortgage term is 30 years. This is probably due to the fact that interest rates has been declining for the past many years and Australian have been good with minimizing their spending.

While there are many calculators in the Internet, I find none serve the basic planning of extra prepayments (including the interest rate changes, planned bonuses, tax return, or planned large holiday).

Attached is a template and sample where you can tweak around and plan what your future balance may be like. See how soon you will be able to pay off your mortgage.