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.

 

[RepetitiveLady]

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.

I'm so sorry about that error. I have double confirmed with my credit card provider again now.

I do have 25 days, starting from the date that my current month statement comes out. But I was charged Interest from the day I spend because I did fulfill the minimum monthly repayment for the previous months.

For now, to make things simple, lets assume that I have never done any Balance Transfer or Cash Advance previously. And all Balance Transfer and Cash Advance amount will be paid when the current month statement comes out.
And lets assume that I did not meet the Minimum Monthly Repayment. So I'll be charged interest the day I make a purchase.

The payment allocations are stated below: (No.1 will be cleared first followed by no.2 and so on...)
1.) Fees & Charges / Interest
2.) Balance Transfer
3.) Cash Advance
4.) Normal Credit Card Purchases
5.) Interest Free Promotions

If you need any other information, please do let me know.
Thanks.

 

[RepetitiveLady]

Please, can anyone help me... I'm not very good at managing my own financials. I need to get a clear calculations so tat I can figure out a way to pay out all my cards...

 

[jasonb75]

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.

Did you allow for this in the formula? I could be missing something that throws the figures out, but I think if you apply the formula to the sum of

Opening balance
Purchases
-Payments

It's not going to be exact but should be fairly close, which bearing in mind the number of assumptions you're working with is probably about as good as it will get.

 

[RepetitiveLady]

Did you allow for this in the formula? I could be missing something that throws the figures out, but I think if you apply the formula to the sum of

Opening balance
Purchases
-Payments

It's not going to be exact but should be fairly close, which bearing in mind the number of assumptions you're working with is probably about as good as it will get.

Thanks for the help. Really appreciate it.
I guess it's really difficult to create something to calculate all these at 1 go.
I'll stick to the original formula and see what i can do.

Thanks.