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.


Latest Questions