VAT & NET From GROSS

Hi all,
I appreciate VAT, and adding & subtracting percentages in general, has probably been done to death, but I've come across a very peculiar problem when creating a spreadsheet to calculate VAT from the GROSS figure paid. i.e. a formula to calculate that a GROSS purchase of £20 has had £4 VAT and a NET amount of £16.

What I'm trying to do is where a GROSS figure is input, the spreadsheet calculates the VAT paid (currently 20%) and NET cost of the goods, in essence working the calculation backwards, but this is where the problems start.

I have 2 receipts, 1 for £15.83 (NET) - £3.16 (VAT@20%) - £18.99 (GROSS) and
1 for £22.91 (NET) - £4.51 (VAT@20%) - £27.49 (GROSS)

Both of these are accurate.

When I add these to my spreadsheet, however (example attached), I get the following:
Example 1 £18.99 (GROSS) - £3.80 (VAT@20%) - £15.19 (NET)
Example 2 £27.49 (GROSS) - £5.50 (VAT@20%) - £21.99 (NET)

Both of which are wrong.

It doesn't matter which of the various formulas I apply from the various previous forum threads, I cannot get the figures to show the accurate VAT and NET amounts, if I start be inputting the GROSS figures and working backwards.

Please can anyone tell me where I'm going wrong?

Thanks for checking out my post and for any advice you can offer.

Kind regards
MrE

Net=Gross/1.20 (VAT at 20%)

Vat=Gross - Net

Gross =18.99

Net=18.99/1.20= 15.83

Vat=18.99-15.83 = 3.16

Thanks to both John and Ali.

I'm a buffoon and your responses and explanations have worked wonderfully!

Now I wont be overpaying the tax office, for which I'm exceptionally grateful.

Thanks again.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.