ref #65: new formula functions COUPNCD and COUPNUM

2 files changed, 67 insertions, 0 deletions

diff --git a/calc.go b/calc.go
index dc57cd5..580ecfb 100644
--- a/calc.go
+++ b/calc.go
@@ -350,6 +350,8 @@ type formulaFuncs struct {
 //    COUNT
 //    COUNTA
 //    COUNTBLANK
+//    COUPNCD
+//    COUPNUM
 //    COUPPCD
 //    CSC
 //    CSCH
@@ -9543,6 +9545,46 @@ func (fn *formulaFuncs) prepareCouponArgs(name string, argsList *list.List) form
 	return newListFormulaArg([]formulaArg{settlement, maturity, frequency, basis})
 }
 
+// COUPNCD function calculates the number of coupons payable, between a
+// security's settlement date and maturity date, rounded up to the nearest
+// whole coupon. The syntax of the function is:
+//
+//    COUPNCD(settlement,maturity,frequency,[basis])
+//
+func (fn *formulaFuncs) COUPNCD(argsList *list.List) formulaArg {
+	args := fn.prepareCouponArgs("COUPNCD", argsList)
+	if args.Type != ArgList {
+		return args
+	}
+	settlement := timeFromExcelTime(args.List[0].Number, false)
+	maturity := timeFromExcelTime(args.List[1].Number, false)
+	ncd := time.Date(settlement.Year(), maturity.Month(), maturity.Day(), 0, 0, 0, 0, time.UTC)
+	if ncd.After(settlement) {
+		ncd = ncd.AddDate(-1, 0, 0)
+	}
+	for !ncd.After(settlement) {
+		ncd = ncd.AddDate(0, 12/int(args.List[2].Number), 0)
+	}
+	return newNumberFormulaArg(daysBetween(excelMinTime1900.Unix(), makeDate(ncd.Year(), ncd.Month(), ncd.Day())) + 1)
+}
+
+// COUPNUM function calculates the number of coupons payable, between a
+// security's settlement date and maturity date, rounded up to the nearest
+// whole coupon. The syntax of the function is:
+//
+//    COUPNUM(settlement,maturity,frequency,[basis])
+//
+func (fn *formulaFuncs) COUPNUM(argsList *list.List) formulaArg {
+	args := fn.prepareCouponArgs("COUPNUM", argsList)
+	if args.Type != ArgList {
+		return args
+	}
+	maturity, dateValue := timeFromExcelTime(args.List[1].Number, false), fn.COUPPCD(argsList)
+	date := timeFromExcelTime(dateValue.Number, false)
+	months := (maturity.Year()-date.Year())*12 + int(maturity.Month()) - int(date.Month())
+	return newNumberFormulaArg(float64(months) * args.List[2].Number / 12.0)
+}
+
 // COUPPCD function returns the previous coupon date, before the settlement
 // date for a security. The syntax of the function is:
 //
diff --git a/calc_test.go b/calc_test.go
index 3e36ef8..26c1ca1 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -1393,6 +1393,13 @@ func TestCalcCellValue(t *testing.T) {
 		"=AMORLINC(150,\"01/01/2015\",\"09/30/2015\",20,20,15%,4)": "0",
 		"=AMORLINC(150,\"01/01/2015\",\"09/30/2015\",20,6,15%,4)":  "0.6875",
 		"=AMORLINC(150,\"01/01/2015\",\"09/30/2015\",20,0,15%,4)":  "16.8125",
+		// COUPNCD
+		"=COUPNCD(\"01/01/2011\",\"10/25/2012\",4)":   "40568",
+		"=COUPNCD(\"01/01/2011\",\"10/25/2012\",4,0)": "40568",
+		"=COUPNCD(\"10/25/2011\",\"01/01/2012\",4)":   "40909",
+		// COUPNUM
+		"=COUPNUM(\"01/01/2011\",\"10/25/2012\",4)":   "8",
+		"=COUPNUM(\"01/01/2011\",\"10/25/2012\",4,0)": "8",
 		// COUPPCD
 		"=COUPPCD(\"01/01/2011\",\"10/25/2012\",4)":   "40476",
 		"=COUPPCD(\"01/01/2011\",\"10/25/2012\",4,0)": "40476",
@@ -2689,6 +2696,24 @@ func TestCalcCellValue(t *testing.T) {
 		"=AMORLINC(150,\"01/01/2015\",\"09/30/2015\",20,1,-1)":       "#NUM!",
 		"=AMORLINC(150,\"01/01/2015\",\"09/30/2015\",20,1,20%,\"\")": "#NUM!",
 		"=AMORLINC(150,\"01/01/2015\",\"09/30/2015\",20,1,20%,5)":    "invalid basis",
+		// COUPNCD
+		"=COUPNCD()": "COUPNCD requires 3 or 4 arguments",
+		"=COUPNCD(\"01/01/2011\",\"10/25/2012\",4,0,0)":  "COUPNCD requires 3 or 4 arguments",
+		"=COUPNCD(\"\",\"10/25/2012\",4)":                "#VALUE!",
+		"=COUPNCD(\"01/01/2011\",\"\",4)":                "#VALUE!",
+		"=COUPNCD(\"01/01/2011\",\"10/25/2012\",\"\")":   "#VALUE!",
+		"=COUPNCD(\"01/01/2011\",\"10/25/2012\",4,\"\")": "#NUM!",
+		"=COUPNCD(\"01/01/2011\",\"10/25/2012\",3)":      "#NUM!",
+		"=COUPNCD(\"10/25/2012\",\"01/01/2011\",4)":      "COUPNCD requires maturity > settlement",
+		// COUPNUM
+		"=COUPNUM()": "COUPNUM requires 3 or 4 arguments",
+		"=COUPNUM(\"01/01/2011\",\"10/25/2012\",4,0,0)":  "COUPNUM requires 3 or 4 arguments",
+		"=COUPNUM(\"\",\"10/25/2012\",4)":                "#VALUE!",
+		"=COUPNUM(\"01/01/2011\",\"\",4)":                "#VALUE!",
+		"=COUPNUM(\"01/01/2011\",\"10/25/2012\",\"\")":   "#VALUE!",
+		"=COUPNUM(\"01/01/2011\",\"10/25/2012\",4,\"\")": "#NUM!",
+		"=COUPNUM(\"01/01/2011\",\"10/25/2012\",3)":      "#NUM!",
+		"=COUPNUM(\"10/25/2012\",\"01/01/2011\",4)":      "COUPNUM requires maturity > settlement",
 		// COUPPCD
 		"=COUPPCD()": "COUPPCD requires 3 or 4 arguments",
 		"=COUPPCD(\"01/01/2011\",\"10/25/2012\",4,0,0)":  "COUPPCD requires 3 or 4 arguments",