Problems with rounding

I am having issues with how decimals are rounded… I have investment products where fee charges are calculated as ‘account value * 0.01 / 4’ for some products, and breakpoints that may equal value * .008 / 4.
The issues is when these values are retrieved from the database they may equal 0.00999 or 0.00812… when a client has million dollar plus accounts these small differences in rates make large differences in fee charges.

I have tried ceil, multiplying by 1000, ceil and round, dividing by 1000 and doing the fee calc, but the results are still wrong. I can’t have client fees being wrong.

How do I get a decimal limited to 0.08 or 0.085 if needed?

Never use doubles in accounting / financial software. This has been conventional wisdom for more than 40 years. Use Int64 values with a fixed number of decimals. Use integers for all internal calcualtions and then divide out the number of decimals for display only.

3 Likes

Or use the Currency type?

As long as he doesn’t need more than 4 digits, currency would be ideal. (Assuming the issues with currency have been resolved.)

1 Like

Is Dec64 a solution to this problem? Just being curious.

https://www.crockford.com/dec64.html

There is unfortunately no implementation in Xojo yet as I know.

1 Like

database field should be decimal,numeric,currency,money
in query may use cast(value * 0.01 / 4.0 as decimal(10,3))
useful type in xojo is currency

What Database? What kind of column? If such column is a floating point kind of column, as double, you may have small amount differences.

Most DBMS have proper decimal (numeric) fields to handle such kind of values with fixed decimals. That’s not the case of simple engines as SQLite (Numbers are Integers or Doubles). In Xojo, internally, we handle those values as Currency (fixed 4 decimals) but all intermediate calculations are done as doubles, so be careful anyway.

MariaDB. I have tried multiple different column types…

I may be suffering some language limitation here.

You by any change are expecting some kind of rounding limited to some perfect divisors? Fractions fitting some perfect steps or “resolutions”?

x * 0.001 / 4 = 0.0025x resolution, x * 0.008 / 4 = 0,0020x resolution

Please explain with examples the values entering in x and the expected n returned for n=f(x)

That is exactly what I want… but I get x*0.001/4=0.0025169686 type of response.


Var test As Double = 1234.0025169686

Var cur As Currency = test  // cur holds just 1234.0025

At MySQL, define fields as something like my_value DECIMAL(20,4)

Thanks everyone for the assistance… I used Tim’s solution of making everything an integer first.