summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go110
-rw-r--r--calc_test.go30
-rw-r--r--rows.go4
3 files changed, 142 insertions, 2 deletions
diff --git a/calc.go b/calc.go
index 91199cc..0899508 100644
--- a/calc.go
+++ b/calc.go
@@ -379,6 +379,7 @@ type formulaFuncs struct {
// DISC
// DOLLARDE
// DOLLARFR
+// DURATION
// EFFECT
// ENCODEURL
// ERF
@@ -471,6 +472,7 @@ type formulaFuncs struct {
// MATCH
// MAX
// MDETERM
+// MDURATION
// MEDIAN
// MID
// MIDB
@@ -10179,6 +10181,96 @@ func (fn *formulaFuncs) dollar(name string, argsList *list.List) formulaArg {
return newNumberFormulaArg(math.Floor(dollar.Number) + cents)
}
+// prepareDurationArgs checking and prepare arguments for the formula
+// functions DURATION and MDURATION.
+func (fn *formulaFuncs) prepareDurationArgs(name string, argsList *list.List) formulaArg {
+ if argsList.Len() != 5 && argsList.Len() != 6 {
+ return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires 5 or 6 arguments", name))
+ }
+ args := fn.prepareDataValueArgs(2, argsList)
+ if args.Type != ArgList {
+ return args
+ }
+ settlement, maturity := args.List[0], args.List[1]
+ if settlement.Number >= maturity.Number {
+ return newErrorFormulaArg(formulaErrorNUM, fmt.Sprintf("%s requires maturity > settlement", name))
+ }
+ coupon := argsList.Front().Next().Next().Value.(formulaArg).ToNumber()
+ if coupon.Type != ArgNumber {
+ return coupon
+ }
+ if coupon.Number < 0 {
+ return newErrorFormulaArg(formulaErrorNUM, fmt.Sprintf("%s requires coupon >= 0", name))
+ }
+ yld := argsList.Front().Next().Next().Next().Value.(formulaArg).ToNumber()
+ if yld.Type != ArgNumber {
+ return yld
+ }
+ if yld.Number < 0 {
+ return newErrorFormulaArg(formulaErrorNUM, fmt.Sprintf("%s requires yld >= 0", name))
+ }
+ frequency := argsList.Front().Next().Next().Next().Next().Value.(formulaArg).ToNumber()
+ if frequency.Type != ArgNumber {
+ return frequency
+ }
+ if !validateFrequency(frequency.Number) {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ basis := newNumberFormulaArg(0)
+ if argsList.Len() == 6 {
+ if basis = argsList.Back().Value.(formulaArg).ToNumber(); basis.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ }
+ return newListFormulaArg([]formulaArg{settlement, maturity, coupon, yld, frequency, basis})
+}
+
+// duration is an implementation of the formula function DURATION.
+func (fn *formulaFuncs) duration(settlement, maturity, coupon, yld, frequency, basis formulaArg) formulaArg {
+ frac := yearFrac(settlement.Number, maturity.Number, int(basis.Number))
+ if frac.Type != ArgNumber {
+ return frac
+ }
+ argumments := list.New().Init()
+ argumments.PushBack(settlement)
+ argumments.PushBack(maturity)
+ argumments.PushBack(frequency)
+ argumments.PushBack(basis)
+ coups := fn.COUPNUM(argumments)
+ duration := 0.0
+ p := 0.0
+ coupon.Number *= 100 / frequency.Number
+ yld.Number /= frequency.Number
+ yld.Number++
+ diff := frac.Number*frequency.Number - coups.Number
+ for t := 1.0; t < coups.Number; t++ {
+ tDiff := t + diff
+ add := coupon.Number / math.Pow(yld.Number, tDiff)
+ p += add
+ duration += tDiff * add
+ }
+ add := (coupon.Number + 100) / math.Pow(yld.Number, coups.Number+diff)
+ p += add
+ duration += (coups.Number + diff) * add
+ duration /= p
+ duration /= frequency.Number
+ return newNumberFormulaArg(duration)
+}
+
+// DURATION function calculates the Duration (specifically, the Macaulay
+// Duration) of a security that pays periodic interest, assuming a par value
+// of $100. The syntax of the function is:
+//
+// DURATION(settlement,maturity,coupon,yld,frequency,[basis])
+//
+func (fn *formulaFuncs) DURATION(argsList *list.List) formulaArg {
+ args := fn.prepareDurationArgs("DURATION", argsList)
+ if args.Type != ArgList {
+ return args
+ }
+ return fn.duration(args.List[0], args.List[1], args.List[2], args.List[3], args.List[4], args.List[5])
+}
+
// 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:
@@ -10504,6 +10596,24 @@ func (fn *formulaFuncs) ISPMT(argsList *list.List) formulaArg {
return newNumberFormulaArg(num)
}
+// MDURATION function calculates the Modified Macaulay Duration of a security
+// that pays periodic interest, assuming a par value of $100. The syntax of
+// the function is:
+//
+// MDURATION(settlement,maturity,coupon,yld,frequency,[basis])
+//
+func (fn *formulaFuncs) MDURATION(argsList *list.List) formulaArg {
+ args := fn.prepareDurationArgs("MDURATION", argsList)
+ if args.Type != ArgList {
+ return args
+ }
+ duration := fn.duration(args.List[0], args.List[1], args.List[2], args.List[3], args.List[4], args.List[5])
+ if duration.Type != ArgNumber {
+ return duration
+ }
+ return newNumberFormulaArg(duration.Number / (1 + args.List[3].Number/args.List[4].Number))
+}
+
// MIRR function returns the Modified Internal Rate of Return for a supplied
// series of periodic cash flows (i.e. a set of values, which includes an
// initial investment value and a series of net income values). The syntax of
diff --git a/calc_test.go b/calc_test.go
index c18176f..2f1b310 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -1472,6 +1472,8 @@ func TestCalcCellValue(t *testing.T) {
"=DOLLARDE(1.01,16)": "1.0625",
// DOLLARFR
"=DOLLARFR(1.0625,16)": "1.01",
+ // DURATION
+ "=DURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,4)": "6.674422798483131",
// EFFECT
"=EFFECT(0.1,4)": "0.103812890625",
"=EFFECT(0.025,2)": "0.02515625",
@@ -1491,6 +1493,8 @@ func TestCalcCellValue(t *testing.T) {
"=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",
+ // MDURATION
+ "=MDURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,4)": "6.543551763218756",
// NOMINAL
"=NOMINAL(0.025,12)": "0.0247180352381129",
// NPER
@@ -2916,6 +2920,19 @@ func TestCalcCellValue(t *testing.T) {
"=DOLLARFR(0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
"=DOLLARFR(0,-1)": "#NUM!",
"=DOLLARFR(0,0)": "#DIV/0!",
+ // DURATION
+ "=DURATION()": "DURATION requires 5 or 6 arguments",
+ "=DURATION(\"\",\"03/31/2025\",10%,8%,4)": "#VALUE!",
+ "=DURATION(\"04/01/2015\",\"\",10%,8%,4)": "#VALUE!",
+ "=DURATION(\"03/31/2025\",\"04/01/2015\",10%,8%,4)": "DURATION requires maturity > settlement",
+ "=DURATION(\"04/01/2015\",\"03/31/2025\",-1,8%,4)": "DURATION requires coupon >= 0",
+ "=DURATION(\"04/01/2015\",\"03/31/2025\",10%,-1,4)": "DURATION requires yld >= 0",
+ "=DURATION(\"04/01/2015\",\"03/31/2025\",\"\",8%,4)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=DURATION(\"04/01/2015\",\"03/31/2025\",10%,\"\",4)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=DURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=DURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,3)": "#NUM!",
+ "=DURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,4,\"\")": "#NUM!",
+ "=DURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,4,5)": "invalid basis",
// EFFECT
"=EFFECT()": "EFFECT requires 2 arguments",
"=EFFECT(\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax",
@@ -2964,6 +2981,19 @@ func TestCalcCellValue(t *testing.T) {
"=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",
+ // MDURATION
+ "=MDURATION()": "MDURATION requires 5 or 6 arguments",
+ "=MDURATION(\"\",\"03/31/2025\",10%,8%,4)": "#VALUE!",
+ "=MDURATION(\"04/01/2015\",\"\",10%,8%,4)": "#VALUE!",
+ "=MDURATION(\"03/31/2025\",\"04/01/2015\",10%,8%,4)": "MDURATION requires maturity > settlement",
+ "=MDURATION(\"04/01/2015\",\"03/31/2025\",-1,8%,4)": "MDURATION requires coupon >= 0",
+ "=MDURATION(\"04/01/2015\",\"03/31/2025\",10%,-1,4)": "MDURATION requires yld >= 0",
+ "=MDURATION(\"04/01/2015\",\"03/31/2025\",\"\",8%,4)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=MDURATION(\"04/01/2015\",\"03/31/2025\",10%,\"\",4)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=MDURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=MDURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,3)": "#NUM!",
+ "=MDURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,4,\"\")": "#NUM!",
+ "=MDURATION(\"04/01/2015\",\"03/31/2025\",10%,8%,4,5)": "invalid basis",
// NOMINAL
"=NOMINAL()": "NOMINAL requires 2 arguments",
"=NOMINAL(\"\",0)": "strconv.ParseFloat: parsing \"\": invalid syntax",
diff --git a/rows.go b/rows.go
index 1e20f0a..7b2f52f 100644
--- a/rows.go
+++ b/rows.go
@@ -774,11 +774,11 @@ func checkRow(ws *xlsxWorksheet) error {
//
// For example set style of row 1 on Sheet1:
//
-// err = f.SetRowStyle("Sheet1", 1, style)
+// err = f.SetRowStyle("Sheet1", 1, 1, styleID)
//
// Set style of rows 1 to 10 on Sheet1:
//
-// err = f.SetRowStyle("Sheet1", 1, 10, style)
+// err = f.SetRowStyle("Sheet1", 1, 10, styleID)
//
func (f *File) SetRowStyle(sheet string, start, end, styleID int) error {
if end < start {
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-16 09:40:36 +0000