3 Replies Latest reply on Jun 26, 2014 1:32 PM by philmodjunk

    Accrued interest calcualtions

    WilliamMatthews_1

      Title

      Accrued interest calcualtions

      Post


           I have a simple table which contains loan disbursements made to multiple clients.  The table records each loan disbursement made by client, date, amount, etc.

           I wish to calculate the interest charges for each customer accrued between two date fields in adjacent records: (1) the current date when a new loan is made, and (2) the date of the previous loan disbursement.  Something like this:

           Record 1:  Date ...  Client ... Disbursement Amount ... Running Loan Total

           Record 2:  Date ...  Client ... Disbursement Amount ... Running Loan Total ... Date (Record 2) - Date (Record 1)

           I know a db is not a spreadsheet, but trying to consolidate outstanding disbursements and interest accrued across multiple client spreadsheets is a clumsy and unreliable manual process.

           Thanks

            

           W. Matthews

        • 1. Re: Accrued interest calcualtions
          philmodjunk

               "adjacent" is the key detail to look at carefully. If you can be confident that the two records will always be "adjacent" in any found set or set of related records in a portal, GetNthRecord can be used to access data in the previous record:

               Let ( r = Get ( RecordNumber ) ; If ( r > 1 ; GetNthRecord ( Date ; r - 1 ) ; 0 ) )

               All of these functions can be looked up in FileMaker help if they are unfamiliar.

               For faster calculations and less chance of hitting a "recursion limit", I'd set up a number field with this expression as an auto-enter calculation to copy this data from the previous record at the time that it is created. Otherwise, you can see long delays waiting for large found sets to update and you can see records lower down int he found set fail to calculate--especially when using FM GO on an iOS device. This also avoids the need for having the records perfectly adjacent at all times as long as they are adjacent when a new record is added to the table. The key draw back to such an approach is that if you change the date field in record 1, this calculation will not automatically update in record 2.

               There are also ways to use a relationship or the ExecuteSQL() function (FileMaker 12 or newer)  to access the data from a previous record.

          • 2. Re: Accrued interest calcualtions
            WilliamMatthews_1

                 Phil:

                 Will this work on sorted records?  I may make 3 entries to record loan disbursements on the same day but for different loan customers.  Then the table is sorted by customer.  The meaningful calculation is the difference in day between disbursements for THAT CUSTOMER; unsorted, those disbursements may be table records 101 and 104.

                 Thanks,

                 Will Matthews

            • 3. Re: Accrued interest calcualtions
              philmodjunk

                   That's why "adjacent" is the key detail.

                   If the new record that you create is adjacent to the previous record, such as performing a find for all such records for a particular client, sorting and then creating the new record, then yes, this will work.

                   But if you want to create several new records without first pulling up the other records for that client, then you'd need to use another method such as a self join relationship that uses a specified sort order in the relationship to refer to the immediately previous disbursement record from the same client even if the record is not present in the found set.