Excel formula for calculating NZ tax paid on annual income.

gistfile1.txt

=IF(B2<=14000,SUM(B2*10.5%),IF(B2<=48000,SUM(B2-14000)*17.5%+1470,IF(B2<=70000,SUM(B2-48000)*30%+7420,IF(B2>=70001,SUM(B2-70000)*33%+14020))))

Very useful! Thanks for this :)

Here's the same formula but for monthly:

=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)))

Sweet, just what I needed when I needed it.
Thank you.

So I get different paye totals using this and ird website

Here's a tweaked version that rounds the total down like the IRD online calculator does. Works in Google Sheets, haven't tested in Excel.

=IF(ROUNDDOWN(B2,0)<=14000,SUM(ROUNDDOWN(B2,0)*10.5%),IF(ROUNDDOWN(B2,0)<=48000,SUM(ROUNDDOWN(B2,0)-14000)*17.5%+1470,IF(ROUNDDOWN(B2,0)<=70000,SUM(ROUNDDOWN(B2,0)-48000)*30%+7420,SUM(ROUNDDOWN(B2,0)-70000)*33%+14020)))

Thanks so much for sharing the formula.

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

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)))

@alangrainger
Thanks very much!

@alangrainger thanks mate!

Thanks, very useful!

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

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!

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)))

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

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))))

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: ***@***.***>

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)))))

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!

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)))))

Thank you, CMac89 :)

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.

Thank you <3 This is even better.

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?

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.

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

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))

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