Financial operation [Hibernate]

[GG]

I'm wondering about mapping type for ammount money in database defined as
numeric(10,2).
I have two option:

1. Mapping to BigDecimal

2. Maping to Double
In this case I'd like move access type to field and give
Long getter and setter in which is doing appropriate conversion
from/to Double (with multiply/ divide 100):

//return salary in cent
public Long getSalary() {
return Util.getAsLong(amount);
}

public void setSalary(Long ammount) {
this.amount = Util.getAsDouble(amount);
}
In this case all financial operation are doing on Long (cents).


I know that using BigDecimal is comfortable solution buy not most
efficient and fast.
What do you think about solution witch Double ?
What are you using for financial operations ?

Thanks in advance
GG

 

[Mark Space]

1. Mapping to BigDecimal

Use this.

2. Maping to Double

Never double. Doubles are not accurate. Ten cents is 0.10 and that is
an infinite repeating number in binary. You will get very strange
effects when using this.

Never never use any sort of double when dealing with money.

Long is not too bad, but sometimes you get into trouble. Gasoline
prices in US has a tenth of a cent digit. Example: $3.999 per gallon.
Longs won't handle this, even if they represent cents.

Use BigDecimal.

 

[Mark Thornton]

Use this.


Never double. Doubles are not accurate. Ten cents is 0.10 and that is
an infinite repeating number in binary. You will get very strange
effects when using this.

Never never use any sort of double when dealing with money.

I do it all the time and it works just fine. As the values I deal with
are estimated costs based on the expected time to complete an operation
(and other factors), the question of the rounding of the final result is
irrelevant. Doubles can also represent integers exactly up to 2^53, so
you can work in cents in the same way as you might with long. It will
work with your petrol (gasoline) prices too (with far more accuracy than
any known gas pump).

If you are doing accounts or tax returns and the like, then sure use
BigDecimal. Otherwise double can be a very appropriate choice.

Mark Thornton

 

[Mark Space]

I do it all the time and it works just fine. As the values I deal with
are estimated costs based on the expected time to complete an operation

Good points. Thanks for the over view.

 

[Lew]

Good points. Thanks for the over view.

Much easier and safer in the long run not to use double.

..10 is not exactly representable in double. If you're going to scale the
number up 100 (or 1000), then you're doing what you'd do with long.

Just don't use double for money. Money is supposed to be exact.

 

[Owen Jacobson]

I do it all the time and it works just fine. As the values I deal with
are estimated costs based on the expected time to complete an operation
(and other factors), the question of the rounding of the final result is
irrelevant. Doubles can also represent integers exactly up to 2^53, so
you can work in cents in the same way as you might with long. It will
work with your petrol (gasoline) prices too (with far more accuracy than
any known gas pump).

If you are doing accounts or tax returns and the like, then sure use
BigDecimal. Otherwise double can be a very appropriate choice.

The key realization here is that an estimate already has some
(possibly unknown) degree of error, and the additional error
introduced by using floating-point arithmetic is not going to matter
(much). Doubles are fine for estimates and predictions and other
error-ful calculations. On the other hand, tax calculations,
accounting, and other purely financial processes often have specific
precision requirements and rounding rules that must be obeyed; the
only way to reliably get that right is to use a decimal type.

Cheers,
Owen

 

[Mark Thornton]

Much easier and safer in the long run not to use double.

In my work using BigDecimal would mean an application that didn't
complete in the time available.

Just don't use double for money. Money is supposed to be exact.

Only in accounting for money already spent. Everything else is a
prediction and thus intrinsically inexact. Even the past sometimes has
to be estimated --- I don't suppose anyone has an EXACT figure for the
cost to the US of the Iraq conflict to date.

In my opinion, far too many programmers have an inadequate understanding
of floating point, and perhaps in consequence are too scared to use it
where appropriate.

Mark Thornton

 

[George Neuner]

I'm wondering about mapping type for ammount money in database defined as
numeric(10,2).
I have two option:

