Managing Sales Tax When Building Accounting Software
I spent the last couple of days learning about the complexities of calculating & recording sales taxes while designing an improved version of our accounting integrations at Float.
This article serves as an introductory overview of calculating and managing sales tax, why it is essential for businesses, and some of the complexities you can face while building software that works with accounting tools.
In particular, I dive deep into the calculations backing one automation feature that requires us to compute pre-tax amounts from credit card statement transaction amounts. I’ll dig into the edge case considerations that we’ve found and our tips for handling them. These minute details on rounding and compounding are fundamental to building trustworthy, reliable software.
Tracking taxes is critical, but it’s often hard to do it properly
Avoiding the practical effect of just having less money to spend, sales tax is a burden for businesses as most need to track and remit money to the government.
Why do you need to keep your receipts?
While there are sadly many taxes in life, one tax that touches both individuals and businesses is sales tax, sometimes called a value-added tax, when purchasing items.
The immediate effect of sales tax is the same for individuals and businesses (we pay an additional percentage of what we buy). The difference that many employees don’t know is that most sales tax is recoverable by companies!
Worded another way, in many situations where you or I would pay an additional ~$200 in sales taxes when we buy a new iPhone, a business making the same purchase would get the money back when filing their taxes. No wonder your finance team wants you to keep your receipts — we would all be sticklers if there were free money on the table!
Keeping track of these refundable taxes is an easy way to improve profits if done correctly. As with income taxes, if businesses underclaim, they are losing out on refund money, but if they overclaim, the Internal Revenue Service or Canadian Revenue Agency can come knocking with penalties and fines.
Different types of sales taxes calculations
For the sake of being thorough, let’s quickly go through how we would compute sales taxes under various scenarios/jurisdictions.
Single tax rate
The simplest jurisdictions have a straight, one-rate tax, such as HST in the Canadian provinces of Prince Edward Island and Ontario, among others. Computing such a tax is as simple as taking the pre-tax amount and multiplying it by the tax rate. For example, a $100 purchase taxed at a 13% rate results in a final bill of $113.
Multiple tax rates
Other jurisdictions, such as the case in Québec, calculate tax as two discrete rates. In this situation, the calculation is only somewhat more complex.
Consider using 5% GST and 9.975% QST and taking the same purchase amount as before. Calculate the two sales tax components separately, to get your final bill as $100 + $100 * 5% ($5) + $100 * 9.975% ($9.98) = $114.98.
Effective tax rate
Today, Québec does not compound sales tax, although Québec and Prince Edward Island have done this historically. There are many sources online today that are outdated and say otherwise, but don’t be fooled — no Canadian jurisdiction is charging compound sales tax as of 2021!
However, for a mathematical discussion, let’s look at how this would work if Québec continued to charge QST on top of the purchase price plus GST. Many accounting providers label this type of tax as “compounded” or “tax on tax.”
Using the same numbers as above, the final bill calculation under compounded taxation is ($100 * 1.05) * 1.09975 = $115.47. The secondary tax is applied to the summed base amount and first tax amount.
The overall compounded tax rate, accounting for all sub-rates, is sometimes expressed as a percentage called the effective tax rate. In the above example, the effective rate is 15.47%.
The effective rate better reflects the actual amount of tax one pays. We’ll see a case later where it’s not quite exact — but it’s never off by more than a few cents.
Making taxes simple — it’s harder than it seems!
You know the business reason to track sales taxes (money returned from the government), and you’re thinking that even compounding taxes aren’t that mathematically difficult, so you’re now asking, “cool, why would I be interested?”
Well, as with most financial calculations, the devil is in the details. No one wants to think about sales tax regularly, so how can we make taxes for business purchases as simple as possible?
At Float, we know only the post-tax final transaction amount that someone spends on their credit card. Recording the tax on these purchases could be done in a few ways:
- Finance teams could read their receipts and input tax amounts manually one by one for each purchase. For even medium-sized businesses, this can become quite time-consuming!
- If we knew (or could infer) the tax code to apply, we can work backwards to calculate tax from the post-tax final amount.
- If a receipt is submitted with a purchase, then we can use OCR to read the subtotal and tax amounts.
Well, Option #1 is clearly the worst-case scenario, but the unfortunate status quo for so many finance professionals. No one is delighted by doing manual data entry! Option #3 is something we’re cooking up, but this article is about tax, not computer vision, and we need a fallback if receipts are missing or unable to be read.
During this past week, I was looking into Option #2: working backwards to calculate the pre-tax subtotal from the post-tax recorded amount and compute the taxes automatically.
My ideal outcome for a minimum viable feature: as a finance user, tag a transaction with a tax code (for example, “Québec — GST/QST”), and our system would reliably calculate and record the federal and provincial taxes. With our integrations to Xero and Quickbooks, tax calculations would be input directly into your accounting system.
Over a couple of days of working through the edge cases, I’ve experienced several “aha!” moments and pen-and-paper math problems that made me appreciate the computational complexity of these seemingly simple sales tax calculations.
Starting at the basics: what’s the tax for $1?
The most interesting case to me was, “Given a final post-tax amount of $1, under a 13% sales tax regime, what is the pre-tax amount?”. At first, most people, including myself, think $1 / 1.13 = 0.8849557522. But I’ve never seen that precision at the store cash register before, so what is the actual amount?
Let’s try to reverse the operation and see what we get:
$0.88 * 1.13 = 0.9944 (rounds down to $0.99)
$0.89 * 1.13 = 1.0057 (rounds up to $1.01)
Hmm, that doesn’t seem right! I wonder what the trusty CRA official calculator says:
It must be $0.88 like we initially thought! Let’s confirm that on the same calculator:
Oops, I guess the CRA finds sales tax difficult too! I dug through the CRA’s math (everything is public), and they take the effective rate and round to the nearest cent. For the non-engineering readers, the e2 stuff is a quirk of the JavaScript language they use for rounding: convert to cents, round using integers, and convert back to dollars.
There’s no exact answer, so maybe this estimation is fine. If this occurred as an edge case in our software, we could flag this to the user as something they need to check manually.
When multi-part taxes can break the effective tax rate
In the above example, we saw a case where no pre-tax amount would result in our target of $1 after taxes, so we can either raise an error or return a “close enough” answer. However, there’s also a second class of edge cases, where the effective rate gives an incorrect pre-tax amount when there is an unambiguously correct one.
Consider a case with a 5% state tax rate and a 9.9975% local sales tax rate, with the latter compounded on the first and the taxes remitted independently. Pre-2013, this was the case for Québec sales tax.
For a $4.00 post-tax amount, calculate the pre-tax amount using the effective tax rate:
$4.00 / ((1 + 0.05) * (1 + 0.09975)) = 3.46
Using this amount and calculating tax (rounding the intermediate taxes to the nearest cent), we get the following:
($3.46 + 5%) + 9.975% = ($3.46 + $0.17) + 9.975% = 3.99
It seems we are off again! However, this time, unlike the unsolvable case of $1.00 that we saw before, there is a correct pre-tax amount to solve the problem: $3.47.
($3.47 + 5%) + 9.975% = ($3.47 + $0.17) + 9.975% = 4.00
The effective tax rate seems to provide only an estimate of the pre-tax amount but can be off in some cases due to intermediate value rounding. When building our algorithm, we need to think about all the edge cases and design an efficient way of computing accurate pre-tax amounts.
Learnings about calculating pre-tax amounts
- Using the effective tax rate is an estimate that is usually right but could be off by a few pennies.
- When using the effective tax rate results in an incorrect value, there may be a solution (my last example), or it could be a case where no solution exists (my first $1 example). You must decide if and how you should flag this to the end-users of your software.
Disclaimer: Looking at the receipt tells you the actual amount of tax charged on a purchase, but I’m trying to minimize manual work and create programmatic rules and heuristics. Finance teams should always be notified of which amounts are estimated and should have the option to override with a “correct” manual amount.
Calculating pre-tax amounts reliably
A good trade-off between efficiency and accuracy is using effective rate as an initial guess of the pre-tax amount and then verifying it by recomputing the tax from this guess. If the amounts match, then you’re in the clear, but if they don’t, you can iteratively adjust to see if a solution exists.
See my below pseudocode for a naïve version of this algorithm. Assume the exampled compute_pretax_amount is a method on a TaxCode object that also has a property effective_rate and another method compute_tax (that correctly accounts for multi-step taxes, compounding, and rounding).
def compute_pretax_amount(self, total_amt):
estimated_pretax_amt = total_amt / (1 + self.effective_rate)
subtotal = estimated_pretax_amt
adj_direction = None
while True:
tax = self.compute_tax(subtotal)
reconstructed_total_amt = subtotal + tax
if reconstructed_total_amt == total_amt:
return subtotal
# If we over-estimated, then we need to adjust the
# subtotal down. To prevent an endless loop, if we've
# already adjusted up, then don't adjust down
elif (
reconstructed_total_amt > total_amt and
adj_direction != "up"
):
subtotal -= 1
adj_direction = "down"
# If we under-estimated, then we need to adjust the
# subtotal up.
elif (
reconstructed_total_amt < total_amt and
adj_direction != "down"
):
subtotal += 1
adj_direction = "up"
# We've changed the direction of adjustment
# with no exact match, so just return the estimated amount
# since we're probably in a loop
else:
return estimated_pretax_amtSome considerations when designing your own software:
- Should we always return the best guess value, or do we want to be extremely strict and throw an error if we can’t find one? You may answer this question differently depending on (a) how you flag an estimated vs actual value to end-users and (b) whether your system is a source of truth to your customers.
- How much do we value computational accuracy versus efficiency? Perhaps there is a middle ground where the iteration is limited to only a handful of attempts. In all my empirical tests, the effective rate has never been off by more than a cent, but perhaps there is an edge case with multiple compounded taxes where it could become relevant.
What’s next?
After my initial investigation of the mathematical edge cases and complexities, it’s time to productionize this in our next engineering sprint!
I’ll share updates once things are built about both:
- The customer-facing features that we built into Float to simplify tax reporting.
- A more detailed technical post about integrating with Quickbooks and Xero. These APIs are a real pain to work with, so I’ll summarize everything I wish I knew before I started.
In the meantime, I’d love to hear from any bookkeepers or finance managers about the complexities of handling tax on your business purchases. What tools could you use to help handle tax reporting?
Disagree with something I’ve said? I’d love to learn more about how you handle your business finances and give you a shout-out in my next article!