implement some financial functions

git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/branches/exprs@1129 f203690c-595d-4dc9-a70b-905162fa7fd2

4 files changed, 239 insertions, 0 deletions

diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFinancialFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFinancialFunctions.java
new file mode 100644
index 0000000..aeb59c1
--- /dev/null
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFinancialFunctions.java
@@ -0,0 +1,201 @@
+/*
+Copyright (c) 2017 James Ahlborn
+
+Licensed under the Apache License, Version 2.0 (the "License");
+you may not use this file except in compliance with the License.
+You may obtain a copy of the License at
+
+    http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+*/
+
+package com.healthmarketscience.jackcess.impl.expr;
+
+
+import com.healthmarketscience.jackcess.expr.EvalContext;
+import com.healthmarketscience.jackcess.expr.Function;
+import com.healthmarketscience.jackcess.expr.Value;
+import static com.healthmarketscience.jackcess.impl.expr.DefaultFunctions.*;
+
+/**
+ *
+ * @author James Ahlborn
+ */
+public class DefaultFinancialFunctions 
+{
+  /** 0 - payment end of month (default) */
+  private static final int PMT_END_MNTH = 0;
+  /** 1 - payment start of month */
+  private static final int PMT_BEG_MNTH = 1;
+
+
+  private DefaultFinancialFunctions() {}
+
+  static void init() {
+    // dummy method to ensure this class is loaded
+  }
+  
+
+  public static final Function NPER = registerFunc(new FuncVar("NPer", 3, 5) {
+    @Override
+    protected Value evalVar(EvalContext ctx, Value[] params) {
+      double rate = params[0].getAsDouble(); 
+      double pmt = params[1].getAsDouble(); 
+      double pv = params[2].getAsDouble(); 
+
+      double fv = 0d;
+      if(params.length > 3) {
+        fv = params[3].getAsDouble();
+      }
+
+      int pmtType = PMT_END_MNTH;
+      if(params.length > 4) {
+        pmtType = params[4].getAsLongInt();
+      }
+
+      double result = calculateLoanPayments(rate, pmt, pv, pmtType);
+
+      if(fv != 0d) {
+        result += calculateAnnuityPayments(rate, pmt, fv, pmtType);
+      }
+
+      return BuiltinOperators.toValue(result);
+    }
+  });
+
+  public static final Function FV = registerFunc(new FuncVar("FV", 3, 5) {
+    @Override
+    protected Value evalVar(EvalContext ctx, Value[] params) {
+      double rate = params[0].getAsDouble(); 
+      double nper = params[1].getAsDouble(); 
+      double pmt = params[2].getAsDouble(); 
+
+      double pv = 0d;
+      if(params.length > 3) {
+        pv = params[3].getAsDouble();
+      }
+
+      int pmtType = PMT_END_MNTH;
+      if(params.length > 4) {
+        pmtType = params[4].getAsLongInt();
+      }
+
+      if(pv != 0d) {
+        nper -= calculateLoanPayments(rate, pmt, pv, pmtType);
+      }
+
+      double result = calculateFutureValue(rate, nper, pmt, pmtType);
+
+      return BuiltinOperators.toValue(result);
+    }
+  });
+
+  public static final Function PV = registerFunc(new FuncVar("PV", 3, 5) {
+    @Override
+    protected Value evalVar(EvalContext ctx, Value[] params) {
+      double rate = params[0].getAsDouble(); 
+      double nper = params[1].getAsDouble(); 
+      double pmt = params[2].getAsDouble(); 
+
+      double fv = 0d;
+      if(params.length > 3) {
+        fv = params[3].getAsDouble();
+      }
+
+      int pmtType = PMT_END_MNTH;
+      if(params.length > 4) {
+        pmtType = params[4].getAsLongInt();
+      }
+
+      if(fv != 0d) {
+        nper -= calculateAnnuityPayments(rate, pmt, fv, pmtType);
+      }
+
+      double result = calculatePresentValue(rate, nper, pmt, pmtType);
+
+      return BuiltinOperators.toValue(result);
+    }
+  });
+
+  private static double calculateLoanPayments(
+      double rate, double pmt, double pv, int pmtType) {
+
+    // https://brownmath.com/bsci/loan.htm
+    // http://financeformulas.net/Number-of-Periods-of-Annuity-from-Present-Value.html
+
+    if(pmtType == PMT_BEG_MNTH) {
+      pv += pmt;
+    }
+
+    double v1 = Math.log(1d + (rate * pv / pmt));
+      
+    double v2 = Math.log(1d + rate);
+      
+    double result = -v1 / v2;
+
+    if(pmtType == PMT_BEG_MNTH) {
+      result += 1d;
+    }
+
+    return result;
+  }
+
+  private static double calculateAnnuityPayments(
+      double rate, double pmt, double fv, int pmtType) {
+
+    // https://brownmath.com/bsci/loan.htm
+    // http://financeformulas.net/Number-of-Periods-of-Annuity-from-Future-Value.html
+    // https://accountingexplained.com/capital/tvm/fv-annuity
+
+    if(pmtType == PMT_BEG_MNTH) {
+      fv *= (1d + rate);
+    }
+
+    double v1 = Math.log(1d - (rate * fv / pmt));
+
+    double v2 = Math.log(1d + rate);
+      
+    double result = v1 / v2;
+
+    if(pmtType == PMT_BEG_MNTH) {
+      result -= 1d;
+    }
+
+    return result;
+  }
+
+  private static double calculateFutureValue(
+      double rate, double nper, double pmt, int pmtType) {
+
+    double result = -pmt * ((Math.pow((1d + rate), nper) - 1d) / rate);
+
+    if(pmtType == PMT_BEG_MNTH) {
+      result *= (1d + rate);
+    }
+
+    return result;
+  }
+
+  private static double calculatePresentValue(
+      double rate, double nper, double pmt, int pmtType) {
+
+    if(pmtType == PMT_BEG_MNTH) {
+      nper -= 1d;
+    }
+
+    double result = -pmt * ((1d - Math.pow((1d + rate), -nper)) / rate);
+
+    if(pmtType == PMT_BEG_MNTH) {
+      result -= pmt;
+    }
+
+    return result;
+  }
+
+
+}
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java
index 34f0bf6..77ddb74 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctions.java
@@ -42,6 +42,7 @@ public class DefaultFunctions
     DefaultTextFunctions.init();
     DefaultNumberFunctions.init();
     DefaultDateFunctions.init();
+    DefaultFinancialFunctions.init();
   }
   
   private DefaultFunctions() {}
diff --git a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java
index 6f71797..0195210 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultNumberFunctions.java
@@ -180,6 +180,7 @@ public class DefaultNumberFunctions
     }
   });
 
+
   // public static final Function Val = registerFunc(new Func1("Val") {
   //   @Override
   //   protected Value eval1(EvalContext ctx, Value param1) {
diff --git a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
index ed1aa23..30b60ad 100644
--- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
+++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
@@ -87,6 +87,42 @@ public class DefaultFunctionsTest extends TestCase
     assertEquals("ah", eval("=Right(\"blah\", 2)"));
     assertEquals("", eval("=Right(\"blah\", 0)"));
     assertEquals("blah", eval("=Right(\"blah\", 17)"));
+
+  }
+
+
+  public void testFinancialFuncs() throws Exception
+  {
+    assertEquals("-9.578594039813165", 
+                 eval("=CStr(NPer(0.12/12,-100,-1000))"));
+    assertEquals("-9.488095005505832", 
+                 eval("=CStr(NPer(0.12/12,-100,-1000,0,1))"));
+    assertEquals("60.08212285376166", 
+                 eval("=CStr(NPer(0.12/12,-100,-1000,10000))"));
+    assertEquals("59.673865674294554", 
+                 eval("=CStr(NPer(0.12/12,-100,-1000,10000,1))"));
+    assertEquals("69.66071689357483", 
+                 eval("=CStr(NPer(0.12/12,-100,0,10000))"));
+    assertEquals("69.16196067980039", 
+                 eval("=CStr(NPer(0.12/12,-100,0,10000,1))"));
+
+    assertEquals("8166.966985640913", 
+                 eval("=CStr(FV(0.12/12,60,-100))"));
+    assertEquals("8248.636655497321", 
+                 eval("=CStr(FV(0.12/12,60,-100,0,1))"));
+    assertEquals("6350.270287076823", 
+                 eval("=CStr(FV(0.12/12,60,-100,1000))"));
+    assertEquals("6431.939956933231", 
+                 eval("=CStr(FV(0.12/12,60,-100,1000,1))"));
+
+    assertEquals("4495.503840622403", 
+                 eval("=CStr(PV(0.12/12,60,-100))"));
+    assertEquals("4540.458879028627", 
+                 eval("=CStr(PV(0.12/12,60,-100,0,1))"));
+    assertEquals("-1008.992318755193", 
+                 eval("=CStr(PV(0.12/12,60,-100,10000))"));
+    assertEquals("-964.0372803489684", 
+                 eval("=CStr(PV(0.12/12,60,-100,10000,1))"));
   }
 
 }