Why currency doesn't always add up in Excel

Well I say Excel this also applies to OpenOffice's Calc and, judging by the number of demands sent out by companies for £0.00, several accounts packages.

Imagine you've bought an item for £15 and VAT at 17.5% has to be applied to that. You run the sum and the total is £17.63. But you've bought two different items both at £15, not a problem add the next item on and total them to £35.25. Anyone spotting that?




If we run £15+£15 and then add VAT we do indeed see £35.25, but how can a price ending in a 3 double to a 5? Because of rounding. See £15+VAT is in fact £17.625 but the display is set to currency and that's only two decimal places so it automatically rounds it either up or down, but the programme still thinks of it as having three places; that invisible third number still counts. So double £17.625 and it correctly calculates it to £35.25.

But this isn't a problem if you subtotal and then add VAT? Possibly. Consider those companies that charge using fractions of a penny yes energy companies I'm looking at you.

So you use 50 units of 'energy' at £0.0513 per unit you owe £2.57 add the lower 5% rate of VAT and you owe £2.69. For anyone following along there, might be stating "Shouldn't that be £2.70?" well again possibly. Let's show our working.

50*0.0513 = 2.565. Now this will show as £2.57 however if I leave it at that I'm applying a 5% increase to 2.656 not £2.57 and that equals 2.69325 which will show as £2.69. Hopefully so far so clear except what happens when I pay my £2.69?

£2.69-£2.69=£0.00. So I'm in the clear? Think again 2.69325-2.69=.00325 which appears as £0.00. But 0.0325 is above 0.00 so you still owe the company money and out goes the letter demanding you pay the £0.00 you owe or they'll take legal action against you.

So how do you avoid this in, for example, Excel? You round the figures. Excel features three functions for this Round(), RoundUp(), and RoundDown(). enter the figure or formula and end it with a comma and the number of decimal places and the output will be exactly that. So:

Round(2.656*1.05,2)=2.69
RoundUp(2.656*1.05,2)=2.70
RoundDown(2.656*1.05,2)=2.69

The other method is to tell Excel to use the figures as displayed. So if you enter "=2.656*1.05" and display it as currency any subsequent use of this figure will use £2.69 and not 2.69325.

Why not use this all the time? Well at least on some versions of Excel it's a programme wide change not a spreadsheet level change which means it'll do it for all your spreadsheets even the ones you don't want it to. Secondly you might want to selectively pick the rounding; that is RoundUp() the charge for energy while RoundDown() the VAT that you'll end up having to pay to the government.


Not that I'm suggesting that's the way to use it of course.