IPO Pricing Model
Corporate Finance Financial Model (Free Excel Download)
Set an IPO offer price by comparing DCF, trading comps, precedent transactions, dilution, greenshoe effects, use of proceeds, and pro forma ownership.
professionals from Deloitte
Used by professionals from
About this model
This valuation and waterfall model computes IPO pricing, dilution, and equity distribution by building pre-IPO cap table with preferred shares, options, and common equity; modelling IPO pricing scenarios; calculating post-IPO ownership; and forecasting per-share returns for founders, employees, and early investors.
The workbook starts with pre-IPO cap table (Series A–C preferred, option pool, common equity), applies price per share across a range of IPO scenarios (conservative, base, bull case valuations). Each scenario produces post-IPO shares outstanding, ownership percentage by class, and per-share proceeds. Dilution bridges show cumulative dilution from founding through IPO (e.g., founder 25% → 18% through Series rounds → 15% post-IPO due to option pool refreshes). Proceeds allocation: company treasury injection (balance sheet strengthening), debt paydown, direct placement (existing shareholder secondary sales). Lockup expiration and secondary overhang are disclosed.
Used by companies planning IPOs, underwriters preparing IPO roadshows, venture firms modelling exit returns, and employee stock option plan (ESOP) administrators valuing liquidity events. The model reveals founder dilution impact (multi-round financings typically dilute founders 30–50% by IPO) and option pool refresh risk (new ESOP pool created at IPO often triggers immediate dilution). Per-share returns to early investors can be modelled: Series A at £0.50/share → IPO at £15/share = 30× multiple. Benchmarks: tech IPOs typically 10–15% secondary overhang post-lockup expiration; traditional companies 5–10%.
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the IPO Pricing Model
- Multi-method valuation with DCF, comps, and precedent transactions
- IPO discount and offer price sensitivity analysis
- Post-money cap table and dilution waterfall
- Greenshoe over-allotment option modelling
- Transaction cost schedule and use-of-proceeds breakdown
- Pre-IPO cap table with preferred shares, options, and common equity
- IPO pricing scenarios and resulting valuations
- Post-IPO cap table and ownership dilution by class
IPO Pricing Model: How the Template Values a Deal
This IPO pricing model template walks through the institutional workflow for setting a public offer price. It combines a 10-year DCF with an 8-peer trading-comps cross-check, runs a demand-driven bookbuild, allocates net proceeds including debt repayment, and tracks lock-up expiry.
Ideal for analysts and corporate teams evaluating deal structures.
Documented Operating Drivers of the IPO Pricing Model
The model is built for ECM analysts, buy-side allocators, corporate finance teams and LPs, with 13 interdependent sheets. Inputs flow from an Assumptions tab that houses a scenario switch plus revenue, cost, WACC and IPO drivers.
- The Income Statement produces a 10-year P&L, while Working Capital converts DSO, DIO and DPO into changes in net working capital. A Debt Schedule tracks beginning balance, IPO paydown, mandatory amortisation and interest at the pre-IPO cost of debt.
- A Lock-Up sheet splits shares across four holder classes and releases them at 180 days.
Calculation Flow From Assumptions to Offer Price
Fair value is the average of the DCF and comps outputs. Comps average three implied prices from EV/Revenue, EV/EBITDA and P/E methods across eight peers.
- The DCF discounts a 10-year unlevered free cash flow stream, defined as NOPAT plus D&A minus CapEx, change in net working capital and stock-based compensation, using a mid-year convention and a blended terminal value from Gordon Growth and exit multiple. The range mid equals fair value multiplied by one minus the effective IPO discount, with the low and high set by multipliers.
- The final offer snaps to low, mid or high based on the demand multiple relative to named thresholds.
Pull-Through of Proceeds to Capital Structure and Returns
The Use-of-Proceeds block splits net proceeds across debt repayment, working capital, R&D, M&A and general corporate purposes. Debt repayment is a direct percentage of pre-IPO net debt, which avoids a circular reference while still feeding the debt schedule's year-one paydown.
- Remaining categories must total 100% and are validated by a check. The debt schedule then computes interest on the average balance, which flows into the income statement's interest expense line.
- Returns analysis reports day-one return as the difference between blended fair value and offer price, plus one-year and three-year IRRs for IPO buyers and cornerstone investors.
Practical Use and Output Interpretation
Outputs include the final offer price, pricing outcome (top, mid or bottom of range), net and primary proceeds, market capitalisation, implied multiples, dilution and free float at IPO. The lock-up sheet shows shares released at 180 days, post-lock-up free float and overhang as a percentage of market capitalisation.
- Thirteen validation checks cover margins, offer price, proceeds, terminal value proportion, day-one return range, free float, use-of-proceeds totals, underwriting spread shares, lock-up percentages, debt paydown and DCF price. All thresholds are named ranges, and the model is fully structural, recalculating from upstream assumptions.
- The public download is a values-only preview.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is an IPO pricing model?+
It is a financial model used to determine the offer price for a company going public, typically by combining intrinsic valuation, peer multiples, and an IPO discount assumption.
What should an IPO pricing model include?+
A strong IPO pricing model should include multi-method valuation, cap table dilution, greenshoe mechanics, transaction costs, and use-of-proceeds analysis.
Who uses IPO pricing models?+
They are commonly used by investment banks, CFOs, private equity sponsors, and corporate finance teams preparing for a public listing.
How does the IPO discount work?+
The IPO discount is a reduction applied to the estimated fair value, typically 15 to 25 percent, to encourage first-day demand and a positive trading debut.
Can I customise the peer group for valuation?+
Yes. The model is designed so you can input comparable public companies and adjust the multiples used to frame the valuation range.
Have more financial modelling questions? Contact us
Related templates
Cap Table Management Model
Equity ownership tracking and scenario modeling.
Equity Portfolio Analysis
Portfolio-level analytics for equity holdings with performance attribution, risk, and rebalancing.
Exit Waterfall Model
Waterfall analysis for M&A, IPO, or sale with proceeds distributed to shareholders and securities holders.
3 Statement Model
Integrated income statement, balance sheet, and cash flow forecasts.
