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.

Thanks in advance.

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.  
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
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  
 
 [ 2 post ] 

 Relevant Pages 

1. Interesting Date/Time Calculation Query - Please Help!

2. Interesting Date/Time Calculation Query - Please Help!

3. Interest Calculation

4. Interest Calculation

5. Compound Interest Function -- need help

6. Need Help, Basic Program to score interest survey

7. help on Interest Rate

8. Interested in Helping?

9. Help with an interesting problem.

10. Very interesting question/Help Needed!

11. Creating Help Files - Also Interested

12. Please help : Very interesting problem - Matrices

 

 
Powered by phpBB® Forum Software