Formula for Equity Vesting

[SaraNicole]

This post is a follow up to a previous post linked below that @Dave Patton helped with (Thank you Dave!!). The original formula worked great for every scenario I've had up until today so resurfacing this thread to seek some expertise in this forum.

Overall, I'm trying to calculate the amount of stock options (whole numbers) that will be vested as of a specific date, and the number of stock options that will vest within sequential calendar years. The area's i'm having challenges with are in red text, as you'll see in the XL2BB and detailed below. Any help is appreciated!

• Description of challenge: John was issued 50,000 shares that started vesting on 1/12/2015 based on the vesting schedule in Column D (i.e. vested 20% of 50,000 shares at 1 year, and then vested 1/60th of the 50,000 shares each month thereafter). When I update Column G with an effective date of 3/1/2023 (a date that is after all of John's shares should have vested), the formula in Column H produces a number larger than the 50,000 shares that were granted. Additionally Columns K-Q, which outline how many shares will vest each year for John, continues to calculate past the vesting end date (which would be 1/12/2020)

Link to original Question: Formula for Equity Vesting - After Year one 25% vested followed by quarteryly vesting.

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Name	Shares Granted	Vesting Start Date Date	Vesting schedule	Initial Vesting	Initial Vesting Years	Effective Date (used to calculate the exact # of shares that are vested as of this date, in Column H)	Number of Vested Shares as of Effective Date (in column G)	Monthly vested shares	Blank	Shares Vesting in 2020	Shares Vesting in 2021	Shares Vesting in 2022	Shares Vesting in 2023	Shares Vesting in 2024	Shares Vesting in 2025	Shares Vesting in 2026
2											12/31/20	12/31/21	12/31/22	12/31/23	12/31/24	12/31/25	12/31/26
3	John	50000	1/12/15	20% on 1 year, 1/60th monthly thereafter	0.2	1	3/1/2023	80805	833		9996	9996	9996	9996	9996	20	0
4	Helen	1000	10/2/20	20% on 1 year, 1/60th monthly thereafter	0.2	1	3/1/2023	456	16		0	232	192	192	192	192	0
5	Mary	1020	10/16/19	20% on 1 year, 1/60th monthly thereafter	0.2	1	3/1/2023	680	17		238	204	204	204	170	0	0
6	Frank	7500	9/16/20	40% @ 2 years, 1/36th monthly thereafter	0.4	2	3/1/2023	6536	208		0	0	3624	2496	1380	0	0
Sheet1

Cell Formulas
Range	Formula
E3:E6	E3	=--TEXTBEFORE(D3," ")
F3:F6	F3	=--TEXTAFTER(TEXTBEFORE(D3," y"),{"on ","@ "})
H3:H6	H3	=IF(DATEDIF($C3,G3,"y")>=1,INT($B3*E3),0)+LET(m,DATEDIF($C3,G3,"m"),IF(m>12,m-12,0)*$I3)
I3:I6	I3	=INT((B3)/TEXTAFTER(TEXTBEFORE(D3,"th"),"/"))
K3:Q6	K3	=MIN($B3-SUM($J3:J3),(LET(m,DATEDIF($C3,K$2,"m"),IF(AND(m>=$F3*12,m<($F3+1)*12),INT($B3*$E3),0)+IF(m>($F3*12),MAX(0,MIN(m-$F3*12,12))*$I3))))

 

[Dave Patton]

The amount would be fully vested early in 2020. The sheet is set up to start in 2020. You could do a calculation for up to and including 2019.

T202312a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Shares Granted	12-01-16	Vesting schedule	Initial Vesting	Initial Vesting Years	Effective Date (used to calculate the exact # of shares that are vested as of this date, in Column H)	Number of Vested Shares as of Effective Date (in column G)	Monthly vested shares	Blank	Shares Vesting in 2020	Shares Vesting in 2021	Shares Vesting in 2022	Shares Vesting in 2023	Shares Vesting in 2024
2										31-Dec-19	31-Dec-20	31-Dec-21	31-Dec-22	31-Dec-23	31-Dec-24
3	John	50000	12-Jan-15	20% on 1 year, 1/60th monthly thereafter	0.2	1	01-Mar-23	50000	833	49,151	849	0	0	0	0
6bb

Cell Formulas
Range	Formula
E3	E3	=--TEXTBEFORE(D3," ")
F3	F3	=--TEXTAFTER(TEXTBEFORE(D3," y"),{"on ","@ "})
H3	H3	=MIN(B3,IF(DATEDIF($C3,G3,"y")>=1,INT($B3*E3),0)+LET(m,DATEDIF($C3,G3,"m"),IF(m>12,m-12,0)*$I3))
I3	I3	=INT(B3/TEXTAFTER(TEXTBEFORE(D3,"th"),"/"))
J3	J3	=MIN(B3,B3*0.2+DATEDIF(42381,J2,"m")*I3)
K3:O3	K3	=MIN($B3-SUM($J3:J3),(LET(m,DATEDIF($C3,K$2,"m"),IF(AND(m>=$F3*12,m<($F3+1)*12),INT($B3*$E3),0)+IF(m>($F3*12),MAX(0,MIN(m-$F3*12,12))*$I3))))

 

[SaraNicole]

Thank you for the reply @Dave Patton!
Two questions:

1. would the updates you made apply to the other people/grants listed on the XL2BB I submitted?

Trying to confirm if I can copy the updated formulas down, and still have the calcs work on columns K-O.

2. The updated formula on column J - does this represent the total shares that have vested through 12/31/2019?

 

[Dave Patton]

Two questions:

"1. would the updates you made apply to the other people/grants listed on the XL2BB I submitted?

Trying to confirm if I can copy the updated formulas down, and still have the calcs work on columns K-O."

I did not look at the other data and dates.
If you have other earlier dates, you can consider using the initial structure but start the headings (row 2) with earlier YE dates.



"2. The updated formula on column J - does this represent the total shares that have vested through 12/31/2019?"
Yes. You can review the calculation to ensure that the amount is correct.

 

[Dave Patton]

T202312a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Name	Shares Granted	Vesting Start Date Date	Vesting schedule	Initial Vesting	Initial Vesting Years	Effective Date (used to calculate the exact # of shares that are vested as of this date, in Column H)	Number of Vested Shares as of Effective Date (in column G)	Monthly vested shares						Shares Vesting in 2020	Shares Vesting in 2021	Shares Vesting in 2022	Shares Vesting in 2023	Shares Vesting in 2024	Shares Vesting in 2025
2											2016	2017	2018	2019	2020	2021	2022	2023	2024	2025
3	John	50,000	12-Jan-15	20% on 1 year, 1/60th monthly thereafter	20%	1	01-Mar-23	50,000	833		19,163	9,996	9,996	9,996	849
4	Helen	1,000	02-Oct-20	20% on 1 year, 1/60th monthly thereafter	20%	1	01-Mar-23	456	16							232	192	192	192	192
5	Mary	1,020	16-Oct-19	20% on 1 year, 1/60th monthly thereafter	20%	1	01-Mar-23	680	17						238	204	204	204	170
6	Frank	7,500	16-Sep-20	40% @ 2 years, 1/36th monthly thereafter	40%	2	01-Mar-23	6,536	208								3,624	2,496	1380
7
6bbb

Cell Formulas
Range	Formula
E3:E6	E3	=--TEXTBEFORE(D3," ")
F3:F6	F3	=--TEXTAFTER(TEXTBEFORE(D3," y"),{"on ","@ "})
H3:H6	H3	=MIN(B3,IF(DATEDIF($C3,G3,"y")>=1,INT($B3*E3),0)+LET(m,DATEDIF($C3,G3,"m"),IF(m>12,m-12,0)*$I3))
I3:I6	I3	=INT(B3/TEXTAFTER(TEXTBEFORE(D3,"th"),"/"))
K3:T6	K3	=MIN($B3-SUM($J3:J3),LET(m,IFERROR(DATEDIF($C3,K$2,"m"),0),IF(AND(m>=$F3*12,m<($F3+1)*12),INT($B3*$E3),0)+IF(m>($F3*12),MAX(0,MIN(m-$F3*12,12))*$I3)))

 

[SaraNicole]

@Dave Patton this is amazing - Thank you so much! I’ve already used some of your originally formula combos in different applications!

 

[Dave Patton]

Thanks for the feedback.