Re: Pennies problem


Dave Griffiths wrote:
Hi John

Did you find an answer to the problem? I see the discussion diverted a
little. I am curious on the answer for a program I am working on. Would
like to try and avoid the same mistake if there is one.


--

DaveG
Norway - Oslo - Skoyen


John wrote:

Hi

I am calculating tax as follows;

= [Net] * [TaxRate] / 100#

Client complains that there is sometimes difference in pennies in tax
calculation. Is there a better (more precise) way to calculate tax?

Thanks

Regards

My opinion, having not done this for a long time:

1. be sure all the numbers used in the calculation are of type
currency; do not use any literals as VBA may decide to treat literals
as some type that we may not want them to be treated as.
So the calculation of the original poster whould be expanded to
something like:

Dim Tax as Currency
Dim Net as Currency
Dim TaxRate as Currency
Dim Per as Currency

Net = 12.35
TaxRate = 7
Per = 100

Then we must consider rounding. Many rounding methods use bankers
rounding. That is each number is rounded to its closest even
approximation eg. 1.315 is rounded to 1.32; this looks great until we
learn that 1.325 is ALSO rounded to 1.32. Many (most?) businesses and
tax collectors do not round in this way. They round all halves UP.

If bankers rounding is suitable for you, you can use the VBA round
function.

So

Tax = Round(New * TaxRate / Per, 2)

But if you want the general old rounding most of us learned in grade
school you will need another algorithm.
There are some at
http://www.xbeat.net/vbspeed/c_Round.htm
You could test these for speed and conformance with how you want 1.325
rounded.

If you chose one your calcuation would now look something like this:
Tax = Round02(New * TaxRate / Per, 2)

Another warning: In the olden days when we tried to store a five digit
decimal numeral like 7.23685 as a currency type the rounding to 7.2368
or 7.2369 was erratic. I have read that this has now been corrected but
.... I would try to avoid this kind of thing regardless.

.

Relevant Pages

  • Re: best practices.rounding
    ... And, if using Currency, whether for actual currency or non-currency situations, ... greater precision at the individual transaction level the cumulative rounding ... can differ from the sum of the individually rounded amounts. ...
    (microsoft.public.access.gettingstarted)
  • Re: =?ISO-8859-1?Q?Sch=FCssel=3A_Imbecile?=
    ... I have long advocated such a tax on speculation although in my case ... sell after 120 days - pay tax as normal ... against adverse commodity and currency movements. ...
    (uk.politics.misc)
  • Re: Why not FP for Money?
    ... >> same rounding in every country. ... > calculate interest, tax, etc, they want to deal in fractional pennies. ... of which calculating withholding tax for US tax returns is ...
    (comp.lang.python)
  • Re: Anyone had different results between Access calculations and E
    ... professionally and am aware of the rounding issues. ... my calculations and try to have closer control over the calculating field ... Dim MyNumber As Single ... If that is the case, then use a currency field, NOT ...
    (microsoft.public.access.reports)
  • RE: best practices.rounding
    ... discrepancy with manual addition or subtraction when a Currency data type is ... greater precision at the individual transaction level the cumulative rounding ... can differ from the sum of the individually rounded amounts. ...
    (microsoft.public.access.gettingstarted)