Calculate interest amount based on average daily balance

[Dani2009]

I would like to know whether there is a way I can calculate the interest
amount based on average daily balance and how I can calculate the average
daily balance for every quarter.
For example:
The beginning balance is $421,315.17 as of June 30, 2009. There are the
following transactions:
7/10/09 -13,000.00
9/1/09 -24,432.83
9/21/09 10,000.00
If I do the calculation in excel it will calculate the average daily balance
from 7/1/09 to 9/30/09 and gives me a daily average balance of $402,707.21.
(however, if I do it in Excel, I need to fill out 91 columns (one per day of
the month) and copy the previous balances including the transactions.
Is there any way I can do this in Access without having to add a record for
every day of the month?

 

[Allen Browne]

It's probably no easier to do this in Access than it is in Excel.

Last time I handled one of these, I ended up calculating the interest on a
day-by-day basis (walking a recordset rather than populating a table, but
you could do it either way.) That's because, in the end, there were too many
variables to try to do it any other way. Loans can start and end at any
time, and interest rates can change at any time. The loan payments may not
coincide with the loan's dates (e.g. they could be by calendar months), and
there can be penalties applied any any time (increasing the amount owing
from that date onwards.)

In short, calculating day-by-day is probably your best option, and certainly
the most flexible for handling situations you may not have
considered/encountered yet.