Skip to content

VAT · Excel formula

VAT calculation in Excel

The Excel formulas to add VAT to a net price, take the net amount out of a gross price, or find just the VAT amount are on this page, with the rate as a cell so any rate works. If you would rather not write them, give Nexell your list and say what you need. Rates here are examples only.

Free account · no card · 30 credits a month · Not ready to sign up? Paste some text and try it

Step by step

VAT in Excel: three formulas, one sentence

  1. 1

    Add VAT to a net price

    With the net price in B2 and the rate in C2 (for example 20%), the gross price is =B2*(1+C2) and the VAT amount alone is =B2*C2. With a fixed rate you can write =B2*1.2. A net 1,000 plus 20% VAT is 1,200, of which 200 is VAT.

  2. 2

    Take the net price out of a gross price

    Divide the gross price by one plus the rate: =B2/(1+C2). To fix the cents, round it: =ROUND(B2/(1+C2),2). A gross 1,200 at 20% VAT has a net price of 1,000.

  3. 3

    Find the VAT inside a gross price

    Subtract the net price from the gross price: =B2-ROUND(B2/(1+C2),2). Net plus VAT then equals the gross price to the cent.

  4. 4

    Or type it to Nexell

    Paste the list, or upload a PDF, Excel or CSV file, and write “split the gross amount into net and VAT”. AI understands the request and Nexell writes the formulas. If the rate is in neither your sentence nor the table, Nexell asks for it instead of assuming one; the question costs no credits.

Example

A mixed-rate expense list, split into net and VAT

We pasted a small business's monthly expenses, gross amounts at three different rates, into the tool.

1Pasted list

2In Nexell

  1. List read: 5 columns, 6 rows. Dates came in as dates, rates as percentages and amounts as numbers.
  2. split the gross amount into net and VATNet and VAT columns added (live formulas, each row at its own VAT Rate)
  3. add a total rowTotal row added, using SUBTOTAL so it follows your filters

Each request came back as a preview first and changed the table only after Apply.

3In Excel

F2fx=ROUND(E2/(1+D2),2)
The Data sheet of the downloaded Excel file
ABCDEFG
1DateSupplierDescriptionVAT RateAmountNetVAT
22026-09-03Stationery shopCopy paper (10 boxes)20%2,460.002,050.00410.00
32026-09-05RestaurantTeam lunch10%1,870.001,700.00170.00
42026-09-08BakeryBread5%420.00400.0020.00
52026-09-12Stationery shopToner cartridges (2)20%3,840.003,200.00640.00
62026-09-19RestaurantMeeting catering10%1,320.001,200.00120.00
72026-09-26BakeryBread5%273.00260.0013.00
8Total10,183.008,810.001,373.00
DataChart

Net and VAT are live formulas; G2 is =ROUND(E2-F2,2). Change a rate or an amount in Excel and the net, the VAT and the totals follow. Rates are percentage-formatted numbers and dates are real Excel dates.

Illustrative example with made-up suppliers and figures; the formulas shown are the kind Nexell writes into the downloaded file.

Why Nexell

What Nexell does differently

You choose the rate

Nexell does not know which rate applies to which goods or services and does not guess. Give the rate in the sentence or keep a VAT Rate column; if neither exists it asks, at no cost.

Each row at its own rate

With a VAT Rate column, every row is split at the rate on that row. You do not have to filter by rate and write separate formulas.

Net and VAT that add up

The net amount is rounded to two decimals and the VAT is the gross amount minus the net, so on every row net plus VAT equals the gross amount exactly.

Limits

What to know before you start

  • You decide the rates. Nexell does not know which VAT rate applies to which goods or services and gives no tax advice; give the rate in the sentence (20%) or as a column.
  • Withholding and reverse-charge rules are not calculated. There is no rule for requests such as “apply 50% withholding”; compute those amounts with your own formula.
  • Privacy. Personal data in text is masked before it is sent to the AI service (what is sent).

FAQ

VAT calculation in Excel: common questions

How do I remove VAT from a gross amount in Excel?

Divide the gross amount by one plus the rate: with the gross amount in B2 and the rate in C2, the net price is =B2/(1+C2) and the VAT is =B2-B2/(1+C2). For example, a gross 1,200 at 20% VAT is a net 1,000 plus 200 VAT. Wrap the net in ROUND(…,2) to fix the cents.

What is the formula to add VAT in Excel?

With the net price in B2 and the rate in C2, =B2*(1+C2) gives the gross price and =B2*C2 gives only the VAT. In Nexell, “add 20% VAT to the amounts” does the same job and adds the column as a live formula.

What if different rows have different VAT rates?

Put the rate in its own column, such as 20%, 10%, 5%. With the amount in B and the rate in C, the net price is =ROUND(B2/(1+C2),2) and the VAT is =B2-ROUND(B2/(1+C2),2). In Nexell, if the table has a VAT Rate column, “split the gross amount into net and VAT” splits each row at its own rate.

How many credits does a VAT calculation cost?

Turning text into a table and each applied request cost 1 credit per 1,000 rows; every PDF page read is 1 credit. The Free plan gives 30 credits a month and 3 downloads a month, no card needed. A request that is not understood uses no credits.

Stop writing VAT formulas row by row.

Create a free account, bring your data and type what you need.

Try it free