danielcompton/gist:6724333

bimoap · 2018-09-06T23:03:43Z

Thanks so much for sharing the formula.

ghost · 2019-01-22T22:38:06Z

@mattdwen if you wanted to calculate that on a monthly basis how would you do that?

alangrainger · 2019-12-02T01:20:06Z

Here's the formula for monthly: @tylerbillions

=IF(E2<=1166.67,SUM(E2*10.5%),IF(E2<=4000,SUM(E2-1166.67)*17.5%+122.5,IF(E2<=5833.33,SUM(E2-4000)*30%+618.33,SUM(E2-5833.33)*33%+1168.33)))

LukePWilkins · 2019-12-02T07:45:23Z

@alangrainger
Thanks very much!

ghost · 2019-12-02T08:03:12Z

@alangrainger thanks mate!

augusto-altman · 2020-01-23T01:34:45Z

Thanks, very useful!

codewithmarc · 2020-09-20T07:11:54Z

Hey there, would anyone here know how to reverse the formula so you input your take home pay and it adds the PAYE to give you a Gross Earnings / annual (or monthly) income. The use case would be to work out how much monthly or annual salary you'd need to earn to achieve your desired level of take home pay. I spent a few hours trying but it's beyond my skills. Thanks heaps

codewithmarc · 2020-10-16T06:42:17Z

Hey @LukePWilkins thanks so much for taking a look at this, I really appreciate it. I had a look at your sheet but when I pass a gross value through the original calculator, and then pass the net answer back through your solution it comes out with a different gross value, so it can't be quite right yet. Would love to hear from you again if you crack it!

kkava · 2021-04-02T00:10:58Z

Here is the annual formula simplified and updated for the new 2021 top tax bracket of 39%.

=IF(E2<=14000,
    E2*10.5%,
    IF(E2<=48000,
        (E2-14000)*17.5%+1470,
        IF(E2<=70000,
            (E2-48000)*30%+7420,
            IF(E2<=180000,
                (E2-70000)*33%+14020,
                (E2-180000)*39%+50320)))

LukePWilkins · 2021-09-05T21:42:55Z

Hello.
How does one convert it to a monthly earn total @alangrainger, taking into account the 39% top end
cheers

LukePWilkins · 2021-10-04T00:39:22Z

Use this formula at your own discretion. I'm by no means a qualified accountant.

This formula will work out the monthly 39% tax rate

=IF(B20<=1166.67,SUM(B20*10.5%),IF(B20<=4000,SUM(B20-1166.67)*17.5%+122.5,IF(B20<=5833.33,SUM(B20-4000)*30%+618.33,IF(B20<=15000,SUM(B20-5833.33)*33%+1168.33,SUM(B20-15000)*39%+4193.33))))

kkava · 2022-03-26T08:34:30Z

Those are the tax contributions from each tax bracket.

…

On Sat, Mar 26, 2022, 9:17 AM CMac89 ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ Thanks very much for this, it works a treat. I do have a question though, can someone explain to me the +numbers and how you get them? +1470 +7420 +14020 ?? Thank you! — Reply to this email directly, view it on GitHub <https://gist.github.com/6724333#gistcomment-4110758>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AC4PDNJZXDYRXLI67S5F2LTVBYNMTANCNFSM4JTO2RVQ> . You are receiving this because you commented.Message ID: ***@***.***>

danielsian · 2022-03-30T02:54:55Z

Hey there, would anyone here know how to reverse the formula so you input your take home pay and it adds the PAYE to give you a Gross Earnings / annual (or monthly) income. The use case would be to work out how much monthly or annual salary you'd need to earn to achieve your desired level of take home pay. I spent a few hours trying but it's beyond my skills. Thanks heaps

For annual income:

=IF(C1<=12530,ROUND(C1/89.5%),ROUND(IF(C1<=40579,(C1-12530)/82.5%+14000,IF(C1<=55978,(C1-40579)/70%+48000,IF(C1<=129678,(C1-55978)/67%+70000,(C1-129678)/61%+180000)))))

CMac89 · 2022-03-30T06:02:28Z

I'm still having trouble with this, hopefully someone can help...

I'm trying to work the formula so it reads the cell values instead of manually typing values. Also I have added the 180K @ 39% bracket.

Here is my formula:
=IF(B29<=L30,SUM(B29*M30%),IF(B29<=L31,SUM(B29-L30)*M31%+N30,IF(B29<=L32,SUM(B29-L31)*M32%+N31,IF(B29<=L33,SUM(B29-L32)*M33%+N32,IF(B29>=K34,SUM(B29-L33)*M34%+N33)))))

Can anybody spot what I'm missing??

Thank you!

CMac89 · 2022-03-30T06:42:02Z

Actually nevermind, I realised I was missing out adding previous taxed amounts!

For anyone who is interested, here is the working formula:

=IF(B29<=L30,SUM(B29*M30%),IF(B29<=L31,SUM(B29-L30)*M31%+N30,IF(B29<=L32,SUM(B29-L31)*M32%+N30+N31,IF(B29<=L33,SUM(B29-L32)*M33%+N30+N31+N32,IF(B29>=K34,SUM(B29-L33)*M34%+N30+N31+N32+N33)))))

jasontomasi · 2022-05-11T07:07:26Z

Thank you, CMac89 :)

