Xero - beautiful accounting software

Xero Business Community

Xero Business Community

Hi everyone, Come on over to the new discussions in Xero Central. It’s a more intuitive way to connect and chat all things business with one another. Ask questions, dish out answers, and get involved.

You can still view topics in the Xero Business Community to see all the useful knowledge that’s been shared there; and you can restart conversations you feel have been of huge value to you on Xero Central.

If you have brilliant ideas for innovations, you can keep on adding feature requests in the Xero Business Community, and voting on existing requests. There’s more in store, as we come up with the best way to take on your thoughts and share developments
Community > Accounting >

Sales Tax Setup for US & Canada, issues with "sales tax payable" / Use Tax

Started by Avery Dorland -   in Accounting

This is a challenge I have been bumping into lately and am looking for some other perspectives on how its being resolved. The Xero Sales Tax Payable account works well for "Commonwealth" style taxes like VAT and HST in Canada where the amounts due to the government are usually the net of amounts charged on sales less the amounts paid on purchases. This creates a nice payables balance for tracking sales tax owed.

The problem for me is with the "quick tax" method for HST in Canada and the common sales tax format in the 3 or 4 US states I've worked in. That is, sales tax is to be collected on sales, but sales tax paid on items to be consumed in the business is considered part of the expense. Sales tax is exempt on goods for resale and never comes into the business.

That is, a business gets no credit against amounts to be remitted unless they were paid in error. The way i've been working with this so far is to set "Tax on purchases" to zero percent and just add a note to the label as to what the state sales tax is, so it reads in Xero something like "Tax on purchases 8.25%" (0%). This prevents credits from adding up in the sales tax payable account but is very confusing for my customers. How do others in the US market resolve this?
Hi Avery

I've had much the same problem with PST (provincial sales tax) in the Canadian province I work in. This tax works much the same as what you talk about with state taxes, but is in addition to the common GST. I've no experience with the GST Quick method as I'v always used the traditional GST filing appoach which works great in Xero.

Xero's approach to taxes is simple and easy for a VAT only country, but in this case I've had to enter one line taxable with GST only and then another line to enter the PST. So say I purchase some flower pots for my office for $10. The total price is $11.20 which includes GST (5% of $10 = $0.50) and PST (7% of $10 = $0.70). So I have one line with flower pots $10 taxable with only GST at 5%. That way the correct GST is captured in the sales tax account. Then I have to do something with the PST and so I enter a second line with the quantity set to 0.07 because the PST is 7% and the subtotal as the unit price. The account is the same expense account as before only with a 0% tax rate. Like this I now have the PST going to the expense. In the case of different expense accounts on multiple lines of a bill it would take a seperate PST line for each different account. This seems like a lot of extra work coming from software where this was completely automated and I didn't have to think about. Maybe I could be doing it differently and I just don't see it.

To all the Xero experts out there, any ideas on how you're doing this, especially, in my case, if you have first hand experience in a GST+PST Canadian province.

 

Greg Penner  

OK - I'm not in Canada (married to one and used to live there, but it's been a while!) and haven't had to do this in Xero, but here is how I would handle it (on the surface). I would create a tax rate called "GST with 7% PST" using 4.6729% (this will create 50c tax on an $11.20 tax inclusive transaction - being 0.50/10.70). You may need several tax rates to deal with the various PST & GST combinations.

It would be nice for Xero to add some flexibility for various tax systems into the Global edition. While US now have their own edition it doesn't sound like it handles Sales Tax on Purchases (which can't be claimed back) very elegantly. Canada doesn't yet have it's own edition so has only the bare basic commonwealth sales tax functionality (which is ironic as Canada is a Commonwealth system and yet has PST using the other method!).
 

Peter McCarroll  

He everyone. There is a feature request at https://community.xero.com/business/discussion/1158318 to address this problem. I have sent it directly to Xero, but they do take the votes on this forum seriously, so if you would like to see a better handling of US or Canadian PST style sales taxes then I recommend that you vote for the feature request. Thanks
 

Peter McCarroll  

Good find Peter, thanks for linking the two threads
 

Avery Dorland  

Merged: Sales & Use Tax (non-VAT) Accounting options

Hoping someone else has crossed this bridge and can offer a good solution. In California (and I suspect most states in the US), there is no VAT style tax which Xero handles beautifully. We do have a Sales & Use tax.

The Sales part is straightforward - you sell a taxable item and you charge sales tax, collect, and remit to the government. Xero works fine with a Tax on Sales, either inclusive or exclusive tax amounts.

The Use tax is more complicated. If you buy something that tax has not been charged on, then that purchase needs to be reported and you remit that tax to the government. This happens in cases where the vendor was from out of state, you purchased goods for resale (tax exempt) but then used them in your business instead of selling them, etc. So in this case, my invoice from the vendor will be for say $500 and I will owe & pay the vendor $500, but then also owe tax payable to the government of $500 * 8.75% = $43.75. Accounting entry:

Dr. Expense $543.75
Cr. AP - Vendor $500
Cr. Sales Tax Payable $43.75

Using any of the tax codes means the amount owed to the Vendor changes (tax exclusive) or the expense isn't right (tax inclusive). I tried to use Tax Exclusive and then add a negative line item to Sales Tax Payable but that ended up producing offsetting entries in the payable account (one for my line item, one for Xero's tax calc).

Has anyone else dealt with this? Looking for a better solution than Manual JE's after the fact. Thanks!
 

Trishan Arul  

Hi I work in BC Canada had same issue for PST i was tracking gst and PST both and when it did not work at end of the year added MJ equal to total PST credit and zero out PST

Not a nice solution but worked time being
 

Maninder Grewal  

Hi all,

For Canadian GST + PST issues, there are two possible solutions depending on your preferred workflow.

Solution 1: (Manually calculate the tax, but use the tax rate set to 0 to have the data flow to the sales tax report)
1. Create a tax rate named “Tax on Purchase PST 7%” and a tax rate “Tax on Purchases GST 5%” both with a tax rate of 0%
2. Manually calculate the PST Gross in the the Item purchased.
3. Manually calculate the GST on a separate invoice line.
(Optional) Use docx. invoice template to hide the sales tax subtotals (which are zero)

Solution 2: (Utilize the sales tax calculation function but you need to Journal Entry to move it to the expense accounts)
1. Create one tax rate “Tax on Sales GST + PST” with two components. One for GST and one for PST.
2. Create one tax rate “Tax on Purchases GST + PST” with two components. One for GST and one for PST. Put the word “expense” in the name the PST Component, so you can identify it in reporting.
3. Apply the tax rates to purchase and sales as appropriate.
4. Periodically or at month end, post a Journal Entry moving the PST sales tax expense to the proper expense accounts (Debit Expense, Credit Sales Tax).
 

Peter Wen Xero.com (Xero Staff)  

Peter,

Do you have an update on this issue? Sadly, I wouldn't be subscribing until I know taxes are handled properly (and automatically) without needing manual JE intervention.
 

Luis Giraldo  

I'd be keen to hear from anyone in the US and Canada about what you have had to do to work around the lack of "add to expense" functionality for sales tax. You can reply to this thread or email me at peter@business-express.co.nz. If anyone has a guide on how to set this up for the various Canadian taxes I'd also be keen to see it.
 

Peter McCarroll  

Help....Market-US..... Industry-Construction. Typically in Florida Construction Materials are taxed upon purchase and paid on the vendor invoice and no tax is directly charged to the consumer. So in the set up the sale is tax exempt , but the component on the purchase side are subject to sales tax. This tax becomes part of the COGS and built in to the sale, but not broken out in the customer invoices. Sounds crazy, but that's the code. Problem is Xero is creating this tax liability which has been paid already.
 

Douglas Corey  

