Calculating sales tax / Value Added Tax in Excel

Today’s question is about sales tax or VAT.

Rebecca asks:

How do you work out sales tax/Value Added Tax in Excel?

 

It sounds like a simple question – but the answer depends on whether you have the price without sales tax, or the inclusive price.

So, can you work out how to calculate tax? And what issues are there?

Why not watch the video below, and see if you are right.

(If you want the video in a bigger window, then the “full screen” button is on the bottom-right of the video.

 

Let’s have a look at the formulas:

If you have the tax-exclusive price in cell A1 and the tax rate in B1, then:

  • the sales tax is = A1 * B1
  • the tax-inclusive price is = A1 * (1+B1)

If you have the tax-inclusive price in cell A1 and the tax rate in B1, then:

  • the tax-exclusive price is = A1 / (1+B1)
  • the sales tax is = A1 – A1 / (1+B1)     OR     = A1 * B1 / (1+B1)

One important thing to remember is that not every item will have the same amount of tax. For example, you may have:

  • basic food items which attract no tax,
  • other items which attract a reduced tax rate, and
  • most items which attract the full tax rate.

Therefore, it’s important not to calculate the tax on an invoice with multiple items – you have to break it down into smaller, individual items which have a different tax rate.

Are there any other issues you can think of? If so, please let me know in the comments section below.

Thank you very much for reading, and please look at answers to other questions.


 

If you would like to know more about Excel, why not take one of my courses – and using the coupon code SQLINTRO will reduce the price to only $10 per course (down from $25).

My course Microsoft Excel – go from Beginner to Specialist certificate takes you through the first 5 levels of Excel functions in a little over 11 hours long. Tables, as used in the video above, is in Level 4 Section 1.


Leave a Reply

Your email address will not be published. Required fields are marked *