Multiple investing rounds Cap Table
Originally published: 07/10/2019 06:52
Last version published: 03/12/2020 07:58
Publication number: ELQ-38818-9
View all versions & Certificate

Multiple investing rounds Cap Table

Cap table for capital raise / buy in - by out and investor IRR and investment multiple forecast

The model allows to easily calculate the cap table of a business post-investment (for 5 shareholders in the current state of the mode) and for multiple rounds. (for 3 rounds in the current state of the model). The model also includes a section to calculate IRR and return on investment multiple for each of the shareholders based on an equity value at exit, assuming no dividends were paid over the investment period.

For a more advanced cap table I also built a model including a SAFE round and a Management pool creation :
For an even more advanced tool, I have built a model offering different deal structures for the conversion of the SAFE and the creation of the management pool which allows to test the dilution impact of each structure :

At first, the user needs to input the pre-money equity value of the company and to indicate the current ownership structure

Then for each round the user will input Pre-money equity value along with the investment amount of each shareholder that will go toward capital raising and the details for each shareholder of the value of shares bought from another shareholder / sold to another shareholder. The post-investment cap table will then be computed automatically.

In the second section, assuming in the current state of the model that Firm value at exit is calculated on an EBITDA multiple, the user needs to input EBITDA at exit, EBITDA multiple at exit, Debt at exit and Cash at exit. Please note that the user can easily just plug in an equity value at exit in the Equity Value at exit cell if he wants to.

Finally the user will need to input the date of each round to allow the model to calculate a IRR and an investment multiple for each of the shareholders. Please note that due to how the IRR function works in Excel, the user will need to manually adjust the IRR function for each shareholder so that the formula starts with the first round in which the shareholder has made an investment.
This could be automated using notably the OFFSET function but this would greatly complexify the formulas and in my opinion the the trade off between simplicity and automation would not be worth it.

This Best Practice includes
1 Excel

Acquire business license for $10.00

Add to cart

Add to bookmarks


Further information

Help in calculated the cap table post capital raising / buyout on multiple round and calculate expected IRR and multiples for investors after exit (assuming no dividends)

If you want to assess the new cap table in a "simple" operation of straightforward capital raising / current shareholder cash out. Can also work for LBO.

If the capital structure is more complex and has several layers of different types of equity, eg preferred shares, convertible bonds etc.

5.0 / 5 (5 votes)

please wait...