summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go147
-rw-r--r--calc_test.go36
2 files changed, 183 insertions, 0 deletions
diff --git a/calc.go b/calc.go
index b8cb645..d63bb3f 100644
--- a/calc.go
+++ b/calc.go
@@ -261,10 +261,12 @@ type formulaFuncs struct {
// Supported formula functions:
//
// ABS
+// ACCRINTM
// ACOS
// ACOSH
// ACOT
// ACOTH
+// AMORDEGRC
// AND
// ARABIC
// ASIN
@@ -8312,6 +8314,151 @@ func (fn *formulaFuncs) ENCODEURL(argsList *list.List) formulaArg {
// Financial Functions
+// ACCRINTM function returns the accrued interest for a security that pays
+// interest at maturity. The syntax of the function is:
+//
+// ACCRINTM(issue,settlement,rate,[par],[basis])
+//
+func (fn *formulaFuncs) ACCRINTM(argsList *list.List) formulaArg {
+ if argsList.Len() != 4 && argsList.Len() != 5 {
+ return newErrorFormulaArg(formulaErrorVALUE, "ACCRINTM requires 4 or 5 arguments")
+ }
+ args := list.New().Init()
+ args.PushBack(argsList.Front().Value.(formulaArg))
+ issue := fn.DATEVALUE(args)
+ if issue.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+ }
+ args.Init()
+ args.PushBack(argsList.Front().Next().Value.(formulaArg))
+ settlement := fn.DATEVALUE(args)
+ if settlement.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+ }
+ if settlement.Number < issue.Number {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ rate := argsList.Front().Next().Next().Value.(formulaArg).ToNumber()
+ par := argsList.Front().Next().Next().Next().Value.(formulaArg).ToNumber()
+ if rate.Type != ArgNumber || par.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ if par.Number <= 0 {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ basis := newNumberFormulaArg(0)
+ if argsList.Len() == 5 {
+ if basis = argsList.Back().Value.(formulaArg).ToNumber(); basis.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ }
+ frac := yearFrac(issue.Number, settlement.Number, int(basis.Number))
+ if frac.Type != ArgNumber {
+ return frac
+ }
+ return newNumberFormulaArg(frac.Number * rate.Number * par.Number)
+}
+
+// prepareAmorArgs checking and prepare arguments for the formula functions
+// AMORDEGRC and AMORLINC.
+func (fn *formulaFuncs) prepareAmorArgs(name string, argsList *list.List) formulaArg {
+ cost := argsList.Front().Value.(formulaArg).ToNumber()
+ if cost.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires cost to be number argument", name))
+ }
+ if cost.Number < 0 {
+ return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires cost >= 0", name))
+ }
+ args := list.New().Init()
+ args.PushBack(argsList.Front().Next().Value.(formulaArg))
+ datePurchased := fn.DATEVALUE(args)
+ if datePurchased.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+ }
+ args.Init()
+ args.PushBack(argsList.Front().Next().Next().Value.(formulaArg))
+ firstPeriod := fn.DATEVALUE(args)
+ if firstPeriod.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+ }
+ if firstPeriod.Number < datePurchased.Number {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ salvage := argsList.Front().Next().Next().Next().Value.(formulaArg).ToNumber()
+ if salvage.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ if salvage.Number < 0 || salvage.Number > cost.Number {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ period := argsList.Front().Next().Next().Next().Next().Value.(formulaArg).ToNumber()
+ if period.Type != ArgNumber || period.Number < 0 {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ rate := argsList.Front().Next().Next().Next().Next().Next().Value.(formulaArg).ToNumber()
+ if rate.Type != ArgNumber || rate.Number < 0 {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ basis := newNumberFormulaArg(0)
+ if argsList.Len() == 7 {
+ if basis = argsList.Back().Value.(formulaArg).ToNumber(); basis.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ }
+ return newListFormulaArg([]formulaArg{cost, datePurchased, firstPeriod, salvage, period, rate, basis})
+}
+
+// AMORDEGRC function is provided for users of the French accounting system.
+// The function calculates the prorated linear depreciation of an asset for a
+// specified accounting period. The syntax of the function is:
+//
+// AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate,[basis])
+//
+func (fn *formulaFuncs) AMORDEGRC(argsList *list.List) formulaArg {
+ if argsList.Len() != 6 && argsList.Len() != 7 {
+ return newErrorFormulaArg(formulaErrorVALUE, "AMORDEGRC requires 6 or 7 arguments")
+ }
+ args := fn.prepareAmorArgs("AMORDEGRC", argsList)
+ if args.Type != ArgList {
+ return args
+ }
+ cost, datePurchased, firstPeriod, salvage, period, rate, basis := args.List[0], args.List[1], args.List[2], args.List[3], args.List[4], args.List[5], args.List[6]
+ if rate.Number >= 0.5 {
+ return newErrorFormulaArg(formulaErrorNUM, "AMORDEGRC requires rate to be < 0.5")
+ }
+ assetsLife, amorCoeff := 1/rate.Number, 2.5
+ if assetsLife < 3 {
+ amorCoeff = 1
+ } else if assetsLife < 5 {
+ amorCoeff = 1.5
+ } else if assetsLife <= 6 {
+ amorCoeff = 2
+ }
+ rate.Number *= amorCoeff
+ frac := yearFrac(datePurchased.Number, firstPeriod.Number, int(basis.Number))
+ if frac.Type != ArgNumber {
+ return frac
+ }
+ nRate := float64(int((frac.Number * cost.Number * rate.Number) + 0.5))
+ cost.Number -= nRate
+ rest := cost.Number - salvage.Number
+ for n := 0; n < int(period.Number); n++ {
+ nRate = float64(int((cost.Number * rate.Number) + 0.5))
+ rest -= nRate
+ if rest < 0 {
+ switch int(period.Number) - n {
+ case 0:
+ case 1:
+ return newNumberFormulaArg(float64(int((cost.Number * 0.5) + 0.5)))
+ default:
+ return newNumberFormulaArg(0)
+ }
+ }
+ cost.Number -= nRate
+ }
+ return newNumberFormulaArg(nRate)
+}
+
// CUMIPMT function calculates the cumulative interest paid on a loan or
// investment, between two specified periods. The syntax of the function is:
//
diff --git a/calc_test.go b/calc_test.go
index 89f54ea..6d16ce9 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -1232,6 +1232,16 @@ func TestCalcCellValue(t *testing.T) {
// ENCODEURL
"=ENCODEURL(\"https://xuri.me/excelize/en/?q=Save As\")": "https%3A%2F%2Fxuri.me%2Fexcelize%2Fen%2F%3Fq%3DSave%20As",
// Financial Functions
+ // ACCRINTM
+ "=ACCRINTM(\"01/01/2012\",\"12/31/2012\",8%,10000)": "800",
+ "=ACCRINTM(\"01/01/2012\",\"12/31/2012\",8%,10000,3)": "800",
+ // AMORDEGRC
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",20,1,20%)": "42",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",20,1,20%,4)": "42",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",20,1,40%,4)": "42",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",20,1,25%,4)": "41",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",109,1,25%,4)": "54",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",110,2,25%,4)": "0",
// CUMIPMT
"=CUMIPMT(0.05/12,60,50000,1,12,0)": "-2294.97753732664",
"=CUMIPMT(0.05/12,60,50000,13,24,0)": "-1833.1000665738893",
@@ -2291,6 +2301,32 @@ func TestCalcCellValue(t *testing.T) {
// ENCODEURL
"=ENCODEURL()": "ENCODEURL requires 1 argument",
// Financial Functions
+ // ACCRINTM
+ "=ACCRINTM()": "ACCRINTM requires 4 or 5 arguments",
+ "=ACCRINTM(\"\",\"01/01/2012\",8%,10000)": "#VALUE!",
+ "=ACCRINTM(\"01/01/2012\",\"\",8%,10000)": "#VALUE!",
+ "=ACCRINTM(\"12/31/2012\",\"01/01/2012\",8%,10000)": "#NUM!",
+ "=ACCRINTM(\"01/01/2012\",\"12/31/2012\",\"\",10000)": "#NUM!",
+ "=ACCRINTM(\"01/01/2012\",\"12/31/2012\",8%,\"\",10000)": "#NUM!",
+ "=ACCRINTM(\"01/01/2012\",\"12/31/2012\",8%,-1,10000)": "#NUM!",
+ "=ACCRINTM(\"01/01/2012\",\"12/31/2012\",8%,10000,\"\")": "#NUM!",
+ "=ACCRINTM(\"01/01/2012\",\"12/31/2012\",8%,10000,5)": "invalid basis",
+ // AMORDEGRC
+ "=AMORDEGRC()": "AMORDEGRC requires 6 or 7 arguments",
+ "=AMORDEGRC(\"\",\"01/01/2015\",\"09/30/2015\",20,1,20%)": "AMORDEGRC requires cost to be number argument",
+ "=AMORDEGRC(-1,\"01/01/2015\",\"09/30/2015\",20,1,20%)": "AMORDEGRC requires cost >= 0",
+ "=AMORDEGRC(150,\"\",\"09/30/2015\",20,1,20%)": "#VALUE!",
+ "=AMORDEGRC(150,\"01/01/2015\",\"\",20,1,20%)": "#VALUE!",
+ "=AMORDEGRC(150,\"09/30/2015\",\"01/01/2015\",20,1,20%)": "#NUM!",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",\"\",1,20%)": "#NUM!",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",-1,1,20%)": "#NUM!",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",20,\"\",20%)": "#NUM!",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",20,-1,20%)": "#NUM!",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",20,1,\"\")": "#NUM!",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",20,1,-1)": "#NUM!",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",20,1,20%,\"\")": "#NUM!",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",20,1,50%)": "AMORDEGRC requires rate to be < 0.5",
+ "=AMORDEGRC(150,\"01/01/2015\",\"09/30/2015\",20,1,20%,5)": "invalid basis",
// CUMIPMT
"=CUMIPMT()": "CUMIPMT requires 6 arguments",
"=CUMIPMT(0,0,0,0,0,2)": "#N/A",
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-20 07:00:22 +0000