/* FinanceFunctionImpl.java

	Purpose:
		
	Description:
		
	History:
		Apr 19, 2010 5:52:10 PM, Created by henrichen

Copyright (C) 2010 Potix Corporation. All Rights Reserved.

*/

package org.zkoss.zssex.formula.fn;

import java.math.BigDecimal;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.jfree.date.SerialDate;
import org.jfree.date.SerialDateUtilities;
import org.zkoss.poi.ss.formula.eval.ErrorEval;
import org.zkoss.poi.ss.formula.eval.EvaluationException;
import org.zkoss.poi.ss.formula.eval.ValueEval;
import org.zkoss.poi.ss.formula.functions.FinanceFunction;
import org.zkoss.poi.ss.formula.functions.Function;
import org.zkoss.poi.ss.formula.functions.NumericFunction;
import org.zkoss.poi.ss.formula.AreaEvalHelper;
import org.zkoss.poi.ss.formula.functions.UtilFns;
import org.zkoss.poi.ss.usermodel.DateUtil;

/**
 * Implementation of Spreadsheet financial functions.
 * @author henrichen
 */
public class FinanceFunctionImpl {

	
	public static final Function ACCRINT = new NumericFunction() {

		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			
			double dissue = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date issue = DateUtil.getJavaDate(dissue);
			double dFirI = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date firI = DateUtil.getJavaDate(dFirI);
			double dSettlement = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			Date settlement = DateUtil.getJavaDate(dSettlement);
			if(settlement.before(issue)) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			double rate = args[3] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			double par = args[4] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex);
			int freq = (int) (args[5] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[5], srcRowIndex, srcColumnIndex));
			int basis = 0;
			if(args.length == 7) {
				basis = basis((int) (args[6] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[6], srcRowIndex, srcColumnIndex)));
			}
			
			if(basis < 0 || basis > 4) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			boolean method = true;
			if(args.length == 8) {
				method = UtilFns.stringToBoolean(args[7]);
			}
			if(!(freq == 1 || freq == 2 || freq == 4)) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			if( rate <= 0 || par <= 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			double result = 0;
			//TODO:only only a few situations are correct
			if(method) //because Par * rate / freq * diff * freq == Par * rate * diff
			{
				result = par * rate * getYearDiff(issue, settlement, basis);
			}
			else //TODO: does someone knows what hell MSFT does it? 
			{
				result = par * rate * getYearDiff(firI, settlement, basis);
			}

			return result;
		}
		
	};
	
	/* get ratio of former owner's portion to count ACCRINT()*/
	private static double getYearDiff(Date firstDate, Date secondDate, int basis)
	{
		// TODO: remove jcommon.jar dependency
		SerialDate startDate = SerialDate.createInstance(firstDate);
		SerialDate endDate = SerialDate.createInstance(secondDate);
		double result = 0;
		int diff = 0;
		switch (basis)
		{
			case 0: //360 days (NASD method)
				diff = SerialDateUtilities.dayCount30(startDate, endDate);
				if(startDate.getYYYY() == endDate.getYYYY() && startDate.getMonth() == 2 && endDate.getMonth() != 2)
				{
					if(SerialDate.isLeapYear(startDate.getYYYY()))
					{
						diff-=1;
					}
					else
					{
						diff-=2;
					}
				}
				result = diff / 360.0;
			break;
			
			case 1: //Actual
				diff = SerialDateUtilities.dayCountActual(startDate, endDate);
				if(SerialDate.isLeapYear(startDate.getYYYY()))
				{
					result = diff / 366.0;
				}
				else
				{
					result = (double)diff / 365.0;
				}
			break;
			
			case 2: //Uses actual day differences but assumes the year only have 360 days
				diff = SerialDateUtilities.dayCountActual(startDate, endDate);
				result = diff / 360.0;
			break;
				
			case 3: //365 days in a year
				diff = SerialDateUtilities.dayCountActual(startDate, endDate);
				result = diff / 365.0;
			break;
			
			case 4: //360 days in a year (European method)
				diff = SerialDateUtilities.dayCount30E(startDate, endDate);
				result = diff / 360.0;
			break;
			
				
		}
		return result;
	}

	public static final Function ACCRINTM = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {

			double rate = args[2] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			double par = args[3] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			
			if( rate <= 0 || par <= 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			
			double dissue = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date issue = DateUtil.getJavaDate(dissue);
			double dSettlement = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date settlement = DateUtil.getJavaDate(dSettlement);
			
			if(settlement.before(issue)) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}

			int basis = 0;
			if(args.length == 5) {
				basis = basis((int) (args[4] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex)));
			}
			int A = UtilFns.dsm(issue, settlement, basis);
			double D = UtilFns.basisToDouble(basis, issue, settlement, A);
			double accrintm = par*rate*(A/D);
			return new Double(accrintm);
		}		
	};

	
	private static double yearFraction(Date d0, Date d1, int basis)
	{
		SerialDate startDate = SerialDate.createInstance(d0);
		SerialDate endDate = SerialDate.createInstance(d1);
		double result = 0;
		int diff = 0;
		switch (basis)
		{
			case 0: //360 days (NASD method)
				diff = SerialDateUtilities.dayCount30(startDate, endDate);
				result = diff / 360.0;
				break;
			
			case 1: //Actual
//				diff = SerialDateUtilities.dayCountActual(startDate, endDate);
//				
//				if(SerialDate.isLeapYear(endDate.getYYYY()) && endDate.getMonth() > 2)
//				{
//					result = diff / (365.0 + 1);
//				}
//				else
//				{
//					result = diff / 365.0;
//				}
//				break;
				int nYears, nFeb29s;
				double peryear;
				
				SerialDate tempDate1 = SerialDate.addYears(1,startDate);
				if(endDate.compare(tempDate1) > 0)//the interval is more than one year.
				{
					nYears = endDate.getYYYY() - startDate.getYYYY() + 1;
					tempDate1 = SerialDate.createInstance(1,1,endDate.getYYYY() + 1);
					SerialDate tempDate2 = SerialDate.createInstance(1,1,startDate.getYYYY());
					nFeb29s = tempDate1.compare(tempDate2)- 365 * nYears;
				}
				else
				{
					nYears=1;
					if((SerialDate.isLeapYear(startDate.getYYYY()) && startDate.getMonth() < 3 ) ||
						SerialDate.isLeapYear(endDate.getYYYY()) && endDate.compare(SerialDate.createInstance(29,2,endDate.getYYYY())) >= 0 )
					{
						nFeb29s = 1;
					}
					else nFeb29s = 0;
				}
				peryear = 365.0 + (double)nFeb29s/(double)nYears;
				result = (double)endDate.compare(startDate)/peryear;
				break;
			
			case 2: //Uses actual day differences but assumes the year only have 360 days
				diff = SerialDateUtilities.dayCountActual(startDate, endDate);
				result = diff / 360.0;
				break;
				
			case 3: //365 days in a year
				diff = SerialDateUtilities.dayCountActual(startDate, endDate);
				result = diff / 365.0;
				break;
				
			case 4: //360 days in a year (European method)
				diff = SerialDateUtilities.dayCount30E(startDate, endDate);
				result = diff / 360.0;
				break;
		}
		return result;
	}

	public static final Function AMORDEGRC = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double cost = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double dPurDate = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date purDate = DateUtil.getJavaDate(dPurDate);
			double dFirDate = args[2] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			Date firDate = DateUtil.getJavaDate(dFirDate);
			double salvage = args[3] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			int period = (int) (args[4] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex));
			double rate = args[5] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[5], srcRowIndex, srcColumnIndex);
			int basis = 0;
			if(args.length == 7)
			{
				basis = basis((int) (args[6] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[6], srcRowIndex, srcColumnIndex)));
			}
			double usePeriod = 1.0/rate; 
			double coeff;
			if(usePeriod < 3)
			{
				coeff = 1.0;
			}
			else if(usePeriod < 5)
			{
				coeff = 1.5;
			}
			else if(usePeriod <= 6)
			{
				coeff = 2.0;
			}
			else
			{
				coeff = 2.5;
			}
			rate *= coeff;
			double nRate = Math.round(yearFraction(purDate, firDate, basis) * rate * cost);
			cost -= nRate;
			double rest = cost - salvage;
			
			for ( int n = 0 ; n < period ; n++ ) 
			{
	            nRate = Math.round(rate * cost);
				//nRate = rate * cost;
	            rest -= nRate;

	            if ( rest < 0.0 )
	            {
	            	switch ( period - n ) 
	                {
						case 0:
						case 1:
							return Math.round(cost * 0.5 );
							
						default:
							return 0.0;
	                }
	            }

	            cost -= nRate;
			}
			
			return nRate;
		}
	};
	
	public static final Function AMORLINC = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double cost = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double dPurDate = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date purDate = DateUtil.getJavaDate(dPurDate);
			double dFirDate = args[2] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			Date firDate = DateUtil.getJavaDate(dFirDate);
			double salvage = args[3] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			int period = (int) (args[4] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex));
			double rate = args[5] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[5], srcRowIndex, srcColumnIndex);
			int basis = 0;
			if(args.length == 7) {
				basis = basis((int) (args[6] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[6], srcRowIndex, srcColumnIndex)));
			}
			double T0 = cost * rate;
			double T1 = yearFraction(purDate, firDate, basis) * T0;
			int numPeriods = (int)(((cost - salvage) - T1)/T0);
			
			if (period==0)
			{
			    return new Double(T1);
			}
			else if (period<=numPeriods) 
			{
				return new Double(T0);
			}
			else if (period==numPeriods+1) 
			{
				return (cost - salvage)- T0*numPeriods - T1;
			}
			else 
			    return 0;
		}
	};
	
	/* return basis */
	private static int basis(int basis) throws EvaluationException {
		if(basis > 4 || basis < 0) {
			throw new EvaluationException(ErrorEval.NUM_ERROR);
		} else {
			return basis;			
		}
	}

	public static final Function COUPDAYBS = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			int frequency = frequency((int) (args[2] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex)));
			int basis = 0;
			if(args.length == 4) {
				basis = basis((int) (args[3] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex)));
			}
			return coupdaybs(settle, maturi, frequency, basis);
		}
	};

	/*return coupdaybs *note: freq has already divided 1 year(12 months)*/
	private static double coupdaybs(Date settle, Date maturi, int freq, int basis) throws EvaluationException
	{
		Date coupday = couppcd(settle, maturi, freq, basis);
		return UtilFns.dsm(coupday, settle, basis);
	}
	
	/* return couppcd  *note: frequency has already treated with 1 year(12 months)*/
	private static Date couppcd(Date settle, Date maturi, int frequency, int basis) {
		GregorianCalendar couppcd = new GregorianCalendar();
		GregorianCalendar settleD = new GregorianCalendar();
		settleD.setTime(settle);
		couppcd.setTime(maturi);
		couppcd.set(Calendar.YEAR, settleD.get(Calendar.YEAR));
		if(couppcd.after(settleD))
		{
			couppcd.add(Calendar.YEAR, -1);
		}
		while(!couppcd.after(settleD))
		{
			couppcd.add(Calendar.MONTH, frequency);
		}
		couppcd.add(Calendar.MONTH, -1*frequency);
		return couppcd.getTime();
		
	}

	private static int frequency(int frequency) throws EvaluationException {
		if(frequency != 1 && frequency != 2 && frequency != 4) {
			throw new EvaluationException(ErrorEval.NUM_ERROR);
		} else {
			return 12/frequency;
		}
	}

	
	public static final Function COUPDAYS = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			int frequency = frequency((int) (args[2] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex)));
			int basis = 0;
			if(args.length == 4) {
				basis = basis((int) (args[3] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex)));
			}
			
			return coupdays(settle, maturi, frequency, basis);
		}
	};
	
	/*return coupdays *note: freq has already divided 1 year(12 months)*/
	private static double coupdays(Date settle, Date maturi, int freq, int basis) throws EvaluationException
	{
		Date couppcd = couppcd(settle, maturi, freq, basis);
		Date coupncd = coupncd(couppcd, freq);
		return UtilFns.dsm(couppcd, coupncd, basis);
	}
	
	private static Date coupncd(Date coupday, int frequency) {
		GregorianCalendar coupncd = new GregorianCalendar();
		coupncd.setTime(coupday);
		coupncd.add(Calendar.MONTH, frequency);
		return coupncd.getTime();
	}
	
	public static final Function COUPDAYSNC = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			int frequency = frequency((int) (args[2] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex)));
			int basis = 0;
			if(args.length == 4) {
				basis = basis((int) (args[3] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex)));
			}
			return coupdaysnc(settle, maturi, frequency, basis);
		}
	};
	
	/*return coupdaysnc *note: freq has already divided 1 year(12 months)*/
	private static double coupdaysnc(Date settle, Date maturi, int freq, int basis) throws EvaluationException
	{
		Date couppcd = couppcd(settle, maturi, freq, basis);
		Date coupncd = coupncd(couppcd, freq);
		double coupdaysnc = UtilFns.dsm(settle, coupncd, basis);
		return coupdaysnc;
	}

	public static final Function COUPNCD = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			int frequency = frequency((int) (args[2] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex)));
			int basis = 0;
			if(args.length == 4) {
				basis = basis((int) (args[3] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex)));
			}
			Date couppcd = couppcd(settle, maturi, frequency, basis);
			Date coupncd = coupncd(couppcd, frequency);

			return DateUtil.getExcelDate(coupncd);
		}
	};
	public static final Function COUPNUM = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			int frequency = frequency((int) (args[2] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex)));
			int basis = 0;
			if(args.length == 4) {
				basis = basis((int) (args[3] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex)));
			}
			
			return coupnum(settle, maturi, frequency, basis);
		}
	};
	
	/*return coupnum *note: freq has already divided 1 year(12 months)*/
	private static double coupnum(Date settle, Date maturi, int freq, int basis)
	{
		Date couppcd = couppcd(settle, maturi, freq, basis);
		GregorianCalendar gc = new GregorianCalendar();
		gc.setTime(couppcd);
		int couppcdY = gc.get(Calendar.YEAR);
		int couppcdM = gc.get(Calendar.MONTH);
		gc.setTime(maturi);
		int maturiY = gc.get(Calendar.YEAR);
		int maturiM = gc.get(Calendar.MONTH);
		return Math.floor(((maturiY - couppcdY)*12 + maturiM - couppcdM)/freq);
	}

	
	public static final Function COUPPCD = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			if(settle.after(maturi)) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			int frequency = frequency((int) (args[2] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex)));
			int basis = 0;
			if(args.length == 4) {
				basis = basis((int) (args[3] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex)));
			}
			
			return DateUtil.getExcelDate(couppcd(settle, maturi, frequency, basis));
		}
	};
	
	public static final Function CUMIPMT = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double rate= NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			int nper = (int)Math.floor(NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex));
			double pv = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			int startPer = (int)Math.floor(NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex));
			int endPer = (int)Math.floor(NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex));
			if(rate == 0 || nper == 0 || pv == 0 || startPer < 1 || endPer < 1 || startPer > endPer) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			} else {
				int type = 0;
				if(args.length > 5)
				{
					type = (int) NumericFunction.singleOperandEvaluate(args[5], srcRowIndex, srcColumnIndex);
				}
				
				if( type != 0 && type != 1)
				{
					throw new EvaluationException(ErrorEval.NUM_ERROR);
				}
				
				double cumipmt = 0.0;
				//first calculate PMT for feed into the following magic formula.
				double pmt = pmt(rate, nper, pv, 0.0, type);
				
				if( startPer == 1 )
				{
					if( type <= 0 )
					{
						cumipmt = -pv;
					}
				    startPer++;
				}
				
				for( int i = startPer ; i <= endPer ; i++ )
				{
					if( type > 0 )
						cumipmt += GetZw( rate, ( i - 2 ), pmt, pv, 1 ) - pv;
					else
						cumipmt += GetZw( rate, ( i - 1 ), pmt, pv, 0 );
				}

				cumipmt *= rate;
				return cumipmt;
			}
		}
	};

	public static final Function CUMPRINC = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			
			double rate= NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double nper = Math.floor(NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex));
			double pv = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			int startPer = (int)Math.floor(NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex));
			int endPer = (int)Math.floor(NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex));
			if(rate == 0 || nper == 0 || pv == 0 || startPer < 1 || endPer < 1 || startPer > endPer) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			} else {
				int type = 0;
				if(args.length > 5)
				{
					type = (int) NumericFunction.singleOperandEvaluate(args[5], srcRowIndex, srcColumnIndex);
				}
				if( type != 0 && type != 1)
				{
					throw new EvaluationException(ErrorEval.NUM_ERROR);
				}
				double cumppmt = 0.0;
				//first calculate PMT for feed into the following magic formula.
				double pmt = pmt(rate, nper, pv, 0.0, type);
				if ( startPer == 1 ) 
				{
		            if ( type <= 0 )
		                   cumppmt = pmt + pv * rate;
		            else
		                    cumppmt = pmt;

		            startPer++;
				}

				for (int i = startPer++ ; i <= endPer ; i++ ) 
				{
		            if ( type > 0 )
		                    cumppmt += pmt - ( GetZw( rate, ( i - 2 ), pmt, pv, 1 ) - pmt ) * rate;
		            else
		                    cumppmt += pmt - GetZw( rate, ( i - 1 ), pmt, pv, 0 ) * rate;
				}
				return cumppmt;
			}
		}
	};
	
	public static final Function DISC = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double pr = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			double redemption = NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			if(pr <= 0.0 || redemption <= 0.0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			double dSettle = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settlement = DateUtil.getJavaDate(dSettle);
			double dMaturi = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturity = DateUtil.getJavaDate(dMaturi);
			int basis = 0;
			if(args.length == 5) {
				basis = basis((int) (args[4] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex)));
			}
			int DSM = UtilFns.dsm(settlement, maturity, basis);
			double B = UtilFns.basisToDouble(basis, settlement, maturity, DSM);
			double disc = ((redemption - pr)/redemption)*(B/DSM);
			return disc;
		}
	};

	public static final Function DOLLARDE = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dollar = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double fraction = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			if (fraction < 0.0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			if (fraction >= 0 && fraction < 1.0) {
				throw new EvaluationException(ErrorEval.DIV_ZERO);
			}
			return Math.floor(dollar) + (dollar%1*100)/fraction;
		}
	};
	
	public static final Function DOLLARFR = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dollar = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double fraction = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			if (fraction < 0.0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			if (fraction == 0.0) {
				throw new EvaluationException(ErrorEval.DIV_ZERO);
			}
			return Math.floor(dollar) + (dollar%1*fraction)/100;
		}
	};
	
	public static final Function DURATION = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			if(settle.after(maturi))
			{
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			double coupon = args[2] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			double yld = args[3] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			if(!(yld > 0 || coupon > 0 ))
			{
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			int freq = (int) (args[4] == null ? 1.0 : NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex));
			if( freq != 1 && freq != 2 && freq != 4 )
			{
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			int basis = 0;
			if(args.length == 6) {
				basis = basis((int) (args[5] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[5], srcRowIndex, srcColumnIndex)));
			}
			double yearFrac = yearFraction(settle, maturi, basis);
			double coupNs = coupnum(settle, maturi, 12/freq,basis);
			
			double dura = 0.0;
			coupon *= 100.0 / freq;	// coupon is used as cash flow
			yld /= freq;
			yld += 1.0;

			double nDiff = yearFrac * freq - coupNs;

			for( int t = 1 ; t < coupNs ; t++ )
			{
				dura += ( t + nDiff ) * ( coupon ) / Math.pow( yld, t + nDiff );
			}
			dura += ( coupNs + nDiff ) * ( coupon + 100.0 ) / Math.pow( yld, coupNs + nDiff );

			double p = 0.0;
			for(int t = 1; t < coupNs ; t++ )
			{
				p += coupon / Math.pow( yld, t + nDiff );
			}
			p += ( coupon + 100.0 ) / Math.pow( yld, coupNs + nDiff );

			dura /= p;
			dura /= (double)freq;
			return dura;
		}
	};

	public static final Function EFFECT = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double rate= NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double npery = Math.floor(NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex));
			if(rate <= 0 || npery < 1) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return Math.pow(1+rate/npery, npery) - 1;
		}
	};
	public static final Function FVSCHEDULE = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double principal = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			List l0 = AreaEvalHelper.toDoubleList(args[1], srcRowIndex, srcColumnIndex);
			double[] schedule = UtilFns.toDoubleArray(l0);
			for(int i = 0; i < schedule.length; i++) {
				principal = principal*(1 + schedule[i]);
			}
			return new Double(principal);
		}
	};

	public static final Function INTRATE = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double investment = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			double redemption = NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			if(investment <= 0 || redemption <= 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			double dSettle = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			int basis = 0;
			if(args.length == 5) {
				basis = basis((int) (args[4] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex)));
			}
			int dsm = UtilFns.dsm(settle, maturi, basis);
			double B = UtilFns.basisToDouble(basis, settle, maturi, dsm);
			return ((redemption - investment)/investment)*(B/dsm);
		}
	};
	
	
	public static final Function NOMINAL = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double rate= NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double npery = Math.floor(NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex));
			if(rate <= 0 || npery < 1) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			return (Math.pow(rate+1, 1/npery)-1)*npery;
		}
	};
	//ZSS-251, this implementation can't pass unit-test, ignored in tld, and use poi's implementation
	public static final Function NPV = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double d = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			List l0 = AreaEvalHelper.toDoubleList(args[1], srcRowIndex, srcColumnIndex);
			double[] values = UtilFns.toDoubleArray(l0);
			return npv(d, values);
		}
	};

	/* Returns npv */
	private static double npv(double rate, double[] values) {
		double[] npv = new double[values.length];
		for(int i = 0;i<values.length;i++) {
			npv[i] = values[i]/Math.pow((1+rate), i+1);
		}
		return UtilFns.getStats(npv).getSum();
	}

	public static final Function PRICE = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			if(settle.after(maturi))
			{
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			double rate= NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			double yld = NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			double redemption = NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex);
			if(!(yld > 0 || rate > 0 || redemption > 0 ))
			{
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			int freq = (int) (args[5] == null ? 1.0 : NumericFunction.singleOperandEvaluate(args[5], srcRowIndex, srcColumnIndex));
			if( freq != 1 && freq != 2 && freq != 4 )
			{
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			int basis = 0;
			if(args.length == 7) {
				basis = basis((int) (args[6] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[6], srcRowIndex, srcColumnIndex)));
			}
			
			return price(settle, maturi, rate, yld, redemption, freq, basis);
		}
	};

	private static double price(Date settle, Date maturi, double rate, double yld, double redemp, int freq, int basis) throws EvaluationException
	{
		//get E
		Date couppcd = couppcd(settle, maturi, frequency(freq), basis);
		Date coupncd = coupncd(couppcd, frequency(freq));
		double E = UtilFns.dsm(couppcd, coupncd, basis);
		
		//get DSC
		double DSC = UtilFns.dsm(settle, coupncd, basis);
		
		//get N
		double N = coupnum(settle, maturi, frequency(freq), basis);
			
		//get A
		double A = UtilFns.dsm(couppcd, settle, basis);
		
		double price = redemp/Math.pow((1+ yld/freq),(N-1 + DSC/E));
		double T1 = 100.0 * rate / freq;
		double T2 = 1.0 + yld / freq;
		double T3 = DSC/E;
		
		for(int i = 0; i < N; i++)
		{
			price += T1/Math.pow(T2, i+T3);
		}
		
		price -= T1*A/E;
		
		return price;
	}

	public static final Function PRICEDISC = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double discount = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			double redemption = NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			if(discount <= 0 || redemption <= 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			double dSettle = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			int basis = 0;
			if(args.length == 5) {
				basis = basis((int) (args[4] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex)));
			}
			int dsm = UtilFns.dsm(settle, maturi, basis);
			double B = UtilFns.basisToDouble(basis, settle, maturi, dsm);
			return redemption - discount*redemption*(dsm/B);
		}
	};

	
	public static final Function PRICEMAT = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			double dissue = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			Date issue = DateUtil.getJavaDate(dissue);
			double rate= NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			double yld = NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex);
			int basis = 0;
			if(args.length == 6) {
				basis = basis((int) (args[5] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[5], srcRowIndex, srcColumnIndex)));
			}
			if(rate < 0 || yld < 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			int dsm = UtilFns.dsm(settle, maturi, basis);
			int dim = UtilFns.dsm(issue, maturi, basis);
			int A = UtilFns.dsm(issue, settle, basis);
			double B = UtilFns.basisToDouble(basis, issue, settle, A);
			
			return ((100 + ((dim/B)*rate*100))/(1 + ((dsm/B)*yld))) - ((A/B)*rate*100);
		}
	};
	