1. Mapping to BigDecimal

2. Maping to Double
In this case I'd like move access type to field and give
Long getter and setter in which is doing appropriate conversion
from/to Double (with multiply/ divide 100):

//return salary in cent
public Long getSalary() {
return Util.getAsLong(amount);
}

public void setSalary(Long ammount) {
this.amount = Util.getAsDouble(amount);
}
In this case all financial operation are doing on Long (cents).


I know that using BigDecimal is comfortable solution buy not most
efficient and fast.
What do you think about solution witch Double ?
What are you using for financial operations ?

Thanks in advance
GG

Coming in a little late but ...

None of this matters if you simply scale your numbers so that they are
*always* integers. Money is traded in units 1/10,000 of the base - so
for dollars the unit is 1/100 of a cent. Scaling the dollar amount by
10,000 allows room for proper rounding.

George

 

[Patricia Shanahan]

George Neuner wrote:
....

None of this matters if you simply scale your numbers so that they are
*always* integers. Money is traded in units 1/10,000 of the base - so
for dollars the unit is 1/100 of a cent. Scaling the dollar amount by
10,000 allows room for proper rounding.

....

That is what BigDecimal does, but you can keep the unit as dollars.

Patricia

 

[Lew]

George Neuner wrote:
....
....

That is what BigDecimal does, but you can keep the unit as dollars.

With the added advantage that BigDecimal directly supports things like
compound interest calculations that would be much harder to handle with longs.

 

[Mark Space]

None of this matters if you simply scale your numbers so that they are
*always* integers. Money is traded in units 1/10,000 of the base - so
for dollars the unit is 1/100 of a cent. Scaling the dollar amount by
10,000 allows room for proper rounding.

This is a really interesting factoid, but I tend to agree with Lew. Big
Decimal is much easier on the programmer and much less error prone.

If you had some operation that takes too long as with Big Decimals, just
convert everything to longs or doubles, run the big operation, then
convert everything back.

Much easier to do this in some tightly scoped routine than try to deal
with every possibility of input format, mathematical calculation, and
whatnot as an int or long. Makes my brain hurt to just think about it.
You'd end up duplicating something like 90% of Big Decimal, and then
you have to debug all your shiny new code. Ouch.

 

[Arne Vajhøj]

I'm wondering about mapping type for ammount money in database defined as
numeric(10,2).
I have two option:

1. Mapping to BigDecimal

2. Maping to Double

I know that using BigDecimal is comfortable solution buy not most
efficient and fast.
What do you think about solution witch Double ?
What are you using for financial operations ?

I hope that nobody uses double for financial operations.

The speed difference between BigDecimal and double should be
negliable in a typical financial app espcially if the data
are fetched from the database.

Arne

 

[George Neuner]

This is a really interesting factoid, but I tend to agree with Lew. Big
Decimal is much easier on the programmer and much less error prone.

If you had some operation that takes too long as with Big Decimals, just
convert everything to longs or doubles, run the big operation, then
convert everything back.

Much easier to do this in some tightly scoped routine than try to deal
with every possibility of input format, mathematical calculation, and
whatnot as an int or long. Makes my brain hurt to just think about it.
You'd end up duplicating something like 90% of Big Decimal, and then
you have to debug all your shiny new code. Ouch.

Oh, I absolutely agree that BigDecimal is the way to go - but only
because it is already there, not because working with scaled numbers
is in any way difficult. In fact, I think it's a skill every
programmer should have - arbitrary precision numbers are not available
everywhere even in Java (they are optional in Java ME).

George

 

[Martin Gregorie]

I hope that nobody uses double for financial operations.

Agreed.

NEVER use a float or double for financial values because arithmetic
results are not always exact. In the early days people writing in
MS-Basic used doubles for monetary values (MS-BASIC only had 16 bit
integers, floats [9 significant digits] and doubles [17 significant
digits]) and, like spreadsheets, caused periodic financial havoc.
Spreadsheets also tend to use doubles.

