Help with interest calculations
Author Message
Help with interest calculations

I wonder if anyone can help me with a specific calculation and put me in
the right direction.

I have to make capital and interest calculations on Credit and Debit
account movements, calculating the interest on the number of days that have
occured since the last calculated balance, and the current date (or a
monthly/bimonthly report).  I know how to do this in excel but I don't know
how you make two dimensional calculations across registers in an Acess
Dynaset.  I am learning Visual Basic for Apps but am a long way off from
getting this one resolved without help.

If you can help, below follow more details on the Table Relations and key
fields.

Robin

Details:
I have a table called ProjectLoans and another one called Beneficiaries.
The two tables have a many to many relation given that a loan can have
several beneficiaries and vice versa.

We wish to control the balance on Capital and Interests, calculating the
difference in the number of days that have passed since the loan was made
and todays date, taking into considerations any other movements in the
account and the date of these transactions.

Furthermore, as in the case of the shared loan, the truth is that each
individual tends to pay on different days. What we really want is to track
movements for each "subloan", ie for each record in the linking table, and
that way we can then obtain balance totals for each beneficiary across
various loans they might have and also totals for each loan project, or
groups of loan projects that might be funded by different financial
agencies.  Hence I believe I should relate the Movements table that records
Date, Credit and Debit, to the linking table Loan Details. - Is this
correct?

Anyway, having got the records in a movements table how do I calculate the
Interest and balance.

Example:

Date            In              Out
1 March 1999    \$300            \$0
10 June 1999    \$0              \$50
15 Dec 1999     \$0              \$25
Today()         \$0              \$0

On this data, for each record I need to calculate the subtotal between
debit and credit, the accumulated interest based on X% interest rate and
the number of days passed since the previous record/365 days in the year,
and the total  balance outstanding (ie the outstanding capital +
accumulated interest.

Thanks alot if you got this far to read this.

Robin

Tue, 20 Aug 2002 03:00:00 GMT
Help with interest calculations

Quote:
>Subject: Help with interest calculations

>Date: 3/3/00 10:30 PM
[snip]
>Anyway, having got the records in a movements table how do I calculate the
>Interest and balance.

>Example:

>Date                In              Out     >1 March 1999        \$300            \$0
>10 June 1999        \$0              \$50
>15 Dec 1999 \$0              \$25
>Today()             \$0              \$0

>On this data, for each record I need to calculate the subtotal between
>debit and credit, the accumulated interest based on X% interest rate and
>the number of days passed since the previous record/365 days in the year,
>and the total  balance outstanding (ie the outstanding capital +
>accumulated interest.

>Thanks alot if you got this far to read this.

>Robin

Hi,
I don't have a full answer but perhaps the following will make it easier. When
calculating the interest, it need not be done for each non-overlapping
sub-period, e.g. Mar 1 to Jun 10, etc. You can compute the interest on \$300 for
Mar 1 to Today, on -\$50 for Jun 10 to Today, and so on and sum the results.
HTH,
Merjet

Wed, 21 Aug 2002 03:00:00 GMT

 Page 1 of 1 [ 2 post ]

Relevant Pages