CapTable Calculations (I): Priced round investors & ESOP pool

DISCLAIMER: The information and methods presented in this article are not hard-and-fast rules for CapTable calculations. These are suggestions based on the Svested team’s decades of experience assisting hundreds of companies with their CapTable calculations.

Introduction

Having been acquainted with the basics of CapTable management, as well as key terminologies, it is now time to delve deeper into the realm of CapTables.  

‍

In the 5th article of our CapTable series, we demonstrate numerically how priced round investors and ESOP pools are incorporated onto a company’s CapTable.  At the same time, we reveal the typical issues encountered while performing CapTable calculations on spreadsheet software (specifically Microsoft Excel), and provide tips on how to ensure your calculations are accurate.  

‍

Do also keep your eyes peeled for our 6th article next week, which will be a calculation deep-dive specifically for noteholders, and where we will share how to ensure accurate calculations for this type of investor.

‍

Calculations for priced round investors and ESOP pools

CapTable calculations carried out for new priced round investors, as well as the company’s ESOP pool, are the simplest, and involve “working backwards” using the total number of shares held by existing stakeholders.  We begin our illustration with the following set-up:

‍

Company X has just completed its Seed Round, and its current equity structure is as follows, and is also shown in its CapTable below:

‍

‍

• It has 2 founders, who collectively invested $1,500 and hold 1,500,000 shares
• It also has 2 Seed investors (Investors C and D), who respectively invested $120,000 and $80,000.  
• At the same time, the Seed investors had requested that Company X to set aside a 10% ESOP pool.  

‍

Now, Company X is looking to fundraise in a Series A round.  The details for this new round are as follows:

• Company X’s pre-money valuation is $4,000,000
• The company has confirmed $800,000 in investment from 2 Series A (priced round) investors (Investors E and F), making its post-money valuation $4,800,000
• Between the 2 Series A investors, Investor E invested $500,000, while Investor F invested $300,000
• The Series A investors have requested to expand the ESOP pool to 12% 

‍

Company X’s final post-Series A CapTable will be as follows:

‍

‍

Let’s now dive into how we attain the CapTable shown above. 

‍

To update the company CapTable, we have to calculate the number of shares, price per share, and percentage shareholdings for the new investors (E and F).  At the same time, we must calculate the updated number of ESOP shares for the company, as well as the new percentage shareholdings of the existing stakeholders.

‍

With the round details given above, we start by calculating the percentage shareholdings of the new priced round investors.  The formula is simple: % shareholdings = Invested amount / Post-money valuation

‍

We derive that Investor E will come to own 10.42% of the company ($800,000 / $4,800,000), while Investor F instead owns 6.25% of the company ($500,000 / $4,800,000).  On top of this, we know that the new ESOP pool takes up 12% of all company shares.  Notice that because the percentage shareholdings for Investors E and F are already calculated on a post-money basis, they will remain unchanged.

‍

Next, we tally up the percentage shareholdings of the new investors, as well as the updated ESOP pool – a quick addition gives us 28.67% (10.4167% + 6.25% + 12%), which also means that the company’s founders and existing investors will be left with only 71.33% (1 – 28.67%).  That is, the total number of shares held by the founders and existing investors (1,840,909 shares) will now only make up 71.33% of the total company shares.

‍

This forms our anchor point, which will be crucial for subsequent calculations.  Because we know that in the Series A round, there is no change in the number of shares held by the founders and Seed investors, we can use their total quantity of shares as a reference to calculate the relevant new values.  Notice that the 1,840,909 shares stated in the previous paragraph EXCLUDES the incoming investor and the ESOP pool – this is because most of the time, the ESOP pool will change, and hence it cannot be used as an anchor.

‍

With this background information in place, we can perform a calculation to find the new (fully diluted) total number of shares, after the Series A investment is completed.  A simple division gives us 2,580,714 (= 1,840,909 / 0.7133) as the new (fully diluted) total number of shares .

‍

With this number calculated, we can finally calculate the number of shares for each investor and the ESOP pool.  To do this, simply take each entity’s calculated percentage shareholdings from above, and multiply it by the total number of new shares.  Investor E will end up with 268,824 shares (= 10.4167% ⨉ 2,580,714), Investor F ends up with 161,295 shares (= 6.25% ⨉ 2,580,714), and the ESOP pool will contain 309,686 shares (= 12% ⨉ 2,580,714). 

‍

Following this, it is easy to calculate the price per share for the priced round investors, which is found by taking Investor E and Investor F’s invested amounts, and dividing them by the quantity of shares they receive. After doing so, we derive a price of $1.86 per share ($500,000 / 268,824 OR $300,000 / 161,295). This also acts as a check and balance because the price per shares MUST be the SAME.

