The files you need Copy link
Each quarterly PFS release is a ZIP bundle from CMS. You need the national relative value file, which holds the RVUs, status and payment-policy indicators for every code, and the GPCI file, which holds three indexes per locality. The conversion factor is a column in the relative value file. To start from a service ZIP you also need the ZIP-to-locality crosswalk, a separate CMS download. The bundle also carries an OPPS cap file for certain imaging codes and the anesthesia conversion factors.
Import codes as text Copy link
Anesthesia codes start with zeros, and a spreadsheet that reads them as numbers turns 00100 into 100. That breaks every lookup on those rows. It has happened in CMS’s own files: the 2020 Q4 and 2022 Q3 relative value files list anesthesia codes without their leading zeros. Set the code column to text before importing, and pad any four-digit numeric code back to five.
Look columns up by name Copy link
CMS changes column order, and sometimes column names, between years. A VLOOKUP with a fixed column number silently reads the wrong field after a layout change. Use XLOOKUP or INDEX/MATCH against the header text, and check that each header exists before you trust the result.
The formula Copy link
Amount = (work RVU × work GPCI + PE RVU × PE GPCI + MP RVU × MP GPCI) × conversion factor. The PE RVU comes from the office (non-facility) column for services performed in an office and from the facility column for services in a hospital or ambulatory surgical center; facility vs non-facility explains why they differ. National amounts use 1.000 for all three GPCIs. The PFS formula walks through it with real numbers.
Check the status before you calculate Copy link
A blank RVU on a contractor-priced (status C) row computes as $0 in a spreadsheet, which is wrong: there is no national amount, not a zero one. Bundled codes can carry RVUs that are never paid. Wrap the formula in a check on the status column that returns the amount only for payable statuses (A and T, and R when RVUs are published) and returns text such as “contractor priced” or “bundled” for everything else.
Adjustments the base formula leaves out Copy link
The formula gives the fee schedule amount for one service. A claim can call for more:
| Adjustment | Rule |
|---|---|
| Multiple procedures | Highest-ranked procedure at 100%, the next four at 50% for indicator 2 |
| Bilateral (-50) | 150% for indicator 1 |
| Assistant at surgery | 16% (80, 81, 82) or 13.6% (AS) |
| Co-surgeons (-62) | 62.5% each |
| NP, PA or CNS under own NPI | 85% of the physician amount |
| OPPS cap | Lower of the PFS amount and the capped amount for listed imaging codes |
| Non-participating | 95%; limiting charge 109.25% of the participating amount |
| Sequestration | About 2%, taken from Medicare’s payment after everything else; keep it in its own column |
Label every amount with its release Copy link
Put the release name and its dates in the sheet header. A workbook built in January answers questions in October unless someone rebuilds it, and the first quarter of 2024 had two releases with different conversion factors.
The starter workbook Copy link
Our starter workbook is built from the current release with all of the above in place: a calculator sheet with the formulas visible, the RVU and GPCI tables with codes stored as text, and a constants sheet with each value’s source. It is free with an account: get the starter workbook.
Or skip the spreadsheet Copy link
The rate calculator prices any code for a date of service, ZIP, setting and modifier, with the CMS file rows behind each input. A free account keeps code lists and saved calculations in a workspace, and the API returns the same amounts to your own systems.
Excel formula builder Copy link
Name the columns in your RVU sheet and the cells that hold your locality’s GPCIs and the conversion factor. The formula checks the status first, so contractor-priced and bundled codes return text instead of $0. Use the office (non-facility) or facility PE RVU column to match the setting. Fill the formula down the rows.
=IF(OR(C2="A",C2="T",AND(C2="R",N(D2)+N(E2)+N(F2)>0)),ROUND((D2*$J$2+E2*$K$2+F2*$L$2)*$M$2,2),"No national PFS amount (status "&C2&")")
Gives the participating fee schedule amount before sequestration. Multiple-procedure, bilateral, assistant and NP/PA adjustments are separate steps.
Frequently asked Copy link
Is there a Medicare fee schedule in Excel format?
CMS publishes the relative value and GPCI files each quarter in a ZIP bundle of CSV and text files, which open in Excel. They hold RVUs, not dollar amounts; you calculate the amounts with the PFS formula.
How do I calculate a Medicare rate in Excel?
Multiply each RVU by its locality GPCI, add the three products, and multiply by the conversion factor: (work RVU × work GPCI + PE RVU × PE GPCI + MP RVU × MP GPCI) × conversion factor. Use the facility or non-facility PE RVU to match the setting.
Why does my spreadsheet show $0 for some codes?
Contractor-priced and other non-payable codes have blank RVUs, which a formula reads as zero. Check the status indicator first and return a text outcome for those codes instead of an amount.
Sources Copy link
- CMS Medicare Physician Fee Schedule Relative Value Files
- CMS Geographic Practice Cost Index (GPCI) files
- CMS ZIP Code to Carrier Locality File (cms.gov)
- CMS Physician Fee Schedule overview (cms.gov)
Written from primary CMS sources—see how we source, compute, and verify everything on this site.
Did this answer your question about Building a Medicare Fee Schedule in Excel?
Tell us what you were trying to work out and we'll answer you or build the page that does.