Calculation of Credit Card %

[Trainee]

Let's say a balance of 1500.00 at 12.99% how do we calculate the FINANCE
CHARGES on XL?
TX.

 

[Niek Otten]

What do you want to know?

 

[Trainee]

I guess this is the formula I'm looking for, probably just need to divide
it for 12(months) to get the monthly FINANCE CHARGES IN C1

 

[Trainee]

The monthly finance charges in C1

What do you want to know?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

 

[Fred Smith]

a1: Annual interest rate (12.99%)
b1: Balance
c1: =b1*a1/12

Regards,
Fred

 

[Stan Brown]

Sun, 4 Apr 2010 06:37:01 -0700 from Trainee

Let's say a balance of 1500.00 at 12.99% how do we calculate the FINANCE
CHARGES on XL?
TX.

It's really, really, really hard: multiply the two and divide by 12.

This isn't an Excel problem, it's a problem in business arithmetic.
You'd probably have learned more if you'd studied the examples in
your textbook.

 

[Joe User]

I guess this is the formula I'm looking for, probably just need to divide
it for 12(months) to get the monthly FINANCE CHARGES IN C1

But I hope you understand that that is only an estimate of the monthly
finance charges.


You wrote previously:

Let's say a balance of 1500.00 at 12.99% how do we calculate the FINANCE
CHARGES on XL?

Generally, for credit card accounts in the US, you cannot use the balance at
the end of the billing cycle as basis for the calculation -- although the
degree of the inaccuracy depends on how you use your credit card event.

The details are in the disclosure statement (and updates) that the credit
card company sent to you.

Generally, interest is calculated by multiplying the daily interest rate
(usually a variable annual rate as of the first day of the billing cycle
divided by 365) by the average daily balance (ADB) during the billing cycle,
then multiplied by the number of days in the billing cylce.

So if the balance of your account was $10,000 for 30 days, but zero on the
31st day, your finance charge would not be zero (0 * 12.99%/12). Instead,
it would be about $106.77 -- (30*10000/31) * (12.99%/365) * 31.

And of course, an account balance is normally not constant throughout the
billing cycle (less one day, in my example). Instead, the ADB is calculated
by AVERAGE(A1:A31), where A1:A31 is the balance at the end of each day of
the billing cycle.

Needless to say, that is too detailed for most of us to calculate.

Alternatively, assuming that your account grows daily (except for the
payment for the previous billing period, usually received in the middle of
the period), I would estimate the ADB by taking the ending balance (or the
largest daily balance in my example above) divided by the total days in the
billing cycle (i.e. the average amount added to the balance daily), multiply
by the sum of the days, then divide by the total days. For my example:
(10000/31)*31*(31+1)/2 / 31. Algebraically, this can be reduced to
10000*(31+1)/2/31.

Thus, the finance charge in my example would be estimated by the following,
making some algebraic simplifications: (10000*(31+1)/2) * (12.99%/365) --
about $56.94.

(BTW, that can be written more succinctly as 12.99%*10000*(31+1)/2/365.)

As you can see, that is significantly less than the original estimate of
$106.77, and less than 10000*12.99%/12 ($108.25).


----- original message -----

 

[Joe User]

BTW, that can be written more succinctly as 12.99%*10000*(31+1)/2/365.

I should have noted that 31 is an example, not a constant, just as 12.99%
and 10000 are examples. Instead of writing 31, you would calculate it by
B1-A1, where A1 and B1 are the starting and ending dates of the billing
cycle respectively.

In contrast, 365 is a constant, at least for my credit card accounts. But
some credit card companies might use 366 in leap years.


----- original message -----