Calculate Net and VAT From Gross Invoices in Excel

To split a VAT-inclusive (gross) total into its net and VAT parts, divide the gross by 1 plus the VAT rate:

• Net = Gross / (1 + VAT rate)
• VAT = Gross − Net

At a 20% rate that means dividing the gross by 1.2; at 5% you divide by 1.05. In a spreadsheet, if the gross total sits in cell A2, the net is =A2/1.2 and the VAT is =A2-A2/1.2. That single pair of formulas is the whole basis for how you calculate VAT from gross in Excel, whether you are working out one figure or a thousand.

The mistake almost everyone makes first is to take the rate straight off the gross: subtract 20% of the gross and call the remainder the net. It gives the wrong answer. The VAT was charged on the net, not on the gross, so the gross is 120% of the net, not 100%. Take a £120 gross at 20%. Subtracting 20% of £120 gives £96, which is wrong. The correct net is £120 / 1.2 = £100, and the VAT is £120 − £100 = £20. Check it the other way: £100 plus 20% is £120. To remove VAT from a gross amount you divide, you do not subtract a percentage of the total.

The same logic lets you work out net from gross at any rate, as long as you divide by the right figure. The divisor is always 1 plus the rate expressed as a decimal: 1.2 for 20%, 1.05 for 5%, and 1.0 for a zero-rated item (where the net equals the gross and the VAT is nil). Those rates are not arbitrary. In the UK they are set by the UK's standard, reduced and zero VAT rates: the standard rate is 20%, the reduced rate is 5%, and the zero rate is 0%. The rate values differ by country, but the structure of the formula does not, which is what makes it portable across any currency or jurisdiction.

Driving the Divisor From a Rate Column Instead of Hardcoding 1.2

The single-figure formula stops being useful the moment your second invoice carries a different rate. Hardcode =A2/1.2 down a column and every row gets divided by 1.2, so a 5% invoice and a zero-rated invoice both come out wrong while looking perfectly plausible. The fix is to stop hardcoding the divisor and read it from the row itself.

Put the gross in column A and the VAT rate in column B, then write the net as =A2/(1+B2) and the VAT as =A2-A2/(1+B2). Fill those down, and each row divides by its own rate. Invoice on row 2 at 20%, row 3 at 5%, row 4 zero-rated: one formula handles all three because the divisor travels with the data. This is the reverse VAT formula for a spreadsheet in the form that actually scales, and it is the single most useful step once you are working across more than one invoice.

Two structural details keep the column reliable. First, give every row its own rate cell rather than trusting a single rate for the whole batch; the relative reference B2 shifts to B3, B4 and so on as you fill down, so the rate must genuinely be present on each line. Keeping the data as one row per invoice is what makes the column clean to compute on in the first place, which is why it pays to structure the extracted data as one row per invoice before you start writing formulas.

Second, watch how the rate cell is stored, because 1+B2 only works if B2 evaluates to a decimal. A cell formatted as a percentage and showing 20% holds the underlying value 0.2, so 1+B2 correctly gives 1.2. A cell containing the literal number 20 does not; 1+20 is 21, and the maths collapses. Decide whether your rate column holds decimals (0.2, 0.05, 0) or percentage-typed cells (20%, 5%, 0%), keep it consistent across the batch, and the formula behaves. Once that column is in place, the reverse-VAT calculation runs down hundreds of rows untouched, which is the groundwork for anyone looking to automate invoice data entry in Excel rather than retype splits by hand.

Handling Mixed VAT Rates Within and Across Invoices

Real invoice batches break the one-rate assumption in two distinct ways, and they need different handling. The first is a batch where each invoice has a single rate but the rates differ between invoices: invoice A is all 20%, invoice B is all 5%, invoice C is zero-rated. The second is a single invoice that carries several rates at once. A UK invoice can mix 0%, 5% and 20% lines on one document; German invoices routinely combine 7% and 19%; Greek invoices mix 13% and 24%.

The first case is already solved. The rate column from the previous section divides each row by its own (1 + rate), so a batch where every invoice uses a different rate needs nothing special. Get the rate right on each row and the column handles the mix on its own. This is the practical core of how to calculate VAT with mixed rates in Excel: not a clever formula, but a correct rate next to every gross.

The single invoice with multiple rates is the harder one, and it is the case the single-rate tutorials never touch. You cannot back net and VAT out of one mixed-rate gross total with one divisor, because there is no single rate that describes the whole figure. A £230 gross made of a £120 band of 20% goods and a £110 band of zero-rated goods does not divide cleanly by any one number. You have to split the gross into rate bands first, one subtotal per rate, then apply Net = subtotal / (1 + rate) to each band and sum the results. The 20% band of £120 gives £100 net and £20 VAT; the zero-rated band of £110 is £110 net and nil VAT; together that reconstructs the £230 and a total VAT of £20.

