Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Credit Cards... how do i calculate their montly interest charged day by day???

This is a discussion on Credit Cards... how do i calculate their montly interest charged day by day??? within the Excel Questions forums, part of the Question Forums category; Hi there everyone, I'm having difficulties calculating the interest charged on my credit card. I have a few cards, so ...

  1. #1
    New Member
    Join Date
    Jul 2009
    Posts
    21

    Question Credit Cards... how do i calculate their montly interest charged day by day???

    Hi there everyone,

    I'm having difficulties calculating the interest charged on my credit card. I have a few cards, so it's really troublesome when it comes to calculating the interest rates that will be charged on the next month. (I have very bad financial management) All the Excel gurus out there, please help me...

    I have called up one of the credit card company and asked about how interest are charged. They gave me this formula.

    Amount owned on card x (16.99% / 365 days) = Daily Interest

    So if i need to calculate the total interest that will be charged at the end of the month (30 days later), i need to calculate this 1 by 1.

    Example:
    1.) 2000.00 * (16.99%/365) = 0.93
    2.) 2000.93 * (16.99%/365) = 0.93
    3.) 2001.86 * (16.99%/365) = 0.93
    4.) 2002.79 * (16.99%/365) = 0.93
    5.) 2003.72 * (16.99%/365) = 0.93
    6.) 2004.65 * (16.99%/365) = 0.93
    7.) 2005.58 * (16.99%/365) = 0.93
    8.) 2006.51 * (16.99%/365) = 0.93
    9.) 2007.44 * (16.99%/365) = 0.93
    10.) 2008.37 * (16.99%/365) = 0.93
    11.) 2009.30 * (16.99%/365) = 0.94
    12.) 2010.24 * (16.99%/365) = 0.94
    13.) 2011.18 * (16.99%/365) = 0.94
    14.) 2012.12 * (16.99%/365) = 0.94
    15.) 2013.06 * (16.99%/365) = 0.94
    16.) 2014.00 * (16.99%/365) = 0.94
    17.) 2014.94 * (16.99%/365) = 0.94
    18.) 2015.88 * (16.99%/365) = 0.94
    19.) 2016.82 * (16.99%/365) = 0.94
    20.) 2017.76 * (16.99%/365) = 0.94
    21.) 2018.70 * (16.99%/365) = 0.94
    22.) 2019.64 * (16.99%/365) = 0.94
    23.) 2020.58 * (16.99%/365) = 0.94
    24.) 2021.52 * (16.99%/365) = 0.94
    25.) 2022.46 * (16.99%/365) = 0.94
    26.) 2023.40 * (16.99%/365) = 0.94
    27.) 2024.34 * (16.99%/365) = 0.94
    28.) 2025.28 * (16.99%/365) = 0.94
    29.) 2026.22 * (16.99%/365) = 0.94
    30.) 2027.16 * (16.99%/365) = 0.94

    Total Interest charged = $28.10

    I was wondering if there is an easier way. Like having a formula in Excel and i only need to key in the total amount owned, the interest rates and the number of days in a month. And the total interest that will be charged at the end of the month will be automatically calculated and shown.

    Any help would be appreciated.
    Thanks.
    Last edited by RepetitiveLady; Sep 14th, 2010 at 08:06 AM. Reason: add info

  2. #2
    Board Regular Ruddles's Avatar
    Join Date
    Aug 2010
    Location
    Glevum Castra, Britannia
    Posts
    5,473

    Default Re: Credit Cards... how do i calculate their montly interest charged day by day???

    See http://support.microsoft.com/kb/141695

    In your case, if your amount is in A1:-
    =A1*(1+(0.1699/365))^1 for Day 1
    =A1*(1+(0.1699/365))^2 for Day 2
    :
    =A1*(1+(0.1699/365))^30 for Day 30, etc.
    We're a penny out somewhere!
    R.
    ---
    On two occasions I have been asked, 'Pray, Mr Babbage, if you put into the machine wrong figures, will the right answers come out?'
    I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

  3. #3
    Board Regular
    Join Date
    Dec 2008
    Posts
    5,679

    Default Re: Credit Cards... how do i calculate their montly interest charged day by day???

    Quote Originally Posted by Ruddles View Post
    See http://support.microsoft.com/kb/141695

    In your case, if your amount is in A1:-
    =A1*(1+(0.1699/365))^1 for Day 1
    =A1*(1+(0.1699/365))^2 for Day 2
    :
    =A1*(1+(0.1699/365))^30 for Day 30, etc.
    We're a penny out somewhere!
    I think your version is correct, the interest should be rounded at period end, not daily which is where the difference comes from

    =ROUND(A1*(1+(0.1699/365))^30,2)

    Should sort it out.

  4. #4
    Board Regular
    Join Date
    Jan 2008
    Posts
    8,295

    Default Re: Credit Cards... how do i calculate their montly interest charged day by day???

    Hi, My initial thoughts are your calculation looks as if you are compounding your capital, daily (Interest on Interest) by adding the interest each day, when the 16.99% is an annual figure,and when divided by 365 , is the daily fig.
    You should , perhaps add each days interest and only compound the capital at the end of each year.
    Presumably you will need to do calculation at each date that there is a transaction based on the loan at that point.
    If the first date was 1/1/10 and Captal was 1000, and the next trasaction was 5/1/10 then the sum would be :-
    1000 X 16.99%/365 X(5/1/10-1/1/10) and copy the formula on down the sheet.(Use cell references for Capital and Dates)
    The next transaction down would, again be based only on the Loan not including the Interest.
    I would work out your calculation and compare it to your statement for Clarity.

    Regards Mick

  5. #5
    Board Regular
    Join Date
    Apr 2008
    Posts
    404

    Default Re: Credit Cards... how do i calculate their montly interest charged day by day???

    The formula to extract the interest is very similar

    =A1*(1+(0.1699/365))^30-1 = interest for a 30 day period

    Though are you sure it works this way, don't most credit cards charge daily but compound monthly?

  6. #6
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: Credit Cards... how do i calculate their montly interest charged day by day???

    You need to know on what basis the 16.99% is being quoted ie is it an APR (annual percentage rate) a nominal rate or something else. If you ask the credit card provider (or look on their website) they should be able to tell you. This then will determine how you apply it to calculating the interest.
    Richard Schollar

    Using xl2013

  7. #7
    Board Regular Ruddles's Avatar
    Join Date
    Aug 2010
    Location
    Glevum Castra, Britannia
    Posts
    5,473

    Default Re: Credit Cards... how do i calculate their montly interest charged day by day???

    Quote Originally Posted by Richard Schollar View Post
    You need to know on what basis the 16.99% is being quoted ie is it an APR (annual percentage rate) a nominal rate or something else. If you ask the credit card provider (or look on their website) they should be able to tell you. This then will determine how you apply it to calculating the interest.
    To be fair, she did say in her OP that she'd done that and that they'd provided the formula.
    R.
    ---
    On two occasions I have been asked, 'Pray, Mr Babbage, if you put into the machine wrong figures, will the right answers come out?'
    I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

  8. #8
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: Credit Cards... how do i calculate their montly interest charged day by day???

    Quote Originally Posted by Ruddles View Post
    To be fair, she did say in her OP that she'd done that and that they'd provided the formula.
    I doubt her implementation is correct though. I suspect it is a nominal rate compounded monthly, but again, this needs to be confirmed with the provider.
    Richard Schollar

    Using xl2013

  9. #9
    New Member
    Join Date
    Jul 2009
    Posts
    21

    Question Re: Credit Cards... how do i calculate their montly interest charged day by day???

    Quote Originally Posted by jasonb75 View Post
    I think your version is correct, the interest should be rounded at period end, not daily which is where the difference comes from

    =ROUND(A1*(1+(0.1699/365))^30,2)

    Should sort it out.

    Thanks everyone for the help.
    I did some calculations using my statements, and called up my card service provider to double check.

    Thanks Ruddles and jasonb75. I have found that this is the best formula, to an extend that there aren't any other transactions made between 2 statement dates. If I purchase something between a statement date, this formula would not work.

    I've also found out that balance transfer and cash advance have different interest rates. Rates are stated below:

    Balance Transfer Rate: 5.99% per annum
    Cash Advance Rate: 20.75% per annum


    Can I use a macro to calculate this?
    I need to have a place to key in:
    Start of Statement
    End of Statement (where this will calculate the number of days of a statement)
    Date of other Transactions made and
    The Amount of the Transaction
    Date of Balance Transfer made and
    The Amount of the Balance Transfer
    Date of Cash Advance made and
    The Amount of the Cash Advance

    Please take note that
    If I purchase something on let's say 15/09/2010, the interest charged will be on the total amount including this amount that I use on 15/09/2010.
    If I make a Balance Transfer to my credit card on 16/09/2010, 16.99%p.a. interest will be charged on the total amount and 5.99%p.a. will be charged on the Balance Transfer amount.
    If I make a Cash Advance on 17/09/2010, 16.99%p.a. interest will be charged on my total amount and 20.75%p.a. will be charged on the Cash Advance amount.

    For now, lets presume that I've never made any Cash Advance and Balance Transfer before this.

    If someone can come out with a way for me to key in amounts and automatically generate calculations on this, I'll be really happy. Any help will be appreciated.
    Last edited by RepetitiveLady; Sep 15th, 2010 at 06:19 AM.

  10. #10
    Board Regular
    Join Date
    Dec 2008
    Posts
    5,679

    Default Re: Credit Cards... how do i calculate their montly interest charged day by day???

    If I purchase something on let's say 15/09/2010, the interest charged will be on the total amount including this amount that I use on 15/09/2010.
    That doesn't sound right, maybe the way interest is calculated varies in different countries, I closed my cc accounts some time ago so I'm only going from memory (on a UK card).

    I seem to recall that interest is applied only after the date on which payment is due, so for example, based on a purchase on 15/9/10, if your statements are printed on the 16th of each month, interest would be applied typically around 28 days after (14/10/10), if your statement was printed on the 14th, the purchase wouldn't appear in print until november, meaning no interest applied until december. (different criteria could apply to cash advances or for different card providers).

    Hope that makes sense.

    In addition to this there are many other things you would need to take into consideration to get an accurate forecast.

    You would need to have all transaction history in the sheet from a zero starting balance, if you start with a balance of 2000, how do you know which rate of interest to apply to how much of it?

    When you make a payment how is it allocated? Typically if you had a 2000 balance consisting of

    1000 balance transfer
    500 purchases
    500 cash advance
    + 28 interest added to the end

    if you made a payment of 100, it wouldn't be allocated in proportion to keep thye interest easy to calculate, you would first be paying the 28 interest, the remaining 72 would be deducted from the outstanding balance transfer amount (the part that attracts the lowest interest so makes least profit for the card provider).

    This leaves the purchases and cash advances stuck at the original balance, attracting the higher amount of interest until the balance transfer has been cleared, cash advances, attracting the most interest will be the last to be cleared.

    I'm sure that with al of the correct information this can be done, but I doubt it will be a simple task.

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com