Free Excel templates

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.

Get the book on Apple Books → Or on Google Play

Want every model at once?

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).

Get the Library, $79 →

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

MeasureValue
Base deal35m shares, $700m
Primary proceeds$400m
Greenshoe5.25m shares, $105m
Market value at the offer$4.0bn
Free float with the greenshoe20.1%
Fees, with the greenshoe$44.3m
IPO discount to fair value9.1%
First-day return17%
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

  1. Enter shares, primary proceeds, secondary shares and the range on Inputs.
  2. Set the offer price and greenshoe and read the float and fees on Offering.
  3. Enter peer multiple and earnings for fair value and the discount.
  4. 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.

Open the companion files →

Worked question on the same ground: How do you calculate TERP in a rights issue?

Other free templates

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.