Home » Our Blog » How to calculate GST at 15% using Excel formulas

How to calculate GST at 15% using Excel formulas

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.

Adding GST formula

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 total including GST formula

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

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

Finding GST amount from total

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 Office 365.

For more information on our Microsoft Excel training courses and onsite workshops, please visit our Excel courses information 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.

Sharyn is an expert trainer.  She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.

She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

Move, Copy and Paste information in Excel

  Learn how to move, copy and paste information in Excel. Once you have mastered Cut, Copy and Paste you will find yourself shifting data around easily and reusing copied data, saving you the need to recreate the same information again. All Windows applications have access to the Windows Clipboard. This is a special area used for

​Read More

Print all or part of an Excel worksheet

  Learn how to preview your work before printing and how to print all or part of an Excel worksheet. Join the ‘Excel at Work Insiders’ group today!100% free. Join now and unlock offers exclusive to this group!Receive tips and guidance to help you SAVE TIME, WORK SMARTER and IMPROVE YOUR UNDERSTANDING of Microsoft® Office. envelope envelope Join

​Read More