# How to set up multiple taxes inclusive formula in Excel

Closed
Posts
1
Registration date
Wednesday September 23, 2015
Status
Member
Last seen
September 23, 2015
-
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
Related:

## 2 replies

Posts
1947
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
November 11, 2021
147
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.
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

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:

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
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