Professional capitalization table template. Full blown tracking and analysis tool

A few years back I made a capitalization table, not going to lie, it was to teach myself how to do cap tables! No one was born knowing anything- you have to learn. Fast forward to today and it’s been used by thousands of professionals and startup founders, but I was never quite happy with it. It’s still the most downloaded tool I have.

The old model is great in some regards, but in an effort to make it simple it can also be limiting. This model will always be available for free.

I have received feedback that y’all would love some new features like automating pro-ratas of investors, making it easier to add more investors and line items, so I fixed all these things and… a whole lot more.

Check out the video overview

 

One bad ass capitalization table template

A founder reached out to me to build him a bespoke and far more complicated (read ‘feature rich’) capitalization table, so I took it as an opportunity to have a horrible time, and really nerd out to make a totally new and awesome model. I literally tried to put everything in it.

This new model has pretty much everything you can possibly imagine. Rather than explain everything it does, I could just say the two things it doesn’t do:

• Participating preferred
• Anti-dilution

I honestly couldn’t get the math to work for all the scenarios (e.g. broad, narrow etc) and I haven’t found anyone that can either… These are also crappy terms, that aren’t standard, so no worries! You’re unlikely to get these terms unless things go poorly and you are in growth stage.

Everything else you can think of, I have you covered!

Get the free training course

There’s a bunch of info on the cap table at a superficial level in this blog. But I know what you want is a step by step guide.

So yay, I’ve written a sequence of 14 emails to teach you about capitalization table and how each and every sheet works. It’s totally free.

If you want to get some learnings, join up. Won’t cost a thing. There are 11,000 words and 60 images in them hills! Don’t say I don’t pull out the stops for you. You can join up right below or hit that big black box.

Get the Pro Cap Table

 

How the model works

Yes, I’ll get up a video on this soon, but let’s have a quick visual run through! There is so much under the hood, that I can’t reasonably cover it all.

The format page sets out the assumptions for drop-down menus. It also is used to explain what the assumptions mean. You shouldn’t change anything on this sheet.
There is a shareholder schedule to keep an overview of all shareholders. This acts as a dropdown menu for the other sheets so that the ‘shareholder name’ will appear in each and every other sheet..

First, input all the shareholders on this sheet and add any key details. You can use this as an aide memoir to ensure that you add in shareholder details into each sheet properly. I make sure I do this when testing the sheet and ensuring I input everything properly.

All the calculations for calculating deal pricing are kept on one sheet. You can see all the assumptions that are fed in explicitly stated.

I originally had these on other sheets, but found it was easier to have a nice consistent overview of all your transactions. If anything doesn’t look right, it’s easy to see.

We also have some checks on the page so you can see that things add up. If you look closely you will see a ‘-1’ in some of the checks- what the heck is that? Don’t worry, in transactions you don’t have perfect share counts since shares don’t round perfectly. You need to decide how to account for ‘fractionalised shares.’ This isn’t a problem, you just need to decide what you want to do, like give the extra share to a founder.

You can also see how pro-rata calculations are done. A cool thing I built in the model is the ability to press a button and automatically pro-rata investors. This is really handy if you want to run some scenarios about what dilution might look like. Normally, that would involve a load of manual inputs, which would take time to do. I’ve tried to make this pretty simple.

The calculations continue, giving you a round by round overview or who owns what by class of share on a fully-diluted basis. Everything will add up to 100%.

Every sheet, from common to preference series and warrants and options have their own sheet. They are set out nice and clear as follows.

All the ledgers present in the capitalization table sheet have a consistent format. You only need to pick their name from a drop-down list to add new staff/investors.

There are capitalization table for common (the starting sheet) up to series-I. I made sure that you will be able to cover as many rounds as possible!

If you do a series-aa, no worries, it’s just another class of share. The model allows you to have 9 series of preference share, on top of common types. That’s a lot!

Holy hell, Batman! 9 classes! That sounds complicated! Dude, you don’t need to touch anything in the future, you can only focus on a seed round if you want. The model allows you to do a whole lot of complicated stuff, but also to be as simple as possible.

Just be logical, fill in the boxes (They are yellow!) and you are cool.  You don’t need to deal with any crazy stuff like restricted shares if you don’t need to… but if you want to, you can! I did the thinking for you (and it was painful to make this simple!).

Here is an example of the series-A capitalization table. You input the name from the drop down menu and the sheet populates.

It’s the same format as the common example above, only you get two more shiny boxes!

For the next round you can insert whether an investor has ‘majority investor rights‘ and if they ‘participate‘ for their pro-rata. This basically enables you to ‘auto-pro-rata’ investors, which is handy if you want to forecast scenarios.

Here is the series-I sheet. You can see in the last three rows before the summary that the plugs have been used. If you want to run scenarios to see ownership across a range of values, the model lets you easily forecast this! Cool beans 😉

Now, you literally don’t have to touch the sheet for running scenarios. You do this from the assumption sheet, but to make it work, I added some dinky little formulas to help.

The inputs for all your fundraises are on the assumption sheet. Each round is on one line. To make them graphical, I’ve separated them into two pictures.

