In this article, you will learn how to calculate VAT in Excel using a function? And what formula can we use to calculate the amount of VAT paid if the only information you have is the total amount?
Well, calculating tax in excel is quite easy. VAT (value-added tax) is the most common type of tax applied to goods. Now GST (goods and services tax) applies to almost all kinds of goods, but for some products, such as spirits, VAT still exists. In the same way you can calculate GST. After reading this article, you will learn:
1) Calculating VAT in Excel
2) How to calculate the selling price of a product
The general formula for calculating VAT in Excel:
Purchase Price *% VAT
The general formula for calculating the sales price:
Purchase price + taxes
Suppose we are dealing with beverages (all kinds). We assume that a different% of VAT applies to each product. And this VAT is listed. We need to calculate the VAT and the sales price.
Step 1: Prepare the table
We have prepared an Excel spreadsheet below.
Step 2. Calculate the amount of VAT
In cell E2, write this formula and press Enter.
= D2 * C2
The amount of VAT will be calculated in E4 for milk. Drag the formula down to E10.
Note: The "%" symbol is mandatory in the % VAT column. If you do not want to use the % symbol, you have to put the percentage before the number "0". This is because Excel converts "number%" to "number / 100".
Step 3: Calculate the sales price:
In cell F2, write this formula and press Enter.
= C2 + E2
The formula for the sales price is "Costs + Taxes" (not including profits). We assume that VAT is the only tax that applies to your product. Then the purchase price + VAT, taxes.
And it's done. Your Excel VAT calculator is ready.
You can add totals to the bottom row or to the far right column, as most beginners do. But a clever way to display totals is on the top row, right above the headers, so it's always visible (if the top row is frozen).
Now you know how to calculate VAT in Excel. You can use this Excel tax formula to create an instant Excel spreadsheet for the VAT calculator. You can override the process to create the tax rate formula in Excel.