How to calculate VAT in a Microsoft Excel accounting spreadsheet? step by step guide

Today this software belonging to microsoft-office It is used all over the world for perform different types of tasksespecially those related to databases and numerical calculations. This is because this program offers a great variety of functions and formulas that will allow you to carry out all this type of activities.

Due to its excellent performance at the time of perform calculations, Excel He has become the right hand of many companies and financial and educational institutions. This is how on many occasions they should complete tasks or projects where should calculate the VAT applied to all types of products.

Therefore, this microsoft app offers you the necessary tools so you can start calculate the tax established by each of the governments of the world for what are the percentages of products and services. That is why here we are going to teach you how calculate VAT in a spreadsheetto do this, follow in detail everything that we will teach you below:

What is VAT or value added tax and how does it affect your Excel accounts?

VAT or value added tax as known throughout Latin America or value added tax in the case of Spain, It is an indirect rate that must be calculated compulsorily on the consumption of services, transactions, products and imports. So it is considered as a indirect tax on consumption.

In other words this means a percentage increase to what he is final price of each of the products you buy or the services you hire. Therefore, every time you buy a product in a store it ends up canceling what is the value of the item plus the percentage of VAT that is applied to its price.

It should be made clear that the person sell the good or provide the service they do not end up staying with this percentage added to the final price, but this Tax must be declared to the government. So every person who declare VAT must maintain a balance between all the invoices that you acquire plus all those that you provide, all this is known as deductible expenses.

All this means that VAT is an indirect tax which is applied on the company’s sales production costs and that they are not going to affect at any time the final income of the company.

This begins to be exercised once the person becomes self-employed or professional and carry out a commercial projectwhere sooner or later will have to create invoices so you should apply VAT on your services. However, on many occasions when you want calculate the VAT of a certain product or service it is not entirely clear how run the procedure. Excel has become one of the best tools to calculate it easily.

That is why here we are going to teach you the main steps so that you can calculate it with the help of this Microsoft program.

Learn how to calculate the VAT of your data tables in Excel correctly

Can have the necessary knowledge to be able to calculate this tax in Excel is very importantespecially if you work in a business or company where do you drive its budget or even because it was sent to you as an assignment from your university.

It is important remember that VAT It’s a tax they owe cancel all consumers when making any legal business transaction. It is also worth mentioning thatThe rate of said tax will depend on each country, can most of them drive a rate totally different from the others.

Therefore, to calculate this data from Microsoft Excel you will have to perform the following steps:

Calculate VAT for a single value

• The first thing will be to enter the price of the product or service in one of the spreadsheet cells.

• Next enter the VAT rate in another cellbut this time in form percentage.

• Now you must do cell value multiplication to achieve the amount of VATfor this in the function section you must place the formula: =B1*B2note that these values ​​depend on the cells you are using.

• In order to get the total amount of the price of the product you should add the cell that contains the price of the same Y add the amount of VAT, in this case you must apply this formula: =B1+B3.

This way you can calculate the tax in a very simple and easy way for the value of a single product.

Next we will see how to calculate it for several products:

Calculating tax for a product listing

another form of power calculate VAT in Excel and perhaps one of the most common when working on the programis to calculate it for a product listingwhere there is a need for calculate VAT for all of them.

According to this, here we show you how to carry out this procedure, to do so follow each of these steps:

• To carry out this procedure it is necessary to have a list of products or services as shown on the screen. As you can see, this listing is made up of 15 products where each of them has its specified price.

• In the first column they are all product names and in the second their respective price. Now in the third column you must add the formula to be able to calculate the VAT of each of themwhile in the fourth column you must calculate what will be the total amount. Finally in the column F2 you can see the “VAT rate” in this case it is from 16%.
• East percentage value will make the one used for each of the calculations that are they will perform. The formula to get the VAT value is as follows: =B2*$F$2.
• has been used the $ symbol to refer to cell F2this is done this way because the objective is that said value in the formula remain fixed at the time of copy the formula down. Once the function is applied, it will be obtained the following result.

• once obtained the first result of the column, now the next thing will be copy the formula down to achieve VAT calculation from everyone else listing products. To do this, click on bottom corner of the cell and move it to the last product in the row.

• Now we must calculate what is the total amountyou must remember that the total it is not more than the value of the product plus the percentage of the tax. Therefore, in this case, the following formula must be applied: =B2+C2, and once the first result is obtained, the following will be scroll the cell down to get the other values.

The formulas that are used in the program make use of absolute references, This means that it can be modified the VAT rate located in cell F2 and automatically the system will modify all the values ​​with respect to the new percentage that has been established.

Calculate the total amount without calculating VAT

In the methods mentioned above, it was calculated the total amount after having VAT calculatedin this case we are going to show you a formula So you can calculate total amount without the need of include VAT cellwhich will allow you to obtain the result in a much more direct way.

This formula is based on the fact that multiply a value by a percentage greater than 100% will be adding to the original value the percentage that is greater than 100%. An example of this is if the VAT rate is 10%, then the value should be 110%.

In this case, multiply a value by 116%, so it is understood that the value of percentage to increase is 16%. For this case the value of the VAT rate is found in cell E2in order to calculate the total amount of each of the products on the list, the following formula must be applied: =B2* (1+$E$2).

Because percentages are a decimal value and 100% equals the number 1in this case it must be done the multiplication of the price of the product by the number 1 plus the percentage that is indicated in cell E2. Once the formula is applied, the result will be obtained. shown in the picture.

As in the previous case, it was use the $ symbol, but this time for me to do reference to cell E2, thus allowing the same remain fixed when applying it to the other cells of the list. Once scrolled down, the following results will be obtained.

This way you can start getting the total amount of each of the products in a much more direct way. You can compare this table with the one in previous method and verify that the result of the final price of all the products is the same.

What to consider before calculating VAT in an Excel spreadsheet?

when you want calculate VAT on the price of a product or serviceit is very important that you take into account various aspects, one of them is the VAT rate that will be added to the cost of each of the products. Furthermore, in the case of doing this mathematical operation in Excelyou must keep in mind what are the formulas you need to useas well as the location of each of the data in the excel cells.

Note that this value obtained from the tax should simply be added to the actual value of the product, namely, if the product costs 100 eurosand the value of the tax is 10 euros, then this item will have a final cost of 110 euros.

As already mentioned above, this difference is not kept by the seller, but must be declared before the governmental federations dedicated to this tax.

VAT in the main Spanish-speaking countries. What is the percentage in each one?

Just as it has been mentioned before in the post, each country has its own VAT value, so it varies according to government laws. However, even though each country handles a different valueit is important to clarify that the calculation to determine the value of the same on some product or service is carried out in the same way, this means that it will always be calculated the same in all cases.

This value must assume all peoplebecause every time you make a buy in a legal shop that billsin each of your items this value will already be added to the amount. So you will end up canceling a increase to what is the real amount of the product.

According to this, here we show you the value of the tax in the main Spanish-speaking countries: