From 37c63d928d52930c642c774ed4c8c5a8a43d252b Mon Sep 17 00:00:00 2001
From: PJ Fanning <fanningpj@apache.org>
Date: Thu, 30 Sep 2021 09:32:02 +0000
Subject: [PATCH] add npv tests (including a commented out one that fails)

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1893747 13f79535-47bb-0310-9956-ffa450edef68
---
 .../ss/formula/functions/TestFinanceLib.java  | 46 +++++++++++++++++++
 1 file changed, 46 insertions(+)

diff --git a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFinanceLib.java b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFinanceLib.java
index 5f61c88da0..565e9ff0e8 100644
--- a/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFinanceLib.java
+++ b/poi/src/test/java/org/apache/poi/ss/formula/functions/TestFinanceLib.java
@@ -21,8 +21,17 @@
 package org.apache.poi.ss.formula.functions;
 
 
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.util.Utils;
 import org.junit.jupiter.api.Test;
 
+import java.io.IOException;
+
+import static org.apache.poi.ss.util.Utils.addRow;
+
 class TestFinanceLib extends BaseTestNumeric {
 
     @Test
@@ -110,6 +119,43 @@ class TestFinanceLib extends BaseTestNumeric {
         npv = FinanceLib.npv(r, v);
         x = 11342283.4233124;
         assertDouble("npv ", x, npv);
+
+        r = 0.08; v = new double[]{-1000, 500, 300, 800};
+        npv = FinanceLib.npv(r, v);
+        x = 328.9170387681793;
+        assertDouble("npv ", x, npv);
+
+        //https://support.microsoft.com/en-us/office/npv-function-8672cb67-2576-4d07-b67b-ac28acf2a568
+        r = 0.1; v = new double[]{-10000, 3000, 4200, 6800};
+        npv = FinanceLib.npv(r, v);
+        x = 1188.4434123352207;
+        assertDouble("npv ", x, npv);
+
+        r = 0.08; v = new double[]{-40000, 8000, 9200, 10000, 12000, 14500};
+        npv = FinanceLib.npv(r, v);
+        x = 1779.6866249373716;
+        assertDouble("npv ", x, npv);
+    }
+
+    @Test
+    void testMicrosoftNpvExample2() throws IOException {
+        //https://support.microsoft.com/en-us/office/npv-function-8672cb67-2576-4d07-b67b-ac28acf2a568
+        try (HSSFWorkbook wb = new HSSFWorkbook()) {
+            HSSFSheet sheet = wb.createSheet();
+            addRow(sheet, 0, "Data");
+            addRow(sheet, 1, 0.08);
+            addRow(sheet, 2, -40000);
+            addRow(sheet, 3, 8000);
+            addRow(sheet, 4, 9200);
+            addRow(sheet, 5, 10000);
+            addRow(sheet, 6, 12000);
+            addRow(sheet, 7, 14500);
+            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
+            HSSFCell cell = wb.getSheetAt(0).getRow(0).createCell(100);
+            Utils.assertDouble(fe, cell, "NPV(A2, A4:A8)+A3", 1992.061554932363);
+            //TODO this scenario does not work
+            //Utils.assertDouble(fe, cell, "NPV(A2, A4:A8, -9000)+A3", -3749.47);
+        }
     }
 
     @Test
-- 
2.39.5