summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go138
-rw-r--r--calc_test.go27
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!",
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-16 09:48:27 +0000