Newbie, Help needed with rounding errors

[John]

I have a table which stores transctions with a currency price field.
I use a query to put together relevant info from several tables including
the sum of the price grouped by invoice.
and a calculated field for sales tax.
My problem is that when I print the invoice report the subtotal +tax dont
always equal Invoice total as displayed.

One example here
subtotal 239.5074 displays after rounding as 239.51
Tax 50.2965 displays after rounding as 50.30
Invoice total = 289.8039 which of course displays as 289.80
However this is incorrect as the rounded figures of 239.51 + 50.30 should
equal 289.81.
I cant round the figures in the query because this would give incorrect
totals.
This must be a common problem, what is the usual solution

Any help greatly appreciated
Regards John

 

[bcap]

You say that "I cant round the figures in the query because this would give
incorrect totals."

But how can 239.5074 be a *correct* total? When did someone last pay you
0.0074 cents?

 

[John Spencer]

Since I don't know how you are doing the calculations it is difficult to be
specific, but I would try

Round(SubTotal,2) + Round(Tax,2)

By the way, you might check the rules in your jurisdiction on calculating Tax.
Some jurisdictions always round up to the next penny.

Also, you might want to be aware how round works. Since it uses

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[bcap]

You got cut off mid-sentence John!

"Also, you might want to be aware how round works. Since it uses bankers
rounding."

 

[Ken Sheridan]

That's exactly the problem because day-to-day consumer items are sold at a
quoted gross price not net price. It derives from computing the net price
and tax from this, e.g. if the gross price of an item is quoted as 30.00 GBP
and the tax rate is 17.5 per cent (the standard rate of VAT in the UK) then
the net price computes as 30 / 1.175 = 25.5319.

Ken Sheridan
Stafford, England

 

[bcap]

What you say is obviously correct as far as it goes, but that doesn't mean
that someone sold you an item for 25.5319 pounds. If the seller gave you a
VAT invoice for that item, it would show the gross price was 25.53, and the
VAT was 4.47. You need to round at the most granular level.

So, I would suggest, that by coming up with a sub-total of 239.5074 the OP
has already erred. All the items making up that sub-total should have been
individually rounded.

I would suggest that he has also erred by applying tax to a total, instead
of to each individual item. Assuming that the items are priced gross, then
the invoice total will need to add up to the total of the gross prices,
otherwise eyebrows will be raised. But, by totalling the net prices and
then applying the tax to the total, you guarantee that the invoice total
will often *not* add up to the total of the gross prices.

The problem with this approach is compounded when you consider that most
jurisdictions have different sales tax rates for different products. Here
in the UK, we have two rates: zero and 17.5%. In the Republic of Ireland
(where the OP is from it would seem) they have three rates: zero, 13.5% and
21%. So, how can you calculate the tax on a total when the constituents of
that total might need different rates applied to them?

 

[Ken Sheridan]

The best practice, i.e. that which produces the most accurate result, is to
compute VAT on each total net price of all items attracting the same rate of
tax, rounding the results in each case, and then summing the subtotals to get
the total invoice amount. Examining a few invoices from my own UK suppliers
where both standard and zero-rated goods or services are supplied I find that
they all conform to this practice, though maybe I'm just lucky and I deal
with suppliers who adopt good accounting practices. I've no doubt that
there are cowboys out there who'd do otherwise if they could make a few extra
quid here and there out of it.

I also see on my suppliers' invoices that the sum of the individual line
amounts net of VAT does not always tally with the total net amount, the
former being rounded per line item, the latter being a rounding of the sum of
the underlying unrounded values. So my supplier is in fact charging me
25.5319 GBP, not the 25.53 GBP which appears in the invoice line, or to be
more accurate they are charging me 25.5319 GBP adjusted pro rata the rounded
total net amount for all items attracting standard rate VAT. I could work
that out from the invoice in front of me, but life is too short!

BTQ, while on the subject of rounding in general you might find the
following of interest:


http://www.pldesignline.com/howto/showArticle.jhtml?articleID=175801189


Ken Sheridan
Stafford, England

 

[bcap]

Well it depends partly on whether your suppliers are pricing net (as is the
normal practice business-to-business) or gross (as is the normal practice in
retailing). If you go into a shop and buy 10 widgets priced at £30.00 each
and 5 gizmos priced at £10 each the invoice must add up to £350.00, not
£349.99 or £350.01.

It doesn't really matter what the gross total is when pricing net, but
nevertheless the invoice needs to be internally consistent: the total of a
column of net prices must equal the sum of those prices, likewise tax and
gross. At least, this is what my customers expect from the order processing
systems I build for them.

 

[Ken Sheridan]

Obviously I picked retail suppliers to illustrate the point. You asked "When
did someone last pay you 0.0074 cents?". I gave you an example of just that.
My guess is that the OP is either a retail trader and pricing gross of tax,
but another possibility is that net prices are being discounted (try 12.5
percent of 22.45 for instance).

Whichever is the case tax should be applied in the way I described, not at
line item level. This is where the OP is going wrong.

Ken Sheridan
Stafford, England

 

[bcap]

Let us suppose that I sell two *different* items at a gross price each of
25.35. The gross total is therefore 50.70.

The net price each (in the UK) is 21.57447. However, I need to show the net
price per item on the invoice, so I round to get 21.57 each.

Now I need to show the net total: 21.57 + 21.57 = 43.14.

Now I calculate VAT: 43.14 * 0.175 = 7.5495, which I round to show on the
invoice: 7.55

My gross total is now the net total + VAT, which is 43.14 + 7.55 = 50.69

WRONG!!!!

You could argue that you would show rounded results on the invoice but
behind the scenes you calculate on un-rounded, but then someone only needs
to take a calculator (or do some simple mental arithmetic if they're as old
as me!) to see that the numbers on your invoice just don't stack up.

