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.
