Calculating Payment Due Date After EOM

Here are two options to calculate payments due date after EOM (End Of Month).

Click the picture to enlarge

Reference Date - Date from which days count starts
X Days - Number of days to count after EOM

First Option (as in Excel row 5 in the above picture)
E5 = Due Date =DATE(YEAR(A5),(MONTH(A5)+QUOTIENT(C5,30)+1),DAY(IF(MOD(C5,30)=0,1,MOD(C5,30))))
A5 = Reference Date
C5 = EOM + X Days

All calendar months are counted as having 30 days.

The X Days is counted from the 1st day of the month follows the EOM of Reference Date.

If X Days is a round number of months to count, such as X=30 or 60 or 90 or 120 etc. then the Due Date is set to the 1st day of the month coming next after the last counted month.

For Example:
The input:
Reference Date is 15/02/2011 (15th/Feb/2011)
EOM + X Days is 30
  
The result will be: 1st/Apr/2011
EOM is end of February so month counting will start from the month of March
X Days = 30 means to count round number of 1 calendar month.
March will be counted as 1 calendar month although in March there are 31 days then the due date is set to the 1st of the next month which is 01/04/2011 (1st/Apr/2011).

If X Days is not a round number of months such as X=35 or 63 or 97 or 122 etc. then the Due Date is set to the day which is the number of days left after counting whole calendar months. In this example the days left are 5 or 3 or 7 or 2 though the Due Date in the month coming right after the last counted month, will be on the 5th or 3rd or 7th or 2nd day of that month accordingly.

For Example:
The input:
Reference Date is 15/02/2011 (15th/Feb/2011)
EOM + X Days is 35
  
The result will be: 5th/Apr/2011
EOM is end of February so the count will start from the month of March
X Days = 35 means to count 1 whole calendar month and after that additional 5 days.
March will be counted as 1 calendar month and after that the 5 days will be counted in April so the due date is set to 05/04/2011 (5th/Apr/2011).

Second Option (as in Excel row 7 in the above picture)
E7 = Due Date =DATE(YEAR(A7),(MONTH(A7)+QUOTIENT(C7,30)),DAY(1))+C7-1
A7 = Reference Date
C7 = EOM + X Days

The X Days is counted from the 1st day of the month coming after EOM (End of Month) of Reference Date.

In this option the days count doesn't have any interpretation of counting whole months but just counting days. 30 days wouldn't mean 1 whole month, it would mean 30 calendar days. So when counting 30 days from February 1st the due date will be March 2nd since February has 28 days only.

* * *