some more financial funcs, not all fully working

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

2 files changed, 313 insertions, 6 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
index aeb59c1..4fe59ec 100644
--- a/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFinancialFunctions.java
+++ b/src/main/java/com/healthmarketscience/jackcess/impl/expr/DefaultFinancialFunctions.java
@@ -58,10 +58,10 @@ public class DefaultFinancialFunctions
         pmtType = params[4].getAsLongInt();
       }
 
-      double result = calculateLoanPayments(rate, pmt, pv, pmtType);
+      double result = calculateLoanPaymentPeriods(rate, pmt, pv, pmtType);
 
       if(fv != 0d) {
-        result += calculateAnnuityPayments(rate, pmt, fv, pmtType);
+        result += calculateAnnuityPaymentPeriods(rate, pmt, fv, pmtType);
       }
 
       return BuiltinOperators.toValue(result);
@@ -86,7 +86,7 @@ public class DefaultFinancialFunctions
       }
 
       if(pv != 0d) {
-        nper -= calculateLoanPayments(rate, pmt, pv, pmtType);
+        nper -= calculateLoanPaymentPeriods(rate, pmt, pv, pmtType);
       }
 
       double result = calculateFutureValue(rate, nper, pmt, pmtType);
@@ -113,7 +113,7 @@ public class DefaultFinancialFunctions
       }
 
       if(fv != 0d) {
-        nper -= calculateAnnuityPayments(rate, pmt, fv, pmtType);
+        nper -= calculateAnnuityPaymentPeriods(rate, pmt, fv, pmtType);
       }
 
       double result = calculatePresentValue(rate, nper, pmt, pmtType);
@@ -122,7 +122,174 @@ public class DefaultFinancialFunctions
     }
   });
 
