Cellwise Start free

Excel guide · 7 min read

Cost per mile formula in Excel

Quick answerCost per mile = total costs / total miles for the same period: =SUM(costs)/SUM(miles). Take miles from odometer readings (end minus start), include fixed costs (payment, insurance, permits) and variable costs (driver pay, fuel, maintenance, tires), and for several trucks or months sum costs and miles separately with SUMIFS before you divide.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1MonthTruckOdo startOdo endMilesLoaded miDriver payFuelMaintenanceTiresFixedTotal cost
2Sep 1, 2026T141235042218098308460609556904200336015565
3Sep 1, 2026T228704029641593757980581355606400336015373
4Oct 1, 2026T14221804329051072593506650624018600336018110
5Oct 1, 2026T229641530702010605914065756180380620336017115
6Nov 1, 2026T143290544161087057390539751053101240336015412
7Nov 1, 2026T23070205980532024500336017110
8Nov 1, 2026T315822015822000004780033608140
9

Download this sheet as an Excel practice file (.xlsx)

The basic formula: one truck, one month

The sheet is a three-month log for a small fleet, one row per truck per month. Miles in E come from the odometer, =IF(OR(C2="",D2=""),"",D2-C2), so a missing reading leaves the cell blank instead of inventing a number. Driver pay, fuel, maintenance and tires (G to J) are what was actually paid that month. Fixed costs come from the input block: truck payment in O1, insurance in O2 and permits and plates in O3 (a yearly cost, divided by 12), 3,360 per truck per month in O4. L adds the row up.

T1 in September cost 15,565 and drove 9,830 miles. Divide the two and format the result as currency with three decimals: a tenth of a cent per mile adds up to real money over 100,000 miles a year.

The same formula works for a personal car or a work van. Put a year of costs on top (insurance, registration, an amount for depreciation, fuel, servicing) and the year's miles underneath.

=L2/E2

Result: 1.5834181078 shows as $1.583 with Currency format

$1.583 per mile for T1 in September: 15,565 of costs over 9,830 miles.

Fixed vs variable cost per mile

Fixed costs are paid whether the truck moves or not: the payment, insurance, permits. Variable costs grow with every mile: driver pay per mile, fuel, maintenance, tires. Split them, because they behave differently. Variable cost per mile stays roughly flat; fixed cost per mile falls as miles go up.

On the sheet, T1's 3,360 of fixed costs is =K4/E4, $0.313 a mile in October (10,725 miles) but =K6/E6, $0.386 in November (8,705 miles). Nothing got more expensive; the truck just drove less. That is why a slow month always looks worse per mile, and why a quote based on one busy month is too low.

The formula below splits the fleet's rate for the months with complete readings (the D2:D8,"<>" part is explained under missing readings). Fixed per mile plus variable per mile is the total, $1.822.

=HSTACK(SUMIFS(K2:K8,D2:D8,"<>")/SUM(E2:E8),(SUMIFS(L2:L8,D2:D8,"<>")-SUMIFS(K2:K8,D2:D8,"<>"))/SUM(E2:E8))

Result: spills 1 row × 2 columns

0.409423233141.4125710804

0.409 fixed and 1.413 variable: $0.41 + $1.41 = $1.82 per mile across the fleet.

Missing odometer readings and trucks that didn't move

T2 has no November closing reading (D7 is empty): the driver's log never came in. Its November costs, 17,110, are still in column L. Divide all costs by all miles and those costs count with none of their miles: =SUM(L2:L8)/SUM(E2:E8) says $2.169, about 35 cents too high.

Best fix: get the reading (the next month's start reading, the ELD or the fuel card's odometer entry). Until then, leave the row out of both the top and the bottom. SUMIFS with D2:D8,"<>" keeps only rows that have a closing reading. =SUMPRODUCT(ISNUMBER(E2:E8)*L2:L8)/SUM(E2:E8) gives the same answer by testing the miles column instead.

T3 is the opposite case: in the shop all November, 0 miles but 8,140 of costs. Those costs are real and belong in the fleet total, which is why the formula keeps T3. Per truck, though, =L8/E8 is #DIV/0!. Guard it with =IF(E8=0,"no miles",L8/E8) rather than IFERROR, which would also hide a typo or a text value.

=SUMIFS(L2:L8,D2:D8,"<>")/SUM(E2:E8)

Result: 1.8219943136 shows as $1.822 with Currency format

$1.822 per mile: 89,715 of costs over 49,240 miles, with T2's unread November left out of both.

Cost per mile per truck with SUMIFS

For one truck, sum its costs and its miles with SUMIFS and divide: =SUMIFS(L2:L8,B2:B8,"T1")/SUMIFS(E2:E8,B2:B8,"T1") gives $1.678 over three months. Do the same for T2 without the reading filter and you get $2.482, because November's 17,110 is on top with no November miles under it. With the filter it is $1.626.

In Excel 365 one formula lists every truck. UNIQUE builds the list, SUMIFS takes the list as its criteria so it returns one total per truck, and IF handles the truck with no miles.

Don't average the monthly rates to get a truck's rate. A month with few miles has a high rate and would count as much as a full month. Sum, then divide.

=LET(t,UNIQUE(B2:B8),c,SUMIFS(L2:L8,B2:B8,t,D2:D8,"<>"),m,SUMIFS(E2:E8,B2:B8,t),HSTACK(t,m,IF(m=0,"no miles",c/m)))

Result: spills 3 rows × 3 columns

T1292601.6776144908
T2199801.626026026
T30no miles

