NOVICE! Formular for 20% VAT...?

[Hellz]

Hi every one,

Ive been glued to this forum for seval hours now, reading older posts, but Im going to admit defeat before my brain explodes!!

OK so Im a novice with excel, but I dont want to be beaten!
Also before answering, can you help me by just keeping it simple, and basing it on 100-20% vat ?

eg: click 100, type *-20 etc etc I think its all the a1/d5 ()... etc thats done me

SO doing my VAT....

Ive got a formular to MINUS the vat (just descovering that subtracting 20% VAT rather than 20% is not the same thing)

THIS is my Minus formular for 100 – 20% vat = 16.67
click destination cell, type = . click 100, type *-1/6% > hit return. = 16.67

Now on the flip side, to work out 20% vat of 100, Ive currently got:

Click destination cell, type = > click 100, type *20% > hit return = 20

Im also checking this is an online VAT calculator the figures seem to be right , but is the formular correct???

Many Many Thanks for the answers in advance, I cant tell you how much help will be appriciated! x

 

[Andrew Poulsom]

Welcome to MrExcel.

VAT is 20% of the amount before VAT. So if the amount before VAT is 100, the VAT is 20 and the amount including VAT is 120. VAT is 20/120 or 1/6 of the amount including VAT.

 

[njimack]

Sounds like you're struggling with the arithmetic rather than Excel.

To calculate a price excluding VAT @ 20%, you would simply enter =100/1.2

 

[Hellz]

Sounds like you're struggling with the arithmetic rather than Excel.

To calculate a price excluding VAT @ 20%, you would simply enter =100/1.2

Hi Thanks for the quick response, Ive tried this but its giving me 0.00
by clicking = then click the 100 cell > 100/1.2 then return and its giving me 0.00

I need to result to be 20?

 

[Hellz]

and yes, I dount doubt that I am LOL which is probably why I find this SO hard LOL

 

[adam087]

Hi Thanks for the quick response, Ive tried this but its giving me 0.00
by clicking = then click the 100 cell > 100/1.2 then return and its giving me 0.00

I need to result to be 20?

You don't need the answer to be £20, you need the answer to be £16.67. VAT is calculated as 20% of the price without VAT. Therefore the price without VAT is £83.33, on which 20% is £16.67 to give a total of £100.

If VAT was £20 then the price without would have to be £100 and the total selling price would be £120.

Observe

Excel Workbook
	A	B	C	D	E	F	G
1	Without VAT	VAT Content	Total Price		Total Price	VAT Content	Without VAT
2	10.00	2.00	12.00		10.00	1.67	8.33
3	20.00	4.00	24.00		20.00	3.33	16.67
4	30.00	6.00	36.00		30.00	5.00	25.00
5	40.00	8.00	48.00		40.00	6.67	33.33
6	50.00	10.00	60.00		50.00	8.33	41.67
7	60.00	12.00	72.00		60.00	10.00	50.00
8	70.00	14.00	84.00		70.00	11.67	58.33
9	80.00	16.00	96.00		80.00	13.33	66.67
10	90.00	18.00	108.00		90.00	15.00	75.00
11	100.00	20.00	120.00		100.00	16.67	83.33
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B2	=A2*0.2
C2	=B2+A2
F2	=E2-(E2/1.2)
G2	=E2-F2

Regards
Adam

 

[Hellz]

oh dear! Im loosing track of what I even wanted now LOL

OK so may be if I tell you exactly what Im trying to work out! This is my first quater of VAT...

SO

My total 1 month sales: £5548.21 so I need to work out how much VAT Im going to have to pay. which acording to the online calculator will be: £924.70

However I had £725.93 in refunds and I want to claim for purchases etc, so I want to Minus the VAT from that total figure....

Can you help me with the 2 formular's please? (im on the verge of giving up and letting the accountant do it )

 

[adam087]

The refunds you'd subtract from the sales BEFORE calculating VAT. So your revenue (sales - refunds) was £4,822.28, on which the VAT due is £803.71.

From this you would subtract the actual VAT paid on purchases.

Might be worth getting the accountant to check it though. HMRC get quite pernickity!

Regards
Adam

 

[Hellz]

The refunds you'd subtract from the sales BEFORE calculating VAT. So your revenue (sales - refunds) was £4,822.28, on which the VAT due is £803.71.

From this you would subtract the actual VAT paid on purchases.

Might be worth getting the accountant to check it though. HMRC get quite pernickity!

Regards
Adam

Hi Adam
Thanks for your help, Dont worry this is just for my benifit BEFORE it goes to the Accountant, Its just anoying that i dont understand the formulars etc...


SOOOO The formular any one??? lol

 

[adam087]

What formula are you after - did mine from the earlier post not do the VAT calculations for you?

Regards
Adam