 Status
Not open for further replies.
1 - 7 of 7 Posts

#### Riempie

·
##### Registered
Joined
·
430 Posts
Discussion Starter · ·
What formula do I use if I have an amount of for example 500 and that is inclusive of 14% sales tax and I want the amount the sales tax alone is

#### mrwendal

·
##### Registered
Joined
·
377 Posts
How can I Calculate the VAT amount when I know the gross amount and VAT percentage?

Take great care to calculate the VAT amount from the gross amount correctly since this amount is made up of the net amount plus the VAT.

Net amount = gross amount * 100 / (100 + VAT percentage)
VAT amount = gross amount less net amount

Example:
Gross amount = £107.50
Tax = 7.5%
Net amount = £107.50 * 100 / (100 + 7.5)= 100
VAT amount = £107.50 - 100 = £7.50

IT IS WRONG to calculate the VAT in the following way:
VAT amount = gross amount * VAT percentage
VAT amount = £107.50 * 7.5% = £8.06 (wrong!)

#### mrwendal

·
##### Registered
Joined
·
377 Posts
So, to follow up..

If you have your Total amount in cell A1, and the percentage in cell B1

then enter this formula in another cell to work out the sales tax alone:

=SUM(A1-((A1*100)/(100+B1)))

#### maxflia10

·
##### Registered
Joined
·
331 Posts
=A1*(100/114) seems to work

#### mrwendal

·
##### Registered
Joined
·
377 Posts
I don't think that is correct, as that will give the total minus tax.
To get the tax amount you would need to take the sum above from the total (ie A1)

therefore the formula would read

=A1-(A1*100/114)

which is the same as

=SUM(A1-((A1*100)/(100+B1)))

As in the original answer!   Hey, it doesn't matter, as long as we get these answers, it doesn't matter if they're repeated!

(But i still get the speed bonus points!!)

haha!

#### maxflia10

·
##### Registered
Joined
·
331 Posts
You're right, I didn't read the question correctly. The OP was looking for the tax amount, not the amount before the tax. My formula produces the amount before the tax. :down: for me! You get ALL the points! #### mrwendal

·
##### Registered
Joined
·
377 Posts
i'm happy to share Great minds think alike, eh... well, closely anyway!

1 - 7 of 7 Posts
Status
Not open for further replies.