Free Excel templates

A free self-funding cost calculator in Excel: expected cost, maximum cost and break-even against your renewal

Expected retained claims, enrollment by tier, the administrator, network, PBM and adviser fees, specific stop-loss rates, the PCORI fee, the aggregate attachment and the renewal premium in; the expected cost, the maximum cost, the break-even, four possible years and a level-funded quote out: one sheet of inputs, live formulas and five checks.

The worked case, a fictional manufacturer with 400 enrolled employees, faces a renewal of $7,174,689. Fixed costs come to $913,452; with an aggregate attachment of $7,082,645, self-funding is expected to cost $6,579,567 and can cost at most $7,996,096. It breaks even at $6,261,237 of retained claims.

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

Self_Funding_Cost_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 Self-Funded Health 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 155 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
Expected retained claims; enrollment by tier and covered lives; PEPM fees for the administrator, network, PBM, adviser and aggregate stop-loss; specific stop-loss rates by tier; the PCORI fee; the aggregate attachment share; the renewal premium; four scenario ratios; the level-funded PEPM, claims fund and refund share.
Budget
Fixed costs line by line, the aggregate attachment point, the expected and maximum cost, the saving and the worst case against the renewal, the break-even, four scenarios and the level-funded cost in each.
Checks
Five tests on the attachment share, the fees, the scenario ratios, the refund share and the level-funded claims fund, and an All checks cell.

What the worked case returns

MeasureValue
Fixed costs$913,452
Specific stop-loss premium$505,920
Aggregate attachment point$7,082,645
Expected total cost$6,579,567
Maximum cost$7,996,096
Expected saving against the renewal$595,122
Maximum cost above the renewal$821,407
Break-even retained claims$6,261,237
Adverse year (115 percent): self-funded cost$7,429,485
Catastrophic year (140 percent): self-funded cost$7,996,096
Level-funded cost, expected year$6,953,958

Every figure is a teaching assumption of the book's fictional case, not a market norm or a quote. Stop-loss terms (contract basis, lasers, aggregate maximum), state minimum attachment points and the treatment of participant contributions decide whether the ceiling holds: check them with the carrier and counsel. Not legal, tax, actuarial or benefits advice.

How to use it on your own case

  1. Type your expected retained claims (claims capped at the specific deductible, trended, plus any laser) and your enrollment by tier.
  2. Enter the fees quoted per employee per month and the specific stop-loss rates by tier.
  3. Enter the aggregate attachment share and the renewal premium, and read the expected cost, the maximum cost and the break-even.
  4. Adjust the four scenario ratios and the level-funded terms, and read the checks.

Questions people ask about it

What is the maximum cost of a self-funded health plan?

Fixed costs plus the aggregate stop-loss attachment point, provided the policy terms hold: covered expenses that match the plan, a contract basis that covers late-paid claims, lasers counted toward the aggregate and an aggregate maximum high enough.

How is the aggregate attachment point set?

As a percentage of expected retained claims, often 120 or 125 percent, built from monthly aggregate factors times enrollment. Some states set a minimum attachment; the NAIC model act uses 110 percent for groups of 51 or more.

Is a level-funded plan the same as self-funded?

Usually yes in law: the employer sponsors a self-funded plan with a tight aggregate attachment, pays a level monthly amount and shares any surplus. It costs more than self-funding in ordinary years and less in bad ones.

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 Self-Funded Health 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 Self-Funded Health Plan Handbook. See the book.