As per our tax calculators, it is relatively easy to work out the tax on a person earning a constant salary. You know the salary, you know the PAYE that should be paid, and you can divide it evenly over the years and be fairly sure that by the end of the year, the person will be up to date. But what about people who earn a variable salary through things like commissions. One month they may shoot the lights out and the next month will be dead. So how do you calculate PAYE for sales people in Excel.
The wrong way to estimate the monthly tax
Below some estimated salary numbers for a sales person.
Note that by the end of the year they earned R387 000 which requires tax of R76 746. However, if on a monthly basis (in column G and H) we only look to the future and estimate the taxes (so we annualise the current situation into the future and then tax that) we effectively ignore what has already been paid.
So below you will see that we ended up paying R2 492 too much (at least we are being conservative and should get a refund :)).
You need to take prior tax payments into account
In order to get this right, you need to take into account what the future AND past looks like.
So a more accurate way is to keep track of the cumulative salary and cumulative tax paid over time.
In row 6 below, we can see that annualised the person will earn R516 000 (48 000 x 10 +36 000) IF things continue as is.
This means that we need to calculate the tax payable on this amount (R122 220) but then subtract the tax we’ve paid to date (R7 230.91) and divide the answer by the number of months left.
In an extreme case like row 14 where the income is very low, this may mean that the person doesn’t need to pay anything but the end result is that the total tax paid, matches the tax calculated on the annual amount.