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

A

avgsuperhero

New Member
Joined
Oct 1, 2014
Messages
4
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!
 
Sort by date Sort by votes
Adding on with another, similar question.
I'm not an expert excel professional so extra details are welcomed:)

I'm trying to calculate the amount of stock that will be vested as of a specific date, and the number of stock that will vest within sequential calendar years, Much like the original author, I'm doing this all by hand now and it's just gotten to be too much and invites human error. I'm also not even sure where to start. Below i'm including the kind of data I have and what I need to fill in (see blank columns). We have several different vesting schedules. Listed a few below; the one in blue text is the most commonly used.. Any ideas/support would be appreciated.

NameSharesVesting Start Date DateVesting scheduleEffective DateNumber of Vested Shares as of Effective DateShares Vesting in 2022Shares Vesting in 2023Shares Vesting in 2024Shares Vesting in 2025
John1,00010/16/201920% on 1 year, 1/60th monthly thereafter
Frank7,5009/16/202040% @ 2 years, 1/36th monthly thereafter
Tom1,0009/16/202020% on 1 year, 1/60th monthly thereafter
Shelly33,75010/14/202320% on 1 year, 1/60th monthly thereafter
Mary75,00010/2/202125% up front, remainder over 48 Months
John1,00010/2/202220% on 1 year, 1/60th monthly thereafter
Frank90,00010/2/202220% on 1 year, 1/60th monthly thereafter
Tom60,0005/2/202110% on 1 year Cliff, 20% monthly during year 2, 20% monthly during year 3, 20% monthly during year 4, 30% monthly during year 5
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Welcome to the forum.

This thread is from 2014. You may secure more responses if you start a new thread.
N.B. You can post an extract of your sheet with the forum's tool named XL2BB.
We do not know the rules and regulations in your area or what you require except for the information that you provide.
What are your expected results for a couple of rows.
What number are vested?
Do the shares vest in fractional units?
What is effective date?
What is the number of shares for the calculation?
What date does the calculation begin?

Quick post pending more complete information.
You can paste the post to a clean sheet. Click on the icon below the f(x) in the heading and then move to your sheet and paste at A1.

T202312a.xlsm
ABCDEFGHIJKLM
1shares vested by year
2NameSharesVesting Start Date DateVesting scheduleEffective DateNumber of Vested Shares as of Effective DateMonthlyShares Vesting in 2020Shares Vesting in 2021Shares Vesting in 2022Shares Vesting in 2023Shares Vesting in 2024Shares Vesting in 2025
331-12-2020212022202320242025
4John1,02016-Oct-1920% on 1 year, 1/60th monthly thereafter1,02017238204204204170
5
6b
Cell Formulas
RangeFormula
F4F4=B4
G4G4=F4/60
H4H4=0.2*B4+(DATEDIF(DATE(2020,MONTH(C4),DAY(C4)),H3,"m")/60*B4)
I4:L4I4=MAX(0,MIN($G4*12,$F4-SUM($H$4:H4)))
 
Upvote 0
@Dave Patton Thank you so much for the quick and thorough reply, and the great questions. Answering your questions below & have a few follow up questions. This is already much farther along than I have been able to get to myself so thank you!
I got an error when I tried to download the forum's tool, XL2BB. I can try again, but if you have any tips i'll take them.

What number are vested? Your formulas produced the correct values for John for G:M
Do the shares vest in fractional units? They do not. Just whole shares
What is effective date? The "effective date" is a particular date that we want to calculate the total number of vested shares. For John in the example you mapped out, if the effective date was 16 Nov 2023, his total number of vested shares would be 833 as of 16 Nov 2023. In that calculation John would have vested 20% of the 1020 shares on 16 Oct 2020, and then 17 shares each month after that until 16 Nov 2023.
What is the number of shares for the calculation? you got this correct - it's B4
What date does the calculation begin? You made the correct assumption in your calculation. It begins on the Vesting Start Date

Follow up questions:
  1. Is there a way to create one formula for G:M that is dynamic based on the Vesting start date? for example, if I use 16 Sept 2020 as the vesting start date in your formula, it incorrectly calculates the shares that will vest in 2020 as 255, when it should be zero
  2. For F4, what formula could I use to calculate the total number of shares that vest by a date that's listed in E4 (see comments to question" what is effective date")?
 
Upvote 0
1. XL2BB
check the information at www.mrexcel.com/board/excel-articles/xl2bb-excel-range-to-bbcode.1/

2. Do the shares vest in fractional units? They do not. Just whole shares Formula was edited see below.
Try reviewing the formula with Excels Formulas Formula Evaluate


