How to calculate GST at 15% using Excel formulas

Posted July 1, 2017

Post updated 18 October 2019 by Sharyn Baines

A common request I receive is to please explain how to calculate GST at 15% using Excel formulas.

You can find information on GST  (Goods and Services Tax) on the NZ Inland Revenue website. I’ve taken this a step further and put together a tutorial on how to put these calculations into Excel formulas.

Excel calculate GST exercise file

Click here to Download the example workbook. Use this as your GST calculator. Follow along with the instruction. We'll also give you a 'completed' copy so that you can check your formulas against the completed copy.

Formula for adding GST

Let’s start by calculating the GST component of a GST exclusive amount.  To do this you simply multiply the value, excluding GST by 15% or by 0.15.  To find the total including GST simply add the two values together.

In the example below B5 has been multiplied by 0.15, which is the same as 15%. You can type either value into the formula and Excel will give you the correct answer.

In cell B7 the GST exclusive amount held in B5 and the GST amount held in cell B6 have been added together to give the GST inclusive amount.

Calculate GST in Excel 1

Formula for calculating the total including GST

There are times when you only want to calculate the Total figure including the GST. So let’s now have a look at calculating the total including GST.  

This time we multiply the total by 115% or by 1.15.

In the example below cell B12, which is the figure without GST (exclusive figure) is being multiplied by 1.15 to calculate the Total including GST. The same result would have been found if you had used 115% instead of the 1.15.

Calculate GST in Excel 2

Formula for finding the GST amount from a Total

Now let’s look at writing a formula to calculate the GST content of a GST inclusive amount.  

This is extremely handy when you have a figure and you need to find how much GST is included in the Total figure. I use this a lot when I've purchased something for work and the receipt just shows the total figure without the GST figure.

This formula contains two parts.  First, multiply the GST inclusive amount by 3 and then divide this figure by 23.

In the example below the formula has been written as =B18*3/23

Extra Note: if you like to use brackets, the formula will still calculate correctly if you use =(B18*3)/23

Calculate GST in Excel 3

Using these formulas you should now be able to quickly find and calculate GST.

In the video tutorial at the top of the page I take you step-by-step through each of the formulas listed above. Download the workbook and use it as your own GST calculator, or use it to follow along with the video.

Excel calculate GST exercise file

Click here to Download the example workbook. Use this as your GST calculator. Follow along with the instruction. We'll also give you a 'completed' copy so that you can check your formulas against the completed copy.

The steps in the tutorial will work in Excel versions 2007, 2010, 2013 and 2016, 2019 and Excel for Microsoft Office 365.

For more information on our Microsoft Excel training courses and onsite workshops, please visit our Home page.

For more assistance with understanding GST (Goods and Services Tax) within New Zealand please visit the NZ Inland Revenue page for all things GST related.

Was this blog helpful? Let us know in the Comments below


Elevate your Excel game and become a pro with our exclusive Insider Group

Be the first to know about new tutorials, videos, and tips for Microsoft 365 products. Join us now and claim your exclusive bonus, your list of Essential Excel Skills to become proficient in Excel!

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    >