We need more information. What is the exact calculation you are looking for? Write it out in straight sentences using the fields you have above.
In addition, please show us an example of the calculation with some numbers.
You must be able to define the algorithm before it can be programmed. How would you manually (pencil and paper) calculate the interest?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
hi,
Find attached the Interest Working
Not going to try analyzing your workbook and convert to Access db.
You need to describe how you would calculate the interest with the db structure you posted.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
hi,
I was able to explain the calculations
DDDate Intt Adv Days CCY Amount
Calculations:
1 DDDate TTDate
(1st Payment Dt)Days: TTDate-DDDate Amount AmtPaid Bal: Amount-AmtPaid InttAmt: (Bal*Days*Intt)/360 2 TTDate
(1st Payment Dt)TTDate
(2nd Payment Dt)Days: TTDate-TTDate Bal
(After 1st Payment)AmtPaid
(2nd Payment)Bal2:
Balance after 1st Payment-AmtPaidInttAmt: (Bal2*Days*Intt)/360 And so on
Okay, possibly you are doing a running balance (running sum). This is a common topic.
Difficult to accomplish in query. Best done in report. Review https://www.accessforums.net/showthread.php?t=69866&
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Sample.ziphi,
I have tried the running sum, but couldn't get the effect of opening balance correctly and line wise days calculation to calculate the interest.
any suggestion
You want to calculate interest accrued or paid on investments/loans over a period? Probably over my head. Surely the OpeningBalance must include previous interest earned/paid?
I don't see a calculation on the report for opening balance. This would have to be derived with a DSum() or by joining an aggregate query to the report data source.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.