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

[RepetitiveLady]

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.

 

[Ruddles]

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!

 

[jasonb75]

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.

 

[MickG]

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

 

[ScottR]

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?

 

[Richard Schollar]

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.

 

[Ruddles]

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.

 

[Richard Schollar]

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.

 

[RepetitiveLady]

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.

 

[jasonb75]

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.