Calculating VAT at different rates

  • Thread starter Thread starter Lawrence Allen
  • Start date Start date
L

Lawrence Allen

I am creating my own spreadsheet to record invoices
received and want Excel to simply enter the 'Gross' amount
and let Excel calculate the VAT content and
subsequent 'Nett' amount automatically.

My problem is that some supplies do not attract any VAT,
some attract VAT at 5% but most attract 17.5% VAT , of
course.

I therefore intend having a 'VAT rate' column where, when
posting each new invoice, I will first enter either 0 (for
zero rated goods), 1 (for goods attracting 5% VAT) or 2
(for goods attracting 17.5% VAT).

Having tried, unsuccesfuly, for several hours to find the
correct formula (and in which column it is best to put the
formula!), I would really appreciate any assistance.
 
Lawrence,

Assuming the gross amount is in A1, the Vat id in B1. In C1, this formula
calculates the VAT
=A1-ROUND((A1/(1+CHOOSE(B1+1,0%,5%,17.5%))),2)
in D1, just use =A1-C1 to get Nett amount
 
One way,

create a table somewhere in the sheet, lets
call it VAT, assume you have the gross price in A1 and
the VAT coding in B1 (0, 1 or 2)

=VLOOKUP(B1,VAT,2,0)*A1

in the cell with the net price

VAT table would look like

A B
0 100%
1 105%
2 117.50%
 
Lawrence Allen said:
I am creating my own spreadsheet to record invoices
received and want Excel to simply enter the 'Gross' amount
and let Excel calculate the VAT content and
subsequent 'Nett' amount automatically.

My problem is that some supplies do not attract any VAT,
some attract VAT at 5% but most attract 17.5% VAT , of
course.

I therefore intend having a 'VAT rate' column where, when
posting each new invoice, I will first enter either 0 (for
zero rated goods), 1 (for goods attracting 5% VAT) or 2
(for goods attracting 17.5% VAT).

Having tried, unsuccesfuly, for several hours to find the
correct formula (and in which column it is best to put the
formula!), I would really appreciate any assistance.
Click to expand...

For a gross amount in A1 and 'VAT code' in B1, you could use this formula in
(say) C1:
=A1/(1+CHOOSE(B1+1,0,0.05,0.175))
You can then just copy it down column C as required.
 
Assuming the invoice value is in column A and the VAT
value in Column B this formula should work
=IF(B1=0,A1,IF(B1=1,A1/1.05,IF(B1=2,A1/1.175,0)))

Hope this helps
Michael
 
Assume your Gross amount is in column D and the data is in row 4
then the gross (net +VAT) for this row is in cell D4, your VAT flag
(pronumeral) is in column E so for this exercise cell E4 contains 0
your net price is in column F, therefore cell F4 contains the formula
=if(e4=1, d4-d4*.05, if (d4=2, d4-d4*.175,d4))
and will display the value in cell d4

The IF function comprises three sections:
1 logical test
2 value if true
3 value if false

with each section separated by a comma .

therefore =IF(logical test, value if true, value if false) is the standard
format.
The formula above uses nested IFs, =IF(test,true,IF(test,true,false))
first test is does E4 = 1 (VAT at 5%)
If so then display the value in d4 less 5% of d4 (if d4=100, then
100-100*.05 which is 95) this could be an issue for rounding, but we'll
leave that at the moment.
If not then proceed to second test
second test is does E4=2 (vat at 17.5%)
If so the d4-17.5% of D4
If not, the assumption is that e4=0, you could test for this by including a
further If statement and for ease of understanding you would then test E4=0,
e4=1, e4=2 in that order, and if E4 did not = 0 or 1 or 2 display and error
message, but that's another issue.

if entered as above you should have something like this displayed
a b c d e f
1
2
3
4 100 1 95

or

4 100 2 82.5

or

4 100 0 100


Helps?

Steve
 
Oops! Read the question wrongly, my formula adapted
to Bob's solution

=ROUND(A1-(A1-A1/(1+VLOOKUP(B1,VAT,2,0))),2)
 
Back
Top