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 |
Productivity addin
More than likely, you aren’t a huge Excel nerd like me, so you probably don’t know about macros? These are hacks to make modeling in Excel much more faster and less painful. I love them!
I’ve been making models for years, so you can be sure I know what is useful and what is not. I decided to not just make the currency conversion macro, but add in all my most used hacks. You just hit a button and it will do handy things.
What can you do?
- Automatically format your cells so they have ‘investment banking’ standard black, blue and green
- Change the zoom and cell position of every sheet so it looks great when others read it
- Save a backup of your model (v1, v2, v3) at the push of a button so you never get a corrupt model
- Add error checks to your formulas
- And a whole bunch of other nerdy things you’ll understand when you are making models yourself!
NOTE:
- This is for PC. Gates hates Mac so…. All the plugins other than ‘check sheet sizes’ work in Mac, but I don’t warrant anything if you are on Mac (Sorry). I’m trying to find a developer to rewrite the code for Mac so the images, tips etc work too
- Currency conversion only works on 50Folds models. It won’t on any other model (My code looks for certain cells… There is a hack- ask if you want to know)
If you want it, you can get the productivity Excel addin here.
Get the Pro capitalization table
Comments 18
the box says ii is to purchase the pitch deck template. Where is the cap sheet stuff? Pricing, etc.
Author
Thanks for letting me know Steve. It all works. I just forgot to change the part of the code which says ‘pitch deck’ to cap table.
I have just bought the $100 cap table. Our convertible note, from an accelerator, defines a set amount of ownership (8%) after the A round – there is no cap/discount rate. How does your model account for this, how do I input the date?
Author
Hi Seth- That’s not normal. Frankly, 8% after the A round is taking the piss!
Well now, it effectively acts like a CN since there is no ownership.
At series A you would be calculating the ownership on a ‘Percentage ownership’ dilution basis, whereby I presume the founders take all the dilution of the note.
For simple purposes, you could treat them the same as the A investors so your pre is fixed but the amount of dilution is higher. So if you sell 20% it looks like you sold 28%.
derr bollocks
Author
Lol. Thanks
Hi Alexander, we are an Australian based company and all our numbers are AUD / A$ will this work for us? do we just use the USD$ or will that not work? I see you have BRL/R$ so am thinking a straight swap may not work
Author
Just leave as Dollars. Same crap, you just AUD. You can either add in headers it’s AUD, tell people it is, or just let Aussie investors assume the model you are giving them is in local which is sort of logical.
The macro just does superficial formatting. It doesn’t change the whole thing to some FX rate. That doesn’t make sense. Just pick a currency and use it.
I have a blog about that: https://www.alexanderjarvis.com/use-dollars-in-your-startup-especially-for-your-financial-model/
Alexander we use google sheets will your template still work if we upload and convert into a google sheet are will key things break?
Author
I do nothing in GDocs unless collaboration is the key. I have an OKR tool for that.
I have never tested. I just would never do a model in GDocs.
Hi Alex, Thank you for your leadership on this impressive work. My quick comment on this, at this stage in the game, I feel engagement and collaboration are keys to success, transparency, and effectiveness. Something like the financial model still requires collaboration between Exec Leadership Team, Board, investors. Is it safe to assume this and the Enterprise Fundraising Model would not work in Google Sheets if purchased? Thank you and happy holidays, Best, Mustafa
Interested in learning how cap tables are built and the various scenarios, events, rights, preferences etc.
Author
Urmi – Basics of the model are here: https://www.alexanderjarvis.com/what-is-a-cap-table-and-other-important-questions/
I don’t have bogs on stuff like events and rights atm. There are a lot of blogs which have this content. Don’t know if it is worthwhile me focusing on this or not?
Thank you very much for this information, how can I get your old cap table model, you mention that it is free, but I don’t know where I can see it, I really appreciate your help. best regards
Author
Under free tools/ dilution. Have too many so had to start grouping.
https://www.alexanderjarvis.com/ultimate-startup-cap-table-and-return-analysis-template/
Is there a way to modify this to account for a complex LLC waterfall and take into considerations built in gains, 704c allocations, hurdles, profit interests etc.
Author
Seth- Sure. It’s an open excel file. If you know how to do all that cool, it’s just a lot of modeling to add in those features, right? If you edit pls share with me so I can add that for everyone to benefit!
How much would it cost to have you convert our existing cap table to the premium version format >100 investors, multiple classes of stock, convertible debt. Doing a recap now with a down round. Happy to talk on phone.