Working with Ceilings or Max Thresholds

Solved5.61K views
0
0 Comments

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.

–Rich

5 Answers

0

Hi – this may still fit the ‘hideous’ category ðŸ™‚ but I got the steps down to two and used a switch statement instead of nested IF’s to make the formula more readable. Model is attached. Hope this helps!

-Mike

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