SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Aug 2008
    Location
    chicago, IL
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    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.

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Member
    Join Date
    Aug 2008
    Location
    chicago, IL
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Let's see what folks over the eCommerce forum think.
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  5. #5
    SitePoint Member
    Join Date
    Apr 2011
    Location
    Kansas City, Missouri
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    We use a fixed dollar return as well, but would be curious to see if anyone has any good suggestions on this.
    Last edited by AnthonySterling; Apr 6, 2011 at 11:55. Reason: Removed external links, please wait for a signature. :)

  6. #6
    SitePoint Member
    Join Date
    Aug 2008
    Location
    chicago, IL
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    $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.

  7. #7
    SitePoint Member
    Join Date
    Aug 2008
    Location
    chicago, IL
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SitePoint Member
    Join Date
    Aug 2008
    Location
    chicago, IL
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok here is the revised code. This should auto correct any rounding errors
    that occur during partial refunds that eventually become full refunds.

    PHP Code:

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

  9. #9
    SitePoint Member
    Join Date
    Aug 2008
    Location
    chicago, IL
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  10. #10
    SitePoint Member
    Join Date
    Aug 2008
    Location
    chicago, IL
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok. I came up with a possible solution to handling fixed dollarrefunds and tax:

    -Find the return percentage:

    PHP Code:
    // Input:
    $refund_amt;

    $rp $refund_amt $total_after_tax
    -Then you return that percentage of sales and tax:

    PHP Code:
    $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:

    PHP Code:
    $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.):

    PHP Code:
    $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:
    PHP Code:

    // 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.):
    PHP Code:
    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
    PHP Code:
    $x = ((($y 100) + ($z 100)) / 100); 
    Last edited by Senior_Archivist; Apr 13, 2011 at 20:55. Reason: fixed some formatting


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •