See http://support.microsoft.com/kb/141695
In your case, if your amount is in A1:-
=A1*(1+(0.1699/365))^1 for Day 1We're a penny out somewhere!
=A1*(1+(0.1699/365))^2 for Day 2
:
=A1*(1+(0.1699/365))^30 for Day 30, etc.
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 ...
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 09:06 AM. Reason: add info
See http://support.microsoft.com/kb/141695
In your case, if your amount is in A1:-
=A1*(1+(0.1699/365))^1 for Day 1We're a penny out somewhere!
=A1*(1+(0.1699/365))^2 for Day 2
:
=A1*(1+(0.1699/365))^30 for Day 30, etc.
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.
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
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?
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
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.
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 07:19 AM.
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).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.
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.
Like this thread? Share it with others