ref #65: new formula function VDB

2 files changed, 165 insertions, 0 deletions

diff --git a/calc.go b/calc.go
index 21ece5d..381f11c 100644
--- a/calc.go
+++ b/calc.go
@@ -592,6 +592,7 @@ type formulaFuncs struct {
 //    VARA
 //    VARP
 //    VARPA
+//    VDB
 //    VLOOKUP
 //    WEEKDAY
 //    WEIBULL
@@ -11145,6 +11146,143 @@ func (fn *formulaFuncs) TBILLYIELD(argsList *list.List) formulaArg {
 	return newNumberFormulaArg(((100 - pr.Number) / pr.Number) * (360 / dsm))
 }
 
+// prepareVdbArgs checking and prepare arguments for the formula functions
+// VDB.
+func (fn *formulaFuncs) prepareVdbArgs(argsList *list.List) formulaArg {
+	cost := argsList.Front().Value.(formulaArg).ToNumber()
+	if cost.Type != ArgNumber {
+		return cost
+	}
+	if cost.Number < 0 {
+		return newErrorFormulaArg(formulaErrorNUM, "VDB requires cost >= 0")
+	}
+	salvage := argsList.Front().Next().Value.(formulaArg).ToNumber()
+	if salvage.Type != ArgNumber {
+		return salvage
+	}
+	if salvage.Number < 0 {
+		return newErrorFormulaArg(formulaErrorNUM, "VDB requires salvage >= 0")
+	}
+	life := argsList.Front().Next().Next().Value.(formulaArg).ToNumber()
+	if life.Type != ArgNumber {
+		return life
+	}
+	if life.Number <= 0 {
+		return newErrorFormulaArg(formulaErrorNUM, "VDB requires life > 0")
+	}
+	startPeriod := argsList.Front().Next().Next().Next().Value.(formulaArg).ToNumber()
+	if startPeriod.Type != ArgNumber {
+		return startPeriod
+	}
+	if startPeriod.Number < 0 {
+		return newErrorFormulaArg(formulaErrorNUM, "VDB requires start_period > 0")
+	}
+	endPeriod := argsList.Front().Next().Next().Next().Next().Value.(formulaArg).ToNumber()
+	if endPeriod.Type != ArgNumber {
+		return endPeriod
+	}
+	if startPeriod.Number > endPeriod.Number {
+		return newErrorFormulaArg(formulaErrorNUM, "VDB requires start_period <= end_period")
+	}
+	if endPeriod.Number > life.Number {
+		return newErrorFormulaArg(formulaErrorNUM, "VDB requires end_period <= life")
+	}
+	factor := newNumberFormulaArg(2)
+	if argsList.Len() > 5 {
+		if factor = argsList.Front().Next().Next().Next().Next().Next().Value.(formulaArg).ToNumber(); factor.Type != ArgNumber {
+			return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+		}
+		if factor.Number < 0 {
+			return newErrorFormulaArg(formulaErrorVALUE, "VDB requires factor >= 0")
+		}
+	}
+	return newListFormulaArg([]formulaArg{cost, salvage, life, startPeriod, endPeriod, factor})
+}
+
+// vdb is a part of implementation of the formula function VDB.
+func (fn *formulaFuncs) vdb(cost, salvage, life, life1, period, factor formulaArg) formulaArg {
+	var ddb, vdb, sln, term float64
+	endInt, cs, nowSln := math.Ceil(period.Number), cost.Number-salvage.Number, false
+	ddbArgs := list.New()
+	for i := 1.0; i <= endInt; i++ {
+		if !nowSln {
+			ddbArgs.Init()
+			ddbArgs.PushBack(cost)
+			ddbArgs.PushBack(salvage)
+			ddbArgs.PushBack(life)
+			ddbArgs.PushBack(newNumberFormulaArg(i))
+			ddbArgs.PushBack(factor)
+			ddb = fn.DDB(ddbArgs).Number
+			sln = cs / (life1.Number - i + 1)
+			if sln > ddb {
+				term = sln
+				nowSln = true
+			} else {
+				term = ddb
+				cs -= ddb
+			}
+		} else {
+			term = sln
+		}
+		if i == endInt {
+			term *= period.Number + 1 - endInt
+		}
+		vdb += term
+	}
+	return newNumberFormulaArg(vdb)
+}
+
+// VDB function calculates the depreciation of an asset, using the Double
+// Declining Balance Method, or another specified depreciation rate, for a
+// specified period (including partial periods). The syntax of the function
+// is:
+//
+//    VDB(cost,salvage,life,start_period,end_period,[factor],[no_switch])
+//
+func (fn *formulaFuncs) VDB(argsList *list.List) formulaArg {
+	if argsList.Len() < 5 || argsList.Len() > 7 {
+		return newErrorFormulaArg(formulaErrorVALUE, "VDB requires 5 or 7 arguments")
+	}
+	args := fn.prepareVdbArgs(argsList)
+	if args.Type != ArgList {
+		return args
+	}
+	cost, salvage, life, startPeriod, endPeriod, factor := args.List[0], args.List[1], args.List[2], args.List[3], args.List[4], args.List[5]
+	noSwitch := newBoolFormulaArg(false)
+	if argsList.Len() > 6 {
+		if noSwitch = argsList.Back().Value.(formulaArg).ToBool(); noSwitch.Type != ArgNumber {
+			return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+		}
+	}
+	startInt, endInt, vdb, ddbArgs := math.Floor(startPeriod.Number), math.Ceil(endPeriod.Number), newNumberFormulaArg(0), list.New()
+	if noSwitch.Number == 1 {
+		for i := startInt + 1; i <= endInt; i++ {
+			ddbArgs.Init()
+			ddbArgs.PushBack(cost)
+			ddbArgs.PushBack(salvage)
+			ddbArgs.PushBack(life)
+			ddbArgs.PushBack(newNumberFormulaArg(i))
+			ddbArgs.PushBack(factor)
+			term := fn.DDB(ddbArgs)
+			if i == startInt+1 {
+				term.Number *= math.Min(endPeriod.Number, startInt+1) - startPeriod.Number
+			} else if i == endInt {
+				term.Number *= endPeriod.Number + 1 - endInt
+			}
+			vdb.Number += term.Number
+		}
+		return vdb
+	}
+	life1, part := life, 0.0
+	if startPeriod.Number != math.Floor(startPeriod.Number) && factor.Number > 1.0 && startPeriod.Number >= life.Number/2.0 {
+		part = startPeriod.Number - life.Number/2.0
+		startPeriod.Number = life.Number / 2.0
+		endPeriod.Number -= part
+	}
+	cost.Number -= fn.vdb(cost, salvage, life, life1, startPeriod, factor).Number
+	return fn.vdb(cost, salvage, life, newNumberFormulaArg(life.Number-startPeriod.Number), newNumberFormulaArg(endPeriod.Number-startPeriod.Number), factor)
+}
+
 // 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() {
diff --git a/calc_test.go b/calc_test.go
index 77c6e30..c9896c7 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -1537,6 +1537,17 @@ func TestCalcCellValue(t *testing.T) {
 		"=TBILLPRICE(\"02/01/2017\",\"06/30/2017\",2.75%)": "98.86180555555556",
 		// TBILLYIELD
 		"=TBILLYIELD(\"02/01/2017\",\"06/30/2017\",99)": "0.024405125076266",
+		// VDB
+		"=VDB(10000,1000,5,0,1)":           "4000",
+		"=VDB(10000,1000,5,1,3)":           "3840",
+		"=VDB(10000,1000,5,3,5)":           "1160",
+		"=VDB(10000,1000,5,3,5,0.2,FALSE)": "3600",
+		"=VDB(10000,1000,5,3,5,0.2,TRUE)":  "693.633024",
+		"=VDB(24000,3000,10,0,0.875,2)":    "4200",
+		"=VDB(24000,3000,10,0.1,1)":        "4233.599999999999",
+		"=VDB(24000,3000,10,0.1,1,1)":      "2138.3999999999996",
+		"=VDB(24000,3000,100,50,100,1)":    "10377.294418465235",
+		"=VDB(24000,3000,100,50,100,2)":    "5740.072322090805",
 		// YIELDDISC
 		"=YIELDDISC(\"01/01/2017\",\"06/30/2017\",97,100)":   "0.0622012325059031",
 		"=YIELDDISC(\"01/01/2017\",\"06/30/2017\",97,100,0)": "0.0622012325059031",
@@ -3075,6 +3086,22 @@ func TestCalcCellValue(t *testing.T) {
 		"=TBILLYIELD(\"01/01/2017\",\"06/30/2017\",0)":    "#NUM!",
 		"=TBILLYIELD(\"01/01/2017\",\"06/30/2018\",2.5%)": "#NUM!",
 		"=TBILLYIELD(\"06/30/2017\",\"01/01/2017\",2.5%)": "#NUM!",
+		// VDB
+		"=VDB()":                          "VDB requires 5 or 7 arguments",
+		"=VDB(-1,1000,5,0,1)":             "VDB requires cost >= 0",
+		"=VDB(10000,-1,5,0,1)":            "VDB requires salvage >= 0",
+		"=VDB(10000,1000,0,0,1)":          "VDB requires life > 0",
+		"=VDB(10000,1000,5,-1,1)":         "VDB requires start_period > 0",
+		"=VDB(10000,1000,5,2,1)":          "VDB requires start_period <= end_period",
+		"=VDB(10000,1000,5,0,6)":          "VDB requires end_period <= life",
+		"=VDB(10000,1000,5,0,1,-0.2)":     "VDB requires factor >= 0",
+		"=VDB(\"\",1000,5,0,1)":           "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=VDB(10000,\"\",5,0,1)":          "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=VDB(10000,1000,\"\",0,1)":       "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=VDB(10000,1000,5,\"\",1)":       "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=VDB(10000,1000,5,0,\"\")":       "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=VDB(10000,1000,5,0,1,\"\")":     "#NUM!",
+		"=VDB(10000,1000,5,0,1,0.2,\"\")": "#NUM!",
 		// YIELDDISC
 		"=YIELDDISC()": "YIELDDISC requires 4 or 5 arguments",
 		"=YIELDDISC(\"\",\"06/30/2017\",97,100,0)":              "#VALUE!",