Free Excel templates

A free cash balance plan calculator in Excel: the cost against a 401(k) alone, the tax saved, who pays for whose credit and the Section 415 ceiling

Practice income, owners' pay, credits and ownership, staff pay and rates, fees, tax rates and the 2026 limit in; the cost of the design, the owners' share, the tax saved, the salary differential that shares the cost fairly, each owner's take-home change, the deferral edge, the largest lump sum and the yearly 415 test out: one sheet of inputs, live formulas and four checks.

The worked case, a fictional two-dentist practice, adds a cash balance plan to a 401(k) plan that tops out at $72,000 per owner. The design costs $537,540 a year, $403,040 more than the 401(k) alone, and saves $169,277 of tax; the owners receive 87.3% of the employer contributions.

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

Cash_Balance_Plan_Calculator.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 model goes with The Cash Balance Plan 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 186 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
Practice income and tax rates; for each owner, ownership, plan pay, the 401(k) contribution today and the cash balance credit; staff pay and rates; fees; the 415 limit, annuity factor, interest credit, years of participation and the older owner's credit for each year.
Contributions
The 401(k) plan alone against the cash balance design: cost, owners' share, tax saved, the combined deduction limit if old contributions were kept, who pays for whose credit, take-home changes, the deferral edge, the 415 ceiling and the yearly 415 test.
Checks
Four tests on ownerships, tax rates, credits and pay, and the interest credit, and an All checks cell.

What the worked case returns

MeasureValue
Employer cost, 401(k) plan alone$134,500
Employer cost with the cash balance plan, a full year$537,540
Share of the practice's income28.3%
Owners' share of employer contributions87.3%
Cost above the 401(k) plan alone$403,040
Tax saved on it$169,277
Part of one owner's credit funded by the other$43,680
Salary differential that moves it back$72,800
Older owner: take-home change a year$-171,102
Older owner: largest lump sum at 62$2,192,400
Highest share of the 415 limit used at any year end99.9%

Every figure is a teaching assumption of the book's fictional case, not a market norm or a quote. Whether a design passes the nondiscrimination tests, whether a plan is exempt from the PBGC and whether a salary differential is reasonable compensation are questions for the plan's actuary and counsel. Not legal, tax, actuarial or investment advice.

How to use it on your own case

  1. Type the practice's income before owners' pay and the owners' marginal rate.
  2. Enter each owner's ownership, plan pay, current 401(k) employer contribution and proposed cash balance credit.
  3. Enter staff pay, the staff rates the actuary proposes and the fees.
  4. Read the cost, the tax saved, each owner's take-home change and the 415 test, then the checks.

Questions people ask about it

How much can I put into a cash balance plan?

It depends on age, pay and years of participation before retirement: the plan may not provide more than an annual benefit of $290,000 at 62 in 2026 ($300,000 in 2027), phased in over ten years of participation and tested at every year end.

Why does the 401(k) profit sharing drop when a cash balance plan is added?

In a plan exempt from the PBGC, employer 401(k) contributions above 6 percent of pay bring in the combined deduction limit of Section 404(a)(7), which can make part of the contributions nondeductible.

Is the staff cost in the calculator the real one?

It is the rate you type. The book's screen gives 9.1 percent for its case and budgets 10.9 percent; the plan's actuary sets the real figure.

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 Cash Balance Plan 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 Cash Balance Plan Handbook. See the book.