Calculating Average Daily Balance With Free Spreadsheet

Click here to view and download the entire No Credit Needed Notebook.

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

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.


Questions?  Find a bug in the spreadsheet?  Leave a comment and I’ll do my best to answer your questions or resolve any issues.

Click here to view and download the entire No Credit Needed Notebook.

Tweet about this on Twitter0Share on Facebook5Share on StumbleUpon7Pin on Pinterest4

Add to Flipboard Magazine.

Recent Articles

10 thoughts on “Calculating Average Daily Balance With Free Spreadsheet
  1. ahc99

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

     
  2. Rock Khosh

    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.

     
  3. MICHAEL HEDRICK

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

     
  4. MICHAEL HEDRICK

    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

     
  5. Diana

    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.

     
  6. MICHAEL HEDRICK

    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!

     
View My Stats