You hold a receipt for 121 and your accountant asks for the base and the VAT separately. If you subtract 21% from the total, the base comes out wrong and the invoice does not match what was declared.
How to use Sales Tax Formula step by step
1. Choose the operation
Add tax to a price, work out the tax alone, remove it from a total that already includes it, see how much tax a total contains, deduce the rate or add up a column with tax.
2. Enter amount and rate
The rate goes in as a number, for example 21 for 21%, or as a cell. Each country has its own, so enter the one that applies to you.
3. Copy the formula
Paste it into your sheet and drag it down the price column. If the rate sits in a cell, anchor that reference with $.
The Step Count: Traditional Friction vs. KorexTools
Separating VAT from a total on a calculator (4 steps)
- Divide the total by one plus the rate.
- Note the result as the base.
- Subtract the base from the total.
- Repeat for every receipt of the month.
With the KorexTools “Sales Tax Formula” builder (3 steps)
- You pick whether to add, remove or deduce.
- You enter amount and rate.
- You copy the formula and drag it down the column.
Removing the tax is not subtracting the percentage: you divide by one plus the rate
Adding tax is direct: multiply the price by one plus the rate. The way back is what misleads. If a total of 121 includes 21%, the tax is not 21% of 121 but 21% of the base, so to recover it you divide by 1.21. Subtracting 21% from the total gives a wrong base.
Once you have the base, the tax contained is simply the total minus the base, and the rate applied follows from the quotient between the price with and without tax. The builder gathers these six operations so you do not have to remember which one takes multiplication and which division, and lets you enter the rate of any country.
Which operation you need
- •I have the base and want the total: multiply by one plus the rate.
- •I have the total and want the base: divide by one plus the rate.
- •I want to know what tax that total carries: total minus base.
A 21% rate with 100 in A2 and 121 in B2
| Operation | Formula | Result |
|---|---|---|
| Price with tax | =A2*(1+21/100) | 121 |
| Base from the total | =B2/(1+21/100) | 100 |
| Rate applied | =(B2-A2)/A2 | 0.21 (21%) |
Common Mistakes and How to Avoid Them
- ⚠️Subtracting the percentage from the total instead of dividing.
- ⚠️Applying the rate to a price that already included the tax.
Frequently Asked Questions
Which tax rate should I enter?
The one that applies to your country and product type. It changes by place, so check it on your tax authority’s site; the tool only calculates with the one you type.
Does it work for other taxes?
Yes. It works with any tax that is a percentage on the base, such as VAT, a sales tax or a fixed service charge.
Why does my total not match to the cent?
Because each line may be rounded separately. Calculate the tax on the total of the base and round at the end so it matches the invoice.
What I would do if I were in your shoes
«Before reconciling an invoice, confirm whether the starting price already carries tax: it is the costliest mistake and the most frequent.» — DataKorex Engineering Team.