Startup stock option vesting schedule excel

Where do you start when you need to put together your cap table? The natural place to start is a spreadsheet. Seasoned entrepreneurs might start from scratch, but most founders begin with some sort of cap table template.

However, More and more people are defaulting to an online platform like Capshare. You can find plenty of templates out there. Some are good for modeling a new round, others are better for analyzing exit scenarios, while others are simply meant to show the basic ownership structure of the company. The best template for you might depend on your objective. This is important because everything you might want to do with a cap table template is completely dependent on you accurately tracking ownership.

Because if you have the ownership correctly modeled out, you can build all sorts of analyses on top of it. If the ownership is wrong, however, you will run into all kinds of issues. Employees might receive stock grants with incorrect share counts. You might send a flawed cap table to prospective investors.

You might even raise money based on faulty dilution analysis and end up getting very little or even nothing when your startup exits. In all these scenarios you look bad and somebody is going to end up with the wrong number of shares. Not a good situation. So without further delay, we present our cap table template in the link below. But keep reading before you get started.

Capshare Cap Table Template. After working in the venture capital and valuation industries for most of our careers, you would think that we could use our mad spreadsheet skills to make a killer cap table template.

Error (Forbidden)

But the fact is that spreadsheets, as powerful as they are, have limitations. For one, the cap table that most people think of — a spreadsheet list of shareholders, securities and share counts — is not actually a cap table. This spreadsheet is really just a representation of transactions between the company and its shareholders. Thinking that your spreadsheet is your entire cap table is a lot like thinking that your bank statement is your entire bank account.

Your bank has to make sure they also keep track of your actual cash and assets, not just updating a paper bank statement. In the same way, you have to be sure you keep track of your entire cap table including signatures, vesting schedules, stock certificates, etc , not just the spreadsheet and summary view.

A real cap table has to keep track of every transaction between your company and all of its shareholders. These transactions are recorded in legal documents like stock purchase agreements, notices of option grants, repurchase agreements, etc.

In addition, you have to keep track of vesting schedules, paper certificates, shareholder agreements, and so forth. Keeping track of all this and keeping it synced up in a spreadsheet is an administrative nightmare. And this is only part of the equation. Things will get more complicated as employees do things like exercise their options or leave the company.

Suppose we have the following cap table with one investor, one founder, and one option holder:. This is only a summary of all the information that needs to be recorded.

Probably the easiest way to do this is to have a ledger for each security class on a separate sheet. With these sheets in place, you can use the SUMIF equation on the summary tab to dynamically keep a running share total by shareholder and security. For example, in the Series A column you would sum the shares column of the Series A ledger if the shareholder name matches.

Pretty good so far. This gives a really good framework to create a paper trail for every change to the cap table. But now we need figure out how to track vesting for our option grants. Vesting gets a little tricky. The number of shares vested changes depending on the date, so our spreadsheet needs to be dynamic.

Calculating vested shares on any given day is important, because we need to know how many shares are exercisable for employees if they get terminated or the company gets acquired. Once the vesting schedule is in place, we can use a VLOOKUP function to calculate vested shares for each option grant.

Alright, that was the easy part. Then we need to record a cancellation on the same date for all 75, unvested shares. This is where things start getting harder.

Notice that there was a new certificate created CS-3 when the option was exercised into common. This transaction needs to have a corresponding entry on the common ledger in order for everything to tie out. This is a major source of headaches in cap table spreadsheets. You have related entries in multiple places throughout. And if you change information in one place but forget to change any corresponding entries, you create errors.

Also notice that the exercise shows 25, shares in the vested column on the right. Similarly, the cancellation shows that those 75, cancelled shares were unvested. This is an important distinction when you have early exercisable options, because they will exercise into restricted common stock and continue vesting. When this happens, you also need to have vested and unvested calculations on your common ledger.

In order to freeze my vesting calculations on the option grant that was just exercised, I now need to hard code the vested and unvested amounts for the issuance. I could probably lookup the termination date on another table and factor it into the equation, but that becomes a pretty nasty formula. At any rate, those vested and unvested columns are using inconsistent formulas, and they convey different information depending on the transaction type.

