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
Thanks in advance.
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.
The linking table called LoanDetails.
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
Anyway, having got the records in a movements table how do I calculate the
Interest and balance.
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 +
Thanks alot if you got this far to read this.