diff options
| -rw-r--r-- | calc.go | 81 | ||||
| -rw-r--r-- | calc_test.go | 23 |
2 files changed, 104 insertions, 0 deletions
@@ -474,6 +474,7 @@ type formulaFuncs struct { // ROUNDUP // ROW // ROWS +// RRI // SEC // SECH // SHEET @@ -481,6 +482,7 @@ type formulaFuncs struct { // SIN // SINH // SKEW +// SLN // SMALL // SQRT // SQRTPI @@ -491,6 +493,7 @@ type formulaFuncs struct { // SUM // SUMIF // SUMSQ +// SYD // T // TAN // TANH @@ -9317,3 +9320,81 @@ func (fn *formulaFuncs) PMT(argsList *list.List) formulaArg { func (fn *formulaFuncs) PPMT(argsList *list.List) formulaArg { return fn.ipmt("PPMT", argsList) } + +// RRI function calculates the equivalent interest rate for an investment with +// specified present value, future value and duration. The syntax of the +// function is: +// +// RRI(nper,pv,fv) +// +func (fn *formulaFuncs) RRI(argsList *list.List) formulaArg { + if argsList.Len() != 3 { + return newErrorFormulaArg(formulaErrorVALUE, "RRI requires 3 arguments") + } + nper := argsList.Front().Value.(formulaArg).ToNumber() + pv := argsList.Front().Next().Value.(formulaArg).ToNumber() + fv := argsList.Back().Value.(formulaArg).ToNumber() + if nper.Type != ArgNumber || pv.Type != ArgNumber || fv.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + if nper.Number <= 0 { + return newErrorFormulaArg(formulaErrorNUM, "RRI requires nper argument to be > 0") + } + if pv.Number <= 0 { + return newErrorFormulaArg(formulaErrorNUM, "RRI requires pv argument to be > 0") + } + if fv.Number < 0 { + return newErrorFormulaArg(formulaErrorNUM, "RRI requires fv argument to be >= 0") + } + return newNumberFormulaArg(math.Pow(fv.Number/pv.Number, 1/nper.Number) - 1) +} + +// SLN function calculates the straight line depreciation of an asset for one +// period. The syntax of the function is: +// +// SLN(cost,salvage,life) +// +func (fn *formulaFuncs) SLN(argsList *list.List) formulaArg { + if argsList.Len() != 3 { + return newErrorFormulaArg(formulaErrorVALUE, "SLN requires 3 arguments") + } + cost := argsList.Front().Value.(formulaArg).ToNumber() + salvage := argsList.Front().Next().Value.(formulaArg).ToNumber() + life := argsList.Back().Value.(formulaArg).ToNumber() + if cost.Type != ArgNumber || salvage.Type != ArgNumber || life.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + if life.Number == 0 { + return newErrorFormulaArg(formulaErrorNUM, "SLN requires life argument to be > 0") + } + return newNumberFormulaArg((cost.Number - salvage.Number) / life.Number) +} + +// SYD function calculates the sum-of-years' digits depreciation for a +// specified period in the lifetime of an asset. The syntax of the function +// is: +// +// SYD(cost,salvage,life,per) +// +func (fn *formulaFuncs) SYD(argsList *list.List) formulaArg { + if argsList.Len() != 4 { + return newErrorFormulaArg(formulaErrorVALUE, "SYD requires 4 arguments") + } + cost := argsList.Front().Value.(formulaArg).ToNumber() + salvage := argsList.Front().Next().Value.(formulaArg).ToNumber() + life := argsList.Back().Prev().Value.(formulaArg).ToNumber() + per := argsList.Back().Value.(formulaArg).ToNumber() + if cost.Type != ArgNumber || salvage.Type != ArgNumber || life.Type != ArgNumber || per.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + if life.Number <= 0 { + return newErrorFormulaArg(formulaErrorNUM, "SYD requires life argument to be > 0") + } + if per.Number <= 0 { + return newErrorFormulaArg(formulaErrorNUM, "SYD requires per argument to be > 0") + } + if per.Number > life.Number { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + return newNumberFormulaArg(((cost.Number - salvage.Number) * (life.Number - per.Number + 1) * 2) / (life.Number * (life.Number + 1))) +} diff --git a/calc_test.go b/calc_test.go index 144811c..fb5876c 100644 --- a/calc_test.go +++ b/calc_test.go @@ -1323,6 +1323,13 @@ func TestCalcCellValue(t *testing.T) { // PPMT "=PPMT(0.05/12,2,60,50000)": "-738.2918003208238", "=PPMT(0.035/4,2,8,0,5000,1)": "-606.1094824182949", + // RRI + "=RRI(10,10000,15000)": "0.0413797439924106", + // SLN + "=SLN(10000,1000,5)": "1800", + // SYD + "=SYD(10000,1000,5,1)": "3000", + "=SYD(10000,1000,5,2)": "2400", } for formula, expected := range mathCalc { f := prepareCalcData(cellData) @@ -2516,6 +2523,22 @@ func TestCalcCellValue(t *testing.T) { "=PPMT(0,0,0,\"\",0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", "=PPMT(0,0,0,0,\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", "=PPMT(0,0,0,0,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // RRI + "=RRI()": "RRI requires 3 arguments", + "=RRI(\"\",\"\",\"\")": "#NUM!", + "=RRI(0,10000,15000)": "RRI requires nper argument to be > 0", + "=RRI(10,0,15000)": "RRI requires pv argument to be > 0", + "=RRI(10,10000,-1)": "RRI requires fv argument to be >= 0", + // SLN + "=SLN()": "SLN requires 3 arguments", + "=SLN(\"\",\"\",\"\")": "#NUM!", + "=SLN(10000,1000,0)": "SLN requires life argument to be > 0", + // SYD + "=SYD()": "SYD requires 4 arguments", + "=SYD(\"\",\"\",\"\",\"\")": "#NUM!", + "=SYD(10000,1000,0,1)": "SYD requires life argument to be > 0", + "=SYD(10000,1000,5,0)": "SYD requires per argument to be > 0", + "=SYD(10000,1000,1,5)": "#NUM!", } for formula, expected := range mathCalcError { f := prepareCalcData(cellData) |