Results 1 to 10 of 10

Interest Caculation on Reducing Balance from 2 Tables

  1. #1
    iranbir is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    12

    Interest Caculation on Reducing Balance from 2 Tables


    Hi,

    I want to calculate interest in reducing balance. The related tables are as per below, the report format is attached. Kindly assist.


    Click image for larger version. 

Name:	Capture.JPG 
Views:	20 
Size:	20.0 KB 
ID:	32616
    Attached Files Attached Files

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,929
    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.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    12,083
    In addition, please show us an example of the calculation with some numbers.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    43,268
    You must be able to define the algorithm before it can be programmed. How would you manually (pencil and paper) calculate the interest?
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    iranbir is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    12
    hi,

    Find attached the Interest Working
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    43,268
    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.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    iranbir is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    12
    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-AmtPaid
    InttAmt: (Bal2*Days*Intt)/360
    And so on

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    43,268
    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 http://www.accessforums.net/showthread.php?t=69866&
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  9. #9
    iranbir is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Feb 2016
    Posts
    12
    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

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    43,268
    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.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Opening balance worked into running balance.
    By Perfac in forum Programming
    Replies: 11
    Last Post: 01-09-2018, 12:20 PM
  2. General Ledger Query With Opening Balance and Running Balance
    By muhammadirfanghori in forum Queries
    Replies: 3
    Last Post: 03-12-2015, 06:17 AM
  3. Replies: 8
    Last Post: 07-07-2014, 10:21 AM
  4. Caculation of total time
    By coliver in forum Access
    Replies: 1
    Last Post: 02-21-2013, 11:01 AM
  5. Reducing code
    By Mclaren in forum Programming
    Replies: 7
    Last Post: 03-16-2011, 01:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums