ref #65: new formula function XNPV

2 files changed, 106 insertions, 0 deletions

diff --git a/calc.go b/calc.go
index a58a96d..225f77b 100644
--- a/calc.go
+++ b/calc.go
@@ -591,6 +591,7 @@ type formulaFuncs struct {
 //    WEEKDAY
 //    WEIBULL
 //    WEIBULL.DIST
+//    XNPV
 //    XOR
 //    YEAR
 //    YEARFRAC
@@ -10984,6 +10985,71 @@ func (fn *formulaFuncs) TBILLYIELD(argsList *list.List) formulaArg {
 	return newNumberFormulaArg(((100 - pr.Number) / pr.Number) * (360 / dsm))
 }
 
+// prepareXArgs prepare arguments for the formula function XIRR and XNPV.
+func (fn *formulaFuncs) prepareXArgs(name string, values, dates formulaArg) (valuesArg, datesArg []float64, err formulaArg) {
+	for _, arg := range values.ToList() {
+		if numArg := arg.ToNumber(); numArg.Type == ArgNumber {
+			valuesArg = append(valuesArg, numArg.Number)
+			continue
+		}
+		err = newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+		return
+	}
+	if len(valuesArg) < 2 {
+		err = newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+		return
+	}
+	args, date := list.New(), 0.0
+	for _, arg := range dates.ToList() {
+		args.Init()
+		args.PushBack(arg)
+		dateValue := fn.DATEVALUE(args)
+		if dateValue.Type != ArgNumber {
+			err = newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+			return
+		}
+		if dateValue.Number < date {
+			err = newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+			return
+		}
+		datesArg = append(datesArg, dateValue.Number)
+		date = dateValue.Number
+	}
+	if len(valuesArg) != len(datesArg) {
+		err = newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+		return
+	}
+	err = newEmptyFormulaArg()
+	return
+}
+
+// XNPV function calculates the Net Present Value for a schedule of cash flows
+// that is not necessarily periodic. The syntax of the function is:
+//
+//    XNPV(rate,values,dates)
+//
+func (fn *formulaFuncs) XNPV(argsList *list.List) formulaArg {
+	if argsList.Len() != 3 {
+		return newErrorFormulaArg(formulaErrorVALUE, "XNPV requires 3 arguments")
+	}
+	rate := argsList.Front().Value.(formulaArg).ToNumber()
+	if rate.Type != ArgNumber {
+		return rate
+	}
+	if rate.Number <= 0 {
+		return newErrorFormulaArg(formulaErrorVALUE, "XNPV requires rate > 0")
+	}
+	values, dates, err := fn.prepareXArgs("XNPV", argsList.Front().Next().Value.(formulaArg), argsList.Back().Value.(formulaArg))
+	if err.Type != ArgEmpty {
+		return err
+	}
+	date1, xnpv := dates[0], 0.0
+	for idx, value := range values {
+		xnpv += value / math.Pow(1+rate.Number, (dates[idx]-date1)/365)
+	}
+	return newNumberFormulaArg(xnpv)
+}
+
 // YIELDDISC function calculates the annual yield of a discounted security.
 // The syntax of the function is:
 //
diff --git a/calc_test.go b/calc_test.go
index c621f3b..6420715 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -3391,6 +3391,46 @@ func TestCalcMIRR(t *testing.T) {
 	}
 }
 
+func TestCalcXNPV(t *testing.T) {
+	cellData := [][]interface{}{{nil, 0.05},
+		{"01/01/2016", -10000, nil},
+		{"02/01/2016", 2000},
+		{"05/01/2016", 2400},
+		{"07/01/2016", 2900},
+		{"11/01/2016", 3500},
+		{"01/01/2017", 4100},
+		{},
+		{"02/01/2016"},
+		{"01/01/2016"}}
+	f := prepareCalcData(cellData)
+	formulaList := map[string]string{
+		"=XNPV(B1,B2:B7,A2:A7)": "4447.938009440515",
+	}
+	for formula, expected := range formulaList {
+		assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula))
+		result, err := f.CalcCellValue("Sheet1", "C1")
+		assert.NoError(t, err, formula)
+		assert.Equal(t, expected, result, formula)
+	}
+	calcError := map[string]string{
+		"=XNPV()":                 "XNPV requires 3 arguments",
+		"=XNPV(\"\",B2:B7,A2:A7)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=XNPV(0,B2:B7,A2:A7)":    "XNPV requires rate > 0",
+		"=XNPV(B1,\"\",A2:A7)":    "#NUM!",
+		"=XNPV(B1,B2:B7,\"\")":    "#NUM!",
+		"=XNPV(B1,B2:B7,C2:C7)":   "#NUM!",
+		"=XNPV(B1,B2,A2)":         "#NUM!",
+		"=XNPV(B1,B2:B3,A2:A5)":   "#NUM!",
+		"=XNPV(B1,B2:B3,A9:A10)":  "#VALUE!",
+	}
+	for formula, expected := range calcError {
+		assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula))
+		result, err := f.CalcCellValue("Sheet1", "C1")
+		assert.EqualError(t, err, expected, formula)
+		assert.Equal(t, "", result, formula)
+	}
+}
+
 func TestCalcMATCH(t *testing.T) {
 	f := NewFile()
 	for cell, row := range map[string][]interface{}{