Working with Ceilings or Max Thresholds

Generic selectors
Exact matches only
Search title only
Search in content
Search in all posts
Search in pages
Solved2.78K 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.


Selected answer as best

Hi Rich,

Maybe this model would be the most simple way for the Tax Due calculation.

Selected answer as best
You are viewing 1 out of 5 answers, click here to view all answers.