0

I have a rounding problem regards to money calculation and its precision.

I am using Java Money (Moneta) libaray for all my calculations.

I store BigDecimal values in database using NUMERIC(19,4)

Problem

Given I have shopping cart in total amount EUR 323.99. I want to offer paying by instalments for this cart for 3 months without interest rate.

When doing 323.99 / 3 you will get Money representation of EUR 108.00 (default rounding HALF_EVEN) and BigDecimal representation of 107.9966...667

Monthly payment is stored as 107.9967 numeric value inside database for all 3 equal payments.

On Invoice I would display EUR 108.00 monthly payment for customer.

When I recieve customer payment of EUR 108.00, I am trying to allocate this transaction amount to the outstanding payment, which is Money with internal number value of 107.9967. On my query side I would want to calculate and display the remaining principal balance for this order, but with these numbers I am unable to produce accurate fractional part as I am not allowed to do any manual rounding here or else I would lose precision.

For example if I would do following calculations over 3 payments:

1) 323.99 - 107.9967 = 215.9933
2) 215.9933 - 107.9967 = 107.9966
3) 107.9966 - 107.9967 = -0.0001

I am left with Money value of EUR 0.00 with internal BigDecimal value -0.0001

Same would happen when calculating another way aswell. Final result would be positive 0.0033 for example which is represented as EUR 0.00, but when when it is accumulated, then second time it would be 0.0066 which gets already rounded to EUR 0.01, as if customer would have gained 1 cent.

Question

Are there any suggestions how such calculations should be performed or truncated or excplicitly rounded?

How should I handle this kind of value? In reality I shouldn't care about any values past 2 decimal points when it is 0.00, but in a long run customer transaction log sum would start to accumulate cents out of these calculations. Is it OK to someway strip all values that come after 0.00 and leave it completely as real 0?

Vaelyr
  • 2,841
  • 2
  • 21
  • 34
  • Generally speaking, there's loads of libraries for dealing with currency values. How do they work? They don't use floating points, its usually something simple like storing two different variables like `dollar_bal` and `cents_bal` – Skarlett Apr 25 '21 at 08:52
  • @Janez Kuhar 2 decimals are only present on the initial input value which gets divided by integer. For any further calculations it always uses at least 4 – Vaelyr Apr 25 '21 at 09:06
  • @Skarlett as I mentioned, I am already using a library. Internally it uses BigDecimal for storing values through string constructor. It never operates on floating points. – Vaelyr Apr 25 '21 at 09:07
  • 2
    Why do the monthly installments need to be identical? Why not charge 108.00 twice and then (323.99 - (2 * 108.00)) on the last installment? – Scratte Apr 25 '21 at 09:08
  • @Scratte they don't need to be, what I am currently doing is just simple division of total with number of months which is integer. The representation format of the Money is used for the amount to be paid which is a result of HALF_EVEN rounding by default. Maybe this is wrong then? – Vaelyr Apr 25 '21 at 09:12
  • 2
    The way I see it is that it's really a problem with not being able to divide 1 into three equal parts when you only have a fixed set of decimal points. Which in the real world, we do :) There's no rounding method that will help you. So you can either choose to let those rounding cents accumulate in the accounting books, or deal with them when billing the customer. If they are presented with "Two payments of 108.00 and one of 107.99" and agree to that, then you've solved your accounting problem. You'll need to handle that in your data/program though. – Scratte Apr 25 '21 at 09:19
  • On a side note: For something like this, you don't need any fancy calculations. Just treat your cents as integers, so 323.99 will be an integer of value 32399. The initial calculation can be made using a float division and a rounding to get an integer value of 10800 cents. – Scratte Apr 25 '21 at 09:27
  • Thanks for the advice for changing the way instalments are divided, I think this would solve all my issues. We don't have a business case for accumulating rounding cents, so better division to closest equal parts seems good enough. – Vaelyr Apr 25 '21 at 11:49

0 Answers0