Free Excel templates

A free ESOP transaction model in Excel, with a full worked case

A 100% leveraged ESOP buyout from valuation to debt-free: sources and uses, seller note and warrants, the internal loan and the 25% test, a ten-year cash waterfall, a stress case, the repurchase schedule and the seller's after-tax comparison. Seven sheets, no macros.

The file follows Carrow Fastener Supply, a fictional distributor sold 100% to its ESOP for $32.76 million, or $27.30 a share. A $15.00 million bank loan, $1.16 million of company cash and a $19.50 million seller note pay for it. The day after closing the shares are worth $0.05; with no federal income tax as an S corporation, the debt is gone by 2034 and the value reaches $51.75 a share at the end of 2036.

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 free models.

ESOP_Transaction_Model_Carrow.xlsx (27 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 the worked case of The ESOP Handbook. The book explains every assumption and every sheet: why the trust may not pay for synergies, how Section 1042 defers the founder's gain, why the internal loan runs thirty years, and how the repurchase obligation grows after the debt is gone.

DRM-free PDF, $19.99 → · Paperback, $32.99, shipped worldwide

What is in the file

Seven sheets, in the order the calculation runs. Blue type on a yellow fill is an input; black type is a formula.

Inputs
Company, valuation, financing, internal loan, participants, seller, stress and the 2026 limits of IRS Notice 2025-67.
Transaction
Enterprise value, equity price, sources and uses, cash to the seller at closing ($13.26 million), warrant shares, leverage at closing (5.39x) and the value per share after closing.
Internal Loan
The level payment of $2.01 million a year that releases 40,000 shares, tested each year against 25% of covered payroll, and the highest-paid participant's annual addition against the 415(c) limit.
Projection
Ten years of cash waterfall: cash available for debt service, senior and seller note payments, the cash sweep, coverage (1.26x in 2027), debt to EBITDA, equity and value per share after warrant dilution.
Repurchase
Shares allocated, departures by cause, vested shares bought back and a matrix of installments by year of departure and year of payment.
Seller
The strategic sale after tax ($27.11 million) against the ESOP sale without Section 1042 ($25.25 million) and with full deferral ($32.76 million kept invested).
Checks
Sources equal uses, the 25% test every year, the 415(c) test, minimum cash, debt repaid, no error value, and a line that compares the outputs with the figures printed in the book.

Three things to try

Apply the stress. Set the EBITDA shortfall to 15%: coverage falls to 1.04x in 2027, the equity is below zero at the end of that year, and the seller note is repaid only at maturity. The bank is still paid on schedule, because the seller note bends.

Shorten the internal loan. Set it to 15 years: the contribution needed exceeds 25% of covered payroll in the first year. That is why internal loans usually run far longer than the bank's.

Raise the multiple. Set it to 6.0x: the price and the seller note rise, and first-year coverage falls. Find the price at which coverage drops below the level your lender would accept.

Questions

Why is the share value almost zero after closing?

The trust paid $32.76 million with borrowed money and the company carries $34.50 million of acquisition debt, so the equity right after closing is worth $0.06 million. It rebuilds as the debt is repaid: $12.02 at the end of 2029 and $51.75 at the end of 2036 in the base case.

Does it use macros or circular references?

No. Repurchase installments sit in a triangular matrix that reads only earlier years, so the file recalculates without iterative calculation.

Can I use it for a real company?

You can reuse the structure and replace every input. Carrow and every figure in it are fictional, and nothing here is legal, tax or investment advice.

Related reading