A free IPO pricing model in Excel: from the price range to the first day
Primary and secondary shares, the base deal and the greenshoe, free float and fees, fair value from peers and the IPO discount, the book, the first day and a later block trade: six sheets, no macros.
The file prices the IPO of Telvara, a fictional company with 180 million shares. The deal raises $400 million of new money and sells 15 million existing shares: a $700 million base deal of 35 million shares at $20, the top of a $17 to $20 range, plus a 15% greenshoe. Peers at 20 times 2027 earnings put fair value at $22, so the offer carries a 9.1% IPO discount. The book is covered nine times, the shares close the first day at $23.40, up 17%, and $119 million is left on the table.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
IPO_Pricing_Model.xlsx (14 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
The book behind this model
This file is one worked case from The Equity Capital Markets Banker. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Six sheets. Inputs are yellow cells with blue figures; prices in dollars per share, amounts in $ million, shares in millions.
Read Me and Inputs
Shares before the IPO, primary proceeds, secondary shares, the range, the offer price, greenshoe, gross spread, earnings, peer multiple, coverage, first-day close and the block.
Offering
New shares, base deal, greenshoe, shares outstanding, market value, free float and fees.
Pricing
Fair value from peers, the offer multiple, the IPO discount, demand, the first-day return and money left on the table.
Block
A secondary block a year later: price, size, fee and the return against the IPO price.
Checks
The book's figures against the formulas.
What the worked case returns
Measure
Value
Base deal
35m shares, $700m
Primary proceeds
$400m
Greenshoe
5.25m shares, $105m
Market value at the offer
$4.0bn
Free float with the greenshoe
20.1%
Fees, with the greenshoe
$44.3m
IPO discount to fair value
9.1%
First-day return
17%
Money left on the table
$119m
The IPO discount and the first-day pop are two different numbers. The discount is set against fair value from peers; the pop is the market's verdict on the day. Here the first-day close is still 6.4% above fair value, which is why the issuer and the selling shareholder count the $119 million differently.
How to use it on your own deal
Enter shares, primary proceeds, secondary shares and the range on Inputs.
Set the offer price and greenshoe and read the float and fees on Offering.
Enter peer multiple and earnings for fair value and the discount.
Enter the first-day close to see what was left on the table.
Questions people ask about it
Is this template really free?
Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Does it use macros or circular references?
No macros and no circular reference.
What is the IPO discount?
The gap between the offer price and the fair value the bankers derive from peers, deliberately left to attract investors. Here $20 against $22, or 9.1%.
Can I use it for a real IPO?
You can reuse the structure. The company and every figure are fictional, and nothing here is investment advice.
The rest of the files
This template is the companion workbook for The Equity Capital Markets Banker; the companion page also carries the other files that go with it. Free, like this one.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
This template comes from The Equity Capital Markets Banker. The book is on Apple Books and Google Play.
If this book helped, or didn’t, a rating where you bought it is worth more than it looks: it is what the next reader goes on. The template stays free either way.