Free Excel templates

A free deferred compensation calculator in Excel: the edge of deferring against a taxable account, the break-even tax rate and the default risk

Amount, return, years, tax rates today and at withdrawal, the tax drag of a taxable account and the recovery in a default in; the after-tax result of each route, the edge, the withdrawal rate and the probability of default at which it disappears, installments and the former state's tax, Medicare at vesting and the cost of a Section 409A failure out: one sheet of inputs, live formulas and five checks.

The worked case defers $100,000 for ten years at 6 percent: $103,869 after tax in the plan against $88,126 in a taxable account, an edge of $15,743 that is lost above a probability of default of 21.7%.

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

NQDC_Deferral_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 Nonqualified Deferred Compensation 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 192 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
Amount, return, years, tax rates, the drag of a taxable account and the recovery in a default; a balance, installments and the former state's rate; a supplemental balance at vesting and Medicare rates; vested credits for the failure example.
Arithmetic
Deferral against a taxable and a buy-and-hold account, the break-even rate and probability of default, installments and state tax, Medicare at vesting and in a missed year, and the cost of a Section 409A failure.
Checks
Five tests on tax rates, the return, years and installments, the recovery and amounts, and an All checks cell.

What the worked case returns

MeasureValue
Value in the plan after tax at withdrawal$103,869
Taken as pay and invested in a taxable account$88,126
Edge of deferral$15,743
Withdrawal rate at which the edge disappears50.8%
Probability of default above which deferral loses21.7%
Buy-and-hold account instead$90,659
Each installment of the balance at separation$251,368
Former state's tax on a lump sum$92,505
Extra Medicare if the vesting year were missed$3,706
Section 409A failure: additional tax of 20 percent$91,167

Every figure is a teaching assumption of the book's fictional case, not a market norm or a quote. Whether a plan complies with Section 409A and whether a payout schedule escapes a former state's tax are questions for counsel and a tax adviser. Not legal, tax or investment advice.

How to use it on your own case

  1. Type the amount you might defer, the return and the years until payment.
  2. Enter your marginal rate today, the rate you expect at withdrawal and how a taxable account would be taxed.
  3. Enter the share you would expect to recover if the company did not pay.
  4. Read the edge, the break-even tax rate and the break-even probability of default, then the checks.

Questions people ask about it

Is it worth deferring compensation?

On the book's assumptions, $100,000 deferred for ten years beats a taxable account by about 16 percent of the amount, and loses on expected value if the company's probability of default over the period is above about one in five.

What tax rate makes deferral a bad idea?

In the example the edge disappears only at a withdrawal rate of about 51 percent, well above today's top combined rates; the larger risk is the employer's credit.

Does the calculator handle state taxes?

It shows a former state's tax on a lump sum and on installments; whether a schedule is protected by federal law is a question for a tax adviser.

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 Nonqualified Deferred Compensation 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 Nonqualified Deferred Compensation Handbook. See the book.