Pro cap table training course

CAP TABLE #11: The ESOP sheet of the cap table

This is the 11th part of the Pro Cap Table training course, the ESOP sheet. In this series we go through the basics you need to know, then work sheet by sheet so you know how to make a seriously kick ass cap table with employee share options.

There are 14 parts:

  1. What is a cap table and other important questions
  2. Cap table dilution step by step example
  3. Cap table dilution math
  4. Starting the cap table (The drop down menus we need)
  5. Shareholders sheet
  6. Deal calculations
  7. The cap table sheet
  8. The assumptions sheet
  9. Individual shareholder returns sheet
  10. Returns waterfall calculation
  11. The ESOP sheet
  12. The Common sheet
  13. The convertible notes and warrants sheet
  14. The preference shares sheets (From Series A to I)

You can join the course and get these sent straight to your mail box here:


Let’s dig into the ESOP sheet.

I’m going to presume that you know about ESOP already. If not then I recommend you check out these:

The ESOP sheet is where all your staff’s shares are accounted for.

There are two main types of option to be aware of:

  • RS = Restricted Stock = These are effectively shares. They get issued out of the ESOP pool, So you need to make a deduction And this sheet and then add them to the common street. The most simple way to understand this to see the double entry In the example where you see it in the ESOP sheet and the common sheet as well as the adjustments and comments that have been made
  • ISO/NSO = These are American terms for options. Whatever country you are in, no biggy. You can change the names in the Format sheet to whatever you want, but be aware that you shouldn’t change the “RS” name as the model looks for that name and the formulas will throw a hissy fit. If you want to learn about these, have a read of this:… These are basically your vanilla share options and what you will likely give to staff.

The first thing you do is add the name of each staff in the names, then add the date they were granted in Grant Date. The board date is linked to the grant date. You need to have a board meeting to issue options to staff! I presume the board date is the same as the grant date. Furthermore, the vesting start date is what matters (when the clock starts) and there is a linked formula to the board date, assuming that the board date is when vesting actually starts. You want to have these dates recorded to be anal.

How many options do you grant to staff? Add these under No. Options. Pick the type of option in the dropdown. If you aren’t sure, just pick ISO and forget about it.

Finally, add the Exercise Price. This is basically the price that staff can buy those options for when they are allowed.

At the bottom, there are checks to make sure the options and RS in the common sheet add up! You need to do double entry, so these checks help you to ensure you don’t do it wrong! Yes, it took me ages to get my head around this.

Let’s look at vesting now. You have three options for the vesting schedule (explain in a moment). Pick the one you do.

Next, do you offer accelerated vesting? This is NOT common and only top execs might get it. If you aren’t sure, pick no.

Here are the three options. I recommend the first option. It means staff have to work for a year before they get shares. They then get their shares over 4 years on a monthly basis.

For Exercise, I have tried to add formulas to make this easy for you. Copy what I do for RS if you need some help. For normal options, you want to type in the actual numbers.

Exercise means that staff are turning their options into actual shares. you can see there are two examples or RS (in black) and two for ISOs (in blue).

The black RS ones are there as you immediately move the RS to common.

The blue ISO ones are because you terminate your staff. I have provided three example scenarios where:

  • Terminated, exercised options
  • Terminated, didn’t meet cliff
  • Terminated, did not exercise options

If you follow the examples you will be able to understand exactly what happens.

You will need to do some calculations to figure out exactly what staff should get at exercise.

Options expire when staff CAN exercise but choose not to. You may think this queer, but you need to pay for your options at conversion under tax rules, and you only have 30 days. A lot of times when it is not an exit, staff don’t have cash. People like Sam Altman (y-com) have been complaining about this, and it is shitty tax tule form the gov…

You will notice in this example that the RS are not cancelled in this sheet, because they haven’t been!

I do a bunch of math to figure out the exact status of your options in various sheets, so make sure you fill things in properly. You won’t be able to negotiate your ESOP pool with investors properly if this is not accurate.

Finally we can see the calculations for what is outstanding, what is vested as of today’s date, what is vested at your acquisition date (you can play with this date to see what happens) and what happens at exit with acceleration, depending on your choice from the accelerated vesting drop down menu earlier in the sheet.

The comments are used to explain what you did! I highly recommend making comment at least as brief (ideally longer) whenever you do something.

There is some to get your head around here. It’s best to play with the examples to internalise it.

Click here to continue

The Common sheet

Show off to your friends. Hit a social button to share the cap table love!

Get in the game

Free tools and resources like this shipped to you as they happen.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Comments 2

  1. The models are great. I would like to use the cap table (simple or pro version), but will need to accomodate an ESOP on a different vest schedule. Specifically, 3-yrs vest with 1-yr cliff. The calc should be straightforward, but not sure if/ how I can amend the existing formulas to accommodate. Could you please advise?

    1. Post

      Geoff –
      Cheers mate.
      I built out the normal structures.
      You can change the formulas. There’s probably a denominator which has “4” (for years) to look out for.
      I did this ages ago so I can’t remember everything. There’s probably one common formula to be changed, but can’t be sure. You’ll have to track dependencies to assumptions to see what is linked.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.