How to calculate net price before tax, when only have price after tax?

We often need to calculate the price of an object before tax when we only know the price after tax and what tax was applied.  For a single tax it is simple, divide by (1 + Tax%)

i.e.  10% tax on price after tax of 10% =   110 / (1 + .1) = 100

However, I am having a problem when there are multiple taxes applied, and some are compound taxes. i.e.

100 + 10% VAT + 10% Compound State = 100 + 10 + 11 = 121, so how do I calculate backwards.

To add to the complication, sometimes the second tax is not compounded, i.e.

100 + 10% VAT + 1-% State = 100 + 10 + 10

To further complicate this, I have an example where there are three taxes, one VAT type tax, and two withholding taxes, again these taxes can be compounded or may not be.

In each case I know the tax rules, and the after tax price, and need to calculate the pre-tax price.

Any help would be greatly appreciated.  I have spend 2 days playing with Excel, but the final equation escapes me.
LVL 6
townsmaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mccarlIT Business Systems Analyst / Software DeveloperCommented:
100 + 10% VAT + 10% Compound State = 100 + 10 + 11 = 121, so how do I calculate backwards.

If it's compounded, just do it in steps (and back to front), so for the above..

121 / (1 + 0.1) = 110                   // Compound state first

110 / (1 + 0.1) = 100                   // Then VAT

or...

( 121 / (1 + 0.1) ) / (1 + 0.1) = 100


If it's not compounded you can do it all in one step, ie...

120 / (1 + 0.1 + 0.1) = 100
townsmaAuthor Commented:
@MCCarl, Many thanks for that. It seems so easy.  I am sure I tried that but it didn't work in Excel for some reason. Will try it again.

How will it work if the tax is a Withholding tax, i.e. a minus tax?
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Can you provide an example of the withholding tax?
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

townsmaAuthor Commented:
100  +10% VAT -5%WHT = 100 + 10 - 5.5 = 104.5

Thanks
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Ok, so the exact same formula still applies, ie. do them in separate steps, back to front, dividing by (1 + Tax)

It still works if tax is a negative, so...

104.5 / (1 + (-0.05)) = 110        // is the same as 104.5 / (1 - 0.05) = 110

110 / (1 + (+0.1)) = 100

or again in one go...

(104.5 / (1 - 0.05) ) / (1 + 0.1) = 100


And again if the WHT is not compounded, ie. 100 +10% - 5% (not compounded) = 100 + 10 - 5 = 105

105 / (1 + 0.1 - 0.05) = 105

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
townsmaAuthor Commented:
Works fine thanks.

It was the 1-% that I missed, I was minusing 1+%, which throws it out.  

Thanks for your help.
mccarlIT Business Systems Analyst / Software DeveloperCommented:
You're welcome!!
townsmaAuthor Commented:
I hate to be a nuisance, but I hoping you can help me with one more.  I fully understand what you did above, but I now have a test case where these do not work.

We actually have three taxes, local (10%), state(10% compound), and withholding (-5% not compound)

So adding taxes is 100 + (100 * 10%) + (110 * 10%) - (100 * 5%) = 116

Now how do I work backwards from 116 to get to 100?

Again sorry to be a pain, and I hope you don't mind helping me.
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Ok, so you just have to think of the calculation with the original value (100) factored out, so...

100 * ( (1 + 0.1) * (1 + 0.1) + (-0.05) ) = 116

and so then reversing the calculation is easy...

116 / ( (1 + 0.1) * (1 + 0.1) + (-0.05) ) = 100
townsmaAuthor Commented:
Great, thanks again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Tax / Financial Software

From novice to tech pro — start learning today.