I’ve spent a day wrestling with troublesome pennies, and it’s all the Institute of Electrical and Electronics Engineers’ fault. Bastards.
It’s that fiduciary time of year again for the company I co-direct. As Saturnalian delights begin to engulf the minds of others, we have to help our book-keeper and accountant prepare the end of year company accounts. It’s not a pleasant task. Customers do stupid things, like underpay or overpay, forget about VAT, demand small refunds and generally act the giddy goat against any prospect of an elegantly balanced set of books. When we started seven years ago, I programmed our company’s invoicing and accounting system. This has grown organically over the years, accreting some wonderfully powerful and tailored capabilities, but also a whole lot of silt. I’m not an accountant, and nor is my co-director. Some of our early decisions in the system’s design have required a lot of untangling, but the most insidious problem turned out to be one that neither of us could have predicted; a problem predicated on a lie told us by our maths teachers.
We’re taught much simplistic tosh at primary school. The silly model of the atom, with all those electrons swooshing round their perfect racetracks, is a famous example. Few A Level students quite shake off the faint stench of betrayal on being shown the messy quantum truth. RE lessons ply more insidious deceits, insinuating that there’s ample evidence for a historical Jesus, whatever one’s opinion of his divinity. The one subject where one might have hoped that subjective whimsy be absent is mathematics. Especially in our early years of arithmetic, we assume that we are being fed vital axioms, each pure, wholesome and incorrigible. All those little tools – carry the 1, move it into the tens column, divide this by that. So useful. So firm in a world of flimflam and fashion. So misleading.
We were taught that rounding up or down was a simple matter of deciding the number of decimal places one desired, and then, if the subsequent digit was five or larger, round the preceding number up. If the digit was between zero and four, round the preceding number down. This is how it was to be done. No arguments. No discussion. One Way to Round Them All. No doubt, had I stayed for A Level Stats, the lie would have been revealed. And a lie it is, one which has had material effect all these years later.
My company’s invoicing system does lots of real-time arithmetic. At the press of a button, for example, it calculates our VAT return with up-to-the-minute data. It lets us find out who owes us what at any moment. It shows what services are earning what income. All of these functions, of course, require mathematics. If one is calculating VAT on the fly, one will round the resulting number to two decimal places, which is all the decimal places that money deserves. To make things even easier, the database has a built in rounding function, so it can spit out subtotals, VAT included, without having to do anything fancy in the programming language that communicates with the database. For years, we have been using the system blithely, but over time, we have noticed anomalies creep in. Pennies here and there just didn’t seem to add up. Credit notes didn’t quite to balance out the full VAT-added invoices. Just by pennies, of course, across hundreds of invoices, so nothing particular to worry about. But pennies add up. Slowly but surely, we began to realise that, somewhere in the huge forest of code was some pernicious weed, tangling our data, its vines warping our results just enough to annoy our book keeper. What could it be? My co-director and I read all our code, examined our algorithms and refactored the more knotty areas. Noting seemed to help – the pennies just kept popping discrepantly into what should have been a balanced set of accounts. They constituted a little froth, eventually forming a scum on our financial books.
We pared our code to the bones, until the answer stared us in the face, astonishing in its simple depravity: both the programming language and the database were simply not rounding numbers consistently. Sometimes they rounded up, sometimes down. We did some research, not quite believing that something so fundamental could be so askew, and made the astounding discovery that what we had been taught in school as the incorrigible rule in rounding numbers was just a fashion. A fad. A debate. A whim.
The Institute of Electrical and Electronics Engineers, or IEEE, sets standards which programming languages and their ancillary libraries follow. Amidst their fiddling, prescribing and proscribing, they decided that our normal school-taught notions of rounding numbers are flawed: why should 5 always be rounded up? It sits in the middle, so why should it not, half the time, be rounded down? Spread the love! So, how does one achieve such ordinal egalitarianism? They decided that, if the number before the 5 is odd, then it shall round up. If the number before the 5 is even, then lo, it shall round down. So, according to the IEEE, 1.5 rounds to 2. And 2.5 also rounds to 2! And what the IEEE sows, our C libraries, perl sprintf functions and MySQL ROUND statements reap. Now, imagine how the IEEE’s capricious juggling affects VAT calculations and the like, which require consistency. Yes, statistically, the IEEE’s rounding produces a “fairer” distribution of roundees. For financial applications, however, such wobbling idiocy is useless. Tellingly, neither the programming language nor the database offered as an alternative the traditional and financially useful rounding as taught at school. We ended up having to hand-write our own rounding functions, and substitute them throughout our code and database query. This wilful lack of such a useful function built in is an interesting insight into standards bodies and the designers who slavishly follow them. In researching this problem, we found we were not the only ones affected by the little bombshell. People are constantly reinventing the wheel in trying to get rounding back to the comfortable certainties of their school days. Language designers and mathematicians have little sympathy. One online complainant received the following retort:
“I wonder whether some people are missing the point here. There is no law of nature which says that 0.5 must round up”.
Could someone please go back in time and tell that to my maths teachers?
Are you sure it’s not just a Perl thing?
When doing implicit conversions, all algol-derived languages tend to simply round down. (int)1.8 == 1
It’s basically anything that uses the standard C or C++ libraries. Perl does it, MySQL does it, python does it, Ruby does it, the shell does it. Try it yourself.
I get the following in python:
>>> “%.2f” % round(1.1249, 2)
‘1.12’
>>> “%.2f” % round(1.124999999999999, 2)
‘1.12’
>>> “%.2f” % round(1.125, 2)
‘1.13’
Curiously enough, 1.1249999999999999 does give 1.13, but I suspect that’s down to the limitations of the IETF floating point type, rather than the language.
I presume that the round() function is not using the standard C library calls to do this rounding. When using any of the sprintf style rounding functions, or int() however, I get the same behaviour as Martin: it always rounds down towards 0.
Interesting: the newer version of python seem to behave as you say, ignoring the standard C library routines. Perhaps sanity is breaking out in some quarters.
Of course, I meant IEEE float. Whoops.
I hope you are also applying the tax return subroutine which has different rounding rules too.
1. Never do currency calculations in floating point, no matter what the precision; use fixed point (e.g. Java’s BigDecimal, whatever the Perl/Ruby/etc. analogs)
2. Never store currency in floating point DB fields, no matter what the precision; used fixed point (e.g. Num 21.8)
3. Financial services industry best practices for currency calculations/storage is AT LEAST 5 significant digits AFTER the decimal point, in FIXED POINT (not floating). This may not necessary for your calculations.
4. If your input data is good to 2 significant digits after the decimal point (e.g. 2.35 Euro), and no calculations, nor storage, is converted to float, no rounding is EVER necessary. That’s the idea of fixed point — invented in COBOL, kids. 1957.
The rounding algorithm that you tripped up on is sometimes called ’round to even’:
http://en.wikipedia.org/wiki/Significance_arithmetic#The_round-to-even_rounding_rule
But it’s traditionally known as ‘Banker’s rounding’, because it’s the way rounding is done in the financial industry. In fact, i think the technique goes back to Gauss, who realised that when summing rounded quantities, using this method would lead to a more accurate sum.
No, the real problem is not that round-to-even is being used, it’s that you didn’t know it was being used. Blame your teachers, your textbooks, or your programming manuals, but don’t blame the IEEE. Indeed, i’d say that if you wrote financial software for real production use in a real company without educating yourself on the basics of computer arithmetic, you don’t really have anyone to blame but yourself.
If you had done your reading, you’d already know the things that dct told you: you don’t use floating point for money. Ever. End of story. You count pence (or cents, or centimes, or whatever) and either use integers, or if you’re serious, fixed point, which gives you some guard digits at the end.
Note that the easiest way to do fixed-point arithmetic is just to use thousandths of a penny as your working unit, and work in integers. You get integer-style round-down behaviour with the counts, but you can apply whatever rounding scheme you like when it’s time to convert to whole pennies.
Tom, I do indeed blame my maths teacher. It’s not that any rounding scheme is “wrong” or “right”, but that one particular flavour is taught as “canonical” at school. This has had damaging effects on not just me, as a google search shows.
You’ll think I’m making it up, since this is a very characteristic comment from me and usually I do it on purpose, but – honestly – I read the start of your post as “I’ve spent a day wrestling with troublesome penises”.
Obviously it says more about me than you.
Hi folks, I found your discussion very interesting and just thought I might add something that I have found in practice.
Facts:
We use a system called TAS Books for our sales invoicing, its set to calculate VAT on the total sale not on each product, quantity line. In the VAT calculation it rounds x.xx5 upwards and anything less than x.xx5 downwards.
When we process a sale over the phone we calculate the vat on a calculator and from inspection of the result, round it in the same way, this works fine.
We have a website that uses a lot of Perl and it uses the
sprintf(‘%.2f’,$longVAT) function to round the VAT figure. It seems to round down generally, meaning that the total is often a penny low.
This is ok but it dosn’t half ‘mess’ us up because the books system does not let us fiddle with its VAT and the invoice does have to be produced through the books system.
Hope this isn’t just too boring, to me its really interesting.
Questions:
Why doesn’t perl have a straight forward, dedicated, Round function?
Does anybody think that this might just be a version problem, the perl interpreter in use is probably year 2000 or 2001 vintage?
I know from earlier discussion that a test indicated that this perl function does round correctly but ours doesn’t seem to.
Kind Regards to all
It’s really very complex in this full of activity life to listen news
on Television, therefore I only use web for that reason, and take the hottest news.