Credit related formulas

[Tom Jones]

Hi,

I am hoping some of the "money math" people that lurk around here could
please point me to reference (textbook prefered) that would help me gain a
deeper understanding of computing interest paid over the life of a loan,
credit card interest, etc. I know that Excel has many built-in functions
for many of these issues, but I need to do more of the "work" myself.

The problem that I am currently stuck on is this:

I have a CC that compounds its interest monthly based upon the average daily
balance during that period. The due date for the bill is the 25th of each
month. I can perform the simple stuff like given the principal, APR, and
payment X, determine how long will it take me to pay off the debt (including
the total interest paid).

What if, instead of paying my bill on time, I pay the bill at the
*beginning* of the month, thus the average daily balance for each period
would be less. The end result is that the total interest paid has
decreased. Unfortunately, I haven't been able to find (or derive) any
formula/algorithm that accomplishes this; but the CC companies can obviously
do it. I am quite sure that every new CC company doesn't figure this stuff
out from scratch - there *has* to be existing formulas for these types of
problems.

Any references (the web, text books, etc.) would be greatly appreciated!!

-TJ

PS: You cannot reply to my email address - if you want talk to me outside of
this newsgroup please post to the group and I will contact you directly.

 

[Bernie Deitrick]

Tom,

The Excel function PMT takes a final parameter that is used to specify
whether the payment is made at the start or end of the period: from help -

Syntax
PMT(rate,nper,pv,fv,type)
For a more complete description of the arguments in PMT, see the PV
function.
Rate is the interest rate for the loan.
Nper is the total number of payments for the loan.
Pv is the present value, or the total amount that a series of future
payments is worth now; also known as the principal.
Fv is the future value, or a cash balance you want to attain after the
last payment is made. If fv is omitted, it is assumed to be 0 (zero), that
is, the future value of a loan is 0.
Type is the number 0 (zero) or 1 and indicates when payments are due.
Set type equal to If payments are due
0 or omitted At the end of the period
1 At the beginning of the period

You can get some of the formulas used by Excel from Help (or elsewhere),
though I'm not sure which ones, since I've not been that interested in
financial functions.

HTH,
Bernie
MS Excel MVP

 

[Norman Harker]

Hi Tom!

Probably the best reference here is John Walkenbach's Excel 2003
Formulas. The financial formulas chapters cover the basic concepts
involved in Time Value of Money calculations with Excel and more
especially the crucial concepts of equivalence of interest rates.
There's even a very basic credit card calculation set up.

Your APR is more correctly described as the Nominal Rate Compounded
Monthly. Using this "regime" the rate is divided by 12 and that result
(the monthly effective rate) is the interest charged each month.

However, interest on credit cards and many other loans is frequently
quoted at the APR12 but the *equivalent* daily effective rate is
applied to the daily outstanding balance.

We can calculate the equivalent daily rate as follows:

=(1+APR12)^(12/365)-1

To accumulate a previous balance at this rate use:

=(1+((1+APR12)^(12/365)-1))^(LaterDate-EarlierDate)

I've got a fairly basic accelerated payment credit card amortization
schedule set up if you'd like it. Just email to address below.

It covers minimum payment / accelerated payment options and allows for
daily effective compounding on previous balance date.

I think it covers you for what you want and I think that you can
insert your preferred (earlier) date for payment and ignore the
"payment by" date.