Free Excel templates

A free direct primary care membership and break-even calculator in Excel

Enter your members by age tier, contract members, monthly fees, the employer's rate, unpaid fees, new members and attrition, labs and medicines, supplies, card processing, the practice's fixed costs with each physician at market pay, the loan payment, physicians and capacity, and read revenue and contribution per panel member, the break-even panel before and after debt service, the fill rate and the months to the break-even.

With the worked case's inputs, each panel member brings in $98.30 a month and leaves $88.28 after labs and medicines at cost, supplies and card processing; the practice needs 734 panel members to cover $64,770 of fixed costs a month, both physicians' market pay included, 763 once the loan payment is added, against a capacity of 1,500 members and a fill rate of 71.2%.

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

DPC_Membership_BreakEven_Calculator.xlsx (15 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 Direct Primary Care Practice Owner, 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 213 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

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

Inputs
Members by age tier, contract members, fees, the employer's rate, unpaid share, enrollment fee, new members, attrition, labs and medicines, supplies, card processing, fixed costs, loan payment, physicians and capacity.
PerMember
Panel, fees billed and collected, labs and medicines, variable costs, revenue and contribution per panel member.
BreakEven
Fixed costs, break-even before and after debt service, capacity, fill rate and EBITDA a month.
Growth
Departures, net members added a month and the months to the break-even.
Checks
Rates as shares with attrition above zero, contribution above zero, panel within capacity and a break-even the growth can reach.

What the worked case returns

MeasureValue
Panel (active paying members plus contract members)1,068
Revenue per panel member a month$98.30
Contribution per panel member a month$88.28
Fixed costs a month$64,770
Break-even of EBITDA, panel members734
Break-even after debt service, panel members763
Panel capacity of the practice1,500
Fill rate of the panel71.2%
Net members added a month30
Months to reach the break-even at this growth0
EBITDA a month at this panel$29,513

The figures come from a fictional practice and are teaching assumptions, not benchmarks, legal or tax advice or a clinical guide. The break-even is one of EBITDA, after each physician's market pay with burden as a fixed cost (it leaves out enrollment fees, depreciation, interest and loan payments); a separate line adds the monthly loan payment. The practice's own revenue only: no insurer, Medicare or Medicaid payment. Each rate has its own base: the unpaid share on individual fees billed, attrition on active paying members at the start of the month, card processing on payments collected by card from members; the months to the break-even are a smooth estimate, not a forecast.

How to use it on your own case

  1. Type your active paying members by age tier, your contract members and the three monthly fees on Inputs, with the employer's rate and the unpaid share.
  2. Type the enrollment fee, new members a month and monthly attrition, then labs and medicines at posted prices and at cost, supplies and the card processing rate.
  3. Type the eight fixed lines of a month (each physician at market pay, with burden), the loan payment, the physicians and the capacity per physician.
  4. Read the member on PerMember, the break-even on BreakEven and the growth on Growth; keep every line of Checks at OK.

Questions people ask about it

How many patients does a direct primary care practice need?

Fixed costs a month over the contribution each panel member leaves, rounded up: 734 panel members in the worked case, 763 once the loan payment is added. The calculator rebuilds it from your own inputs.

What is revenue per panel member?

Membership fees collected plus labs and medicines sold, over the panel: $98.30 a month in the worked case. Enrollment fees, paid once, are left out.

What is the contribution per panel member?

Revenue less labs and medicines at cost, supplies and card processing, over the panel: $88.28 a month in the worked case. It is before fixed costs and the loan payment.

Does the break-even include the physicians' pay?

Yes. Each physician's market pay with burden is a fixed cost every month, so the break-even of 734 members is after it, never a value of a physician's work.

Go further

The free workbooks of The Direct Primary Care Practice Owner 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 Direct Primary Care Practice Owner. See the book.