How to Calculate VAT in Microsoft Excel

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/other-qa-formulas/how-to-calculate-vat-tax-and-net-sale-price-in-microsoft-excel.html">
SHARE




In this article you will learn how to calculate VAT in Microsoft Excel by using the functions? And  Which formula we 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 calculate the “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

 

In this way we can calculate VAT in Microsoft Excel.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at [email protected]
 

 
 

Please follow and like us:
261


16 thoughts on “How to Calculate VAT 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.

    • 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?

    • Hello,

      As per the given example, it seems you are keeping 11.11% margin between selling price and MRP. If yes, assuming you have selling price (900) in A2 and you want MRP in B2. Try the following function

      =ROUNDUP(A2+A2*11.11%,0)

      Please let us know if it works!

      Thanks,
      Team Excel Forum

  5. Subtotal-A: 133.80
    Subtotal-B: ( Add 2% on Subtotal-A/ A1) : 136.48
    Subtotal-C: 10% Profit ( Add 10% on Subtotal-B) : 150.12
    VAT 5.5% of Total 9.12
    IT 4% of Total 6.64
    Total 165.88
    What is the formula of this rate

    I want how a river 150.12 to 165.88

  6. Subtotal-A: 133.80
    Subtotal-B: ( Add 2% on Subtotal-A/ A1) : 136.48
    Subtotal-C: 10% Profit ( Add 10% on Subtotal-B) : 150.12
    VAT 5.5% of Total 9.12
    IT 4% of Total 6.64
    Total 165.88

    I want how a arrive from 150.12 to 165.88
    Paz send me an email

Leave a Reply

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


1 × 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>