To add VAT to a net amount, multiply by the rate and add it on: =A2*B2 gives the VAT amount where A2 is the net price and B2 the rate (say 20%), and =A2*(1+B2) gives the gross total in one step.
To work backwards from a VAT-inclusive price — which is what most people actually need — divide rather than multiply: =A2/(1+B2) gives the net amount, and =A2-A2/(1+B2) gives the VAT contained within it.
This is the part people get wrong: taking 20% of a VAT-inclusive 120 gives 24, but the VAT in it is 20, because the 20% was added to 100, not to 120. Use =A2*B2/(1+B2) for the shortcut.
Keep the rate in its own cell so a change from 15% to 15.5% means editing one cell, not every formula. And round to two decimals with =ROUND(A2*B2,2) if the figures go onto an invoice, since unrounded VAT can leave totals a penny out.
To calculate VAT in Excel, you need to know the exact tax percentage, then multiply it by the net amount (the amount before tax) to get the VAT amount. After that, add that tax amount to the actual invoice amount.
In this tutorial, we will write formulas to calculate the VAT. Let’s check this out.
Calculate VAT Separately
You can use the below steps:
- First, refer to the cell with the invoice amount.
- After that, enter the asterisk (*) operator for multiplication.
- Now, refer to the cell where you have the VAT percentage.
- In the end, hit Enter to get the tax amount.
=tax_percentage * invoice_amount
Once you hit Enter, you’ll get the VAT amount in the cell.
In the next part, you need to add the tax to the total amount. For this, you need to refer to the cell with the net invoice amount (before tax), enter the addition operator, and refer to the cell with the VAT amount (TAX) you have calculated.
=vat_amount + invoice_amount
This will give you the total amount, including the VAT amount.
Calculating VAT in the Total Amount
If you want to get the total amount with the VAT included in one step, you can use the formula below:
=(tax_percentage * invoice_amount) + invoice_amount
With the above formula, you don’t need a separate column for the VAT amount. The total amount includes the tax and the invoice amount.
There’s one more way to write this formula. In this formula, you can use the percentages to get the total amount with the VAT.
=invoice_amount * (100% + vat_percentage)
This formula works exactly like the first one, but we use the percentage instead.
When you use 100% and add the tax percentage, for example, 10%, and multiply it by the invoice amount, it increases the amount by the exact tax percentage, and you get the total amount, including tax.
Extract VAT from a Total That Includes VAT
Sometimes you only have the total amount with VAT included (the gross amount), and you need to find the amount before tax and the VAT in it. Here, you can’t simply multiply the total by the VAT percentage, as the VAT was calculated on the net amount, not on the total. With 1,200 as the total and 20% VAT, 1,200 × 20% gives 240, while the actual VAT is 200.
To get the amount before tax, divide the total by 1 plus the VAT percentage:
=total_amount / (1 + vat_percentage)
And to get the VAT amount from the total, use the following formula. For 1,200 and 20%, both formulas give you 1,000 and 200:
=total_amount * vat_percentage / (1 + vat_percentage)
Tips for VAT Formulas
- Keep the VAT rate in one cell: put the rate in a cell like F1 and lock it with dollar signs ($F$1) in the formula, so it doesn’t move when you copy the formula down. When the rate changes, you only change that cell.
- Round the tax amount: VAT usually needs to be in whole cents, so wrap the formula in ROUND with 2 decimal places (formula below this list).
- Enter the rate as a percentage: type 20% (or 0.2), not 20. With 20, the formula multiplies the amount by 20.
=ROUND(A2*$F$1,2)
Frequently Asked Questions
How do I remove VAT from a price in Excel?
Divide the price by 1 plus the VAT rate. Don’t subtract 20% of the price, as that removes too much. For a price in A2 and 20% VAT:
=A2/(1+20%)
What is the difference between the net and the gross amount?
The net amount is the price before VAT, and the gross amount is the price including VAT. You calculate VAT on the net amount.
Can I use the same formulas for GST or sales tax?
Yes. GST and sales tax are also a percentage added to the net amount, so the same formulas work. Just use your tax rate.
How to increase 15% to 15.5% VAT in spreadsheet.
i.e R total
VAT 15.5%
Total price
Are you facing a problem changing the VAT percentage?