How to Use an Excel Spreadsheet to Calculate IFTA & HUT

The International Fuel Tax Agreement (ITFA) is legislation covering the lower 48 states and Canadian provinces. IFTA benefits drivers who use highway motor vehicles in excess of 26,000 pounds along with varying additional criteria. Drivers who purchase an IFTA license receive a decal permitting them to consolidate taxes for various jurisdictions into a quarterly payment. According to Venture Logistics, the Highway Use Tax (HUT) is "assessed by the federal and state governments against users of the highway. The use tax is used to pay for the construction, maintenance, and policing of highways." Each of these taxes can be calculated in a Microsoft Excel spreadsheet

...
Microsoft Excel can calculate IFTA and HUT.

Calculate IFTA in Microsoft Excel

Step

Enter the following column headers in the Excel spreadsheet from left to right: State, Mileage, Gallons, Fuel, Difference, Tax Paid, Tax Rate, Mile Tax, IFTA.

...
Make sure the columns are in the proper order, as shown.
...
Alaska, Hawaii and the Canadian territories do not participate in IFTA

Step

Enter the abbreviations for the 48 states, one per row, in Column 1 (State). Enter the tax rate for each state from the IFTA Fuel Tax matrix, which can be found at IFTA's website in Resources below. Be sure you are entering the correct rate for each state.

...
Holding down the mouse button allows you to paste a formula in a column.

Step

Enter the formulas across row 2 under the correct column headings. Column E will be "Difference." Without typing the brackets enter the formula on row 2 exactly as it appears between brackets [=C4-D4]. Column I will be IFTA. Enter the formula on row 2 exactly as it appears between the brackets [=G4*E4] do not type the brackets. After entering both formulas, select the cell E2 (under Difference) until it is highlighted and hold down the left mouse button. With cell E2 highlighted hover the mouse until the cursor becomes a black cross. Continue to hold down the left mouse button to drag the formula down the column until it fills all the rows that contain a State, then release. Repeat for the formula in Column I (IFTA).

...
Test your numbers to make sure they are calculating correctly.

Step

Enter sample figures to test the accuracy of the formulas. You can find sample data to test your spreadsheet in the Resources section titled "IFTA Tax Data."

Calculate Highway Use Tax (HUT) in Microsoft Excel

Step

Type the following column headers in Microsoft Excel: TAX, Additional Tax, Total Tax, Credits, and Balance Due. Be sure to type the headers in the correct order.

...
Heavy vehicles pay a tax to cover highway maintenance.
...
Find the tax due for each vehicle using instructions on Form 2290

Step

Enter the formulas under the following headers in Row 2. Column C is Total Tax. Enter the formula on row 2 exactly as it appears between the brackets =A2+B2] do not type the brackets. Column E is Balance Due. Enter the formula on row 2 exactly as it appears between the brackets [ =C2-D2] do not type the brackets. Repeat the steps above for copying and pasting the formulas on subsequent rows. In this case the rows will likely be determined by the tax year. One row for each tax year.

...
Compare the results in Excel to your calculator figures.

Step

Enter the figures as follows: Tax due for the current year in Column 1 (Tax). This figure is normally entered on row 2 on Form 2290. Enter additional tax in Column 2 (Additional Tax). Total tax is calculated by the formula in Column 3 (Total Tax). Enter any credits in Column 4 (Credits). The balance due is calculated by the formula in Column 5 (Balance Due). Check your figures with a calculator for accuracy.