Spreadsheets To Model Cap Tables

Question: One thing that I think would be incredibly helpful would be to have a spreadsheet showing how a cap sheet evolves under a set of financing rounds with the important variables parameterized so we can play with them.

Having spent the past 15 years trying to come up with the definitive spreadsheet model for all cases, we’ve given up. It’s really hard to create the general case and actually using it is very difficult. So we don’t have a generic one to offer.

However, there are plenty of resources for cap tables on the web. Here are some examples. Use at your own risk – we can’t guarantee the mathematical integrity of any of these.

There are many others on the web – just search around for “cap table” or “capitalization table”. I expect your favorite lawyer has one also – if he doesn’t, or won’t share it with you, I encourage you to find a new favorite lawyer.

  • Check out VC Experts Private Company Analysis Tool. It is by far the most thorough of all the web apps available.

  • Pingback: ‘Venture Deals’ is a must-have for entrepreneurs - Dave Naffziger’s Blog()

  • Pingback: Model Cap Tables With VCHub | Ask The VC()

  • David Sachs

    We’ll be launching http://www.valuative.com soon.  The product itself has been in use for several years at VC firms / by a number of entrepreneurs, and includes all of the features above plus a few others.

    – David Sachs

  • Robin

    also see https://captable.io, an online tool for managing your startup’s stock, paperlessly.

  • stevewfindlay

    At http:///www.reportally.com we’ve built one of the easiest-to-use yet comprehensive interfaces for cap table building and managing. And its completely free for startups and early stage companies…taking the pain out of excel modelling….

    • Michael Wilkinson

      Hi, Steve, something new to me and very interesting, we have a new idea where a few investors are very keen to throw in. Thing is they want some added security in the way of having the equivalent amount of shares they purchase held in escrow for a period of 24 months, I am wondering how would I express this in a simple cap table.

      • stevewfindlay

        Michael – under what scenarios would the escrow shares be issued? Just the passage of time; or would another event trigger the issue. Also, what happens if there is another funding round in the interim. Lots to think about… We should be able to cater for most, if not all of these on the http://www.reportally.com platform, depending on the final structure

        Taking the discussion up a level – why do you want to structure it in this way – (i) control or (ii) economics?

        If its Control – then you may consider better ways of covering this off in the Investment Agreement and Articles. We often create a position called an “Investor Director”; this is an independent person appointed by the non-management shareholders, and who has a casting vote on material items set out in the Investment Agreement – e.g. hiring someone with a large salary; spending capex over $y,000…

        If its Economics – then Liquidation Preferences can protect against the downside or set a minimum return (although LP has limitations). On the upside; then you just need to agree a low pre-money now.

        Lots to think about – happy to chat through…

        • Hi Steve, thanks for coming back so quickly, by the way, I have had a chance to look at your site, it is fantastic.

          we have an investor that will put up 50k toward the 500k we need, based on an idea I have come up with and the business plan supporting it. given he is in, it would appear that 8 others are good for getting us very close to the total required funding.

          so far on the table is the following offer,

          500k – one round to get us into the English speaking market.
          20% of the business to investor
          25% interest on the 500k to investor
          interest starts 13th month, on monthly basis
          as added security they are asking for 20% of the shares to be held in escrow until 25th month as added security.
          once they are paid out the 500 k, i get the 20% back to do as I please with.
          they keep their 20% of shares until diluted by new round
          I think at that this time I will be wanting a second round to go into Asia.

          hope this makes sense

          Michael Wilkinson

          • stevewfindlay

            Thanks for the positive feedback on Reportally!

            [This is not advice]

            I can’t comment on whether the deal is good or not – as that depends on your specific circumstances. However, in terms of structuring…I have one more question – you say the second 20% is in escrow until they get $500 repaid:
            – is the $500k to repaid during the life of the investment – e.g. a loan; and if they don’t get $500k back by month 25; then they get the other 20% (and if so, is it pro-rata; e.g. you pay back $250k so they only get to keep 10%?); or
            – is it just a calculation at exit – e.g. if exit doesn’t happen by month 25; or if the exit gives them less than $500k, then they get the additional 20% (and again, is it pro-rata in this case)?

            The devil is always in the detail when structuring these things…

          • the positive feed back comes from – if I hadn’t already – I would be there scenario. though I am sure there is business we can do. and thank you for your quick response and it is not taken as anything more than an idea you may have that could suit.

            the 500k is to be paid out by the 25th month, if not then the 20% stays with them, I am sure there would be room for a clause for a % or pro-rata as you say.

            The margins we show on our cash flow and P&l shows we will have the money by the 17th month, so the exit plan is to pay them out in the 25th month fully and get the 20% back, which would by another round – have us still holding our own shares and having a boast to grow.

          • stevewfindlay

            The Cap Table builder on Reportally doesn’t model the company’s cash flows – so sadly we could track how and when the loan is repaid, and the impact on the returns. but a very simple way to model this, using the tool, is to add a Cash Out round – you can then sell 20% from you to the investor on Month 25 for a nominal sum – e.g. $1. You can then add / remove this round as needs be for your modelling purposes.

            Please ask if you have any questions. And good luck with your fundraising!

  • Michael Wilkinson

    When putting out an offer, the investor has asked for shares to be held in escrow for added security, this will be until they see RoI established. So basically we are selling 20% of the company for 500k and they are wanting the security of another 20% for the period of the investment which will be 24 months. My question is how would I show this within a Cap Table.