Taxes are monetary tributes that taxpayers have to pay to the public administration, these are mandatory and must be paid by the subjects provided by law. The value added tax, better known as VAT, is an indirect tax imposed on goods, services and products in general.
The main objective of this tax is to tax the income of freelance companies and professionals, so a good idea is to know how to calculate VAT automatically using Excel . This way you will be able to keep an exact record of the tax generated by each product or service you offer.
Index( )
- What is the procedure to calculate the VAT of a product in my Excel table?
- with the percentage
- Using decimals
- How can I get the VAT of several products at the same time in a spreadsheet?
- with decimals
- Using the percentage
- How can I add the VAT of my table automatically?
- How can I automatically subtract the VAT value from the values in my table?
What is the procedure to calculate the VAT of a product in my Excel table?
Implementing Excel to know 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 having to waste time. Below we will indicate two ways that will allow you to obtain VAT with Excel.
with the percentage
This is the easiest way 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 the product, tax base, VAT rate and the total by cells .
In the case of the cell where the VAT rate is, it is important that you place the value as a percentage, for example 10%, 16%, 21%. Then, in the column of VAT to be charged, you must stop and first select the tax base of the product and then multiply it by the VAT value (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 make the following formula. =12/100, that is, the rate of the value added tax, 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 tax base and VAT rate cells that we got and that’s it.
One suggestion is that if you intend to 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 get the VAT of several products at the same time in a spreadsheet?
To get the VAT of 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 will be able to verify it and make a backup in case of any problem.
To speed up the process, the ideal is to make this process automatic, the first thing is to prepare the table and in the upper row place the data of the products such as the name and code. Then enter this data, you can import that information if you have it in an existing Excel sheet, otherwise what you will have to do is enter the information manually.
with decimals
To get the VAT with decimals for 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 there is a cross that you must drag as shown in the figure; then multiply the taxable base cell and VAT rate in the position of VAT to be charged and drag again so that the entire table is automated.
Using the percentage
In the event 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.
Later 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 voila.
How can I add the VAT of 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 the VAT out 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 collected, go to the menu and click on start, in the upper right part look for modify “autosum” and when you select you will notice that a similar equation comes out =SUMA(E2:E21) .
This is indicating that the values that are between those two intervals or columns will automatically be added, so in case of changing the tax base or the VAT, it will be modified by itself without the need 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 is basically carried out when you have the price of the product for sale to the public and you want to break down the VAT from the tax base. To achieve this you will have to apply the following formula.
Select the cell where the price of the product is found and divide it by 1+12% , the latter being the VAT rate. The following image shows the formula more clearly. Then you must subtract that value from the retail price and you will know what the base and VAT are separately.
Finally, keep in mind when getting the VAT values in Excel that in some countries this rate varies and scales are even established according to the product or service. A clear example is Spain , where food considered essential has a VAT of 4% while the rest of the food can have up to 21%.