View Javadoc
1   /*
2   Copyright (c) 2017 James Ahlborn
3   
4   Licensed under the Apache License, Version 2.0 (the "License");
5   you may not use this file except in compliance with the License.
6   You may obtain a copy of the License at
7   
8       http://www.apache.org/licenses/LICENSE-2.0
9   
10  Unless required by applicable law or agreed to in writing, software
11  distributed under the License is distributed on an "AS IS" BASIS,
12  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  See the License for the specific language governing permissions and
14  limitations under the License.
15  
16  NOTICE:
17  Many of the financial functions have been originally copied from the Apache
18  POI project (Apache Software Foundation) and the UCanAccess Project.  They
19  have been then modified and adapted so that they are integrated with Jackcess,
20  in a consistent manner.  The Apache POI and UCanAccess projects are licensed
21  under Apache License, Version 2.0 http://www.apache.org/licenses/LICENSE-2.0.
22  
23  */
24  
25  package com.healthmarketscience.jackcess.impl.expr;
26  
27  
28  import com.healthmarketscience.jackcess.expr.EvalContext;
29  import com.healthmarketscience.jackcess.expr.Function;
30  import com.healthmarketscience.jackcess.expr.Value;
31  import static com.healthmarketscience.jackcess.impl.expr.DefaultFunctions.*;
32  import static com.healthmarketscience.jackcess.impl.expr.FunctionSupport.*;
33  
34  /**
35   *
36   * @author James Ahlborn
37   */
38  public class DefaultFinancialFunctions
39  {
40    // Useful Sources:
41    // https://brownmath.com/bsci/loan.htm
42    // http://financeformulas.net/Number-of-Periods-of-Annuity-from-Future-Value.html
43    // https://accountingexplained.com/capital/tvm/fv-annuity
44    // http://www.tvmcalcs.com/index.php/calculators/apps/excel_loan_amortization
45  
46  
47    /** 0 - payment end of month (default) */
48    private static final int PMT_END_MNTH = 0;
49    /** 1 - payment start of month */
50    private static final int PMT_BEG_MNTH = 1;
51  
52    private static final int MAX_RATE_ITERATIONS = 20;
53    private static final double RATE_PRECISION = 0.0000001;// 1.0e-8
54  
55  
56    private DefaultFinancialFunctions() {}
57  
58    static void init() {
59      // dummy method to ensure this class is loaded
60    }
61  
62  
63    public static final Function NPER = registerFunc(new FuncVar("NPer", 3, 5) {
64      @Override
65      protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
66        double rate = params[0].getAsDouble(ctx);
67        double pmt = params[1].getAsDouble(ctx);
68        double pv = params[2].getAsDouble(ctx);
69        double fv = getFV(ctx, params, 3);
70        int pmtType = getPaymentType(ctx, params, 4);
71  
72        double result = calculateLoanPaymentPeriods(rate, pmt, pv, fv, pmtType);
73  
74        return ValueSupport.toValue(result);
75      }
76    });
77  
78    public static final Function FV = registerFunc(new FuncVar("FV", 3, 5) {
79      @Override
80      protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
81        double rate = params[0].getAsDouble(ctx);
82        double nper = params[1].getAsDouble(ctx);
83        double pmt = params[2].getAsDouble(ctx);
84        double pv = getOptionalDoubleParam(ctx, params, 3, 0d);
85        int pmtType = getPaymentType(ctx, params, 4);
86  
87        double result = calculateFutureValue(rate, nper, pmt, pv, pmtType);
88  
89        return ValueSupport.toValue(result);
90      }
91    });
92  
93    public static final Function PV = registerFunc(new FuncVar("PV", 3, 5) {
94      @Override
95      protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
96        double rate = params[0].getAsDouble(ctx);
97        double nper = params[1].getAsDouble(ctx);
98        double pmt = params[2].getAsDouble(ctx);
99        double fv = getFV(ctx, params, 3);
100       int pmtType = getPaymentType(ctx, params, 4);
101 
102       double result = calculatePresentValue(rate, nper, pmt, fv, pmtType);
103 
104       return ValueSupport.toValue(result);
105     }
106   });
107 
108   public static final Function PMT = registerFunc(new FuncVar("Pmt", 3, 5) {
109     @Override
110     protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
111       double rate = params[0].getAsDouble(ctx);
112       double nper = params[1].getAsDouble(ctx);
113       double pv = params[2].getAsDouble(ctx);
114       double fv = getFV(ctx, params, 3);
115       int pmtType = getPaymentType(ctx, params, 4);
116 
117       double result = calculateLoanPayment(rate, nper, pv, fv, pmtType);
118 
119       return ValueSupport.toValue(result);
120     }
121   });
122 
123   public static final Function IPMT = registerFunc(new FuncVar("IPmt", 4, 6) {
124     @Override
125     protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
126       double rate = params[0].getAsDouble(ctx);
127       double per = params[1].getAsDouble(ctx);
128       double nper = params[2].getAsDouble(ctx);
129       double pv = params[3].getAsDouble(ctx);
130       double fv = getFV(ctx, params, 4);
131       int pmtType = getPaymentType(ctx, params, 5);
132 
133       double result = calculateInterestPayment(rate, per, nper, pv, fv,
134                                                   pmtType);
135 
136       return ValueSupport.toValue(result);
137     }
138   });
139 
140   public static final Function PPMT = registerFunc(new FuncVar("PPmt", 4, 6) {
141     @Override
142     protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
143       double rate = params[0].getAsDouble(ctx);
144       double per = params[1].getAsDouble(ctx);
145       double nper = params[2].getAsDouble(ctx);
146       double pv = params[3].getAsDouble(ctx);
147       double fv = getFV(ctx, params, 4);
148       int pmtType = getPaymentType(ctx, params, 5);
149 
150       double result = calculatePrincipalPayment(rate, per, nper, pv, fv,
151                                                    pmtType);
152 
153       return ValueSupport.toValue(result);
154     }
155   });
156 
157   public static final Function DDB = registerFunc(new FuncVar("DDB", 4, 5) {
158     @Override
159     protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
160       double cost = params[0].getAsDouble(ctx);
161       double salvage = params[1].getAsDouble(ctx);
162       double life = params[2].getAsDouble(ctx);
163       double period = params[3].getAsDouble(ctx);
164       double factor = getOptionalDoubleParam(ctx, params, 4, 2d);
165 
166       double result = calculateDoubleDecliningBalance(
167           cost, salvage, life, period, factor);
168 
169       return ValueSupport.toValue(result);
170     }
171   });
172 
173   public static final Function SLN = registerFunc(new FuncVar("SLN", 3, 3) {
174     @Override
175     protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
176       double cost = params[0].getAsDouble(ctx);
177       double salvage = params[1].getAsDouble(ctx);
178       double life = params[2].getAsDouble(ctx);
179 
180       double result = calculateStraightLineDepreciation(cost, salvage, life);
181 
182       return ValueSupport.toValue(result);
183     }
184   });
185 
186   public static final Function SYD = registerFunc(new FuncVar("SYD", 4, 4) {
187     @Override
188     protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
189       double cost = params[0].getAsDouble(ctx);
190       double salvage = params[1].getAsDouble(ctx);
191       double life = params[2].getAsDouble(ctx);
192       double period = params[3].getAsDouble(ctx);
193 
194       double result = calculateSumOfYearsDepreciation(
195           cost, salvage, life, period);
196 
197       return ValueSupport.toValue(result);
198     }
199   });
200 
201   public static final Function Rate = registerFunc(new FuncVar("Rate", 3, 6) {
202     @Override
203     protected Value/com/healthmarketscience/jackcess/expr/Value.html#Value">Value evalVar(EvalContext ctx, Value[] params) {
204       double nper = params[0].getAsDouble(ctx);
205       double pmt = params[1].getAsDouble(ctx);
206       double pv = params[2].getAsDouble(ctx);
207       double fv = getFV(ctx, params, 3);
208       int pmtType = getPaymentType(ctx, params, 4);
209       double guess = getOptionalDoubleParam(ctx, params, 5, 0.1);
210 
211       double result = calculateRate(nper, pmt, pv, fv, pmtType, guess);
212 
213       return ValueSupport.toValue(result);
214     }
215   });
216 
217   private static double calculateFutureValue(
218       double rate, double nper, double pmt, double pv, int pmtType) {
219     if (rate == 0d) {
220       return -1 * (pv + (nper * pmt));
221     }
222     double r1 = (pmtType == PMT_BEG_MNTH ? (rate + 1) : 1);
223     double p1 = Math.pow((rate + 1), nper);
224     return ((((1 - p1) * r1 * pmt) / rate) - (pv * p1));
225 
226   }
227 
228   private static double calculatePresentValue(
229       double rate, double nper, double pmt, double fv, int pmtType) {
230     if (rate == 0d) {
231       return -1 * ((nper * pmt) + fv);
232     }
233     double r1 = (pmtType == PMT_BEG_MNTH ? (rate + 1) : 1);
234     double p1 = Math.pow((rate + 1), nper);
235     return ((((1 - p1) / rate) * r1 * pmt) - fv) / p1;
236   }
237 
238   private static double calculateLoanPayment(
239       double rate, double nper, double pv, double fv, int pmtType) {
240 
241     if (rate == 0d) {
242       return -1*(fv + pv) / nper;
243     }
244     double r1 = (pmtType == PMT_BEG_MNTH ? (rate + 1) : 1);
245     double p1 = Math.pow((rate + 1), nper);
246     return (fv + (pv * p1)) * rate / (r1 * (1 - p1));
247   }
248 
249 
250   private static double calculateInterestPayment(
251       double rate, double per, double nper, double pv, double fv, int pmtType) {
252 
253     if((per == 1d) && (pmtType == PMT_BEG_MNTH)) {
254       // no inerest for pmt at beginning of month of 1st period
255       return 0d;
256     }
257 
258     double pmt = calculateLoanPayment(rate, nper, pv, fv, pmtType);
259     double result = calculateFutureValue(
260         rate, per - 1, pmt, pv, pmtType) * rate;
261 
262     if (pmtType == PMT_BEG_MNTH) {
263       result /= (1 + rate);
264     }
265     return result;
266   }
267 
268   private static double calculatePrincipalPayment(
269       double rate, double per, double nper, double pv, double fv, int pmtType) {
270     double pmt = calculateLoanPayment(rate, nper, pv, fv, pmtType);
271     double ipmt = calculateInterestPayment(rate, per, nper, pv, fv, pmtType);
272     return (pmt - ipmt);
273   }
274 
275   public static double calculateDoubleDecliningBalance(
276       double cost, double salvage, double life, double period, double factor) {
277     if (cost < 0 || ((life == 2d) && (period > 1d))) {
278       return 0;
279     }
280     if (life < 2d || ((life == 2d) && (period <= 1d))) {
281       return (cost - salvage);
282     }
283 
284     double v1 = ((factor * cost) / life);
285     if (period <= 1d) {
286       return Math.min(v1, cost - salvage);
287     }
288 
289     double v2 = (life - factor) / life;
290     double v3 = Math.max(salvage - (cost * Math.pow(v2, period)), 0);
291     double result = (v1 * Math.pow(v2, period - 1d)) - v3;
292 
293     return Math.max(result, 0);
294   }
295 
296   private static double calculateStraightLineDepreciation(
297       double cost, double salvage, double life) {
298     return ((cost - salvage) / life);
299   }
300 
301   private static double calculateSumOfYearsDepreciation(
302       double cost, double salvage, double life, double period) {
303     double result = ((cost - salvage) * (life - period + 1) * 2d) /
304       (life * (life + 1));
305     return result;
306   }
307 
308   private static double calculateLoanPaymentPeriods(
309       double rate, double pmt, double pv, double fv, int pmtType) {
310 
311     if (rate == 0d) {
312       return -1 * (fv + pv) / pmt;
313     }
314 
315     double cr = ((pmtType == PMT_BEG_MNTH) ? (1 + rate) : 1) * pmt / rate;
316     double v1;
317     double v2;
318     if((cr - fv) < 0d) {
319       v1 = Math.log(fv - cr);
320       v2 = Math.log(-pv - cr);
321     } else {
322       v1 = Math.log(cr - fv);
323       v2 = Math.log(pv + cr);
324     }
325 
326     return (v1 - v2) / Math.log(1 + rate);
327   }
328 
329   public static double calculateRate(double nper, double pmt, double pv,
330                                      double fv, int pmtType, double guess) {
331     double y, f = 0;
332     double rate = guess;
333     if (Math.abs(rate) < RATE_PRECISION) {
334       y = pv * (1 + nper * rate) + pmt * (1 + rate * pmtType) * nper + fv;
335     } else {
336       f = Math.exp(nper * Math.log(1 + rate));
337       y = pv * f + pmt * (1 / rate + pmtType) * (f - 1) + fv;
338     }
339     double y0 = pv + pmt * nper + fv;
340     double y1 = pv * f + pmt * (1 / rate + pmtType) * (f - 1) + fv;
341 
342     // find root by Newton secant method
343     int i = 0;
344     double x0 = 0.0;
345     double x1 = rate;
346     while ((Math.abs(y0 - y1) > RATE_PRECISION) && (i < MAX_RATE_ITERATIONS)) {
347       rate = (y1 * x0 - y0 * x1) / (y1 - y0);
348       x0 = x1;
349       x1 = rate;
350 
351       if (Math.abs(rate) < RATE_PRECISION) {
352         y = pv * (1 + nper * rate) + pmt * (1 + rate * pmtType) * nper + fv;
353       } else {
354         f = Math.exp(nper * Math.log(1 + rate));
355         y = pv * f + pmt * (1 / rate + pmtType) * (f - 1) + fv;
356       }
357 
358       y0 = y1;
359       y1 = y;
360       ++i;
361     }
362 
363     return rate;
364   }
365 
366   private static double getFV(EvalContext ctx, Value[] params, int idx) {
367     return getOptionalDoubleParam(ctx, params, idx, 0d);
368   }
369 
370   private static int getPaymentType(EvalContext ctx, Value[] params, int idx) {
371     int pmtType = PMT_END_MNTH;
372     if(params.length > idx) {
373       pmtType = (params[idx].getAsLongInt(ctx) != PMT_END_MNTH) ?
374         PMT_BEG_MNTH : PMT_END_MNTH;
375     }
376     return pmtType;
377   }
378 }