E-commerce tax calculation issues

If you are building an ecommerce system that can handle orders with items that have different tax rates and rounding is applied to the total tax of the order (vs on the tax of each item).

How do you calculate the amount of sales tax to return on partial returns assuming the same rounding rules are used throughout the application to the exact cent?

I am trying to implement two types of returns that the site administrator can use. The first one is a line item return. where they just check off the items on a list that they want to issue a refund for and the second is a fixed dollar return where they can choose to return an arbitrary amount that is not tied to a specific item.

I have looked all over the place and have found very little information on coding for tax calculation. So before I go and start ripping into the code of all of the various shopping carts to see if and how they do it I thought that I would ask the experts here. :slight_smile:

Also just to clarify, I am not looking for jurisdiction specific tax rates and rules. I am looking for answers regarding the overall logic used to handle them when you have them.

It’s certainly a tricky one. I do however, have the pleasure of working in the the middle of an accounts department. Rounding is nearly always discussed at ‘month end’ and from what I can gather, it’s always wrote off - and our auditors are good with this.

I guess the key is to be consistent, and ‘right off’ the discrepancies; whether you over or under refund by 1c|p.

Hopefully others can offer some insight.

Thank you for the quick reply AnthonySterling. So far this has been the most
difficult issue that I have faced in building a custom e-commerce system from scratch.

Let’s see what folks over the eCommerce forum think. :slight_smile:

We use a fixed dollar return as well, but would be curious to see if anyone has any good suggestions on this.

I thought I would share with you guys a basic overview of the logic I have
come up with so far for calculating and keeping track of the amount of tax for partial line item refunds. I have changed most of the variable names to simple letters just to emphasize the overall structure of the code and make it a little easier to read.

Essentially I round as usual for each partial refund till the last which
gets checked against the total to make sure it does not come out short
or go over due to rounding.

There is more to the actual working code but this is kind of the gist of it.


$a = total of refunded items
$b = total tax of refunded items
$c = total refunded tip
$d = any previously refunded items
$e = any previously refunded tax
$f = any previously refunded tip
$g = total_before_tax on order
$h = total_tax on order
$i = total tip for order

// Note:  In this scenario tip has a sales tax of 0 %.
// Also the tax for the returned taxable items
// uses the stored tax rate for each item when they were ordered.

// Check if it is a partial or full refund:

if ($a + $c + $d + $f) == ($g + $i) {

  // The order has a refund type of Full.
  $order_type = 'Full';

} else {

  // The order has a refund type of Partial.
  $order_type = 'Partial';

}

// Check if it is a full refund for all the taxable items:

if (($a + $d) == $g) {

  // Check and make sure that the the total tax refunded 
  // matches the total tax for the order.

  if (($b + $e) != $h) {

    // If the total tax of refunded items is over or under  
    // due to rounding recalculate it.
    $total_tax_to_refund = ($h - $e); 

  }

}

// You will end up with these 4 variables at the end
// Which you can use to keep track of how much
// of each was refunded for the order and update the database
// accordingly.
$refund_type
$total_of_refunded_items
$total_tax_of_refunded_items
$total_tip_refunded


I am still stumped on how to do fixed dollar partial refunds and keep track
of tax refunded.

I was absolutely exhausted when I typed this up so I hope it makes sense.

I looked over the code some more and it appears that the above logic breaks if any of the other line items other than tip have a 0% tax. sigh… back to the drawing board.

I think I have come up with a fix to the above code to make it work with other items with a 0% tax. I will post it when I am done.

Ok here is the revised code. This should auto correct any rounding errors
that occur during partial refunds that eventually become full refunds.



// Note:  In this scenario tip has a sales tax of 0%. 
// Also the tax for the returned taxable items 
// uses the stored tax rate for each item when they were ordered.

$total_taxable_sales_to_refund;
$total_nontaxable_sales_to_refund;
$total_tax_to_refund;
$total_tip_to_refund;
$total_taxable_sales_from_previous_refunds;
$total_previous_refunded_sales;
$total_previous_refunded_tax;
$total_previous_refunded_tip;

$total_tip;
$total_before_tax;
$total_taxable_sales;
$total_tax;

$a = ($total_taxable_sales_to_refund + $total_nontaxable_sales_to_refund + $total_tip_to_refund + $total_previous_refunded_sales + $total_previous_refunded_tip);

$b = ($total_tip + $total_before_tax);

// Check if it is a partial or full refund:
if ($a == $b) {
    // The order has a refund type of Full.
   $order_type = 'Full';  
} else {
    // The order has a refund type of Partial.
   $order_type = 'Partial';  
}

$c = ($total_taxable_sales_to_refund + $total_taxable_sales_from_previous_refunds);
$d = ($total_tax_to_refund + $total_previous_refunded_tax);

// Check if it is a full refund for all the taxable items:
if ($c == $total_taxable_sales) {

  if ($d != $total_tax) {
    // Set new total_tax_to_refund:
    $total_tax_to_refund = ($total_tax - $total_previous_refunded_tax);
  }

}

// You will end up with these 4 variables at the end 
// Which you can use to keep track of how much 
// of each was refunded for the order and update the database 
// accordingly .  The database tables would need to have fields for
// if the order item had been refunded and for the historical tax rate
// of the item.  You would also need fields to keep track of
// total_refunded_sales, total_refunded_tax, and total_refunded_tip for each
// order.
$refund_type;
$total_sales_to_refund;
$total_tax_to_refund;
$total_tip_to_refund;

I am working on some possible solutions for fixed dollar refunds I will post my code when I am done. As always any feedback would be greatly appreciated.

ok. I came up with a possible solution to handling fixed dollarrefunds and tax:

-Find the return percentage:


// Input:
$refund_amt;

$rp = $refund_amt / $total_after_tax;

-Then you return that percentage of sales and tax:


$sales_returned = $rp * $total_sales;
$tax_returned = $rp * $total_tax;

-If the cumulative refunded amt for the order is less than
the total after tax then:


$sales_returned = $previous_sales_returned + $sales_returned;
$tax_returned = $previous_tax_returned + $tax_returned;

-When the cumulative refunded amt for the order is
equal to the total after tax then (This is the important part.
This will prevent the multiple
instances of rounding from throwing off your final totals.):


$sales_returned = $total_sales - $previous_cumulative_sales_returned;
$tax_returned = $total_tax - $previous_cumulative_tax_returned;

$new_total_refunded_sales = $sales_returned + previous_refunded_sales;

$new_total_refunded_tax = $tax_returned + previous_refunded_tax;

$new_refunded_amt = $refunded_amt + $previous_refunded_amt;

I also have cleaned up and simplified the line item refund solution:
-First determine if it is a full or partial refund:



// Input:
$sales_returned;
$tax_returned;
$refunded_amt;

if ($total_cumulative_refunded_sales == $total_before_tax) {
  // Full refund
} else {
  // Partial refund
}

-Then check if it is a full refund for all taxable sales (This again
is the important part.):


if ($total_cumulative_refunded_taxable_sales == $total_taxable_sales) {

  if ($total_cumulative_refunded_tax != $total_tax) {

    $tax_returned = $total_tax - $total_cumulative_tax_refunded;

  } 

}

$new_total_refunded_sales = $sales_returned + previous_refunded_sales;

$new_total_refunded_tax = $tax_returned + previous_refunded_tax;

$new_refunded_amt = $refunded_amt + $previous_refunded_amt;

Also make sure that you are doing all of your math with whole numbers
and not floats due to the floating point precision problem php has. i.e


$x = ((($y * 100) + ($z * 100)) / 100);