Conversely:

Net price per item is 21.57, VAT per item is 25.35 - 21.57 = 3.78.

Net total is 43.14. VAT total is 3.78 + 3.78 = 7.56

Gross total is 43.14 + 7.56 = 50.70

All is sweet!

But you don't have to take my word for it, you can ask HM Revenue & Customs:

http://www.hmrc.gov.uk/vat/reclaim-howto-calc.htm

"How to calculate the VAT in a VAT-inclusive price:
a.. Divide the VAT-inclusive price by 1.175 to arrive at the VAT-exclusive
price, for example: £117.50/1.175 = £100 VAT-exclusive price
b.. Subtract the VAT-exclusive price from the VAT-inclusive price to
arrive at the VAT element, for example: £117.50 - £100 = £17.50 VAT"

 

[Ken Sheridan]

All may be sweet as far as the Revenue concerned, and heaven knows Alistair
Darling needs the money, but not as far as your shareholders are concerned,
as you've paid an unnecessary 0.01 GBP in tax. This may be very public
spirited, but not good business!

50.70 - (50.70/1.175) = 7.55106382978724

which rounds to 7.55 GBP of course, not the 7.56 you paid.

And 50.70/1.175 = 43.1489361702128

so you should have pocketed 43.15 GBP That 1p could be the difference
between solvency and bring in the administrators one day.

What you are doing is making the classic mistake of unnecessarily
introducing cumulative rounding errors. This is what the currency data type
is designed to prevent, so by rounding at the line item level you are
subverting the protection the MS developers (bless 'em!) tried to give mere
mortals like us. The underlying mathematical principle is quite simple: to
guard against cumulative rounding errors all calculations should be
undertaken at a precision of at least 2 significant decimal points greater
than the precision to which the final amount is expressed.

This is why tax should only be computed on the sub-grouped total values, not
on each line item. The method of calculating tax is not really an issue.
The Revenue's method of subtracting the computed price net of tax from the
gross price is a simple and reliable one, but the same result can equally be
obtained by some other algorithm.

If you are showing rounded net prices per line then the total may well not
tally with this. You might recall that I said yesterday that I had an
invoice in front of me where this was the case, but that is perfectly
acceptable theses days. Even though you an I can probably remember the days
of leather bound ledgers, we no longer have armies of clerks perched on high
stools manually adding columns of L S D. People by and large understand that
the individual amounts are in fact only approximations of the real underlying
values. My solicitor's bill when he drew up my will even showed discrepant
amounts!

This principle is a generalised one, not limited to financial calculations.
It applies to many areas where arithmetical calculations are undertaken.

I think we've probably exhausted this topic by now. I think I have some
interesting paint to watch dry.

sláinte,

Ken Sheridan
Stafford, England

 

[bcap]

So your answer to the problem of how to prevent invoices showing
discrepancies is...who cares? And *you* are the one who was talking
earlier about good practice!

But, you go your way, I will continue to go my way of delivering to my
customers what they want: invoices that *add up*!

All may be sweet as far as the Revenue concerned, and heaven knows
Alistair
Darling needs the money, but not as far as your shareholders are
concerned,
as you've paid an unnecessary 0.01 GBP in tax. This may be very public
spirited, but not good business!

50.70 - (50.70/1.175) = 7.55106382978724

which rounds to 7.55 GBP of course, not the 7.56 you paid.

And 50.70/1.175 = 43.1489361702128

so you should have pocketed 43.15 GBP That 1p could be the difference
between solvency and bring in the administrators one day.

Oh puhleeeze. It's just as easy to come up with an example where it works
the other way.

What you are doing is making the classic mistake of unnecessarily
introducing cumulative rounding errors. This is what the currency data
type
is designed to prevent, so by rounding at the line item level you are
subverting the protection the MS developers (bless 'em!) tried to give
mere
mortals like us. The underlying mathematical principle is quite simple:
to
guard against cumulative rounding errors all calculations should be
undertaken at a precision of at least 2 significant decimal points greater
than the precision to which the final amount is expressed.

Great, but how you gonna make your invoices add up? Oh that's right, you're
not.

This is why tax should only be computed on the sub-grouped total values,
not
on each line item. The method of calculating tax is not really an issue.
The Revenue's method of subtracting the computed price net of tax from the
gross price is a simple and reliable one, but the same result can equally
be
obtained by some other algorithm.

I think I've already demonstrated that it cannot, at least for anyone who
wants their invoices to add up correctly (as the OP does).

If you are showing rounded net prices per line then the total may well not
tally with this. You might recall that I said yesterday that I had an
invoice in front of me where this was the case, but that is perfectly
acceptable theses days.

Not to me, or my customers.

Even though you an I can probably remember the days
of leather bound ledgers, we no longer have armies of clerks perched on
high
stools manually adding columns of L S D. People by and large understand
that
the individual amounts are in fact only approximations of the real
underlying
values. My solicitor's bill when he drew up my will even showed
discrepant
amounts!

I don't think that a general principle can be derived from your solicitor's
sloppy practice.

 

[Tom Lake]

If you are showing rounded net prices per line then the total may well not

tally with this. You might recall that I said yesterday that I had an
invoice in front of me where this was the case, but that is perfectly
acceptable theses days. Even though you an I can probably remember the days
of leather bound ledgers, we no longer have armies of clerks perched on high
stools manually adding columns of L S D. People by and large understand that
the individual amounts are in fact only approximations of the real underlying
values. My solicitor's bill when he drew up my will even showed discrepant
amounts!

Maybe that's the way thing are done in England but here in the US, everything
had better add up or the accountant will be looking for a new job or an
orange jump suit (worn by prisoners) that fits depending on the amount of
discrepancy.

Tom Lake