Columns like that are highly prone to errors. Lastly, we should probably have a place to show the status of the shareholder that was terminated. We can also use this sheet to store other shareholder info like email address, mailing address, etc.

That was a lot of work for just one option grant. We made several simplifying assumptions. For instance, terminated option holders generally have the right to exercise for a limited time after departure.

This time period can vary by shareholder. Also, not every employee has the same vesting schedule. Even if most employees have 3- or 4-year cliff vesting, most companies end up giving at least one employee a unique vesting plan due to milestones, leaves of absence, etc.

So you will likely end up creating customized schedules.

Vesting | Wolverine Startup Law

Finally, sometimes there are long chains of activity involving a single option grant. In our example, we demonstrated the exercise of an option grant.

But what if the option holder exercised into restricted stock, vested their shares over time, transferred some shares, then the new owner sold some shares. Good luck keeping track of that in a spreadsheet.

Our simple example has just a few shareholders. Now, imagine a cap table with dozens or hundreds of option grants! And what happens if you need to go back in time to see what the cap table looked like on a specific date? Luckily you have all the info you need for that analysis, but it will require a lot of manual work. Starting to get the picture?

As your cap table gets bigger, your spreadsheet gets harder to maintain. Adding a new round of funding or a new option plan becomes a huge chore. You might have thought that I was joking about losing your sanity.

I may be exaggerating a little, but just ask anyone who has tried to manage a moderately sized cap table. They will tell you that their spreadsheet turned into a nightmare. No matter how good of a template you start off with, your cap table spreadsheet will end up as a patchwork of duct tape and bubble gum.

With the power and possibilities of modern of web applications, why would entrepreneurs waste time manually building out complex cap table spreadsheets? While some may enjoy the challenge of building elaborate spreadsheet models, this is a waste of time for entrepreneurs. With a combination of object oriented programming and relational databases, Capshare blows Excel out of the water.

All the issues we ran into above — duplicate entries, out of sync information, mundane manual work — have been solved. Take terminations for example. But on Capshare, we can easily show what happened to an option grant when the shareholder was terminated:. In the image above, we can see that shares vested until the date of termination. You can also use Capshare to get rid paper certificates that frequently get lost and out of sync.

Issue electronic certificates instead, or ditch certificates entirely and just invite your shareholders to view their equity online.

Dave Naffziger’s Blog » Startup Stock Options: Vesting Schedules & Acceleration

Things like waterfall analysis, stock expense calculations ASC , round modeling and a valuations used to require advanced financial modeling skills and lots of time. Capshare has reduced these complex exercises to a few trivial mouse clicks.

The point is that cap table templates and spreadsheets are obsolete. The web has enabled a new and better way to manage equity. Resources Equity Guide for Founders DIY A Tool How to Choose an Equity Solution About Contact Us More Capshare Home Capshare Pricing Request a Demo.

The Best Cap Table Template Out There And Why It Still Sucks by Todd Miller Oct 13, Cap Tables. What is the best cap table template? When you start a new company, you need a way to keep track of who owns what.

So what type of template is best? Accurately Tracking Ownership is More Important than You May Realize The best template for you might depend on your objective. Capshare Cap Table Template Why even the best cap table template is hopelessly inadequate After working in the venture capital and valuation industries for most of our careers, you would think that we could use our mad spreadsheet skills to make a killer cap table template.

So why do spreadsheets fall short?

Forbidden

Suppose we have the following cap table with one investor, one founder, and one option holder: And after we add a row for our new shareholder, the summary tab updates automatically: First, we create a table that outlines the vesting percentages for each schedule: There is a better way.

Maintain your cap table and your sanity with software With the power and possibilities of modern of web applications, why would entrepreneurs waste time manually building out complex cap table spreadsheets? But on Capshare, we can easily show what happened to an option grant when the shareholder was terminated: Software is just so cool. Better than any cap table template Capshare is software for managing and modeling your cap table. Keep everything accurate, model financing rounds, manage shareholder transactions and compare outcomes without complex spreadsheets.

Recent Posts A Process: Best Practices and What to Expect Startup Ideas: