Easily calculate the amount of a product with or without VAT, the value-added tax whose percentage will depend on the nature of the product itself. Thanks to the VAT calculator you will be able to know how much a product costs with or without this tax in a quick way.
How to calculate VAT
For calculate the VAT, we must consider two different assumptions: the amount that already has VAT applied and the price that does not yet have VAT but we want to apply it. Let's see how VAT is applied in each particular case:
Calculate the VAT of a total
Imagine that you have bought an iPhone costing 699 euros with VAT already included and you want to know how much of that amount goes to value added tax. In this case, you should apply the following formula for calculating the tax base:
Price excluding VAT = Price with VAT / (1 + VAT)
In the case of the example, it would be like this:
Price excluding VAT = 699 euros / (1 + 0.21) = 577.69 euros.
Please note that VAT is expressed in decimal format and not as a percentage. If you have doubts about how to pass a percentage to decimalClick on the link we have just left you.
If you would like to to know the amount of VAT you are paying for the product, you simply have to apply this other formula:
VAT = price with VAT - price without VAT
Adding VAT to a price that does not include it
If you have a amount to which you want to apply VAT because it does not have it yet, then you must apply this other formula:
Price with VAT = price excluding VAT + (price excluding VAT * VAT)
For the sake of clarity, let's imagine that we have a price of 577.69 euros to which we want to apply VAT. Following the formula above we have:
Price incl. VAT = 577,69€ + (577,69€ * 0,21) = 577,69€ + 121,31€ = 699€.
We have already added VAT to a price that did not have it.
Apply VAT in Excel
The above process can be automated in Excel to apply VAT following the theory that we have seen in the previous point and applying the steps that can be seen in the video that we have recorded and in which we have dedicated a cell to each of the variables to fully automate the calculation of VAT in Excel.
Again, we have two different situations in which we will have one price with VAT already applied and another without.
Add VAT in Excel to a price that does not include VAT
If you already have the subtotal or price without VAT and want to know how much tax to add, apply this formula in one of the cells of your spreadsheet:
= price excl. VAT + (price excl. VAT * %)
Remember that you can directly write the quantities you want inside the formula or make each variable point to a cell as we have done in our video example.
Calculate VAT in Excel at a price that already has it
To obtain the price excluding VAT in Excel of a price that already comes with the tax applied, you must write the following formula:
= price with VAT / (1 + %)
Since last September 1, 2012, Spain faced an increase in the value added tax, better known as VAT. Thus, we have the following VAT classification:
- General21% applies and is generally carried by all products and services.
- ReducedVAT: he adds a 10% for VAT. In this case, food, transportation services, and plants usually have this reduced VAT rate applied.
- Super-reduced4% of VAT. The cheaper or less painful variant can be found in staple products such as bread, milk, fruits, vegetables and a number of other items.
Applying one VAT rate or another depends on the type of product being traded. For example, a loaf of bread does not have the same VAT rate as a vehicle.
To find out what VAT rate to apply In specific cases, the Tax Agency has made available to citizens the following information a PDF document in which we can clear our doubts.
Thanks to our VAT calculatorIf you have a VAT tax, you can easily calculate the VAT of a product or know how much you are allocating to the collection of this tax with a given purchase. For example, if you buy a 64 GB iPhone 6s for 869 euros, of that amount, 182.49 euros are for value added tax.