## Calculating sales tax / Value Added Tax in Excel

Today’s question is about sales tax or VAT.

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.