How to create a spreadsheet with alterable cells using vesting schedules

[braucheeinenJob]

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!

 

[Giordano Bruno]

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.

 

[braucheeinenJob]

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!

 

[Giordano Bruno]

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.

 

[braucheeinenJob]

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 .

 

[Giordano Bruno]

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.

 

[excelgeeks]

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 Options	25,000
Vesting Commencement	6/12/2021
1 Year Vest	25%
Monthly Vesting % Thereafter	1/26
Vested Options Date	12/5/2022

 

[Peter_SSs]

@excelgeeks
Welcome to the MrExcel board!
Is this essentially the same question that you have asked here?