Formula for Equity Vesting - After Year one 25% vested followed by quarteryly vesting.

[avgsuperhero]

I'm attempting to write a formula to calculate the amount of vested equity based on award date and current date.

If award date is 1/1/2013 for 50,000 units than on 1/1/2014 25% have vested. The remaining 75% will vest on a quarterly bases. I'd like it updated in real time based on today's date.

I'm not even sure where to start with this one, right now I'm entering everything by hand doing the calculations monthly, but there is too much room for human error.

Thanks!

 

[Dave Patton]

If for 1/60, we expect 48 amounts. The following might eliminate the rounding issue but I have not edited the formula.
Or use the edit you cite in your last post which is probably the initial approach; the rounding would probably resolve in the last year.

T202312a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
26		1,000	20	16-Oct-19	1/60th	200				33	200	200	200	167
27		1015				203				33	203	203	203	170
28
29					48	vested amounts
30
6b

Cell Formulas
Range	Formula
J26:J27	J26	=INT(2*$B26/60-SUM($I26:I26))
K26:K27	K26	=INT(14*$B26/60-SUM($I26:J26))
L26:L27	L26	=INT(26*$B26/60-SUM($I26:K26))
M26:M27	M26	=INT(38*$B26/60-SUM($I26:L26))
N26:N27	N26	=INT(48*$B26/60-SUM($I26:M26))
F26:F27	F26	=0.2*B26
E29	E29	=60*0.8

 

[Dave Patton]

T202312a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1								Vested by year
2	Name	Shares	Start Date	Vesting schedule	Initial Vesting	Years to vesting	Effective Date	# Vested @ Effective Date	By Mo	Blank	------------- Shares vesting in -----------------------
3											2020	2021	2022	2023	2024	2025	2026
4	John	1,020	16-Oct-19	20% on 1 year, 1/60th monthly thereafter	20%	1			17		238	204	204	204	170
5	Frank	7,500	16-Sep-20	40% @ 2 years, 1/36th monthly thereafter	40%	2			208				3,624	2,496	1,380
6b

Cell Formulas
Range	Formula
E4:E5	E4	=--TEXTBEFORE(D4," ")
F4:F5	F4	=--TEXTAFTER(TEXTBEFORE(D4," y"),{"on ","@ "})
K4:Q5	K4	=MIN($B4-SUM($J4:J4),(LET(m,DATEDIF($C4,K$3,"m"),IF(AND(m>=$F4*12,m<($F4+1)*12),INT($B4*$E4),0)+IF(m>($F4*12),MAX(0,MIN(m-$F4*12,12))*$I4))))
I4:I5	I4	=INT(B4/TEXTAFTER(TEXTBEFORE(D4,"th"),"/"))

 

[Dave Patton]

T202312a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3											2020	2021	2022	2023	2024
4	John	1,020	16-Oct-19	20% on 1 year, 1/60th monthly thereafter	20%	1			17		238	204	204	204	170
5	Frank	7,500	16-Sep-20	40% @ 2 years, 1/36th monthly thereafter	40%	2			208				3,624	2,496	1,380
6b

Cell Formulas
Range	Formula
E4:E5	E4	=--TEXTBEFORE(D4," ")
F4:F5	F4	=--TEXTAFTER(TEXTBEFORE(D4," y"),{"on ","@ "})
K4:O5	K4	=MIN($B4-SUM($J4:J4),(LET(m,DATEDIF($C4,K$3,"m"),IF(AND(m>=$F4*12,m<($F4+1)*12),INT($B4*$E4),0)+IF(m>($F4*12),MAX(0,MIN(m-$F4*12,12))*$I4))))
I4:I5	I4	=INT(B4/TEXTAFTER(TEXTBEFORE(D4,"th"),"/"))