Credit card Calculation


G

Guest

Hi

I was wondering if anyone could help me with a minimum payment calculation
on my spreadsheet for my Credit card

I have the following Cells

Credit Limit (L2) £4300

Credit Card Amount Owed (L4) £3300

Minimum Amount (L6) 2%

And then I have a cell (L8) Minimum Payment and it is in this Cell I want it
to say what my minimum payment will be if you could help with this that would
be great

Regards Joel

Amount Owed (L6) £3000
 
Ad

Advertisements

G

Guest

Hi thans for your help

I left out a bit of information so here goes;

Hi

I was wondering if anyone could help me with a minimum payment calculation
on my spreadsheet for my Credit card

I have the following Cells

Credit Limit (L2) £3500

Credit Card Amount Owed (L4) £3200

Credit Card APR (L6) 17.9%

Minimum Amount (L8) 2%

And then I have a cell (L10) Minimum Payment and it is in this Cell I want it
to say what my minimum payment will be if you could help with this that would
be great

Regards Joel
 
D

David Biddulph

Well, your starting point is to think about which items of data are
relevant, and to think about how you would do the calculation by hand (or
with a calculator). Once you have decided what calculation you need Excel
to do for you, finding the way of doing it is normally the easy part.
 
S

Stephen

Try this:
=MIN(ROUND(L4*L8,2),L10)

Joel said:
Hi thans for your help

I left out a bit of information so here goes;

Hi

I was wondering if anyone could help me with a minimum payment calculation
on my spreadsheet for my Credit card

I have the following Cells

Credit Limit (L2) £3500

Credit Card Amount Owed (L4) £3200

Credit Card APR (L6) 17.9%

Minimum Amount (L8) 2%

And then I have a cell (L10) Minimum Payment and it is in this Cell I want
it
to say what my minimum payment will be if you could help with this that
would
be great

Regards Joel
 
Ad

Advertisements

D

David Biddulph

So if you don't know what the question is, you shouldn't be looking at Excel
for an answer.

Perhaps you could look at the terms and conditions of your credit card
account, or (as I said earlier), think about which of the supplied
parameters is likely to be relevant. Another course worth pursuing is to
look at your credit card bill and see what value they have calculated, and
see how that compares with what you might interpret from the defined
conditions and the input data.

Note also that you have supplied us with conflicting information, in that
there are two things which you've told us are in L6.
 
J

joeu2004

I was wondering if anyone could help me with a minimum payment calculation
on my spreadsheet for my Credit card

I have the following Cells
Credit Limit (L2) £3500
Credit Card Amount Owed (L4) £3200
Credit Card APR (L6) 17.9%
Minimum Amount (L8) 2%

And then I have a cell (L10) Minimum Payment and it is in this Cell I wantit
to say what my minimum payment will be

If we assume (probably incorrectly; see below) that Minimum Payment
(L10) is simply the Minimum Percentage (L8) of the Amount Owed (L4),
then:

=roundup(L8*L4, 2)

And that might be good enough for a classroom assignment. But in real
life, you need to consult your credit card agreement to see how the
minimum payment is determined. Here is what mine says:

If the Amount Owed is $10 or less, the Minimum Payment due is the
Amount Owed. Otherwise, the Minimum Payment due is the largest
of the following: $10; 2% of the Amount Owed; or the sum of 1% of
the Amount Owed, plus the total billed finance charges, plus the
total
billed late fees.

The might be implemented as follows:

=if(L4<=10, L4, roundup(max(10, L4*L10, L12*L4 + L14 + L16), 2) )

where I have added L12 (1%), L14 (finance charges), and L16 (late
fees). L14 and L16 come from your periodic statement.

You might approximate L14 by L4*L6/12, if you make monthly payments.

But the periodic finance charges might be computed in a far more
complex manner. In my case, the monthly finance charge is the sum of
the daily finance charges. The daily finance charge is the daily
balance times the daily rate, which is L6/365 in my case. Since the
daily finance charge is added to each daily balance, the daily finance
charge is compounded. Of the course, the daily balance depends on
your spending pattern. But the monthly finance charge might be
estimated FV(L6/365, 365/12, -L4*12/365, 0, 1) - L4.

Beware that that might underestimate the monthly finance charge if
credits tend to occur more toward the beginning of the month. The
most pessimistic estimate of monthly finance charges in my case might
be L4*(L6/365)^(365/12).

(Actually, the __most__ pessimistic estimate is L4*(L6/365)^31.)

Obviously, it is easier to simply take the finance charge from the
statement.
 
Ad

Advertisements

J

joeu2004

Errata... (Sigh, this has been a bad morning for my posting.)

The most pessimistic estimate of monthly finance charges
in my case might be L4*(L6/365)^(365/12).

(Actually, the __most__ pessimistic estimate is L4*(L6/365)^31.)

Those formulas should be:

L4 * (1 + L6/365)^(365/12) - L4

L4 * (1 + L6/365)^31 - L4
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top