Create Average Daily Balance Calculator in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn to create an average daily balance calculator in Excel. Credit card companies use the Average Daily Balance Method to charge interest to their customer during a billing cycle. Today, we will demonstrate 2 methods. Using these methods, you will be able to create an average daily balance calculator in Excel easily. So, without further delay, let’s start the discussion.


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.

Read More: How to Make Balance Sheet Format in Excel for Individual


2 Easy Methods to Create Average Daily Balance Calculator in Excel

To explain the methods, we will use 2 different datasets in the following 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.

average daily balance calculator excel

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. Let’s follow the methods to learn more.


1. Use the SUM Function to Create an Average Daily Balance Calculator in Excel

In the first method, we will use the SUM function to create an average daily balance calculator in Excel. But before applying the SUM function, we need to evaluate the missing values in the dataset. Let’s follow the steps below to see how we can create an average daily balance calculator using the SUM function.

STEPS:

  • Firstly, we need to fill the Balance column.
  • To do so, we need to add the Purchase amount with the Remaining/Beginning Balance of the previous cell.
  • In the case of the Payment amount, we need to subtract it from the Remaining/Beginning Balance.
  • Here, we added $300 with $1300 first in Cell D6.
  • Then, added $200 with $1600 in Cell D7.
  • In Cell D8, we subtracted $400 from $1800.

Use SUM Function to Create Average Daily Balance Calculator in Excel

  • Secondly, we need to fill the No. of Days column.
  • You can calculate the number of days by subtracting the lower limit from the upper limit in the range of Days and then adding 1 with it. For example, in Cell E7, we performed 20-11+1 to get 10 as the number of days.

Use SUM Function to Create Average Daily Balance Calculator in Excel

  • Thirdly, select Cell F5 and type the formula below:
=D5*E5

Use SUM Function to Create Average Daily Balance Calculator in Excel

  • After that, press Enter and drag the Fill Handle down.

  • As a result, you will see the Total Balance for each span of days.

  • In the following step, you need to find the Total Number of Days.
  • For that purpose, select Cell E10 and type the formula below:
=SUM(E5:E8)
  • Press Enter.

Use SUM Function to Create Average Daily Balance Calculator in Excel

  • Also, type the formula below in Cell F10 to get the Total Balance:
=SUM(F5:F8)
  • Also, hit Enter to see the result.

Use SUM Function to Create Average Daily Balance Calculator in Excel

  • At this moment, select Cell E12 and type the formula below:
=F10/E10

Use SUM Function to Create Average Daily Balance Calculator in Excel

Here, we have 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, you need to type the formula below in Cell E16:
=(E12*E14*E15)/365

Use SUM Function to Create Average Daily Balance Calculator in Excel

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

  • Finally, hit the Enter key to get the Finance Charge for a billing cycle.

Read More: How to Prepare Charitable Trust Balance Sheet Format in Excel


2. Insert Excel AVERAGE Function to Make an Average Daily Balance Calculator

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.

Insert Excel AVERAGE Function to Make Average Daily Balance Calculator

Let’s follow the steps below to see how we can use the Average function to build an average daily balance calculator.

STEPS:

  • First of all, select Cell E5 and type the formula below:
=G5+C5-E5
  • Press Enter to see the result.

Insert Excel AVERAGE Function to Make Average Daily Balance Calculator

  • Secondly, type the formula below in Cell E6:
=E5+C6-D6
  • Hit Enter.

Insert Excel AVERAGE Function to Make Average Daily Balance Calculator

  • Thirdly, drag the Fill Handle down to Cell E18.

Insert Excel AVERAGE Function to Make Average Daily Balance Calculator

  • As a result, you will see the Balance for each day.
  • You can see a Payment on the 6th day.
  • After the payment, the Balance is reduced by $200.

  • In the following step, select Cell E20 and type the formula below:
=AVERAGE(E5:E18)

Insert Excel AVERAGE Function to Make Average Daily Balance Calculator

  • Also, press Enter to see the average daily balance.

Insert Excel AVERAGE Function to Make Average Daily Balance Calculator

  • Finally, to find the Finance Charge for a billing cycle, you need to type the formula below in Cell G15:
=(G11*G13*E20)/365
  • Press Enter to see the result.

Read More: How to Create Consolidated Balance Sheet Format in Excel


Download Practice Workbook

You can download the workbook from here.


Conclusion

In this article, we have demonstrated 2 easy methods to create an Average Daily Balance Calculator in Excel. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles


<< Go Back to Balance Sheet | Finance Template | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo