How to create a spreadsheet with alterable cells using vesting schedules

braucheeinenJob

New Member
Joined
Jun 3, 2011
Messages
3
Hi! I have a chart with four columns: shares, grant date, vesting schedule, and prior sales. With this information, I have to create a spreadsheet that calculates how many of Employee X's shares will be vested on any given future date, taking into consideration (i.e. adding alterable cells) possible future sales or tax withholdings. The only problem is, I don't know what functions to use to create this spreadsheet. If anyone has any tips, I would greatly appreciate it. Thanks!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Welcome to the forum braucheeinenJob. I think you will have to provide more detail. For example, you don't even tell us what goes in each of the columns. Under "Shares" is there text or values, etc.

You should tell us what the formulas are supposed to return and what they have to work upon.

If you can provide this information, then maybe someone can help you.
 
Upvote 0
Thanks! Sorry, I don't have very much experience making spreadsheets (clearly).

Under the Shares column, I have the number of shares; under Grant Date, it's obviously just a date; the vesting schedules vary, but an example would be: "30% vests on the first anniversary of the closing date (which is the same as the grant date); the remainder vests 1.666666667% per month thereafter."; the prior sales vary as well (e.g. x amount of shares sold on a specific date, x amount of shares withheld for tax on this date, etc.)

I hope that's enough information!
 
Upvote 0
Hi braucheeinenJob,

That's enough information to tell me that the whole project is in a bit of a mess.

If "30% vests on the first anniversary of the closing date" then "% which vests on the first anniversary of the closing date" should be the header to the colum and the cell entry under that header should be just "30%". If the other entries in that column vary, then they should be put in different columns with different headers.

If you can do that, I recommend that you simplify the problem by looking at the various parts of the solution separately. Suppose that the number of shares is in cell A2, the 30% is in B2, the Closing date is in C2, then the formula if "IF(DATE(YEAR(C2)+1,MONTH(C2),DAY(C2))<TODAY(),A2*B2,0)" will provide you with the initial vesting based upon the criteria that one year had passed. A similar formula could calculate the 1.67% per month thereafter. Other formulas could deal with shares sold on specific dates, etc.

The key to all this is to present the information in such a way that a formula can be consistently applied to the various columns. This forum could heop with the individual formulas if you can present the problem in a clearly defined way. If you have a mixture of values and text in a single cell, that is a recipe for disaster.
 
Upvote 0
There won’t be a mixture of values in a single cell. I am probably not explaining it very well so here is a better example (hopefully)

Cell A1 contains Number of Shares for grant 1 (10000) Cell B1 contains the grant date for grant 1 (06/30/2010)
The vesting schedule for grant 1 (which I don’t think has to be a cell at all, just used for calculation) is 50% after first year and 1.666666667% per month thereafter for the remainder of shares

Cell A2 contains Number of shares for grant 2 (5000) cell B2 contains the grant date for grant 2 (08/30/2010)
The vesting schedule for grant 2 is 50% after 2 years and 25% at the end of the 3rd and 4th year.



The spreadsheet also must contain a cell (C1 )for input where the user can input any future date to determine how many shares are vested (D1). For example,
In C1 the user enters 06/30/2011 I need to be able to calculate the time between the value of C1 and my known grant dates so that I can then calculate the shares that have vested based on the schedule and the given time period.

In the example above the number of shares that have vested will be (D1)5000 or 50% of the total amount granted in grant 1. None of the shares for grant 2 will have vested yet.

If the user entered 08/30/2012 then the amount vested would be 6166 shares from grant 1 (that’s 5000 or 50% after the first year and 1166 shares or 1.666666667% of the remaining per month (83.33 shares per month for 14 months). From grant 2 there will be 2500 shares or 50% of the total. The value therefore returned to (D1) would be 8666.

The problem is I’m not sure how to do these calculations within excel based on the variable date that is entered .
 
Upvote 0
Thanks for the detail braucheeinenJob

Please try the following formula in D1:
=ROUNDDOWN(MIN(A1,IF(DATEDIF(B1,C1,"m")<12,0,A1*50%+(DATEDIF(B1,C1,"m")-12)*(5/6)%*A1)),0)+ROUNDDOWN(MIN(A2,IF(DATEDIF(B2,C1,"y")<2,0,A2*50%+(DATEDIF(B2,C1,"y")-2)*A2*25%)),0)

This formula works for me with the data you have presented, but you might prefer to adjust some of the dates by a day when you test it further (DATEDIF rounds everything down)

Hope that helps.
 
Upvote 0
Hi braucheeinenJob,

That's enough information to tell me that the whole project is in a bit of a mess.

If "30% vests on the first anniversary of the closing date" then "% which vests on the first anniversary of the closing date" should be the header to the colum and the cell entry under that header should be just "30%". If the other entries in that column vary, then they should be put in different columns with different headers.

If you can do that, I recommend that you simplify the problem by looking at the various parts of the solution separately. Suppose that the number of shares is in cell A2, the 30% is in B2, the Closing date is in C2, then the formula if "IF(DATE(YEAR(C2)+1,MONTH(C2),DAY(C2))<TODAY(),A2*B2,0)" will provide you with the initial vesting based upon the criteria that one year had passed. A similar formula could calculate the 1.67% per month thereafter. Other formulas could deal with shares sold on specific dates, etc.

The key to all this is to present the information in such a way that a formula can be consistently applied to the various columns. This forum could heop with the individual formulas if you can present the problem in a clearly defined way. If you have a mixture of values and text in a single cell, that is a recipe for disaster.
Have a follow up to this with a similar question/issue. Trying to make it dynamic to include the 1/26 each month, and be able to swap out the 12/3/22 date.

ABC Stock Options25,000
Vesting Commencement6/12/2021
1 Year Vest25%
Monthly Vesting % Thereafter1/26
Vested Options Date12/5/2022
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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