1 2 Previous Next 18 Replies Latest reply on Dec 22, 2009 6:40 AM by jonnyt

    recurring payments calculations

    jonnyt

      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.

        • 1. Re: recurring payments calculations
          comment_1
             What are the options for the frequencyOfPayment field?
          • 2. Re: recurring payments calculations
            jonnyt
               weekly, daily and monthly
            • 3. Re: recurring payments calculations
              comment_1
                

              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.







              • 4. Re: recurring payments calculations
                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

                • 5. Re: recurring payments calculations
                  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? 

                  • 6. Re: recurring payments calculations
                    comment_1
                      

                    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?

                     



                    • 7. Re: recurring payments calculations
                      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

                       

                       

                      • 8. Re: recurring payments calculations
                        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!

                        • 9. Re: recurring payments calculations
                          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?

                          • 10. Re: recurring payments calculations
                            comment_1
                              

                            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.


                            • 11. Re: recurring payments calculations
                              jonnyt
                                

                              Its a car hire system so there a a huge amount of individual and recurring invoice items that would be hard to track manually.

                               

                              For example there:-

                               

                              hire charge

                              incident charge(these can be recurring payments spread over 20 days, 6 months, 12 months, 5 weeks etc)

                              fuel charges

                              loss of use charges

                              scratch charges

                              soiling charges

                               

                              The reason I want to run a script daily is so that the system users only have to run a script to show the invoices that need to be sent to customers that day. Otherwise it will be almost impossible for the users of the system to go back through previous records to issue new/recurring invoices.

                               

                               

                              • 12. Re: recurring payments calculations
                                comment_1
                                   I am not sure I follow: if all the payment records are created in advance, then all that's left on any day is to find them?
                                • 13. Re: recurring payments calculations
                                  jonnyt
                                    

                                  a vehicle can be hired for a long period of time i.e. 3 months.

                                   

                                  During this period:-

                                   

                                  an incident could occur

                                  the vehicle might get scratched but still be driveable

                                  and upon its return it might have no fuel in it and the inside might be dirty.

                                   

                                  Therefore invoices are created as and when they need to be.

                                   

                                  Also, a recurring charge or spread payment invoice might be created to spread the cost of fixing the fender/ bumper for example .

                                   

                                  So due to the above the invoices need to be created at anytime rather than in advance.

                                  • 14. Re: recurring payments calculations
                                    jonnyt
                                      


                                    "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."

                                     

                                     

                                    Whats the best way to implement the above?

                                    1 2 Previous Next