T1 29,260 miles at $1.678, T2 19,980 miles at $1.626, T3 0 miles: "no miles".

Cost per mile by month

Swap the truck column for the month column and the same pattern gives a monthly trend. Format the first column as a date (mmm yyyy). For a single month typed in a cell, =SUMIFS(L2:L8,A2:A8,O9,D2:D8,"<>")/SUMIFS(E2:E8,A2:A8,O9) gives October alone.

September $1.611 and October $1.651 look normal. November jumps to $2.706: only T1's 8,705 miles were read, while T3's 8,140 of shop and fixed costs sat on the books with no miles. That is a true picture of the fleet's November (an idle truck costs money) but not of what a running truck costs. Show both: the fleet rate for the business, the running-truck rate for quoting loads.

=LET(w,UNIQUE(A2:A8),c,SUMIFS(L2:L8,A2:A8,w,D2:D8,"<>"),m,SUMIFS(E2:E8,A2:A8,w),HSTACK(w,m,c/m))

Result: spills 3 rows × 3 columns

46266192051.6109346524
46296213301.6514299109
4632787052.7055715106

Sep 2026 19,205 miles at $1.611, Oct 2026 21,330 at $1.651, Nov 2026 8,705 at $2.706 (T3 idle, T2 unread).

Fuel cost per mile from MPG and price

Fuel per mile is price per gallon divided by miles per gallon: =O6/O5, $3.85 / 6.6 MPG. Use your real MPG (miles between fill-ups / gallons, see fuel cost and mileage), not the brochure figure.

Check it against the log: =SUMIFS(H2:H8,D2:D8,"<>")/SUM(E2:E8) is what fuel actually cost per mile, $0.584. The two agree, so the MPG and price inputs are realistic. If the logged figure is much higher, look for idling, a fuel card used for another truck, or a reefer unit burning fuel on its own.

Because MPG is in the bottom, small changes matter: at 7.1 MPG the same diesel is =O6/7.1, about $0.542 a mile, 4 cents less, or roughly 4,000 a year per 100,000 miles. For a car: 28 MPG at 3.29 a gallon is about 12 cents a mile.

=O6/O5

Result: 0.58333333333 shows as $0.583 with Currency format

$0.583 of fuel per mile at 6.6 MPG and $3.85 a gallon.

Cost per loaded mile and the rate you need

Rates are usually quoted on loaded miles, but you pay for every mile, empty ones included. Divide total cost by loaded miles (F) to see what a paying mile has to cover. Deadhead here is =1-SUM(F2:F8)/SUM(E2:E8), 14.1% of all miles.

To earn a margin, divide by 1 minus the margin in O7: =SUMIFS(L2:L8,D2:D8,"<>")/SUM(F2:F8)/(1-O7) gives $2.494 per loaded mile for 15%. The offered rate in O8, $2.45, covers cost ($2.120) but falls short of the target. Dividing by (1 - margin) is right when margin means profit as a share of revenue; multiplying cost by 1.15 gives a 13% margin, not 15%.

If you're paid a flat rate per mile by an employer or a customer, put that rate in a cell and compare it with your cost per mile in the same way. The rate is whatever your agreement or your tax adviser says; the spreadsheet only does the comparison. The trucking hub has profit per load with deadhead, and the cost per mile calculator template is a one-truck version you can download.

=SUMIFS(L2:L8,D2:D8,"<>")/SUM(F2:F8)

Result: 2.1199196597 shows as $2.120 with Currency format

$2.120 per loaded mile: 89,715 of costs over 42,320 loaded miles. Break-even on a loaded-mile rate.

Common mistakes

Summing odometer readings instead of the miles between them

An odometer reading is a position, not a distance. Miles = closing reading - opening reading, and each month's opening reading should equal last month's closing one; a gap means lost miles.

Keeping costs for a period with no miles recorded

A missing reading leaves costs on top and nothing underneath, so the rate jumps. Fix the reading or leave the row out of both sums (SUMIFS with D2:D8,"<>").

Averaging monthly or per-truck rates

A low-mile month gets the same weight as a full one. Sum costs and miles separately, then divide.

Leaving fixed costs out because they're "not per mile"

Payment, insurance and permits are part of what each mile costs. Spread them over the miles actually driven, and expect the rate to rise in slow months.

FAQ

What is the formula for cost per mile?

Total costs for a period divided by miles driven in the same period: =SUM(costs)/SUM(miles). Include fixed costs (payment, insurance, permits) and variable costs (fuel, driver pay, maintenance, tires).

How do I calculate fuel cost per mile in Excel?

Divide the price per gallon by miles per gallon: =price/MPG. At $3.85 and 6.6 MPG that is about $0.583 a mile. With litres and kilometres, divide the price per litre by kilometres per litre.

How do I calculate cost per mile for each vehicle?

Use SUMIFS twice: =SUMIFS(costs,vehicle,"T1")/SUMIFS(miles,vehicle,"T1"). In Excel 365, give SUMIFS a UNIQUE list of vehicles as the criteria to get every vehicle in one formula.

Why is my cost per mile so high this month?

Usually because miles fell while fixed costs didn't, a truck sat idle, or a reading is missing so costs have no miles against them. Check each month's opening reading matches the previous closing one.

Should I use loaded miles or total miles?

Use total miles for what the truck costs to run, and loaded miles to judge a rate that is paid only on loaded miles. The loaded-mile cost is always higher because it also covers the deadhead.

Functions used

Related guides

Updated