How To Calculate NZ GST In Google Sheets?

A good way to calculate GST is with a spreadsheet tool like Google Sheets (Excel is another one and we have a guide on that here). Google Sheets can be used to calculate both the GST inclusive and exclusive price, along with the GST component/amount as well. In fact, Google Sheets can do it on multiple prices/products at once, massively speeding up your calculations. In this guide we are going to show you how to set up a Google Sheet to do this.

Setting Up a Google Sheet for Calculating GST

When you open Google Sheets, you will be greeted by an empty sheet. To get started it can be a good idea to create a cell with the GST figure in it and then reference that for the calculations. This makes it easier to use the GST figure in multiple places and if you need to change it you only need to do it in one place.

Calculating From GST Exclusive Price

Calculating the GST amount and the GST-inclusive price from a GST-exclusive price is straightforward. In our example, the label is in column A (A7), and the GST-exclusive amount is in column B (B7). To create a formula in cell B8 that calculates the GST component, follow these steps:

1. Click on the input box next to the fx symbol. You should see the correct cell reference displayed on the left.
2. Enter the formula. In this example, it will be the GST-exclusive price in B7 multiplied by the GST percentage in cell B3. The formula we used is =B7*B3, but this may be different for you if the cells you are using are different.
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. To do this, simply add the GST-exclusive price to the GST amount. The formula for this in our sheet is =B7+B8, which, in this case, results in $115

Calculating From GST Inclusive Price

If you have the GST-inclusive price (price with GST included) and need to find the GST amount and the GST-exclusive price, you can follow these steps:

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 similar to the previous method but works in reverse; instead of dividing by 23 and multiplying by 3, we now multiply by 23 and divide 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 get started, create a new sheet by clicking the "+" button at the bottom next to your existing sheet's name. (This step is optional, but it helps keep everything organised.)

• Set Up Column Headings: Add column headings at the top of your new sheet so that you know what each column represents. For example:
• Column A: Exclusive Price
• Column B GST Component
• Column C: Inclusive Price

For this section, we'll focus on calculating the GST component and the inclusive price using a list of GST-exclusive prices. The formulas provided can also be applied in reverse; just remember to adjust the formula based on whether you're calculating the GST amount or the exclusive/inclusive price.

In cell B2 of column "B," we’ll add a formula to calculate the GST component from the exclusive price. The formula is =A2*$F$2. Here, F2 refers to the cell containing the GST percentage, and the $ symbols before F and 2 lock this reference. This way, when you drag the formula down from B2, it will continue to reference F2 instead of shifting to cells without values (e.g., C3, C4, etc.).

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.

You may also notice a pop up appear that asks you if you want to duplicate the formula to the other cells in the column, which is essentially doing the same as we just mentioned above.

Finally, add the formula for the GST-inclusive price. In our sheet it is =A2+B2. After that, follow the same steps as before to apply the formula to the rest of the column.

Use Our Calculator

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