So, a client said to you that they want the price for a service or product to be $79.99, including taxes regardless of the tax rates in the different parts of the country where the product is sold. The math is simple, just divided the total (in this case $79.99) by one plus the tax rate to determine the price before taxes.

If you have one jurisdiction where the rate is 7%, the net amount would be $74.76 (rounded to the penny) making the tax $5.23. Now if the rate is 9%, the net amount would be $73.39 (rounded to the penny) making the tax $6.60. There’s a problem with the second calculation here, however, but more on that shortly; first, here’s what it would look like in T-SQL.

Or you could create a simple function to do most of the work for you, such as:

In this case, the T-SQL would look like this:

The problem, as mentioned, is that you cannot get to $79.99 with a 9% tax rate. The calculation works here because the tax amount is derived by subtracting the calculated net amount from the total amount passed into the function. If you were to calculate that total from the net amount provided, and round to the penny, you would get $60.00 as the tax would be $6.61 (actually $6.6051 before rounding) on $73.39.

The temptation might be to reduce the price by a penny to $73.38, but the tax then would be $6.60 ($6.6042 before rounding) for a total of $79.98. You are now off by a penny from the stated total price. Rounding prevents you from getting to the desired amount if you calculate the taxes, leaving this subtraction solution or storing and displaying the net price and tax to four decimal places instead of two. Neither of these solutions is ideal, given that you will have to reconcile the difference one way or another and display the results for the purchaser in the form of an invoice and/or receipt.

BUT!

The point of this article was to show how simple it can be to create a T-SQL function that will calculate the tax based on the final price and the tax rate. Whether or not you decide to use taxes included pricing will very much depend on your marketing requirements and strategies. If you do, consider also that charging a half-cent more or less of tax has implications for what is ultimately owed to the tax collector, so discussions should always include your accountant.

What this is not, however, is an example of something that MAI would use in a production environment. While we do have several tax related functions in our library, including a couple for taxes included, none are as bare bones as the example above. The first consideration is that passing in the tax rate implies that it is provided outside of the database, which means that implementation could lead to hardcoding the rates in the desktop or web-based application; something to always be avoided.

Tax rates in MAI applications have traditional been stored in the State/Province tables for both provincial and federal rates as that is how they are assigned as each can vary accordingly. Instead of passing in the rate, the state or province is passed in, with a lookup then to the appropriate field for the corresponding record based on that value provided by the user or the user’s stored profile. If a tax rate changes, there is only one place it needs to be updated.

Other Considerations for Taxes

Note that in some municipal jurisdictions in the USA also charge taxes, so a separate city/state tax table needs to be implemented on top of a state/federal tax table. To complicate taxes even further, a way of tracking what services or products are tax exempt must be part of the discussion with both your programmer and accountant.

Please use our contact page to submit questions.