Income tax calculation UK

[cp12345]

HI,

I'm trying to develop a formula which will help me calculate income tax dynamically for multiple income figures.
tax.PNG

in the attached image there is a banding, and the logic would be as follows:

if the 46773 falls within the first range then 46773*8.80%
then if it falls within the second banding then deduct 46773 - 4116 (which is the value from above calc) then do 42657*11.05%

hope this help as i have been stuck on it for a while now

[JohnTopley]

Are you sure about the calculation ?

You pay 21402 at 8.8 % and the balance (46733-21402) at 11.5% ?????

[Glenn Kennedy]

One way??

=SUMPRODUCT(--(A2>E2:E3),--(A2-E2:E3),(G2:G3-N(+G1:G2)))

[JohnTopley]

@Glenn: I think the 21403 should be 21402 in your table.

and

=SUMPRODUCT(--(A2>=E2:E3),--(A2-E2:E3),(G2:G3-N(+G1:G2)))

????

[Glenn Kennedy]

You're right. It should. Just having my first coffee of the day, so maybe still not quite awake. File replaced...

[cp12345]

Hi Glenn, thank you, do you mind quickly explaining what the formula does? many thanks in advance

[Glenn Kennedy]

=SUMPRODUCT(--(A2>E2:E3),--(A2-E2:E3),(G2:G3-N(+G1:G2)))

Red returns TRUE for each tier where a charge will be due (there are only two in your example, but it's easily scaleable for multiple tiers)

Orange calculates how many units have been used in each tier

Cyan calculates the Price CHANGE at each tier - the N(+XXXX) bit converts the text header to a blank, which would otherwise have caused the formula to fall over.

It multiplies the 3 components and adds up the results to give you your final answer.


Go to formulas /evaluate formula and step through the process.


You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.