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 when Employee has Termination Date (column D)
- Reverse total Amortization on the same month and year entered on 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,
@josovu
Not particularly proud of it but the formulas I added in the attached workbook (below yours) seem to work as intended. With a bit more time and a clearer mind I probably could have done better.
Note that I added a column with the amount for the period and an empty one to have a fixed starting point for a SUM function. It made it easier to 'compose' the formula.
Come back if you can't get it to work in your real file.
Thank you, Riny...I have done tests with different scenarios and this formula works perfectly.
