ref #65: new formula functions COUPPCD and PRICEMAT

2 files changed, 154 insertions, 0 deletions

diff --git a/calc.go b/calc.go
index 592f315..dc57cd5 100644
--- a/calc.go
+++ b/calc.go
@@ -350,6 +350,7 @@ type formulaFuncs struct {
 //    COUNT
 //    COUNTA
 //    COUNTBLANK
+//    COUPPCD
 //    CSC
 //    CSCH
 //    CUMIPMT
@@ -507,6 +508,7 @@ type formulaFuncs struct {
 //    POWER
 //    PPMT
 //    PRICEDISC
+//    PRICEMAT
 //    PRODUCT
 //    PROPER
 //    PV
@@ -9504,6 +9506,67 @@ func (fn *formulaFuncs) AMORLINC(argsList *list.List) formulaArg {
 	return newNumberFormulaArg(0)
 }
 
+// prepareCouponArgs checking and prepare arguments for the formula functions
+// COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNUM and COUPNCD.
+func (fn *formulaFuncs) prepareCouponArgs(name string, argsList *list.List) formulaArg {
+	if argsList.Len() != 3 && argsList.Len() != 4 {
+		return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("%s requires 3 or 4 arguments", name))
+	}
+	args := list.New().Init()
+	args.PushBack(argsList.Front().Value.(formulaArg))
+	settlement := fn.DATEVALUE(args)
+	if settlement.Type != ArgNumber {
+		return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+	}
+	args.Init()
+	args.PushBack(argsList.Front().Next().Value.(formulaArg))
+	maturity := fn.DATEVALUE(args)
+	if maturity.Type != ArgNumber {
+		return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+	}
+	if settlement.Number >= maturity.Number {
+		return newErrorFormulaArg(formulaErrorNUM, fmt.Sprintf("%s requires maturity > settlement", name))
+	}
+	frequency := argsList.Front().Next().Next().Value.(formulaArg).ToNumber()
+	if frequency.Type != ArgNumber {
+		return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+	}
+	if !validateFrequency(frequency.Number) {
+		return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+	}
+	basis := newNumberFormulaArg(0)
+	if argsList.Len() == 4 {
+		if basis = argsList.Back().Value.(formulaArg).ToNumber(); basis.Type != ArgNumber {
+			return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+		}
+	}
+	return newListFormulaArg([]formulaArg{settlement, maturity, frequency, basis})
+}
+
+// COUPPCD function returns the previous coupon date, before the settlement
+// date for a security. The syntax of the function is:
+//
+//    COUPPCD(settlement,maturity,frequency,[basis])
+//
+func (fn *formulaFuncs) COUPPCD(argsList *list.List) formulaArg {
+	args := fn.prepareCouponArgs("COUPPCD", argsList)
+	if args.Type != ArgList {
+		return args
+	}
+	settlement := timeFromExcelTime(args.List[0].Number, false)
+	maturity := timeFromExcelTime(args.List[1].Number, false)
+	date, years := maturity, settlement.Year()-maturity.Year()
+	date = date.AddDate(years, 0, 0)
+	if settlement.After(date) {
+		date = date.AddDate(1, 0, 0)
+	}
+	month := -12 / args.List[2].Number
+	for date.After(settlement) {
+		date = date.AddDate(0, int(month), 0)
+	}
+	return newNumberFormulaArg(daysBetween(excelMinTime1900.Unix(), makeDate(date.Year(), date.Month(), date.Day())) + 1)
+}
+
 // CUMIPMT function calculates the cumulative interest paid on a loan or
 // investment, between two specified periods. The syntax of the function is:
 //
@@ -10388,6 +10451,68 @@ func (fn *formulaFuncs) PRICEDISC(argsList *list.List) formulaArg {
 	return newNumberFormulaArg(redemption.Number * (1 - discount.Number*frac.Number))
 }
 
+// PRICEMAT function calculates the price, per $100 face value of a security
+// that pays interest at maturity. The syntax of the function is:
+//
+//    PRICEMAT(settlement,maturity,issue,rate,yld,[basis])
+//
+func (fn *formulaFuncs) PRICEMAT(argsList *list.List) formulaArg {
+	if argsList.Len() != 5 && argsList.Len() != 6 {
+		return newErrorFormulaArg(formulaErrorVALUE, "PRICEMAT requires 5 or 6 arguments")
+	}
+	args := list.New().Init()
+	args.PushBack(argsList.Front().Value.(formulaArg))
+	settlement := fn.DATEVALUE(args)
+	if settlement.Type != ArgNumber {
+		return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+	}
+	args.Init()
+	args.PushBack(argsList.Front().Next().Value.(formulaArg))
+	maturity := fn.DATEVALUE(args)
+	if maturity.Type != ArgNumber {
+		return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+	}
+	if settlement.Number >= maturity.Number {
+		return newErrorFormulaArg(formulaErrorNUM, "PRICEMAT requires maturity > settlement")
+	}
+	args.Init()
+	args.PushBack(argsList.Front().Next().Next().Value.(formulaArg))
+	issue := fn.DATEVALUE(args)
+	if issue.Type != ArgNumber {
+		return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE)
+	}
+	if issue.Number >= settlement.Number {
+		return newErrorFormulaArg(formulaErrorNUM, "PRICEMAT requires settlement > issue")
+	}
+	rate := argsList.Front().Next().Next().Next().Value.(formulaArg).ToNumber()
+	if rate.Type != ArgNumber {
+		return rate
+	}
+	if rate.Number < 0 {
+		return newErrorFormulaArg(formulaErrorNUM, "PRICEMAT requires rate >= 0")
+	}
+	yld := argsList.Front().Next().Next().Next().Next().Value.(formulaArg).ToNumber()
+	if yld.Type != ArgNumber {
+		return yld
+	}
+	if yld.Number < 0 {
+		return newErrorFormulaArg(formulaErrorNUM, "PRICEMAT requires yld >= 0")
+	}
+	basis := newNumberFormulaArg(0)
+	if argsList.Len() == 6 {
+		if basis = argsList.Back().Value.(formulaArg).ToNumber(); basis.Type != ArgNumber {
+			return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+		}
+	}
+	dsm := yearFrac(settlement.Number, maturity.Number, int(basis.Number))
+	if dsm.Type != ArgNumber {
+		return dsm
+	}
+	dis := yearFrac(issue.Number, settlement.Number, int(basis.Number))
+	dim := yearFrac(issue.Number, maturity.Number, int(basis.Number))
+	return newNumberFormulaArg(((1+dim.Number*rate.Number)/(1+dsm.Number*yld.Number) - dis.Number*rate.Number) * 100)
+}
+
 // PV function calculates the Present Value of an investment, based on a
 // series of future payments. The syntax of the function is:
 //
