Break interest into quarters

[Ron]

A user wants me to set up a MDB that will break down
interest intervals on payments on deadbeat accounts. It
is confusing and I'm stuck.

The interest on the accounts can change on the first day
of the quarter, and depending on how long of a time frame
exists between the payments, the interest on one payment
can change multiple times.

So if Jane Smith sends in payment 1 on 06/20/2003 and
payment 2 on 07/20/2003, I need it to break payment 2
down into the number of days that it spans in each
quarter. In this case 10 days in Q2 and 20 days in Q3
and then multiply the days by the interest rate of that
quarter.

The real annoyance comes in when you have people spanning
more than 2 quarters. For instance, if John Doe sends in
payment 1 on 09/20/2002 and doesn't send payment 2 until
07/20/2003, I need it to break it down into the number of
days in each quarter (in this case 5 quarters). Those
days are then multiplied by the interest rate for each
quarter.

The user inputs the "interest rate" each quarter and
inputs the payments as they come in (with a "date
received" field). Both interest rates and payments are
logged into a table (not overwritten).

So if the payment spans two quarters, it has two separate
interest amounts calculated and deducted from the payment
amount (before it is applied to principal due).

Wheew! This one has been hanging me up for several days,
any suggestions?

Thanks..
RT

 

[Allen Browne]

Ron, I did one of these once, and ended up calculating the interest for each
day separately rather than using a compound interest formula.

I found there were so many variables. Things that could change any day:
- the interest rate (institutions don't always stick to quarters);
- the client's balance (paying early/late);
- penalty rates applied to overdrawing or late payment;
- even the loan type (switching from one type to another with different
rates);
- institution or government fees and charges.