Credit Card Interest Rates

[One-Leg]

Hello,

Is there a function in Excel that will allow me to enter my current credit
card balance, the interest rate I'm being charged and the amount I wanna pay
every month and have Excel give me the rundown of how many months I must pay
that exact amount before my balance gets at 0.00$???

Or be able to calculate the exact amount I'm paying on interest day by
day??? For example:

Date Desc. Amount Bal.
2007/01/01 Balance 1000.00$ 1000.00$
2007/01/01 Payment 100.00$ 900.00$
2007/01/02 Interest 0.65$ 900.65$
2007/01/03 Interest 0.65$ 901.30$
2007/01/04 Interest 0.65$ 901.95$
2007/01/05 Interest 0.65$ 902.60$
2007/01/06 Interest 0.65$ 903.25$

And so on.................................

 

[joeu2004]

Is there a function in Excel that will allow me to enter my current credit
card balance, the interest rate I'm being charged and the amount I wanna pay
every month and have Excel give me the rundown of how many months I must pay
that exact amount before my balance gets at 0.00$???
Or be able to calculate the exact amount I'm paying on interest day by
day?? For example:
Date Desc. Amount Bal.
2007/01/01 Balance 1000.00$ 1000.00$
2007/01/01 Payment 100.00$ 900.00$
2007/01/02 Interest 0.65$ 900.65$
2007/01/03 Interest 0.65$ 901.30$
2007/01/04 Interest 0.65$ 901.95$
2007/01/05 Interest 0.65$ 902.60$
2007/01/06 Interest 0.65$ 903.25$

To compute "the exact amount" of interest on a daily basis, the
following should do the trick:

=b*(1+i/365)

where "b" is the previous balance (e.g. 900), and "i" is the nominal
annual interest rate (which might not be the same as the APR). Note:
in the US, lenders can use 366 instead of 365 in leap years.

As for the first part of your question, if all credit card debt worked
the same way (it doesn't!), NPER() might be the function you are
looking for. But beware of the assumptions built into NPER(), namely
that interest compounds at the same frequency as the payment (e.g.
monthly).

Moreover, beware that in real life, interest and balance may or may
not be rounded internally, even though they are invariably rounded on
periodic statements. Excel financial functions presume that nothing
is rounded.

Finally, daily interest may or may not be compounded on a daily basis
-- although for loans, I would expect that it is, if interest is
charged on a daily basis.

Assuming that the daily interest is compounded, the following might
provide the answer you want:

=roundup(nper(fv(i/365,d2-d1,0,-1)-1, p, -b), 0)

where "i" is the nominal annual rate, "d2" and "d1" are two payment
dates (not column references; sorry for the ambiguity), "p" is the
payment (round to the smallest legal tender of the realm, at least),
and "b" is the outstanding balance after the most recent payment (e.g.
900).

The expression "fv(i/365,...,-1)-1" computes the monthly rate when
interest is compounded daily. Alternatively, it can be written "(1+i/
365)^(d2-d1) - 1".

The last payment might be smaller than "p". It is difficult to
compute when interest is compounded at a different frequency than
payments (e.g. daily v. monthly). The following is one approximation
daily compound interest and monthly payments. But it can produce
substantial error over long periods of time and when the interest rate
is very high, because 7 months of the year have more than 365/12 days.

=fv(fv(i/365,365/12,0,-1)-1, n-1, p, -b))*(1+i/365)

where "n" is the result of ROUNDUP(NPER(....),0) formula above. This
last formula should be rounded up to the smallest legal tender of the
realm.

HTH.