Intro
Having committed people on board is the #1 concern of any founder. Startups are born to achieve bold and compelling visions, in spaces where typically low entry barriers and switching costs make competition extremely hard. Their paths are often anything but linear and involve tough and disheartening times.
Incentivizing your team is thus one powerful leverage any founder has to align objectives and make everyone play an active role in the company's success.
Here’s a sneak peek of what you will get:
Glossary
Before looking closely at our ESOP tracker, let's go through some useful concepts:
- Employee equity pool. A portion of a company's equity is set aside as a form of incentive compensation for employees.
- Stock option. A security that gives the owner the right, but not the obligation, to buy a specific stock for a predetermined price in the future.
- Virtual stock option. Security that mimics the payout of a stock option, granting the owner an equivalent cash payment in the future once specific conditions are met (commonly referred also as phantom stock)
- Strike price. The predetermined price at which you can buy stock in the future, under a (virtual) option agreement.
- Liquidity event. It refers to the event (usually, the sale of all or a substantial portion of one company's shares) that triggers employees' equity-linked compensation.
- Vesting. It refers to the process by which the recipient is rewarded with shares or stock options but receives the full rights to them gradually, over a preset tenure.
- Cliff. It refers to the milestones (usually measured in months, quarters, or years) through which the vesting process is granted.
Let's start!
To use our template, follow the link Employee equity tracker, click on Use template and save it in one of your workspace’s folders.
This template offers you an easy UI to track the vesting and allotment of your ESOP over time.
Let's reverse-engineer it together.
First, use the B column of the Input table to add your company's details, including the total number of shares outstanding, the total number of shares in your employee equity pool, the share price of the last funding round, and the vesting scheme (monthly or quarterly).
Cell B1 contains a TODAY() function that displays the current date, automating updates whenever you open the spreadsheet.
The table 'Allotment list' lets you write down the details of all the allotments you granted to your team: the date of allotment, the employees' full name, the number of shares, the vesting cliff, and the horizon. In case any of them leaves the company, use column F to input the leave date. In case of multiple allotments to the same employee, make sure you input the leave date on all the records.
The Overview table offers you a highly summarized view of the status of your entire pool, including the allotment and vesting progress.
Let's now go deeper into the vesting process.
The Vesting page gives you an overall view of the share split and the current vesting process. A chart gives you an immediate view of the shares still not vested:
If you look at data per allotment, the table adds some logic to the Allotment list to document the actual progress of vesting: column B simply contains the number of shares allotted, and column C calculates their market value by multiplying the value in B times the last share price and column D the corresponding % ownership in basis points. Column E includes the following formula to calculate the # of vested shares to date, as follows:
1=IF(A2<>"",IF(OR('Input'!$B$1<('Allotments list'!B2+'Allotments list'!D2*365),AND('Allotments list'!F2<>"",'Allotments list'!F2<('Allotments list'!B2+'Allotments list'!D2*365))),0,IF(or(AND('Allotments list'!F2="",'Input'!$B$1>('Allotments list'!B2+'Allotments list'!E2*365)),AND('Allotments list'!F2>('Allotments list'!B2+'Allotments list'!E2*365),'Input'!$B$1>('Allotments list'!B2+'Allotments list'!E2*365))),B2,IF('Allotments list'!F2<>"",roundup(rounddown(('Allotments list'!F2-'Allotments list'!B2)/365*IF('Input'!$B$5="Monthly",12,4))/'Allotments list'!E2/IF('Input'!$B$5="Monthly",12,4)*B2),roundup(rounddown(('Input'!$B$1-'Allotments list'!B2)/365*IF('Input'!$B$5="Monthly",12,4))/'Allotments list'!E2/IF('Input'!$B$5="Monthly",12,4)*B2)))),"")The formula above first checks whether the current date is antecedent to the allotment date or if the employee has left the company before any vesting could occur. If either of them is satisfied, it assigns 0 vested shares. Vice versa, if the leave date is not populated or the current date is posterior to the allotment date, the number of vested shares is computed according to the cliff and horizon structure. Column D shows the unvested shares as the difference between the total and vested, and the formula reads:
1=IF(A2<>"",B2/'Input'!$B$2*10000)In column H, the market value per year is calculated as the total market value of column C, divided by the vesting horizon, as follows:
1=IF(AND(A2<>"",F2>0),C2/'Allotments list'!E2,0)The Vesting per employee table groups all the allotments by employee, displaying the employee's name in the first column, and summing across allotments for each of them - thanks to the function SUMIF(). For example, column C reads:
1=SUMIFS('Vesting per allotment'!C$2:C,'Vesting per allotment'!$A$2:$A,$A5)Automation
The Backend table includes four jobs that automate the whole spreadsheet.
Cell B1 schedules a daily trigger to the TODAY() function, according to the following formula:
1=SCHEDULE('Input'!B1,"Every day 08:00")This way, every day the vesting progress is recomputed and the balances get updated.
Cell B2 generates the first column of the Vesting per employee table, using UPDATE() to write cells 'Allotments list'!A1:A to 'Vesting per employee'!A1 destination:
1=UPDATE('Allotments list'!A1:A,,'Vesting per employee'!A1)Finally, cells B3 and B4 automate the Vesting tables population through our FILL() function, which takes the formulas in the 2nd row of each table and drag them down for all the remaining records, as follows:
1=FILL('Vesting per employee'!B2:H2,COUNTA('Vesting per employee'!A2:A)-1)Embed into documents
Finally, if you are using Notion or any other internal tools that support iframes to build a report, you can embed any table or chart of this spreadsheet in your document. Just click on the ... icon in the top right corner of any table or chart and pick Embed. A modal will open, as follows:
Before embedding, you need to grant access to it. To do so, toggle on the Share privately option and then hit the Copy URL button: your element is now ready to be pasted into your favorite tool.
