gladiator13
MIS
I have a report that adds up Agent's Commissions for all commissions agents earn for our company throughout the year. We allow tax free commissions of up to $1500 dollars and then start taxing them after that point on any commissions earned above the tax free commissions allowed in this case being $1500 times the tax rate. Now the real question, I have a report that gives me the amount of commissions earned per agent in one column and the running sum in the next, so I thought the logical equation to build was:
IF the running sum of the agents commissions earned goes above the allowed tax free commission(1500) then multiply the tax rate times those commissions earned at that point in time. However when the running sum goes above the tax free commissions(1500)for the first time the equation will multiply the tax rate times those commissions earned in that point in time NOT giving the leeway for both the commissions earned and the running sum the amount of tax free commissions. I think my example below will clear what I am saying up
MY automated equation is
IF
YEAR TO DATE COMMISSIONS > TAX FREE ALLOWABLE then
WITHOLDINGS=AGENTS COMMISSIONS EARNED * AGENT TAX RATE
ELSE
WITHOLDINGS=0.00
WHICH PRODUCES THIS
1st RECORD FOR AGENT 1
AGENTS COMMISSIONS EARNED: $297.31
YEAR TO DATE COMMISSIONS: $297.31
COMMISSION WITHHOLDING: 0.00-correct since it is not above the tax free commissions of $1500
2nd Record for Agent 1
AGENTS COMMISSIONS EARNED: $1448.74
YEAR TO DATE COMMISSIONS: $1746.05
COMMISSION WITHHOLDING: 101.41- INCORRECT, the tax free allowable commission is $1500 so since the YEAR TO DATE COMMISSION is above my tax free allowable amount it multiplied the tax rate(7% in this instance)times the commissions earned in that point in time of $1448.74 not allowing the leeway of $1500 allowed!
So for the first record in which the year to date commissions earned goes above the allowable tax free commissions it should just multiply the tax rate times the amount of money above the allowable amount
YTD-$1746.05-$1500.00( TAX FREE COMMISSIONS)so only
$246.05 should be taxed.
PLAIN AND SIMPLE: HOW DO I GET MY EQUATION TO DO THIS STARTING ON THE FIRST RECORD THAT GOES ABOVE THE $1500 TAX FREE AMOUNT!!
I AM DESPERATE!!!
THIS SOUNDS VERY COMPLICATED I KNOW!!! HELPPPPPPPP!!!!!
IF the running sum of the agents commissions earned goes above the allowed tax free commission(1500) then multiply the tax rate times those commissions earned at that point in time. However when the running sum goes above the tax free commissions(1500)for the first time the equation will multiply the tax rate times those commissions earned in that point in time NOT giving the leeway for both the commissions earned and the running sum the amount of tax free commissions. I think my example below will clear what I am saying up
MY automated equation is
IF
YEAR TO DATE COMMISSIONS > TAX FREE ALLOWABLE then
WITHOLDINGS=AGENTS COMMISSIONS EARNED * AGENT TAX RATE
ELSE
WITHOLDINGS=0.00
WHICH PRODUCES THIS
1st RECORD FOR AGENT 1
AGENTS COMMISSIONS EARNED: $297.31
YEAR TO DATE COMMISSIONS: $297.31
COMMISSION WITHHOLDING: 0.00-correct since it is not above the tax free commissions of $1500
2nd Record for Agent 1
AGENTS COMMISSIONS EARNED: $1448.74
YEAR TO DATE COMMISSIONS: $1746.05
COMMISSION WITHHOLDING: 101.41- INCORRECT, the tax free allowable commission is $1500 so since the YEAR TO DATE COMMISSION is above my tax free allowable amount it multiplied the tax rate(7% in this instance)times the commissions earned in that point in time of $1448.74 not allowing the leeway of $1500 allowed!
So for the first record in which the year to date commissions earned goes above the allowable tax free commissions it should just multiply the tax rate times the amount of money above the allowable amount
YTD-$1746.05-$1500.00( TAX FREE COMMISSIONS)so only
$246.05 should be taxed.
PLAIN AND SIMPLE: HOW DO I GET MY EQUATION TO DO THIS STARTING ON THE FIRST RECORD THAT GOES ABOVE THE $1500 TAX FREE AMOUNT!!
I AM DESPERATE!!!
THIS SOUNDS VERY COMPLICATED I KNOW!!! HELPPPPPPPP!!!!!