AnsweredAssumed Answered

Payment Schedules based on contract sent date

Question asked by tzaluski on Feb 12, 2010
Latest reply on Feb 12, 2010 by comment_1

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 

 

 

 

 

 

 

Outcomes