Hi All,
I'm trying to create an iterative macro to effectively generate all reset dates for a financial swap however, am stuck on the logic for the numerous possible scenarios.
Currently, I'm trying to account for forward looking (based off effective date), backwards looking (based off maturity date), and if it's setup as an End of Month reset.
A hypothetical example would be inputs of the below all in one row:
Effective Date: 07/25/2023
Maturity Date: 10/18/2024
ResetDirection: Forward (or Backward)
EndofMonth: Yes (or No/Null)
FirstofMonth: Yes (or No/Null)
Reset Frequency: Monthly (or Quarterly, Semi-Annually, One-Time, etc)
This depending on the variables selected can have many outcomes...
If direction was Forward, you'd expect the following dates excluding holidays (those I can add later):
08/25/2023, 09/25/2023, 10/25/2023, and so on.
If direction was Backwards it would reverse and be the following:
08/18/2023, 09/18/2023, 10/18/2023, etc.
If the EndofMonth field was set to "Yes" it would be as follows
07/31/2023, 08/31/2023, 09/31/2023......
This is all assuming a monthly reset which isn't always the case, clearly this is a very extensive use of formulas and conditions however, is there any advice on how to implement this into an iterative macro in order to produce ALL resets based on the above data?
I've accomplished 80% of the above conditions in a standard flow however, it only calculates the current period i.e. if today's date is 01/19/23 my next reset date is 01/31/23 and my last was 12/29/2023 (12/31 is a weekend which similar to holiday)
I'm far from expecting a full solution as this involves financial swap understanding, etc. Rather guidance.
