Free Excel templates

A free NEMT cost per trip and break-even calculator in Excel

A fleet's fixed costs turned into the cost of a busy vehicle hour, the floor price of a contract trip and the cost of each trip type, and a plan of trips turned into a break-even in trips a month. One sheet of inputs, live formulas and three checks.

The file opens on the book's fictional NEMT company and its price build of September 2025. Ten vehicles with one driver each at 6.6 trips a day, six days a week: 20,592.0 trips and 14,837 busy hours a year out of 21,840 vehicle hours on shift, a utilization of 67.9 percent. Fixed costs of $847,920, the hourly drivers included, make $57.15 a busy vehicle hour and a floor of $41.48 for a dialysis contract trip of 0.55 busy hours and 11 miles, so a contract rate of $41. At the plan's mix a trip leaves $44.93 and the plan breaks even at 1,572.5 trips a month against 1,716.0 planned.

Get the calculator

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

NEMT_Cost_per_Trip_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 calculator

This file is drawn from workbooks 1 and 2 of The NEMT Business Owner, which follows one fictional NEMT company through two fiscal years: Medicaid broker trips at the broker's rates, a dialysis contract priced from the cost of a busy hour, four new wheelchair vans and a used stretcher van, drivers moved from pay by the trip to an hourly wage, break-even in trips a month and a letter of intent from a competitor.

DRM-free PDF, $19.99 → · Paperback, $32.99, shipped worldwide

What is in the file

Three sheets. Blue type on a yellow fill is an input; black type is a formula, protected against overtyping with no password.

Inputs
Vehicles, driver hours a week, weeks, trips a vehicle a day and days a week; for each of five trip types (ambulatory broker trip, wheelchair broker trip, dialysis contract trip, facility trip, private pay trip) its share of trips, busy hours, miles, fuel and maintenance a mile and revenue a trip; card fees on private pay; vehicle payment, insurance and registration a vehicle a month and other fixed costs a month; driver pay by the hour (inside the fixed costs) or by the completed trip, with the payroll burden; a no-show allowance and a target margin; and the four totals of a closed year for its break-even.
Calculator
Trips a year, busy hours and utilization, fixed costs, the cost per busy vehicle hour and per vehicle hour on shift; the floor price and contract rate of a dialysis contract trip; for each trip type its variable cost, contribution, full cost and margin after full cost ($38.70 of contribution for an ambulatory broker trip against $59.54 for a wheelchair broker trip in the case); the contribution per trip at the plan's mix, break-even trips a month and the owner's income above break-even; and the break-even of the closed year (1,529.4 trips a month against 1,845.3 in the case's FY2026).
Checks
The trip mix adds up to 100 percent, busy hours do not exceed vehicle hours, and the price divisor stays above zero, with an All checks cell.

Three things to try

Raise the target margin to 18 percent. The cost per busy vehicle hour does not move; the floor of the dialysis contract trip goes from $41.48 to $44.67, and the contract rate the floor calls for from $41 to $45. Only the divisor changes.

Change the mix. Move five points of trips from ambulatory broker trips to wheelchair broker trips. The contribution per trip at the plan's mix rises from $44.93 to $45.98 and break-even falls from 1,572.5 to 1,536.9 trips a month, with the same vehicles and the same fixed costs, provided the wheelchair trips are there to be run.

Lower the trips a vehicle a day to 6.0. Fewer busy hours carry the same fixed costs: the cost per busy vehicle hour climbs from $57.15 to $62.87, the floor of a contract trip to $45.22, and the plan of 1,560.0 trips a month falls short of its break-even of 1,572.5.

Questions

Why divide by busy hours and not hours on shift?

Because a vehicle earns only on a trip. A cost spread over every hour on shift assumes every hour carries a passenger, and every price built on it is too low by exactly the share spent waiting between trips.

Why is there a no-show allowance in the price?

In the case the broker pays nothing for a no-show, yet the vehicle still drives to the pickup. A no-show is never billed; the allowance simply sets part of the price of the trips that do run against the cost of the ones that do not. Your broker contract and your state's rules decide what a no-show is and how it is recorded.

Related reading