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





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 .

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.


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

  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.

    • VAT is 20% in the UK, not 17.5%. It has been 20% since the Tories got back into power in 2009. It is one of the first things they did to generate more income for the Government coffers

  3. Hi,

    I raised invoice in Busy Software and this software tax calucation is very good when i put total amount 4000 in this software this software automatically get tax calculation when i print the invoice.
    I want know only for is any this calcuation is in excel or not and if yes so plz confirm me what is the formuls.

  4. VAT/Tax Amount
    •Select the cell D2, and write the formula to calculate the “VAT/Tax Amount”.
    •=B2-B2/(100%+C2)

    That formula cannot be correct. B2-B2?

Leave a Reply

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


6 + five =

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>