Take note that COBOL, which was designed for business and financial use,
works in the equivalent of fixed point BigDecimal - and there's a good
reason for that design feature. Some dialects can do floating point but
its almost never used for financial calculations. OK, maybe for currency
conversions but nothing else I can think of, and even currency
conversions are typically defined as fixed point calculations with the
number of decimal places, the order of operations and the rounding rules
legally specified.

It is almost universal to hold monetary values in the smallest currency
unit, so dollar amounts are held in cents and Sterling is held in pence,
with the decimal point only appearing in values intended for human input
or output.

 

[Patricia Shanahan]

Agreed.

NEVER use a float or double for financial values because arithmetic
results are not always exact.

I would change "financial values" to "accounting". Some financial
calculations are inherently imprecise, dealing with estimates and
projections. Those calculations are often easier done in double.

For example, I've just been through a round of financial planning that
involved Monte Carlo methods and estimates of future interest rates etc.
Floating point rounding error is probably one of the least significant
sources of error in that sort of calculation.

On the other hand, accounting usually does require exactness.

Patricia

 

[Eric Sosman]

[...]
NEVER use a float or double for financial values [...]

Never say NEVER. No special reason to pick on Martin's advice
other than that it's the camel's backbreaker, as others have made
similar statements. To all those who think floating-point has no
place in financial matters, I propose the following scenario for a
simple mortgage loan:

Loan principal: 300000 splonders
Repayment schedule: Monthly, in 359 equal payments plus
one final payment no more than twice as large (i.e.,
"substantially equal" monthly payments)
Nominal interest rate: 6.125% annually
Origination fee: 4500 splonders (1.5 "points")

Question: What is the "Annual Percentage Rate" or
APR for this loan?

I will concede that it is *possible* to solve this problem
without floating-point arithmetic, but I maintain it would be
foolish to eschew the convenience.

 

[Lew]

[...]
NEVER use a float or double for financial values [...]

Never say NEVER. No special reason to pick on Martin's advice
other than that it's the camel's backbreaker, as others have made
similar statements. To all those who think floating-point has no
place in financial matters, I propose the following scenario for a
simple mortgage loan:

Loan principal: 300000 splonders
Repayment schedule: Monthly, in 359 equal payments plus
one final payment no more than twice as large (i.e.,
"substantially equal" monthly payments)
Nominal interest rate: 6.125% annually
Origination fee: 4500 splonders (1.5 "points")

Question: What is the "Annual Percentage Rate" or
APR for this loan?

I will concede that it is *possible* to solve this problem
without floating-point arithmetic, but I maintain it would be
foolish to eschew the convenience.

You can use BigDecimal to do that.

 

[Martin Gregorie]

[...]
NEVER use a float or double for financial values [...]

Never say NEVER. No special reason to pick on Martin's advice
other than that it's the camel's backbreaker, as others have made
similar statements. To all those who think floating-point has no
place in financial matters, I propose the following scenario for a
simple mortgage loan:

Loan principal: 300000 splonders
Repayment schedule: Monthly, in 359 equal payments plus
one final payment no more than twice as large (i.e.,
"substantially equal" monthly payments)
Nominal interest rate: 6.125% annually
Origination fee: 4500 splonders (1.5 "points")

Question: What is the "Annual Percentage Rate" or
APR for this loan?

I will concede that it is *possible* to solve this problem
without floating-point arithmetic, but I maintain it would be
foolish to eschew the convenience.

The problem is that some bean-counter somewhere will get upset if values
in the millions or billions don't balance to the exact penny.

I take Patricia's point about projections (usually done with
spreadsheets, so using real numbers whether you like it or not) and the
same applies to the APR, i.e. projections and APR are never directly
converted to monetary values.

However, as other mortgage calculations DO tend to end up as payments
and/or debt, IMO its best to stick to fixed point calculations for them.
The same applies to saving and borrowing financial products too.

