Help Needed: Incorrect Results in Maturity Profile Calculation

Question

Hi everyone,

I'm encountering an issue while calculating the "Maturity Profile" using the following formula:  (attached)

The issue arises for some specific cases:

1. When the difference in days is **90, 181, 212, 365, and 1095 days**, the results are incorrect.

2. For example, if the **Maturity Date** is March 31st and the **MIS Date** is December 31st, it should fall under "Less than 3 Months." However, it is being categorized incorrectly.

Could someone please help me identify what might be wrong in the formula or suggest an optimized way to handle such cases?

note that I changed the customer names with the correct maturity profile to compare the result

Thank you in advance!

list2s.JPG

list1.JPG

testx.yxmd

Yoshiro_Fujimori · Answer

@WassimAB ,

Thank you for the clarification.

Your boundary can be translated to IF clause as below:

[# of Days] = DateTimeDiff([Maturity Date],[MIS Date],"day")

[Maturity Profile] =

IF     IsNull([# of Days]) THEN "N/A"  // If you care about null data
  ELSEIF [# of Days] < 91    THEN "Less than 3 months"
  ELSEIF [# of Days] < 182   THEN "3 months to Less than 6 momths"
  ELSEIF [# of Days] < 366   THEN "6 months to Less than 1 year"
  ELSEIF [# of Days] < 1096  THEN "1 year to Less than 3 years"
  ELSE                            "Over 3 years"
  ENDIF

Workflow

Result

It seems the output matches with your expected result. I hope this helps.

MIS DateMaturity Date# of DaysMaturity Profile2024-12-31[Null][Null]N/A[Null]2025-01-01[Null]N/A2024-12-312025-02-2859Less than 3 months2024-12-312025-03-3190Less than 3 months2024-12-312025-04-01913 months to Less than 6 momths2024-12-312025-04-301203 months to Less than 6 momths2024-12-312025-05-011213 months to Less than 6 momths2024-12-312025-05-311513 months to Less than 6 momths2024-12-312025-06-301813 months to Less than 6 momths2024-12-312025-07-011826 months to Less than 1 year2024-12-312025-07-312126 months to Less than 1 year2024-12-312025-12-313656 months to Less than 1 year2024-12-312026-01-013661 year to Less than 3 years2024-12-312027-12-3110951 year to Less than 3 years2024-12-312028-01-011096Over 3 years

workflow.yxmd

WassimAB · Answer

hi @Yoshiro_Fujimori

thanks for your reply, yes i think this is the issue

below are the parameters

Cut of DateDecember 31, 2024  # of DaysMaturity Risk ProfilefromTo0Less than 3 months-31-Mar-25913 months to less than 6 months01-Apr-2530-Jun-251826 months to less than 1 year01-Jul-2531-Dec-253661 year to less than 3 years01-Jan-2631-Dec-271096Over 3 years01-Jan-28

Yoshiro_Fujimori · Answer

It seems the issue is the definition of boundary values.

* Less than 3 months
* 3 months to less than 6 months
* 6 months to less than 1 year
* 1 year to less than 3 years
* Over 3 years

The period from 2024-12-31 to 2025-03-31 is exactly 3 months.

So normally it should be "3 months to less than 6 months", but you think it is "incorrect".

So I am confused.

Can you define the "Maturity Profile"?