Hi Douglas. You will need to stop using the tax code on purchase, as Xero (still) does not properly account for non-refundable sales tax. The only way to correctly deal with this is to gross up the purchase by the Sales Tax amount to ensure that it is properly recorded as part of the COGS (if you have multiple lines with different treatment you could put 1.07 in the quantity field for example using a 7% tax). For items already coded, you will need to edit them (may need to remove payments first) to take out the tax, or do a Manual Journal to bulk adjust (this really needs an expert).

If you need more help don't hesitate to contact me.

Peter.
I'm an NZ Chartered Accountant, Xero Certified Advisor, add-on Developer and Xero's 2012 Most Valuable Professional. I support small businesses in NZ with professional accounting services and offer paid Xero support and implementation advice to clients all over the globe. Feel free to email me at peter at business-express.co.nz if you need a Xero MVP on your team! Visit my blog for various Xero articles.
 

Peter McCarroll  

Hello, More then a half of the year from last post and Xero.com still does not understand that tax on purchases in USA - USE TAX is not deducting sales tax on sales! It is additional liability not connected with liability to vendor of products used in business. Can someone from Xero at least confirm they know the problem? Is any solution other then manual journals possible in the future? Do you work on it? Fact that many entrepreneurs are not aware of use tax liability is not argument for accounting software to not have basic tax features.
 

Michal Sydor  

Hi Michal. I'm pretty sure that the folks at Xero understand the difference between a Use Tax and a Sales Tax. Xero does not have a Use Tax module, but it is easy enough to gather the data that you need. Please see my article Use Taxes in Xero for a suggestion.

Peter.
Xero Certified Advisor, Canadian CPA & New Zealand CA and Xero's award winning Most Valuable Professional. I give implementation advice to clients all over the globe. Get a Xero MVP on your team! Visit my blog for various Xero articles.

You can book a live remote Xero support session with me for a very reasonable price.
 

Peter McCarroll  

Hi Peter, Than you for your advice.
 

Michal Sydor  

Ya really stuck on this one as well. In California.

Thanks for the help Peter, I am often dealing with items with multiple units so the multiplying in the unit doesn't really work for us.

I feel like this is a major issue and as we are now needing a very accurate balance and income reports, this is becoming a deal breaker for us.

We have many cogs accounts and I really want to be able to track the true expense on those accounts rather than try to create manual entries that roughly approximate the tax in each account.

Really think the Xero team needs to at least address the concern.
 

Aram Zadikian  

We would've switched to Xero 3-4 years ago if this issue had been dealt with. Even Quickbooks online knows how to deal with PST expense in BC. We're using them for several other businesses as a result.

This really does need action on Xero's part.
 

Luis Giraldo  

Aram - not entirely certain that you have posted in the correct thread here. Sounds like you need to consult with a Xero Expert to better understand your situation. I can't tell from your post what multiple units has to do with Sales tax or COGS in California. Feel free to book a support session with me if you would like to discuss further.

Peter.
Xero Certified Advisor, Canadian CPA & New Zealand CA and Xero's award winning Most Valuable Professional. I give implementation advice to clients all over the globe. Get a Xero MVP on your team! Visit my blog for various Xero articles.

You can book a live remote Xero support session with me or self-paced Xero training for a very reasonable price.
 

Peter McCarroll  

Sorry I realize my post was confusing. What I was referring to was the trick of getting the tax included in the line item using the unit as a multiplier: Example: 7% tax, use: 1.07 as the unit count.

Unless I've misunderstood?

But for multiple units this won't work. Example:
1) 1.07×10 = 10.70 = Is right
2) 5.07x10 = 50.7 = Should actually be 53.5

So you would need to first multiply 1.07 time the number of units to get the correct multiplier. Example: 1.07 * 5 = 5.35.

This also gets tricky if the base unit count is something like 5.5 to begin with, which frequently happens.

In the end, I feel that this is important enough to warrant a native built in solution.

