Report

How to set up multiple taxes inclusive formula in Excel [Closed]

Ask a question Bucks. 1Posts Wednesday September 23, 2015Registration date September 23, 2015 Last seen - Last answered on Jun 17, 2017 at 05:21 AM by U Know Me
I have a reverse calculation problem-
Lets say the price of an item is $10.00 with two taxes @ 10% and 20%.
Now to calculate the inclusive tax amounts - a simple calculation would give you $1 and $2 respectively leaving you with $7.00.
Here's the rub-
If I start with $7 and then apply the taxes I get .7 and 1.4 totaling $9.10
Obviously there is another ratio calculation I'm missing since the correct number to get back to $10.00 are a $7.69 price plus .77 and 1.54 in taxes totaling $10.00

any help is greatly appreciated - thank you in advance
See more 
Helpful
+1
plus moins
Your original tax calculation is wrong.

If the taxes are inclusive at 10% and 20%, then your calcs should be
10 / 1.1 (to get base price) = 9.09 = 0.91 tax
10 / 1.2 (to get base price) = 8.33 = 1.67 tax

Your true base price is therefore 10 - both tax amounts = 10 - 0.91 - 1.67 = 7.42

Because you are using multiple tax lines, you can't simply multiple it back out (ie. I can't go 7.42 * 1.1 * 1.2) because this is not the inverse of the original tax calculation.

However, something like:
7.42 + (9.09 * 0.1) + (8.33 * 0.2) will work (assuming rounding to 2 decimals also)
U Know Me- Jun 17, 2017 at 05:21 AM
Divide Total Amount (Including Tax) By (100+ Tax % 1 + Tax % 2) * 100
This Will Give You Base Price.

Note: This Is Applicable Only If Both Taxes Are On Base Item Rate & Not On Tax On Tax
Reply
Leave a comment
Helpful
+0
plus moins
Bucks, Good afternoon.

Issues involving taxes are always complicated to understand.
This happens anywhere in the world.
Accept then, is an almost impossible thing. ha ha ha
That's my opinion.

Returning to the main issue ...

If taxes are within the price so you do not have 100% of their price only.

You have 100% of their price PLUS taxes.
In your case over 30%.
Therefore, its price is 130%.

So $10.00 is 130%
therefore:
10% of the price is $0.77
20% of the price is $1.54
Your actual price is $7.69

The formulas:

A1 = 10
B1 --> tax --> 30%

C1 --> TAX --> =A1/13*3
D1 --> Your REAL Price --> =A1-C1

Then...
TAX 10% --> =A1/13
TAX 20% --> =A1/13*2

Compoundig Final PRICE:

D1 --> Your price =$7.69
D2 --> Tax =30%
c3 --> FINAL price --> =D1*(1+D2)

Is that what you're looking for?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!