Calculation of tax breakdown on an order...
Hi,
I have an order that contains two items, item A and item B. They have different net (before tax) prices and they have different tax rates applied. There is one of each product on the order.
Lets say item A is $100 with 5% tax and item B is $50 with 10% tax
A = 100, B = 50
The order total is taken as (A + A * 5%) + (B + B * 10%)
So the order total is 105 + 55 = $160 including tax.
$10 of the total is therefore tax.
What I need is a formula so that given the total tax on the order ($10), and knowing there are two products on the order, and knowing their net price and tax rate, how can I calculate the amount of tax per item.
I need to reverse calculate the tax payable on each individual item from the total tax on the order. I cannot in this scenario just use the item price and tax rate as I used in the example when forward calculating the tax, this has to be worked from total tax back to individual tax. The reason for this is to overcome rounding errors that creep in due to money only being worked with at 4dps. With rounding errors calculating tax forward can give different results to calculating it backwards - hence I need to be able to work from total tax value back to individual item tax value.
Note the solution will form part of an algorithm so a multi step solution woudl be fine.
I have an order that contains two items, item A and item B. They have different net (before tax) prices and they have different tax rates applied. There is one of each product on the order.
Lets say item A is $100 with 5% tax and item B is $50 with 10% tax
A = 100, B = 50
The order total is taken as (A + A * 5%) + (B + B * 10%)
So the order total is 105 + 55 = $160 including tax.
$10 of the total is therefore tax.
What I need is a formula so that given the total tax on the order ($10), and knowing there are two products on the order, and knowing their net price and tax rate, how can I calculate the amount of tax per item.
I need to reverse calculate the tax payable on each individual item from the total tax on the order. I cannot in this scenario just use the item price and tax rate as I used in the example when forward calculating the tax, this has to be worked from total tax back to individual tax. The reason for this is to overcome rounding errors that creep in due to money only being worked with at 4dps. With rounding errors calculating tax forward can give different results to calculating it backwards - hence I need to be able to work from total tax value back to individual item tax value.
Note the solution will form part of an algorithm so a multi step solution woudl be fine.
If you know the items net prices (N1 & N2) and their tax rates (T1 & T2), the tax per item is found by multiplying the price and the tax rate (N1 * T1) and (N2 * T2).
I'm guessing I'm misreading it or something......
I'm guessing I'm misreading it or something......
ASKER
Thanks guys...
...but I mentioned that I don't want to forward calculate the tax based on net and tax rate.
There are scenarios where the total tax is the important figure, and the individual item taxes have to be estimated from their individual tax rate. Hence the need to work backwards from the total tax.
So I'm looking for a way to work out what proportion of the sale total each item represents, and then just take that proportion of the tax total as the tax for that item.
It's turning out to be a tricky problem to solve as I have brain burn at the moment, and it's also quite tricky to explain the question which doesn't thelp ;-)
...but I mentioned that I don't want to forward calculate the tax based on net and tax rate.
There are scenarios where the total tax is the important figure, and the individual item taxes have to be estimated from their individual tax rate. Hence the need to work backwards from the total tax.
So I'm looking for a way to work out what proportion of the sale total each item represents, and then just take that proportion of the tax total as the tax for that item.
It's turning out to be a tricky problem to solve as I have brain burn at the moment, and it's also quite tricky to explain the question which doesn't thelp ;-)
"With rounding errors calculating tax forward can give different results to calculating it backwards - hence I need to be able to work from total tax value back to individual item tax value"
...but in either case if you're still left with rounding errors, what makes the reverse error more valid than the forward error?
...but in either case if you're still left with rounding errors, what makes the reverse error more valid than the forward error?
ASKER
In my scenario I'm working backwards from the total tax, so the rounding errors need to end up at a line item level. Neither is more valid apart from I need to choose where my rounding errors end up.
Probably best to forget the rounding altogether and focus on the question of how can I work backwards from the total tax to arrive at the individual tax.
Probably best to forget the rounding altogether and focus on the question of how can I work backwards from the total tax to arrive at the individual tax.
If you know the NET ammount and the TAX% AND your now going to ignore rounding errors then calculate forward!!
I think you are inventing a problem that you do not have.
I think you are inventing a problem that you do not have.
I agree with [Neilsr].
Is this something you're being directed to do? I smell a C-level manager who wants to try something s/he read in a book last week...
But to the problem. Tell us which elements we can presume you have. Don't say Net, Tax and Total Tax because we'll keep telling you to work it forward (which is the only good way you'll get the correct answer).
Is this something you're being directed to do? I smell a C-level manager who wants to try something s/he read in a book last week...
But to the problem. Tell us which elements we can presume you have. Don't say Net, Tax and Total Tax because we'll keep telling you to work it forward (which is the only good way you'll get the correct answer).
ASKER
@paulmacd - don't take this the wrong way, but if you can't understand that my requirement is to work the solution backwards, maybe you're not the right person to be answering my question?
Maybe your not the right person for the job your in either then!!
There is no financial sense to what you are asking. From a final figure on an invoice/order you can NOT work back to each individual items net, that you already have!
There is no financial sense to what you are asking. From a final figure on an invoice/order you can NOT work back to each individual items net, that you already have!
Maybe. I am (and others are, I'm sure) trying hard to understand what it is you want to accomplish.
What I'm hearing is that you have three numbers, two of which result in the third. You want to work backwards from the result, without using either number used to calculate it.
If there's a piece of information I'm missing or something I'm not understanding, please illuminate me!
What I'm hearing is that you have three numbers, two of which result in the third. You want to work backwards from the result, without using either number used to calculate it.
If there's a piece of information I'm missing or something I'm not understanding, please illuminate me!
ASKER
Thanks Paul, going to wait for the moderator to get around to this question as it's badly misifred so far.
ASKER
*Misfired even.
Unless the tax rate is ALWAYS exactly the same on ALL items then what you are asking can not be done.
IF the tax rate is the same on ALL items then its simple maths. What proportion of the total price is the current line And assign the same proportion of the total tax.
IF the tax rate is the same on ALL items then its simple maths. What proportion of the total price is the current line And assign the same proportion of the total tax.
Can you illustrate with an example of when the forward rounding errors would give an incorrect result, and what you would expect as the correct result in that case?
What I need is a formula so that given the total tax on the order ($10), and knowing there are two products on the order, and knowing their net price and tax rate, how can I calculate the amount of tax per item.
knowing their net price and tax rate - unless you know the net price of each product, I don't think that there is a way to calculate this
I'll try to actually answer the question.
If you have the price and rate then you can use those to get the proportion
When I say rate, I am assuming it's a decimal number between 0 and 1 (e.g. rate = 5% = .05)
costA = priceA*(1+rateA)
costB = priceB*(1+rateB)
costA/(costA+costB) * TotalTax
This will give the estimated tax from A
If you have the price and rate then you can use those to get the proportion
When I say rate, I am assuming it's a decimal number between 0 and 1 (e.g. rate = 5% = .05)
costA = priceA*(1+rateA)
costB = priceB*(1+rateB)
costA/(costA+costB) * TotalTax
This will give the estimated tax from A
Wait, that's not right. Hang on.
ASKER
Thanks @TommySzalapski - I think you're on the right lines there!
ASKER
@TommySzalapski I've been trying to solve it with ratios..
Taking the total tax, and working out what proportion or ratio of it each product would contribute based on product price and tax rate.
If I could find the ratios I could then use them to work out the (estimated) tax amount per product.
Too much brain fog to piece it together at the moment.
Taking the total tax, and working out what proportion or ratio of it each product would contribute based on product price and tax rate.
If I could find the ratios I could then use them to work out the (estimated) tax amount per product.
Too much brain fog to piece it together at the moment.
I mean, the right way to get the ratio would be rateA*priceA/totalTax but that puts you back to square 1.
The ratios is exactly I posted in
#36980743 But you can only do this if you know the TOTAL net price, The net price of each line AND the tax rate remains constant.
It is then very simple math.
#36980743 But you can only do this if you know the TOTAL net price, The net price of each line AND the tax rate remains constant.
It is then very simple math.
ASKER
...the closest I've got to a solution so far has been to visualize it as a pie chart.
The whole pie represents the total tax amount. Each slice of the pie is the amount of tax for each product. There can be no gaps in the pie, all the slices have to add up to 100% of the pie.
The total tax is not derived from the individual products, it's an amount that's fixed externally. Doesn't matter for the purposes of the maths by who or why...
The size of each slice is proportional to the net price of the product and the tax rate of it.
Just can't find a way of converting this to maths.
Maybe could forward calculate from net price + tax to arrive at the forward *calculated* total tax. That would give the proportions of each 'slice'. Then apply those proportions to the total tax I need to reverse back from?
Again to use the pie visualisation, forward calculating the pie chart it would have no gaps. This could fix the ratios. Then take a bigger (or smaller pie) and then apply teh ratios to it to get the reverse calculated values?
The whole pie represents the total tax amount. Each slice of the pie is the amount of tax for each product. There can be no gaps in the pie, all the slices have to add up to 100% of the pie.
The total tax is not derived from the individual products, it's an amount that's fixed externally. Doesn't matter for the purposes of the maths by who or why...
The size of each slice is proportional to the net price of the product and the tax rate of it.
Just can't find a way of converting this to maths.
Maybe could forward calculate from net price + tax to arrive at the forward *calculated* total tax. That would give the proportions of each 'slice'. Then apply those proportions to the total tax I need to reverse back from?
Again to use the pie visualisation, forward calculating the pie chart it would have no gaps. This could fix the ratios. Then take a bigger (or smaller pie) and then apply teh ratios to it to get the reverse calculated values?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm trying to understand what you are asking. Are you saying that if you have two items, one of $100 and one of $50, that together had a total tax of $10, is there a way to determine what the individual tax rates were for each item?
ASKER
@awking00 thanks, it's a tricky question to explain...
Your summary is correct but it hides the complexity that I'm trying to find a solution for, which is where it gets difficult to explain...
I guess one way of putting it would be that you're given a list of products on an order, you know their tax rates and you know their net prices excluding tax. You could very easily work out the tax per product by adding product net price and tax rate.
But...
The total tax on the order has been calculated by some other means (doesn't matter what for the purposes of this exercise). So in other words the total tax on the order is not derived from the sum of all the product tax rates, it has been calculated some other way.
Assume that the 'other way' of calculating the total is a valid way of doing so.
The problem is to then calculate each products tax value not from its net price and tax rate, but as what proportion of the total tax it is.
So instead of the total tax for the order being the sum of all the taxes for each product, I need to start with a given total tax value, and work backwards to approximate the tax for each item.
To be honest it's the kind of problem that if I could explain it well enough for someone to understand it, I would be able to solve it myself ;)
Your summary is correct but it hides the complexity that I'm trying to find a solution for, which is where it gets difficult to explain...
I guess one way of putting it would be that you're given a list of products on an order, you know their tax rates and you know their net prices excluding tax. You could very easily work out the tax per product by adding product net price and tax rate.
But...
The total tax on the order has been calculated by some other means (doesn't matter what for the purposes of this exercise). So in other words the total tax on the order is not derived from the sum of all the product tax rates, it has been calculated some other way.
Assume that the 'other way' of calculating the total is a valid way of doing so.
The problem is to then calculate each products tax value not from its net price and tax rate, but as what proportion of the total tax it is.
So instead of the total tax for the order being the sum of all the taxes for each product, I need to start with a given total tax value, and work backwards to approximate the tax for each item.
To be honest it's the kind of problem that if I could explain it well enough for someone to understand it, I would be able to solve it myself ;)
ASKER
@TommySzalapski have been running your formula through Excel and I think it's what I'm going to need. Will get back to you once I've had a chance to test it fully.
ASKER
Thanks Tommy, by taking the time to understand and more importantly answer my question you have got me moving forward again. I have got the maths working in Excel and I can turn it into code from there. Very much appreciated sir.
If you have the net price and the rate then the original price is (net/(1+rate)) so if you know A was 105 with 5% tax do (105)/(1+.05) = 100
There's no way to tell whether or not something got rounded. If the numbers don't match up perfectly, then you just have to guess where the missing penny goes.