Build an IFTA calculator in Excel that catches Indiana and Kentucky surcharges your current spreadsheet misses

Most Excel IFTA templates fail because they treat surcharges as optional add-ons instead of building them into the state-by-state fuel-cost formula from the start, which costs you $200–$400 per quarter in missed liability.

Why your current spreadsheet undercalculates Indiana and Kentucky

Indiana consolidated its surcharge into the base rate effective Q2 2026—diesel is now $0.63/gallon all-in—but older templates still show separate lines for base tax and surcharge. You update the rate cell and suddenly your Indiana liability drops because the old surcharge row is now orphaned.

Kentucky is worse. It imposes a 2.0¢/gallon surcharge calculated on total gallons consumed in the state, not net gallons after fuel credit. Most spreadsheets treat surcharges the same way they treat base tax: multiply consumed gallons by rate. For Kentucky's surcharge, you still owe it on every gallon you burned there, even if you purchased more fuel in Kentucky than you actually consumed. The surcharge never generates a credit.

Single-line-per-state templates can't separate base tax from surcharge calculation because they don't have the row structure to handle it. The IFTA form itself uses separate schedules: Schedule 1 for base fuel tax, Schedule 2 for surcharges. Your Excel sheet should match that structure.

The two-line Excel structure that prevents surcharge blindness

Build two calculation lines for every surcharge state:

• Line 1 (base fuel tax): gallons consumed in state × base rate (eligible for fuel credit if you overpaid)
• Line 2 (surcharge): gallons consumed in state × surcharge rate (never eligible for credit)

Kentucky example: You consume 280 gallons in Kentucky. The base rate is $0.37/gallon; the surcharge is $0.02/gallon. Your calculation is not one flat cell ($0.39 × 280 = $109.20). It's two separate rows: base tax $103.60 and surcharge $5.60. If you bought more fuel in Kentucky than you consumed there, you claim a credit on the base tax. The surcharge is already owed on the 280 gallons—no credit applies.

Use a separate worksheet tab (label it "Schedule 2" or "Surcharges") for all surcharge rows. This separates base-tax logic from surcharge logic and matches the structure of the actual IFTA form that auditors expect to see reconstructed in your workpapers.

Worked example: Q3 2026 run through Indiana, Kentucky, and Tennessee with correct surcharge handling

Owner-operator runs 5,400 total miles: 1,600 in Indiana, 1,800 in Kentucky, 2,000 in Tennessee. Total fuel purchased is 520 gallons. At an average of 10.38 MPG (5,400 ÷ 520), gallons consumed by state are:

• Indiana: 154 gallons
• Kentucky: 173 gallons
• Tennessee: 193 gallons

Using Q3 2026 official IFTA rates:

Notice Indiana has no separate surcharge line. As of Q2 2026, Indiana's motor carrier fuel tax is $0.63 per gallon for diesel, consolidated from the old structure of base rate plus surcharge. If your template still shows a separate Indiana surcharge row, delete it or you'll double-count.

Kentucky's surcharge is $3.46. If your spreadsheet missed this line—either by using a single all-in rate or by calculating surcharge on net gallons instead of consumed gallons—you'd underpay by $3.46 per quarter. Over a year, that's $13.84 plus a 10% late penalty ($1.38) plus monthly interest.

How to build the rate lookup table so it updates without breaking formulas

Create one dedicated worksheet called "Rates" with these columns:

In your main calculation worksheet, use VLOOKUP or INDEX/MATCH to pull the correct quarter's rate:

`` =VLOOKUP("IN", Rates!A:E, 3, FALSE) ``

This looks up "IN" in the Rates tab and returns the Q3 rate from column 3 (or column 4 for Q4, etc.). When Indiana's rate changes next quarter, you update the Rates tab once; all formulas in the main sheet automatically pull the new rate.

Flag surcharge states in the "Surcharge Type" column. This lets you set up conditional logic: if Surcharge Type = "Per gallon", automatically route that row to your Schedule 2 worksheet instead of Schedule 1. You can't automate everything in Excel, but you can make surcharge states visually obvious so they don't get forgotten.

Update the Rates tab before quarter end using the official IFTA tax matrix at iftach.org. Rates lock at the start of each quarter (January, April, July, October), not on the current date. If you're filing a Q2 return in May, use the rates that were in effect April–June, not May's rates.

Kentucky's weight-distance tax (KYU) is not IFTA—don't mix them in the same calculator

Kentucky imposes a separate Weight Distance Tax (KYU) based on miles traveled on Kentucky highways, filed on a different quarterly return. A carrier running through Kentucky must hold both an IFTA license and a KYU Number and submit two separate filings.

KYU uses distance-based tiers, not fuel-consumption rates. It cannot be calculated in the same Excel sheet as your IFTA fuel tax because the logic is fundamentally different. Most carriers add Kentucky miles to IFTA fuel-tax rows, which creates an audit trigger because you're mixing two separate tax bases.

Your IFTA calculator should have a note in the Kentucky state row: "Kentucky miles ≠ Kentucky IFTA gallons. File KYU separately." If you run 1,800 miles in Kentucky, that's a KYU input on a separate form. The 173 gallons you consumed in Kentucky is your IFTA input.

The three Excel traps that trigger audits

Trap 1: Using current-quarter rates instead of historic rates. Rates update January, April, July, and October. If you're filing a Q3 return in November, use the rates that were in effect July–September, not October's new rates. A rate lookup table locked to the quarter you're filing for prevents this.

Trap 2: Calculating surcharge on net gallons instead of total gallons. Surcharges are never creditable. If you calculate Kentucky surcharge as (total gallons purchased − fuel credit) × surcharge rate, you've just eliminated liability you owe. Surcharge is always (gallons consumed in state) × (surcharge rate), period.

Trap 3: Building a single "Surcharge" column instead of separate Schedule 2 logic. Auditors reconstruct your IFTA form from your workpapers. If you show one liability number per state instead of Schedule 1 (base) and Schedule 2 (surcharge) broken out separately, it flags as a documentation gap. Use separate tabs or clearly labeled columns so the auditor can follow the two-line structure.