Excel - Sliding decreasing scale (payroll tax calc)

[Libbo]

Hello,

I am trying to create a formula that will calculate the amount of threshold i will receive for Payroll tax.

Wages up to 1.1 mil get full threshold, but for every $4 over 1.1mil the threshold decreases by $1. (until 5.5 mil where it is zero)

i have been trying to use the 'IF' function but i cant wrap my head around it.

Surely i wouldn't have to create some sort of table detailing the threshold amount for every $4 change? there must be a quicker way???

please help!

thanks in advanced everyone!

 

[Becky]

Welcome to the forum!

Yep, you are right that this can be done using the IF formula, but you will need three nested IF formula. The first asks "is income lower or equal to the threshold?" and if so the full threshold is available; the next asks "is income between 1.1m and 5.5m?" and if so then for every $4 over 1.1m, the threshold decreases by 1$; and the final IF function asks "is income over 5.5m?" and if it is then there is 0 threshold available.

This is how it would look as a formula:

=IF(income<=$A$1,$A$1,IF(AND(income>$A$1,income<$B$1),$A$1-(ROUNDDOWN((income-$A$1)/4,0)),IF(income>=$B$1,0)))

where $A$1 is 1,100,000 and $B$1 is 5,500,000 (it's best to have these figures on your spreadsheet so that if they change you just type them in and don't need to change the formula. The dollar signs mean the cells won't change if you copy this formula down a list). Obviously you will need to reference the income cell(s) where I have typed 'income'. The ROUNDDOWN is there to make sure that the threshold is adjusted for every whole $4 above 1,100,000. That is, 1,200,000 and 1,200,002 income levels have the same threshold available to them.

Hope this helps! Let me know how you get on.

 

[AmjiBhai]

I use Vlookup formula as an alternate to nested IF