IF Formulas using Date with Multiple Conditions

[Jomahone]

Hello,

I need to automate a report for Stock Options calculating Monthly Amortization that has to meet Start date and End dates when amortizing monthly. Currently, I am manually plugging formulas. The formulas are inconsistent; needs to be updated for when an employee is terminated. This is so cumbersome because there is a significant number of employees and it takes me over an hour to complete the report.

What I need is one formula that I can use for all the rows and columns and eliminate manual plugging of formulas.
Amortize Stock Options with the following conditions:
- Start calculating Amortization when Stock Option Grant Date, (column F) has the same Month and Year (regardless of day) with the header periods starting on column K1
- Continue calculating amortization UNTIL Stock Option Amort End Date (column G). Stop amortizing the month after Stock Option Amort End Date and thereafter
- Stop the Amortization on the month and year of Employee Termination Date (column D)
- Reverse total Amortization on the month and year entered o Stock Option Grace Termination Date (column E,)

I have attached a template with desired outcomes. It would be great if formulas starting on column K and thereafter is replaced with a more efficient formula.

Thank you,
JSM

 

[Faseeh]

Hi,

Please see if solution in SuggestedSol is acceptable.

Thanks

 

[Jomahone]

Hi Faseeh...Thank you so much for taking the time to look into my report. When I tested your solution, it does not appear to work when I remove the termination date, the results generated zero values . It would be great if the calculation also takes into consideration for when a row has no termination date, the amortization must continue until the end date entered on column G.

 

[Faseeh]

In your given example, the stock Option Grand Date start month does not reflect on month time line for example if i set it to April 25, the month of Feb and March should display zero. Is this correct? So as per my understanding, amortization amount should start from a start_month, should see if there is a terminationd date, if not fill entire row, if yes should stop on the termination month and amount should display in reverse after the grace period.

 

[Jomahone]

If you enter Stock Option Grant date start to start April 15, 2025 for example, the formula should start amortization on the header period of April 30, 2025, previous periods Jan to Mar would be zero. In my example, I entered Feb 5, 2025 so the amortization should begin in the column period header of Feb 28-25. Refer to Row 2 from the submitted template of desired outcome. The termination date is optional, but it needs to be considered in the formula when the employee terminates. When a termination date is entered, amortization stops on the same month and year as the termination date. The reversal should be reflected 90 days after termination date as entered on column C. Refer to row 3 of the submitted template for desired outcome.

Hopefully this clears up any confusion.

Thank you,
JSM

 

[Faseeh]

Thank you. see attachment.