So here's a rundown of the top tips I can give for making your financial calculations that bit easier.
Always Work In Minor Units
I can't stress enough how much this helps in terms of accuracy, rounding and speed. Working in major units may look better to you as you don't have to reformat the numbers to display them but I hope I can make the case here for minor units.
Integer arithmetic is much much faster than floating point arithmetic. Remember that even a single decimal place makes a number a float as far as your computer is concerned and all the processor overheads that go along with them suddenly arrive. I know it's not a lot slower but in a complex financial system it all adds up believe me.
Floating point arithmetic can get it's sums wrong. Don't believe me? Then pull up a Ruby console and try this:
a = "69.54".to_f
b = a * 100
Gives 6955 instead of 6954. This is because the limitations of floating point arithmetic have caused something like 0.0000000000000000000000000000000000000000000000001 to be added to 69.54. I spend a good 4 hours chasing this bug which manifested itself as a 1p discrepancy.
Trailing zeros can cause problems for major units. Think of trying to pass round £10.00 or £1.10 in major units. Storing it as a float you would keep losing the trailing zeros and would find yourself having to sprintf all over the place. I've seen plenty of systems in my time that store prices as decimal strings to get round these issues! There are of course various decimal formats that can be used (decimal is a data type in MySQL and BigDecimal has been introduced in Ruby on Rails) but when it comes down to it, these are just wrappers around either floats or stings and majorly sub-optimal for the other reasons given.
Freeze the Exchange Rate
If your business works in pounds but you allow payments to be made in Euros then with every payment you need to store the current exchange rate with it. Exchange rates change by the day and if you don't know exactly what rate you get for your transactions then you can kiss goodbye to any sort of accurate profit calculations.
Rounding - Pick a Direction and Stick With It
Often you will need to apply discounts, add markup etc and have to perform percentage calculations. If you are working in minor units this should be the only time (in normal day to day operations) that you ever have to handle fractions of pence. You will make your life so much simpler if, for all these calculations you decide the direction to round and stick with it. Do you want to keep the extra for yourself or be a nice guy and let the customer keep it? That's what the decision comes down to.
If you don't have consistency in this you really will find yourself spending days chasing 1p discrepancies.
From a coding point of view I tend to round down as preference because (as I demonstrate above) floating point arithmetic can get it wrong sometimes and, as it just wipes out everything after the decimal point, a 'floor' function is much more reliable that a 'ceil'.
Use a Pre-Filter on your Data Submissions
Of course your customers are always going to want to work in major units - no-one wants to see prices in pence splashed all over your website and it's much more intuitive to type major units into form fields.
What I like to do is put a pre-filter on all input coming into my back end system (so in Rails you would run the filter on 'params' or in PHP you would run it on '$_REQUEST') which pattern matches any string monetary amount (remember, all form submission values come through as strings) in a major unit and converts it to an integer minor unit.
In Rails it's in the application controller and looks like this:
def filter_units (input)
if [Array, Hash, HashWithIndifferentAccess].include?(input.class)
input.each do |key, value|
#recurse through the data structure
input[key] = self.filter_units(value)
#match the string format for a major unit
elsif not input.nil? and input.match(/^\d+\.\d\d)$/)
#convert to a minor unit integer
(input.to_f * 100.0).to_i
#return the value unchanged
This also has the added benefit of validating monetary amounts - if a monetary field doesn't hit your back end as an integer then you know it has failed validation.