Free Excel templates

A free buy-sell agreement calculator in Excel: the Connelly estate tax gap of four structures, the heirs' proceeds and the survivors' basis

Company value, the deceased owner's percentage, other assets, the exclusion and the rate, the cover and the price in; for four structures (as signed, entity redemption at the appraised price, a price including the proceeds, cross-purchase) the estate tax value of the shares, the gap with the price, the estate tax, what the heirs keep and the survivors' value and basis out: one sheet of inputs, live formulas and four checks.

The worked case, a fictional founder with 55 percent of a company worth $20,000,000, is paid $11,000,000 for his shares. With company-owned cover, his shares count at $17,050,000 for estate tax and the estate owes $4,228,000; in a cross-purchase they count at the price and the tax is $1,808,000. The difference is $2,420,000.

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

Buy_Sell_Structure_Calculator.xlsx (12 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 model goes with The Buy-Sell Agreement Handbook, which explains the method behind it, chapter by chapter.

Get the PDF edition → See the book

Want every model at once?

The Financial Model Library: all 165 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

3 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.

Inputs
Equity value before insurance; the deceased owner's percentage, other assets, the exclusion and the estate tax rate; the cover and the price as signed; the appraised price; the survivors' basis and rate; premiums and Section 101(j) inputs.
Estate
Four structures line by line: cover, price, company value counted, estate tax value of the shares, gap, note, taxable estate, estate tax, heirs' proceeds, survivors' value, basis and later tax; the comparison and the Section 101(j) case.
Checks
Four tests on the percentage, the rate, the exclusion and equity value, and the covers and prices, and an All checks cell.

What the worked case returns

MeasureValue
As signed: estate tax value of the shares$12,650,000
As signed: estate tax$2,468,000
Entity redemption: estate tax value of the shares$17,050,000
Entity redemption: value taxed above the price$6,050,000
Entity redemption: estate tax$4,228,000
Cover needed for a price including the proceeds$24,444,444
Cross-purchase: estate tax$1,808,000
Cross-purchase: survivors' basis$12,500,000
Extra estate tax of the redemption$2,420,000
Survivors' later tax saved by the cross-purchase basis$2,618,000

Every figure is a teaching assumption of the book's fictional case, not a market norm or a quote. Whether an agreement price controls the estate tax value, whether a block takes a discount, and whether moving policies falls within the transfer-for-value exceptions are questions for counsel. Not legal, tax, valuation or insurance advice.

How to use it on your own case

  1. Type the company's equity value before any insurance proceeds and the owner's percentage.
  2. Enter the owner's other assets, the basic exclusion amount for the year of death and the estate tax rate.
  3. Enter the cover and the price of the current agreement, and the appraised price.
  4. Read the estate tax of each structure, what the heirs keep and the survivors' basis, then the checks.

Questions people ask about it

What did Connelly v. United States decide?

On 6 June 2024 the Supreme Court held that a company's obligation to redeem a deceased owner's shares does not offset the life insurance proceeds it receives, so the proceeds count in the company's value for estate tax.

Does a cross-purchase avoid the Connelly problem?

Generally yes for the valuation point: the proceeds belong to the surviving owners, not the company, so they do not raise the company's value for the deceased owner's estate.

Does the gap matter if the estate is below the exclusion?

Only if the extra value pushes it over. Run the calculator with the owner's real figures and the exclusion for the year.

Is this template really free?

Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Go further

The free workbooks of The Buy-Sell Agreement Handbook go further: the full companion set, blank models and the worked cases of the book.

See the book and its free files →

Other free templates

This template goes with The Buy-Sell Agreement Handbook. See the book.