Calculation based on staggered percentage increases, but with certain parameters

Hi Everyone,
I've been working on an excel sheet to auto calculate taxes for me, and I have managed to get most of it sorted but am struggling with the last part, I cannot get the YTD USC calculations to do what I want them to do.

If you could please have a look at the tab called 2018(2) and try to assist.

You'll see on the left hand side, there is the thresholds, so the first €1001 per month is charged at 0.5%, then the next €613.33 per month (difference between 1001 and 1614.33) is charged at 2%, then the next €4222.67 per month (5837-1614.33) is charged at 4.75%, and then anything above €5837 per month is charged at 8%.

My example is looking at month 2, and the figure being taxed is €6000. The correct calculations are highlighted in orange, however my formulas in the table above are returning me an incorrect figure, it is not able to deal with earnings below the highest threshold and is trying to refund too much money.

I have looked at first calculating the amount inbetween each threshold, and then multiplying them by the relevant percentage and adding them together, I'm not sure if I need to split it out more and create more stages. I have been scratching my head looking at this for some time and am eager to learn if there is a solution, any help would be greatly appreciated!

Thanks,

Mark

Thanks daddylonglegs, I'll have a look through that. Not really a task for a Friday afternoon but I'll see how far I get! Will let you know if (when) I struggle with any of it.

Originally Posted by daddylonglegs

Hello MarkyBoi,

Welcome to excelforum

Here’s one way to do that

http://www.mcgimpsey.com/excel/variablerate.html

I had a look on Friday and couldnt work out how to do it! Will have another look today.

Originally Posted by kvsrinivasamurthy

IN K19 it is shown

=769.34*4.75%

What is this 769.34.

Hi,

So the total figure we are calculating tax for is H11 which is €6000

K17 shows the first 2002 is taxed at 0.5% (1001 X 2)
K18 shows the next 1226.66 is taxed at 2% (613.33 X 2)

The next tax bracket is 4.75%. If you look at J30 and K30 you will see the Lower and Higher threshold for the 4.75% bracket. The total figure we are looking at is €6000 so we can see that some of it is chargeable at the 4.75%, but it doesn't fill the whole bracket as €6000 is less than K30.

We have already taxed 2002 and 1226.66. So if you take 6000 - 2002 - 1226.66 = €2771.34. So 2771.34 is what needs to be taxed at 4.75%, not 769.34, that was my mistake.

Originally Posted by kvsrinivasamurthy

This works. In M9

=(MAX((MIN(H3*C8,H11)-H3*C7)*D8,0)+MAX((MIN(H3*C9,H11)-H3*C8)*D9,0)+MAX((MIN(H3*C10,H11)-H3*C9)*D10,0)+MAX((MIN(H3*C11,H11)-H3*C10)*D11,0))

You are an absolute legend, thanks so much! That has solved my major headache and taught me something new, I really appreciate that.