//	public static final Function PV = new NumericFunction() {
//		@Override
//		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
//			double rate = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
//			int nper = (int) NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
//			double pmt = args[2] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
//			double fv = args[3] == null || args.length <= 3 ? 0.0 : NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
//			int type = 0;
//			if(args.length == 5) {
//				type = (int) NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex);
//			}
//			
//			return pv(rate, nper, pmt, fv, type);
//		}
//	};
	
//	private static double pv(double rate, int nper, double pmt, double fv, int type) {
//		double pv = 0.0;
//		if(rate == 0) {
//			pv = -(fv+(pmt*nper));
//		} else {
//			pv = -(fv + (pmt*(1+rate*type)*(Math.pow(1+rate, nper)-1)/rate)/Math.pow(1+rate, nper));
//		}
//		return pv;
//	}
	
	public static final Function RECEIVED = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double investment = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			double discount = NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			if(investment <= 0 || discount <= 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			double dSettle = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			int basis = 0;
			if(args.length == 5) {
				basis = basis((int) NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex));
			}
			int dim = UtilFns.dsm(settle, maturi, basis);
			double B = UtilFns.basisToDouble(basis, settle, maturi, dim);
			
			return investment/(1 - (discount*(dim/B)));
		}
	};
	public static final Function TBILLEQ = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			double discount = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			if(discount <= 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			int DSM = UtilFns.dsm(settle, maturi, 2);
			
			return (365*discount)/(360 - (discount*DSM));
		}
	};

	public static final Function TBILLPRICE = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			double discount = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			if(discount <= 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			int DSM = UtilFns.dsm(settle, maturi, 2);
			
			return 100*(1-(discount*DSM)/360);
		}
	};

	public static final Function TBILLYIELD = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			double pr = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			if(pr <= 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			int DSM = UtilFns.dsm(settle, maturi, 2);
			double p1 = (100 - pr) / pr;
			double p2 = 360.0 / DSM;
			return p1 * p2;
		}
	};

	public static final Function XNPV = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double rate = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			List l0 = AreaEvalHelper.toDoubleList(args[1], srcRowIndex, srcColumnIndex);
			double[] values = UtilFns.toDoubleArray(l0);
			List l1 = AreaEvalHelper.toDoubleList(args[2], srcRowIndex, srcColumnIndex);
			
			if(values.length != l1.size()) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			} else {
				double[] dates = UtilFns.toDoubleArray(l1);
				double xnpv = 0.0;
				for(int i = 0; i < dates.length; i++) {
					double di = dates[i];
					double d1 = dates[0];
					double firstArg = 1 + rate;
					double secondArg = (di - d1)/365;
					double pow = Math.pow(firstArg, secondArg);
					xnpv += values[i]/pow;
				}
				return xnpv;
			}
		}
	};

	public static final Function YIELD = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			double rate = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			double pr = NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			double redemp = NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex);
			int freq = (int) NumericFunction.singleOperandEvaluate(args[5], srcRowIndex, srcColumnIndex);
			int basis = 0;
			if(args.length == 7) {
				basis = basis((int) NumericFunction.singleOperandEvaluate(args[6], srcRowIndex, srcColumnIndex));
			}
			double result;
			if(coupnum(settle, maturi, frequency(freq), basis) <= 1.0)
			{
				double A = coupdaybs(settle, maturi, frequency(freq), basis);
				double DSR = coupdaysnc(settle, maturi, frequency(freq), basis);
				double E = coupdays(settle, maturi, frequency(freq), basis);
				
				double term = rate /freq;
				double coeff = freq * E / DSR;
				double num = (redemp / 100.0  +  term)	- (pr / 100.0  +  (A / E  * term));
				double den = pr / 100.0  +  (A / E  *  term);
				
				result = num / den * coeff;
			}
			else 
			{
				result = getYld(settle, maturi, rate, pr, redemp, freq, basis);
			}
			
	
			return result;
		}
	};
	private static double getYld(Date set, Date mat, double rate, double fPrice, double redemp, int freq, int basis) throws ArithmeticException, EvaluationException
	{
		double		fPriceN = 0.0;
		double		fYield1 = 0.0;
		double		fYield2 = 1.0;
		double		fPrice1 = price( set,mat, rate, fYield1, redemp, freq, basis );
		double		fPrice2 = price( set,mat, rate, fYield2, redemp, freq, basis );
		double		fYieldN = ( fYield2 - fYield1 ) * 0.5;

			for( int nIter = 0 ; nIter < 100 && fPriceN != fPrice ; nIter++ )
			{
				fPriceN = price( set, mat, rate, fYieldN, redemp, freq, basis );

				if( fPrice == fPrice1 )
					return fYield1;
				else if( fPrice == fPrice2 )
					return fYield2;
				else if( fPrice == fPriceN )
					return fYieldN;
				else if( fPrice < fPrice2 )
				{
					fYield2 *= 2.0;
					fPrice2 = price( set, mat, rate, fYield2, redemp, freq, basis );

					fYieldN = ( fYield2 - fYield1 ) * 0.5;
				}
				else
				{
					if( fPrice < fPriceN )
					{
						fYield1 = fYieldN;
						fPrice1 = fPriceN;
					}
					else
					{
						fYield2 = fYieldN;
						fPrice2 = fPriceN;
					}

					fYieldN = fYield2 - ( fYield2 - fYield1 ) * ( ( fPrice - fPrice2 ) / ( fPrice1 - fPrice2 ) );
				}
			}

			if( Math.abs( fPrice - fPriceN ) > fPrice / 100.0 )
				throw new ArithmeticException();	// result not precise enough

			return fYieldN;
	}		

	public static final Function YIELDDISC = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			//TODO:some result aren't the same as MSFT Excel when basis = 1
			double dSettle = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			double price = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			double redemp = NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			int basis = 0;
			if(args.length == 5) {
				basis = basis((int) NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex));
			}
			double result = ( redemp / price ) - 1.0;
			
			return	result /= yearFraction(settle, maturi, basis);
			
		}
	};
	public static final Function YIELDMAT = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double dSettle = NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			Date settle = DateUtil.getJavaDate(dSettle);
			double dMaturi = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			Date maturi = DateUtil.getJavaDate(dMaturi);
			double dIssue = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			Date issue = DateUtil.getJavaDate(dIssue);
			double rate = NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
			double price = NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex);

			int basis = 0;
			if(args.length == 6) {
				basis = basis((int) NumericFunction.singleOperandEvaluate(args[5], srcRowIndex, srcColumnIndex));
			}
			
			double		fIssMat = yearFraction( issue, maturi, basis );
			double		fIssSet = yearFraction( issue, settle, basis );
			double		fSetMat = yearFraction( settle, maturi, basis );

			double		y = 1.0 + fIssMat * rate;
			y /= price / 100.0 + fIssSet * rate;
			y--;
			return y /= fSetMat;
		}
	};
	
	public static final Function Temp = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			
			return 0;
		}
	};

	public static final Function DB = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double cost = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			if(cost < 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			double salvage = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			if(salvage < 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			if(args[2] == null || args[3] == null) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			} else {
				double life = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
				double period = NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
				if(life < 0 || period < 0) {
					throw new EvaluationException(ErrorEval.NUM_ERROR);
				}
				if(period-1 > life) {
					throw new EvaluationException(ErrorEval.NUM_ERROR);
				} else {
					double month = 12.0;
					if(args.length > 4) {
						month = NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex);
					}
					double rate = new BigDecimal(1-Math.pow((salvage/cost), 1/life))
						.setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
					return db(cost, salvage, life, period, month, rate);
				}
			}
			
		}
	};
	private static double db(double cost, double salvage, double life, double period, double month, double rate) {
		double db = cost*rate*month/12;
		double d = cost*rate*month/12;
		if(period>1) {
			for(int i = 1; i <= period-1; i++) {
				if(i<life) {
					db = (cost - d)*rate;
					d = d + db;
				} else {
					db = (cost - d)*rate*(12-month)/12;
				}
			}
		}
		return db;
	}
	
	public static final Function DDB = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double cost = args[0] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			if(cost < 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			double salvage = args[1] == null ? 0.0 : NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			if(salvage < 0) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			}
			if(args[2] == null || args[3] == null) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			} else {
				double life = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
				double period = NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
				if(life < 0 || period < 0) {
					throw new EvaluationException(ErrorEval.NUM_ERROR);
				}
				if(period > life) {
					throw new EvaluationException(ErrorEval.NUM_ERROR);
				} else {
					double factor = 2;
					if(args.length > 4) {
						factor = NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex);
					}
					return ddb(cost, salvage, life, period, factor);
				}
			}
		}
	};
	private static double ddb(double cost, double salvage, double life, double period, double factor) {
		double d1 = factor*cost*Math.pow((life-factor)/life, period-1)/life;
		double d2 = (factor*cost*Math.pow((life-factor)/life, period-1)/life) - ((1-Math.pow((life-factor)/life, period))*cost - (cost-salvage));
		return Math.min(d1, d2);
	}

	public static final Function IPMT = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double rate= NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double per = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double nper = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			if (per > nper) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			} else {
				double pv = NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
				double fv = (args.length > 4 && args[4] != null) ?
						NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex) : 0.0;
				int type = args.length > 5 ? 
						(int) NumericFunction.singleOperandEvaluate(args[5], srcRowIndex, srcColumnIndex) : 0;
				return ipmt(rate, per, nper, pv, fv, type);
			}
		}
	};
	
	public static final Function PPMT = new NumericFunction() {
		@Override
		public double eval(ValueEval[] args, int srcRowIndex, int srcColumnIndex) throws EvaluationException {
			double rate= NumericFunction.singleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
			double per = NumericFunction.singleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
			double nper = NumericFunction.singleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
			if (per > nper) {
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			} else {
				double pv = NumericFunction.singleOperandEvaluate(args[3], srcRowIndex, srcColumnIndex);
				double fv = (args.length > 4 && args[4] != null) ?
						NumericFunction.singleOperandEvaluate(args[4], srcRowIndex, srcColumnIndex) : 0.0;
				int type = args.length > 5 ? 
						(int) NumericFunction.singleOperandEvaluate(args[5], srcRowIndex, srcColumnIndex) : 0;
				return ppmt(rate, per, nper, pv, fv, type);
			}
		}
	};
	private static double ppmt(double rate, double per, double nper, double pv, double fv, int type) {
		return pmt(rate, nper,pv, fv, type) - ipmt(rate, per, nper, pv, fv, type);
	}
	private static double pmt(double rate, double nper, double pv, double fv, int type) {
		double pmt = 0.0;
		if(rate == 0) {
			pmt = (fv + pv)/nper;
		} else {
			double term = Math.pow(1.0 + rate, nper);
			if (type == 1)
				pmt = (fv * rate / (term - 1.0) + pv * rate / (1.0 - 1.0 / term)) / (1.0+rate);
			else
			    pmt = fv * rate / (term - 1.0)  + pv * rate / (1.0 - 1.0 / term);
		}
		return -pmt;
	}
	private static double ipmt(double rate, double per, double nper, double pv, double fv, int type) {
		double ipmt = 0;
		
		// first period, different PayType has different value.
		if(per == 1) {
			ipmt =  type == 0 ?	-pv : 0.0;
		} else{
			double pmt = pmt(rate, nper, pv, fv, type);
			if(type == 0)
			{
				ipmt = GetZw(rate, per-1.0, pmt, pv, 0);
			}
			else if(type == 1)
			{
				ipmt = (GetZw(rate, per-2, pmt, pv, 1) - pmt);
			}
		}
		
		return ipmt * rate;

	}
	private static double GetZw(double rate, double nper, double pmt, double pv, int type) {
		double Zw = 0;
		if (rate == 0.0)
			Zw = pv + pmt * nper;
		else {
			double term = Math.pow(1.0 + rate, nper);
			if (type > 0.0)
				Zw = pv * term + pmt*(1.0 + rate)*(term - 1.0)/rate;
			else
				Zw = pv * term + pmt*(term - 1.0)/rate;
		}
		return -Zw;
	}
	
	public static final Function SLN = new FinanceFunction() {
		@Override
		protected double evaluate(double cost, double salvage, double life, double arg3, boolean type) throws EvaluationException {
			if (life == 0)
				throw new EvaluationException(ErrorEval.DIV_ZERO);
			return (cost-salvage)/life;
		}
	};
	
	public static final Function SYD = new FinanceFunction() {
		@Override
		protected double evaluate(double cost, double salvage, double life, double per, boolean type) throws EvaluationException {
			if(life <= 0 || per <= 0)
				throw new EvaluationException(ErrorEval.NUM_ERROR);
			return ((cost - salvage)*(life - per + 1)*2)/(life*(life + 1));
		}
	};
	
}
