Find the VAT in a price that includes it
PremiumThe prices in column B include 25% VAT. In C2, work out how much of the printer's price is VAT: the price minus the price before VAT, B2-B2/(1+$F$1).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Price incl. VAT | VAT | VAT rate | 0.25 | ||
| 2 | Printer | 1000 | |||||
| 3 | Monitor | 2250 | |||||
| 4 | Keyboard | 375 | |||||
| 5 | |||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Premium lesson
Unlock Percentages and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:IFAND and OR
Need help?
Why this works
The printer costs 800 before VAT, and 25% of 800 is 200, which makes 1,000. So the VAT is 200 of the 1,000, and that is 20% of the total, not 25%. Dividing by 1.25 finds the 800, and subtracting it from the total leaves the VAT.
Common mistake: Taking 25% of the total, B2*F1. That gives 250, because the VAT was 25% of 800, not of 1,000.
The answer
- C2
=B2-B2/(1+$F$1)
Subtract the price before VAT from the total, =B2-B2/(1+$F$1). For a total of 1,000 at 25% VAT, that is 200.
The 25% was added to the price before VAT, not to the total. At 25% VAT the tax is 20% of the total, and at 20% VAT it is one sixth.