We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
Excel Formula to work out net cost using UK vat rate

talksr
Posts: 296 Forumite


in Techie Stuff
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:
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:
0
Comments
-
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)0 -
I would just go with =K7*1.2
In your current verion you should be multiplying by 120 not 20.0 -
Thank you guys. The posts were really helpful.
I am changing it as we speak!0 -
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)[FONT="]si talia jungere possis sit tibi scire satis [/FONT]0 -
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).No free lunch, and no free laptop0 -
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% ?Owner of andrewhope.co.uk, hate cars and love them
Working towards DFD
HSBC Credit Card - £2700 / £7500
AA Loans - (cleared £9700)0
This discussion has been closed.
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 349.8K Banking & Borrowing
- 252.6K Reduce Debt & Boost Income
- 453K Spending & Discounts
- 242.8K Work, Benefits & Business
- 619.6K Mortgages, Homes & Bills
- 176.4K Life & Family
- 255.7K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 15.1K Coronavirus Support Boards