In theory, we can find the amount of tax to pay using a VLOOKUP() function. However, examples showing how to do that are usually over simplified and they cannot cope with the situation I faced the other day.
Table of taxes
You can see that if someone earns up to and including 3132, they pay no tax. If they earn up to and including 3972, they pay tax at 5% on the income from 3172 to 3972. If they earn up to and including 5172, they pay tax of 5% on the amount 3972 – 3172 plus 10% of the amount between 3972 and 5172. And so on. An incremental tax rate.
As you can see I ahve programmed this is two ways:
- in the table, line by line
- as an IFS statement
There is an Excel file to download to help you here but do try to solve this problem yourself and if you find an easier way to do it, please feel free to write and suggest how that might be!
As an aside, I created the following tax schedule to show that an apparently non linear or step function can actually reveal itself to be linear or almost linear. In this case, I used polynomial order 2 for this but a linear function is very similar to this and could be used instead. This means that you could approximate the tax computation quite successfully!
Here is the file to download
14th November 2018