inspiredearth · 2022-10-10T22:45:45Z

Thanks for sharing this, and for the updates in the comments.

Here's another way of going about calculating annual income tax on a known income amount (A1) ...

=SUMPRODUCT(--(A1>={1;14000;48000;70000;180000}),(A1-{0;14001;48001;70001;180001}),{0.105;0.07;0.125;0.03;0.06})

In this case, A1 is the income total.

jasontomasi · 2022-10-12T03:58:03Z

Thank you <3 This is even better.

Gopher-nz · 2023-03-05T01:24:43Z

I love this formula. But what this does not have future proofing from my point of view like the previous suggestions which include tax percentages in a table. I guess my comment comes from me not understanding the sumproduct formula and what would I change if the tax percentages changed - currently I do not have a clue.

Looking at
=SUMPRODUCT(--(F35>={1;14000;48000;70000;180000}),(F35-{0;14001;48001;70001;180001}),{0.105;0.07;0.125;0.03;0.06})

I suspect it is the third array which covers the tax percentages, but I cannot see how these are calculated. Any ideas?

inspiredearth · 2023-03-05T03:02:39Z

I love this formula. But what this does not have future proofing from my point of view like the previous suggestions which include tax percentages in a table. I guess my comment comes from me not understanding the sumproduct formula and what would I change if the tax percentages changed - currently I do not have a clue.

Looking at =SUMPRODUCT(--(F35>={1;14000;48000;70000;180000}),(F35-{0;14001;48001;70001;180001}),{0.105;0.07;0.125;0.03;0.06})

I suspect it is the third array which covers the tax percentages, but I cannot see how these are calculated. Any ideas?

The third array contains the tax rates for the first income bracket and the incremental tax rates for each subsequent bracket.
The first tax rate in the third array is the tax rate for the first income bracket, which is 10.5%.

The second tax rate is the incremental tax rate for the second bracket, which is 7% (i.e., the difference between the tax rate for the second income bracket, which is 17.5%, and the tax rate for the first bracket, which is 10.5%).

The third tax rate is the incremental tax rate for the third bracket, which is 12.5% (i.e., the difference between the tax rate for the third income bracket, which is 30%, and the tax rate for the second bracket, which is 17.5%).

The fourth tax rate is the incremental tax rate for the fourth bracket, which is 3% (i.e., the difference between the tax rate for the fourth income bracket, which is 33%, and the tax rate for the third bracket, which is 30%).

The fifth tax rate is the incremental tax rate for the fifth income bracket, which is 6% (i.e., the difference between the tax rate for the fifth bracket, which is 39%, and the tax rate for the fourth bracket, which is 33%).

Knowing this, you should be able to adjust them accordingly, should the need arise.

Gopher-nz · 2023-03-05T04:02:23Z

Awesome! You are awesome. And I feel stupid - LOL - for not seeing the pattern. Thank you and have a great day.

Dialga · 2023-05-25T14:20:24Z

