The Value Added Tax (VAT) is a tax that is applied directly on consumption and it is important to learn how to calculate it in different ways, such as in spreadsheets and applications that facilitate its use, for budgeting, fiscal and inventory issues.
A great tool that helps calculate and apply the different types of are spreadsheets. For this reason, we have prepared this article for you where I will teach you how to get VAT in Excelapply it to different cases, as well as other simple ways to calculate it.
It’s important to note that although I used 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 perform the examples with the one that suits you best!
How is VAT calculated?
in our article 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, I recommend giving it a review.
Before starting with the spreadsheets, I will just take up from that article the formula with which we obtained the VAT manually in order to better understand how to apply it. So, let’s go with a brief review, is that okay with you?
if you want to know how to calculate VAT of a product, you need to know your base cost (before any tax) and the current rate —16% for almost all of our country, except the border area where a subsidy applies that reduces it to 8%.
With this data, you have two paths:
- If you want to know the amount of VAT that corresponds to a product that does not have it, just multiply the base cost of the product by 1.16. In a simple formula, this would look like this:
(Base cost of the product) x (1.16) = Total with VAT applied
- If you need the amount corresponding to the VAT of a product that already has it, you need to divide its final price by 1.16 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.16) = Total without VAT
and then:
(Total product cost with VAT) – (Total without VAT) = VAT amount
With this basic information well remembered, let’s move on to the spreadsheet examples where it will be easier to explain step by step.
How to get VAT in Excel? examples to practice
Now yes, I will teach you how to get VAT in Excel. The five examples that we will use for you to 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.
- Applied on base cost in border area.
- Calculation of VAT in price lists.
📢 Type: if you need a version of Excel to work in the cloud, use the suite with all the basic functions of the platform. It is free and you only need an email to register.
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 understand this part, the arrangement 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 get VAT in Excel with a formula, I have placed the concept of each amount on the left side. In cases where a calculation or formula is applied, I put this just on the right side so that you have it much clearer. Excel will take care of showing you in blue and red the cells that are part of said formula.
📢 Type: to enter a formula in Excel, you need to type the ‘ sign=‘, followed by the opening parentheses ‘(‘, to then select with the pointer each cell that makes up the calculation and the operation signs (‘+,–,*,/‘).
Calculation of VAT applied on base cost
- First, you need to enter the base cost (before taxes) in a cell. In this case, the cell with the value is the B3.
- Next, you set the VAT rate you want to apply. The 1.16 was left here in the cell B4.
- Finally, you apply the formula to multiply the previous two cells. The formula would be =(B3*B4). This is how you get your total with VAT applied.
Calculation of VAT broken down from base cost
- You start with your final cost (the one that already has VAT included). Here he was in the cell B3.
- As in the previous example, set your VAT rate in the next cell. Here was the B4.
- The first formula in this calculation divides the final cost by the rate, that is, the previous two cells. The formula is =(B3/B4).
- To obtain our VAT itemized, it remains to subtract the base cost without VAT (B5) at final cost (B3). In this case, the formula that gives us this result is =(B3-B5).
Calculation of VAT before applying it to the base cost
- When you need calculate VAT Before you add it to the base cost, as a subtotal, you start with the base cost without the tax. Here he was in the cell B3.
- Set your VAT rate (in decimals or percentage) in the cell below. Here was the B4.
- You must first calculate the VAT on the base cost, multiplying the two previous cells. The formula then remains =(B3*B4).
- To get our total already with VAT, you add the base cost (B3) to the VAT you just obtained in the previous step (B5) at final cost. The formula for this calculation is =(B3+B5).
📢 Type: Did you notice that the VAT rate in the example above is now 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, and you do this in the numbers tab at the top left of the Excel toolbar or by entering the direct number followed by the symbol ‘%‘.
Calculation of VAT on base cost in border area
- Just like in the first example, you need to enter the base cost (before taxes) in a cell. Here the value remained in the B3.
- Next, you set the VAT rate you want to apply. For the border area, it corresponds to 8%, that is, the VAT corresponds to 1.08 in decimals, as seen in the cell B4.
- Finish by applying the formula to multiply the previous two cells. The formula would be =(B3*B4) to obtain your total with applied border area VAT.
Calculation of VAT in price lists
- You need to start with your list of costs in one column. Here goes from the cell A4 until the A11.
- In cell A16to facilitate this example, we put our VAT rate at 16%.
- In the second column we want to obtain 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 of ‘$‘ applied to the 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 VAT total for your entire list, you need to add each base cost (from A4 until A11) and the VAT obtained for each product (what you will have in the second column between B4 Y B11). The formula to obtain this result is =(A4+B4) and stays in C4. For now, we also leave it that way.
- Since you have the two initial formulas for each column to calculate (B4 Y C4), it’s time 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 initial formula is (it starts with B4) and marking down all the cells parallel to your base costs, that is, until B11.
- Once marked, press the keys CTRL + D to generate the padding from the initial formula in the entire column of B4 a B11. We love you, tech! 😍
- Now, repeat this last step (CTRL + D), but in the column that starts with the formula in C4 and arrives until 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 with a price list 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 can VAT be withdrawn? Other ways to calculate it
In addition to Get VAT in Excelthere are other tools that you can use to calculate VAT on an amount. The most popular are:
- Local calculators.
- World calculators.
- Mobile applications.
Below we explain what each one consists of with my suggestions to try them.
local calculators
If your doubt is to know how to get VAT on calculator, you should know that there are different personal finance and tax topics sites with tools that allow you to calculate the tax quickly and easily. One that I recommend is found in Billing-e. It is very easy to use and you have the option to select the regular VAT rate and the border zone 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. The ones, like the one from Vatglobal, have the advantage of including the rates of many countries for your tax calculations.
mobile applications
when it is necessary remove VAT from an amount on the go on your mobile device, apps are your best bet. 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 and apply the Value Added Tax, it is possible to use many tools. you already learned how to get VAT in excelexplaining the procedure and the necessary formulas so that you can carry out this calculation in your product lists or for your fiscal needs.
You also learned that there are other ways to obtain VAT, such as online calculators and mobile applications. Well, 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 greatly facilitate your interaction with this tax.
Knowing how to obtain VAT will be of great help for your tax calculations, or together with it, it will allow you to carry out your and other procedures with the SAT more easily.
I invite you to try Tiendanube, the platform that will make it easy for you to create your own website to sell whatever you want. and when you stay with her, pay only $99 MXN per month, without commission costs. ☁
Facebook Comments
Disqus