T202312a.xlsm
ABCDEFGHIJKLMN
1shares vested by year
2NameSharesVesting Start Date DateVesting scheduleEffective DateNumber of Vested Shares as of Effective DateMonthlyBlankShares Vesting in 2020Shares Vesting in 2021Shares Vesting in 2022Shares Vesting in 2023Shares Vesting in 2024Shares Vesting in 2025
331-12-2020212022202320242025
4John1,02016-Oct-1920% on 1 year, 1/60th monthly thereafter1,020172382042042041700
51,00002-Oct-2020% on 1 year, 1/60th monthly thereafter1,000160232192192192192
61,02016-Oct-1920% on 1 year, 1/60th monthly thereafter15-Nov-23816172382042042041700
6b
Cell Formulas
RangeFormula
F4:F5F4=B4
G4:G6G4=INT(B4/60)
F6F6=IF(DATEDIF($C6,E6,"y")>=1,INT($B6*0.2),0)+LET(m,DATEDIF($C6,E6,"m"),IF(m>12,m-12,0)*$G6)
I4:N6I4=MIN($B4-SUM($H4:H4),(IF(DATEDIF($C4,I$3,"y")=1,INT($B4*0.2),0)+LET(m,DATEDIF($C4,I$3,"m"),IF(m>12,MAX(0,MIN(m-12,12))*$G4))))
 
Upvote 0
@Dave Patton this worked! I am so impressed. Thank you for the support, and for mapping things out so clearly. It was really easy to follow along with your thinking by the way you outlined the formulas in each cell. I'm going to play around with this formula and the other vesting schedules we have and see if I can piece together a solution that will apply a different formula based on the specific vesting schedule we have for each equity grant.

And thank you for the link on downloading XL2BB - I have it downloaded and ready for future use!

One final question if you're open to sharing. Are there any resources you'd recommend for someone who is trying to be more self sufficient in excel?
I've got a good handle on aspects of Excel like Vlookups and basic functions, I understand the basics of macros and power query, but I'm not proficient in all of the excel functions, and sometimes I'm not exactly sure how to piece the different functions together like you did in this example. I plan to stay close to this forum for learning, but if you have any other tips I'd love them!
 
Upvote 0
"I plan to stay close to this forum for learning, but if you have any other tips I'd love them!"
Ongoing improvement is a great plan.
Watch for new ideas, help others on the forum, try new ways of improving your spreadsheets.
N.B. You can test posts to the test area for XL2BB.

A variety of changes; hopefully, some improvements.

T202312a.xlsm
ABCDEFGHIJKLMNOPQ
1
2NameSharesVesting Start Date DateVesting scheduleInitial VestingInitial Vesting YearsEffective DateNumber of Vested Shares as of Effective DateMonthlyBlankShares Vesting in 2020Shares Vesting in 2021Shares Vesting in 2022Shares Vesting in 2023Shares Vesting in 2024Shares Vesting in 2025Shares Vesting in 2026
331-12-20202120222023202420252026
4John1,02016-Oct-1920% on 1 year, 1/60th monthly thereafter20%11323015615615615615610
51,00002-Oct-2020% on 1 year, 1/60th monthly thereafter20%113 226156156156156150
61,02016-Oct-1920% on 1 year, 1/60th monthly thereafter20%115-Nov-236721323015615615615615610
7Frank7,50016-Sep-2040% @ 2 years, 1/36th monthly thereafter40%2125  3375150015001125 
6b
Cell Formulas
RangeFormula
E4:E7E4=--TEXTBEFORE(D4," ")
F4:F7F4=--TEXTAFTER(TEXTBEFORE(D4," y"),{"on ","@ "})
K4:Q7K4=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))))
H6H6=IF(DATEDIF($C6,G6,"y")>=1,INT($B6*E6),0)+LET(m,DATEDIF($C6,G6,"m"),IF(m>12,m-12,0)*$I6)
I4:I7I4=INT((B4-B4*E4)/TEXTAFTER(TEXTBEFORE(D4,"th"),"/"))
 
Last edited:
Upvote 0
This works perfectly @Dave Patton! Thank you so much again!
Dave Patton said:
"I plan to stay close to this forum for learning, but if you have any other tips I'd love them!"
Ongoing improvement is a great plan.
Watch for new ideas, help others on the forum, try new ways of improving your spreadsheets.
N.B. You can test posts to the test area for XL2BB.

A variety of changes; hopefully, some improvements.

T202312a.xlsm
ABCDEFGHIJKLMNOPQ
1
2NameSharesVesting Start Date DateVesting scheduleInitial VestingInitial Vesting YearsEffective DateNumber of Vested Shares as of Effective DateMonthlyBlankShares Vesting in 2020Shares Vesting in 2021Shares Vesting in 2022Shares Vesting in 2023Shares Vesting in 2024Shares Vesting in 2025Shares Vesting in 2026
331-12-20202120222023202420252026
4John1,02016-Oct-1920% on 1 year, 1/60th monthly thereafter20%11323015615615615615610
51,00002-Oct-2020% on 1 year, 1/60th monthly thereafter20%113 226156156156156150
61,02016-Oct-1920% on 1 year, 1/60th monthly thereafter20%115-Nov-236721323015615615615615610
7Frank7,50016-Sep-2040% @ 2 years, 1/36th monthly thereafter40%2125  3375150015001125 
6b
Cell Formulas
RangeFormula
E4:E7E4=--TEXTBEFORE(D4," ")
F4:F7F4=--TEXTAFTER(TEXTBEFORE(D4," y"),{"on ","@ "})
K4:Q7K4=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))))
H6H6=IF(DATEDIF($C6,G6,"y")>=1,INT($B6*E6),0)+LET(m,DATEDIF($C6,G6,"m"),IF(m>12,m-12,0)*$I6)
I4:I7I4=INT((B4-B4*E4)/TEXTAFTER(TEXTBEFORE(D4,"th"),"/"))
Click to expand...
 
Upvote 0
@Dave Patton - Opps just found something after I hit send on my last reply. Column I (the amount of shares vesting each month), incorrectly subtracts the inital vesting on year one from the total shares, and then divides by the "##th" in column D. This calculation here should just be the total Shares/the "##th" in column D. So for row 4 the monthly vesting would be B4(total shares)/60 (months) =17 shares vested per month. Think I can just update the formula for I4 to be =INT((B4)/TEXTAFTER(TEXTBEFORE(D4,"th"),"/"))
See any concerns with that change?

app.xlsx
ABCDEFGHIJKLMNOPQ
1
2NameSharesVesting Start Date DateVesting scheduleInitial VestingInitial Vesting YearsEffective DateNumber of Vested Shares as of Effective DateMonthlyBlankShares Vesting in 2020Shares Vesting in 2021Shares Vesting in 2022Shares Vesting in 2023Shares Vesting in 2024Shares Vesting in 2025Shares Vesting in 2026
312/31/202012/31/202112/31/202212/31/202312/31/202412/31/202512/31/2026
4John102010/16/201920% on 1 year, 1/60th monthly thereafter0.211723820420420417000
5100010/2/202020% on 1 year, 1/60th monthly thereafter0.211602321921921921920
6102010/16/201920% on 1 year, 1/60th monthly thereafter0.2111/15/20238161723820420420417000
7Frank75009/16/202040% @ 2 years, 1/36th monthly thereafter0.422080036242496138000
Sheet1
Cell Formulas
RangeFormula
E4:E7E4=--TEXTBEFORE(D4," ")
F4:F7F4=--TEXTAFTER(TEXTBEFORE(D4," y"),{"on ","@ "})
K4:Q7K4=MIN($B4-SUM($J4:J4),(LET(m,DATEDIF($C4,K$4,"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))))
H6H6=IF(DATEDIF($C6,G6,"y")>=1,INT($B6*E6),0)+LET(m,DATEDIF($C6,G6,"m"),IF(m>12,m-12,0)*$I6)
I4:I7I4=INT((B4)/TEXTAFTER(TEXTBEFORE(D4,"th"),"/"))
 
Upvote 0
T202312a.xlsm
EFGHIJKLMNOPQ
1020481613.613102023015615615615615610
112041,020171020238204204204170
6b
Cell Formulas
RangeFormula
E10E10=1020*0.2
F10F10=B4-E10
H10H10=INT(G10)
J10J10=E10+G10*60
K10K10=204+2*13
L10:P10L10=13*12
G10:G11G10=F10/60
K11K11=204+2*G11
L11:N11L11=12*$G11
O11O11=10*G11



The calculation depends on the exact wording of the vesting document.
1/60 implies 60 amounts.
If we do not deduct the initial amount, the 17 would allow only 48 payments.


T202312a.xlsm
DEFGHIJKL
13Reconciliation
14N.B. vesting by integer therefore 131010101020
15Note 2 payments in 2020 and 12 in 21-26
16The monthly amounts are low by .6 but 2 extra payments are made leaving 10 for 2026.
6b
Cell Formulas
RangeFormula
H14H14=SUM(K10:P10)
I14I14=B10-H14
J14J14=13*62+204+10
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Vesting Schedule - Formula/Formatting
Replies
2
Views
241
ExcelUser18
E
S
  • Question Question
Formula for Equity Vesting
Replies
6
Views
1K
Dave Patton
D
M
  • Question Question
Calculate Date + 2 years starting 1st Jan
Replies
5
Views
115
FormR
FormR
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
567
mrshuck
M
E
  • Solved
Add together figures for Year-To-Date (with criteria)
Replies
3
Views
210
Tetra201
Tetra201

Forum statistics

Threads
1,224,814
Messages
6,181,124
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top