Calculate IFTA liability in Excel with a two-table system that catches Indiana and Kentucky surcharges in under 20 minutes

A working IFTA spreadsheet needs exactly two tables: one for state-by-state fuel consumption (gallons ÷ fleet MPG × state rate) and one lookup table for current rates, because Indiana's $0.61/gallon consolidated rate and Kentucky's 2.0¢ surcharge (which never generates a credit) blow up any calculation that treats them as afterthoughts.

Why most Excel IFTA templates fail

Most owner-operators build a single calculation table that tries to do rate lookup inline. When Indiana or Kentucky rates change mid-quarter, you have to manually edit 10+ cells instead of updating one lookup table. Kentucky surcharges don't care about net gallons—they're charged on gallons purchased, not gallons burned—and templates that mix net-gallon logic with surcharge logic produce credits that shouldn't exist.

The fix is a separation of concerns: Calculation Table (state, miles, gallons, tax) and Rate Lookup Table (state, base rate, surcharge rate, current_quarter_effective_date). One table is your working surface. One table is your source of truth.

Table 1: The calculation frame—miles, fleet MPG, and net taxable gallons by state

Fleet MPG is calculated once per quarter: total miles (all states) ÷ total gallons purchased (all states), carried to three decimals.

Taxable gallons per state = miles driven in that state ÷ fleet MPG.

Indiana: Multiply taxable gallons × $0.61 (not $1.22). Starting April 1, 2026, Indiana folded its Motor Carrier Fuel Tax surcharge into its base Special Fuel tax rate. If you're filing Q1 2026 (January–March), use $0.61 base + $0.61 surcharge in separate calculations. Q2 2026 onward, use $0.61 total on one line. Confirm current rates at in.gov/dor/motor-carrier-services/fuel-tax.

Kentucky: Two separate lines—base tax on (gallons purchased in KY × KY base rate) PLUS surcharge on (gallons purchased in KY × $0.02). The surcharge does not reduce if you bought more fuel than you burned. If you purchased 210 gallons in Kentucky but burned only 216 net gallons across all states, you still owe surcharge on all 210 gallons purchased in Kentucky.

Your column order:

Table 2: The rate lookup—where Indiana and Kentucky surcharges live

Indiana gets one row: IN | Q2 2026 | $0.61 | $0.00 | 2026-04-01.

Kentucky gets two rows (or one row with both columns): KY | Q2 2026 | $0.27 | $0.02 | 2026-04-01.

Use VLOOKUP or INDEX/MATCH to pull rates into Calculation Table. When a rate changes on the state DOR website, you update one cell in Lookup Table and all calculations recalculate. You don't touch your Calculation Table formulas ever again. Surcharge rates are published at the same time as base rates. Check Kentucky, Virginia, New Mexico, and New York surcharge columns for mid-quarter changes at IFTA Inc.

Worked example: 6,800 miles, 1,050 gallons, four states, Q2 2026

Driver ran: Texas 1,900 mi (350 gal purchased), Oklahoma 1,600 mi (280 gal), Kentucky 1,400 mi (210 gal purchased), Indiana 1,900 mi (210 gal). Total 6,800 miles, 1,050 gallons.

Fleet MPG = 6,800 ÷ 1,050 = 6.48.

State	Miles	Gal Purchased	Taxable Gal (mi÷6.48)	Base Rate	Base Tax	Surcharge Rate	Surcharge	Total
TX	1,900	350	293.2	$0.20	$58.64	$0.00	$0.00	$58.64
OK	1,600	280	246.9	$0.19	$46.91	$0.00	$0.00	$46.91
KY	1,400	210	216.0	$0.27	$58.32	$0.02	$4.20	$62.52
IN	1,900	210	293.2	$0.61	$178.85	$0.00	$0.00	$178.85
TOTAL	6,800	1,050	1,049.3	—	—	—	—	$347.92

Texas: 1,900 mi ÷ 6.48 = 293.2 gal × $0.20 = $58.64.

Oklahoma: 1,600 mi ÷ 6.48 = 246.9 gal × $0.19 = $46.91.

Kentucky base: 1,400 mi ÷ 6.48 = 216.0 gal × $0.27 = $58.32. Kentucky surcharge: 210 gal purchased × $0.02 = $4.20. Kentucky total = $62.52.

Indiana: 1,900 mi ÷ 6.48 = 293.2 gal × $0.61 = $178.85.

Total IFTA due: $58.64 + $46.91 + $62.52 + $178.85 = $347.92.

Note the Kentucky surcharge row: it uses gallons purchased (210), not taxable gallons (216). If you used taxable gallons for the surcharge, you'd calculate 216 × $0.02 = $4.32 instead of $4.20, overpay by $0.12, and file incorrectly.

Indiana's consolidated rate: why $0.61, not $1.22

Starting Q2 2026 (April 1, 2026), Indiana folded its Motor Carrier Fuel Tax surcharge into its base Special Fuel tax rate. Old method (Q1 2026 and earlier): base rate $0.61 + separate surcharge $0.61 = $1.22 effective, filed on two lines (Schedule 1 base, Schedule 2 surcharge).

New method (Q2 2026 onward): single rate $0.61 on Schedule 1, no separate surcharge line. Your lookup table goes from two Indiana rows to one Indiana row, and your Calculation Table loses the Indiana surcharge line entirely.

If you're filing Q1 2026 (January–March), use $0.61 base + $0.61 surcharge in separate calculations. If you're filing Q2 2026 or later, use $0.61 total on one line. Your Rate Lookup Table catches this because you update it once per quarter before filing; your Calculation Table never changes.

Kentucky surcharges: why they don't generate credits even if you over-purchased

Kentucky charges 2.0¢/gallon surcharge on every gallon you purchased in Kentucky, not on every gallon you burned there. If you bought 210 gallons in Kentucky but burned only 216 net gallons (miles ÷ fleet MPG) across all states, you still owe surcharge on all 210 gallons purchased in Kentucky.

Your Calculation Table must have two separate Kentucky rows: one for base tax (using net taxable gallons and base rate), one for surcharge (using gallons purchased and surcharge rate). If you combine them, you'll calculate surcharge on taxable gallons instead of purchased gallons and generate false credits.

How to pull rates from IFTA Inc. and state DOR websites into your lookup table

IFTA Inc. publishes a master rate chart at iftach.org before each quarter begins. Download it, extract the base rates for all states you operate in, and paste into your Lookup Table.

For Indiana, confirm the current rate at in.gov/dor/motor-carrier-services/fuel-tax. The consolidated rate is $0.61 for Q2 2026 onward.

For Kentucky, check the surcharge specifically—it's not always on the main IFTA chart. Go to revenue.ky.gov or cite the IFTA Inc. chart, which flags Kentucky's 2.0¢ surcharge separately. Record the effective date (first day of the quarter) in your Lookup Table. If rates change mid-quarter, update the effective date and re-file using the new rates for the remainder of the quarter. Your Calculation Table will recalculate automatically via VLOOKUP or INDEX/MATCH.

Rounding and carry-forward: why three decimals matter

Calculate taxable gallons to three decimals (e.g., 293.247 gal) and carry them forward to tax calculation. Round only the final tax liability (to nearest cent) for each state.

If you round at each step (miles ÷ MPG → round to 2 decimals, then × rate → round again), you accumulate rounding error across four states and can lose or gain $5–$15 over a quarter. Excel tip: use =ROUND(A1/B1, 3) for gallons and =ROUND(C1*D1, 2) for tax only. Format the final column as Currency to show cents only on output. Your two-table system keeps your Rate Lookup Table static; you refresh it once per quarter and never edit your Calculation Table formulas.