How to built exemption formula

[Shaitaan]

Hello Experts,

I am working on exemption formula which exempts first 50,000 dollars and as soon as that 50,000 is reached the excess amount is subject to tax.

Semi_Monthly_Income	Exemption_for_Yr	Exemption_Used	Exemption_Balance	Subject_to_Tax_Amount	Tax_Rate	Tax_Amount	Payment #
30,000	50,000	50,000-30,000	20,000	0.00	2%	0.00	1
30,000	50,000	20,000-30,000	0.00	10,000	2%	200	2
30,000	50,000	0.00-30,000	0.00	30,000	2%	600	3

Now issue is how do I built this formula to keep track of exemption_used, exemption_balance & subject_to_tax_amount columns. Keeping in mind if there is a reversal the exemption available should automatically be updated.

Looking for help !

Thanks,

[#2]

@Shaitaan 

The formula isn't probably the biggest issue you face. You don't describe, for one thing, the rest of your spreadsheet, how you are going to track income for individuals. What the whole spreadsheet is about.

Are you in HR or Payroll? Are you just looking for a single formula, or will you be tracking income (and other deductions, payments) on a weekly, semi-monthly, monthly basis? Will the tax be taken out on a pro-rated basis once the 50,000 exemption level has been passed, or is it an end-of-the-year thing?  Are there other taxes, other deductions?

Is it possible to post a copy (or a mock-up) of your whole spreadsheet? Just make sure it has no confidential or private personal information in it.

[#3]

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,

[#4]

@Shaitaan 

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.

[#5 · Accepted solution]

@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.

[#6]

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. 

[#7]

@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.