All You Need to Know about Pro forma Cap Table Excel Template 

An aggregate capitalization table also called a “cap table,” is a list of each security that your company has issued throughout its history. It also tells who owns those securities. And if you are wondering what “securities” mean here, you can look at them as stocks, convertible notes, warrants, shares of equity, and more. Moreover, we have shared a step-by-step guide to creating a Pro forma Cap Table Excel Template in this article for your ease. 

A cap table makes it easier to understand who owns the shares. As a result, the table also makes it easier to identify how much ownership in the company you have. In the end, the cap table helps you see how much of the company you own.

An entrepreneur should know everything about a startup—and this includes understanding the structure and mechanics of a startup’s cap table.

1. What is a Cap Table?

Consider a cap table as a report that a company prepares. This report lists the names of all of the shareholders who have taken part. Capitalization comes from the number one shareholders, i.e., those who have acquired substantial shares that the company has issued. It also indicates the total amount of ownership by this group of shareholders. The Cap table shows the total shares outstanding, the value of the stock, the price per share, and the percentage ownership of each shareholder.

The term table comes from the list of all the investors (in the case of an IPO) and indicates the name of each person, their percentage of ownership, and the value of their investment. A cap table is usually visible in the form of a list and consists of 4 to 5 columns: the first column represents the details of shareholders, while the second column shows the number of shares each investor owns. Then further details are visible in the Cap Table Example that we have shared below.  

The capitalization table shows the individual investors, the type of capital ownership, and the price of the shares. For more complex tables, information about mergers and acquisitions, public offerings, or other hypothetical transactions could become a part.

Private companies typically use capitalization tables to provide information about their investors and market value.  

2. When Should You Make a Cap Table?

Companies often make a capitalization table to indicate who’s investing in what percentage of stock and how much that stock is worth. When a company goes public, it must disclose all of this information to the SEC. Companies typically make these disclosures early on to prepare investors for their potential future earnings. A complete cap table can answer many important questions. For example,  

• Do the founders own enough shares to get a majority vote on every decision? 
• Is there any other significant investor who wants a voice? 
• What’s the risk of losing control?

The cap table is a way for companies to indicate the owners and managers of the company. It helps you to know what you are getting into. If you own stock in a company, you will be able to see who owns the company. If you are investing money in a company, you should ask yourself if you are going to want to become a co-owner. This can help you to know who has a say in the company. You might want to invest in a company if it is run by people you trust or if you believe they can make the company successful.

A cap table also lets you know if other investors want a say in the company. You can also see who owns what percent of the company. For example, if you are looking at an initial public offering, you may want to ask about the number of shares outstanding and the number of shares that the company sold to investors.

3. How to Create a Cap Table? 

Most companies use spreadsheets to create their cap table at the outset of their operations. You should design this table in a simple, organized layout that clearly shows who owns certain shares and how many remain outstanding. Typically, the Y-axis is there for listing the names of investors/owners of securities, while the X-axis comes into use to list the types of securities.

Companies can also use a spreadsheet template to add data and figures relevant to their business. The first row of the spreadsheet template should indicate the company’s number of shares. The following lines of the spreadsheet should include the following information:

Authorized Shares:

As the name suggests, authorized shares refer to the total number of shares that a company has the right to issue or is permitted to issue.

Outstanding Shares:

This is not something to do with the unpaid amount or anything. Outstanding shares are basically the shares that all the shareholders in the company hold.

Unissued Shares:

This refers to the number of shares that have not yet been issued to shareholders.

Reserved Shares for Stock Option:

These are also unissued shares but with a specific purpose. And that purpose is the reservation of shares for upcoming hirings. 

 

The capitalization table should contain a separate table that contains the following information:

Shareholders’ Names:

The names of all the shareholders of the company who have purchased shares.

Each Shareholder’s Cut:

The number of shares that each shareholder has in their hands.

Stock Options:

These are the stock options that each shareholder owns.

Fully Diluted Shares:

Any shares which are fully diluted (supports shareholders in finding the value of their shares).

Options Remaining:

The number of shares remaining that is eligible for optioning.

The founders of the company are listed first in the table. Then, executives and employees who own equity in the company are listed next, then investors such as angel investors and venture capitalists are listed. 

4. What Kind of Security Can You Cap in a Cap Table?

When creating the cap table, the types of capping securities should be clearly visible. These include convertible securities and preferred stocks.

Convertible Securities

Convertible securities have the potential of conversion into the company’s stock at a certain ratio. For example, suppose a company has $100 million worth of stock outstanding. It has issued $20 million worth of stock. The rest is outstanding as convertible stock. If the convertible stock is convertible into $10 million worth of company stock, then this is the amount of stock that each shareholder has the right to hold. This means that a company has an excess of 20 shares or $20 million of outstanding stock over its authorized shares. If this company goes public, each shareholder will receive one share of company stock for each share of convertible stock they own.

