A free accretion dilution model in Excel, with a complete worked deal
Funding, the purchase price allocation, five years of combined earnings per share under four conventions and a twelve-line year-one bridge: nine linked sheets, one fictional deal, no macros.
The file models one acquisition from the funding to the earnings per share. Auchendinny buys Balmacara at an enterprise value of 248,400,000, nine times its EBITDA of 27,600,000, and pays 201,200,000 at completion with 60% debt and 40% new shares. In year one the deal is 15.71% dilutive on a statutory basis, 5.71% dilutive on a cash basis, 1.21% accretive on an adjusted basis and 11.22% accretive on an adjusted cash basis. Same deal, four columns, and every one of them is a formula you can move.
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.
Accretion_Dilution_Model.xlsx (33 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.
Need the full deal structure? The LBO Model, Pro Edition adds four debt tranches, covenants, a management ratchet and 31 checks, with no circular reference and no macros. $99 → · Watch the 60-second preview
The book behind this model
This file is one worked case from Mergers and Acquisitions. 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).
LBO Model, Pro Edition: seven-year buyout model with four debt tranches, covenants, a management ratchet, 31 checks, a blank copy and an 18-page guide. $99 → · See what is inside · Watch the 5-minute demo
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
Nine sheets, in the order the calculation runs. Inputs are blue type on a pale yellow fill and sit on one sheet; everything else is a formula over them.
Read me
What the workbook does and three experiments to run first: the amortisation life, the share of the excess given to intangibles, and the tax rate.
Inputs
The target and the acquirer, the price, tax and funding, the earn-out, the synergy ramp and its cost, the valuation and allocation inputs, the four debt-like items with three independent switches each, and the lender's margin ladder.
Deal
The engine every other sheet reads: the seven prices, the funding, the interest rate solved from the margin ladder in eight explicit passes, the earn-out at fair value, the shares issued, the acquirer on its own, the premium over a discounted cash flow, the allocation and the synergy value.
Financing
Cash out at completion split into new debt and new shares at a discount to the market, the dilution, and the leverage, margin and rate the funding then prices.
Allocation
The consideration against the net assets acquired, the excess split into identified intangibles, their deferred tax liability and residual goodwill, and the annual amortisation.
Accretion
Five years of combined earnings per share under four conventions side by side: statutory, adjusted, cash and adjusted cash, plus the share of the synergy run rate year one would need to break even.
Year 1 bridge
From the acquirer's standalone earnings per share to the combined figure in twelve after-tax lines, with a closing difference that must read zero.
Charges
The eight charges the deal creates, ranked per share and as a share of standalone earnings per share, and the one charge it does not create.
Checks
Every figure the book prints set against what the workbook computes, with a PASS or FAIL on each of the 44 lines.
What the worked deal returns
Measure
Value
Announced enterprise value
248,400,000 (9.00x EBITDA)
Cash out at completion, fees included
201,200,000
New debt / equity raised
120,720,000 / 80,480,000
Shares issued, and dilution
10,153,283 (7.92%)
Leverage after the deal, and rate on all group debt
2.97x, 5.25%
Acquirer's standalone EPS / combined EPS, year one
0.417362 / 0.351781
Year one accretion, statutory / adjusted
-15.71% / 1.21%
Year one accretion, cash / adjusted cash
-5.71% / 11.22%
Statutory accretion, year two
1.26%
Synergy run rate needed for year one to break even, statutory
185.64% of the announced run rate
Read the four year-one columns together. The gap between minus 15.71% and plus 11.22% contains no error: it is the transaction fees, the integration cost and the amortisation of 64,219,653 of identified intangibles, each kept in or set aside by convention. The Charges sheet shows the eight charges the deal creates add up to 45.64% of the acquirer's own earnings per share. A press release will print one column; an investment committee should ask for all four, and for the bridge that closes year one line by line.
How to use it on your own case
Open Inputs and overwrite the blue cells: the two companies, the multiple, the funding split, the issue discount, the earn-out, the synergies and the allocation.
Read Deal before Accretion. Check that the ladder admits a solution (row 26): where it does not, the model takes the higher margin and says so.
Look at Allocation. The share of the excess given to intangibles and the amortisation life move the statutory column and leave the cash column alone.
Read all four columns on Accretion, then the Year 1 bridge. If the closing difference is not zero, a line has moved on one side and not the other.
Keep the Checks sheet: once you change inputs it stops matching the book, which is expected. Use its structure for your own tie-outs.
Questions people ask about it
Is this accretion dilution model 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. The rate depends on the leverage, the leverage on the earn-out's fair value and the fair value on the rate. The Deal sheet solves that loop in eight explicit passes across one row instead of with iterative calculation, so the file recalculates in Excel, LibreOffice or Google Sheets as it is.
Why four accretion figures for one deal?
Because the market uses four conventions. Statutory takes everything. Adjusted sets aside the transaction fees and the integration cost, which the acquirer itself calls exceptional. Cash sets aside the amortisation of the acquired intangibles. Adjusted cash does both. On this deal year one runs from -15.71% to 11.22%.
Can I use it for a real transaction?
You can reuse the structure. Auchendinny, Balmacara and every figure in the file are fictional, and nothing here is investment advice.
The rest of the files
This template is one of the companion files for Mergers and Acquisitions: the full set adds The Seven Prices (the price table, the multiples and the debt-like items), Value Created (the synergy ramp, the break-even run rate and the discount rate grid) and The Ladder (the margin ladder, the earn-out fair value and the band with no solution). Free, like this one.
Cash or stock: which should an acquirer pay with?The break-even is the same either way: synergies equal to the premium. What the method of payment changes is who wins above it and who loses below it.
Other free templates
LBO modelSources and uses to sponsor IRR, twelve sheets, every figure checked.
Real estate property modelRent roll, cash flow, debt and returns for one asset, empty and ready to fill.
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.
If this book helped — or didn’t — a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The template stays free either way.