Excel Formula to work out net cost using UK vat rate

talksr

Hello,

I am very bad at maths.
I made a spread sheet which would take a gross cost and then work out the net cost, based on the 17.5% vat rate.

Here is an example...

=SUM(K7/100)*117.5

How can I change this to the 20% rate we now have?

If I put:

=SUM(K7/100)*20.0

It does not like it.

Any help would be great :money:

sweep9

You simply for got to add 100 to your formula

You put:-

=SUM(K7/100)*20.0

It should be:-

=SUM(K7/100)*120.0

If i was you, I'd put the VAT rate in another cell (in my example A1)

then you could use:-

=SUM(K7/100)*(100+A1)

CAN1976

I would just go with =K7*1.2

In your current verion you should be multiplying by 120 not 20.

talksr

Thank you guys. The posts were really helpful.

I am changing it as we speak!

Lazy_Ike

I might be getting it wrong as to what you are trying to achieve. You say you want a net cost by removing VAT from the Gross. But your formular grosses up cost plus VAT.
To get the net you need to calculate the VAT and deduct it from the Gross price. Either:
=(A1/120)*100 or
=A1-(A1/6) or
to round decimal points
=round((A1/120)*100,2) or
=round(A1-(A1/6),2)

macman

Your first formaula calculates the VAT component-not the total price including VAT.
Using sweep9's method wil make it much easier to change things should the VAT rate go up again (can't see it coming down).

Lil306

macman wrote: »

Your first formaula calculates the VAT component-not the total price including VAT.
Using sweep9's method wil make it much easier to change things should the VAT rate go up again (can't see it coming down).

As an add-on, I thought vat was now 20% ?