Loop ( Iterative Macros ) for tax calculation

[fikrizahari]

Hi,

Greetings!

I would like to ask if there is any solution to create loop for calculation that have circular calculation.
The requirement is to match 'Net Salary' and 'New Net Salary' based on additional 'Benefit'.
Previously, we can only 'try and error' on 'Benefit' ( green color cell ) until the 'New Net Salary' is correct.

Please find my attachment on the details steps on how we calculate before.
Thank you !

[Christina_H]

Maybe I'm misunderstanding something, but based on your calculations it seems like the new benefit is just the old benefit multiplied by 1.25, no need for iteration.

[FinnCharlton · Accepted solution]

Surely you can just solve this with algebra? I don't think there's any need for iteration.

GS=Gross Salary

I=Insurance

OB=Old Benefit

NB=New Benefit

Therefore old tax = (GS)/5 and new tax = (GS+NB)/5

The old and new net salaries must be the same, so:

GS - I - (GS)/5 + OB = GS + NB - I - (GS+NB)/5

This simplifies to:

OB - (GS)/5 = NB - (GS+NB)/5

And then again to:

-GS = 5NB - 5OB - GS - NB

Meaning:

5OB = 4NB , or NB = OB*1.25

Your new benefit will always be 1.25* your old benefit.

[fikrizahari]

Hi @Christina_H and @FinnCharlton ,

Thank you for your suggestion.
It surely gives me an idea to use algebra and mathematics solution instead.


Actually, the calculation for tax much more complex than just 20%
The rate just an example to ease the understanding of question.

Below is the actual calculation, but not for you to calculate.
Just to show you the complexity:

Thanks a lot for the help !