Calculating Average Daily Balance With Free Spreadsheet
Many credit card companies use the Average Daily Balance Method when calculating how much interest they charge their customers during a particular billing cycle. I have created a free-to-download spreadsheet that will help you calculate –
- The Average Daily Balance
- The Impact of Purchases and Payments
- The Impact of Timing Your Payments
- The Amount of Interest Charged During a Particular Cycle
The spreadsheet is free for you to download and is available in two versions:
Be sure that you select ‘save as‘ or ‘save link as…‘ If you select ‘open with’ the file will open as ‘read only’.
Here are some screen shots from the spreadsheet and some notes on how it works –
Enter Days In Cycle (between 20 and 31) and Beginning Balance
Enter any Purchases or Payments (One purchase / One payment, Per day)
In the example below, the billing cycle is 31 days and the beginning balance is $2000. To keep things simple, I’ve entered just one transaction, a payment made on day 25.
The spreadsheet then uses the information provided to calculate Average Daily Balance and Cycle Interest Charge. It also details how you can calculate these outputs on your own.
Alongside the first section of data is another section, allowing you to compare two payment plans. In the example below, I simply made payment on day 5 instead of day 25.
Notice how making the payment earlier in the cycle changes both the Average Daily Balance and the Cycle Interest Charge.
The spreadsheet also calculates the difference between two payment plans. In this example, making the payment 20 days sooner will result in a savings of $.55.
I hope you will download the spreadsheet and work though various plans. I used a similar spreadsheet when calculating the impact of making multiple monthly payments when reducing my debt.
Click here to view and download other spreadsheets and tools in the No Credit Needed Notebook.