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:
- 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.
- 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.
- 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:
- The transaction values are correctly signed (positive for deposit/negative for interest or redraws).
- All interest charges have the word ‘interest’ in it. Case doesn’t matter.
- All payments have the word ‘payment’ in it. We may use this in the future.
- 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:
- Incorrect interest rate applied. Check your statements and documentations.
- Different rounding method. Some banks may round the interest accrued daily. If this is the case the difference should be very small.
- 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.
If you like this page, a donation can go a long way…
Make a monthly donation
Make a yearly donation
Choose an amount
Or enter a custom amount
Your contribution is appreciated.
Your contribution is appreciated.
Your contribution is appreciated.
DonateDonate monthlyDonate yearly
Big Data Guy 