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
U Know Me - Jun 17, 2017 at 05:21 AM
Related:
- Inclusive tax calculation formula in excel
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- Date formula in excel dd/mm/yyyy - Guide
- Vat calculation formula - Guide
- Calculation is incomplete. recalculate before saving - Guide
2 responses
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Sep 23, 2015 at 01:17 PM
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
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)
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)
