Interest Compounding Scenario
I've got a scenario driving me crazy. I'm building a
pension contribution system with a "Yearly" table,
containing the employee id, year, pre- and post-tax
contribution amount, interest from the previous year, and
summary fields of Yearly TOTAL (contributions + interest)
and Pension TOTAL. Interest is defined as the previous
YEARLY TOTAL * a given percentage, and is carried over to
the next year. Basically, each year's record is dependent
upon a value calculated from the previous year's record,
and the processing must be executed sequentially
This is easy to do in Excel (which is what the pension
folks currently use), but not easy in Access.
I've developed a series of multiple queries, divided by
individual year criteria, which are run in a macro from
the earliest year to the most recent year. For example,
1990 records are reviewed, the interest value is updated
from the 1989 record, the total and running total are
calculated. Then, the same evolutions are run for 1991
records, etc. The entire macro takes about 5 minutes to
update approx. 35,000 records.
Is there a way to work these for a single employee using
an array? I haven't needed to use arrays yet, but I think
it might be the way to go. Any ideas?
I thank you in advance wholeheartedly.