That splitting step only works if you hold the data at line-item level, with each line carrying its own rate, so the gross can be grouped by rate before the formula runs. A SUMIF that totals line amounts per rate band, =SUMIF(rate_range,0.2,amount_range) for the 20% lines, gives you those subtotals to divide. Once the rate lives on every line, both problems reduce to the same move you already know: divide each amount by 1 plus its own rate. The invoice-level gross alone is not enough for a mixed-rate document, which is exactly why a single printed total cannot be reverse-engineered without the breakdown beneath it. The same per-rate breakdown is what feeds period-end VAT reporting too: if you trade in Ireland, classifying a year of invoices by rate is the groundwork for compiling the annual Return of Trading Details from your invoices.

Reconciling Your Calculated VAT Against the Printed Invoice Figure

Most invoices already print their own VAT figure. That gives you a free check: compute the VAT independently with the reverse-VAT formula, then compare it to the number the supplier printed. When the two agree, you have confirmation the gross was captured correctly and the rate is right. When they disagree, you have caught something worth catching, before it reaches your books. Some of this checking can happen before the data ever reaches the sheet, since AI extraction can derive and validate calculated fields like net, VAT and total during capture rather than leaving every reconciliation to spreadsheet formulas afterwards. The point of the exercise is to reconcile calculated VAT against the printed invoice for every row, not just trust whichever figure you happened to type or extract.

Set it up as three columns alongside the gross. One holds your computed VAT, =A2-A2/(1+B2). One holds the printed VAT lifted from the invoice. The third is the difference, =ABS(C2-D2) if computed VAT is in C and printed VAT in D. Then flag the exceptions instead of reading every row: =IF(ABS(C2-D2)>0.01,"CHECK","") marks any row where the two VAT figures diverge by more than a penny. Across a batch of several hundred invoices you scan only the rows tagged CHECK, which turns reconciliation from a line-by-line slog into a short exception list.

A mismatch usually means one of three things. The gross was captured wrong, whether mistyped or mis-extracted. The supplier made an arithmetic error on the invoice itself, which happens more than people expect. Or it is nothing more than rounding. That last category is why an exact-match test is the wrong tool and a small tolerance is the right one. Suppliers round at different points, so your computed VAT and their printed VAT can legitimately differ by a sub-penny without anything being wrong.

You can remove most of that rounding noise by splitting the way the invoice does. Round the net to two decimals and derive the VAT as the gross minus the rounded net, so the net and VAT always add back exactly to the printed gross. The practitioner shorthand is ROUNDDOWN on the net and ROUNDUP on the VAT, which keeps the VAT from being understated while guaranteeing the two parts tie back to the total. Set the tolerance just above the sub-cent level, and the only rows that flag are the ones where the difference is real: a genuine extraction error or a genuine vendor mistake, not a rounding artefact. That is the line between a formula that looks right and numbers you can actually stand behind.

Where the Column of Gross Totals Comes From at Scale

Every formula in this guide assumes one thing: a column of gross totals already sitting in your sheet, one row per invoice, ready to divide down. In practice that column rarely starts life as a column. It starts as a stack of PDF invoices and fiscal receipts, and the real work at any volume is turning that stack into structured rows before the reverse-VAT formula has anything to run against.

The workflow is the same whichever way you get there. Get the figures out of the PDFs and into the sheet, put each invoice on its own row with the gross total in its own column, and only then fill the reverse-VAT formula down. When you need mixed-rate handling or a reconciliation against printed VAT, capture at line-item level instead, so each line brings its own rate and the supplier's own VAT figure travels with it. Both the per-rate split from the mixed-rate section and the printed-VAT check from the reconciliation section depend on that data being present in the row, which is why how you extract matters as much as how you calculate.

At a handful of invoices you can type the totals in by hand. At a few hundred a month you need to extract a batch of supplier invoices into a spreadsheet rather than rekey them. This is the job our tool is built for: you upload a batch of invoices, up to 6,000 files at once, and describe what you need in a plain prompt, for example the vendor, gross total, VAT rate and printed VAT, one row per invoice. It returns a structured Excel, CSV or JSON file with those fields in their own columns, every value typed as a real number so the formulas work immediately, and every row carrying a reference back to its source page for checking. If you would rather see the mechanics first, the same approach is covered step by step in how to convert your PDF invoices into an Excel spreadsheet, and you can equally export the extracted invoice data to CSV if that fits your downstream workflow better. The same prompt-driven extraction can do more than pull the gross and VAT: with one reusable prompt you can also sort each invoice into your own expense categories and cost centres as the rows land in the sheet, so the column is already coded for the ledger before any formula runs. Once every invoice sits on its own row, the same structure lets you add up total spend across a folder of invoices with SUMIFS and a PivotTable, broken down by supplier, category or date.

From there the column of gross totals is just a column, and everything above applies: divide by 1 plus each row's rate, split mixed-rate invoices by band, and reconcile your computed VAT against the figure the supplier printed.