Invoice Tax & Rounding Reconciliation
Find the tax inside a gross price and explain why an invoice total can differ by a cent. Combine tax-inclusive and tax-exclusive lines, supplied tax rates, percentage discounts and credits in one worksheet. Compare rounding each line with rounding each rate and price-basis group, inspect signed net/tax breakdowns, and reconcile against an optional observed invoice total. Exact fractions preserve the calculation trail in tables and CSV. Rates and currency precision are supplied by you; examples are illustrative. This planner does not determine tax eligibility, jurisdictional rates, legally required rounding or filing obligations. Changing a price basis reinterprets the entered price rather than converting it.
Unpack the tax. Explain the difference.
Extract tax from inclusive prices, add it to net prices, and trace discounts, credits and rounding through one invoice.
Invoice gross USD 349.5. Tax USD 39.5. Difference from observed invoice USD 0.
Edit invoice lines, taxes and rounding
Reconstruct an invoice using the rates and price basis on your documents. Tax rates are illustrative user inputs. This worksheet does not determine tax eligibility, jurisdictional rules or filing obligations.
Invoice lines
Enter unsigned quantities and prices. Choose credit to reverse the complete line. All numeric line inputs accept up to six decimal places. Required blanks stay invalid; enter a confirmed zero when intended.
For tax-exclusive lines, net and tax are rounded and added. For tax-inclusive lines, gross and tax are rounded and net is their difference. Group-rounded amounts are not allocated back to individual lines. A discrepancy is selected rounded gross minus the observed total; it does not identify the cause of a mismatch.
Exact gross change from discounts: USD 24. A discount on a credit reduces the credit amount. These user-selected conventions do not establish tax compliance.
Where does the rounding difference come from?
| Method | Net | Tax | Gross |
|---|---|---|---|
| Exact before rounding | USD 310 | USD 79/2 | USD 699/2 |
| Round each line | USD 310 | USD 39.5 | USD 349.5 |
| Round each group | USD 310 | USD 39.5 | USD 349.5 |
| Group minus line rounding | USD 0 | USD 0 | USD 0 |
Halfway values round away from zero. Exclusive prices: round net and tax, then add them. Inclusive prices: round gross and tax, then subtract to obtain net. Groups share the same price basis and exact tax rate; credits participate in their group.
Common gross scale: USD 0 to USD 349.5. Dashed marker is zero; every amount is also written below.
Line roundingUSD 349.5
Group roundingUSD 349.5
Observed invoiceUSD 349.5
Inside each line: net and tax
Exact amounts after discounts. Solid blue is net; purple outline is tax. Credits extend left of the dashed zero marker. Common scale: USD -24 to USD 216. No rounded group amount is allocated to individual lines.
Illustrative goods
Net USD 180 + tax USD 36 = gross USD 216.
Illustrative service
Net USD 150 + tax USD 15/2 = gross USD 315/2.
Illustrative credit
Net USD -20 + tax USD -4 = gross USD -24.
Line audit trail
Listed amount and discount use each line’s entered price basis. Exact values can be fractions. Rounded columns always show line rounding, even when group rounding is selected above.
| Line / basis | Listed amount | Discount | After discount | Exact net | Exact tax | Exact gross | Line-rounded net | Line-rounded tax | Line-rounded gross |
|---|---|---|---|---|---|---|---|---|---|
| Illustrative goodssale · inclusive · 20% | 240 | 24 | 216 | 180 | 36 | 216 | 180 | 36 | 216 |
| Illustrative servicesale · exclusive · 5% | 150 | 0 | 150 | 150 | 15/2 | 315/2 | 150 | 7.5 | 157.5 |
| Illustrative creditcredit · inclusive · 20% | -24 | 0 | -24 | -20 | -4 | -24 | −20 | −4 | −24 |
Rate and price-basis groups
| Group | Included lines | Exact net | Exact tax | Exact gross | Rounded net | Rounded tax | Rounded gross |
|---|---|---|---|---|---|---|---|
| exclusive · 5% | Illustrative service | 150 | 15/2 | 315/2 | 150 | 7.5 | 157.5 |
| inclusive · 20% | Illustrative goods, Illustrative credit | 160 | 32 | 192 | 160 | 32 | 192 |
Save the reconciliation
Export inputs, exact line and group amounts, both rounding methods and the observed-total difference. No invoice or tax return is sent.
How to use this calculator
- Load Mixed invoice example or One-cent rounding example, then open Edit invoice lines, taxes and rounding. Enter one currency label and the invoice’s minor-digit precision.
- For each line, choose sale or credit and specify whether the entered unit price already includes tax. Enter unsigned quantity, pre-discount unit price, tax percentage and line discount percentage. Use zero explicitly when applicable.
- Optionally enter the observed invoice total. Leave it blank to calculate without comparison. Use a minus sign for a credit total and match the chosen minor-digit precision.
- Compare line rounding and rate/basis group rounding. Inspect exact net and tax amounts, the line audit trail and group ledger to locate where the methods differ.
- Download the CSV or show its selectable text. Confirm your rates, basis and rounding convention against the actual invoice and applicable rules; this worksheet does not establish tax compliance.
The Formula
The mathematical logic powering this calculation is:
For quantity q, unit price p and discount percentage d, listed amount L = s × q × p, where s = +1 for a sale and −1 for a credit. Discount amount = L × d / 100. Discounted anchor A = L × (1 − d / 100). Quantities and prices are entered unsigned; credit reverses the full line, including discount and tax.
For tax rate r percent and tax-exclusive prices: net N = A; tax T = N × r / 100; gross G = N + T. For tax-inclusive prices: gross G = A; net N = G × 100 / (100 + r); tax T = G × r / (100 + r). This tax fraction is described in HMRC VAT Notice 700 section 7.3.1. It is not the same as multiplying the gross amount by r / 100. The same calculation before discount gives the undiscounted amounts; gross discount effect is before-discount gross minus after-discount gross. A discount on a credit reduces its magnitude.
The selected planning convention rounds half away from zero to 0–4 minor digits. For exclusive lines, round net and tax separately and add them for rounded gross. For inclusive lines, round gross and tax separately and subtract tax from gross for rounded net. Line policy applies this to each line and then sums. Group policy sums exact amounts by identical price basis and exact tax rate, then applies the corresponding rounding once per group. Credits participate in their group. Group minus line totals quantify rounding differences. No rounded group amount is allocated to individual lines. These are explicit worksheet conventions, not a claim to implement any jurisdiction’s required rounding rules; HMRC sections 17.5–17.6 illustrate that rounding treatment varies.
Selected gross minus the optional observed total is the discrepancy. Blank observation means no reconciliation; an explicit zero is a real observed amount. A discrepancy does not identify its cause. Exact numerator/denominator values remain in tables and CSV; charts display exact line components and both rounded invoice totals.
Supports 1–40 lines; quantities from 0 to 1,000,000; unit prices from 0 to 1,000,000,000; tax and discount percentages from 0 to 100; up to six decimal places for these inputs. Observed totals may be signed, up to 100 billion in magnitude, and must be representable at the selected minor-digit precision. Currency is one user-supplied label, not a conversion or a precision lookup. No tax eligibility, compound taxes, withholding, fixed-amount discounts, inclusive shipping rules, exchange rates or filing advice is inferred.Worked Example
If you input:
- Currency: USD; 2 minor digits; line rounding
- Goods: Sale: 2 × 120 tax-inclusive; 20% tax; 10% discount
- Service: Sale: 3 × 50 tax-exclusive; 5% tax; no discount
- Credit: Credit: 1 × 24 tax-inclusive; 20% tax; no discount
- Observed invoice: USD 349.50
Result:
Goods: listed gross 240 less 10% gives gross 216, net 180 and tax 36. Service: net 150 plus tax 7.50 gives gross 157.50. Credit: gross −24 contains net −20 and tax −4. Total net is USD 310, tax is USD 39.50 and gross is USD 349.50. Both rounding methods agree, so the discrepancy from the observed USD 349.50 is zero. The exact gross discount effect is USD 24. The separate three-line rounding example totals USD 0.06 with line rounding and USD 0.05 with group rounding, a USD −0.01 group-minus-line difference.
Frequently Asked Questions
How do I remove sales tax or VAT from an inclusive price?
For gross price G and rate r percent, net is G × 100 / (100 + r), and tax is G × r / (100 + r). At an illustrative 20% rate, a gross price of 120 contains net 100 and tax 20. Multiplying 120 by 20% would overstate the tax content.
Can an invoice contain both inclusive and exclusive prices?
Yes. Choose the basis on each line. Inclusive prices already contain the supplied tax; exclusive prices have tax added. The planner keeps different bases in separate rounding groups even when their tax rates are equal.
Why do line rounding and group rounding differ?
Rounding several fractions separately can differ from rounding their sum. Three exclusive lines of 0.01 at 50% tax each have exact tax 0.005. At two decimals with half-away rounding, line tax totals 0.03 while group tax rounds to 0.02; gross totals are 0.06 and 0.05.
How do I enter a credit or return?
Choose credit and keep quantity and unit price nonnegative. The complete line is reversed, including its discount and tax. Credits participate in their rate/basis group. This worksheet does not determine whether a credit is legally allowable or send a refund.
Does a discount change the tax?
Here the percentage discount applies to the entered price basis before tax extraction or addition. This reduces both net and tax for a sale. A discount on a credit reduces the credit magnitude. Other discount treatments are outside this model.
Does this use the legally required rounding method?
No. It compares two explicitly defined planning methods using half-away-from-zero rounding. Tax rules and invoice systems may use different treatments. Check the applicable rules and your invoice settings rather than treating either result as automatically compliant.
What does the difference from observed total mean?
It is selected rounded gross minus the total you entered. Positive means the calculation is higher; negative means lower. It does not diagnose missing lines, incorrect rates or a different provider convention. Blank observed total skips the comparison; zero does not.
Why are fractions visible in the audit tables and CSV?
Removing tax can produce a nonterminating decimal. Exact fractions retain that value without premature rounding. Headline amounts use your selected rounding method; line-rounded columns remain line-rounded even when group rounding is selected.
Similar Calculators
Prorated Billing & Change Planner
Calculate prorated charges and credits across dated price or quantity changes. Compare invoice rounding, inspect service timelines and reconcile payment balances.
Payment Processing Fee Calculator
Compare processing quotes across payment groups, refunds, fee caps and monthly minimums. Visualize fees and net proceeds, find a target-net charge and export exact ledgers.
Profit Margin Calculator
Calculate gross, operating, and net profit margin percentages for your business or product.
