Creating a Dynamic Vesting Calendar

[excelgeeks]

Basically as the title says:
1. Vesting Begins 1/1/22
2. 30% vests on 1-year anniversary
3. 1/48 vest monthly thereafter (total 5 year vesting period)
5. Total of 40,000 options
6. Need to calculate vested options as of 6/19/22
7. Make calculation dynamic so that the Beginning of the vesting period can be changed by the user

Generally speaking, I have something like this - IF(DATE(YEAR(C20)+1,MONTH(C20),DAY(C20))<TODAY(),C19*C21,0) which gives me the 1 year vesting amount, but not 100% sure how to add the monthly vesting thereafter. The above - C20=Vesting date. C19=# of options. C21=1 year vest %.

Thanks in advance!

 

[Eric W]

Try:

Book1
	B	C
19	# of options	40000
20	Start Date	1/1/2022
21	1 year %	30%
22
23	Vested %	37%
24	Vested Amt	14916.67
Sheet5

Cell Formulas
Range	Formula
C23	C23	=IF(DATEDIF(C20,TODAY(),"y")<1,0,MIN(1,C21+(1-C21)*(DATEDIF(C20,TODAY(),"m")-12)/48))
C24	C24	=C23*C19

 

[excelgeeks]

thank you, that worked! Wondering if you can help me with a related question that I'm using the formula provided as a follow on to calculate Common Distribution. The deal value is $20m with Debt of $7m and Cash of $3m. Given this, the equity to be distributed would be $16m, right?

 

[Eric W]

I'm glad the formula worked for you.

As far as the follow-up question, my financial math is far in the past. I can't help you there.