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.

**Table of Contents**hide

## Download Practice Workbook

You can download the workbook from here.

## 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**.

## 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**.

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 SUM Function to Create 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**.

- 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.

- Thirdly, select
**Cell F5**and type the formula below:

`=D5*E5`

- 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**.

- Also, type the formula below in
**Cell F10**to get the**Total Balance**:

`=SUM(F5:F8)`

- Also, hit
**Enter**to see the result.

- At this moment, select
**Cell E12**and type the formula below:

`=F10/E10`

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`

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 Calculate Average in Excel (Including All Criteria)**

### 2. Insert Excel AVERAGE Function to Make 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 **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.

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.

- Secondly, type the formula below in
**Cell E6:**

`=E5+C6-D6`

- Hit
**Enter**.

- Thirdly, drag the
**Fill Handle**down to**Cell E18**.

- 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**reduces by $**200**.

- In the following step, select
**Cell E20**and type the formula below:

`=AVERAGE(E5:E18)`

- Also, press
**Enter**to see the average daily balance.

- 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 Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)**

## 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. Also, you can visit **the ExcelDemy website** for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.

## Related Articles

**Calculate Daily Average from Hourly Data in Excel****How to Average Every Nth Row in Excel (3 Ways)****Calculate Average Growth Rate in Excel (3 Easy Methods)****How to Calculate Average of Averages in Excel (with Easy Steps)****Calculate Moving Average for Dynamic Range in Excel (3 Examples)****[Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)****Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)**