How To Calculate NZ GST In Excel?

A handy tool to calculate GST is a spreadsheet tool like Excel (Google Sheets is another great option, but in this article we will focus on Excel). Excel can help you calculate the GST inclusive and exclusive price, along with the GST component/amount as well. In fact, Excel can do it on multiple prices/products at once, massively speeding up your calculations. In this guide we are going to show you all that and more!

Setting Up an Excel Workbook for Calculating GST

Upon opening Excel, you will be greeted by a blank sheet. To get started I like to create a cell with the GST figure in it. The benefit of this is that if you have multiple places where you use the GST figure, you only have to change it in one spot to update all calculations. If you don't do this you will have to go into each cell and change the number to the updated one.

Calculating From GST Exclusive Price

Finding the amount of GST and the GST inclusive price from the GST exclusive price is fairly simple. In out example we have put the label on the left in the A column (A7) and the GST exclusive amount in the B column (B7). To create a formula in B8 that will calculate the GST component, do the following:

  1. Click on the input box next to the fx. You should see the correct cell number on the left hand side.
  2. Enter the formula. In this case it will be B7 (the GST exclusive price) x the GST percentage we put in cell B3. The actual Excel formula we put in the input box will be =B7*B3 in our case.
  3. You should notice that B8 populates with the result when you click off it.

The next step is to calculate the GST Inclusive price in cell B9. This is as simple as adding the GST exclusive price to the GST component/amount. The formula for this is =B7+B8, which in our case results in $115.

Calculating From GST Inclusive Price

If you already have the GST inclusive price (price with GST added) and want to find the GST component and exclusive price, you can use the following formula:

GST Component = GST Inclusive Price x 3 ÷ 23

Put =B7*3/23 in cell B8

GST Exclusive Price = GST Inclusive Price - GST Component

Put =B7-B8 in cell B9

Calculating From GST Component/Amount

This is very similar to above, but essentially in reverse where we x by 23 and divide by 3 rather than dividing by 23 and x by 3:

GST Inclusive Price = GST Component x 23 ÷ 3

Put =B7*23/3 in cell B8

GST Exclusive Price = GST Inclusive Price - GST Component

Put =B8-B7 in cell B9

Calculating Multiple Prices at Once

To do this we are going to make a new sheet, by clicking the "+" button down the bottom next to our sheet name (This isn't necessary but we want to keep it cleaner). From here let's add our column headings so we know what column represents what.

For this section we are going to focus on calculating the GST component and inclusive price using a list of GST exclusive prices. What we will show you here will also work gong the other way as well, just remember to change the formula depending on what you want to calculate.

In the "B" column in B2 we are going to add the formula to calculate the GST component from the exclusive price. In this case it is =A2*$F$2. The F2 is from the cell that contains the GST percentage, while the $ signs before the F and 2 lock the cell, so when we drag down from B2 it doesn't change (ie, it doesn't go to C3, C4, etc. where there are no values).

Once you have added the formula in B2, you can simply drag down and the values in B3, B4 and so on should populate. If you want to populate all cells in the column with the formula, you can press ctrl/command + shift + down arrow on your keyboard with B2 selected. This should select all the cells in the column. Once you have done this, press ctrl/command + D and the formula should populate to all cells.

Finally, add the formula for the GST inclusive price (in this case it will be =A2+B2) and repeat as explained before.

Use Our Calculator

If you don't won't to do this all yourself, check out our handy calculator here.