SalesTax ( Taxable ; Exempt ; Rate ; Method ; Start )

Calculate Sales Tax

Average rating: 4.6 (13 votes) Log in to vote

Jeep Watson
Uptown Press
www.uptownpress.com

Sample input:

Ohio - SalesTax ( 8.40 ; 0 ; .0675 ; "R" ; .08 )
Maryland - SalesTax ( 10.75 ; 0 ; .06 ; "C" ; .20 )
Texas - SalesTax ( 11.59 ; 0 ; .0825 ; "R" ; .07 )
Texas - SalesTax ( 11.59 ; 1 ; .0825 ; "R" ; .07 )

Sample output:

.57
.65
.96
.00

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

// SalesTax ( Taxable ; Exempt ; Rate ; Method ; Start )

// Jeep Watson 08/10/16

// Parameter: Taxable = the taxable amount (obviously) 
// Parameter: Exempt = 1 if your customer is tax exempt, else 0
// Parameter: Rate = Your municipality's tax rate (entered as a decimal)
// Parameter: Method = "R" or "C" (Round or Ceiling)
// Parameter: Start = The smallest sale amount that is taxable (decimal)

Case ( 
Method = "R" ; 

Case (
Exempt or Taxable < Start ; 0 ; Round ( ( Taxable * Rate ) ; 2 ) ) ; 

Method = "C" ; 

Case (
Exempt or Taxable < Start ; 0 ; Taxable = Start ; .01 ; Ceiling ( 100 * Taxable * Rate ) / 100 ) ; 

"" )

I've written software for several retail clients in my home state of Maryland. In 2008
Maryland raised its Retail Sales Tax from 5% to 6%. The 5% rate was relatively easy
to do with a simple calculation. Not so with the 6% rate. Multiplying a subtotal by 6%
(.06) results in fractional cents. I tried using the Round function in Filemaker,
thinking that would be an equitable solution but the results did not line up with the
state's published tax chart. Rather than spending more time on figuring out the math
behind Maryland's chart, I decided to make the calculation a simple lookup table. It
took less than 20 lines of code, was easy to write, and was plenty quick enough.

Recently I was tuning up some older custom functions and thought it might be a
good idea to create a custom function for calculating Sales Tax. Nice and portable
and easy to implement in new applications. I posted the function on Brian Dunning's
site for others to use. In a couple of days I got a response from Michael Horak
(Comment, as he is known on various Filemaker forums) showing a short calculation
using the Ceiling function - a variation of the Round function (it always rounds up).
The results synced perfectly with Maryland's chart.

After rewriting the function, I thought "why not make it flexible so it could be used in
other states." I downloaded charts from a dozen other states, added a few extra
parameters, and started testing. The results were accurate for every state I tested.
You just need test it to see which computational method is used by your state. That's
why I created this testing demo. I can't guarantee every state will use one of these
computational methods but if it works for you, enjoy.

A demo file and state charts are available at this link:

https://www.dropbox.com/sh/pj3as15ttiphvwp/AAAfkZfWPaamJXNRDGksDRkOa?dl=0

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

SalesTax ( Taxable ; Exempt ; Rate ; Method ; Start )

Comments

Support this website.

Support this website.