Rounding problems performing math with Decimal data types

We're seeing some rounding errors in a financial application. We had chosen
to use Decimal(38, 10) fields for both dollar amounts and allocation
percentages. However, when we multiply the numbers together we loose
significant precision, as in the following example:
DECLARE @.TNA_PERCENT AS DECIMAL(38, 10)
DECLARE @.ALLOC_PERCENT AS DECIMAL(38, 10)
DECLARE @.Result AS DECIMAL(38, 10)
SET @.TNA_PERCENT = .7363612218
SET @.ALLOC_PERCENT = .01
SET @.Result = @.TNA_PERCENT * @.ALLOC_PERCENT
SELECT @.TNA_PERCENT, @.ALLOC_PERCENT, @.Result
Returns:
.7363612218 .0100000000 .0073640000
Granted, the Decimal(38, 10) data type is overkill for the value .01, but it
was one example that came up. Why do we lose the precision during this
multiplication operation? Should we revise our data types, and if so, to
what? We will be dealing with large dollar amounts that need to be multiplie
d
accurately by calculated allocation percentages and stored.
Thanks,
Craig RandallCraig,
If you multiply a decimal 38 with another decimal 38, then the result
would be a decimal 76 (38+38). Since your result variable is restricted
to 38 digits, you loose precision.
If you want an exact result in a decimal(38,10), then the two
multiplication variables should have a combined total of at most 38.
Same for the precision. So if you multiply a decimal(19,5) with a
decimal(19,5), then this will fit in a decimal(38,10).
You will see that if you change the definition of @.TNA_PERCENT and
@.ALLOC_PERCENT to Decimal(19,10) that the result will be:
.7363612218 .0100000000 .0073636122
In this case, to get an exact result, you would need a precision of (at
least) 12 digits.
Gert-Jan
Crandaddy wrote:
> We're seeing some rounding errors in a financial application. We had chose
n
> to use Decimal(38, 10) fields for both dollar amounts and allocation
> percentages. However, when we multiply the numbers together we loose
> significant precision, as in the following example:
> DECLARE @.TNA_PERCENT AS DECIMAL(38, 10)
> DECLARE @.ALLOC_PERCENT AS DECIMAL(38, 10)
> DECLARE @.Result AS DECIMAL(38, 10)
> SET @.TNA_PERCENT = .7363612218
> SET @.ALLOC_PERCENT = .01
> SET @.Result = @.TNA_PERCENT * @.ALLOC_PERCENT
> SELECT @.TNA_PERCENT, @.ALLOC_PERCENT, @.Result
> Returns:
> .7363612218 .0100000000 .0073640000
> Granted, the Decimal(38, 10) data type is overkill for the value .01, but
it
> was one example that came up. Why do we lose the precision during this
> multiplication operation? Should we revise our data types, and if so, to
> what? We will be dealing with large dollar amounts that need to be multipl
ied
> accurately by calculated allocation percentages and stored.
> Thanks,
> Craig Randall