Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →The correct Excel formula depends on whether your starting amount is before VAT or already includes VAT. For a VAT-exclusive amount, multiply by the rate. For a VAT-inclusive amount, divide by 1 + rate to find the net amount, then derive the tax.
Choose the formula that matches your amount
| Known amount | Result needed | Excel formula |
|---|---|---|
| VAT-exclusive (net) | VAT amount | =Net*Rate |
| VAT-exclusive (net) | VAT-inclusive total | =Net*(1+Rate) |
| VAT-inclusive (gross) | VAT-exclusive amount | =Gross/(1+Rate) |
| VAT-inclusive (gross) | VAT amount | =Gross*Rate/(1+Rate) or =Gross-Net |
Net or VAT-exclusive means the amount before tax. Gross or VAT-inclusive means the net amount plus VAT. Enter a rate as a percentage such as 20%, not the whole number 20. These formulas perform arithmetic; the applicable rate, taxable base, exemptions and invoicing rules depend on the relevant jurisdiction and transaction.
Method 1: Calculate VAT from a net price
Set up the worksheet
| Cell | Label | Example value or formula |
|---|---|---|
| A1 | Net price (VAT-exclusive) | |
| B1 | VAT rate | |
| C1 | VAT amount | |
| D1 | Gross total (VAT-inclusive) | |
| A2 | 100 | |
| B2 | 20% | |
| C2 | =A2*B2 |
|
| D2 | =A2+C2 |
With 100 net and a 20% rate, C2 returns 20 and D2 returns 120. The shorter total formula is =A2*(1+B2). Excel formulas begin with = and use * for multiplication; Microsoft documents these operators and cell-reference practices in its Excel calculator guidance and formula overview.
Copy the calculation down
Enter the formulas in the first data row and drag the fill handle down. Relative references such as A2 and B2 become A3 and B3 in the next row.
#1 Best Overall
- 【Calculator with Tax Function】Two line display tax calculator can calculate VAT, simplify the tax computing process, and provide users with convenient tax computing services.calculators including 4 basic calculations, %, GT,M+,M- and other multi-functions buttons.
- 【Desk Calculator with History】Calculators features a 120-step calculation history, making it easy to handle lengthy numerical operations.you can quickly find and display a previously entered number or calculation result. This is useful for situations where you need to frequently review previous data or perform proofreading.
- 【2-line Display Calculators】Desk calculator with 12-digit LCD big display can display two lines of information at the same time, including real-time input or calculation results and the input or calculation history of the previous step. This design not only optimizes the calculation process, but also significantly improves calculation efficiency and result accuracy.
- 【Dual Power Basic Calculator】Calculators is powered by solar and AAA batteries, it is not affected by power interruptions during use, it has an auto shut-off function, and the non-slip pad on the bottom ensures that the tax calculator is placed on the desktop stably and is not easy to slip.
- 【Large Button Calculator】Large display calculator is designed with large ABS plastic buttons that are comfortable to touch and responsive, it reduces typing errors and the large 12-digit HD screen is easy to read, Particularly suitable for seniors, including those working in offices,stores, markets, etc, accountants, sales or other business people.
Method 2: Extract VAT from a gross price
Do not multiply a VAT-inclusive total directly by the rate. At a 20% rate, VAT is 20% of the net base, not 20% of the gross total.
Worksheet formulas
| Cell | Label | Example value or formula |
|---|---|---|
| A1 | Gross price (VAT-inclusive) | |
| B1 | VAT rate | |
| C1 | Net price | |
| D1 | VAT amount | |
| A2 | 120 | |
| B2 | 20% | |
| C2 | =A2/(1+B2) |
|
| D2 | =A2-C2 |
For a gross amount of 120 at 20%, C2 returns 100 and D2 returns 20. The direct VAT-only formula is =A2*B2/(1+B2). Microsoft Q&A shows the same divide-then-subtract approach for a gross amount; that community answer is mathematical guidance, not tax-law advice: Excel and 20% formula.
Use an editable VAT-rate cell
If one rate applies throughout a sheet, place it in a dedicated cell such as B1 and lock that reference when copying formulas:
Rank #2
- LARGE EIGHT-DIGIT DISPLAY – Clear and easy-to-read 8-digit display, perfect for everyday calculations and ensuring accurate results in home or office settings.
- TAX & CURRENCY EXCHANGE FUNCTIONS – Effortlessly handle tax calculations and convert home currency to other currencies for easy financial management.
- GENERAL PURPOSE CALCULATOR – Ideal for a wide range of applications, from basic math to business and personal use, with memory keys for quick storage and recall.
- USER-FRIENDLY KEYBOARD – Easy-to-use layout, featuring square root, percent calculation, and simple functions that make it perfect for everyday tasks.
- COMPACT & PORTABLE DESIGN – Space-saving design that fits easily on any desk or in a briefcase, making it ideal for both home and office use.
- VAT from net:
=A2*$B$1 - Gross total:
=A2+C2 - Net from gross:
=A2/(1+$B$1) - VAT from gross:
=A2*$B$1/(1+$B$1)
The dollar signs make $B$1 an absolute reference, so it stays fixed as the formula is filled down. This is easier to maintain than repeating 20% in every formula.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallRound currency without losing reconciliation
If your worksheet displays currency to two decimal places, you can round the calculation explicitly:
- VAT from net:
=ROUND(A2*B2,2) - Gross total:
=ROUND(A2+C2,2) - Net from gross:
=ROUND(A2/(1+B2),2) - VAT from gross: calculate net in C2, then use
=ROUND(A2-C2,2)
Whether to round each line, a VAT subtotal or only the final invoice total can be governed by local rules, accounting policy or invoicing software. Rounding intermediate values independently can create a one-cent difference, so follow the policy used for your records.
Rank #3
- 【Advanced Tax & Business Functions】Quickly set tax rates with dedicated TAX+/- keys and simplify budgeting via GT, MU, and memory keys (MRC/M+/M-). These tools enable seamless tax calculations and customizable rate settings for precise, tailored results—ideal for accountants, business owners, and professionals requiring accurate tax computations.
- 【Visualized Operation Symbol Display】When you would like to calculate the number, it will express the symbol (plus, subtract, multiply, or divide) on the screen. It can help you see the operation steps when you operate continuously.
- 【Efficient Dual Power System】The calculator uses high conversion rate solar panels, and solar power can be used in well-lit areas. At the same time, we provide a matching battery double safe uninterruptible power supply. The calculator will shut down automatically after 8 minutes without operation.
- 【Small Size, Large Display & Premium Build】It features a large LCD display with a 30° angled screen for clear visibility, paired with a high-grade metal panel and durable ABS buttons. The ergonomic button curvature aligns with natural finger movements, reducing errors and enhancing efficiency.
- 【Durable Construction and Portability】 Made of high-grade metal panel and durable ABS buttons, this 12-digit calculator is lightweight yet durable, capable of withstanding multiple impacts from desktop height. The large, clear LCD Display offers crisp, easy-to-read numbers, minimizing eye strain and ensuring professional-grade performance whether at home, in the office, or while traveling.
Handle discounts, charges and mixed rates
Discounts and additional charges
Where a discount is applied before VAT and the resulting amount is taxable, the calculation can be represented as:
- Taxable net:
=Net-Discount - VAT:
=TaxableNet*Rate - Gross total:
=TaxableNet+VAT
Shipping, handling and other fees may form part of the taxable supply in some jurisdictions and transactions. Do not assume a universal treatment.
Recommended Free Tools
Different rates on one invoice
Use one row per item rather than applying one rate to the entire invoice:
Rank #4
- Check your calculations thanks to the calculator's inbuilt serial impact roller printer that enables you to monitor your inputs and retains ongoing records. This two-color printer with a four-key memory prints red and black ink at up to 2.3 lines per second onto the included roll of paper.
- Printing calculator offers 12-digit LCD display for convenient viewing. 4-key memory keeps often-used figures accessible for faster calculations. Clock and calendar functions help maintain schedules.
- Easy-to-use solution for all of your basic math needs. Streamline financial calculations with currency conversion, tax calculation, and item counter functions. 1-year manufacturer limited warranty.
- Dimensions: 2.2"H x 6.4"W x 9.1"D. Package content: AC adapter, paper roll, user manual.
- Powered by any standard AC outlet, eliminating the need for expensive batteries. Decimal switch, rounding switch, percent, sign change, backspace, double zero, and grand total functions help you solve a variety of mathematical problems.
| Item | Net amount | VAT rate | VAT amount | Gross amount |
|---|---|---|---|---|
| Item A | 100 | 20% | =B2*C2 |
=B2+D2 |
| Item B | 50 | 5% | =B3*C3 |
=B3+D3 |
| Total | =SUM(B2:B3) |
=SUM(D2:D3) |
=SUM(E2:E3) |
Excel’s SUM function and AutoSum can total ranges; see Microsoft’s ways to add values in a spreadsheet. Zero-rated, exempt and outside-scope items can have different reporting consequences, so classify them using the terminology and rules of the applicable tax authority.
Conditional VAT flags and rate lookups
For a simple worksheet flag, where E2 contains “Yes” or “No” and VAT applies only when it says “Yes”, use =IF(E2="Yes",A2*B2,0). For category-based rates, a lookup table is generally easier to maintain than nested conditions. Microsoft Q&A provides implementation examples using XLOOKUP and VLOOKUP, but those examples do not determine the legally correct rate: Calculating VAT using IF formula.
Common errors and fixes
- Entering 20 instead of 20%: If B2 contains 20, use
=A2*(B2/100). If it contains 20%, use=A2*B2. Thus=100*20returns 2,000, while=100*20%returns 20. - Applying the rate to a gross amount: For extraction, use
=A2*B2/(1+B2), not=A2*B2. - Mixing bases: Label columns explicitly as net or gross; an unlabeled “Price” column is ambiguous.
- Formula displayed instead of result: Confirm the entry starts with
=, multiplication uses*rather thanx, and the cells contain numbers rather than text. - Unexpected results: Check parentheses, number formatting, and that Excel is set to automatic calculation.
- Regional settings: Decimal and function-argument separators vary by locale; some installations use semicolons instead of commas in functions such as
IF.
Microsoft’s troubleshooting guidance covers parentheses, arguments, data types and calculation settings: How to avoid broken formulas in Excel. The basic arithmetic works in modern desktop Excel and Excel for the web, although interface labels and regional separators can differ.
Formula reference
| Purpose | Formula |
|---|---|
| VAT from net | =Net*Rate |
| Gross from net | =Net*(1+Rate) |
| Net from gross | =Gross/(1+Rate) |
| VAT from gross | =Gross*Rate/(1+Rate) |
Excel supplies the arithmetic only. Confirm the applicable rate, taxable base, treatment of discounts and charges, exemptions, cross-border or reverse-charge rules, and rounding method with the relevant tax authority or a qualified adviser.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
