How to Calculate the VAT of a Product Using Excel – All the Tricks

Taxes are monetary tributes that taxpayers have to pay to the public administration, these are mandatory and it must be found out by the subjects that the law provides. The value added tax better known as VAT is an indirect tax that is imposed on goods, services and products in general.

The main objective of this tax is to tax the income of companies and professionals of free exercise, therefore a good idea is to know how automatically calculate VAT using Excel. This way you will be able to keep an exact record of the tax generated by each product or service you offer.

What is the procedure to calculate the VAT of a product in my Excel table?

Implementing Excel to know what is the VAT of a product is a simple task, if you have a small store you can apply this method to all products automatically without wasting time. Below we will indicate two ways that will allow you to obtain VAT with Excel.

With the percentage

This is the simplest way that exists to calculate VAT, for this you must know what the taxable base of the product is. Then open a spreadsheet in Excel and create a table like the one shown in the figure, in this you must indicate by cells the product, tax base, VAT rate and total.

In the case of the cell where the VAT rate is important that the value is placed in percentage, for example 10%, 16%, 21%. Then in the column of VAT to collect you must stop and to select first the tax base of the product and then multiply it by the value of VAT (which is expressed as a percentage). This way you will automatically obtain the amount of this tax.

Using decimals

To get the VAT using decimals, you will follow the previous procedure, the only difference is that in the VAT percentage cell you will not use the% and instead you will do the following formula. = 12/100 that is the value added tax rate you will write it in decimal, hence the name. In this case the value in the cell will be 0.12, if the percentage were 16 it would be 0.16, then simply multiply the cells taxable base and VAT rate that we take and that’s it.

One suggestion is that if you pretend use this table to run your business The ideal is to protect Microsoft files with a password and thus prevent someone from altering your data.

How can I extract the VAT of several products at the same time in a spreadsheet?

To extract VAT from several products at the same time, it is recommended that you first learn to duplicate the Excel sheets. Since by having various reference codes and prices linked, each sheet will have valuable information and by duplicating it you can verify it and make a backup in case of any problem.

To speed up the process, the ideal is to do this automatic process, the first thing is to prepare the table and in the upper row place the product data such as name and code. Then enter these data, you can import that information if you have it in an existing Excel sheet, if not, what you will have to do is place the information manually.

With decimals

To get the VAT with decimals to several products you only have to get the value of the tax in decimal as explained in the previous section. Then in the right corner of the cell a cross comes out that you must drag like shown in the figure; subsequently multiply the taxable base cell and VAT rate in the VAT receivable position and drag it again so that the entire table is automated.

By percentage

In the case that you want to extract the VAT of several products with Excel, repeat the previous procedure and in the VAT rate section, simply place the value of the tax in percentage and drag so that all the products have the VAT associated in the table.

Subsequently use the * to multiply the cells (VAT rate and tax base) and thus you will get the value you need. Again drag the formula to the entire table and that’s it.

How can I add the VAT from my table automatically?

The sum is within the basic functions of Excel, but in case you do not know how to do it, you should know that it is a very simple procedure.

When you finish taking out the VAT of each product, the only thing you have to do additionally is; put yourself at the end of the table and in the column of the tax to be charged, go to the menu and click on start, in the part top right look at modify “autosuma” and when selecting you will notice that a similar equation = SUM (E2: E21) comes out.

This is telling you that it will automatically add the values ​​that are between those two intervals or columns, So in case of changing the tax base or the VAT it will be modified only without having to do it manually.

How can I automatically subtract the VAT value from the values ​​in my table?

This procedure is known as inverse calculation. and it is basically done when you have the price of the product for sale to the public and you want to break down the VAT of the tax base. To achieve this you will have to apply the following formula.

Select the cell where it is located the price of the product and divide it by 1 + 12% the latter being the VAT rate, the formula is shown more clearly in the image below. Then you must subtract that value from the retail price and you will know what the base and the VAT are separately.

Finally, keep in mind when taking the VAT values ​​in Excel that in some countries this rate varies and even scales are established according to the product or service. A clear example is Spain that food considered essential has a VAT of 4% while the remaining foods can have up to 21%.