Eh, doubles are fine for (most) currencies. Just don't do comparisons without appropriate epsilons.
People who compoare floating-point numbers for equality are going to make other fundamental mistakes with whatever data type you force them to work with.
You should have seen my face when debugging this.
Here's what I'm seeing:
---
JavaScript
(function() {
let inc = 0.37;
let times = 5000;
let total = 0;
for (let i = 0; i < 5000; i++) {
total += inc;
}
console.log('expected:', inc \* times);
console.log('actual:', total);
})();
actual: 1849.9999999997679
---
Java
public class MyClass {
public static void main(String args[]) {
double inc = 0.37;
double times = 5000;
double total = 0;
for (double i = 0; i < 5000; i++) {
total += inc;
}
System.out.println("expected: " + (inc \* times));
System.out.println("actual: " + total);
}
}
actual: 1849.9999999997679
---
update: Changing `double` to `float` in Java yields:
expected: 1850.0
actual: 1849.9778
and maybe that lines up with what you meant by "the differences should be a LOT farther than 3 digits", though it's hard to tell what you mean by "differences on the order of 10-20".
declare @i float =0.37,@times int = 0,@total float = 0
while @times < 5000
begin
set @total+= @i
set @times +=1
end
select @total
or
select sum(t) from(
select top 5000 convert(float, 0.37) t
from sysobjects a cross join sysobjects b) z
Do the sum two ways:
- export them in excel and do a sum
- select sum(value) from t
and you get two different values, with a difference of something like 15.
Replace double(10,2) with decimal(10,2), and the sum(value) works correctly.
I still have no idea why this happens. I could expect a tiny difference, and code like your above will indeed give differences like this, well under 1.0, but my particular scenario had differences many orders of magnitude higher. Still can't explain.
Oh, that's something entirely different. If some of your other numbers are much larger than 0.37, you will run into a different kind of problem.
You know about exponent and mantissa? Adding a value with a small exponent to a value with a big exponent will cause imprecisions. In extreme cases the result is just the bigger number.
But actually, since fixed point decimals are not all ways available use the smallest unit of account, not smallest legal tender, and integer arithmetic
And learn how to round
If you really don’t have access to a decimal type I think the best solution is to convert it to micro-units (price * 10^6). This is what Android does in its billing library.
What people use as cash is a distraction, generally
1. Decimal math
2. Binary floating point math
3. Domain knowledge trumps all; it makes no difference
The cardinal sin isn't using doubles for currency; it's using them without understanding either the tool or the job that you're asking the tool to perform.
I guess there are some contexts where floats are fine for monetary amounts, for example if you make economic forecasts or simulations.
Then that's your epsilon. Or, more reasonably, one or two powers of 10 further down. Absolutely nobody cares about a millionth of a cent. A few people care about a thousandth, though. Most people get antsy if you can't pin a calculation down to the nearest cent.
Floats are pretty much never fine for currency, but doubles are usually OK. There's a world of difference between a 24-bit mantissa and a 53-bit mantissa.
This is exactly what the article describes and explains. 10 cent plus 20 cent does not equal 30 cent, when using binary floating point. This is not acceptable in accounting, since at one point the error may accumulate and cause an error at the size of a cent (or more).
Years ago, I had a few apps in the App Store that made extensive use of it. It really was very nice to work with.
Example: http://tpcg.io/_A0IXQJ
(I guess it depends on what you mean by "true" decimal. If you meant BigNum, then sure.)
Only floating-point decimal is hairy. Fixed-point decimal is hardly more difficult than integer math.
That's because the binary is implemented in hardware for you; and those days you can usually (but not always) trust the hardware to do the correct thing; if you have to implement it youself (say, deterministic, side-effect free math), it's also hairy.
Don’t write with such certainty! Decimal math is great advice for many/most situations, but what if you have a LOT of numbers and not a lot of time? That big number crunching GPU is not available if you take this approach.
Numerical Methods was the most difficult CS course I took in university and also the one I did the worst in. And of course it was an elective, or else they wouldn’t have graduated many people at all. If you’re doing a lot of number crunching stuff, maybe you should ask people that know how to number crunch to design your system so it has the smallest errors :)
PS - I’m not the person for that job!
Just getting the data to the GPU for a compute shader to run on it would take longer than just doing on the CPU in almost every case.
On the other hand the CPU has floating point math too, and floating point math is MUCH faster than decimal math.
So his point holds if you replace GPU with CPU, but his use of GPU is likely inaccurate.
Anecdote: In state of the art physics code, it's common to use integers for coordinates [1]. Recently, I was working on a BSc. toy project, and I used ints for coordinates as is common. With both ints and floats it's important to be careful around functions like `tan` that go to infinity, but of course floats are more forgiving, so I prototyped some of the code using doubles.
I ended up comparing performance and it wasn't even funny. Double precision arithmetic was anywhere between 3x (where a good int algorithm is known) all the way to 100x faster (if the int algorithm is cordic, for example) than integers.
1: Springel 2013 p. 1-82 https://wwwmpa.mpa-garching.mpg.de/gadget4/gadget4-code-pape...
I'm not sure when I read it, but long ago I read some kind of AMA from a MS engineer working on Excel saying that his greatest achievement was working on the team that made the Excel's DAG solver trivially parallelizable. In the same thread, he mentioned that offloading to the GPU was being looked into. I guess it never came to fruition.
The number 1 supercomputer in the world has 8 million GPU cores (or is it 8 million GPUs? Execution Units?) and 600k CPU cores.
They are doing floating point math on GPUs. Numerical analysis [1] is used to create the best accuracy possible. This is something that has been done for thousands of years, basically since math has had problems with no "exact" solution.
Boy, did I file a pissy bug with the Sheets team that day, and then requested an Excel install I never let go of since that day.
* I guess it's maybe not surprising to js developers, but don't most modern browsers have integers by now?
What numbers in javascript are not actually doubles (what js uses under the hood for every number)?
You can convert them from doubles to 32-ints apparently by some bitwise hacks like (I'm pretty sure they still are 'doubles' though - and it just does some rounding tricks)
``` |0 //signed >>>0 //unsigned ```
So any webapp (google sheets) will likely have problems related to floating point math.
You can use the native BigInt arbitrary-precision integers and ignore all "fits under this arbitrary limited bit slice" problems.
You can use doubles as a integer directly, in a safe way, until you touch the 53 bits barrier (Number.MIN_SAFE_INTEGER === -9007199254740991 or Number.MAX_SAFE_INTEGER === 9007199254740991).
None of those really are (modern) webapp issues.
Floats have varying precision, not uniform. The closer the number is to zero, the more precision it has. There's a point where the precision is so low that it only covers whole numbers, and then a point after that where the precision is less than whole numbers.
Given the context of pointers, it's quite possible that they were large enough to reach that less-than-whole-number-precision range.
EDIT: For 32-bit floats, that's apparently any number above 16,777,216. Which seems surprisingly small to me. For 64-bit doubles that goes to 9,007,199,254,740,992.
https://blog.demofox.org/2017/11/21/floating-point-precision
Anyway, yes, that's all true. Excel, Sheets, JavaScript numbers, Java doubles, Python floats, and so on all work this way. That's why I asked how switching spreadsheet implementations solved the problem.
I agree with you that switching programs that are all using the IEEE floating-point formats with the same underlying hardware implementations should result in the same answers.
But Excel also uses floats (as in, Single-precision floating-point format, aka float32), because of "compatibility with Lotus 1-2-3".
https://softwarerecs.stackexchange.com/questions/53292/any-s...
https://learn.microsoft.com/en-us/office/troubleshoot/excel/...
[1] https://people.eecs.berkeley.edu/~wkahan/Mindless.pdf, §2
I find it funny the gap between what computer people think finance requires and actual practice.
The tax people in the US generally aren't interested in pennies any more! And when you use tax software that throws away the pennies before the final results, then your sums may very well not match the forms whose information is independently reported to the IRS, by well over a dollar. But nobody cares!
...while financial institutions send reports to clients and the IRS that have the totals rounded only at the end, meaning the things that are supposed to match don't and can't.
Even though they are both in dollars, they are not consistent even to the nearest dollar.
You do not want your numbers changing in the next version of Excel.
But there’s nothing stopping you from doing arbitrary precision decimal in excel except back compatibility (and all the thousands of lines of code looking for a float).
Fundamental reason being that powers of 1/10 in decimal fractions are not always representable by powers of 1/2 in binary. Specifically 0.1 = 0b00011001100110011... is infinitely long binary string. Truncating it to any finite bit-width like 32 or 64 bits always introduces an error.
The correct answer is to use floating point and to understand it and numerical software before doing it. If you don't have a decent understanding of numerical analysis, don't write important numerical software.
So yes, if you use fixed point you obviously get different results, but you won't get correct results according to what's required by accounting standards unless you truncate to fixed point when needed. It's not that this difference is large - after all, it's about rounding off fractions of a cent - but accounting does need to be exact.
Completely depends - package them in tranches to sell to secondary markets by the thousands - then you do exactly as I pointed out. Or if you're doing Monte Carlo futures projections modeled as compound interest and only need the value at a future time.... Or any of thousands of financial modeling needs....
If you're printing monthly bills for consumers then you round, but only at output, and only for viewable parts.
So you cannot claim things are not computed this way - it depends on the financial application you're working on,
>So yes, if you use fixed point you obviously get different results, but you won't get correct results according to what's required by accounting standards
Ha - which standard are these? Care to cite them? I've been through this space a long time, and every time someone tells me there is a standard and I ask them for it they soon realize there is no gold, single standard. There are zillions of acceptable choices. There are ones for consumers, ones for intrabank, interbank, fed to bank, loans, mortgagaes, taxes, and on and on. There is no "correct results according to what's required by accounting standards ".
Please cite your standards that apply to all these cases.
Have you worked in finance on numerical financial software?
>you round to a fixed point (e.g. whole cents) and that's it, that's the final truth
Having done numerical stuff, including finance for decades, you simply write the entire codebase in floating point, being sure to do proper analysis that things handle ranges correctly.
Then, and only for output, do you snap to desired observable precision. Never ever even once do you round something to make it look pretty, then jam it back into calculations.
It doesn't matter if the interest is calculated for consumers, intrabank, interbank, fed to bank, loans, mortgages, taxes - the interest rates and interest periods and interest day basis and all kinds of details may vary, and of course accounting standards vary between countries, but the core principles are the same that it all eventually comes up to some amount of money owed to the counterparty - measured in whole cents or perhaps whole dollars or roubles or whatever, but never an arbitrary-precision float. You can't get to a final compounded result "only for output" because when interest compounds (for example, monthly) then at every such point you do have actual "intermediate output" which materializes into a customer-visible change of balance from which the next period's interest is then calculated, and that intermediate output gets rounded because that (unlike estimates or models) is a specific balance owed and it is denominated in a currency with fixed, limited precision. And so after many such steps, the total actual compound interest - i.e. the actual dollars and cents paid by (or to) the counterparty - is slightly different from what the common modeling approach gets if e.g., as you state, it does rounding only for the final output. The difference is tiny, so there is no problem for modeling to ignore it, but actual financial systems (i.e. tracking facts of money owed, not doing estimates and models for decision support) do have to come down to a rounded fixed-precision number owed for every contract at the end of every day.
var t = obj.x // fixed -> fp
for (<n times>) {
t += 0.1
}
obj.x = t // adds exactly n/10
The correct answer is to use floating point and to understand it and numerical software before doing it
Yes, but this also has associated costs and risks. Unless you’re pressed against some wall, it’s more safe to offload that to a runtime. Humans are way too unreliable when it comes to understanding numerical software.
This is why ad-hoc methods that people feel are ok based on untrained or not-carefully-studied analysis should not write production numerical code.
Just use floating point everywhere, and only output things snapped to whatever resolution you want (and even that is tricky). Otherwise all those fixed to float to fixed to float going on in your code are going to add all sorts of numerical problems - each loses information.
In case you did not miss it: I’ve worked with and supported financial systems which do exactly that for a very long time without any micro-numerical issues^. Otoh, floating-point is a constant source of microbugs, unless all your developers are Knuth-level pros who are also versed in consulting and have no monday mornings or deadlines. It doesn’t matter if an error is O(N < 1e6) or O(1) when an underwater comparison to a limit fails and control flow triggers randomly.
^ The last [few] cent problem is usually handled explicitly, either naively (last=rest) or in Bresenham-like way (rarely, when it matters) and is easily catched in an accounting balance when left unhandled
I've been down this argument with other HN commenters some time back and explicitly demonstrated that it fails when you do it this way. It's not worth chasing down all the details again.
The short answer is it will fail, and in unexpected places. The only correct answer when doing this to is do the numerical analysis completely and correctly. This half-assed "it rounds the error away" is completely insufficient (and wrong).
The problem with letting such error slop around in code is that someone will take your code and use it to aggregate 1m loans, then your 25 bits of safety just became real money. Then someone will leverage that routine and add more problems.
When you build the lowest pieces so sloppily, it quickly contaminates the whole system. Make each piece as numerically solid as possible, otherwise you will get bitten.
If you have not proven your algorithm correct using numerical analysis for this stuff, it is not correct. End of story.
>but repeating multiplication doesn’t appear in finance naturally
Yes it does - compound interest if you need periods and tables.
And we're in agreement - floating point, not fixed point, is how to do financial calculations. I'm amazed how many people on HN want to argue that fixed point works when it's easy to demonstrate it fails in terrible ways and is significantly more error prone than simply using doubles (or double- or quad- doubles when needed).
Yes it does - compound interest if you need periods and tables.
Only if you don’t round to fixed before capitalizing. But when you don’t, numerically less savvy investors (99.9% of people) would just ask to fix it and stop being so smart. They want deterministic output for any particular end of period.
I see you’re coming from academic side, but real world doesn’t work like that. Nobody’s going to take our algorithms and shove 2^(>20) records of sums greater than $100M into them.
Worth noting that this is a risk whenever there is rounding, if this chain of logic happens with cents as ints, it is still wrong:
1 / 2 => 0
0 * 2 => 0
1 == 0 (a + b) >= c
A1-B1-C1 >= 0
All the reporting software I have seen in banks use decimals for adding numbers.
If you are adding small numbers together, those errors are negligible and get rounded out in the result (you usually can't pay an amount with more than two decimals). It's only a problem if somehow you are doing some calculations that need to be exact on amount large enough that the float rounding starts affecting your pennies.
Financial reporting has materiality thresholds, no one cares about pennies if the size of a balance sheet is trillons, the materiality will likely be in millions, not the least because the numbers will be shown in millions in the financial report and the numbers won't be additive because of rounding) and for a BI tool a number with 12 digits is unreadable and too much information to be useful.
If you are doing pricing, also no one really cares about pennies on a 1 million payment.
> PS. If you design software that works with money amounts, always use fixed point decimals. Don't use floats, it's just wrong!
Well, it depends. If all you do is add and substract numbers, ok, and that's what they typically do. If you need to do any other calculation (and most financial software do), this will bite you as percentages and ratios will be rounded aggressively.
Not very suitable for complex scientific calculations of course, but perfect for web development which is more likely to deal with money than scientific calculations.
PS. If you design software that works with money amounts, always use fixed point decimals. Don't use floats, it's just wrong!