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 <jsv...@yahoo.com> > To: POI Users List <user@poi.apache.org> > 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 <jsv...@yahoo.com> > To: POI Users List <user@poi.apache.org> > 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 <edward...@gmail.com> > To: POI Users List <user@poi.apache.org> > 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 <ye...@dinom.ru> 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<ye...@dinom.ru> 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<nick.bu...@alfresco.com> >>>>> 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: user-unsubscr...@poi.apache.org >>>>>> For additional commands, e-mail: user-h...@poi.apache.org >>>>>> >>>>>> >>>>>> >>>>>> --------------------------------------------------------------------- >>>> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org >>>> For additional commands, e-mail: user-h...@poi.apache.org >>>> >>>> >>>> >> >> --------------------------------------------------------------------- >> To unsubscribe, e-mail: user-unsubscr...@poi.apache.org >> For additional commands, e-mail: user-h...@poi.apache.org >> >> > > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-unsubscr...@poi.apache.org > For additional commands, e-mail: user-h...@poi.apache.org > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-unsubscr...@poi.apache.org > For additional commands, e-mail: user-h...@poi.apache.org > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: user-unsubscr...@poi.apache.org > For additional commands, e-mail: user-h...@poi.apache.org > Marcel May ConSol* Software GmbH Franziskanerstr. 38 81669 München Tel: +49 (0)89-45841-155 Fax: +49-(0)89-45841-111 marcel....@consol.de http://www.consol.de
--------------------------------------------------------------------- To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org