Luis - That's too bad. I will definitely have another look at quickbooks.
 

Aram Zadikian  

HI Aram. I understand now. Yes, you've got it. You need to multiply the quantity field (or the unit price) by 1.07 to gross up the rate to absorb the Sales Tax. There is a feature request on this point which you really need to vote on (EVERYONE PLEASE VOTE).

I bang this drum everytime I talk to people at Xero (they are sick of me bringing it up, and I have connections deep inside Xero - I have raised it with Rod Drury himself). It's really good for some of you US people to contact Xero about this directly as it was initially seen as a Canadian problem only (I realised from day 1 that this would be a US problem as well). They are actively looking at this (more so now than previously), but it's very difficult to get tangible dates out of Xero. Also, please know that this is not a "simple" problem to solve - (even tough it feels like it).
 

Peter McCarroll  

Aram,
I use what I did in Quickbooks. The total amount of the item gets multiplied by use tax, increasing the total cost. Then I add a line below, going into the sales tax payable account, with a NEGATIVE amount of the tax. Example:
Transaction: Item: $10.00
Item with tax: $10.70 Account: Expense Item
Use Tax: -$0.70 Account: Sales Tax Payable
Total: $10.00
Everything works out and the Sales Tax Payable account is debited this amount.
 

Mark Boeschen  

@Mark, thats a pretty good work around, thanks for posting.
 

Avery Dorland  

The problem with Mark's solution is that the negative line amount won't show up on any Sales Tax report (as there is not Sales Tax Code). So if you are happy to file your reports based on ledger balance alone then this will be just fine.
 

Peter McCarroll  

I also created a new tax item called "Use Tax Only" and only assign it to the Use Tax line item I created (the 7% tax). I then add the total of the Use Tax Only line item to the rest of the Sales Tax due.
 

Mark Boeschen  

The "Use Tax Only" has a sales tax amount of 0%.
 

Mark Boeschen  

We are in Ontario, Canada; we pay 13% HST on some goods and services which we are able claim back 100%. If I was to put against our GL code for Office supplies ( as an example) a tax code " Tax included (13%) " would that show in the Tax summary as 13/113 of the value of the suppliers invoice?
 

Hugh Drogemuller  

Hi Hugh,

You should set up a Tax Rate for HST Ontario on Purchases at 13% (and a separate one for HST ON on Sales). You may also need other tax rates for other provinces as the rates vary depending on who you sell to and where you are when you purchase.

You do not set the rate at 13/113 - you set it at 13. Xero will work out whether to add 13% or deduct 13/113 based on the invoice/bill setting of "Tax Exclusive" or "Tax Inclusive". Spend/Receive money transactions created in the reconcile area area always Tax Inclusive.

Peter.
Xero Certified Advisor, CPA & Chartered Accountant and Canada's ONLY Xero award winning accountant (Most Valuable Professional). I also give implementation advice to clients all over the globe. Get a Xero MVP on your team! Visit my blog for various Xero articles.

You can book a live remote Xero support session with me or self-paced Xero training for a very reasonable price.
 

Peter McCarroll  

Now I'm having a better idea of why the Xero Certified accountants cost so much. I'm in Quebec and sell online to all provinces. I'm so proud to have put my GST/QST paid and now simply looking for how to add a monthly summary of GST/HST/QST collected into XERO. I had no idea it is so complicated. What a mess...
 

Cara Rynerson  

Cara,

If you are not registered for PST in any provice (other than Quebec), then everything should be nice and easy. This thread only applies to Purchases (not sales) and only to Provinces with both GST and PST (not QST because QST works exactly the same as GST so is handled natively by Xero with no workarounds needed).

Peter.
Xero Certified Advisor, CPA & Chartered Accountant and Canada's ONLY Xero award winning accountant (Most Valuable Professional). I also give implementation advice to clients all over the globe. Get a Xero MVP on your team! Visit my blog for various Xero articles.

