If you operate a business in New Zealand (NZ) like me, chances are you too need to calculate GST on purchases & sales. Today, let me share the excel formulas needed to calculate GST as per NZ laws. I have also attached a free GST calculator template to help you if you are in a hurry.
How to calculate NZ GST using Excel?
Assuming you have the sale price in cell C5, the GST is calculated by the below formula.
=C5 * 0.15We multiply the “sale” or “service” price by 0.15 (or 15%) as the official GST rate in New Zealand is 15% [ref].
Excel formula for NZ GST from “total” price
Let’s say you want to figure out the GST from total price (GST inclusive price) of something. This is quite common in retail scenarios. You have an item for $140 on the shelves, but you need to figure out what the GST should be on this. In this case, you can use the below Excel formula.
Assuming your “total” price is in cell C15, the GST is calculated with this formula:
=C15 * 15/115
GST Reverse Calculation – What is the “sale price” if I know GST?
Occasionally, we may have the reverse problem. We know how much the GST is, but just need to figure out the total. In this case, you can use the below formula.
Assuming your GST is in cell I5, the sale price can be calculated with below Excel formula
=I5*100/15
and total price can be calculated with this formula
=I5*115/15The 3 / 23rds and 3/20ths rules
Here is a handy shortcut to quickly figure out the GST from total or sale prices.
3/23rds rule – GST from total
If your total amount is known, just multiply that with 3 and divide by 23 to get the GST.
For example, if your total is $230, then GST would be $30.
=230 x 3 / 23
=690 / 23
=303/20ths rule – GST from Sale Price
If you know the “sale” price, just multiply it with 3 and divide by 20 get the GST.
For example, if your sale price is $140, then GST would be $21
=140 x 3 / 20
=420 / 20
=21GST for hourly rates, services
If you work as a plumber / electrician / some other type of service provider and you charge by hour, then you can use below formulas for calculating GST.
Assuming your hourly rate is in cell I15 and hours worked in cell I16, the GST formula looks like this:
=I15*I16*0.15
FREE NZ GST calculator workbook
I have created a simple, plug-n-play GST calculator workbook for you. Please download it here, enter your price / total / hourly information and the file automatically calculates the GST for you. It also has the GST formulas / patterns that you can apply to your own data.
Click here to download the NZ GST Calculator.
Need Spreadsheet help?
If you are an NZ business and need spreadsheet help or automation services, please get in touch with me. I am a Wellington based Excel / automation expert and I have been helping clients for the past 15 years in creating simple & easy automation and Excel solutions. Please email me on hello@chandoo.org to discuss more.
