I have a problem that I think may not have an answer other then completely rethinking the application, but hopefully you guys can prove me wrong!
I have an application that uses an Exchange Rate to back calculate a Base value from a Currency value to four decimal places. The calculation is pretty simple, and passes all the relevant unit tests giving correct results each time:
public static decimal GetBaseValue(decimal currencyAmount, RateOperator rateOperator,
double exchangeRate)
{
if (exchangeRate <= 0)
{
throw new ArgumentException(ErrorType.
ExchangeRateLessThanOrEqualToZero.ErrorInfo().Description);
}
decimal baseValue = 0;
if (currencyAmount != 0)
{
switch (rateOperator)
{
case RateOperator.Divide:
baseValue = Math.Round(currencyAmount * Convert.ToDecimal(exchangeRate),
4, MidpointRounding.AwayFromZero);
break;
case RateOperator.Multiply:
baseValue = Math.Round(currencyAmount / Convert.ToDecimal(exchangeRate),
4, MidpointRounding.AwayFromZero);
break;
default:
throw new ArgumentOutOfRangeException(nameof(rateOperator));
}
}
return baseValue;
}
I also have the equivalent to calculate currency from base, not shown to avoid confusing the issue and in any case pretty much identical code apart from parameter names and the reversal of the math operators in the switch statement.
My problem comes when I need to apply this process to a transaction which has multiple lines. The rule is that the total of the debits must equal the total of the credits, but some numbers are out by a fraction of a penny. This is because we are totalling the result of two or more individually converted numbers against a single conversion, and I believe that the accumulated rounding causes the error.
Let us assume the following:
Now let us review an example transaction:
Line # Debit Credit
1 $1.20
2 $1.00
3 $0.20
This passes the test, as total credits match total debits.
When we convert (divide each dollar value by 1.454), we see the problem:
Line # Debit Credit
1 £0.8253
2 £0.6878
3 £0.1376
========================
Total £0.8254 £0.8253
========================
This fails and breaks the rule, I believe as a result of the two sets of rounding in the debit column and only one set on the credit column. Yet, I need to be able to calculate backwards and forwards with accuracy, and I need to make sure that the transaction balances in Currency and in Base across all the lines.
So to my question: how best to address this problem? Anyone experienced something similar, and if so do you mind sharing how you resolved it?
EDIT - The Solution I Used
Following the answer from Charles that pointed me absolutely in the right direction I am posting my working method for the benefit of anyone else who might face a similar issue. Whilst understanding that this specific code may not be suitable for a direct copy and paste solution, I hope that the comments and the procedure followed will be of help:
private static void SetBaseValues(Transaction transaction)
{
// Get the initial currency totals
decimal currencyDebitRunningTotal = transaction.CurrencyDebitTotal;
decimal currencyCreditRunningTotal = transaction.CurrencyCreditTotal;
// Only one conversion, but we do one per column
// Note that the values should be the same anyway
// or the transaction would be invalid
decimal baseDebitRunningTotal =
Functions.GetBaseValue(currencyDebitRunningTotal,
transaction.MasterLine.RateOperator,
transaction.MasterLine.ExchangeRate);
decimal baseCreditRunningTotal =
Functions.GetBaseValue(currencyCreditRunningTotal,
transaction.MasterLine.RateOperator,
transaction.MasterLine.ExchangeRate);
// Create a list of transaction lines that belong to this transaction
List<TransactionLineBase> list = new List<TransactionLineBase>
{ transaction.MasterLine };
list.AddRange(transaction.TransactionLines);
// If there is no tax line, don't add a null entry
// as that would cause conversion failure
if (transaction.TaxLine != null)
{
list.Add(transaction.TaxLine);
}
// Sort the list ascending by value
var workingList = list.OrderBy(
x => x.CurrencyCreditAmount ?? 0 + x.CurrencyDebitAmount ?? 0).ToList();
// Iterate the lines excluding any entries where Credit and Debit
// values are both null (this is possible on some rows on
// some transactions types e.g. Reconciliations
foreach (var line in workingList.Where(
line => line.CurrencyCreditAmount != null ||
line.CurrencyDebitAmount != null))
{
if (transaction.CanConvertCurrency)
{
SetBaseValues(line);
}
else
{
var isDebitLine = line.CurrencyCreditAmount == null;
if (isDebitLine)
{
if (line.CurrencyDebitAmount != 0)
{
line.BaseDebitAmount =
line.CurrencyDebitAmount ?? 0 /
currencyDebitRunningTotal * baseDebitRunningTotal;
currencyDebitRunningTotal -=
line.CurrencyDebitAmount ?? 0;
baseDebitRunningTotal -= line.BaseDebitAmount ?? 0;
}
}
else
{
if (line.CurrencyCreditAmount != 0)
{
line.BaseCreditAmount =
line.CurrencyCreditAmount ?? 0/
currencyCreditRunningTotal*baseCreditRunningTotal;
currencyCreditRunningTotal -= line.CurrencyCreditAmount ?? 0;
baseCreditRunningTotal -= line.BaseCreditAmount ?? 0;
}
}
}
}
}
This does rather depend on the situation, but one option for this is to only convert the totals and then allocate this proportionately to each of the parts using a reducing balance method. This ensures that the sum of the parts will always equal the total exactly.
Your debit and credit columns both add up to $1.20, so you convert this at your rate giving you £0.8253.
You then proportionately allocate this to your debit amounts from the smallest up to the largest. The theory behind the sorting is that you're less likely to care about the extra/missing pennies of a larger number.
So you start with the totals of $1.20 and £0.6878 and then calculate the proportion of your converted balance that applies to your smallest dollar amount:
$0.20 / $1.20 * 0.8253 = £0.1376
You then deduct the amounts from your totals (this is the 'reducing balance' part):
$1.20 - $0.20 = $1.00
£0.8253 - £0.1376 = £0.6877
And then calculate the next largest (as you only have 1 more amount in this example, this is trivial):
$1.00 / $1.00 * £0.6877 = £0.6877
So this gives you:
Line # Debit Credit
1 £0.8253
2 £0.6877
3 £0.1376
========================
Total £0.8253 £0.8253
========================