Generating rows discussion

[Antung213]

Dear all,

I am currently have an issue as follows:

1. In the data set, we have:

- Loan ID: ID of each loan, 1 loan ID can have multiple payment schedule which will be consecutive

- Payment schedule: payment date (if the "remaining term" =1) or the start date of a series of payment (if the "remaining term" >1)

- Payment amount: amount paid for each remaining term

- Payment frequency: the number of month that will add up when the "remaining term" > 1

- Frequency unit: "M" stand for "months"

- Maturity date: Date that the loan will be ended

2. I want to create a timeline which each payment date will be presented. For examples, if the remaining terms = 6, there will be 5 additional rows will be added. However, there are 3 possible outcome:

- The end date of the payment schedule is the exact date of maturity

- The end date of the payment schedule is after of the date of maturity

- The end date of payment schedule is before the date of maturity

For the first 2 outcome, I have used the "Generating rows" function with the express that ([Rowcount] <= Remaining terms] and it works (the first outcome, keep; the 2nd outcount, replace the last date with maturity date). But for the last outcome, I would like to add 1 more row below the schedule so that I can balance all remaining principal to that date. Please help me on this one.

3. The last thing is not relevant to the title, I have the workflow but still want to see some suggestion. The payment amount for each Loan ID, if we add up, can result in 3 cases:

- The total principal payment equals the outstanding balance > Nothing to process

- The total principal payment higher than the outstanding balance > Balancing the remaining amount to the maturity date

- The total principal payment lower than the outstanding balance > Balancing to the last payment before the total principal payment higher than the outstanding balance, any payment after that date will be zero.

Sorry if my explanation is quite confusing, really hope to have your support.

Thank you and regards,

[BrandonB]

Could you use filters to split each of the conditions into their own separate streams, use the generate rows tool if required for row generation, and then union the three streams back together? This would allow you to maintain separate logic and easily manage each condition. 

[RobertOdera]

Hi, @Antung213 

For Q2 have you considered initializing your Generate rows from zero (0) instead of the default of one?

Doing so might you resolve that final piece.

For Q3, please share your workflows of the sample file, and we will help you from there OR please share (for any one loan ID), what the final output should look like. Cheers!

[Antung213]

Thank you @BrandonB , simple method but I haven't thought of that lol. Anyway, the issue is that for the last outcome where the last date is before the date of maturity and I came up with the idea to add +1 to the "Remaining terms" if that condition is met.

Something like this:

[Antung213]

Hi @RobertOdera ,

For the Q2, I have come up with a solution in the previous message where I changed the "remaining terms" of last rows if the condition is met.

For the Q3, my solution is like this:

- Create a column for "Outstanding balance after each payment"

- In case that the total payment < outstanding balance  then the payment of the last date will equal the Row-1: Outstanding balance after each payment

- In case that the total payment > outstanding balance  then I will mark that date using Summarise tool and the after process is quite the same as the above case.

[RobertOdera]

@Antung213 

Sounds like you have it! Fantastic.