# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Formula to minus VAT at 20%

## EmStar

Dear all,

OK, at the risk of sounding stupid... I am trying to create a balance sheet for my weekly accounts. I would like a column to contain a formula that minus' the 20% VAT and shows the end amount e.g if in  A1 I have £100.00 of sales I would like Column A2 to show the amount minus the VAT i.e £80.00

I have looked for the answer on hear as I don't want to be the one repeating others! But all I have managed to find so far is the formula for VAT at 17.5% which I believe to be e.g. A1*7/47??

Any help would be appreciated and thank you in advance  :Smilie:

----------


## zbor

Try =A1*(1-20%)

----------


## TMS

If you have £100 in cell A1 (gross), the net value is not £80.  If you start with £80 and apply 20%, you'd get £16, so the gross value would be £96.  You need to divide by 120, as the gross value is 120%, and then multiply by 100.  So the net value would be £83.33, and the VAT would be £16.67


Regards, TMS

----------


## EmStar

Thanks TMS I think I see what you're saying. So what would be the formula for that?

----------


## zbor

Try =A1/(1+20%)

----------


## EmStar

Ok, That seems to work for what has been advised.
At the risk of sounding like I'm banging on about it, I was told that A1*7/47 was the formula to calculate VAT at 17.5% by a friend who used to do a similar thing, and I assumed that the formula for VAT at 20% would be of a similar set up?

Apologies, I'm just trying to understand what I'm doing here.

----------


## Kyle123

=A1*1/6 gives you the VAT @ 20%

Though personally I prefer:
=A1/1.2
And
=A1-(A1/1.2)

----------


## zbor

1000/(1+20%) = 833,33
so VAT is: 1000-833,33 = 166,67

In previous example, you've used 17,5%.
1000/(1+17,5%) = 851,06 
so VAT is: 1000-851,06 = 148,94

Solution you've found will result with:
1000*7/47 = 148,94 but above solution is more easier to adopt for future changes because you just change 20% into other number, while 7/47 is specific and you would need to calculate fraction for each VAT.

As Kyle told you you can use A1/1.2 but I write intentionally in this form so you can easily change values.

----------


## EmStar

But I am not trying to add VAT. for example if I make £100 worth of sales I need to pay 20% of that as VAT. Which is £20. 
So in Column A I have food sales, B Wet sales, C total cash, D total EFT then E total take. So if the total take is £350.00 then 20% of that I need to pay as VAT. So I need Column F to show what amount is 20% of the total take and then Column G is the take after VAT is paid by me.

Again, I apologise if I am over complicating this  :Confused:

----------


## Kevin UK

Hi EmStar

Use =ROUND((Gross*VAT)/(1+VAT),2)

Put your VAT in a separate cell that you can reference in your formula. Just in case the VAT man changes it.  :Smilie:

----------


## TMS

> Put your VAT in a separate cell that you can reference in your formula. Just in case the VAT man changes it.



But just be aware that if you do that, the VAT man changes the rate, and you change the cell, it will affect all previous calculations.

Thanks for the rep.

Regards, TMS

----------