You can book a live remote Xero support session with me or self-paced Xero training for a very reasonable price.
 

Peter McCarroll  

BC Canda 5% GST (refundable) and 7% PST (non-refundable) >> my work around for a single purchase invoice split between multiple types of costs:

Apply 5% GST to each line.

Create a single additional line with 0% tax exempt for the entire PST amount. Allocate that to the expense line with the largest cost. Allocation errors not at all material in my business at this stage so I live with it.

However, as my business grows this could be a deal breaker because most of my purchasing attracts PST and impacts various categories of cost of goods sold and therefore impacts product costing analysis etc.

Seems as simple to my non-developer brain as adding refundable/non-refundable options to the tax codes.
 

Anthony M  

Anthony, nothing is as simple as it seems. Your approach requires LOTS of work for every transaction. The best way to deal with this is as previously advised: create a tax rate called "GST with 7% PST on Purchases" using 4.6729% (this will create 50c tax on an $11.20 tax inclusive transaction - being 0.50/10.70) and code things tax inclusive.

Peter.
Xero Certified Advisor, CPA & Chartered Accountant and Canada's ONLY Xero award winning accountant (Most Valuable Professional). I also give implementation advice to clients all over the globe. Get a Xero MVP on your team! Visit my blog for various Xero articles.

You can book a live remote Xero support session with me or self-paced Xero training for a very reasonable price.
 

Peter McCarroll  

Hi Peter, thanks for your suggestion. Here is an example of a receipt I have in front of me:

Net $18.98 expense account type 1
Net $42.87 expense account type 2
Sub-total $61.85
GST $3.09
PST $4.33
Total cash paid $69.27

Because GST and PST are not shown at the line item level which need to go to different GL accounts, I don't see how I can code tax inclusive, or am I wrong here?

