I have been asked twice in one week how to write Excel formulas to calculate GST at the rate of 15%.
You can find information on how to calculate the rate 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.
Let’s start by calculating the GST component of a GST exclusive amount. To do this you simply multiply the value, ex GST by 15% or by 0.15. To find the total including GST simply add the two values together.
Let’s now have a look at calculating the total including GST. This time multiply the total by 115% or by 1.15
Now let’s look at writing a formula to calculate the GST content of a GST inclusive amount. This formula contains two parts. First, multiply the GST inclusive amount by 3 and then divide this figure by 23.
In the video tutorial below I show you in Excel how to:
- calculate the GST component to be added to a GST exclusive figure
- calculate the total figure including GST
- calculate the GST component of a GST inclusive figure
The steps in the tutorial will work in Excel versions 2010, 2013 and 2016