diff options
| -rw-r--r-- | calc.go | 246 | ||||
| -rw-r--r-- | calc_test.go | 74 |
2 files changed, 320 insertions, 0 deletions
@@ -265,11 +265,16 @@ var tokenPriority = map[string]int{ // CUMPRINC // DATE // DATEDIF +// DB +// DDB // DEC2BIN // DEC2HEX // DEC2OCT // DECIMAL // DEGREES +// DOLLARDE +// DOLLARFR +// EFFECT // ENCODEURL // EVEN // EXACT @@ -332,6 +337,7 @@ var tokenPriority = map[string]int{ // ISODD // ISTEXT // ISO.CEILING +// ISPMT // KURT // LARGE // LCM @@ -357,6 +363,7 @@ var tokenPriority = map[string]int{ // MUNIT // N // NA +// NOMINAL // NORM.DIST // NORMDIST // NORM.INV @@ -7237,6 +7244,185 @@ func (fn *formulaFuncs) cumip(name string, argsList *list.List) formulaArg { return newNumberFormulaArg(num) } +// DB function calculates the depreciation of an asset, using the Fixed +// Declining Balance Method, for each period of the asset's lifetime. The +// syntax of the function is: +// +// DB(cost,salvage,life,period,[month]) +// +func (fn *formulaFuncs) DB(argsList *list.List) formulaArg { + if argsList.Len() < 4 { + return newErrorFormulaArg(formulaErrorVALUE, "DB requires at least 4 arguments") + } + if argsList.Len() > 5 { + return newErrorFormulaArg(formulaErrorVALUE, "DB allows at most 5 arguments") + } + cost := argsList.Front().Value.(formulaArg).ToNumber() + if cost.Type != ArgNumber { + return cost + } + salvage := argsList.Front().Next().Value.(formulaArg).ToNumber() + if salvage.Type != ArgNumber { + return salvage + } + life := argsList.Front().Next().Next().Value.(formulaArg).ToNumber() + if life.Type != ArgNumber { + return life + } + period := argsList.Front().Next().Next().Next().Value.(formulaArg).ToNumber() + if period.Type != ArgNumber { + return period + } + month := newNumberFormulaArg(12) + if argsList.Len() == 5 { + if month = argsList.Back().Value.(formulaArg).ToNumber(); month.Type != ArgNumber { + return month + } + } + if cost.Number == 0 { + return newNumberFormulaArg(0) + } + if (cost.Number <= 0) || ((salvage.Number / cost.Number) < 0) || (life.Number <= 0) || (period.Number < 1) || (month.Number < 1) { + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) + } + dr := 1 - math.Pow(salvage.Number/cost.Number, 1/life.Number) + dr = math.Round(dr*1000) / 1000 + pd, depreciation := 0.0, 0.0 + for per := 1; per <= int(period.Number); per++ { + if per == 1 { + depreciation = cost.Number * dr * month.Number / 12 + } else if per == int(life.Number+1) { + depreciation = (cost.Number - pd) * dr * (12 - month.Number) / 12 + } else { + depreciation = (cost.Number - pd) * dr + } + pd += depreciation + } + return newNumberFormulaArg(depreciation) +} + +// DDB function calculates the depreciation of an asset, using the Double +// Declining Balance Method, or another specified depreciation rate. The +// syntax of the function is: +// +// DDB(cost,salvage,life,period,[factor]) +// +func (fn *formulaFuncs) DDB(argsList *list.List) formulaArg { + if argsList.Len() < 4 { + return newErrorFormulaArg(formulaErrorVALUE, "DDB requires at least 4 arguments") + } + if argsList.Len() > 5 { + return newErrorFormulaArg(formulaErrorVALUE, "DDB allows at most 5 arguments") + } + cost := argsList.Front().Value.(formulaArg).ToNumber() + if cost.Type != ArgNumber { + return cost + } + salvage := argsList.Front().Next().Value.(formulaArg).ToNumber() + if salvage.Type != ArgNumber { + return salvage + } + life := argsList.Front().Next().Next().Value.(formulaArg).ToNumber() + if life.Type != ArgNumber { + return life + } + period := argsList.Front().Next().Next().Next().Value.(formulaArg).ToNumber() + if period.Type != ArgNumber { + return period + } + factor := newNumberFormulaArg(2) + if argsList.Len() == 5 { + if factor = argsList.Back().Value.(formulaArg).ToNumber(); factor.Type != ArgNumber { + return factor + } + } + if cost.Number == 0 { + return newNumberFormulaArg(0) + } + if (cost.Number <= 0) || ((salvage.Number / cost.Number) < 0) || (life.Number <= 0) || (period.Number < 1) || (factor.Number <= 0.0) || (period.Number > life.Number) { + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) + } + pd, depreciation := 0.0, 0.0 + for per := 1; per <= int(period.Number); per++ { + depreciation = math.Min((cost.Number-pd)*(factor.Number/life.Number), (cost.Number - salvage.Number - pd)) + pd += depreciation + } + return newNumberFormulaArg(depreciation) +} + +// DOLLARDE function converts a dollar value in fractional notation, into a +// dollar value expressed as a decimal. The syntax of the function is: +// +// DOLLARDE(fractional_dollar,fraction) +// +func (fn *formulaFuncs) DOLLARDE(argsList *list.List) formulaArg { + return fn.dollar("DOLLARDE", argsList) +} + +// DOLLARFR function converts a dollar value in decimal notation, into a +// dollar value that is expressed in fractional notation. The syntax of the +// function is: +// +// DOLLARFR(decimal_dollar,fraction) +// +func (fn *formulaFuncs) DOLLARFR(argsList *list.List) formulaArg { + return fn.dollar("DOLLARFR", argsList) +} + +// dollar is an implementation of the formula function DOLLARDE and DOLLARFR. +func (fn *formulaFuncs) dollar(name string, argsList *list.List) formulaArg { + if argsList.Len() != 2 { + return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires 2 arguments", name)) + } + dollar := argsList.Front().Value.(formulaArg).ToNumber() + if dollar.Type != ArgNumber { + return dollar + } + frac := argsList.Back().Value.(formulaArg).ToNumber() + if frac.Type != ArgNumber { + return frac + } + if frac.Number < 0 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + if frac.Number == 0 { + return newErrorFormulaArg(formulaErrorDIV, formulaErrorDIV) + } + cents := math.Mod(dollar.Number, 1) + if name == "DOLLARDE" { + cents /= frac.Number + cents *= math.Pow(10, math.Ceil(math.Log10(frac.Number))) + } else { + cents *= frac.Number + cents *= math.Pow(10, -math.Ceil(math.Log10(frac.Number))) + } + return newNumberFormulaArg(math.Floor(dollar.Number) + cents) +} + +// EFFECT function returns the effective annual interest rate for a given +// nominal interest rate and number of compounding periods per year. The +// syntax of the function is: +// +// EFFECT(nominal_rate,npery) +// +func (fn *formulaFuncs) EFFECT(argsList *list.List) formulaArg { + if argsList.Len() != 2 { + return newErrorFormulaArg(formulaErrorVALUE, "EFFECT requires 2 arguments") + } + rate := argsList.Front().Value.(formulaArg).ToNumber() + if rate.Type != ArgNumber { + return rate + } + npery := argsList.Back().Value.(formulaArg).ToNumber() + if npery.Type != ArgNumber { + return npery + } + if rate.Number <= 0 || npery.Number < 1 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + return newNumberFormulaArg(math.Pow((1+rate.Number/npery.Number), npery.Number) - 1) +} + // IPMT function calculates the interest payment, during a specific period of a // loan or investment that is paid in constant periodic payments, with a // constant interest rate. The syntax of the function is: @@ -7310,6 +7496,66 @@ func (fn *formulaFuncs) ipmt(name string, argsList *list.List) formulaArg { return newNumberFormulaArg(principal) } +// ISPMT function calculates the interest paid during a specific period of a +// loan or investment. The syntax of the function is: +// +// ISPMT(rate,per,nper,pv) +// +func (fn *formulaFuncs) ISPMT(argsList *list.List) formulaArg { + if argsList.Len() != 4 { + return newErrorFormulaArg(formulaErrorVALUE, "ISPMT requires 4 arguments") + } + rate := argsList.Front().Value.(formulaArg).ToNumber() + if rate.Type != ArgNumber { + return rate + } + per := argsList.Front().Next().Value.(formulaArg).ToNumber() + if per.Type != ArgNumber { + return per + } + nper := argsList.Back().Prev().Value.(formulaArg).ToNumber() + if nper.Type != ArgNumber { + return nper + } + pv := argsList.Back().Value.(formulaArg).ToNumber() + if pv.Type != ArgNumber { + return pv + } + pr, payment, num := pv.Number, pv.Number/nper.Number, 0.0 + for i := 0; i <= int(per.Number); i++ { + num = rate.Number * pr * -1 + pr -= payment + if i == int(nper.Number) { + num = 0 + } + } + return newNumberFormulaArg(num) +} + +// NOMINAL function returns the nominal interest rate for a given effective +// interest rate and number of compounding periods per year. The syntax of +// the function is: +// +// NOMINAL(effect_rate,npery) +// +func (fn *formulaFuncs) NOMINAL(argsList *list.List) formulaArg { + if argsList.Len() != 2 { + return newErrorFormulaArg(formulaErrorVALUE, "NOMINAL requires 2 arguments") + } + rate := argsList.Front().Value.(formulaArg).ToNumber() + if rate.Type != ArgNumber { + return rate + } + npery := argsList.Back().Value.(formulaArg).ToNumber() + if npery.Type != ArgNumber { + return npery + } + if rate.Number <= 0 || npery.Number < 1 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + return newNumberFormulaArg(npery.Number * (math.Pow(rate.Number+1, 1/npery.Number) - 1)) +} + // PMT function calculates the constant periodic payment required to pay off // (or partially pay off) a loan or investment, with a constant interest // rate, over a specified period. The syntax of the function is: diff --git a/calc_test.go b/calc_test.go index d105b14..74cc627 100644 --- a/calc_test.go +++ b/calc_test.go @@ -1139,9 +1139,35 @@ func TestCalcCellValue(t *testing.T) { // CUMPRINC "=CUMPRINC(0.05/12,60,50000,1,12,0)": "-9027.762649079885", "=CUMPRINC(0.05/12,60,50000,13,24,0)": "-9489.640119832635", + // DB + "=DB(0,1000,5,1)": "0", + "=DB(10000,1000,5,1)": "3690", + "=DB(10000,1000,5,2)": "2328.39", + "=DB(10000,1000,5,1,6)": "1845", + "=DB(10000,1000,5,6,6)": "238.52712458788187", + // DDB + "=DDB(0,1000,5,1)": "0", + "=DDB(10000,1000,5,1)": "4000", + "=DDB(10000,1000,5,2)": "2400", + "=DDB(10000,1000,5,3)": "1440", + "=DDB(10000,1000,5,4)": "864", + "=DDB(10000,1000,5,5)": "296", + // DOLLARDE + "=DOLLARDE(1.01,16)": "1.0625", + // DOLLARFR + "=DOLLARFR(1.0625,16)": "1.01", + // EFFECT + "=EFFECT(0.1,4)": "0.103812890625", + "=EFFECT(0.025,2)": "0.02515625", // IPMT "=IPMT(0.05/12,2,60,50000)": "-205.26988187971995", "=IPMT(0.035/4,2,8,0,5000,1)": "5.257455237829077", + // ISPMT + "=ISPMT(0.05/12,1,60,50000)": "-204.8611111111111", + "=ISPMT(0.05/12,2,60,50000)": "-201.38888888888886", + "=ISPMT(0.05/12,2,1,50000)": "208.33333333333334", + // NOMINAL + "=NOMINAL(0.025,12)": "0.024718035238113", // PMT "=PMT(0,8,0,5000,1)": "-625", "=PMT(0.035/4,8,0,5000,1)": "-600.8520271804658", @@ -2058,6 +2084,42 @@ func TestCalcCellValue(t *testing.T) { "=CUMPRINC(0,0,0,\"\",0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", "=CUMPRINC(0,0,0,0,\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", "=CUMPRINC(0,0,0,0,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // DB + "=DB()": "DB requires at least 4 arguments", + "=DB(0,0,0,0,0,0)": "DB allows at most 5 arguments", + "=DB(-1,0,0,0)": "#N/A", + "=DB(\"\",0,0,0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=DB(0,\"\",0,0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=DB(0,0,\"\",0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=DB(0,0,0,\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=DB(0,0,0,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // DDB + "=DDB()": "DDB requires at least 4 arguments", + "=DDB(0,0,0,0,0,0)": "DDB allows at most 5 arguments", + "=DDB(-1,0,0,0)": "#N/A", + "=DDB(\"\",0,0,0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=DDB(0,\"\",0,0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=DDB(0,0,\"\",0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=DDB(0,0,0,\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=DDB(0,0,0,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // DOLLARDE + "=DOLLARDE()": "DOLLARDE requires 2 arguments", + "=DOLLARDE(\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=DOLLARDE(0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=DOLLARDE(0,-1)": "#NUM!", + "=DOLLARDE(0,0)": "#DIV/0!", + // DOLLARFR + "=DOLLARFR()": "DOLLARFR requires 2 arguments", + "=DOLLARFR(\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=DOLLARFR(0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=DOLLARFR(0,-1)": "#NUM!", + "=DOLLARFR(0,0)": "#DIV/0!", + // EFFECT + "=EFFECT()": "EFFECT requires 2 arguments", + "=EFFECT(\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=EFFECT(0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=EFFECT(0,0)": "#NUM!", + "=EFFECT(1,0)": "#NUM!", // IPMT "=IPMT()": "IPMT requires at least 4 arguments", "=IPMT(0,0,0,0,0,0,0)": "IPMT allows at most 6 arguments", @@ -2070,6 +2132,18 @@ func TestCalcCellValue(t *testing.T) { "=IPMT(0,0,0,\"\",0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", "=IPMT(0,0,0,0,\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", "=IPMT(0,0,0,0,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // ISPMT + "=ISPMT()": "ISPMT requires 4 arguments", + "=ISPMT(\"\",0,0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=ISPMT(0,\"\",0,0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=ISPMT(0,0,\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=ISPMT(0,0,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + // NOMINAL + "=NOMINAL()": "NOMINAL requires 2 arguments", + "=NOMINAL(\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=NOMINAL(0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=NOMINAL(0,0)": "#NUM!", + "=NOMINAL(1,0)": "#NUM!", // PMT "=PMT()": "PMT requires at least 3 arguments", "=PMT(0,0,0,0,0,0)": "PMT allows at most 5 arguments", |