The Value Added Tax (VAT) is a tax that is applied directly to consumption and it is important to learn how to estimate it in different ways, either in Excel or with applications that facilitate its use for budget, tax and inventory issues. Today you will learn how to calculate VAT.
A tool that helps calculate and apply the different types of are spreadsheets. For this reason, we have prepared this article for you where we show you how to get VAT in Excel, apply it to different cases, as well as other simple ways to calculate it.
It’s important to note that although we use the popular Microsoft spreadsheet for the examples, they are all applicable to other platforms that have similar functionality, such as Google Sheets and Apple Numbers. Feel free to make the models with the one you prefer!
If you are finding out how to calculate VAT because you have a business, take the plunge and digitize it with an online store! Start selling online with Tiendanube:
How to calculate VAT?
In our article about we show you the basic information about this tax, how it is applied and what it is for. In case you want to go deeper, we recommend reading it.
Before we learn how to get VAT out of a price and get started with spreadsheets, let’s pick up from that article the formula with which we obtained the VAT manually to better understand how to apply it. So let’s do a quick review, is that okay with you?
If you want to know how to calculate VAT on a product, you need to know its base cost (before any taxes) and the current rate. In Argentina there are three rates: 21% is the general rate, 10.5% is for some capital goods and basic necessities, and 27% applies to activities such as electricity and telecommunications.
With these data, you have two paths:
- To know the amount of VAT that corresponds to a product that does not have it, simply multiply the base cost of the product by 1.21, in the event that it is the rate of 21%. For the reduced rate of 10.5 the figure will be 1.105 and for the increased rate of 27% it will be 1.27. In a simple formula, this would look like this:
(Base cost of the product) × (1.21) = Total with VAT applied - If you need the amount corresponding to the VAT of a product that already has it, it is necessary to divide its final price by 1.21, 1.105 or 1.27 to obtain the base price and then find the VAT. In a formula, it looks like this:
(Total cost of the product with VAT) ÷ (1.21) = Total without VAT
and then:
(Total product cost with VAT) – (Total without VAT) = VAT amount
With this review on how to calculate VAT on a price, let’s move on to the spreadsheet examples where it’s easier to explain step by step.
How to get VAT in Excel? examples to practice
Now yes, we are going to tell you how to get VAT in Excel. The 4 examples that we are going to use so that you learn how to calculate VAT in the spreadsheet are:
- Applied on base cost
- Broken down from base cost
- Before applying it to the base cost
- Calculation of VAT in price lists
📢 Tip: if you need a version of Excel to work in the cloud, you can use the suite with all the basic functions of the platform. It is free and only asks for an email to register.
In this case, we are focusing on getting VAT out of Excel but, as a general rule, remember that when it comes to spreadsheet procedures, the most important thing is to understand where the formulas come from and how they work in order to apply them correctly.
When you have this part clear, the organization of the columns can be anything, from sheets with simple calculations, to your billing or the .
So that each example is better understood and you learn how to calculate VAT in Excel with a formula, we place the concept of each amount on the left side. In cases where a formula applies, we put it on the right side so you can see it clearly.
Excel takes care of showing you with the colors blue and red the cells that are part of said formula.
📢 Tip: to enter a formula in Excel, type the equal sign “=” and select with the mouse each cell that makes up the calculation and the operation signs (+, -, *, /).
1. Calculate the VAT applied on base cost
- To get VAT out in Excel, I entered the base cost (before taxes) in a cell. In this case, the cell with the value 10,000 in B3.
- Next, set the VAT rate you want to apply. In Argentina it can be 1.21, 1.105 or 1.27. In the image you will see this figure in cell B4.
- Finally, apply the formula to multiply the previous two cells. The formula would be =B3*B4. This way you will get your total with VAT applied.
2. Calculate VAT broken down from base cost
- Start with your final cost (the one that already has VAT included). Here he was in cell B3.
- As in the previous example, set your VAT rate in the cell below. In our case it was the B4.
- The first formula in this calculation divides the final cost with the rate, that is, the previous two cells. The formula is =B3/B4.
- To calculate the VAT in Excel and obtain it broken down, it remains to subtract the base cost without VAT (B5) from the final cost (B3). In this case, the formula that gives us this result is =B3-B5.
3. Calculate VAT before applying it to the base cost
- When you need to calculate VAT before adding it to the base cost, as a subtotal, you start with the base cost without the tax. Here he was in cell B3.
- Set your VAT rate (in decimals or percentage) in the cell below. Here was the B4.
- First you have to calculate the VAT on the base cost, multiplying the two previous cells. The formula then becomes =B3*B4.
- To obtain our total with VAT, you add the base cost (B3) to the VAT that you just obtained in the previous step (B5) to the final cost. The formula for this calculation is =B3+B5.
📢 Tip: Did you notice that the VAT rate in the example above was marked as a percentage and not a decimal? In case it is easier for you to do it this way, you need to change the property of the number. This is done in the numbers tab at the top left of the Excel toolbar or by entering the direct number followed by the “%” symbol.
4. Calculate VAT on price lists
- You need to start with your list of costs in one column. Here it goes from cell A4 to A11.
- In cell A15, to facilitate this example, we put the VAT rate of 21%.
- In the second column we want to calculate the VAT corresponding to each product in the list, so we need to multiply each value in the list by our rate. This is achieved with the formula =A4*$A$16.
The “$” signs applied to cell A16 establish that this value will be fixed for the entire calculation. For now we leave it like that. - In order to get the total with VAT applied for your entire list, you need to add each base cost (from A4 to A11) and the VAT obtained for each product (what you will have in the second column between B4 and B11).
The formula to achieve this result is =A4+B4 and remains in C4. For now, we also leave it that way. - Since you have the two initial formulas for each column to calculate (B4 and C4), you have to apply the formula fill with Excel so you don’t have to do each formula manually. You do this by selecting the cell where your starting formula is (begins with B4) and marking down all cells parallel to your base costs, ie up to B11.
- Once checked, press the CTRL + D keys to generate the fill from the initial formula in the entire column from B4 to B11.
- Now, I repeated this last step (CTRL + D), but in the column that starts with the formula in C4 and goes all the way to C11.
- When you finish, you will have your amounts corresponding to the VAT of each product and the total with VAT applied in each column.
Now Practice how to get VAT in Excel with a that you have on hand! Remember that the important thing is to understand how the formulas work so that you can apply the same method to any arrangement you have in your spreadsheets.
How to get VAT? Other ways to calculate it
In addition to getting the VAT in Excel, there are other tools that you can use to calculate the VAT of an amount. The most popular are:
- local calculators,
- world calculators and
- mobile applications.
Next, we explain what each one consists of with our suggestions to try them.
local calculators
If your doubt is knowing how to calculate VAT on a calculator, we tell you that there are different personal finance and tax topics sites with tools that allow you to calculate the tax quickly and easily.
In the following link you will find the VAT of Calculaterix. It is very easy to use and you have the option to select regular, reduced and increased VAT rate.
world calculators
VAT is not an exclusive tax of our country. Although in many Spanish-speaking countries it maintains the acronym VAT, in the rest of the world that is applied it receives the name of its acronym in English: VAT or Value-Added Tax.
Those, like Vatglobal, have the advantage of including the rates of many countries for your tax calculations.
mobile applications
When you need to remove VAT on an amount on the go on your mobile device, apps are your best option.
Although the calculator on any cell phone is enough to calculate the tax manually, applications for and regionalized already have the rates preloaded to facilitate use.
Conclusions
To calculate the VAT of a price and apply the tax, it is possible to use many tools.
You have already learned how to get VAT in Excel, seeing the procedure and the formulas necessary to perform this calculation in your product lists or for your tax needs.
You also learned that there are other ways to obtain VAT, such as online calculators and applications for your cell phone. You even learned that VAT in other countries is called VAT and where you can go if you need to calculate it. Undoubtedly, these tools will make it easier for you to interact with this tax.
We invite you to get to know Tiendanube, the platform that helps you create your own website to sell what you want. and know everything it offers for you to scale your business.
Facebook Comments
Disqus