HOW TO - Calculate average daily balance on bank statement in excel

Introduction

A common task that I want to analyze is the average daily balance from my bank statements.

Now this is often used by banks and credit card companies to calculate interest charges. I sometimes need to calculate this for my tax purposes.

In this post, I will go over my steps to calculate this:

1. Download the bank statement from your bank
2. Extract the data in PdfDodo and download it into Excel
3. Review and fix the data so that we do not have missing days/ transactions
4. In Excel, you can simply sum all the daily balances and divide by the number of days.

👉 Use PdfDodo to extract the data to Excel

If your bank only provide statements in PDFs, then consider using PdfDodo to extract the data and convert it to Excel.

To do start the Excel conversion, we go through the following steps:

• Step 1: Get an account with PdfDodo
• Step 2: Go to the home page and click “Upload Document”

• Step 3: After the document has uploaded - click “Start Analysis”. This will usually take a few seconds.

• Step 4: Click on the uploaded document link to see the results.

After PdfDodo has completed analysis, it will list out all the data that it thinks is in a table format.

Now we can see our result and download the Excel or CSV file:

After that, can open up the Excel file and start doing our analysis.

Finding the average daily balance of the bank statement

So after we have the data in excel we can clean it up to do our average balance calculation.

So for example, in our example bank statement above we remove other columns and only have the date and balance column:

Date	Daily Balance
12-Jul 20	$3619.43
16-Jul 20	$3855.49
23-Jul 20	$4096.51
…	…

Generally, the idea is to find the sum of the balance and divide by the number of days.

We can see that not all days are accounted for - eg missing 13 Jul, 14 Jul, 15 Jul, etc. So we need to fill those out manually. (The missing days will have the same balance as the previous day)

The final table will look like this:

Date	Daily Balance
12-Jul 20	$3619.43
13-Jul 20	$3619.43
14-Jul 20	$3619.43
15-Jul 20	$3619.43
16-Jul 20	$3855.49
…	…
…	…
23-Jul 20	$4096.51
…	…

Calculate the SUM of all balances

Now In a blank cell enter the formula =SUM(B2:B9). This will give you the total of all the daily balances.

Calculate Number of Days

If you have a date range, the number of days can be found using the COUNT function. For example, if the dates are in column A from A2 to A9, in another blank cell (say, C10) enter the formula =COUNT(A2:A9). This will give you the number of days you have balances for.

Calculate Average Daily Balance

In another blank cell (say, D10), divide the total daily balances by the number of days: =B10/C10.

The result in D10 will be the average daily balance for the period you have data for.

Dealing with missing days

One common issue that can come up is that not all days and their bank balances are listed in the transactions table.

Generally there are a few ways to approach it.

Method 1: Assume a constant balance for missing days

This is my preferred method to deal with missing days. Generally when you get the statement from your bank - the missing days will mean that the balance didn’t change on the missing days.

We can simply use the last known balance for those days. This method assumes there were no transactions on the missing days.

For example, if you have the following data:

• August 1st - $3000
• August 4th - $4000

However there is no data for August 2nd and 3rd, you can assume the balance for August 2nd and 3rd was the same as on August 1st - $3000.

The following methods are approaches on how to deal with data that we know are missing!

Method 2: Interpolate balances for missing days

Now if we know that there is actual data missing, we can “interpolate” (or guess) the balance amount.

For instance, if on August 1st the balance was $1,000 and on August 4th it was $1,300, you might assume that on August 2nd it was $1,100 and on August 3rd it was $1,200.

If you want to do this in Excel:

• Calculate the difference in the balance between two known dates.
• Divide the difference by the number of days between the two dates to get the daily change.
• Multiply the daily change by the day number (for the missing day) and add it to the earlier known balance.

So as from our previous example, with the following data:

• August 1st - $3000
• August 4th - $4000

We can calculate that the daily change is ($4000 - $3000) / 3 (number of days between the 4th and 1st). This results in roughly $333 Now if we need to interpolate the balance for the August 2nd, we take $333 and add it to the August $3000 value -> $3333.

Ignore missing days:

One other option is to just simply ignore the missing days and only calculate the average of the days you have.

This may skew the average if the missing days had significantly different balances than the days you have data for.

Fill with zero or another constant

If you have a reason to believe the balance on missing days was zero or another specific amount, fill in those days with that balance.

Refer to external sources

If you know that the data is missing and there are external documents you can refer to - such as credit cards, other bank statements then we can use this method.

This will generally be more time consuming than the other methods but may give a more accurate overview.

Final thoughts

Overall calculating the average daily balance on your bank statement involves taking the total number of days and dividing it by the SUM of all the balances.

We can achieve this by first downloading the bank statement from your bank. Most likely it is going to be in PDF format, so we can use PdfDodo to extract the data.

After than we need to clean up the data - most likely deal with missing days. There are multiple ways to deal with missing data, but if the statement is coming from your bank, then we can use a constant value for the missing days. We can simply use the last known balance for those days.

If the data is actually missing - we can guess the data, ignore it or go through other external documents to check.

We can then use the SUM(Balances) and COUNT(days) formulas to figure out the average daily balances.