How much VAT is inside a gross amount? (ROUND)
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
The problem
Problem statement
The invoice total in B1 already includes VAT at the rate in B2. Put the VAT portion in the VAT cell D2, rounded to two decimals.
The rate applies to the net amount, not to the gross — dividing the gross by (1 + rate) is the whole trick.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | |
|---|---|---|---|
| 1 | Gross amount | 1190 | VAT inside the gross |
| 2 | VAT rate | 0.19 |
Notes
VAT inside a gross figure is gross − gross ÷ (1 + rate). Multiplying the gross by the rate overshoots by the VAT on the VAT, which is the error auditors look for. Round to cents because the split must add back to the gross exactly.
How to approach
Your answer goes in D2.
- Target cell is D2
- Take the VAT out of the gross, do not add it on
- Net = gross / (1 + rate)
- Shape: =ROUND(? - ?/(1+?), ?)
Function guide
FAQ
How do I extract VAT from a gross amount?
Net = gross ÷ (1 + rate); VAT = gross − net. Never multiply the gross by the rate.
Why is my VAT too high by a little?
You applied the rate to the gross, which charges VAT on the VAT. Divide by 1 + rate first.