-  private static double calculateLoanPayments(
+  public static final Function PMT = registerFunc(new FuncVar("Pmt", 3, 5) {
+    @Override
+    protected Value evalVar(EvalContext ctx, Value[] params) {
+      double rate = params[0].getAsDouble(); 
+      double nper = 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 = calculateLoanPayment(rate, nper, pv, pmtType);
+
+      if(fv != 0d) {
+        result += calculateAnnuityPayment(rate, nper, fv, pmtType);
+      }
+
+      return BuiltinOperators.toValue(result);
+    }
+  });
+
+  // FIXME not working for all param combos
+  // public static final Function IPMT = registerFunc(new FuncVar("IPmt", 4, 6) {
+  //   @Override
+  //   protected Value evalVar(EvalContext ctx, Value[] params) {
+  //     double rate = params[0].getAsDouble(); 
+  //     double per = params[1].getAsDouble(); 
+  //     double nper = params[2].getAsDouble(); 
+  //     double pv = params[3].getAsDouble(); 
+
+  //     double fv = 0d;
+  //     if(params.length > 4) {
+  //       fv = params[4].getAsDouble();
+  //     }
+
+  //     int pmtType = PMT_END_MNTH;
+  //     if(params.length > 5) {
+  //       pmtType = params[5].getAsLongInt();
+  //     }
+
+  //     double pmt = calculateLoanPayment(rate, nper, pv, pmtType);
+
+  //     if(fv != 0d) {
+  //       pmt += calculateAnnuityPayment(rate, nper, fv, pmtType);
+  //     }
+
+  //     double result = calculateInterestPayment(pmt, rate, per, pv, pmtType);
+
+  //     return BuiltinOperators.toValue(result);
+  //   }
+  // });
+
+  // FIXME untested
+  // public static final Function PPMT = registerFunc(new FuncVar("PPmt", 4, 6) {
+  //   @Override
+  //   protected Value evalVar(EvalContext ctx, Value[] params) {
+  //     double rate = params[0].getAsDouble(); 
+  //     double per = params[1].getAsDouble(); 
+  //     double nper = params[2].getAsDouble(); 
+  //     double pv = params[3].getAsDouble(); 
+
+  //     double fv = 0d;
+  //     if(params.length > 4) {
+  //       fv = params[4].getAsDouble();
+  //     }
+
+  //     int pmtType = PMT_END_MNTH;
+  //     if(params.length > 5) {
+  //       pmtType = params[5].getAsLongInt();
+  //     }
+
+  //     double pmt = calculateLoanPayment(rate, nper, pv, pmtType);
+
+  //     if(fv != 0d) {
+  //       pmt += calculateAnnuityPayment(rate, nper, fv, pmtType);
+  //     }
+
+  //     double result = pmt - calculateInterestPayment(pmt, rate, per, pv,
+  //                                                    pmtType);
+
+  //     return BuiltinOperators.toValue(result);
+  //   }
+  // });
+
+  // FIXME, doesn't work for partial days
+  // public static final Function DDB = registerFunc(new FuncVar("DDB", 4, 5) {
+  //   @Override
+  //   protected Value evalVar(EvalContext ctx, Value[] params) {
+  //     double cost = params[0].getAsDouble(); 
+  //     double salvage = params[1].getAsDouble(); 
+  //     double life = params[2].getAsDouble(); 
+  //     double period = params[3].getAsDouble(); 
+
+  //     double factor = 2d;
+  //     if(params.length > 4) {
+  //       factor = params[4].getAsDouble();
+  //     }
+
+  //     double result = 0d;
+
+  //     // fractional value always rounds up to one year
+  //     if(period < 1d) {
+  //       period = 1d;
+  //     }
+
+  //     // FIXME? apply partial period _first_
+  //     // double partPeriod = period % 1d;
+  //     // if(partPeriod != 0d) {
+  //     //   result = calculateDoubleDecliningBalance(
+  //     //       cost, salvage, life, factor) * partPeriod;
+  //     //   period -= partPeriod;
+  //     //   cost -= result;
+  //     // }
+  //     double prevResult = 0d;
+  //     while(period > 0d) {
+  //       prevResult = result;
+  //       double remPeriod = Math.min(period, 1d);
+  //       result = calculateDoubleDecliningBalance(
+  //           cost, salvage, life, factor);
+  //       if(remPeriod < 1d) {
+  //         result = (prevResult + result) / 2d;
+  //       }
+  //       period -= 1d;
+  //       cost -= result;
+  //     }
+
+  //     return BuiltinOperators.toValue(result);
+  //   }
+  // });
+
+  // FIXME, untested
+  public static final Function SLN = registerFunc(new FuncVar("SLN", 3, 3) {
+    @Override
+    protected Value evalVar(EvalContext ctx, Value[] params) {
+      double cost = params[0].getAsDouble(); 
+      double salvage = params[1].getAsDouble(); 
+      double life = params[2].getAsDouble(); 
+
+      double result = calculateStraightLineDepreciation(cost, salvage, life);
+
+      return BuiltinOperators.toValue(result);
+    }
+  });
+
+  // FIXME, untested
+  public static final Function SYD = registerFunc(new FuncVar("SYD", 4, 4) {
+    @Override
+    protected Value evalVar(EvalContext ctx, Value[] params) {
+      double cost = params[0].getAsDouble(); 
+      double salvage = params[1].getAsDouble(); 
+      double life = params[2].getAsDouble(); 
+      double period = params[3].getAsDouble(); 
+
+      double result = calculateSumOfYearsDepreciation(
+          cost, salvage, life, period);
+
+      return BuiltinOperators.toValue(result);
+    }
+  });
+
+
+  private static double calculateLoanPaymentPeriods(
       double rate, double pmt, double pv, int pmtType) {
 
     // https://brownmath.com/bsci/loan.htm
@@ -145,7 +312,7 @@ public class DefaultFinancialFunctions
     return result;
   }
 
-  private static double calculateAnnuityPayments(
+  private static double calculateAnnuityPaymentPeriods(
       double rate, double pmt, double fv, int pmtType) {
 
     // https://brownmath.com/bsci/loan.htm
@@ -197,5 +364,77 @@ public class DefaultFinancialFunctions
     return result;
   }
 
+  private static double calculateLoanPayment(
+      double rate, double nper, double pv, int pmtType) {
+
+    double result = -(rate * pv) / (1d - Math.pow((1d + rate), -nper));
+
+    if(pmtType == PMT_BEG_MNTH) {
+      result /= (1d + rate);
+    }
+
+    return result;
+  } 
+
+  private static double calculateAnnuityPayment(
+      double rate, double nper, double fv, int pmtType) {
+
+    double result = -(fv * rate) / (Math.pow((1d + rate), nper) - 1d);
+
+    if(pmtType == PMT_BEG_MNTH) {
+      result /= (1d + rate);
+    }
+
+    return result;
+  } 
+
+  private static double calculateInterestPayment(
+      double pmt, double rate, double per, double pv, int pmtType) {
+
+    // http://www.tvmcalcs.com/index.php/calculators/apps/excel_loan_amortization
+    // http://financeformulas.net/Remaining_Balance_Formula.html
+
+    double pvPer = per;
+    double fvPer = per;
+    if(pmtType == PMT_END_MNTH) {
+      pvPer -= 1d;
+      fvPer -= 1d;
+    } else {
+      pvPer -= 2d;
+      fvPer -= 1d;
+    }
+
+    double remBalance = (pv * Math.pow((1d + rate), pvPer)) - 
+      // FIXME, always use pmtType of 0?
+      calculateFutureValue(rate, fvPer, pmt, PMT_END_MNTH);
+
+    double result = -(remBalance * rate);
+
+    return result;
+  }
+
+  private static double calculateDoubleDecliningBalance(
+      double cost, double salvage, double life, double factor) {
+
+    double result1 = cost * (factor/life);
+    double result2 = cost - salvage;
+
+    return Math.min(result1, result2);
+  }
+
+  private static double calculateStraightLineDepreciation(
+      double cost, double salvage, double life) {
+    return ((cost - salvage) / life);
+  }
+
+  private static double calculateSumOfYearsDepreciation(
+      double cost, double salvage, double life, double period) {
+
+    double sumOfYears = (period * (period + 1)) / 2d;
+    double result = ((cost - salvage) * ((life + 1 - period) / sumOfYears));
+
+    return result;
+  }
 
 }
+
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 30b60ad..0eec59b 100644
--- a/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
+++ b/src/test/java/com/healthmarketscience/jackcess/impl/expr/DefaultFunctionsTest.java
@@ -123,6 +123,74 @@ public class DefaultFunctionsTest extends TestCase
                  eval("=CStr(PV(0.12/12,60,-100,10000))"));
     assertEquals("-964.0372803489684", 
                  eval("=CStr(PV(0.12/12,60,-100,10000,1))"));
