Where do you start when you need to put together your cap table? We’ll give you a template to use as a starting point. But we’ll also show you why a cap table spreadsheet will eventually lead to suicide, and what you can do to prevent it.
What is the best cap table template?
When you start a new company, you need a way to keep track of who owns what.
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 Shareworks Startup Edition.)
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.
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. But in most cases, I would say that the best template will accurately depict the company’s ownership.
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.
That’s why our template is focused on helping you accurately record your company’s ownership structure. We’ve done our best to help you easily and accurately track everything on your cap table. As far as spreadsheets go, I’m not sure they get much better than this.
So without further delay, we present our cap table template in the link below. But keep reading before you get started. Because we’ll explain how to use the template and why you probably won’t want to use any template ever, for the sake of your own sanity.
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.
But the fact is that spreadsheets, as powerful as they are, have limitations. Even if you throw in some Visual Basic programming, you’d still end up with a clunky, half-decent solution.
So why do spreadsheets fall short?
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.
Your spreadsheet, and especially the ‘summary view,’ means nothing on its own. 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. Once your cap table matures, you’ll have to keep track of exercises, cancellations, terminations, sales, transfers… the list goes on.
Putting Templates to the Test
Let’s give our cap table template a shot to see how it holds up in a few common scenarios. 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. In order to make sure that every change to the cap table is correctly accounted for, you’ll want to have a running ledger that details every transaction.
Probably the easiest way to do this is to have a ledger for each security class on a separate sheet. You’d end up with the following sheets:
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.
To see this in action, let’s suppose the founder decided to sell some of their common stock to a third party. To record that on our cap table, we’ll make two entries – one to “debit” shares from the founder, and one to “credit” shares to the buyer. Here’s what it looks like on our ledger:
And after we add a row for our new shareholder, the summary tab updates automatically:
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.
First, we create a table that outlines the vesting percentages for each schedule:
You’ll notice that I used a nonsensical starting date in the schedule. The absolute dates don’t really matter here. What’s important is that the correct percentages of vesting versus unvested shares corresponds to the right date relative to the start date.
Once the vesting schedule is in place, we can use a VLOOKUP( ) function to calculate vested shares for each option grant. We’ll just add the calculations onto our option grant ledger, and we get the following result:
Alright, that was the easy part. Now let’s see what happens when our option holder is terminated and decides to exercise their vested shares.
To do this, we need to record an exercise to our 2014 Option Plan Ledger as of 8/8/2015 for all 25,000 vested shares.Then we need to record a cancellation on the same date for all 75,000 unvested shares. Here’s what our updated option ledger looks like:
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.
We’ve actually seen this already on the common ledger when we recorded the sale. 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. You could spend hours trying to figure out why things don’t tie out.
Also notice that the exercise shows 25,000 shares in the vested column on the right. Similarly, the cancellation shows that those 75,000 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. For this, we’ll create a shareholder sheet in our template. 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. But we’ve only scratched the surface with this cap table example.
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. You need a way to keep track of this “exercise window” because the option holder will forfeit their shares if they don’t exercise before the deadline.
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.
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! Layer on multiple option plans, preferred securities with accruing dividends, plus some convertible debt, and you’ve got a giant mess.
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.
But don’t worry. 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?
While some may enjoy the challenge of building elaborate spreadsheet models, this is a waste of time for entrepreneurs. You don’t add any value to your clients or your business by spending hours on spreadsheet modeling for the cap table.
That’s why we built Shareworks Startup Edition.
With a combination of object oriented programming and relational databases, startup edition 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. Our cap table spreadsheet doesn’t really have an elegant way of linking all the information relevant to a shareholder termination. But on our solution, 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. On 4/20/2014 the shareholder exercised the vested shares into a new common certificate, and the unvested shares were cancelled.
We’re also tracking some other important details about the cancellation:
We could get our spreadsheet to track all this info, but we wouldn’t be able to link all these transactions and seamlessly bring it all together in an interactive view.
Software is just so cool.
You can also use our software to get rid of 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.
Additionally, we’ve built powerful tools that leverage your cap table information to perform powerful analyses. Things like waterfall analysis, stock expense calculations (ASC 718), round modeling and 409a valuations used to require advanced financial modeling skills and lots of time. Startup edition 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.