Class to implement internal excel function RATE

CalculateRate.java (text/x-java-source), 4.25 KB, created by carlo.traversa on 2011-10-18 16:01:14 UTC

(hide)

Description: Class to implement internal excel function RATE

Filename:

MIME Type:

Creator: carlo.traversa

Created: 2011-10-18 16:01:14 UTC

Size: 4.25 KB

patch

obsolete

>package org.apache.poi.ss.formula.eval; > >import org.apache.poi.ss.formula.OperationEvaluationContext; >import org.apache.poi.ss.formula.eval.ErrorEval; >import org.apache.poi.ss.formula.eval.EvaluationException; >import org.apache.poi.ss.formula.eval.NumberEval; >import org.apache.poi.ss.formula.eval.OperandResolver; >import org.apache.poi.ss.formula.eval.ValueEval; >import org.apache.poi.ss.formula.functions.FreeRefFunction; > >public class CalculateRate implements FreeRefFunction { > > > public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) { > if (args.length < 3) { //First 3 parameters are mandatory > return ErrorEval.VALUE_INVALID; > } > > double periods, payment, present_val, future_val = 0, type = 0, estimate = 0.1, rate; > > try { > ValueEval v1 = OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex()); > ValueEval v2 = OperandResolver.getSingleValue(args[1], ec.getRowIndex(), ec.getColumnIndex()); > ValueEval v3 = OperandResolver.getSingleValue(args[2], ec.getRowIndex(), ec.getColumnIndex()); > ValueEval v4 = null; > if (args.length >= 4) > v4 = OperandResolver.getSingleValue(args[3], ec.getRowIndex(), ec.getColumnIndex()); > ValueEval v5 = null; > if (args.length >= 5) > v5 = OperandResolver.getSingleValue(args[4], ec.getRowIndex(), ec.getColumnIndex()); > ValueEval v6 = null; > if (args.length >= 6) > v6 = OperandResolver.getSingleValue(args[5], ec.getRowIndex(), ec.getColumnIndex()); > > periods = OperandResolver.coerceValueToDouble(v1); > payment = OperandResolver.coerceValueToDouble(v2); > present_val = OperandResolver.coerceValueToDouble(v3); > if (args.length >= 4) > future_val = OperandResolver.coerceValueToDouble(v4); > if (args.length >= 5) > type = OperandResolver.coerceValueToDouble(v5); > if (args.length >= 6) > estimate = OperandResolver.coerceValueToDouble(v6); > rate = calculateRate(periods, payment, present_val, future_val, type, estimate) ; > > checkValue(rate); > > } catch (EvaluationException e) { > e.printStackTrace() ; > return e.getErrorEval(); > } > > return new NumberEval( rate ) ; > } > > private double calculateRate(double nper, double pmt, double pv, double fv, double type, double guess) { > //FROM MS http://office.microsoft.com/en-us/excel-help/rate-HP005209232.aspx > int FINANCIAL_MAX_ITERATIONS = 20;//Bet accuracy with 128 > double FINANCIAL_PRECISION = 0.0000001;//1.0e-8 > > double y, y0, y1, x0, x1 = 0, f = 0, i = 0; > double rate = guess; > if (Math.abs(rate) < FINANCIAL_PRECISION) { > y = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv; > } else { > f = Math.exp(nper * Math.log(1 + rate)); > y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv; > } > y0 = pv + pmt * nper + fv; > y1 = pv * f + pmt * (1 / rate + type) * (f - 1) + fv; > > // find root by Newton secant method > i = x0 = 0.0; > x1 = rate; > while ((Math.abs(y0 - y1) > FINANCIAL_PRECISION) && (i < FINANCIAL_MAX_ITERATIONS)) { > rate = (y1 * x0 - y0 * x1) / (y1 - y0); > x0 = x1; > x1 = rate; > > if (Math.abs(rate) < FINANCIAL_PRECISION) { > y = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv; > } else { > f = Math.exp(nper * Math.log(1 + rate)); > y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv; > } > > y0 = y1; > y1 = y; > ++i; > } > return rate; > } > > /** > * Excel does not support infinities and NaNs, rather, it gives a #NUM! error in these cases > * > * @throws EvaluationException (#NUM!) if <tt>result</tt> is <tt>NaN</> or <tt>Infinity</tt> > */ > static final void checkValue(double result) throws EvaluationException { > if (Double.isNaN(result) || Double.isInfinite(result)) { > throw new EvaluationException(ErrorEval.NUM_ERROR); > } > } > >} View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]