Preferred Stock

Preferred stock is another type of security that you can cap in the cap table. This type of security has special rights and privileges. For example, the preferred stock is revokable at any time, but you cannot reissue it until the company has paid off all its existing preferred shares. It also has a special dividend rate, which is the number of dividends per share paid on the preferred stock. If a company’s shares are priced at $10, and the preferred stock is priced at $15, then each shareholder gets $2 worth of preferred stock for every $10 worth of common stock outstanding.

5. How to Update Your Cap Table?

It is necessary to update the cap table with each additional round of funding. In addition, it is mandatory to update the cap table whenever any of the following situations occur:

• New shares of existing stock are issued.
• Employees’ stock options are increased or decreased
• New shares of a stock are issued.

The company also has to update the cap table whenever key shareholders exit, shares are transferred to another shareholder, and when employees retire or leave the company.

It is important for entrepreneurs and venture capitalists to regularly review a cap table to make informed decisions based on the most recent information. In addition, the cap table is the only method by which US companies can record stock ownership information. US law allows cap tables to act as a formal record of stock ownership. As a consequence, cap tables must be continuously updated to reflect any changes in ownership of stock.

6. Who Needs to See the Cap Table?

Startup founders have been discussing this question for a long time. To what extent should a startup make employee equity information public? Equity management is an essential component of business strategy. Is it advisable to make this information public? Consider these tips:

Employees’ salaries are their equity. Equity compensation is still part of the salary structure in startups, though it is not cash-based. Therefore, revealing this information would be like revealing all employees’ salaries, which would be absurd.

It is essential to inform employees how much equity they own, its current value, and the extent of preference that precedes them. As long as this information is provided, little else needs to be shared. Spreadsheets are not ideal for providing personalized access. But a cap table allows every employee to access this feature.

A select group of employees can access the startup cap tables. These include the board members, CFOs, key members of the finance team, legal team, and investors with significant information rights. To plan a secure financial future for the startup, these individuals are crucial to driving business.

The argument is that granting all employees access to the cap table creates an open and fluid environment that fits with the startup culture. However, since not all employees share the same percentage, disclosing the cap table will be counterproductive. The hiring team is the only one that understands the discretion behind granting variable equity to employees. Out-of-context disclosure of such sensitive data would harm the company.

7. The Common Cap Table Mistakes

The basic sources of every cap table problem are two: a lack of institutional control and an abundance of versions. The need to obtain your cap table from a law firm cannot ensure accurate and consistent updates to the table. In addition, it can be difficult to remember and communicate exactly which version is the most up-to-date once you have made any changes to your document. As a result of these challenges, almost all cap tables run on Excel have errors. Have a look at the most common cap table mistakes.

Entity Names that are Incorrect or Vary

You have to be precise when handling stock certificates. Usually, the same company or person is listed in cap tables under shortened or incorrect names. For example, entering both Randy Dueck and Randy W. Dueck, or entering XYZ Financials and XYZ Financials LLC, respectively. In addition, an entity’s short name or variations of its name that are not in line with its legal name may confuse, resulting in the cancellation or replacement of mislabeled stock certificates.

Conflicting Dates

Stock certificates should be issued on the same date as stakeholder options are exercised. Cap tables often leave a lag between the two dates. One of the easiest fixes is to use software that allows you to issue shares electronically.

Keeping No Track of Transactions

Multiple option grants are common among employees and investors. Therefore, it’s vital to keep track of which option is being exercised to create which certificate when issuing shares. Stock certificates must have matching option ID numbers. Otherwise, tracking transactions becomes extremely difficult. As a result, the company and employees could suffer tax consequences and improper exercise.

8. How to Do Proforma Cap Table Calculation?

Summary Sheet

This sheet of cap table will provide you with a summary of all shareholders’ equity structure holding and their number of issued shares and capital contribution. Also, this pro forma table will show the fully diluted percentage of each type of equity holder. This sheet connects with other calculation sheets. Fully diluted ownership refers to the percentage of total holding currently issued or outstanding shares that could be claimed through converting convertible notes or through the exercise of outstanding options.

Ownership Summary

This summary table breakdown the equity structure according to the number of shares and diluted ownership. In this table, a user can see each owner’s contribution to the company. This cap table template shows each investors’ equity capital stake by multiplying the number of each investor’s share by the total number of shares.

