Why Does Xero Round Incorrectly?

[Mr Pointy]

I create invoices in Excel & it calculates VAT & rounds to 2 places: £x.886 is rounded to £x.89

When I create a matching invoice in Xero it rounds down to £x.88 so the invoice I send out doesn't match by 1p. Is there some accounting convention where sums are rounded down rather than 0-4 rounding down & 5-9 rounding up?

[droopsnoot]

Is it a single-line invoice, or a multiple line? Xero calculates VAT on a line-by-line basis which can lead to discrepancies if you're calculating it on the total, as I found out when sending invoices from a point-of-sale system that calculated the various VAT totals for the whole invoice.

More here: https://developer.xero.com/documentation/guides/ho...

I did a quick search on VAT rules and the explanation on how it's done was basically normal numeric rounding rules.

[Bradders278]

I have experienced this; and for me it was my excel formulas causing the issue.

I have cost and sell prices in one spreadsheet. And the sell price is shown as "currency" and rounded to 2 decimal places like you say. Some of my costs are dependant on exchange rate and as such the complex formula can often drive a number being something like 28.1232 - what what happening is that the Xero line was using 28.12 for the calculation (that's what I entered for the line) but I think Excel was doing some of my calculations based on 28.1232 so on high quantities I was getting 0.0032 extra which soon added up.

I now have a column where I copy over the whole column as-seen so that I start fresh - in this instance excel will be feeding 28.12 not 28.1232 if that makes sense?

I never figured the exact issue but that was my best guess - and for me it fixed the immediate problem of my Xero quote not matching my previous estimate which was driven by Excel.

Hope this may be of help!
Scott.

[MustangGT]

droopsnoot said:

Is it a single-line invoice, or a multiple line? Xero calculates VAT on a line-by-line basis which can lead to discrepancies if you're calculating it on the total, as I found out when sending invoices from a point-of-sale system that calculated the various VAT totals for the whole invoice.

More here: https://developer.xero.com/documentation/guides/ho...

I did a quick search on VAT rules and the explanation on how it's done was basically normal numeric rounding rules.

This,

As noted above VAT rounding follows normal numeric rounding rules. If you have a VAT calculation of £0.886 Xero should round up to £0.89. It does not have an automatic round down facility. You can overwrite it yourself though.

[Mr Pointy]

droopsnoot said:

Is it a single-line invoice, or a multiple line? Xero calculates VAT on a line-by-line basis which can lead to discrepancies if you're calculating it on the total, as I found out when sending invoices from a point-of-sale system that calculated the various VAT totals for the whole invoice.

More here: https://developer.xero.com/documentation/guides/ho...

I did a quick search on VAT rules and the explanation on how it's done was basically normal numeric rounding rules.

Thanks, that seems to explain it. I suppose if you had different VAT rates for items in a multi-line invoice then calculating on the total would give the wrong answer. I'll have to change they way my invoices calculate VAT.

[MustangGT]

Mr Pointy said:

Thanks, that seems to explain it. I suppose if you had different VAT rates for items in a multi-line invoice then calculating on the total would give the wrong answer. I'll have to change they way my invoices calculate VAT.

Xero will still calculate it correctly since it takes it line by line, If a line is at a different rate the item should have been set up with the different rate and Xero will know this.

[48k]

Mr Pointy said:

droopsnoot said:

Is it a single-line invoice, or a multiple line? Xero calculates VAT on a line-by-line basis which can lead to discrepancies if you're calculating it on the total, as I found out when sending invoices from a point-of-sale system that calculated the various VAT totals for the whole invoice.

More here: https://developer.xero.com/documentation/guides/ho...

I did a quick search on VAT rules and the explanation on how it's done was basically normal numeric rounding rules.

Thanks, that seems to explain it. I suppose if you had different VAT rates for items in a multi-line invoice then calculating on the total would give the wrong answer. I'll have to change they way my invoices calculate VAT.

Exactly this. Line items might have different rates attached - in the case of UK VAT one product might be 20% VAT, another 5% VAT, another zero rated, or exempt and so on. To cope with this, Xero has to calculate the tax per line it can't simply have one blanket tax amount applied at the end.

[droopsnoot]

Even with all line items having the same tax rate, it's easy to get different totals when rounding by line vs. rounding by totals. The link I posted shows a worked example where three lines with the same tax rate produce a 1p difference between the two methods.

[uknick]

Bradders278 said:

I have experienced this; and for me it was my excel formulas causing the issue.

I have cost and sell prices in one spreadsheet. And the sell price is shown as "currency" and rounded to 2 decimal places like you say. Some of my costs are dependant on exchange rate and as such the complex formula can often drive a number being something like 28.1232 - what what happening is that the Xero line was using 28.12 for the calculation (that's what I entered for the line) but I think Excel was doing some of my calculations based on 28.1232 so on high quantities I was getting 0.0032 extra which soon added up.

I now have a column where I copy over the whole column as-seen so that I start fresh - in this instance excel will be feeding 28.12 not 28.1232 if that makes sense?

I never figured the exact issue but that was my best guess - and for me it fixed the immediate problem of my Xero quote not matching my previous estimate which was driven by Excel.

Hope this may be of help!
Scott.

Did you try precision as displayed?