Courier: Per-Stop Route Pay

Excel Formulas › Courier & Delivery

All versions

Courier pay almost never comes from a single number. There is a base for showing up, a rate for each stop completed, and mileage on top. Build the three pieces into one formula and settlement stops being a debate.

Quick formula: Base plus stops plus miles, in one line:

=B2+C2*D2+E2*F2

A $40 base, 12 stops at $3.25, and 46 miles at $0.55 settles at $104.30.

Functions used (tap for the full reference guide):

SUM PRODUCT

The example

Three routes from one day, each with its own stop count and mileage.

	A	B	C	D	E	F	G
1	Route	Base	Stops	Per stop	Miles	Per mile	Pay
2	Route A	40	12	3.25	46	0.55	$104.30
3	Route B	40	20	3.25	62	0.55	$139.10
4	Route C	40	7	3.25	28	0.55	$78.15

The formula

Two products and a base, added together:

=B2+C2*D2+E2*F2 // base + (stops x per-stop) + (miles x per-mile)

How it works

Each term answers a different question:

B2 is the route base — what the driver earns for taking the route at all.
C2*D2 pays for productivity: 12 stops at $3.25 is $39.00.
E2*F2 covers the vehicle: 46 miles at $0.55 is $25.30.
Excel does the multiplication before the addition, so no parentheses are needed — though they do not hurt readability.

Keep the two rate columns as cells rather than typing 3.25 into the formula; a rate change then takes one edit instead of twenty.

Try it: interactive demo

Interactive

Enter the route base, stop count, and mileage.

Route base ($)

Stops

Per stop ($)

Miles

Per mile ($)

Variations

Guarantee a route floor

Protect drivers on a light day by paying the higher of the calculation and a guaranteed minimum.

=MAX(B2+C2*D2+E2*F2,H2)

Effective pay per stop

Divide total pay by stops to compare routes on equal footing.

=(B2+C2*D2+E2*F2)/C2

Pitfalls & errors

Pay attempted stops and completed stops differently, or drivers will argue every undeliverable package. Split them into two columns with two rates.

If mileage reimbursement is meant to be tax-free, keep it in its own column so it does not get mixed into taxable earnings on the settlement.

Practice workbook

📊

Download the free Courier: Per-Stop Route Pay practice workbook

Edit the yellow base, stop, and mile cells; route pay recalculates.

Frequently asked questions

Should the base be paid when a route is cancelled?

That is a contract question, not a formula one — but if you do pay it, put a Cancelled flag in a column and let an IF zero out the stop and mile terms instead of deleting the row.

Do I use IRS mileage rates?

For employee reimbursement, many companies do. For contractor settlements the per-mile rate is whatever the agreement says. Either way it lives in a cell you can update.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: SUM PRODUCT