diff --git a/calc_test.go b/calc_test.go
index eb63130..3e36ef8 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -1393,6 +1393,10 @@ 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",
+		// COUPPCD
+		"=COUPPCD(\"01/01/2011\",\"10/25/2012\",4)":   "40476",
+		"=COUPPCD(\"01/01/2011\",\"10/25/2012\",4,0)": "40476",
+		"=COUPPCD(\"10/25/2011\",\"01/01/2012\",4)":   "40817",
 		// CUMIPMT
 		"=CUMIPMT(0.05/12,60,50000,1,12,0)":  "-2294.97753732664",
 		"=CUMIPMT(0.05/12,60,50000,13,24,0)": "-1833.1000665738893",
@@ -1456,6 +1460,9 @@ func TestCalcCellValue(t *testing.T) {
 		// PRICEDISC
 		"=PRICEDISC(\"04/01/2017\",\"03/31/2021\",2.5%,100)":   "90",
 		"=PRICEDISC(\"04/01/2017\",\"03/31/2021\",2.5%,100,3)": "90",
+		// PRICEMAT
+		"=PRICEMAT(\"04/01/2017\",\"03/31/2021\",\"01/01/2017\",4.5%,2.5%)":   "107.17045454545453",
+		"=PRICEMAT(\"04/01/2017\",\"03/31/2021\",\"01/01/2017\",4.5%,2.5%,0)": "107.17045454545453",
 		// PV
 		"=PV(0,60,1000)":         "-60000",
 		"=PV(5%/12,60,1000)":     "-52990.70632392748",
@@ -2682,6 +2689,15 @@ 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",
+		// COUPPCD
+		"=COUPPCD()": "COUPPCD requires 3 or 4 arguments",
+		"=COUPPCD(\"01/01/2011\",\"10/25/2012\",4,0,0)":  "COUPPCD requires 3 or 4 arguments",
+		"=COUPPCD(\"\",\"10/25/2012\",4)":                "#VALUE!",
+		"=COUPPCD(\"01/01/2011\",\"\",4)":                "#VALUE!",
+		"=COUPPCD(\"01/01/2011\",\"10/25/2012\",\"\")":   "#VALUE!",
+		"=COUPPCD(\"01/01/2011\",\"10/25/2012\",4,\"\")": "#NUM!",
+		"=COUPPCD(\"01/01/2011\",\"10/25/2012\",3)":      "#NUM!",
+		"=COUPPCD(\"10/25/2012\",\"01/01/2011\",4)":      "COUPPCD requires maturity > settlement",
 		// CUMIPMT
 		"=CUMIPMT()":               "CUMIPMT requires 6 arguments",
 		"=CUMIPMT(0,0,0,0,0,2)":    "#N/A",
@@ -2850,6 +2866,19 @@ func TestCalcCellValue(t *testing.T) {
 		"=PRICEDISC(\"04/01/2016\",\"03/31/2021\",0,100)":       "PRICEDISC requires discount > 0",
 		"=PRICEDISC(\"04/01/2016\",\"03/31/2021\",95,0)":        "PRICEDISC requires redemption > 0",
 		"=PRICEDISC(\"04/01/2016\",\"03/31/2021\",95,100,5)":    "invalid basis",
+		// PRICEMAT
+		"=PRICEMAT()": "PRICEMAT requires 5 or 6 arguments",
+		"=PRICEMAT(\"\",\"03/31/2021\",\"01/01/2017\",4.5%,2.5%)":                "#VALUE!",
+		"=PRICEMAT(\"04/01/2017\",\"\",\"01/01/2017\",4.5%,2.5%)":                "#VALUE!",
+		"=PRICEMAT(\"04/01/2017\",\"03/31/2021\",\"\",4.5%,2.5%)":                "#VALUE!",
+		"=PRICEMAT(\"04/01/2017\",\"03/31/2021\",\"01/01/2017\",\"\",2.5%)":      "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=PRICEMAT(\"04/01/2017\",\"03/31/2021\",\"01/01/2017\",4.5%,\"\")":      "strconv.ParseFloat: parsing \"\": invalid syntax",
+		"=PRICEMAT(\"04/01/2017\",\"03/31/2021\",\"01/01/2017\",4.5%,2.5%,\"\")": "#NUM!",
+		"=PRICEMAT(\"03/31/2021\",\"04/01/2017\",\"01/01/2017\",4.5%,2.5%)":      "PRICEMAT requires maturity > settlement",
+		"=PRICEMAT(\"01/01/2017\",\"03/31/2021\",\"04/01/2017\",4.5%,2.5%)":      "PRICEMAT requires settlement > issue",
+		"=PRICEMAT(\"04/01/2017\",\"03/31/2021\",\"01/01/2017\",-1,2.5%)":        "PRICEMAT requires rate >= 0",
+		"=PRICEMAT(\"04/01/2017\",\"03/31/2021\",\"01/01/2017\",4.5%,-1)":        "PRICEMAT requires yld >= 0",
+		"=PRICEMAT(\"04/01/2017\",\"03/31/2021\",\"01/01/2017\",4.5%,2.5%,5)":    "invalid basis",
 		// PV
 		"=PV()":                     "PV requires at least 3 arguments",
 		"=PV(10%/4,16,2000,0,1,0)":  "PV allows at most 5 arguments",