I can confirm this is a bug - just had the same problem.
a) the NPV computation is wrong
- sum += ds[i] / Math.pow(rate + 1, i);
+ sum += ds[i] / Math.pow(rate + 1, i+1);
I think the computation should actually use the FinanceLib#npv method
(which is correct).
b) The impl does not support ranges
I got a patch fix a) and b), including a test case:
Jon, do you want to create the bug ticket? Then I'll add my patch for it.
Cheers,
Marcel
On Dec 7, 2010, at 10:48 PM, Jon Svede wrote:
> Some additional information:
>
> If I enumerate the values in the area in the formula, the evaluation works
> (=NPV(B5,B1,B2,B3,B4) ). However the result doesn't match what Excel has.
>
> Thanks,
>
> Jon
>
>
> ----- Original Message ----
> From: Jon Svede <[email protected]>
> To: POI Users List <[email protected]>
> Sent: Tue, December 7, 2010 9:44:40 AM
> Subject: NPV bug?
>
> I've attached a simple example of my issue but here is the description.
>
> I have a spreadsheet with values (not formulas) in B1:B4. In B5 there is the
> value 0.10.
>
>
> In B6 I have this formula:
>
> =NPV(B5,B1:B4)
>
> This seems to work fine in Excel.
>
> When I try to evaluate this B6 cell, I get the errorCode 15 that I've
> previously
>
> e-mailed about. This error isn't thrown overtly, the CellValue from the
> evaluator returns 0 and the errorCode is now set to a non-zero value.
>
>
> When I debug this, it's falling in the NPV.evaluate(ValueEval[] args, int
> srcRowIndex, int srcColumnIndex) method. This will eventually end up in the
> OperandResolver.chooseSingleElementFromAreaInternal(AreaEval ae, int
> srcCellRow,
>
> int srcCellCol) which has this if statement in it:
>
> if (ae.isColumn()) {
> if(ae.isRow()) {
> return ae.getRelativeValue(0, 0);
> }
> if(!ae.containsRow(srcCellRow)) {
> throw EvaluationException.invalidValue();
> }
> return ae.getAbsoluteValue(srcCellRow, ae.getFirstColumn());
> }
>
> My error is coming from the throw clause. Interestingly, if I comment that
> out,
>
> I get a better error message:
>
> java.lang.IllegalArgumentException: Specified row index (5) is outside the
> allowed range (0..3)
>
> So my question is, is this a bug or is this user error? Separating that out:
> is
> my usage of NPV user error on my part and is that throw statement a bug?
>
> This example of the NPV function is used heavily in the spreadsheet I am
> trying
> to use POI with (and I don't know Excel nearly well enough to have known
> about
> or how to use the NPV function). I've tried changing it so that the range of
> cells includes B5 but this doesn't change the behavior (meaning that guessing
> about what the proper usage of NPV isn't helping me).
>
> Thanks in advance for any advice or suggestions.
>
> Jon
>
>
>
>
> ----- Original Message ----
> From: Jon Svede <[email protected]>
> To: POI Users List <[email protected]>
> Sent: Tue, December 7, 2010 7:17:17 AM
> Subject: Re: Using FinanceLib
>
> Is this function, the FinanceLib.npv() method, the one used internally by POI
> when a cell in a spreadsheet specifies the NPV function?
>
> Thanks,
>
> Jon
>
>
>
>
>
> ----- Original Message ----
> From: Edward Ivanovic <[email protected]>
> To: POI Users List <[email protected]>
> Sent: Mon, December 6, 2010 4:24:58 PM
> Subject: Re: Using FinanceLib
>
> That's great. Thanks Yegor!
>
>
> On Mon, Dec 6, 2010 at 11:24 AM, Yegor Kozlov <[email protected]> wrote:
>
>> OK, I got the request. The visibility of the FinanceLib class will be
>> raised from default to public.
>>
>> Thanks for raising this topic.
>>
>> Regards,
>> Yegor
>>
>>
>> That's a shame. Why hide a perfectly good Java financial library?
>>>
>>> On Mon, Dec 6, 2010 at 3:03 AM, Yegor Kozlov<[email protected]> wrote:
>>>
>>> On 12/5/2010 8:42 PM, Edward Ivanovic wrote:
>>>>
>>>> That's interesting - it no longer comes up in the classes list on
>>>>> http://poi.apache.org/apidocs/index.html
>>>>> I wonder if the direct page link that you can find via a Google search
>>>>> is
>>>>> an
>>>>> orphaned page.
>>>>>
>>>>>
>>>>> I updated the online apidocs to reflect current POI trunk.
>>>>
>>>> Looking in the svn code repository, the FinanceLib class is no longer
>>>>
>>>>> defined as public:
>>>>> final class FinanceLib {...
>>>>>
>>>>>
>>>>>
>>>>> http://svn.apache.org/repos/asf/poi/trunk/src/java/org/apache/poi/ss/formula/functions/FinanceLib.java
>>>>>
>>>>>
>>>>> a
>>>>>
>>>>> I think this is why I can't access any methods out of it even though the
>>>>> methods themselves are declared as public.
>>>>>
>>>>>
>>>>> It was done intentionally. FinanceLib is intended for POI internal use
>>>> only. This class can be renamed / re-packaged in future versions of POI
>>>> or
>>>> access
>>>> to its methods can be changed from 'public' to 'default' or less.
>>>>
>>>>
>>>> Yegor
>>>>
>>>> As you can see, the javadoc you send me shows the previous FinanceLib
>>>>
>>>>> declared as a public class. Can it be reverted to public again?
>>>>>
>>>>> Thanks
>>>>>
>>>>> On Sun, Dec 5, 2010 at 5:01 AM, Nick Burch<[email protected]>
>>>>> wrote:
>>>>>
>>>>> Hi, I'd like to use the pv() function that used to be a static method
>>>>> in
>>>>>
>>>>>> the
>>>>>>> FinanceLib class. I just upgraded to POI 3.7 and it's no longer
>>>>>>> available
>>>>>>> (seems the entire FinanceLib class is no longer public).
>>>>>>>
>>>>>>> The methods on FinanceLib are all public static, so you ought to be
>>>>>>>
>>>>>> fine:
>>>>>>
>>>>>>
>>>>>>
>>>>>> http://poi.apache.org/apidocs/org/apache/poi/hssf/record/formula/functions/FinanceLib.html
>>>>>>
>>>>>>
>>>>>> l
>>>>>>
>>>>>> Note that in 3.8, the package will change to
>>>>>> org.apache.poi.ss.formula.functions for this class
>>>>>>
>>>>>> Nick
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>>>> To unsubscribe, e-mail: [email protected]
>>>>>> For additional commands, e-mail: [email protected]
>>>>>>
>>>>>>
>>>>>>
>>>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [email protected]
>>>> For additional commands, e-mail: [email protected]
>>>>
>>>>
>>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
Marcel May
ConSol* Software GmbH
Franziskanerstr. 38
81669 München
Tel: +49 (0)89-45841-155
Fax: +49-(0)89-45841-111
[email protected]
http://www.consol.de
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
