In this article you will learn how to calculate VAT in Microsoft Excel by using the functions? And Which formula we can use to calculate how much VAT has been paid when the only information you have is the total amount?
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 for VAT 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.
Step 2: Calculate the VAT amount
In Cell E2 write this formula and hit enter.
You will have your VAT amount calculated in E4 for milk. Drag Down the formula to E10.
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.
And it is done. Your Vat Calculator in Excel is ready.
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 VAT calculator excel spreadsheet. You can reverse the process to create an tax rate formula in excel.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org
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.