Working with Ceilings or Max Thresholds

Solved5.61K viewsFormulas and Functions

Here is a challenge for you that I’ve been trying to wrap my mind around for a few days. I suspect it has to be easier done in Quantrix than what I’m doing, so I’m asking for suggestions.

In the attached model I am attempting to calculate tax payments. The model has 3 matrices

1. PayRate – By employee by month by year is a monthly wage.
2. TaxParameters -By Tax the monthly rate paid up to the Salary Cap. After that ceiling is met the tax is 0.
3. TaxDue – Here I perform the calculations to compute the tax due.

Step 1: Calculates a running total of the pay rate over the months .
Step 2: Calculates a tax to be paid on the running total applying the tax rate in Tax_Parameters
Step 3: Used to identify if the tax ceiling is reached in step 2.
Step 4: Logical expression to compute the tax due based off the tax parameters.
Because step 4 formula will not work for month 1 I’ve inserted another formula for month 1.

Any thoughts on how to condense this or simplify possibly into one formula without it looking hideous is appreciated.



So I’ve been looking at this further and for this to work in subsequent years beyond the first listed a couple of things need to be changed.
Step 1 Running Total in the sublist function needs to be updated from
[b:q7syo37m]sublist(PayRate::Year:Month, 1, #Month) [/b:q7syo37m]
, CASE(#Year, 1,1,
#Year + ((#Year -1)*12) ) //Take the year index and add it to the year index less 1 and times that by 12. This gets you to the correct index position to sublist from.
, #Year:Month)

Step 2 Tax Due also needs to be changed to use soft recursion on all months other than 1 (We want the Running total to reset at the start of each year). Therefore a case statement has been added to this formula anytime soft recursion was used (I suppose maybe removing timeline would negate this but…I’m sticking with timeline)
[b:q7syo37m]CASE(#Month,1, 0, ‘Step 1 – Tax Running Total’:Month[~PREV])[/b:q7syo37m]

The entire goal of this question was to come up with a more simple way to calculate..not sure it was accomplished, but nonetheless this is a solution.

See the attached model.

Maybe one of these days I will do a video on this… :)

You are viewing 1 out of 5 answers, click here to view all answers.

Latest Questions