Expert guide

IFTA Fuel Tax Spreadsheet in Excel

Quarterly miles, gallons and tax by jurisdiction, with the records to back them up

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: keep a trip log (truck, date, jurisdiction, miles) and a fuel log (truck, date, jurisdiction, gallons, tax paid) as Excel Tables. Each quarter, sum miles and gallons by jurisdiction with SUMIFS, calculate fleet MPG, taxable gallons = taxable miles ÷ MPG, net taxable gallons = taxable − tax-paid gallons, and tax due = net gallons × the quarter's published rate. Reconcile miles to odometer or ELD totals before filing. We automate the data feeds as custom Excel spreadsheets.

For small carriers and owner-operators, IFTA is a quarterly scramble: collect fuel receipts from cab floors, pull mileage from the ELD or GPS portal, split it by state or province, look up rates and calculate. Errors are expensive in both directions, overpaying or facing an audit with records that do not reconcile. A structured spreadsheet makes the calculation repeatable and the records defensible. This guide explains the mechanics; it is not tax advice, and your base jurisdiction's rules prevail.

The Two Logs

LogColumnsSource
Trip / mileage logTruck, Date, Jurisdiction, Miles, Taxable (Y/N), Trip or load referenceELD or GPS jurisdiction report, or driver trip sheets
Fuel logTruck, Date, Jurisdiction, Vendor, Gallons, Fuel type, Tax-paid (Y/N), Receipt referenceFuel card export plus receipts for cash purchases
Rates tableQuarter, Jurisdiction, Fuel type, Rate, SurchargeCurrent IFTA tax rate matrix
Odometer tableTruck, Quarter, Start odometer, End odometerReconciliation check

Fuel card providers and ELD platforms export these as CSV. Power Query can append each quarter's exports into the logs in one refresh, which removes most manual entry.

The Quarterly Calculation

On a Quarter sheet, one row per jurisdiction (A), quarter start in B1:

Total miles in jurisdiction:
=SUMIFS(tblTrips[Miles], tblTrips[Jurisdiction], A5,
        tblTrips[Date], ">=" & $B$1, tblTrips[Date], "<" & EDATE($B$1, 3))

Taxable miles:
=SUMIFS(tblTrips[Miles], tblTrips[Jurisdiction], A5, tblTrips[Taxable], "Y",
        tblTrips[Date], ">=" & $B$1, tblTrips[Date], "<" & EDATE($B$1, 3))

Tax-paid gallons purchased:
=SUMIFS(tblFuel[Gallons], tblFuel[Jurisdiction], A5, tblFuel[TaxPaid], "Y",
        tblFuel[Date], ">=" & $B$1, tblFuel[Date], "<" & EDATE($B$1, 3))

Fleet MPG (one cell, B2):
=SUM(TotalMilesColumn) / SUMIFS(tblFuel[Gallons],
        tblFuel[Date], ">=" & $B$1, tblFuel[Date], "<" & EDATE($B$1, 3))

Taxable gallons:      =ROUND(C5 / $B$2, 0)
Net taxable gallons:  =E5 - D5
Rate:                 =XLOOKUP(1, (tblRates[Jurisdiction]=A5)*(tblRates[Quarter]=$B$3), tblRates[Rate])
Tax due / (credit):   =ROUND(F5 * G5, 2)

Check your jurisdiction's rounding rules for MPG and gallons; many filing systems round MPG to two decimals and gallons to whole numbers. Surcharges in some jurisdictions are calculated on taxable gallons rather than net gallons, so they need their own column.

Checks Before You File

  • Miles reconcile to odometers

    Sum of trip miles per truck should match end minus start odometer within a small tolerance.

  • Fleet MPG is plausible

    A sudden jump from 6.4 to 8.9 MPG usually means missing fuel receipts, not a better driver.

  • Every fuel purchase has a receipt reference

    Filter for blanks. Tax-paid credit without a receipt does not survive an audit.

  • Jurisdiction codes are consistent

    Use standard two-letter codes and a validation list so 'Ont', 'ON' and 'Ontario' do not split totals.

  • No miles in jurisdictions with no route

    Spot-check unusual jurisdictions against load records.

Per-Truck View

IFTA is filed at fleet level, but auditors look at individual vehicles. A per-truck summary (miles, gallons, MPG by quarter) makes outliers obvious: a truck with no fuel purchases in a state it drove 1,200 miles in, or one whose MPG is far from the rest of the fleet.

Where AI Helps

  • Read photographed fuel receipts into the fuel log with vendor, jurisdiction, gallons and date.

  • Flag receipts where the jurisdiction does not match the truck's route that day.

  • Summarise the quarter's figures and anomalies for the owner before filing.

Receipt capture uses the same approach as extracting invoice data into Excel with AI. Keep the original image linked to each fuel log row.

Beyond IFTA: Fleet Records That Pay Off

The same mileage and fuel data feeds cost per mile, maintenance scheduling and load profitability. See the fleet maintenance log and freight load tracking spreadsheet. When fuel card and ELD data should flow in automatically each week, that is an Excel API integration or an automated consolidation project.

FAQ

IFTA spreadsheet FAQ

Calculation, rates and records

For each qualified vehicle and quarter: total miles (or kilometres) travelled in each member jurisdiction, total miles overall, and fuel purchased by jurisdiction with receipts. From these you calculate fleet average fuel economy, taxable fuel used per jurisdiction, and the tax owed or credit due after tax-paid purchases.

Fleet MPG = total miles ÷ total gallons purchased. For each jurisdiction, taxable gallons = taxable miles in that jurisdiction ÷ fleet MPG. Net taxable gallons = taxable gallons − tax-paid gallons purchased there. Tax due = net taxable gallons × that jurisdiction's rate for the quarter. Positive results are owed; negative results are credits.

IFTA, Inc. publishes the official tax rate matrix for each quarter, and your base jurisdiction's filing system uses it. Always use the current quarter's published rates rather than rates copied from a previous file, and include any surcharges your base jurisdiction requires.

Excel is commonly used to prepare the figures and keep the supporting records; the return itself is filed with your base jurisdiction, usually online. The spreadsheet must be backed by trip records and fuel receipts that would satisfy an audit.

IFTA requires records to be retained for a set period, commonly four years from the return due date or filing date, whichever is later. Confirm the current requirement with your base jurisdiction and keep trip sheets, ELD or GPS mileage data and fuel receipts together.