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
Things You'll Need
- A copy of the IFTA fuel tax matrix
- IRS Form 2290
- A calculator (optional)
Calculate IFTA in Microsoft Excel
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.
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.
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).
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
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.
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.
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.
Tips & Warnings
- Always keep the columns in the same sequence in Microsoft Excel when using formulas.
- If desired, copy and "paste values" to a new worksheet. This copy will enable you to do more calculations, if desired. In Excel, the original formulas are what gets pasted from their cells. If you replace the formula with the actual value, then you lose the formula.