Adding a percentage when over a certain amount

[doggax99]

Hello all,

I'm pretty new to Excel and I have learnt the basics over the past couple of weeks but i'm really struggling now i'm trying to add more in depth formulas.
Basically I have just starting my own company and I have created a basic Excel template for my accounts but I would like to add to the total a percentage for tax and national insurance once the total goes over a certain amount but only on the wages not the dividends
National Insurance:
Class 2 if your profits are £6,025 or more a year (£2.85 a week)
Class 4 if your profits are £8,164 or more a year (9% on profits between £8,164 and £45,000, 2% on profits over £45,000)
Income TAX:
£11,501 - £45,000 20 per cent (basic rate)
£45,001 - £150,000 40 per cent (higher rate)
Over £150,000 45 per cent (additional rate)

I have attached the excel file for you to have a look at

Thanks
James

[AliGW]

The formula in column D only needs to be this:

=SUM(B5:C5)

So, what exactly are you asking us to do? Where are these calculations meant to go? All you have given us is an incomings and outgoings table.

[Richard Buttrey]

Does the attached help.

I've moved the Total cells to row 5. I prefer to see totals above the data since there is then no restriction when adding data.


Class 2 NIC =IF(C5>=6025,52*2.85,0)
Class 4 NIC =IF(C5>8165,(45000-8164)*9%+IF(C5>45000,(C5-45000)*2%),0)
Income Tax @ 20% =MAX(0,(C5-11500)*20%)
Income Tax @ 40% =MAX(0,(C5-45000)*40%)
Higher Rate =MAX(0,(C5-150000)*45%)

[doggax99]

Thanks Richard that's an amazing job! but there's only one little problem, if I was paid £200,000 it calculates that I have to pay all 3 types of income tax on the full 200,000 rather than then each bracket.
For example if I was paid £200,000 I would pay 20% on the first (£11,501 - £45,000) and then 40% on the (£45,001 - £150,000) and then 45% on the (£150,000 - £200,000) does that make sense?
Thanks
James

[doggax99]

Thanks Ali but I think Richard has figured it out

[doggax99]

Does the attached help.

I've moved the Total cells to row 5. I prefer to see totals above the data since there is then no restriction when adding data.


Class 2 NIC =IF(C5>=6025,52*2.85,0)
Class 4 NIC =IF(C5>8165,(45000-8164)*9%+IF(C5>45000,(C5-45000)*2%),0)
Income Tax @ 20% =MAX(0,(C5-11500)*20%)
Income Tax @ 40% =MAX(0,(C5-45000)*40%)
Higher Rate =MAX(0,(C5-150000)*45%)

Thanks Richard that's an amazing job! but there's only one little problem, if I was paid £200,000 it calculates that I have to pay all 3 types of income tax on the full 200,000 rather than then each bracket.
For example if I was paid £200,000 I would pay 20% on the first (£11,501 - £45,000) and then 40% on the (£45,001 - £150,000) and then 45% on the (£150,000 - £200,000) does that make sense?
Thanks
James

[JohnTopley]

Try

for Income Tax

=SUMPRODUCT(($C5>={0,11500,4500,150000})*($C5-{0,11500,45000,150000}),{0,0.2,0.2,0.05})

[doggax99]

That's a good one John thanks, I have added it as a total underneath but now the original 3 income tax brackets Richard made are still incorrect, how can I change them to match up and give me a break down of the total Income TAX you made?
Thanks
James

[AliGW]

James - please don't quote whole posts - it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

[doggax99]

For normal conversational replies, try using the QUICK REPLY box below.

Do you mean like this Ali? Thanks James

[AliGW]

No - when responding to the previous post, you do not need to quote anything. Thanks for your co-operation.

[doggax99]

I thought I had to quote the message of the person I was responding to so they would get a notification that I have responded to their question? If I post without their quote do they still get a notification? Thanks James

P.s Is this how you would like me to respond?

[AliGW]

Yes - just respond to the thread. Let's not clutter the thread up any further - I have explained the optimum way to respond above. Thanks.

[JohnTopley]

In H5

=MAX(0,(45000-11500)*20%)

in H6

=MAX(0,(150000-45000)*40%)

in H7

=MAX(0,(C5-150000)*45%)

You may want to make the tax bands and %s as variables so accommodate tax changes.

[doggax99]

I've just tried them John but it has messed the maths up now, when I put in £500 wages in, it confuses it and all the sums are wrong Thanks James

[Richard Buttrey]

Hi,

Apologies. Try these instead

20% rate

Please Login or Register  to view this content.

Higher 40% Rate

Please Login or Register  to view this content.

Additional > 150K

Please Login or Register  to view this content.

[doggax99]

Thanks Richard, look spot on now

[JohnTopley]

Sorry bad day at the office!!!

in H5

=MAX(0,(MIN(C5,45000)-11500)*20%)

in H6

=MAX(0,(MIN(C5,150000)-45000)*40%)

in H9

=SUMPRODUCT(($C5>={11500,45000,150000})*($C5-{11500,45000,150000}),{0.2,0.2,0.05})

[doggax99]

Sorry to bother you guys again but I'm really confused because I have just tried to add my wages as 8500 and it should calculate 9% of 8500 = 765 (Class 4 NIC) but instead it says 3315.24. Does anyone have any ideas why it works it out to be this much? Thanks James

[Richard Buttrey]

Sorry to bother you guys again but I'm really confused because I have just tried to add my wages as 8500 and it should calculate 9% of 8500 = 765 (Class 4 NIC) but instead it says 3315.24. Does anyone have any ideas why it works it out to be this much? Thanks James

Hi,

OOps!. Please change that NIC Class 4 to

Please Login or Register  to view this content.

[doggax99]

Thanks Richard

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.