FWIW one of my favorite financial programming systems is the Sculptor
4GL. It provides the usual numeric variables (i1, i2, i4 [8, 16 and 32
bit signed integers], r4 and r8 [32 and 64 bit reals]) and adds two
financial types, m4 and m8 [signed 32 bit integer and 64 bit real]. Both
the latter are externally represented with the number of digits to the
right of the decimal point required by the local currency, so both are
convenient to use. Use of the m8 type comes with lots of warnings about
rounding errors while m4 is exact.

In Java programming I'd use only ints, longs and BigDecimal for monetary
values, but the actual one(s) chosen would be heavily influenced by the
database supporting the system and the east of mapping the Java
variables to the database's preferred monetary representation. I'd then
encapsulate the choice in a monetary value class together with all the
i/o and calculation methods.

 

[Eric Sosman]

Lew wrote On 11/26/07 09:26,:

[...]
NEVER use a float or double for financial values [...]

Never say NEVER. No special reason to pick on Martin's advice
other than that it's the camel's backbreaker, as others have made
similar statements. To all those who think floating-point has no
place in financial matters, I propose the following scenario for a
simple mortgage loan:

Loan principal: 300000 splonders
Repayment schedule: Monthly, in 359 equal payments plus
one final payment no more than twice as large (i.e.,
"substantially equal" monthly payments)
Nominal interest rate: 6.125% annually
Origination fee: 4500 splonders (1.5 "points")

Question: What is the "Annual Percentage Rate" or
APR for this loan?

I will concede that it is *possible* to solve this problem
without floating-point arithmetic, but I maintain it would be
foolish to eschew the convenience.

You can use BigDecimal to do that.

Yes, and you can use byte[], too, or even boolean[],
and it would still be foolish. If you think not, would
you care to exhibit FP-free code to solve the proposed
problem? For extra credit, explain why your FP-free
solution is better than an FP-ful one.

 

[Eric Sosman]

Martin Gregorie wrote On 11/26/07 12:03,:

[...]
NEVER use a float or double for financial values [...]

Never say NEVER. No special reason to pick on Martin's advice
other than that it's the camel's backbreaker, as others have made
similar statements. To all those who think floating-point has no
place in financial matters, I propose the following scenario for a
simple mortgage loan:

Loan principal: 300000 splonders
Repayment schedule: Monthly, in 359 equal payments plus
one final payment no more than twice as large (i.e.,
"substantially equal" monthly payments)
Nominal interest rate: 6.125% annually
Origination fee: 4500 splonders (1.5 "points")

Question: What is the "Annual Percentage Rate" or
APR for this loan?

I will concede that it is *possible* to solve this problem
without floating-point arithmetic, but I maintain it would be
foolish to eschew the convenience.

The problem is that some bean-counter somewhere will get upset if values
in the millions or billions don't balance to the exact penny.

I take Patricia's point about projections (usually done with
spreadsheets, so using real numbers whether you like it or not) and the
same applies to the APR, i.e. projections and APR are never directly
converted to monetary values.

Then let's ask another question about the same mortgage
loan, a question whose answer *is* monetary: How much is the
monthly payment? To get this answer you need to solve for
the term payment T in

T = P * (R - 1) / (1 - R^(-N))

.... for the principal P = 300000 splonders, the number of
monthly payments N = 360, and the monthly interest rate
R = 1 + 0.06125/12. If payment amounts are stated in
centisplonders (as in the USA), you'll need an accuracy of
one part in 30 million, or about 3E-8.

Now, *after* you use floating-point to calculate this
amount (I get 1822.8316+ splonders per month), you'll chop
or round to an amount in centisplonders, probably 1822.84.
At this point, you *should* switch to BigDecimal or long
or some other exact representation for calculating month-
by-month balances, the final month's payment, late fees,
document fees, fee management fees, and all such monetary
amounts, so the bean counters will be happy with how exact
everything is. But the original calculation of T is best
performed in floating-point.