The fully diluted shares consider those shares that count as common shares and those that could be claimed through the conversion of convertible preferred stock or through the exercise of outstanding options and warrants. Diluted ownership calculates by dividing each investor or shareholder share by the total shares.

Founders Shares Ledger

The founder equity ledger is simply a list of founders and their shareholdings in the company’s stock before making any investment. When a  business establishes, the founders are usually granted common stock to reflect their ownership of the company.

The Input Assumption table will require the total number of authorized shares by the company and how much a company reserved shares for the option pool. The field of the unissued shares will automatically calculate minus the number of shares issued and reserved option shares from authorized shares.

Unissued Shares = Authorized shares-number of shares issued – Shares reserved for options

In the second table, a calculation of the common unit has been placed. Here, you can see two input columns; Amount Invested by each founder and Price per share. These inputs will let the table calculate no. of shares issued by each investor by dividing the amount invested by the price per share.

Issued Shares = Amount invested / Price per Share

In the above picture, the voting ownership is visible as a percentage. For example, one founder owns 50% and another 23%, followed by 18% and 10% of the company. The important point is that the total percentage ownership must always add up to 100%. Each founder’s ownership percentage will be calculated by dividing each founder’s holding shares by the total number of shares.

Ownership Percentage = Number of issued shares to each founder / Total number of issued shares.

 

Series Seed Preferred (SSP) Ledger

This sheet of cap table template will show you an official equity funding stage. It represents the first official money that a startup raises. The above table will reflect each investor’s contribution at the seed investment level. The only two inputs in this table are pre-money valuation and how many shares will issue in the seed round. The price per share refers to an essential variable to calculate this table. We have calculated the price per share in this cap table by dividing the pre-money company valuation price by the total number of shares issued. This price per share amount will tell us at what rate a company should issue its shares to new seed round investors.

Price per Share =Pre Money Company Valuation / Total Number of Shares Issued

By using this price per share amount, a capital contribution column calculates automatically by multiplying the number of share issues by the price per share.

Capital Contribution = Number of Shares Issued to Each Investor * Price per Share

Option Pool ledger

Startups reserve a few shares for an options pool for later issuance. However, depending on the details of the stock option plan, the stock option pool can cause significant dilution to founders later. In this Proforma Cap Table Excel Template, an Option pool ledger has four input assumptions required to fill.

1. Options Grant Date
2. Expiration Date of Options.
3. Allotted price to options.
4. No share outstanding to each candidate.

You can calculate capital contribution and dilution fields automatically by filling these inputs. The capital Contribution of each candidate will compete by multiplying no. of shares outstanding by the price per share. Therefore, the dilution column will divide each candidate’s contribution with the total contribution.

Capital Contribution = Each candidate contribution / Total Contribution

Convertible Notes

An investor usually invests in early-stage companies via a convertible note, and when a company raises the next round, investors convert that note into equity. Before converting into equity, it’s a loan, and it will accrue interest at an annual rate until it converts into equity in the future. In the above picture, we can see some input cells filled in peach color. These cells a user can change according to their requirement. Moreover,  A simple interest method calculates interest amounts. The following inputs are essential for this sheet:

Note Cap:

Note cap valuation amount refers to the amount that a founder has reserved for convertible notes in the future. Thus, the valuation cap sets the maximum price limit that the loan will convert into equity. For example, if the valuation cap limit is $15M and the new investor evaluates the company at $20M. Therefore, the convertible loan of early-stage investors will conver at a $15M valuation.

Interest Rate:

The convertible loan considers a debt mechanism. In this proforma cap table template, we used the simple interest method to calculate the interest amount.

Discount Rate:

A discount rate establishes to compensate for an investor’s additional risk by investing in a company early on. For example, if a share price is $10, a 20% discount gives an investor an $8 price per share on conversion.

Discount Price = Price Per Share *(1-Discount Rate)

Here, in the amount column, a user will input the amount which each lender has invested.

An Amount + Interest field is equal to the principal amount a lender has invested plus the interest amount up to the conversion. Hence, this field will automatically calculate when you fill in the date, interest, and amount input.

Amount + Interst = Amount + Amount * interest rate * Time

Financing Shares:

This is the number of shares that will convert the loan amount into shares at a discounted price after its maturity date.

Financing share= Amount + Interest Rate / Discounted Price 

Financed Amount :

This amount refers to an after-conversion amount an investor invested by adding the interest amount.

Financing Amount = Financing shares * Price per share 

The Bottom Line

In conclusion, the best way to understand cap tables is to practice them. If you are looking to increase revenue for your business, it’s important that you understand what happens to the money that comes into your business. The cap table helps you to visualize the cash flows, as well as the relationships between your shareholders. Understanding the relationship between the stock and the owner is very important for a successful business.