As far as setting up the tax rate goes, am I setting up a entirely new rate as you suggest or adding it as a second component to the existing default Tax on Purchases rate? (There's no option for me to add it as a second component.)
 

Anthony M  

Anthony,

I usually set the quantity to 1.12 to gross up the expense to the tax inclusive rate. If the item doesn't include PST then you would gross it up by 1.05. Now you can apply the hybrid rate in a tax inclusive mode.

I would probably create a new rate and use the old one for 5% GST only.
 

Peter McCarroll  

Ok I'm following. Last question then: will the 1.12 affect quantities in inventory? I haven't set up inventory yet. Thanks for our help with this. much appreciated.
 

Anthony M  

Yes, it will. If you are using inventory items then you will need to multiply the amount by 1.12 rather than the quantity - but you can do this in the cell.
 

Peter McCarroll  

Terrific, this is all working great. Thank you Peter. I'm about to install Unleashed inventory next so hopefully that will capture PST on purchases and pass it across to Xero okay.
 

Anthony M  

Hi Anthony - running an inventory system is a whole new level of complication. Remember that when you buy for resale you should be giving the vendor an exemption certification for PST, and therefore only have GST to account for as normal.

Peter.
Xero Certified Advisor, CPA & Chartered Accountant and Canada's ONLY Xero award winning accountant (Most Valuable Professional). I also give implementation advice to clients all over the globe. Get a Xero MVP on your team! Visit my blog for various Xero articles.

You can book a live remote Xero support session with me or self-paced Xero training for a very reasonable price.
 

Peter McCarroll  

I have voted for this feature as well, I think it is so important and yes, I agree, perhaps people aren't taking the time to vote on this, and I think you will find that you are losing a fair amount of business or having disappointed customers that are not aware of this forum or aren't taking the time. Also, if a free accounting app like Wave (www.waveapps.com) can have the option when creating taxes to check off a Non-Refundable button that takes care of this problem with PST, it kind of amazes me that a company this big that has intentionally made this platform available globally, would not take the time to roll out a fundamental feature. I still love Xero but this is definitely annoying and our clients are not excited about it.
 

Alice Peterson  

Hi team - really appreciate all of the discussion here! So much so that the team decided to write up some Help content in response to some of the questions on this discussion. Have a quick read of it here and let us know what you think.

No set plans as yet to address this specifically within Xero - but of course we'll keep you updated of any changes. Feel free to add your vote here as well.
 

Brittany H (Community Manager)  

Brittany, after many years of discussion, and numerous BA's being assigned to research this issue, I am very disappointed that this is not being addressed. It is significantly impacting Xero's adoption here in Canada where QBO deals with non-refundable sales taxes much more naturally. Given that the Canadian marketplace is bigger than Australia, and far simpler than the US, this is a huge slap in the face!

The help page doesn't cover enough - I am sending you a rewrite.

Peter.
Xero Certified Advisor, CPA & Chartered Accountant and Canada's ONLY Xero award winning accountant (Most Valuable Professional). I also give implementation advice to clients all over the globe. Get a Xero MVP on your team! Visit my blog for various Xero articles.

You can book a live remote Xero support session with me or self-paced Xero training for a very reasonable price.
 

Peter McCarroll  

I agree with Peter above... Its mind boggling that Xero wouldn't do something about this. I would put all my votes on this feature (not really a feature but rather a core part of any accounting system) if I could. I'm steadily adding clients on QBO but this sales tax thing in Xero just makes it extremely difficult to do the same.
 

Greg Penner  

Also agree with Peter above. Good point re: size of CA market vs. AU/NZ as well. And good point re: interest - we didn't bother creating an account and posting two years ago when we were investigating adopting Xero, we just decided to wait until the issue was resolved. Still not resolved, and looks like we'll have to move to QBO as much as we dislike their system and service (we want to do everything we can to move away from QB).
 

Darren Widenmaier  

Peter, from your post back in April, did you ever send a rewrite to Xero? If so, can you post a shared link where we can download it? Or does the current Xero help link incorporate your suggestions/edits? Thanks.
 

Greg Lowitz  

Like Greg L, I would be interested in Peter's rewrite if available. Trying to figure out how to make Xero properly track our California use tax liability (for purchases from out-of-state vendors who don't charge us sales tax).
 

Greg Johnstone  

Hi Greg1 and Greg2. Yes, I did send Xero a rewrite and they did update the help page linked to above, but it is specific to Canadian GST/HST/PST issues, not US issues.

I find that the Sales Tax system works just fine for US sales tax IF you use it correctly. If you would like me to help you through it please feel free to book a support session.

Peter.
Xero-award winning Most Valuable Professional accountant serving NZ, Canada and US. Book a paid live remote Xero support session with me (no matter where you are) or use our self-paced Xero training.
 

Peter McCarroll  

Just looking into Xero and am a little confused still about the PST tracking. If you add a tax rate with two components (i.e. gst & pst) it still all goes into one sales tax account, correct?? This is confusing as it is two different tax agencies, with two different reporting times. Am I missing something?
 

DENISE Meyer  

Hi Denise,

If you are selling in a PST province then you do want to have a Sales Tax Code that has two components - one for the GST and one for the PST. When you run the Sales Tax report it will break these down so that you can see what total to report to which agency. The Sales Tax Report can be run for any date range, so you could report for a quarter for PST and annual for GST (for example).

If you would like an expert to review and tweak your setup please book a session below.

Peter.
Xero Certified Adviser, CPA & Chartered Accountant and Canada's ONLY Xero award winning accountant (Most Valuable Professional). I also give implementation advice to clients all over the globe. Get a Xero MVP on your team! Visit my blog for various Xero articles.

You can book a live remote Xero support session with me or self-paced Xero training for a very reasonable price.
 

Peter McCarroll  

Hi, I have a confusion
I made a payment to the government for my sale taxes G.C.t (general consuption taxes)
which I made every month but I'm unsure which ledger to use.
Help please
 

Silvana Aldea