Free Excel templates

A free 340B savings calculator in Excel

Gross savings against WAC and against the GPO price, product line by product line, then the Medicaid discount, the contract pharmacy fees, a manufacturer restriction and a rebate float, down to net savings by channel.

Four product lines, two dispensed in-house and two through contract pharmacies, save $354,000, $399,840, $805,500 and $538,200 gross against WAC. After the Medicaid discount passed to the state, the dispensing and TPA fees, a restriction that removes 25 percent of contract pharmacy claims and the cost of an 8-day rebate float, the total is $1,587,199.92. The calculator lays the chain out for up to ten lines, ready for your own figures.

Get the calculator

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other free models.

340B_Savings_Calculator.xlsx (13 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 calculator

This calculator is a simplified version of the companion workbook of The 340B Program Handbook, which follows one disproportionate share hospital through a full 340B year: eligible and ineligible sites, the patient definition, three purchase accounts and an accumulator, a carve-in and a carve-out, four contract pharmacies and their TPA, the bridge from potential to net savings, a manufacturer restriction, a rebate scenario and a self-audit.

Paperback PDF edition, $19.99

What is in the file

Four tabs. Inputs are in blue; everything else is a formula; the Checks tab counts the failures. Empty input rows stay blank without errors, so you can use one line or ten.

1. Start here
What the calculator does, the conventions it applies, what it leaves out, and a note that the example prices, fees and rates are teaching assumptions, not market figures.
2. Inputs
Up to ten product lines: the channel (in-house or contract pharmacy), the units, the 340B price, the GPO price and WAC, the Medicaid fee-for-service share and the carve-in or carve-out choice, the reimbursement per claim and the units per claim; then the dispensing fee and the TPA fee, per claim or in percent, the share of contract pharmacy claims lost to a restriction, the rebate float in days and the cost of capital.
3. Calculator
Gross savings against WAC and against the GPO price for each line, the Medicaid discount passed to the state at carve-in, the net benefit per contract pharmacy claim, the restriction effect, the net savings of each channel, the cost of the rebate float and the total.
4. Checks
The controls of the calculator, each showing a gap that must read 0, and a count of the failures.

What the worked example returns

MeasureValue
Line 1, in-house: gross savings against WAC / against GPO$354,000 / $276,000
Line 1: Medicaid discount passed to the state (carve-in)$53,100
Line 2, in-house: gross savings against WAC / against GPO$399,840 / $354,240
Line 2: Medicaid discount passed to the state (carve-in)$39,984
Line 3, contract pharmacy: gross savings against WAC / against GPO$805,500 / $666,000
Line 3: net benefit per claim / restriction effect$933 / $195,300
Line 4, contract pharmacy: gross savings against WAC / against GPO$538,200 / $469,200
Line 4: net benefit per claim / restriction effect$160.40 / $114,300
Net savings in-house, after the Medicaid discount$660,756
Net savings at contract pharmacies, after dispensing and TPA fees$1,238,400
Rebate float: cash need / cost of capital$29,450.96 / $2,356.08
Total, after restrictions and the float$1,587,199.92

The example is fictional and every input is a teaching assumption, not a market figure. Line 1, in-house: 1,200 units at a 340B price of $310, a GPO price of $540 and a WAC of $605, 15 percent Medicaid fee-for-service. Line 2, in-house: 4,800 units at $14.20, $88 and $97.50, 10 percent Medicaid. Line 3, contract pharmacy: 900 units at $720, $1,460 and $1,615, reimbursed $1,680 per claim. Line 4, contract pharmacy: 3,000 units at $19.60, $176 and $199, reimbursed $207 per claim. One unit per claim, a dispensing fee of $16 and a TPA fee of $11 per claim, 25 percent of contract pharmacy claims lost to a restriction, an 8-day rebate float on the contract pharmacy lines and an 8 percent cost of capital. Every check of the control block reads 0.

Read the conventions with the figures. The in-house net is gross savings against WAC less the Medicaid discount passed to the state. The contract pharmacy net is gross savings against WAC less the dispensing and TPA fees on every claim. The restriction effect is the claims lost times the discount per claim less the fees no longer paid on them, and it comes off the total. The cash need of the float is the contract pharmacy gross savings times the float days over 365, and its cost is that need times the cost of capital. The calculator is separate from the book's case: read its figures against each other, not against the book's hospital.

How to use it on your own program

  1. Enter one product line per row, with the channel it is dispensed through; keep inpatient units and outpatient units that fail the patient definition out.
  2. Enter the three prices in force for the period: the 340B price you paid, the GPO price and WAC.
  3. Enter the Medicaid fee-for-service share and the site's choice on the Medicaid Exclusion File; for carve-out lines, leave the Medicaid units out of the 340B units.
  4. For contract pharmacy lines, enter the reimbursement and the units per claim, then the fees as your agreements set them.
  5. Read the Checks tab before any figure: a count above zero means a cell to fix.

Conventions explained in the book with the rules as read in November 2026 (42 U.S.C. 256b, 42 CFR 10.10, HRSA's Medicaid Exclusion File and contract pharmacy guidance). Education only, not legal advice: the facts of your own program govern, and the program's counsel decides.

Questions people ask

What does the 340B savings calculator do?

For up to ten product lines it computes gross savings against WAC and against the GPO price, the Medicaid discount passed to the state at carve-in sites, the net benefit per claim at contract pharmacies, the effect of a manufacturer restriction, the net savings of each channel, the cost of a rebate float and the total, with a block of checks that must read zero.

Which rules does it apply?

None of its own: it applies arithmetic conventions. Gross savings are the reference price less the 340B price times the 340B units; the Medicaid discount passes to the state where a carve-in site is paid at acquisition cost; the net benefit of a claim is reimbursement less 340B cost and fees. The rules behind them, as read in November 2026, are explained in the book and in the articles linked below.

What does it leave out?

Eligibility and OPAIS registration, the patient definition, the accumulator and replenishment, state Medicaid payment rules, Medicaid managed care, Medicare payment for 340B drugs, and the self-audit. The book's full workbook covers most of them on one hospital.

Can I rely on it for a report to the board or an auditor?

No. It is built for a first pass and for learning the mechanics. A savings report needs the program's own dispensations, prices and contracts, and the conclusions belong to the program's managers and counsel.

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 workbook of The 340B Program Handbook goes further: four sites with their OPAIS dates, the patient definition tested row by row, three purchase accounts and an accumulator, four contract pharmacies with fees per claim and in percent, a bridge from potential to net savings by channel, a rebate scenario, a self-audit with its extrapolation, and sensitivities run one at a time.

See the book and its free files →

Related reading