Created attachment 126049 [details] test file open attached file ctrl+shift+F9 - recalculation see the cell B2 - #VALUE! formula =ODDFYIELD(D1,E1,F1,G1,H1,I1,J1,K1,L1) works in excel, but in Calc B2=#VALUE!
*** Bug 100769 has been marked as a duplicate of this bug. ***
All calls to ODDFYIELD -and ODDFPRICE- will return an error. These functions have been disabled at 26 September 2001, because of 'faulty algorithm'. 1. It seems that these functions have not been used since then, given the absence of bug reports. 2. Revitalising these functions may mean that an Add-in function (compatible with Excel) and an ODFF1.2 function will be needed. I have not studied possible differences in definitions (yet). 3. I cannot find what is supposed to be faulty with the algorithms. @Eike: do you remember anything about these functions and have a suggestion with regard to fixing?
I don't have details about what or how faulty the algorithm was, and the bug mentioned in the original source code unfortunately was in the StarDivision/Sun internal bug tracker and not public. We could only reimplement from scratch. My guess is that once a correct algorithm is found then it would not be necessary to have separate functions for the Add-In and ODFF purposes. ODDFPRICE maths are given in https://support.office.com/en-US/article/ODDFPRICE-function-D7D664A8-34DF-4233-8D2B-922BCF6A69E1 For ODDFYIELD see https://support.office.com/en-US/article/ODDFYIELD-function-66BC8B7B-6501-4C93-9CE3-2FD16220FE37 that claims to use Newton interval search on ODDFPRICE function results.
I will add new ODFF functions ODDFYIELD and ODDFPRICE. Don't know yet if the current Add-In functions are best renamed (ODDFYIELD_EXCEL2003, ODDFPRICE_EXCEL2003), removed (and loosing the xls-interoperabiltity) or other. I will study the mathematics of these financial functions first, in ODFF1.2, MS support and in the mathematical/financial 'world'.
The documentation on these functions is sparse and ambiguous, the functions are implemented as Add-In functions for interoperability with Excel, Excel has known issues with these functions, these functions have been deactivated in 2004 and nobody has complained about it yet, so I work on it with a low priority.
I can't get it to produce the same results as Excel produces, see comment#5 for reasons. Also, the odd first period calculations differ between countries/regions. The calculation Excel uses is useful only in the USA. I suggest to remove these 2 functions from Calc. No user filed a bug since the code was removed in 2001. Making the functions fully compatible with Excel means that we have to produced documented incorrect results in some cases; I don't think we want this.
> I suggest to remove these 2 functions from Calc. > No user filed a bug since the code was removed in 2001. > Making the functions fully compatible with Excel means that we have to > produced documented incorrect results in some cases; I don't think we want > this. @Raal, do you agree with this solution ?
Yes, of course. Thank you Winfried for the analyze.