1 Reply Latest reply on Feb 12, 2010 1:35 PM by comment_1

    Payment Schedules based on contract sent date

    tzaluski

      Title

      Payment Schedules based on contract sent date

      Post

      First..sorry this is so long.., wanted to give you as much info as I thought you would need to totally understand what I am trying to

      accomplish. If this is considered 2 different topics.. ignore #1 and please just help me with the 11 pay calculation.

       

      Need help with:

      1) Is there an easier way to write the calculation for the 1 pay schedule shown below?

      2) help with setting up a payment schedule based on the contract sent date for 11 pay. 

       

      Contract wording:

      1 pay  Deposit of $..... (20% of net tuition) due April 1,2009. Balance of tuition and fees due by July 1, 2009 

      2 pay  Deposit of $....  (20% of net tuition) due April 1, 2009. Payments of 50% of remaining tuition and fees due July 1, 2009 and November 1, 2009. 

      11 pay Deposit of $.... (1/11th of net tuition) due April 1, 2009 followed by 10 equal installments of the balance of tuition and fees due the first of each month from may 1, 2009 through February 1, 2010. A $15 monthly processing fee will be added to each payment.

       

      Scenario:

      I have 3 different payment options (1, 2 and 11 pay), each one has a different result of how much is due and when, depending upon the date the contract is sent out. 

       

      Global fields:  Current Academic year (CAY):   2009-2010

                          Contractdue_date:                  4/1/2009 

      Oher fields:

      Family_contribution (tuition_due + fees)

      Tuition_due (net tuition)

      Contract_sent_date (date field)

      Deposit_due (20% of tuition due) 

       

       

      Admissions start date: 2/1/2009 

      Admissions End Date and last pay date for 11 pay: 2/1/2010

             1 and 2 pay tuition due date: 7/1/2009

             contract Cut off date 1:        8/15/2009

             contract Cut off date 2:       11/1/2009

       

       

       

      For 1 pay:   (calculation below)

      1) If the contract is sent prior to April 1, 2009 : the $(deposit_due) is due April 1,2009 and the (Family_contribution) is due by July 1,2009.

      2) If the contract is sent after 8/15/2009, the (Deposit_due) and (Family_contribution) is due 2 weeks after the (contract_sent_date)

       

       

      For 11 pay, I need to incorporate a payment schedule, the last payment would be 2/1/2010.

      1) if contract is sent out < 4/1/2009  "Deposit of $(deposit_due) is due (contract_due_date) followed by 10 equal installments of the (Family_contribution) plus a $15 monthly processing fee for each payment starting May 1, 2009 through 2/1/2010. (showing the payment schedule month and amount)   

      2) if contract is sent out > 4/1/2009 "Deposit of $(deposit_due) is due (contract_sent_date +14) followed by (the number of payments left from (contract_sent_date +14) to (admissions_end_date)) equal installments of (Family_balance/# = $15.00).

       

      This is the calculation for 1 pay,  Is there an easier way to write this?

       

      Case(Contract_date_sent_final   ≥ Contract_date_due_final;"Deposit of"  & " " &  Deposit_Due  & " " & "and Balance of Tuition and fees" & " " & (Int(Round(Family_contribution;2)) - Int(Deposit_Due)) & " "  & "due by" & " "  & (Contract_date_sent + 14); 

                "Deposit of" & " "  &  Deposit_Due & " "  & "(20% of net tuition) due by" & " "  &  Portal Preferences::ContractDueDate_All & " "  & "Balance of Tuition and fees" & " "   & (Family_contribution - Deposit_Due) & " "  & "are due by" & " "  & AdmissinsSeason_EndDate) 

       

       

      The result: If Contract was sent on 3/10/2009 and Tuition is $12185.00

         Deposit of $2,437.00 (20% of net tuition) due by 4/1/2009  Balance of Tuition and fees $9,748.00 are due by 07/01/09

                      If contract was sent on 8/24/2009 and tuition is $21,000.00

           Deposit of $4,100.00 and Balance of Tuition and fees $16,900.00 is due by 9/4/2009 

       

       

       

       

       

       

        • 1. Re: Payment Schedules based on contract sent date
          comment_1
            

          I think you should break up the problem into smaller pieces:

           

          a) determine the scenario;

          b) calculate the amounts due;

          c) calculate the dates due.

           

          It would probably be best to script the process and create a record in a related table for each payment. Although you could use repeating calculation fields for the dates and amounts, you will be limited in finding and reporting abilities.