Well, it is quite easy to calculate tax in excel. The VAT (Value Added Tax) is most common type of tax that is applied to goods. Now GST (Good and Services Tax) is applied to almost every kind of goods but for some products like liquors, VAT is still there. You can calculate GST in the same way. After reading this article you will know:
Generic Formula of VAT Amount Calculation:
|Purchase Price * VAT%|
Generic Formula of Selling Price Calculation:
|Purchase + Taxes|
Let’s say we deal with drinks (all kind of). We are assuming that a different VAT% is applied to each product. And that VAT% is given. We need to calculate the VAT amount and Selling Price.
Step 1: Prepare a Table
We prepared below table in Excel Spreadsheet.
In Cell E2 write this formula and hit enter.
Note: in Vat% column ‘%’ symbol is necessary. If you don’t want % symbol then you need to write your percentage preceding with “0.” Because excel converts “number%” into “number/100”.
Step 3: Calculate Selling Price:
In Cell F2, write this formula and hit enter.
The formula for selling price is ‘Cost+Taxes’ (neglecting profit here). We are assuming that VAT is only tax that is applied to your product. Then the cost is Purchase +VAT amt Taxes.
You can add Totals in the bottom row or In the rightmost column like most newbies do. But the smart way of showing Totals is on the top row, just above the headers so that it is always visible (if top row is frozen).
Now you know how to calculate vat in Excel. You can use this excel tax formula to create an instant tax calculator excel spreadsheet. You can reverse the process to create an tax rate formula in excel.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.