Formula Errors

How to Calculate 20% VAT and Net Amount in Excel

Adam Davis Oct 20, 2026 868 views

Question details

The user needs to calculate and separate the 20% VAT and the net amount from a given gross total using Excel formulas.

How to Calculate 20% VAT and Net Amount in Excel

Product: Excel
Device & OS: not provided
Scenario: Financial calculations or invoice processing where the gross total is known, but the 20% VAT and the original net amount need to be broken down into separate columns.
Observed behavior: The user has a gross amount in column A and requires formulas to accurately populate the VAT amount in column B and the net amount in column C without mathematical errors.

Before you start

Ensure your gross amounts are entered as numerical values in your spreadsheet, and format the cells as Currency or Accounting so the results properly display currency symbols and decimal places.

Solution 1Recommended

Use Division and Subtraction to Calculate Net and VAT Amounts

The standard and most accurate method to extract a 20% VAT from a gross amount is by dividing the gross figure by 1.2 to find the net amount, then subtracting.

When a standard 20% VAT is already included in a price, the gross amount represents 120% of the original net value. By dividing the gross amount by 1.2, you isolate the 100% net value. Subtracting this net value from the gross amount will yield the precise VAT portion.

Calculate the Net Amount

Click on cell C2 (or wherever you want the net amount). Type the formula `=A2/1.2` and press Enter. If A2 contains £120, this formula will correctly return £100.

Calculate the VAT Amount

Click on cell B2. Type the formula `=A2-C2` and press Enter. This subtracts the newly calculated net amount from the gross amount, leaving a VAT of £20.

Apply Formulas to the Entire Column

Select both cells B2 and C2. Click and drag the fill handle (the small square at the bottom-right corner of the selection box) downwards to apply these calculations to the rest of the rows in your dataset.

Use Division and Subtraction to Calculate Net and VAT Amounts

Rounding Currency Values: To ensure your values don't have hidden trailing decimals which can cause total errors later, wrap your formulas in the ROUND function. Use `=ROUND(A2/1.2, 2)` for the net amount and `=ROUND(A2-C2, 2)` for the VAT.

Solution 2

Calculate VAT Directly Using Percentage Multiplication

If you prefer to calculate the VAT amount directly from the gross total without finding the net amount first, you can use a direct mathematical fraction.

Efficient Spreadsheet Management

Easily Calculate Taxes and Invoices in WPS Spreadsheet

WPS Office provides a powerful, free spreadsheet tool that seamlessly handles complex financial formulas, VAT calculations, and business invoice templates. It supports all standard Excel formulas, ensuring your tax calculations are highly accurate and easy to manage.

1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx file containing the gross amounts.
2. Enter the VAT formula: Select the target cell, type `=A2/1.2`, and press Enter to instantly find your net amount.
3. Format as Currency: Highlight your results, right-click, select 'Format Cells', and apply your local currency format (like £ or $).

Fully compatible with Microsoft Excel (.xlsx) formats and standard formulas.Includes built-in financial formulas for quick tax and invoice calculations.Free, lightweight, and allows for fast data processing on any device.Familiar user interface makes switching from Microsoft Office completely effortless.

microsoft office alternative - wps office

Frequently Asked Questions

How do I calculate a different VAT rate, such as 5%?

To calculate a different VAT rate from a gross amount, adjust the divisor in your formula. For a 5% VAT, divide the gross amount by 1.05 instead of 1.2 to find the net amount (`=A2/1.05`). For a 15% rate, use 1.15.

How do I add 20% VAT to a net amount instead of extracting it?

If you have the net amount and want to calculate the final gross amount (Net + 20% VAT), simply multiply the net amount by 1.2. For example, use the formula `=A2*1.2` to instantly generate the total price.

Why am I getting a #DIV/0! error when calculating VAT?

The #DIV/0! error occurs if you accidentally divide by an empty cell or zero. When extracting VAT, ensure you are directly typing the number '1.2' into the formula (`=A2/1.2`), rather than referencing a blank cell.

Should I always use the ROUND function for financial spreadsheets?

Yes, it is highly recommended to use the ROUND function for currency calculations. Excel may display two decimal places but store more behind the scenes, leading to totals that appear mathematically incorrect by a penny or two. Wrapping calculations in `=ROUND(formula, 2)` prevents this.

Excel & Data Problems