Help Needed: Incorrect Results in Maturity Profile Calculation

[WassimAB]

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!  

[binu_acs]

@WassimAB I believe your [Product Code] is MDAC for all the records and which is falling in the first condition , that's why you are getting the same result for all the records

[WassimAB]

even if i delete that ... that is not the solution

[Yoshiro_Fujimori]

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"?

[WassimAB]

hi @Yoshiro_Fujimori 

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

below are the parameters 

Cut of Date	December 31, 2024
# of Days	Maturity Risk Profile	from	To
0	Less than 3 months	-	31-Mar-25
91	3 months to less than 6 months	01-Apr-25	30-Jun-25
182	6 months to less than 1 year	01-Jul-25	31-Dec-25
366	1 year to less than 3 years	01-Jan-26	31-Dec-27
1096	Over 3 years	01-Jan-28

[Yoshiro_Fujimori]

@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 Date	Maturity Date	# of Days	Maturity Profile
2024-12-31	[Null]	[Null]	N/A
[Null]	2025-01-01	[Null]	N/A
2024-12-31	2025-02-28	59	Less than 3 months
2024-12-31	2025-03-31	90	Less than 3 months
2024-12-31	2025-04-01	91	3 months to Less than 6 momths
2024-12-31	2025-04-30	120	3 months to Less than 6 momths
2024-12-31	2025-05-01	121	3 months to Less than 6 momths
2024-12-31	2025-05-31	151	3 months to Less than 6 momths
2024-12-31	2025-06-30	181	3 months to Less than 6 momths
2024-12-31	2025-07-01	182	6 months to Less than 1 year
2024-12-31	2025-07-31	212	6 months to Less than 1 year
2024-12-31	2025-12-31	365	6 months to Less than 1 year
2024-12-31	2026-01-01	366	1 year to Less than 3 years
2024-12-31	2027-12-31	1095	1 year to Less than 3 years
2024-12-31	2028-01-01	1096	Over 3 years