Working back VAT: how to calculate the amount excluding VAT

Working back VAT means converting an amount including VAT into the amount excluding VAT. You divide the amount including VAT by 1.21 at the standard Dutch rate of 21 percent, or by 1.09 at the reduced rate of 9 percent. The difference between the two amounts is the VAT. If you subtract 21 percent of the gross amount, you take off too much VAT and end up too low.

How do you work back VAT from gross to net?

Divide the amount including VAT by 1 plus the VAT rate as a decimal. At 21 percent that is 1.21, at 9 percent 1.09. An invoice of 121 euros including 21 percent VAT is therefore 121 divided by 1.21, or 100 euros excluding VAT. The VAT is 121 minus 100, so 21 euros. The reduced rate works the same way: 109 euros including VAT is 109 divided by 1.09, so 100 euros excluding VAT and 9 euros of VAT.

• Excluding VAT = amount including VAT divided by 1.21 (at 21 percent)
• Excluding VAT = amount including VAT divided by 1.09 (at 9 percent)
• VAT amount = amount including VAT minus amount excluding VAT

Why subtracting 21 percent is wrong

VAT is 21 percent of the amount excluding VAT, not of the amount including VAT. If you take 21 percent off 121 euros, you subtract 25.41 euros and end up at 95.59 euros instead of 100 euros. On a single receipt that is a few euros, but in accounts with hundreds of lines it adds up and the VAT return no longer reconciles. A quick check: calculate 21 percent of the net amount you found and add it on. If you do not get back to the original amount, the division went wrong.

Which formula do you use in Excel?

If the amount including VAT is in cell A1, the amount excluding VAT is =A1/1.21 and the VAT is =A1-A1/1.21. For the reduced rate, replace 1.21 with 1.09. It is better to put the rate in a separate cell, for example B1 containing 21 percent, and use =A1/(1+B1). Then you only need to change one cell when the rate changes. Only round in the last step, otherwise a cent shifts per line and the total no longer matches the invoice. If you would rather not remember formulas, the free VAT calculator at kantexsoftware.com/tools/vat-calculator works in both directions.

When should you automate VAT calculations?

As soon as amounts come from several sources, such as receipts, purchase invoices and a webshop with prices including VAT, working back by hand becomes error-prone. An accounting package or an integration between webshop and accounts calculates per line based on the correct rate, handles mixed rates on one invoice and records which rate applied on the invoice date. That last point matters when a rate changes: old invoices must stay consistent with the rate that applied at the time.

Last updated: October 23, 2026