A modified version of @inspiredearth's formula:
This includes ACC levy of 1.53% and 3% Kiwisaver.
=(($A1-SUMPRODUCT(--($A1>={1;14000;48000;70000;180000}),($A1-{0;14001;48001;70001;180001}),{0.105;0.07;0.125;0.03;0.06}))-($A1*(0.0153+0.03))

Turbo-Jet · 2024-03-21T03:33:53Z

Hi all,
Just created a Google Sheet to help calculate the tax, also the ability to include deductions for Kiwisaver and ACC levy. You can add your custom values to the boxes in green.

https://docs.google.com/spreadsheets/d/1hPtxMEZgM4t4Z2_cyE-G05yDzwvaSKH_f3U48-s9B0o/edit?usp=sharing

LightspeedNZ · 2024-05-28T23:47:59Z

Hi guys, thanks for this.

I found that the correct formula to calculate the edge case of exactly $14,000 etc needs to be as follows;

=SUMPRODUCT(--(A1>={0;14000;48000;70000;180000}),(A1-{0;14000;48000;70000;180000}),{0.105;0.07;0.125;0.03;0.06})

spmp · 2024-08-28T03:38:12Z

Thanks @LightspeedNZ, I've got an update for FY25 which tests out pretty well against IRD website based on:

From	To	Rate
0	$14,000.00	10.50%
$14,001.00	$15,600.00	12.82%
$15,601.00	$48,000.00	17.50%
$48,001.00	$53,500.00	21.64%
$53,501.00	$70,000.00	30.00%
$70,001.00	$78,100.00	30.99%
$78,101.00	$180,000.00	33.00%
$180,001 and over		39.00%

=SUMPRODUCT(--(A1>={0;14000;15600;48000;53500;70000;78100;180000}),(A1-{0;14000;15600;48000;53500;70000;78100;180000}),{0.105;0.0232;0.0468;0.0414;0.0836;0.0099;0.0201;0.06})

From 1st April 2025 with the following tax rates:

From	To	Rate
0	$15,600.00	10.50%
$15,601.00	$53,500.00	17.50%
$53,501.00	$78,100.00	30.00%
$78,101.00	$180,000.00	33.00%
$180,001 and over		39.00%

Gives the following formula (untested):

=SUMPRODUCT(--(A1>={0;15600;53500;78100;180000}),(A1-{0;15600;53500;78100;180000}),{0.105;0.07;0.125;0.03;0.06})

danielsian · 2024-08-28T05:02:23Z

Here's a slightly simpler version to prevent the problem mentioned by @LightspeedNZ:

=SUMPRODUCT((A1>{1;14000;48000;70000;180000})*(A1-{0;14000;48000;70000;180000}),{0.105;0.07;0.125;0.03;0.06})

Lambda limits you to one cell reference, which is a time saver:
=LAMBDA(x,SUMPRODUCT((x>{1;14000;48000;70000;180000})*(x-{0;14000;48000;70000;180000}),{0.105;0.07;0.125;0.03;0.06}))(A1)

spmp · 2024-11-04T08:54:34Z

Just to add that most PAYE calculators online are incorrectly using the formula for 1st April 2025 already. If you are calculating PAYE or total tax you may need to include the ACC Earners Levy of $2,276.53 pa.

danielcompton/gist:6724333

bimoap commented Sep 6, 2018

ghost commented Jan 22, 2019 •

edited by ghost

Loading

alangrainger commented Dec 2, 2019

LukePWilkins commented Dec 2, 2019 •

edited

Loading

ghost commented Dec 2, 2019

augusto-altman commented Jan 23, 2020

codewithmarc commented Sep 20, 2020

codewithmarc commented Oct 16, 2020

kkava commented Apr 2, 2021 •

edited

Loading

LukePWilkins commented Sep 5, 2021 •

edited

Loading

LukePWilkins commented Oct 4, 2021

kkava commented Mar 26, 2022 via email

danielsian commented Mar 30, 2022

CMac89 commented Mar 30, 2022

CMac89 commented Mar 30, 2022

jasontomasi commented May 11, 2022

inspiredearth commented Oct 10, 2022 •

edited

Loading

jasontomasi commented Oct 12, 2022

Gopher-nz commented Mar 5, 2023

inspiredearth commented Mar 5, 2023 •

edited

Loading

Gopher-nz commented Mar 5, 2023

Dialga commented May 25, 2023

Turbo-Jet commented Mar 21, 2024 •

edited

Loading

LightspeedNZ commented May 28, 2024

spmp commented Aug 28, 2024

danielsian commented Aug 28, 2024 •

edited

Loading

spmp commented Nov 4, 2024

danielcompton/gist:6724333

bimoap commented Sep 6, 2018

ghost commented Jan 22, 2019 • edited by ghost Loading

alangrainger commented Dec 2, 2019

LukePWilkins commented Dec 2, 2019 • edited Loading

ghost commented Dec 2, 2019

augusto-altman commented Jan 23, 2020

codewithmarc commented Sep 20, 2020

codewithmarc commented Oct 16, 2020

kkava commented Apr 2, 2021 • edited Loading

LukePWilkins commented Sep 5, 2021 • edited Loading

LukePWilkins commented Oct 4, 2021

kkava commented Mar 26, 2022 via email

danielsian commented Mar 30, 2022

CMac89 commented Mar 30, 2022

CMac89 commented Mar 30, 2022

jasontomasi commented May 11, 2022

inspiredearth commented Oct 10, 2022 • edited Loading

jasontomasi commented Oct 12, 2022

Gopher-nz commented Mar 5, 2023

inspiredearth commented Mar 5, 2023 • edited Loading

Gopher-nz commented Mar 5, 2023

Dialga commented May 25, 2023

Turbo-Jet commented Mar 21, 2024 • edited Loading

LightspeedNZ commented May 28, 2024

spmp commented Aug 28, 2024

danielsian commented Aug 28, 2024 • edited Loading

spmp commented Nov 4, 2024

ghost commented Jan 22, 2019 •

edited by ghost

Loading

LukePWilkins commented Dec 2, 2019 •

edited

Loading

kkava commented Apr 2, 2021 •

edited

Loading

LukePWilkins commented Sep 5, 2021 •

edited

Loading

inspiredearth commented Oct 10, 2022 •

edited

Loading

inspiredearth commented Mar 5, 2023 •

edited

Loading

Turbo-Jet commented Mar 21, 2024 •

edited

Loading

danielsian commented Aug 28, 2024 •

edited

Loading