+
+    assertEquals("22.24444768490176", 
+                 eval("=CStr(Pmt(0.12/12,60,-1000))"));
+    assertEquals("22.024205628615604", 
+                 eval("=CStr(Pmt(0.12/12,60,-1000,0,1))"));
+    assertEquals("-100.20002916411586", 
+                 eval("=CStr(Pmt(0.12/12,60,-1000,10000))"));
+    assertEquals("-99.20794966744144", 
+                 eval("=CStr(Pmt(0.12/12,60,-1000,10000,1))"));
+    assertEquals("-122.44447684901762", 
+                 eval("=CStr(Pmt(0.12/12,60,0,10000))"));
+    assertEquals("-121.23215529605704", 
+                 eval("=CStr(Pmt(0.12/12,60,0,10000,1))"));
+
+    // FIXME not working for all param combos
+    // assertEquals("10.0", 
+    //              eval("=CStr(IPmt(0.12/12,1,60,-1000))"));
+    // assertEquals("5.904184782975672", 
+    //              eval("=CStr(IPmt(0.12/12,30,60,-1000))"));
+    // 0
+    // assertEquals("", 
+    //              eval("=CStr(IPmt(0.12/12,1,60,-1000,0,1))"));
+    // 5.84572750...
+    // assertEquals("5.845727507896704", 
+    //              eval("=CStr(IPmt(0.12/12,30,60,-1000,0,1))"));
+    // 0
+    // assertEquals("", 
+    //              eval("=CStr(IPmt(0.12/12,1,60,0,10000))"));
+    // 40.9581521702433
+    // assertEquals("40.95815217024329", 
+    //              eval("=CStr(IPmt(0.12/12,30,60,0,10000))"));
+    // 0
+    // assertEquals("", 
+    //              eval("=CStr(IPmt(0.12/12,1,60,0,10000,1))"));
+    // 40.552625911132
+    // assertEquals("40.55262591113197", 
+    //              eval("=CStr(IPmt(0.12/12,30,60,0,10000,1))"));
+    // assertEquals("10.0", 
+    //              eval("=CStr(IPmt(0.12/12,1,60,-1000,10000))"));
+    // assertEquals("46.862336953218964", 
+    //              eval("=CStr(IPmt(0.12/12,30,60,-1000,10000))"));
+    // 0
+    // assertEquals("", 
+    //              eval("=CStr(IPmt(0.12/12,1,60,-1000,10000,1))"));
+    // 46.3983534190287
+    // assertEquals("46.39835341902867", 
+    //              eval("=CStr(IPmt(0.12/12,30,60,-1000,10000,1))"));
+
+    // FIXME, doesn't work for partial days
+    // assertEquals("1.3150684931506849", 
+    //              eval("=CStr(DDB(2400,300,10*365,1))"));
+    // assertEquals("40.0", 
+    //              eval("=CStr(DDB(2400,300,10*12,1))"));
+    // assertEquals("480.0", 
+    //              eval("=CStr(DDB(2400,300,10,1))"));
+    // assertEquals("22.122547200000042", 
+    //              eval("=CStr(DDB(2400,300,10,10))"));
+    // assertEquals("245.76", 
+    //              eval("=CStr(DDB(2400,300,10,4))"));
+    // assertEquals("307.20000000000005", 
+    //              eval("=CStr(DDB(2400,300,10,3))"));
+    // assertEquals("480.0", 
+    //              eval("=CStr(DDB(2400,300,10,0.1))"));
+    // 274.768033075174
+    // assertEquals("", 
+    //              eval("=CStr(DDB(2400,300,10,3.5))"));
+
+    
   }
 
 }