# How to set up multiple taxes inclusive formula in Excel

Closed
Bucks. Posts 1 Registration date Wednesday September 23, 2015 Status Member Last seen September 23, 2015 - Updated on Jun 18, 2017 at 07:41 PM
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

## 2 replies

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Sep 23, 2015 at 01:17 PM
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
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