Calculate a value that fits withing a cap?

Not really sure what to call it, but I work with a lot of taxation and there is a lot of "what if" in my work. One problem that I can't seem to get started on is as follows:

Employee has a $20,000.00 Cap.
Employee has uses $10,000.00 so far.
With each amount, there is a benefit called tax assistance applied. So in reality the employee may not get all the $10k remaining, but more so $7k as then $3k of tax assistance would be appied to make up the $10 total. But it's going to be based of other specifics such as their policy (4 of them). So I'm looking to put a formula in place that will give me the max amount they could be paid without going over after tax assistance is applied. I think I've done this in the past, but for the life of me I can't either remember or I am thinking of something else.

Originally Posted by motown

Not really sure what to call it, but I work with a lot of taxation and there is a lot of "what if" in my work. One problem that I can't seem to get started on is as follows:

Employee has a $20,000.00 Cap.
Employee has uses $10,000.00 so far.
With each amount, there is a benefit called tax assistance applied. So in reality the employee may not get all the $10k remaining, but more so $7k as then $3k of tax assistance would be appied to make up the $10 total. But it's going to be based of other specifics such as their policy (4 of them). So I'm looking to put a formula in place that will give me the max amount they could be paid without going over after tax assistance is applied. I think I've done this in the past, but for the life of me I can't either remember or I am thinking of something else.

Is the tax assistance a % or an absolute value?

You're implying there are other policy factors which also need to be incorporated in the solution. Obviously we need to know what they are and how they operate.

Rgds

For this we would say the tax assistance is a fixed value gathered from another calculation. I was able to use the Solver tool and run/save a scenario that makes the application work solid, the only issue is that only works for one amount you need it to equal, and I don't want to create thousands of secnarios. But that tool works the problem to a T, it's just not as editable as desired.

Originally Posted by motown

For this we would say the tax assistance is a fixed value gathered from another calculation. I was able to use the Solver tool and run/save a scenario that makes the application work solid, the only issue is that only works for one amount you need it to equal, and I don't want to create thousands of secnarios. But that tool works the problem to a T, it's just not as editable as desired.

Hi,

I'm still not understanding this from your description.

Can you give some typical worked examples showing the starting data, the result you expect and how you calculate the result.

Rgds