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!

Generate Rows.yxmd

Yoshiro_Fujimori

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

Generate Rows_YF.yxmd