summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go115
-rw-r--r--calc_test.go26
2 files changed, 141 insertions, 0 deletions
diff --git a/calc.go b/calc.go
index 879a0ad..f346fa2 100644
--- a/calc.go
+++ b/calc.go
@@ -507,6 +507,8 @@ type formulaFuncs struct {
// RANDBETWEEN
// RANK
// RANK.EQ
+// RATE
+// RECEIVED
// REPLACE
// REPLACEB
// REPT
@@ -9968,6 +9970,119 @@ func (fn *formulaFuncs) PV(argsList *list.List) formulaArg {
return newNumberFormulaArg((((1-math.Pow(1+rate.Number, nper.Number))/rate.Number)*pmt.Number*(1+rate.Number*t.Number) - fv.Number) / math.Pow(1+rate.Number, nper.Number))
}
+// RATE function calculates the interest rate required to pay off a specified
+// amount of a loan, or to reach a target amount on an investment, over a
+// given period. The syntax of the function is:
+//
+// RATE(nper,pmt,pv,[fv],[type],[guess])
+//
+func (fn *formulaFuncs) RATE(argsList *list.List) formulaArg {
+ if argsList.Len() < 3 {
+ return newErrorFormulaArg(formulaErrorVALUE, "RATE requires at least 3 arguments")
+ }
+ if argsList.Len() > 6 {
+ return newErrorFormulaArg(formulaErrorVALUE, "RATE allows at most 6 arguments")
+ }
+ nper := argsList.Front().Value.(formulaArg).ToNumber()
+ if nper.Type != ArgNumber {
+ return nper
+ }
+ pmt := argsList.Front().Next().Value.(formulaArg).ToNumber()
+ if pmt.Type != ArgNumber {
+ return pmt
+ }
+ pv := argsList.Front().Next().Next().Value.(formulaArg).ToNumber()
+ if pv.Type != ArgNumber {
+ return pv
+ }
+ fv := newNumberFormulaArg(0)
+ if argsList.Len() >= 4 {
+ if fv = argsList.Front().Next().Next().Next().Value.(formulaArg).ToNumber(); fv.Type != ArgNumber {
+ return fv
+ }
+ }
+ t := newNumberFormulaArg(0)
+ if argsList.Len() >= 5 {
+ if t = argsList.Front().Next().Next().Next().Next().Value.(formulaArg).ToNumber(); t.Type != ArgNumber {
+ return t
+ }
+ if t.Number != 0 {
+ t.Number = 1
+ }
+ }
+ guess := newNumberFormulaArg(0.1)
+ if argsList.Len() == 6 {
+ if guess = argsList.Back().Value.(formulaArg).ToNumber(); guess.Type != ArgNumber {
+ return guess
+ }
+ }
+ maxIter, iter, close, epsMax, rate := 100, 0, false, 1e-6, guess.Number
+ for iter < maxIter && !close {
+ t1 := math.Pow(rate+1, nper.Number)
+ t2 := math.Pow(rate+1, nper.Number-1)
+ rt := rate*t.Number + 1
+ p0 := pmt.Number * (t1 - 1)
+ f1 := fv.Number + t1*pv.Number + p0*rt/rate
+ f2 := nper.Number*t2*pv.Number - p0*rt/math.Pow(rate, 2)
+ f3 := (nper.Number*pmt.Number*t2*rt + p0*t.Number) / rate
+ delta := f1 / (f2 + f3)
+ if math.Abs(delta) < epsMax {
+ close = true
+ }
+ iter++
+ rate -= delta
+ }
+ return newNumberFormulaArg(rate)
+}
+
+// RECEIVED function calculates the amount received at maturity for a fully
+// invested security. The syntax of the function is:
+//
+// RECEIVED(settlement,maturity,investment,discount,[basis])
+//
+func (fn *formulaFuncs) RECEIVED(argsList *list.List) formulaArg {
+ if argsList.Len() < 4 {
+ return newErrorFormulaArg(formulaErrorVALUE, "RECEIVED requires at least 4 arguments")
+ }
+ if argsList.Len() > 5 {
+ return newErrorFormulaArg(formulaErrorVALUE, "RECEIVED allows at most 5 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)
+ }
+ investment := argsList.Front().Next().Next().Value.(formulaArg).ToNumber()
+ if investment.Type != ArgNumber {
+ return investment
+ }
+ discount := argsList.Front().Next().Next().Next().Value.(formulaArg).ToNumber()
+ if discount.Type != ArgNumber {
+ return discount
+ }
+ if discount.Number <= 0 {
+ return newErrorFormulaArg(formulaErrorNUM, "RECEIVED requires discount > 0")
+ }
+ basis := newNumberFormulaArg(0)
+ if argsList.Len() == 5 {
+ if basis = argsList.Back().Value.(formulaArg).ToNumber(); basis.Type != ArgNumber {
+ return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM)
+ }
+ }
+ frac := yearFrac(settlement.Number, maturity.Number, int(basis.Number))
+ if frac.Type != ArgNumber {
+ return frac
+ }
+ return newNumberFormulaArg(investment.Number / (1 - discount.Number*frac.Number))
+}
+
// RRI function calculates the equivalent interest rate for an investment with
// specified present value, future value and duration. The syntax of the
// function is:
diff --git a/calc_test.go b/calc_test.go
index 1545c76..ffaec1a 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -1393,6 +1393,13 @@ func TestCalcCellValue(t *testing.T) {
"=PV(0,60,1000)": "-60000",
"=PV(5%/12,60,1000)": "-52990.70632392748",
"=PV(10%/4,16,2000,0,1)": "-26762.75545288113",
+ // RATE
+ "=RATE(60,-1000,50000)": "0.0061834131621292",
+ "=RATE(24,-800,0,20000,1)": "0.00325084350160374",
+ "=RATE(48,-200,8000,3,1,0.5)": "0.0080412665831637",
+ // RECEIVED
+ "=RECEIVED(\"04/01/2011\",\"03/31/2016\",1000,4.5%)": "1290.3225806451612",
+ "=RECEIVED(\"04/01/2011\",\"03/31/2016\",1000,4.5%,0)": "1290.3225806451612",
// RRI
"=RRI(10,10000,15000)": "0.0413797439924106",
// SLN
@@ -2703,6 +2710,25 @@ func TestCalcCellValue(t *testing.T) {
"=PV(10%/4,16,\"\",0,1)": "strconv.ParseFloat: parsing \"\": invalid syntax",
"=PV(10%/4,16,2000,\"\",1)": "strconv.ParseFloat: parsing \"\": invalid syntax",
"=PV(10%/4,16,2000,0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ // RATE
+ "=RATE()": "RATE requires at least 3 arguments",
+ "=RATE(48,-200,8000,3,1,0.5,0)": "RATE allows at most 6 arguments",
+ "=RATE(\"\",-200,8000,3,1,0.5)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=RATE(48,\"\",8000,3,1,0.5)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=RATE(48,-200,\"\",3,1,0.5)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=RATE(48,-200,8000,\"\",1,0.5)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=RATE(48,-200,8000,3,\"\",0.5)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=RATE(48,-200,8000,3,1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ // RECEIVED
+ "=RECEIVED()": "RECEIVED requires at least 4 arguments",
+ "=RECEIVED(\"04/01/2011\",\"03/31/2016\",1000,4.5%,1,0)": "RECEIVED allows at most 5 arguments",
+ "=RECEIVED(\"\",\"03/31/2016\",1000,4.5%,1)": "#VALUE!",
+ "=RECEIVED(\"04/01/2011\",\"\",1000,4.5%,1)": "#VALUE!",
+ "=RECEIVED(\"04/01/2011\",\"03/31/2016\",\"\",4.5%,1)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=RECEIVED(\"04/01/2011\",\"03/31/2016\",1000,\"\",1)": "strconv.ParseFloat: parsing \"\": invalid syntax",
+ "=RECEIVED(\"04/01/2011\",\"03/31/2016\",1000,4.5%,\"\")": "#NUM!",
+ "=RECEIVED(\"04/01/2011\",\"03/31/2016\",1000,0)": "RECEIVED requires discount > 0",
+ "=RECEIVED(\"04/01/2011\",\"03/31/2016\",1000,4.5%,5)": "invalid basis",
// RRI
"=RRI()": "RRI requires 3 arguments",
"=RRI(\"\",\"\",\"\")": "#NUM!",
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-19 00:18:41 +0000