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!

 

[Ron Coderre]

This formula returns the percent vested as of the current date:

=MIN(DATEDIF(AwardDate,Today(),"y")/4,1)/CODE]

Is that something you can work with?

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F
1				Balance	Vesting
2	Award Date	01/01/13	50,000.00	50,000.00	Initially	25%
3	Vested	01/01/14	12,500.00	37,500.00	Quarterly	5%
4		31/03/14	2,500.00	35,000.00
5		30/06/14	2,500.00	32,500.00
6		30/09/14	2,500.00	30,000.00
7		31/12/14
8
9	Can you supply an example of your calculations?
10	How is the amount vested calculated?
11	The above is just a guess at your situation.
12
1a

Excel 2003

Cell Formulas
Range	Formula
D2	=+C2
D3	=D2-C3
D4	=D3-C4
D5	=D4-C5
D6	=D5-C6
C3	=IF(AND($B$2=41275,B3)),F2*$C$2,"")
C4	=IF(AND($B$2=41275,B4)),$F$3*$C$2,"")
C5	=IF(AND($B$2=41275,B5)),$F$3*$C$2,"")
C6	=IF(AND($B$2=41275,B6)),$F$3*$C$2,"")
C7	=IF(AND($B$2=41275,B7)),$F$3*$C$2,"")

 

[avgsuperhero]

That makes sense, but I feel like it will create too many line items. However, that may be the only way to do it. I was hoping we could potentially have the formula calculated in a single cell in column G? Heres what it currently looks like, apologies for being a novice.

	A	B	C	D	E	F	G	H	I
1	Name	Benchmark	Class B Units	Total Class B Units	Class B Proportionate Percentage	Date of Grant	Vested	Total Vested	Formula
2	John Smith	$3.000	7,500.00	25,000.00	N/A	10/2/13	1,875.00	3,750.00	1 year 25%, 3 years quarterly
3	John Smith	$3.000	7,500.00			10/2/13	1,875.00		1 year 25%, 3 years quarterly
4	John Smith	$4.500	10,000.00			1/17/14	0.00		1 year 25%, 3 years quarterly

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Benchmark	Class B Units	Total Class B Units	Class B Proportionate Percentage	Date of Grant	Start	Calc	Quarters to date	Vested 1 year	Vested Quarterly	Total Vested to Date
2							Date	Date		25%	6.25%
3	John Smith	$3.00	7,500.00	25,000.00	N/A	2-Oct-13	1-Jan-13	2-Oct-14	7	1,875.00	1,406.25	3,281.25
4	John Smith	$3.00	7,500.00			2-Oct-13				1,875.00
5	John Smith	$4.50	10,000.00			17-Jan-14				0
6
1aa

Excel 2003

Cell Formulas
Range	Formula
K2	=(1-0.25)/(3*4)
K3	=IF(I3>4,(I3-4)*$K$2*C3,"")
I3	=INT((H3-G3)/90)
J3	=(I3>=4)*(C3*$J$2)
L3	=+K3+J3

Please provide information on relevant dates and details on how you calculate the amounts.
N.B. I inserted 2 columns for the dates.

hth Dave

 

[avgsuperhero]

Worksheet Formulas Cell Formula K2 =(1-0.25)/(3*4) I3 =INT((H3-G3)/90) J3 =(I3>=4)*(C3*$J$2) K3 =IF(I3>4,(I3-4)*$K$2*C3,"") L3 =+K3+J3 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

So that makes total sense and was super helpful! My only concern is in I3 when we divide by 90. Is this offset by rounding down or does the formula recognize 90 days as a quarter of a year?

 

[Dave Patton]

I3 =INT((H3-G3)/90)

Edit the 90 to 91

The INT part effectively rounds to a whole number and any decimal part is excluded.


91 * 4 = 364 which is nearly an entire year.

If you are calculating on a date that is a quarter end date, ensure the the number of quarters is correct.

 

[Dave Patton]

consider

31-Dec-13 30-Jun-14 2

=INT((B3-A3+(B3=EOMONTH(B3,0))*2)/91)

Try Excel's Formula Evaluate to step through the formula.

 

[avgsuperhero]

That's perfect, makes a lot of sense the way you broke things down.

Thank you very much.

 

[Dave Patton]

Thanks for the feedback.

Dave