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?
Let’s take an example and understand how VAT calculator allows to calculate VAT percentage in easy way .
I have a table in range A1:C6, in which column “A” is containing the product details, column “B” is containing sale price and column “C“ VAT/Tax percentage.
In Column “D”, we need to calculate the “Net Sale Price” and in column “E” the “VAT/Tax” amount.
To calculate the “Net Sale Price” and “VAT/Tax” amounts follow the below mentioned steps:-
Net Sale Price
VAT/Tax Amount
In this way we can calculate VAT in Microsoft Excel.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
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 info@exceltip.com
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.
please send me some excel formulas
I don’t understand, please send some excel formula
“This tip doesn’t seem right?
I want to add the tax to the net price”
to add VAT formula
VAT=ROUND(B2*0.2,2)=540
Total net price including VAT = B2+540=3240
“100.00-18% = 82.00 NOT 84.75
Try to find the net price:
A1 100.00 B1 18.00% then try the formula=(100%-b1)*al answer 82.00 “
You are wrong. you cannot minus the 18% vat/tax directly to the gross amount. 18% is only added into net amount to get the gross price. the tutorial is correct.
100.00 = gross sales
18% = vat
getting net sale price: 100.00/1.18 =84.75
getting the VAT: 100.00/1.18*0.18 =15.25
Great thank you boss
Thank you very much NICK .
Tip is great. We all know how to add VAT onto the basic price, but I hadn’t a clue where to begin doing it the other way around. By the way – VAT is 17.5% in the UK.
VAT is 20% in the UK, not 17.5%. It has been 20% since the Tories got back into power in 2009. It is one of the first things they did to generate more income for the Government coffers
Hi,
I raised invoice in Busy Software and this software tax calucation is very good when i put total amount 4000 in this software this software automatically get tax calculation when i print the invoice.
I want know only for is any this calcuation is in excel or not and if yes so plz confirm me what is the formuls.
VAT/Tax Amount
•Select the cell D2, and write the formula to calculate the “VAT/Tax Amount”.
•=B2-B2/(100%+C2)
That formula cannot be correct. B2-B2?
Hey Tim,
Apply this formula once, I am sure you will get appropriate result.
Hi there is there not a formula or something like ctrl+V u can use that is easier to work out vat cos i tried the method that was given and does not work for me.
Ta Much
how to calculate mrp from selling price
ex-
1000*10%=900
i want from 900 how to arrive 1000
Hello,
As per the given example, it seems you are keeping 11.11% margin between selling price and MRP. If yes, assuming you have selling price (900) in A2 and you want MRP in B2. Try the following function
=ROUNDUP(A2+A2*11.11%,0)
Please let us know if it works!
Thanks,
Team Excel Forum
PLEASE LET ME KNOW THE FORMULA TO FIND OUT PROFIT AMOUNT :
PROVIDED DETAILS ARE;
COST OF PURCHASE, VAT % AND MRP .
Subtotal-A: 133.80
Subtotal-B: ( Add 2% on Subtotal-A/ A1) : 136.48
Subtotal-C: 10% Profit ( Add 10% on Subtotal-B) : 150.12
VAT 5.5% of Total 9.12
IT 4% of Total 6.64
Total 165.88
What is the formula of this rate
I want how a river 150.12 to 165.88
Subtotal-A: 133.80
Subtotal-B: ( Add 2% on Subtotal-A/ A1) : 136.48
Subtotal-C: 10% Profit ( Add 10% on Subtotal-B) : 150.12
VAT 5.5% of Total 9.12
IT 4% of Total 6.64
Total 165.88
I want how a arrive from 150.12 to 165.88
Paz send me an email
I know the formulas seen on example are right. I have a small business and been going through the arithmetic for a very longtime so as not to make any mistakes that can cost me a lot of money.
My question is, do you use the same formula when you actually give a discount on the selling price before VAT? or you just apply the straight forward deduction of percentage?
I came to this question as when working out a discount with the formula above you can always go back to the selling price by adding the percentage given. If you deduct the percentage strait forward you will find out that there is a small percentage discrepancy that works against the retailer.
Hi John,
Yes, a retailer will always face a little disadvantage or drawback because of this.
Happy Learning,
Site Admin
Tutorial is OK but there are typing errors,
In the example : The correct formula is =B2*100/(100+20)
In the second example: The correct formula is =B2-D2
hello what wil be the program codes to calculate the selling price of a produc
sp=bp+vat
vat=tax*bp
tax=16/100
Please Give me excel Formula as given below
Basics Rs. 2640000 +Excise 12.5%=+5.5% Vat= Total Tax vale =49335
VAT/Tax Amount
Select the cell D2, and write the formula to calculate the “VAT/Tax Amount”
=B2*vat
Press Enter
Example, the Vat for Nigeria is 5%
Hence, my formula would translate to;
=B2*5%
or
From the table, the formula should be;
=B2*20%
Press Enter
Dear Admin, thanks for the tips, this is 200% correct formula, I was looking for this formula for a long time. In this tips we just have to put sale price and it calculate vat & net price depending on vat percentage. Thank you so much.
Great article . I was enlightened by the facts ! Does someone know where my assistant could acquire a blank CA FTB 540/540A BK copy to complete ?
Hi Guys
So i need help…i have used your formulas to calculate the vat and nett selling price. But now i am faced with a dilema.
We are a Guest House right so we purchase many fresh produce which is zero rated. I need to show that on my input tax spreadsheet.
Thank you for your assistance its much appreciated
hi
Please give me formula vat calculation for 5.5%
Like for 14.5% i used to do
Rate = 100
14.5% = 100/1.145= 87.33 Price
100-87.33= 12.67 Vat @ 14.5%
Like wise please give me formula for 5.5% calculation