‍

Special Note

Do take note that when calculating the number of ESOP shares, to prevent breaching any agreed limits, we always round down our calculations to the nearest whole number.  A 1 or 2-share difference may be insignificant but will result in a percentage shareholding difference that determines whether the share agreement may be breached.  Consider the following example: 

‍

Rounding up the number of shares: 12.000001% > 12% (Breaching Limit)

Round down the number of shares: 11.999999% < 12% (Within Limit)

‍

Another interesting point to note in relation to the ESOP pool is that even though in this example, the ESOP pool has been expanded, there may be cases where incoming investors wish for the ESOP pool to be maintained or reduced (less likely).

‍

Summary

The diagram below is a compilation of Company X’s CapTables by fundraising round, which tracks the CapTable’s evolution, starting from the company’s incorporation round.  

‍

‍

Notice the dilution effects of each subsequent round of fundraising: Founder A and B, who started off with 66.67% and 33.33% shareholdings respectively, end up with 38.75% and 19.37% after the completion of Company X’s Series A – they are diluted twice.  A similar situation occurs for Investors C and D, who get diluted once.  On average, the dilution to existing shareholders per priced round will be around 20% +2% (top-up for ESOP).

‍

Circular Reference issue

A problem typically faced when doing manual spreadsheet calculations, is the Circular Reference error.  This error occurs when the calculation of values for 1 cell directly or indirectly references back to the same cell.  

‍

For instance, in the image below, the sample calculation for Investor C (Cell C37) involved multiplying Cell D53 (10.416667%) and D59 (2,580,714). The latter cell was in turn calculated by taking Cell C40 (1,840,909) and dividing it by D57 (71.33%). It all seems alright, until you realise that Cell C40 is a sum of cells C33 to C39. At the end of this entire loop of cell references, we have inadvertently included Cell C37’s value in a calculation for its own value, causing the error.

‍

‍

This illustrates a key problem with using Microsoft Excel for CapTable calculations: within the web of formulae and numbers, these hard-to-detect errors will frequently surface, rendering calculations not only inefficient, but possibly inaccurate as well.

‍

There is no particularly easy way around Circular References. One way would be to perform a Copy and Paste Special (Values only), so that the cell no longer contains a formula. Another possibility would be to manually enable circular references. To do this, in your Excel workbook, click “File” at the top left corner of the page, followed by “Options”, then “Formulas”. At this page, you can check the “Enable iterative calculation” box, selecting the maximum number of iterations, as well as the maximum change that will occur. However, this is not advisable, as it may affect all other calculations on the spreadsheet.

‍

Ensuring accurate calculations: Priced round investors & ESOP pool

After calculating the values for the respective shareholders, as well as the ESOP pool, it is vital to check that these numbers are correct and agreed on by all existing and incoming shareholders.  

‍

The most important number to check is the percentage shareholdings for each investor upon completion. Not only should the sum of all percentage shareholdings add up to 100% on the dot, but the percentage shareholdings for each investor in the CapTable should be exactly the same as your own intermediate calculations. To ensure this, make sure that in your final CapTable, you should expand the number of decimal places for the percentage shareholdings to at least 5 to 8 decimal places. This is to eliminate as much rounding error as possible. An example would as follows, in the post-completion CapTable:

‍

‍

Do take note of the decimal places, as we are dealing with large numbers – for instance, when dealing with hundreds of millions of shares or dollars, even a 0.001% difference would be significant, in terms of absolute numbers or dollar value.  

‍

However, these decimal places are, again, used for the intermediate CapTable calculations, and for checking.  When the CapTable is presented to investors, it is fine to keep these figures to 1 or 2 decimal places, so that the CapTable looks neater.

‍

Conclusion

In Svested, we try to keep things simple as we approach CapTable Calculations.  When adding priced round investors to a CapTable, along with an updated ESOP pool, we use the “work backwards” approach, whereby we first calculate the final percentage shareholdings of the incoming investors and/or ESOP pool.  We then subtract this value from 100%, deriving the combined percentage shareholdings of the company’s existing shareholders.  This will serve as a reference or anchor point for calculating subsequent key values, such as the new total number of shares, how many shares each investor and/or the ESOP pool receives, as well as the price per share.

‍

In terms of ensuring accurate CapTable calculations, it is important to double check the final percentage shareholdings of the latest priced round investors, the price per share, as well as the ESOP pool, and get approval from existing and incoming shareholders prior to finalising the completion documents. 

‍