Create an Average Daily Balance Calculator in Excel

Whether you’re tracking your personal or business finances, an average daily balance calculator in Excel can help you get a better understanding of your loan payments, and it can also be useful for creating payment projections.

The average daily balance method is commonly used to calculate finance charges for credit card balances, as well as other types of loans, such as mortgages or auto loans.

In this post, we’ll start by talking through the concepts and demonstrate the related math with some basic screenshots. Then we’ll provide a template you can download that includes some fancy formulas that do all of the computations for you.

Note: feel free to scroll down to the end if you just want to grab the Excel template 🙂

Why is Average Daily Balance important?

Essentially, it can help you understand your credit situation and the amount of outstanding debt you have on a daily basis. Credit cards typically have a revolving balance, which means that you can carry a balance from month to month and accrue interest on it. 

At a high level, your average daily balance is found by taking the sum of your daily balances and dividing it by the number of days in the billing cycle. 

Here is a walk through of the basic mathematical concepts.

Average Daily Balance Using the SUM Function

Imagine you have a column for dates, transaction amounts, balance, number of days, and a total column. It could look something like this:

The Dates column represents the date range that you carried a specific balance. For example, if you make a purchase, you will carry that new balance until your next purchase or payment. You may have one transaction per day, or one transaction per week. In any case, the Date column includes the span of dates you carry a specific balance.

The Transaction Amount column is where you would store the transaction amount. If it is a purchase, it would increase the balance. If it is a payment, it would decrease the balance.

The Balance represents the cumulative debt. It is basically the previous balance plus the purchase amount (or minus the payment amount).

The # of Days column represents the span of the number of days in the corresponding Dates column.

The Total column is the balance multiplied by the number of days.

Next, let’s look at how the total is used to compute the average daily balance.

• In cell E10, we’ll count the total number of days in column E using the formula: =SUM(E3:E8), which should be the number of days in the month.
• In cell F10, sum the total column using the formula: =SUM(F3:F8)
• In cell E12, divide the total by the number of days to compute the average daily balance: =F10/E10

The result is the average daily balance for the month. With this, we can estimate the finance charge for the month.

How to Find Finance Charges Using the Average Daily Balance Calculator in Excel

Remember that the average daily balance method uses the balance on each day of the billing cycle.

To calculate finance charges this way, you need to know the interest rate % (APR), the length of the billing cycle, and, of course, the average daily balance on the account over the course of the billing cycle.

Let’s say your credit card has an interest rate APR of 9.5%:

This basic formula will calculating the monthly finance charge using the average daily balance method:

Finance Charge = Average Daily Balance * (APR/365) * Number of Days in the Billing Cycle

In Excel, we could enter this formula in cell E16:

=E12*(E14/365)*E15

The computed finance charge is $18.70. 

It’s important to note that while the ADB method is popular, not all companies use it. So, your bank or financial institution may compute your finance charge using a different method. Still, this method could provide an estimate.

Sample Template

Now, I’ve created a sample template you can use to estimate the finance charge for financial institutions that use a similar finance charge method. Not all do. But, the template uses some pretty cool functions to automate all of the math. You just enter transactions into the table, and provide the month and interest rate.

Here’s what it looks like:

Note: not all versions of Excel support the functions used.

Download

Disclaimer

This Excel workbook template is provided “as is” and the user assumes all responsibility for any results or outcomes that may arise from the use of this template. The creator of this template does not guarantee the accuracy of the data or results, and shall not be held liable for any damages or losses related to the use of this template. The user is solely responsible for ensuring that the template is suitable for their purposes.

Do you have any other tips about calculating your average daily balance? Let us know in the comments!