How to system-design paying a payment with multiple currencies

Question

I am developing an accounting app where users can have a product with currency A. The value of the product can be escrowed using a cheque payment with currency B and this cheque payment can be paid in cash with currency C. Whenever a cheque/cash payment is created for the product, paid_amount in case of cash and escrow_amount in case of a cheque of this product should be incremented with this value. Whenever the cheque payment is paid through a cash payment, paid_amount of the payment and the product should be incremented, and the escrow_amount of the product should be decremented with the payment value. A "CHECK" constraint is added to the tables where paid_amount >= 0 && <= amount AND escrow_amount >= 0 && <= amount The following is the relation between both tables.

I am facing a problem when trying to pay/escrow amounts with currency conversions.

Suppose we have a product with amount of "4000.00" and currency A. A cheque payment is created with currency B that should pay the full amount of the product ("4000.00" A). The app will ask for the amount to be paid in currency A and apply the conversion based on the input conversion rate and save the result in payment amount. ie. "4000.00" A ≈ "1066.67" B, so a payment with amount "1066.67" B will be created and escrow_amount of the product will be incremented by "4000.00". Now a cash payment is created to pay the full amount of the cheque. The app will ask for the amount to be paid in currency B. ie. "1066.67" B ≈ "996.89" C, so a payment with amount "996.89" C will be created. Now the cheque payment paid_amount column will be incremented by "1066.67" which is the input value. Now the paid_amount column of the product should be incremented. Using the following calculation 1066.67 * 3.75 ≈ 4000.01. This will throw an error because the maximum allowed value is "4000.00".

A = 3.75 B
B = 1.07 C

I am asking how I can solve this issue even with a proper way of implementing the calculations or with how client input is prompted.

Answer 1

First, you can create a stored function that will be responsible to convert currency X to currency Y.

Second, choose a currency for your calculations and store everything using your reference currency and always pass the origin currency, the final currency and the amount (in original currency) to your calculations.

Third, make sure you have your limitations being referred to and computed in your reference currency.

In short, you should have a single currency consistently applied in your calculations and translate every input into that currency in the space of your calculations and then translate it back to the one the user is interested about for the purpose of representation.

Answer 2

You should keep more decimals stored in database and use them for all calculations. When you are prompting the client round the amounts to two decimals.
Every calculation that you use cut of all the decimals after the second one and that is the reason why your backward checking doesn't match (lost decimals degrade precision).
Table below shows three versions of keeping more decimals in the database:

Option 1: amounts 6 decimals, rates 2 decimals (bank exchange rates are usualy at 6 decimals)
Option 2: amounts 2 decimals, rates 6 decimals
Option 3: ( the best one ) amounts and rates 6 decimals

OPTION  ORIG_CURR  ORIG_AMOUNT   CHECK_CURR   CHECK_CURR_RATE  CHECK_CURR_AMOUNT   (   TEST_REVERSE_B2A)  CASH_CURR  CASH_CURR_RATE  CASH_CURR_AMOUNT
------  ---------  -----------   ----------   ---------------  -----------------   (-------------------)  ---------  --------------  ----------------
1       A                 4000   B                       3.75        1066.666667   (      4000.00000125)  C                    1.07        996.884736
2       A                 4000   B                   3.749988            1066.67   (      3999.99969996)  C                1.069998            996.89
3       A                 4000   B                   3.749988       1.066,670080   (   3999.99999995904)  C                1.069998        996.889789

Note:
This way (option 3) you would keep precision through all calculations. When showing amounts to the client or realy do transactions round the amounts to 2 decimals. Personaly, I prefer prompting exchange rates with all 6 decimals - always.