How to calculate VAT / Tax and net sale price in Microsoft Excel





This formula you can use to calculate how much VAT has been paid when the only information you have is the total amount.

Let’s take an example and understand how to calculate VAT/Tax and net sale price in Microsoft Excel 2010 & 2013.

I have a table in range A1:C6, in which column “A” is containing the product details, column “B” is containing sale price and column “C“ VAT/Tax percentage.

In Column “D”, we need to calculatethe “Net Sale Price” and in column “E” the “VAT/Tax” amount.

img1

 

To calculate the “Net Sale Price” and “VAT/Tax” amounts follow the below mentioned steps:-

 

Net Sale Price

  • Select the cell D2, and write the formula tocalculate the “Net Sale Price”.
  • =B2/(100%+C2)
  • Press Enter.
  • The function will return the “Net Sale Price”.
  • To return the “Net Sale Price” for the rest of the products copy the same formula.
  • To copy the formula, select cell D2, copy formula by pressing the key “CTRL+C” and paste into the rest of the cells by pressing the key “CTRL+V” on your keyboard.

img2

 

VAT/Tax Amount

  • Select the cell D2, and write the formula to calculate the “VAT/Tax Amount”.
  • =B2-B2/(100%+C2)
  • Press Enter.
  • The function will return the “VAT/Tax Amount”.
  • To return the “VAT/Tax Amount” for the rest of the products copy the same formula.
  • To copy the formula, select cell D2, press the key “CTRL+C on the keyboard” and paste into the rest of the cells by pressing the key “CTRL+V”.

img3

 

To remove the formula from the data use the “Paste Special” option:-

  • Select the range D2:E6, Copy by pressing the key “CTRL + C”.
  • Right click on the mouse and select “Paste Special”.
  • In the dialog box select values and click on ok.
  • The formula will be replaced by values.


5 thoughts on “How to calculate VAT / Tax and net sale price in Microsoft Excel

  1. “100.00-18% = 82.00 NOT 84.75

    Try to find the net price:
    A1 100.00 B1 18.00% then try the formula=(100%-b1)*al answer 82.00 “

    • You are wrong. you cannot minus the 18% vat/tax directly to the gross amount. 18% is only added into net amount to get the gross price. the tutorial is correct.

      100.00 = gross sales
      18% = vat

      getting net sale price: 100.00/1.18 =84.75
      getting the VAT: 100.00/1.18*0.18 =15.25

  2. Tip is great. We all know how to add VAT onto the basic price, but I hadn’t a clue where to begin doing it the other way around. By the way – VAT is 17.5% in the UK.

Leave a Reply

Your email address will not be published. Required fields are marked *


nine + = 13

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>