Generate rows based on payment dates

[BudzyW]

Hi,

I’m using the generate-row tool to calculate payment dates starting from the first due date up to the maturity date, based on a specified frequency. The formula works as expected when payment dates don’t fall on the 31st of the month.

For example, I have a project with a quarterly payment frequency, where payments are expected on 31st March, 30th June, 30th September, and 31st December. However, the current condition in the generate-row tool calculates the payment dates correctly until the 30th September of the first year, but then identifies the next payment date as 30th December instead of 31st December. This seems to be due to the row generation condition that adds 3 months to the previous payment date, which doesn’t account for months with 31 days.

I would greatly appreciate any suggestions on how to resolve this issue. I’ve attached a sample workflow illustrating the problem for reference.

Thank you in advance!

[Cole]

Try this formula in the generate rows tool.  It will add 4, 7, or 13 months and then subtract one day when applicable. 

IF [Principal Repayment Frequency] = "Semi Annual" THEN
IIF(DateTimeDiff(DateTimeAdd(DateTimeTrim([Next Due Date Initial], "month"), 1, "month"), [Next Due Date Initial], "days") = 1,
DateTimeAdd(DateTimeTrim(DateTimeAdd([Next Due Date Initial], 7, "months"), "month"), -1, "days"),
DateTimeAdd([Next Due Date Initial], 6, "months")
)
ELSEIF [Principal Repayment Frequency] = "Quarterly" THEN
IIF(DateTimeDiff(DateTimeAdd(DateTimeTrim([Next Due Date Initial], "month"), 1, "month"), [Next Due Date Initial], "days") = 1,
DateTimeAdd(DateTimeTrim(DateTimeAdd([Next Due Date Initial], 4, "months"), "month"), -1, "days"),
DateTimeAdd([Next Due Date Initial], 3, "months")
)
ELSE
IIF(DateTimeDiff(DateTimeAdd(DateTimeTrim([Next Due Date Initial], "month"), 1, "month"), [Next Due Date Initial], "days") = 1,
DateTimeAdd(DateTimeTrim(DateTimeAdd([Next Due Date Initial], 13, "months"), "month"), -1, "days"),
DateTimeAdd([Next Due Date Initial], 12, "months")
)
ENDIF

[Yoshiro_Fujimori · Accepted solution]

Hi @BudzyW ,

I would add a branch when the due date falls on the month end like below.

I hope this helps.

Formula Tool
EndOfMonth =
DateTimeAdd(
  DateTimeAdd(
    DateTimeTrim([First Next Due Date],"month"),1,"month"),
  -1,"day"
)
[DueDateIsMonthEnd] =
  [First Next Due Date] = [EndOfMonth]
[Cycle] =
  IF [Principal Repayment Frequency]="Semi Annual" THEN 6
  ELSEIF [Principal Repayment Frequency]="Quarterly" THEN 3
  ELSE 12
  ENDIF

Generate Rows Tool / Loop Expression
IF [DueDateIsMonthEnd]
THEN DateTimeAdd(
DateTimeAdd(
DateTimeTrim(
DateTimeAdd([Next Due Date Initial], [Cycle], "months"),
"month"),1,"month"),
-1,"day"
)
ELSE DateTimeAdd([Next Due Date Initial], [Cycle], "months")
ENDIF

Workflow

Output

[binu_acs]

@BudzyW one way of doing this

 IF [Principal Repayment Frequency] = "Semi Annual" THEN
    DateTimeAdd(DateTimeAdd(DateTimeAdd([Next Due Date Initial], 1, "day"), 6, "months"),-1,'day')
ELSEIF [Principal Repayment Frequency] = "Quarterly" THEN
    DateTimeAdd(DateTimeAdd(DateTimeAdd([Next Due Date Initial], 1, "day"), 3, "months"),-1,'day')
ELSE
    DateTimeAdd(DateTimeAdd(DateTimeAdd([Next Due Date Initial], 1, "day"), 12, "months"),-1,'day')
ENDIF

[BudzyW]

Thanks for the solution @Cole. The solution worked for the original scenario. However I tried out a small deviation from the initial data set by changing the "Next due date initial" from 3/31/2024 to 3/30/2024. when I did that noticed a slight mismatch in the output as per the following.

[BudzyW]

Thanks for the solution @binu_acs . The solution worked for the original scenario. However I tried out a small deviation from the initial data set by changing the "Next due date initial" from 3/31/2024 to 3/30/2024. when I did that noticed a slight mismatch in the output as per the following.

[BudzyW]

Thank you for the solution @Yoshiro_Fujimori, it worked on all the normal scenarios. I noticed a minor mismatch during leap years when the payment date is February 28th. However, given the possibility of this occurring is very minimal I will be using this in my workflow.

Thank you again 😀