How to calculate vat calculation in Excel

Follow by Email

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 VAT calculator allows to calculate VAT percentage in easy way .

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.

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

Net Sale Price

img1img2
    • 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.

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”.

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.

Please follow and like us:

Source: www.exceltip.com

Category: Bank

Similar articles: