The formula
Gross from netExcelGoogle Sheets
=A2*(1+C2)In E2 → $120.00
Net from grossExcelGoogle Sheets
=B2/(1+C2)In E3 → $100.00
VAT included in grossExcelGoogle Sheets
=B2-B2/(1+C2)In E4 → $20.00
Common mistakeExcelGoogle Sheets
=B2*(1-C2)In E5 → $96.00
✓ Tested. All 4 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Net | Gross | VAT rate | ||
| 2 | 100 | 120 | 20% | $120.00 | |
| 3 | $100.00 | ||||
| 4 | $20.00 | ||||
| 5 | $96.00 |
How it works
- 1+C2
- 1.2 for 20% VAT.
- B2/(1+C2)
- Reverses the VAT that was added.
- B2-B2/(1+C2)
- The VAT portion; equals B2*C2/(1+C2).
Common errors and fixes
Net price too low?
B2*(1-C2) takes 20% of the gross, not the net (96 instead of 100).
Rate entered as 20?
Use 20% or 0.2.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.