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__":
    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
    export_output(mortgage_df)
    
    #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
    export_output(mortgage_df)
    
def merged_offsets(df):
    i=1
    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)
        
        i=i+1
    
    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(), end=datetime.now())
    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(), end=datetime.now())
    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
    df.to_excel(path+"output"+datetime.now().strftime("%Y%m%d_%H%M%S")+".xlsx")

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s