[openroad-users] high precision calcs

Paul White

unread,

May 13, 2010, 3:19:02 AM5/13/10

to openroa...@googlegroups.com

Hi all,

I’m writing a routine to calculate hire purchase interest rates, principle and interest schedules. The internal OpenROAD functions PMT() PPMT() and IPMT() agree with the Excel equivalents, however the IPER() function agrees to Excel RATE() only to 7 significant digits. I’ve checked the numbers against two separate iterative guessing methods and it indicates both OR and Excel are wrong. And, when trying to implement high precision decimal calculations, I’ve uncovered some gotchas in OpenROAD and Ingres.

If all this finance stuff gives you a headache like it does me, skip down to the high precision calcs below.

Here are some examples relating to a 1000 loan over 3 years at 12% interest.

pv=1000, nper=36, rate=.01

PMT() = monthly repayments

OR -33.21430981285120000000

Excel -33.21430981285120000

Formula see below

Calc -33.214309812851194685699334206815 Windows Calculator

OR2 -33.214309812851194685699334100 Using decimal(31,27)

PPMT() Principle in 13^th payment

OR -26.1584653543589000000000000

Excel -26.15846535435890000

Calc -26.158465354358927818593542265473

OR2 not yet implemented

IPMT() Interest in 13^th payment

OR -7.0558444584922700000000000

Excel -7.05584445849227000

Calc 7.055844458492266867105791941337

OR2 not yet implemented

Now calculate backwards the interest rate based on monthly payments of exactly 33.21000

IPER() / RATE() pv=1000, nper=36, pmt=33.21

OR 0.0099924802202880

Excel 0.00999248010912268000

Iterative methods

Excel1 0.00999248010911712000 iterative method in Excel

OR2 0.009992480109117187151450363560 using decimal(31,27)

Convert to Annual rate

OR 11.9909762643456000

Excel 11.9909761309472000

Excel1 11.9909761309405000

OR2 11.99097613094062458174043627000

These findings suggest the IPER() function is accurate only to 7 or 8 decimal places.

Working these annual interest rates forward again to calculate the payment amount.

(windows calc used to check)

OR 33.210000063711970779676746464784 Last place

Excel 33.210000000003147950411862636948 Close but no medal

Excel1 33.209999999999961515517145798825 Second place

OR2 33.209999999999999999999999987191 Winner

Techy stuff starts here---->

So why all the precision? The customer is comparing our performance to manual Excel spreadsheets. The results are stored in the database. We cant tell the customer that we agree to 7 decimal places:

OR 0.0099924802202880

Excel 0.00999248010912268000

And for a 10 million dollar loan, these types of calculations start losing whole dollars.

Floating point calculations are always an estimate. For a good background on floating point look here:

http://ariel.its.unimelb.edu.au/~yuan/Ingres/us_14577.html

The core of the PMT() calculation is here:

J

M = P x ------------------------

1 - ( 1 + J ) ^ -N

