How to Create an Average Daily Balance Calculator in Excel (2 Methods)

What Is Average Daily Balance?

The Average Daily Balance method is a way to find the interest or finance charge on a credit card. To calculate the average daily balance, we multiply the balance for each day during a billing period and then, calculate their average. The general formula for Average Daily Balance can be written as:

=[Day 1 Balance + Day 2 Balance + Day 3 Balance…]/Number of Days in that Billing Period

After calculating the Average Daily Balance, we need to find the Finance Charge for a billing cycle. The formula of Finance Charge is:

=(Average Daily Balance X APR X Days in Billing Cycle)/365

Here, APR is the Annual Percentage Rate.

Dataset Overview

To explain the methods, we will use 2 different datasets in the two methods. In the first method, we will use a dataset that contains Days, Transactions, Balance, Number of Days, and Total columns. Here, we have Transactions for a range of days. Using that, we need to find the Balance, No. of Days, and Total value first. Then, we will calculate the Average Daily Balance.

In the second method, we will use a dataset that contains Balance for each day from Day 1 to Day 14. It also contains the Purchase and Payment columns.

Method 1 – Using the SUM Function

We need to fill the Balance column by adding the Purchase amount with the Remaining/Beginning Balance of the previous cell.
We need to subtract the Payment amount from the Remaining/Beginning Balance.
- Add $300 to $1300 in Cell D6.
- Add $200 to $1600 in Cell D7.
- Subtract $400 from $1800 in Cell D8.

We need to fill the No. of Days column.
We calculate the number of days by subtracting the lower limit from the upper limit in the range of Days and then adding 1 to it.

For example, in Cell E7, we performed 20-11+1 to get 10 as the number of days.

Select Cell F5 and insert the formula below:

=D5*E5

Press Enter and drag the Fill Handle down.

The Total Balance for each span of days will be calculated.

To find the Total Number of Days, select Cell E10 and insert the formula below:

=SUM(E5:E8)

Press Enter.

Insert the formula below in Cell F10 to get the Total Balance:

=SUM(F5:F8)

Press Enter to see the result.

Select Cell E12 and insert the formula below:

=F10/E10

We divided the Total Balance by the Total Number of Days in a billing cycle to get the Average Daily Balance.

Press Enter to see the Average Daily Balance for a billing cycle of 30 days.

To find the Finance Charge for a billing cycle, insert the formula below in Cell E16:

=(E12*E14*E15)/365

E12 is the Average Daily Balance, E14 is the Annual Percentage Rate (APR) and E15 is the Days in Billing Cycle.

Press Enter key to get the Finance Charge for a billing cycle.

Method 2 – Using the AVERAGE Function

We can use the AVERAGE function directly to make an average daily balance calculator. The Average function finds the arithmetic average of a range of numbers. To use the Average function, we need to have a dataset that contains the Balance for each day in a billing cycle. In the dataset below, you can see we have a record of Purchase, Payment, and Balance for a billing cycle of 14 days.