Equity vesting is a process where an employee or a founder of a company earns shares or stock options over time, based on certain conditions or milestones. Equity vesting is often used as an incentive or a reward for the employee’s or founder’s contribution to the company’s growth and success.
There are different types of equity vesting schemes, such as:
- Cliff vesting: A percentage of the equity vests after a specified period of time, usually one year. For example, an employee may receive 25% of their shares after one year of service, and nothing before that.
- Graded vesting: A percentage of the equity vests gradually over a period of time, usually monthly or quarterly. For example, an employee may receive 20% of their shares after one year of service, and then 1/60th of the remaining shares every month thereafter.
- Milestone vesting: A percentage of the equity vests upon achieving certain goals or targets, such as revenue, profit, or customer acquisition. For example, a founder may receive 10% of their shares after reaching $1 million in revenue, and another 10% after reaching $5 million in revenue.
In this article, we will focus on how to calculate the amount of vested equity using Excel formulas, based on the vesting start date, the vesting schedule, and the effective date. We will also provide a scenario to illustrate the calculation with real data.
The Basic Formula
The basic formula for calculating the amount of vested equity is:
The vesting percentage depends on the type and the terms of the vesting scheme. For cliff vesting, the vesting percentage is either 0% or a fixed percentage, depending on whether the effective date is before or after the cliff date. For graded vesting, the vesting percentage is a function of the number of months or quarters that have passed since the vesting start date, up to a maximum of 100%. For milestone vesting, the vesting percentage is a function of the achievement of the milestones, which may be binary or proportional.
To calculate the vesting percentage in Excel, we can use the IF and DATEDIF functions. The IF function evaluates a logical condition and returns one value if the condition is true, and another value if the condition is false. The DATEDIF function calculates the difference between two dates in various units, such as years, months, or days.
For example, suppose we have the following data in Excel:
| Name | Total Equity | Vesting Start Date | Vesting Schedule | Effective Date |
|---|---|---|---|---|
| John | 50,000 | 1/12/2015 | 20% on 1 year, 1/60th monthly thereafter | 3/1/2023 |
| Helen | 1,000 | 10/2/2020 | 20% on 1 year, 1/60th monthly thereafter | 3/1/2023 |
| Mary | 1,020 | 10/16/2019 | 20% on 1 year, 1/60th monthly thereafter | 3/1/2023 |
| Frank | 7,500 | 9/16/2020 | 40% @ 2 years, 1/36th monthly thereafter | 3/1/2023 |
To calculate the vesting percentage for John, who has a cliff vesting scheme, we can use the following formula:
=IF(DATEDIF(C2,E2,"y")>=1,20%,0%)
This formula checks if the difference between the vesting start date and the effective date is greater than or equal to one year, in which case the vesting percentage is 20%, otherwise it is 0%.
To calculate the vesting percentage for Helen, who has a graded vesting scheme, we can use the following formula:
=MIN(DATEDIF(C3,E3,"y")*20%,20%)+IF(DATEDIF(C3,E3,"m")>12,(DATEDIF(C3,E3,"m")-12)/60,0)
This formula calculates the initial vesting percentage as the product of the number of years and 20%, up to a maximum of 20%, and then adds the monthly vesting percentage as the fraction of the number of months above 12 and 60.
To calculate the vesting percentage for Mary, who has the same graded vesting scheme as Helen, we can use the same formula as above, but with different cell references:
=MIN(DATEDIF(C4,E4,"y")*20%,20%)+IF(DATEDIF(C4,E4,"m")>12,(DATEDIF(C4,E4,"m")-12)/60,0)
To calculate the vesting percentage for Frank, who has a different graded vesting scheme, we can use the following formula:
=MIN(DATEDIF(C5,E5,"y")*40%,40%)+IF(DATEDIF(C5,E5,"m")>24,(DATEDIF(C5,E5,"m")-24)/36,0)
This formula calculates the initial vesting percentage as the product of the number of years and 40%, up to a maximum of 40%, and then adds the monthly vesting percentage as the fraction of the number of months above 24 and 36.
The Scenario
To illustrate the calculation of the vested equity with real data, let us consider the following scenario:
Alice is a co-founder of a startup that develops a mobile app for online shopping. She was granted 100,000 shares of the company’s common stock on 1/1/2020, with a vesting schedule of 25% on 1 year, and then 1/48th monthly thereafter. The company’s valuation as of 3/1/2024 is $10 million, and Alice wants to know how much her shares are worth as of that date.
To calculate Alice’s vested equity, we can use the following steps:
- Enter the data in Excel, as shown below:
| Name | Total Equity | Vesting Start Date | Vesting Schedule | Effective Date | Valuation |
|---|---|---|---|---|---|
| Alice | 100,000 | 1/1/2020 | 25% on 1 year, 1/48th monthly thereafter | 3/1/2024 | $10,000,000 |
- Calculate the vesting percentage for Alice, using the formula:
=MIN(DATEDIF(C2,E2,"y")*25%,25%)+IF(DATEDIF(C2,E2,"m")>12,(DATEDIF(C2,E2,"m")-12)/48,0)
The result is 0.7292, or 72.92%.
- Calculate the vested equity for Alice, using the formula:
=B2*F2
The result is 72,916.67, or 72,917 shares.
- Calculate the value of the vested equity for Alice, using the formula:
=G2/F6
The result is $7,291,666.67, or $7.29 million.
- Format the results as desired, such as adding commas, decimals, or currency symbols.
The final output is shown below:
| Name | Total Equity | Vesting Start Date | Vesting Schedule | Effective Date | Valuation | Vesting Percentage | Vested Equity | Value |
|---|---|---|---|---|---|---|---|---|
| Alice | 100,000 | 1/1/2020 | 25% on 1 year, 1/48th monthly thereafter | 3/1/2024 | $10,000,000 | 72.92% | 72,917 | $7,291,666.67 |