(Thanks to http://www.hughchou.org/calc/formula.html)

In OpenROAD, Ingres and Excel the exponentiation operator ignores the precision of the base and returns only floating point..

1.01 ** -36

Ingres select 0.6989249496272590000000000

OR 0.6989249496272590000000000

Excel 0.6989249496272590000

Wincalc 0.69892494962725897400242918260869

Seeing as the formula above needs only an integer exponent and the base (monthly interest) will always between 0 and about 1.3, I wrote a custom exponentiation function with decimal(31,30)

Declare

my_exp = procedure returning decimal(31,30);

Call

pmt = pv * ( monthly_interest / (ONE - my_exp(v=(ONE + monthly_interest), x= -num_periods)));

procedure my_exp(v = decimal(31,30), x = integer ) =

declare

ONE = decimal(31,30);

i = integer not null;

x1 = integer;

v1 = decimal(31,30);

enddeclare

{

x1 = abs(x);

v1 = 1.0;

ONE = 1.0;

FOR i = 1 TO x1 DO

v1 = decimal(v1 * v,31,30);

ENDFOR;

IF x < 0 THEN

v1 = decimal(ONE / v1,31,30);

ENDIF;

return v1;

}

My first attempts failed using decimal(31,25). I think it is an OpenROAD bug but I’ve worked around it by using decimal(31,30).

Bug? Decimal(31,25) * Decimal(31,25) loses precision

on click btn1 =

declare

v = decimal(31,25);

v1 = decimal(31,25);

v2 = decimal(31,25);

v3 = decimal(31,25);

v4 = decimal(31,25);

v5 = decimal(31,25);

v6 = decimal(31,25);

v7 = decimal(31,25);

v8 = decimal(31,25);

v9 = decimal(31,25);

v10 = decimal(31,25);

{

v = 1.01;

v1 = 1.2081089504435315091605500;

v2 = 1.01 * 1.2081089504435315091605500;

v3 = v * 1.2081089504435315091605500;

v4 = 1.01 * v1;

v5 = v * v1;

v6 = decimal(1.01 * 1.2081089504435315091605500,31,25);

v7 = decimal(v * 1.2081089504435315091605500,31,25);

v8 = decimal(1.01 * v1,31,25);

v9 = decimal(v * v1,31,25);

v10 = decimal(v,31,25) * decimal(v1,31,25);

MESSAGE varchar(v1) + HC_NEWLINE +

varchar(v2) + HC_NEWLINE +

varchar(v3) + HC_NEWLINE +

varchar(v4) + HC_NEWLINE +

varchar(v5) + HC_NEWLINE +

varchar(v6) + HC_NEWLINE +

varchar(v7) + HC_NEWLINE +

varchar(v8) + HC_NEWLINE +

varchar(v9) + HC_NEWLINE +

varchar(v10) ;

}

1.2081089504435315091605500

1.2201900399479668242521555

1.2201900399479668242521550 <- wrong

1.2201900399479668242521555

1.2201900399479668242000000 <- wrong

1.2201900399479668242521555

1.2201900399479668242521550 <- wrong

1.2201900399479668242521555

1.2201900399479668242000000 <- wrong

But if I change to decimal(31,30) I get the correct answers

1.208108950443531509160550000000

1.220190039947966824252155500000

And I change 1.01 to 1.123456789012345678901234567890 I think I get good numbers.

1.208108950443531509160550000000

1.357258202242364960281718933580

Wincalc gives

1.2081089504435315091605500 x

1.123456789012345678901234567890 =

1.3572582022423649602817189335859

I suspect I can’t trust the last digits when working at this precision.

Bug 2? Also I found Ingres had problem with simple decimal calculations where the values are retrieved from the database.

Compare the results above with ingres SQL

* select decimal(1.01 * 1.2081089504435315091605500,31,25)\g

┌─────────────────────────────────┐

│col1 │

├─────────────────────────────────┤

│ 1.2201900399479668242521555│

└─────────────────────────────────┘

* select decimal(1.01 * 1.2081089504435315091605500,31,30)\g

┌─────────────────────────────────┐

│col1 │

├─────────────────────────────────┤

│ 1.220190039947966824252155500000│

└─────────────────────────────────┘

* drop table pw;

* create table pw (v decimal(31,27), v1 decimal(31,27));

* commit;

* insert into pw values(1.01, 1.01);

* select * from pw;

* select v * v1 from pw;\g

┌─────────────────────────────────┬─────────────────────────────────┐

│v │v1 │

├─────────────────────────────────┼─────────────────────────────────┤

│ 1.010000000000000000000000000│ 1.010000000000000000000000000│

└─────────────────────────────────┴─────────────────────────────────┘

(1 row)

E_AD1127 Packed Decimal overflow detected in query.

(Thu May 13 16:54:03 2010)

* create table pw (v decimal(31,25), v1 decimal(31,25))\g

* commit\g

* insert into pw values(1.01, 1.2081089504435315091605500)\g

* select * from pw\g

┌─────────────────────────────────┬─────────────────────────────────┐

│v │v1 │

├─────────────────────────────────┼─────────────────────────────────┤

│ 1.0100000000000000000000000│ 1.2081089504435315091605500│

└─────────────────────────────────┴─────────────────────────────────┘

* select v * v1 from pw;\g

E_AD1127 Packed Decimal overflow detected in query.

* drop table pw\g

* commit\g

* create table pw (v decimal(31,30), v1 decimal(31,30))\g

* commit\g

* insert into pw values(1.01, 1.2081089504435315091605500)\g

* select * from pw\g

┌─────────────────────────────────┬─────────────────────────────────┐

│v │v1 │

├─────────────────────────────────┼─────────────────────────────────┤

│ 1.010000000000000000000000000000│ 1.208108950443531509160550000000│

└─────────────────────────────────┴─────────────────────────────────┘

* select v * v1 from pw;\g

E_AD1127 Packed Decimal overflow detected in query.

*

* drop table pw;

* create table pw (v decimal(5,3), v1 decimal(31,27));

* commit;

* insert into pw values(1.01, 1.2081089504435315091605500);

* select * from pw;

* select v * v1 from pw;\g

┌───────┬─────────────────────────────────┐

│v │v1 │

├───────┼─────────────────────────────────┤

│ 1.010│ 1.208108950443531509160550000│

└───────┴─────────────────────────────────┘

┌─────────────────────────────────┐

│col1 │

├─────────────────────────────────┤

│ 1.220190039947966824252155500000│

└─────────────────────────────────┘

I’ll shoot this one over to the ingres forums

Regards

Paul

--
You received this message because you are subscribed to the Google Groups "OpenROAD Users Mailing List" group.
To post to this group, send email to openroa...@googlegroups.com.
To unsubscribe from this group, send email to openroad-user...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/openroad-users?hl=en.

Durwin Wright

unread,

May 13, 2010, 3:33:39 AM5/13/10

to openroa...@googlegroups.com

I have not looked at this very closely but the version of OpenROAD and Ingres will be important to any resolution. I general OpenROAD and Ingres rely on the underlying Ingres Abstract Data Facility (ADF) to evaluate expressions. Different releases of OpenROAD use different releases of Ingres ADF.

Joseph C. Kronk

unread,

May 13, 2010, 11:16:37 AM5/13/10

to openroa...@googlegroups.com

Paul,

This stuff also gives me a headache. Open issues for the bugs that you need fixed to help you accomplish. With both Ingres and OpenROAD where ever the bugs may exist. We can then start the process to get them resolved.

Thanks,

-Joe

From: openroa...@googlegroups.com [mailto:openroa...@googlegroups.com] On Behalf Of Paul White
Sent: Thursday, May 13, 2010 12:19 AM
To: openroa...@googlegroups.com
Subject: [openroad-users] high precision calcs

Hi all,

Paul Andrews

unread,

May 13, 2010, 11:33:37 AM5/13/10

to openroa...@googlegroups.com

From: openroa...@googlegroups.com [mailto:openroa...@googlegroups.com] On Behalf Of Paul White

Sent: Thursday, May 13, 2010 12:19 AM
To: openroa...@googlegroups.com
Subject: [openroad-users] high precision calcs

Hi all,

I’m writing a routine to calculate hire purchase interest rates, principle and interest schedules. The internal OpenROAD functions PMT() PPMT() and IPMT() agree with the Excel equivalents, however the IPER() function agrees to Excel RATE() only to 7 significant digits. I’ve checked the numbers against two separate iterative guessing methods and it indicates both OR and Excel are wrong. And, when trying to implement high precision decimal calculations, I’ve uncovered some gotchas in OpenROAD and Ingres.

If all this finance stuff gives you a headache like it does me, skip down to the high precision calcs below.

snip

Techy stuff starts here---->

So why all the precision? The customer is comparing our performance to manual Excel spreadsheets. The results are stored in the database. We cant tell the customer that we agree to 7 decimal places:

OR 0.0099924802202880

Excel 0.00999248010912268000

And for a 10 million dollar loan, these types of calculations start losing whole dollars.

This reminds me of a problem where we had ingres reports disagreeing with the visual output from Windows4GL (yes, a while ago). It certainly did matter because the discrepancy was large enough to show on very ordinary calculations.

I deal mostly with user interface issues these days and while it's irritating to have these differences, to all intents there is little value in pursuing what is a tiny practical error that in almost all cases has no effect on business. The interest payments on the loan calculations wouldn't result in a difference of payment. A millionaire mogul would express disbelief that money was spent in trying to eradicate a discrepancy of one dollar on a million dollar loan. The irony is that if excel was wrong, you'd have to justify why ingres was right!

Personally I would re-educate the client on the use of internal software calulations and the practical effect it has on their business - none. At the end of the day, it's always possible to work around these these things if need be with dedicated routines - no I am not volunteering.

I can almost see a Dilbert cartoon where a one dollar discrepancy in a million dollar deal costs ingres thousands of dollars to rectify and will only benefit the end customer to the value of a dollar.

It's not a dig at you Paul, just an ironic view of the world. I'm sure you can work with this as is.

Paul

Reply all

Reply to author

Forward