This is a public Forum  publicRSS

Forum post

    jonnyt
    recurring payments calculationsAnswered
    Forum post posted December 21, 2009 by jonnyt , last edited February 9, 2012
    463 Views, 18 Comments
    Title:
    recurring payments calculations
    Post:

    Hi there,

     

     

    I am trying to set up a recurring payments system.

     

    I have set up the following fields:-

     

    StartDate

    frequencyOfPayment

    numberOfPayments

    nextInvoiceDate

    lastPaymentDate

     

    What calculations can I use to calculate nextInvoiceDate and lastPaymentDate??

     

    Some sort of date function I imagine.

     

    Could anyone also recommend how I can automaticallly add a recurring payment thats payable to an invoice table?? Perhaps using a script that runs every day on the server (we have filemaker server 10)

     

    Many thanks for your kind help.

    Best Answer

    comment

    The following calculation should return the date of the last payment =

    Case (
    frequency = "daily" ;
    StartDate + numberOfPayments ;

    frequency = "weekly" ;
    StartDate + numberOfPayments * 7 ;

    frequency = "monthly" ;
    Date ( Month ( StartDate ) + numberOfPayments ; Day ( StartDate ) ; Year ( StartDate ) )
    )



    To calculate the next payment, you need to calculate how many intervals have already elapsed, round the result up to the nearest integer, then plug this as the numberOfPayments into the same formula.

     

     

     

    It might be more efficient to script the creation of payment records in a related table at the time the invoice is issued.

    Answer

    • comment
      What are the options for the frequencyOfPayment field?
    • jonnyt
      weekly, daily and monthly
    • comment

      The following calculation should return the date of the last payment =

      Case (
      frequency = "daily" ;
      StartDate + numberOfPayments ;

      frequency = "weekly" ;
      StartDate + numberOfPayments * 7 ;

      frequency = "monthly" ;
      Date ( Month ( StartDate ) + numberOfPayments ; Day ( StartDate ) ; Year ( StartDate ) )
      )



      To calculate the next payment, you need to calculate how many intervals have already elapsed, round the result up to the nearest integer, then plug this as the numberOfPayments into the same formula.

       

       

       

      It might be more efficient to script the creation of payment records in a related table at the time the invoice is issued.

    • jonnyt

      could you give me a high level plain english overview of how you would script an invoicing function to be run manually whenever the user wants to run it.

       

       

      thanks again

    • jonnyt

      comment, the calculation for monthly doesnt seem to work when it goes into the next year:-

       

      frequency = "monthly" ;
      Date ( Month ( StartDate ) + numberOfPayments ; Day ( StartDate ) ; Year ( StartDate ) )
      )

       

       

      what can I do to fix that? 

    • comment

      jonnyt wrote:

      could you give me a high level plain english overview of how you would script an invoicing function to be run manually whenever the user wants to run it.


      1. If there are related Payments, delete them; 

       

      2. Put StartDate and numberOfPayments into variables.

       

      3. In the Payments table, loop: increase $startDate by an interval, decrease $numberOfPayments by 1, create a new record. Exit loop when $numberOfPayments is depleted.

       

       


      jonnyt wrote:

      the calculation for monthly doesnt seem to work when it goes into the next year


      Can you provide a specific example of "doesnt seem to work?

       

    • jonnyt

      frequency = "monthly" ;
      Date ( Month ( StartDate ) + numberOfPayments ; Day ( StartDate ) ; Year ( StartDate ) )
      )

       

       

      It might be as I am based in the UK, so I switched the calculation round to DD/MM/YYYY

       

       

    • jonnyt

      Its working now ive changed the date function back to month, day, year.

       

      Thanks for the advice. I seem tohave it all sorted now!

    • jonnyt

      If you would be so kind could you help with the calculation to work out the next payment date?

       

      Im then going to create a server script that runs daily and searches for any next payment datesthat match todays date, then the script inserts the payment into the payments table.

       

       

      I will then create a second script that creates a report showing all outstanding payments that havent yet been invoiced.

       

       

      Does the above sound about right?

    • comment

      jonnyt wrote:

       

       Im then going to create a server script that runs daily and searches for any next payment datesthat match todays date, then the script inserts the payment into the payments table.


      I am not sure what you are trying to achieve by that. I would create all the individual payment records at the time the invoice is issued.