Free Excel templates

A free pharmacy margin per prescription and break-even calculator in Excel

Type a generic's payment, price concession, invoice cost and rebate, a brand's payment and cost, your margin per prescription, technician and supply costs, fixed weekly costs and days open: the sheet returns the generic and brand margins, the break-even in prescriptions a day and what a cut per generic would do to it, with three checks.

The worked example is the book's fictional Part D case at calendar 2026 terms: a generic paid $18.45 less a $1.35 price concession, bought at $7.95 less a 3 percent rebate, leaves $9.39, and a brand paid $475.52 on a $468.00 cost leaves $7.52. With $10.86 of gross margin per prescription across all payers and $7.12 left after technician and supply costs, fixed costs of $4,991 a week need 116.8 prescriptions a day, and 119.6 after a cut of 25 cents on every Part D and commercial generic.

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

Pharmacy_Margin_per_Prescription_and_Break_Even_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 Independent Pharmacy 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 130 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
A generic's payment, price concession, invoice cost and rebate; a brand's payment and cost; the store's gross margin per prescription, technician pay and other variable costs per prescription, fixed weekly costs, prescriptions a day, days open, the share of Part D and commercial generics and the cut to test.
Results
The generic and brand margins in dollars and percent, the margin after variable costs, the break-even in prescriptions a day, the weekly contribution, and the break-even and weekly cost after the cut.
Checks
Three tests on negative entries, the generic share entered and the margin after variable costs, and an All checks cell.

What the worked case returns

MeasureValue
Generic: margin after concession and rebate$9.39
Generic: margin as a share of the net payment (percent)54.9
Brand: margin$7.52
Brand: margin as a share of the payment (percent)1.6
Store margin per prescription after variable costs$7.12
Break-even prescriptions a day116.8
Weekly contribution at the prescriptions entered$7,795
Break-even after the cut (prescriptions a day)119.6

Every figure is a teaching assumption of the book's fictional case, not a market norm or the rate of a real payer, wholesaler or PBM. The generic and brand lines price one Part D claim each; the break-even uses the store's margin across all payers and drugs, which is the figure to take from your own statements.

How to use it on your own case

  1. Type one generic's payment before the price concession, the concession, the invoice cost and the wholesaler's generic rebate.
  2. Type one brand's payment and invoice cost.
  3. Enter the store's gross margin per prescription from your statements, technician pay and other variable costs per prescription, fixed weekly costs net of front-end and clinical margins, prescriptions a day, days open and the share of prescriptions that are Part D and commercial generics.
  4. Read the margins, the break-even, the effect of the cut and the checks.

Questions people ask about it

How much does a pharmacy make per prescription?

It depends on the payer and the drug. In the book's case a Part D generic leaves $9.39 after the price concession and the rebate, a Part D brand $7.52 on a $468.00 cost, and the store averages $10.86 across all payers.

How do I calculate my pharmacy's break-even?

Fixed weekly costs, net of the front-end and clinical services margins, divided by the margin per prescription after technician pay, supplies, claim and card fees, then by the days open.

What does a cut of 25 cents per generic do?

A PBM's cut reaches only the generics it pays, Part D and commercial, not cash or Medicaid. Multiply the cut by their share of prescriptions to see what it takes from the average prescription; in the case it moves the break-even from about 116.8 to about 119.6 prescriptions a day.

Is this calculator 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 Independent Pharmacy 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 Independent Pharmacy Owner. See the book.