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,
