Loop ( Iterative Macros ) for tax calculation

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 !

Benefit & Tax Calculation.xlsx

Iterative Macro

Accepted answers

FinnCharlton

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.

All comments

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

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 !

Quick Links

This months top contributors

mceleavey 383

AkimasaKajitani 382

mbarone 337

Hollingsworth 335

LanisC 335