1package functions23import (4 "math"56 "src.rest/cat/012/internal/value"7)89// Finance functions follow the usual cash-flow sign convention: money10// paid out is negative. type (end_or_beginning) is 0 for payments at the11// end of each period and 1 for the beginning.1213func init() {14 define(15 &FuncDef{Name: "PMT", Args: "rate, number_of_periods, present_value, [future_value], [end_or_beginning]", Desc: "Payment per period of a loan or investment", Min: 3, Max: 5,16 eval: finance(func(x []float64) Value {17 r, n, pv, fv, t := x[0], x[1], x[2], x[3], x[4]18 if n == 0 {19 return value.ErrNum20 }21 if r == 0 {22 return num(-(pv + fv) / n)23 }24 g := math.Pow(1+r, n)25 return num(-r * (fv + pv*g) / ((1 + r*t) * (g - 1)))26 })},27 &FuncDef{Name: "PV", Args: "rate, number_of_periods, payment_amount, [future_value], [end_or_beginning]", Desc: "Present value of a series of payments", Min: 3, Max: 5,28 eval: finance(func(x []float64) Value {29 r, n, pmt, fv, t := x[0], x[1], x[2], x[3], x[4]30 if r == 0 {31 return num(-(fv + pmt*n))32 }33 g := math.Pow(1+r, n)34 return num(-(fv + pmt*(1+r*t)*(g-1)/r) / g)35 })},36 &FuncDef{Name: "FV", Args: "rate, number_of_periods, payment_amount, [present_value], [end_or_beginning]", Desc: "Future value of a series of payments", Min: 3, Max: 5,37 eval: finance(func(x []float64) Value {38 r, n, pmt, pv, t := x[0], x[1], x[2], x[3], x[4]39 if r == 0 {40 return num(-(pv + pmt*n))41 }42 g := math.Pow(1+r, n)43 return num(-(pv*g + pmt*(1+r*t)*(g-1)/r))44 })},45 &FuncDef{Name: "NPER", Args: "rate, payment_amount, present_value, [future_value], [end_or_beginning]", Desc: "Number of periods to pay off a loan or reach a value", Min: 3, Max: 5,46 eval: finance(func(x []float64) Value {47 r, pmt, pv, fv, t := x[0], x[1], x[2], x[3], x[4]48 if r == 0 {49 if pmt == 0 {50 return value.ErrNum51 }52 return num(-(pv + fv) / pmt)53 }54 a := pmt * (1 + r*t)55 ratio := (a - fv*r) / (a + pv*r)56 if ratio <= 0 || r <= -1 {57 return value.ErrNum58 }59 return num(math.Log(ratio) / math.Log(1+r))60 })},61 &FuncDef{Name: "RATE", Args: "number_of_periods, payment_per_period, present_value, [future_value], [end_or_beginning], [rate_guess]", Desc: "Interest rate per period of an annuity", Min: 3, Max: 6,62 eval: rate, format: returns(value.Preset(value.FmtPercent))},63 &FuncDef{Name: "NPV", Args: "discount, cashflow1, [cashflow2, ...]", Desc: "Net present value of periodic cash flows", Min: 2, Max: -1,64 eval: func(args []Node, get lookup) Value {65 r, err := numArg(args[0], get)66 if err != nil {67 return *err68 }69 flows, err := nums(args[1:], get)70 if err != nil {71 return *err72 }73 if r == -1 {74 return value.ErrDiv075 }76 return num(npv(r, flows, 1))77 }},78 &FuncDef{Name: "IRR", Args: "cashflow_amounts, [rate_guess]", Desc: "Internal rate of return of periodic cash flows", Min: 1, Max: 2,79 eval: irr, format: returns(value.Preset(value.FmtPercent))},80 )81}8283// finance evaluates arguments as numbers, defaulting the optional84// future/present value and type to 0 (type is 1 if non-zero).85func finance(f func(x []float64) Value) func([]Node, lookup) Value {86 return func(args []Node, get lookup) Value {87 x := make([]float64, 5)88 for i := range x {89 v, err := optNum(args, i, 0, get)90 if err != nil {91 return *err92 }93 x[i] = v94 }95 if x[4] != 0 {96 x[4] = 197 }98 return f(x)99 }100}101102// npv discounts flows, the first at period first.103func npv(r float64, flows []float64, first int) float64 {104 total := 0.0105 for i, v := range flows {106 total += v / math.Pow(1+r, float64(i+first))107 }108 return total109}110111// rate solves the annuity equation for the rate with Newton's method, as112// Sheets does, starting from the guess (10% by default).113func rate(args []Node, get lookup) Value {114 x := make([]float64, 6)115 for i := range x {116 def := 0.0117 if i == 5 {118 def = 0.1119 }120 v, err := optNum(args, i, def, get)121 if err != nil {122 return *err123 }124 x[i] = v125 }126 n, pmt, pv, fv, t, r := x[0], x[1], x[2], x[3], x[4], x[5]127 if t != 0 {128 t = 1129 }130 if n <= 0 {131 return value.ErrNum132 }133 f := func(r float64) float64 {134 if math.Abs(r) < 1e-12 {135 return pv + pmt*n + fv136 }137 g := math.Pow(1+r, n)138 return pv*g + pmt*(1+r*t)*(g-1)/r + fv139 }140 for range 100 {141 y := f(r)142 h := 1e-7 * math.Max(1, math.Abs(r))143 d := (f(r+h) - f(r-h)) / (2 * h)144 if d == 0 || math.IsNaN(d) {145 return value.ErrNum146 }147 next := r - y/d148 if next <= -1 {149 next = (r - 1) / 2150 }151 if math.Abs(next-r) < 1e-12 {152 return num(next)153 }154 r = next155 }156 return value.ErrNum157}158159// irr finds the rate where the cash flows' present value is zero: Newton160// from the guess, falling back to bisection.161func irr(args []Node, get lookup) Value {162 flows, err := nums(args[:1], get)163 if err != nil {164 return *err165 }166 guess, err := optNum(args, 1, 0.1, get)167 if err != nil {168 return *err169 }170 pos, neg := false, false171 for _, v := range flows {172 pos = pos || v > 0173 neg = neg || v < 0174 }175 if !pos || !neg {176 return value.ErrNum177 }178 f := func(r float64) float64 { return npv(r, flows, 0) }179 df := func(r float64) float64 {180 total := 0.0181 for i, v := range flows {182 total -= float64(i) * v / math.Pow(1+r, float64(i+1))183 }184 return total185 }186 r := guess187 for range 50 {188 d := df(r)189 if d == 0 || math.IsNaN(d) {190 break191 }192 next := r - f(r)/d193 if next <= -1 || math.IsNaN(next) || math.IsInf(next, 0) {194 break195 }196 if math.Abs(next-r) < 1e-12 {197 return num(next)198 }199 r = next200 }201 // Bisection over a bracket where the sign changes.202 lo, hi := -0.9999999, 1.0203 for f(hi)*f(lo) > 0 && hi < 1e6 {204 hi *= 2205 }206 if f(hi)*f(lo) > 0 {207 return value.ErrNum208 }209 for range 200 {210 mid := (lo + hi) / 2211 if f(lo)*f(mid) <= 0 {212 hi = mid213 } else {214 lo = mid215 }216 }217 return num((lo + hi) / 2)218}