Formula Errors

How to Calculate 5% VAT Included in a Gross Amount in Excel

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to extract a 5% VAT from a gross amount that already includes the tax, and subsequently calculate 60% of that extracted VAT amount using Excel formulas.

Product: Excel
Device & OS: not provided
Scenario: Calculating tax-related data entries for accounting, specifically extracting VAT and applying a percentage to it.
Observed behavior: The user requires specific formulas to perform the VAT extraction and percentage calculation automatically within their spreadsheet to simplify data entry.

Before you start

Ensure your gross amount data is formatted as numbers or currency in your spreadsheet so the calculation formulas can function correctly without returning errors.

Solution 1Recommended

Extract the 5% VAT from a VAT-inclusive Gross Amount

Use this formula to find out exactly how much of the total gross amount is made up of the 5% VAT.

When a gross amount already includes a 5% VAT, the total represents 105% of the original net price. To isolate just the 5% tax portion, you must divide the total by 105 and multiply the result by 5.

Select the target cell

Click on an empty cell (e.g., B2) where you want the extracted VAT amount to appear.

Enter the VAT extraction formula

Type the formula =A2/105*5 (assuming your gross amount is in cell A2) and press Enter. This isolates the 5% tax from the inclusive total.

Apply to other rows

Click on cell B2 again, grab the fill handle at the bottom-right corner, and drag it down to apply this formula to the rest of your accounting data rows.

Formula Tip: If your gross amount is located in a different cell, simply replace 'A2' in the formula with your specific cell reference.

Solution 2

Calculate 60% of the Extracted VAT Value

Use this formula when you need to calculate a specific portion (60%) of the VAT amount you just extracted.

Solve Accounting Formulas with WPS Spreadsheet

Calculate VAT and Taxes Easily in WPS Spreadsheet

You can perform complex tax calculations, including extracting included VAT, effortlessly using WPS Spreadsheet. It supports all standard Excel formulas and offers a streamlined interface designed for accounting tasks.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your accounting workbook containing the gross amounts.
2. Enter the VAT formula: Select the adjacent cell and type =A2/105*5 to extract the 5% VAT.
3. Calculate the 60% portion: In the next column, type =B2*0.6 to find 60% of the VAT value.
4. Batch apply formulas: Hover over the bottom-right corner of the cells and drag the fill handle down to calculate the entire column instantly.

Fully compatible with Microsoft Excel (.xlsx) formats and formulas.Easy-to-use interface for fast accounting and data entry.Free and lightweight alternative to heavy spreadsheet software.Advanced drag-and-fill features for batch tax calculations.

microsoft office alternative - wps office

Frequently Asked Questions

How do I calculate a different VAT rate, like 20% included in the gross amount?

To extract a 20% inclusive VAT, modify the formula to divide by 120 and multiply by 20. For example, if the gross amount is in A2, the formula would be =A2/120*20.

Why am I getting a #VALUE! error when calculating VAT?

This error occurs if the cell containing the gross amount contains text, hidden spaces, or special characters instead of a pure numeric value. Ensure your gross amount cells are formatted strictly as Numbers or Currency.

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

To add a 5% VAT to a net amount (exclusive of VAT) to find the gross total, you simply multiply the net amount by 1.05. For example, use the formula =A2*1.05.

Excel & Data Problems