Formula to minus VAT at 20%

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

Try =A1*(1-20%)

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

Try =A1/(1+20%)

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.

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.

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

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.

Originally Posted by 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

You're on the right track! To subtract 20% VAT from a value in cell A1, you can use the following formula in cell A2:

=A1 - (A1 * 20%)

Or, in Excel, you can use the formula with the cell references:

=A1 - (A1 * 0.20)

This will give you the amount minus the 20% VAT in cell A2. In your example, if A1 contains £100.00 of sales, cell A2 would display £80.00, which is the amount after subtracting the VAT.

That isn’t how VAT works, it’s added onto a value, so 100 less vat is 83.33