Forum Discussion
How to built exemption formula
mathetes Had a look at it also and wondered about the purpose of so many columns with seemingly redundant information. I changed the formulae in P and Q and added three columns (U, V and W), just to demonstrate how I would have approached it. But perhaps I missed the point altogether. Up to Shaitaan to judge.
Hello mathetes ,
You got it right its for Payroll calculation. The mockup information attached in excel in this reply.
if you see the excel sheet it has annual payroll values based on payroll frequency. in this case its semi-monthly payroll execution value, which will be entered manually. i have built the formula but the challenge i am getting is when the payroll is reversed. If the payroll is reversed when exemption is not reached its working fine. But when payroll exemption thrash-hold is reached or partially reached then calculation messes up. In attached sheet in A27 when first payroll was executed with 30k the exemption had room for 10k and rest of the 20k is subject to tax. But in A28 that payroll got reversed and entire payroll value of 30k is going towards exemption whereas, only 10k should be going towards exemption reversal and rest 20k should not. Column N & S are same only difference is N is coming from step by step calculation columns whereas, S is using single formula.
Do let me know if you need more explanation on the columns and its values.
Thanks,
I apologize for the long delay in responding. We had guests last evening, so I had to put this aside.
This was an interesting illustration of how difficult it can be to try to make sense of a spreadsheet somebody else has designed. So although I've come up with what I hope is a step in the direction of resolving your problem, it's clearly not altogether right.
IF, big "if", your reversal of a payroll ALWAYS is in the row right after the instance that is being reversed, it seemed to me that maybe the simplest way to reverse things was to add an IF clause to the formulas involved, such that
-IF(C12="RE",-1*valueInRowAbove,YourPreviousFormula)
which takes whatever the value was in the prior row and simply negates it (multiplies by -1); if it's not an RE row, then your prior formula still works.
I don't fully get what's happening in column O, so you'll need to weigh in.
The basic idea, though, is just to, well, reverse that prior row, since that's what you are doing.
mathetes Had a look at it also and wondered about the purpose of so many columns with seemingly redundant information. I changed the formulae in P and Q and added three columns (U, V and W), just to demonstrate how I would have approached it. But perhaps I missed the point altogether. Up to Shaitaan to judge.
Hello Riny_van_Eekelen ,
The working you did really helped. The additional columns in the excels were made to understand the step results as what is going on and how the final formula will look like.
The formula you made is good but I need other way round like you are calculating tax on YTD subject wages (column V) whereas, column V should be aggregate of column W.
Shaitaan Not sure I follow your last sentence. I calculate the YTD tax number and the monthly tax amount (or actually, the tax amount due after each transaction) equals current YTD minus prior year to date. In your example, you have three transactions in July (+30000 -30000 +40000), thus +40000 in total. Of these 40000, 30000 are taxable at 1.95%, thus 585. On rows 24:26 column W, this is reflected by +390 - 390 + 585 = 585.
In other words, the reversal of the initial 30000 cancels out the initial tax payable (390) and the corrected payment of 40000 leads to a new tax amount of 585. In your own example you present it as if the initial 390 remains and 195 is added for the extra 10000. Same end result, but not consistently calculated, resulting in the incorrect amount for the YTD exception used.