Don’t freak out. There’s a lot of boxes, I know! But they are there to cover all the assumptions you need.

To add a series-a round, you input the pre and post money and the date of closing. If there were convertible notes, then pick the method from the drop-down menu and the model does all the calculations for you. If you don’t have CNs, then they don’t matter!

To add an ESOP pool at a post-money level, you just type in the % you want. To add multiple liquidation preferences, you just pop them in the last column.

Individual returns

You can see the return profile of every investor by simply picking their name from a drop-down menu. Once you click the name, the chart and calculation fields all populate. This is a great means by which you can show staff what they own, or might own depending on assumptions.

Waterfall returns

To understand who will get what at an exit, for a range of values, you need to make a returns waterfall (which is no easy feat, trust me! I spent two weeks trying to find a friend in private equity who could do this and nada, till I figured it out… eventually).

The model allows you to see what happens if you accelerate options, or even remove unissued options, from the diluted shares.

It would take me a blog to explain some of the cool calcs here, but you can decide what happens to options is various scenarios, with the press of a drop down menu.

 

You can see the effect of acceleration and the option you pick.

Here you can see some of the calculations at the top of the waterfall. Yeah, this wasn’t fun to figure out!

At the end of the model, you can see how much each class of investor owns. this is split out between preference, common and total returns.  You can also see the per share ($) returns as well as the multiple of investment returned. This is mapped out by each class of share.

Ownership sheets

In the ESOP sheet, you record the options held by staff. Yes, restricted stock is issued from the ESOP… did I just scare you? A proper capitalization table is like a double entry ledger, only you probably haven’t seen that before. You can geek out like a lawyer would here. And by the way, it is easy to add in a thousand line items for all you care. I made it easy for you to do so. The simplest things are the hardest to do.

Founders and staff, as well as any other common owners of common stock, are recorded in the common ledger. Any options which exercised are moved from the ESOP to the common ledger. You deduct them from the ESOP so you know what is left outstanding. you will need to keep increasing the ESOP over time to meet staff demands (and those of investors)

Whenever you issue convertible notes, you input the key details in the convertible ledger. All you need to do is input the principal, interest, cap and discount. All the complicated calculations such as the shares issued and the effective discount rate are calculated. You may have read my blog on how to calculate convertible notes under the three scenarios? Here you don’t need to do any math at all, you just pick the flavour you want. Easy.

Warrants are not common, but you see them when warranted (I hate myself for making a pun there…). If you happen to have warrants, chuck them in the warrants sheet. Simples.

From Series-A to I you have one schedule to input your classes of shares. This is not only powerful but extensible and clean. Capitalization table can get really messy, so splitting things up makes things better. I did the thinking for you.

If you have convertible holders which convert into S-A, this is the sheet where they would convert. Of course, convertibles are way more complicated that one might think. Actually, most people don’t know that… I’ve made it simple for you for all your principal requirements.

At your Series-E you raise $20m from two investors. You add them on two lines with only a few inputs.

You want to see what happens if you did a series-H, but manually having to change every sheet is a pain! So each page has a plug linked to the assumption sheet. Easy. You didn’t even need to touch this sheet if you wanted to forecast the dilution effect… of a $160m raise! I wish the best for you 😉

Some details

Unbeknownst to many, there are three manners through which convertible notes convert. In this model, accounting for all this complexity is as easy as clicking in a drop down.

If you want investors to take their pro-rata in a deal, that can be a little hard to calculate. In this model it’s again as easy as clicking yes or no. Let’s be honest, you need to do a few calcs, but I made them for you.

The model has built-in three vesting schedules to pick from (you can build your own if you do something weird). I’ve built the three most common options (though only two are at all common).

Acceleration of options can be an important consideration to your staff. The model allows you to control what happens in a change of control. The returns waterfall lets you override the model assumptions with full and no acceleration… just in case an acquirer wants to screw your terms (which happens).

Giving investors pro-rata rights is incredibly simple.

To set your ESOP target per round is as easy as adding a percentage. All the calcs are done for you.

Yes, you HAVE to have iterative calculations turned on in tools/calculations. I hate putting this in models as it makes it unstable, but given how complicated some calculations are, and to do the heavy lifting for you, there is no way around it. I apologise for trying to make things easy for you! The devil always needs to be paid 😉

It’s easy to change who’s returns you want to see, as it’s all in a drop-down menu. Simply change the name, and the whole sheet will too. Literally, one change of a name and everything will change. Don’t burn me as a witch for the magic 😉

Get the free training course

FAQs

I really hope you will love this capitalization table template!

This model can be converted into 17 different currencies

This model uses formatted currencies in $. I often get asked if it is available in other currencies such as €? So I made an Excel PC addin to do that (It was hard!)

At the press of a button you can now convert the model into 17 different currencies. You just need to purchase the productivity addin.

Here are the currencies you can use with the macro:

Currency list	What it looks like
EUR	€
USD	$
GBP	£
YEN	¥
INR	₹
Krone	kr
BAHT	฿
RUB	₽
Peso	₱
BRL	R$
RM	RM
Rp	Rp
HKD	HK$
ILS	₪
KPW	₩
RMB	¥
CHF	CHF

Get the Pro capitalization table