Re: [lmi] Spreadsheet tools for validation

[Greg Chicares]

On 2010-01-27 16:13Z, Greg Chicares wrote:
>   http://savannah.nongnu.org/files/?group=lmi
[...]
> 'validate_commfns.xls', derived from the former, provides the
> "results imported from a spreadsheet" referred to on line 614 here:
>   
> http://svn.savannah.gnu.org/viewvc/lmi/trunk/commutation_functions_test.cpp?annotate=4766&root=lmi&pathrev=4766

I'll update that file, adding a column of reserves calculated with
gnumeric; and upload a new 'validate_commfns.gnumeric' file that I
used to produce those reserves. The latter file is gnumeric-specific:
it improves accuracy by using the transformation
  (1+i)^n - 1 <-> expm1(log1p(i) * n)
which requires functions that gnumeric provides:
  http://projects.gnome.org/gnumeric/doc/gnumeric-EXPM1.shtml
  http://projects.gnome.org/gnumeric/doc/gnumeric-LN1P.shtml
but that don't seem to be available in the other spreadsheet programs
I have. Apparently 'excel 2010' adds expm1() but (bizarrely enough)
not its natural companion log1p()--see section 2.3.5 here:
  blogs.msdn.com/excel/attachment/9905140.ashx
and the OOo developers are certainly aware of both functions:
  http://sc.openoffice.org/servlets/ReadMsg?listName=dev&msgNo=3371
  http://qa.openoffice.org/issues/show_bug.cgi?id=91870
but neither seems to be available in OOO310m19.

I'll follow up here with a discussion of discrepancies between lmi and
these spreadsheets. My preliminary finding is that the worst relative
error in net reserves is 2.5e-13 with the naive interest formula, but
1.2e-14 with expm1() and log1p().