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

Bucks. 1 Posts Wednesday September 23, 2015Registration date September 23, 2015 Last seen - Sep 23, 2015 at 12:12 PM - Latest reply:  U Know Me
- Jun 17, 2017 at 05:21 AM
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 

3 replies

Mazzaropi 1826 Posts Monday August 16, 2010Registration dateContributorStatus April 15, 2018 Last seen - Sep 23, 2015 at 01:17 PM
0
Helpful
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
-1
Helpful
1
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)
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