Resources

Calculating Average Daily Balance With Free Spreadsheet

August 29, 2008

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

Click here to download Microsoft Excel version.

Click here to download Open Office version.

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.

10 thoughts on “Calculating Average Daily Balance With Free Spreadsheet”

  1. Thanks for providing this spreadsheet. It’s also useful for figuring out how much interest you should be making from the bank.

  2. Great piece of resource.I have just downloaded the SS and can be quiet useful in one’s daily financial life.

    Great site as well.

  3. Thanx for the spreadsheet. Just the thing I was looking around for long time. Thanx again 🙂

  5. My begining balance on my Chase card is $4,602.23 on12/29/08 and I made a payment of $92.00 on 01/16/09 and the statement closing date is 01/28/09 with a finance charge of $15.49 calculated on the closing day. The bank showing me an average daily balance of $4,571.33 which I can not figure out how they arrived at it. I would appreciate your help.

  6. Please tell me how to modify the program to calculate compounded interest charges. As the program currently stands, I have to manually enter day one balance and multiply it by 0.019836 DPR same as 7.24%APR for my card, and when finished, the screen looks something like 0.8374837223 in the purchase column and $4,224 [as an example] in the day two column. When you click on the payment column showing 0.83, then it shows the full number 0.8374837223. Therefore, I have to open my calculator program and manually calculate day 2 through day 30 of interest. When I have entered in the DPR for each day in that day’s purchase column, then the program calculates everything else perfectly. Note, my card comp. does not add DPR to DAY 1.
    Please respond. Thanks Michael H.
    P.S. After H.R. 627 goes into law next year, credit card companies will not be allowed to charge interest on fees and interest. Also, sometimes credit cards will have different APRs on different purchases. [Note for further research.]

  7. I FORGOT TO SAY THAT 7.24% IS A PERCENTAGE AND THEREFORE I ENTER 0.019836 DPR AS 0.00019836 TO BRING IT BACK TO A DECIMAL CALCULATION. I WOULD ASK THAT WHOEVER MADE THE PROGRAM PLEASE E-MAIL ME.

    THANKS

  9. Michael, I am confused by a statement that you made in your P.S. You state that”P.S. After H.R. 627 goes into law next year, credit card companies will not be allowed to charge interest on fees and interest.”

    Where did you get this information? I have searched and cannot find that we will not be charged interest on interest. I do hope that you are correct!!!!

    I found this link to the new “cardholders bill of rights” and the no interest on interest is not there.http://www.billshrink.com/credit-cards/bill-of-rights/#bill1-desc.

    thanks for any info.

  10. HI Diana, The Bill S. 414 had said originally “No INTEREST CHARGES ON FEES,” but I guess I kinda’ overstated that when I said “credit card companies will not be allowed to charge interest on fees and interest.” Nonetheless, the bills S 414 & HR 627 were merged into HR 627 and went through several forms, but the form that passed is at thins link.
    http://www.govtrack.us/congress/bill.xpd?bill=h111-627
    And this link:
    http://www.govtrack.us/congress/billtext.xpd?bill=h111-627
    Enjoy.
    PS CREDIT CARD COMPANIES WON’T BE ABLE TO CHARGE YOU INTEREST BACK TO THE ORIGINAL DATE ON WHAT YOU HAVE ALREADY PAID OFF, ACCORDING TO SEC. 102. PG 11. SO I GUESS THEY CAN STILL COMPOUND THE INTEREST, BUT THIS BILL MAKES THINGS A LOT NICER!

Comments are closed.