Quarterly fuel tax worksheet
Open the miles export from your logging or trip system and your fuel-card export, then paste the rate table you are working from. The worksheet computes fleet miles per gallon, taxable gallons and net taxable gallons per jurisdiction, and tax at your rates. The page holds no rates of its own, and every rounding is stated.
Both files are read in your browser and stay on this device. Nothing is uploaded.
Both files are read in this browser and are not uploaded.
Matched from the header words. Check them and change any that are wrong. Rows for the same jurisdiction are added together. The file's own total line and lines with no miles are listed as left out, not added.
Only gallons and the jurisdiction are used here. When the jurisdiction column is blank, the two-letter code at the end of the location is used.
This page holds no rates. Paste or type the table you are working from: one jurisdiction per line, then its rate per gallon in dollars (up to 4 decimals). Use the same jurisdiction codes as your files.
Summary
Worksheet
This is a worksheet of your own numbers at the rates you supplied. It is not a return and is not in any agency's form layout.
What gets checked
- Every jurisdiction with miles or fuel is on the worksheet exactly once.
- Each jurisdiction's miles are its own miles rows added up, the jurisdictions add up to total miles, and total miles equal the total you typed when you type one.
- Each jurisdiction's gallons purchased are its own fuel rows added up, rounded to a whole gallon unless that is turned off. Total gallons purchased is every fuel row added up before rounding, and equals the total you typed when you type one.
- Miles per gallon is total miles divided by total gallons, rounded to 2 decimals.
- Each jurisdiction's taxable gallons are its own miles divided by fleet miles per gallon, rounded to a whole gallon (or to 3 decimals). This is tested one jurisdiction at a time, so gallons moved between jurisdictions fail even when the total is unchanged.
- Taxable gallons add up to the total shown, within half a gallon per jurisdiction of total miles divided by miles per gallon.
- Net taxable gallons are taxable gallons minus gallons purchased, row by row and in total.
- Every jurisdiction on the worksheet carries the rate from your table. A jurisdiction with no rate fails this check.
- Each jurisdiction's tax is its taxable gallons minus its gallons purchased, times its rate in your table, rounded to the cent, and the rows add up to total tax.
- These checks are made by a separate routine that does no division: every rounded figure is tested by cross-multiplication against the inputs.
- A warning, not an error, when miles per gallon is outside the band you set.
- Every row with data is accounted for: used, listed as left out (with its row number and the reason), or listed as unreadable. An unreadable row marks the result not verified. Fuel with no jurisdiction counts as unreadable here.
How it works
- Open the two files. Drop the miles export (.csv or .xlsx) in the first box and the fuel-card export in the second. Both are read in the browser.
- Check the column mapping. The jurisdiction, miles, and gallons columns are matched from the header words and can be changed. Rows for the same jurisdiction are added together.
- Paste your rate table. One jurisdiction per line with its rate per gallon in dollars, up to 4 decimals. The page has no built-in rates; the figures are the ones you enter.
- Build, review, and export. Miles per gallon is rounded to 2 decimals, gallons to whole gallons unless that is turned off, and tax to the cent, each half away from zero. The worksheet is shown with the verification certificate and can be downloaded as Excel or CSV, or printed.
Worked example
A made-up quarter: 10,000.0 miles and 1,600.000 gallons purchased, so fleet miles per gallon is 6.25. The rates are invented for this example and are not any jurisdiction's actual rate.
| Jurisdiction | Miles | Gallons purchased | Taxable gallons | Net taxable gallons | Rate per gallon | Tax |
|---|---|---|---|---|---|---|
| NM | 1,650 | 300 | 264 | -36 | $0.2275 | -$8.19 |
| OK | 2,150 | 401 | 344 | -57 | $0.1850 | -$10.55 |
| TX | 6,200 | 900 | 992 | 92 | $0.2500 | $23.00 |
Taxable gallons are miles divided by 6.25: 264 + 344 + 992 = 1,600, the gallons consumed. Purchased gallons of 299.500 and 400.500 round to 300 and 401, so the purchased column adds to 1,601 while total gallons purchased, added up before rounding, is 1,600.000. OK tax is -57 gallons at $0.1850 = -$10.545, which rounds half away from zero to -$10.55. Total tax at these made-up rates is $23.00 - $10.55 - $8.19 = $4.26.
The example uses made-up names and numbers.
Questions
Where do the rates come from?
From you. The page has no rate table and does not look one up. The rate box takes the table you are working from, written with the same jurisdiction codes your files use; a jurisdiction with miles or fuel and no rate in your table fails a check instead of being skipped.
How is the rounding done?
Miles per gallon is rounded to 2 decimals. Taxable gallons and purchased gallons per jurisdiction are rounded to whole gallons unless whole gallons are turned off, in which case 3 decimals are kept. Tax is rounded to the cent. All rounding is half away from zero and is done with whole-number math.
Is this a tax return?
No. It is a worksheet of your own miles and gallons at the rates you supplied, in its own layout. It is not an agency form and it does not say what is owed to anyone.
What if a fuel row has no jurisdiction?
It is listed as a row that could not be used and the result is marked not verified, because leaving its gallons out would change miles per gallon. The fuel ledger tool lists such rows separately.
How are the figures checked?
By a separate routine that shares no arithmetic with the one that builds the worksheet. It does no division: each rounded figure is tested by cross-multiplication against that jurisdiction's own miles and fuel rows and the rate in your table. Gallons moved from one jurisdiction to another fail a check even when every total still adds up.
What happens to a total line or a blank-looking row in my files?
A row with nothing in it is skipped. Every other row is either used, listed as left out with its row number (the file's own total line, a line with no miles or no gallons), or listed as unreadable. A fuel row with gallons and no date that is not a total line counts as unreadable, because leaving it out would change the totals.
This tool calculates and checks numbers. It is not legal, tax, or medical advice.