Excel Formula to work out net cost using UK vat rate

talksr
talksr Posts: 296 Forumite
Part of the Furniture 100 Posts Combo Breaker
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:

Comments

  • sweep9
    sweep9 Posts: 407 Forumite
    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
    CAN1976 Posts: 263 Forumite
    I would just go with =K7*1.2

    In your current verion you should be multiplying by 120 not 20.
  • talksr
    talksr Posts: 296 Forumite
    Part of the Furniture 100 Posts Combo Breaker
    Thank you guys. The posts were really helpful.

    I am changing it as we speak!
  • Lazy_Ike
    Lazy_Ike Posts: 185 Forumite
    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=&quot]si talia jungere possis sit tibi scire satis [/FONT]
  • macman
    macman Posts: 53,129 Forumite
    Part of the Furniture 10,000 Posts Name Dropper
    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 laptop ;)
  • Lil306
    Lil306 Posts: 1,692 Forumite
    Part of the Furniture 1,000 Posts Name Dropper Combo Breaker
    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% ?
    Owner of andrewhope.co.uk, hate cars and love them

    Working towards DFD

    HSBC Credit Card - £2700 / £7500
    AA Loans - (cleared £9700)
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

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

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.