kspread

kspread_functions_financial.cpp
1/* This file is part of the KDE project
2 Copyright (C) 1998-2002 The KSpread Team
3 www.koffice.org/kspread
4 Copyright (C) 2005 Tomas Mecir <mecirt@gmail.com>
5
6 This library is free software; you can redistribute it and/or
7 modify it under the terms of the GNU Library General Public
8 License as published by the Free Software Foundation; either
9 version 2 of the License.
10
11 This library is distributed in the hope that it will be useful,
12 but WITHOUT ANY WARRANTY; without even the implied warranty of
13 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
14 Library General Public License for more details.
15
16 You should have received a copy of the GNU Library General Public License
17 along with this library; see the file COPYING.LIB. If not, write to
18 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
19 * Boston, MA 02110-1301, USA.
20*/
21
22// built-in financial functions
23
24#include <math.h>
25
26#include "functions.h"
27#include "kspread_functions_helper.h"
28#include "valuecalc.h"
29#include "valueconverter.h"
30
31#include <tdelocale.h>
32#include <kcalendarsystem.h>
33
34using namespace KSpread;
35
36// prototypes (sorted)
37Value func_accrint (valVector args, ValueCalc *calc, FuncExtra *);
38Value func_accrintm (valVector args, ValueCalc *calc, FuncExtra *);
39Value func_compound (valVector args, ValueCalc *calc, FuncExtra *);
40Value func_continuous (valVector args, ValueCalc *calc, FuncExtra *);
41Value func_coupnum (valVector args, ValueCalc *calc, FuncExtra *);
42Value func_db (valVector args, ValueCalc *calc, FuncExtra *);
43Value func_ddb (valVector args, ValueCalc *calc, FuncExtra *);
44Value func_disc (valVector args, ValueCalc *calc, FuncExtra *);
45Value func_dollarde (valVector args, ValueCalc *calc, FuncExtra *);
46Value func_dollarfr (valVector args, ValueCalc *calc, FuncExtra *);
47Value func_duration (valVector args, ValueCalc *calc, FuncExtra *);
48Value func_effective (valVector args, ValueCalc *calc, FuncExtra *);
49Value func_euro (valVector args, ValueCalc *calc, FuncExtra *);
50Value func_fv (valVector args, ValueCalc *calc, FuncExtra *);
51Value func_fv_annuity (valVector args, ValueCalc *calc, FuncExtra *);
52Value func_intrate (valVector args, ValueCalc *calc, FuncExtra *);
53Value func_ipmt (valVector args, ValueCalc *calc, FuncExtra *);
54Value func_ispmt (valVector args, ValueCalc *calc, FuncExtra *);
55Value func_level_coupon (valVector args, ValueCalc *calc, FuncExtra *);
56Value func_nominal (valVector args, ValueCalc *calc, FuncExtra *);
57Value func_nper (valVector args, ValueCalc *calc, FuncExtra *);
58Value func_pmt (valVector args, ValueCalc *calc, FuncExtra *);
59Value func_ppmt (valVector args, ValueCalc *calc, FuncExtra *);
60Value func_pv (valVector args, ValueCalc *calc, FuncExtra *);
61Value func_pv_annuity (valVector args, ValueCalc *calc, FuncExtra *);
62Value func_received (valVector args, ValueCalc *calc, FuncExtra *);
63Value func_sln (valVector args, ValueCalc *calc, FuncExtra *);
64Value func_syd (valVector args, ValueCalc *calc, FuncExtra *);
65Value func_tbilleq (valVector args, ValueCalc *calc, FuncExtra *);
66Value func_tbillprice (valVector args, ValueCalc *calc, FuncExtra *);
67Value func_tbillyield (valVector args, ValueCalc *calc, FuncExtra *);
68Value func_zero_coupon (valVector args, ValueCalc *calc, FuncExtra *);
69
70// registers all financial functions
71void RegisterFinancialFunctions()
72{
73 FunctionRepository* repo = FunctionRepository::self();
74 Function *f;
75
76 f = new Function ("ACCRINT", func_accrint);
77 f->setParamCount (6, 7);
78 repo->add (f);
79 f = new Function ("ACCRINTM", func_accrintm);
80 f->setParamCount (3, 5);
81 repo->add (f);
82 f = new Function ("COMPOUND", func_compound);
83 f->setParamCount (4);
84 repo->add (f);
85 f = new Function ("CONTINUOUS", func_continuous);
86 f->setParamCount (3);
87 repo->add (f);
88 f = new Function ("COUPNUM", func_coupnum);
89 f->setParamCount (3, 5);
90 repo->add (f);
91 f = new Function ("DB", func_db);
92 f->setParamCount (4, 5);
93 repo->add (f);
94 f = new Function ("DDB", func_ddb);
95 f->setParamCount (4, 5);
96 repo->add (f);
97 f = new Function ("DISC", func_disc);
98 f->setParamCount (4, 5);
99 repo->add (f);
100 f = new Function ("DOLLARDE", func_dollarde);
101 f->setParamCount (2);
102 repo->add (f);
103 f = new Function ("DOLLARFR", func_dollarfr);
104 f->setParamCount (2);
105 repo->add (f);
106 f = new Function ("DURATION", func_duration);
107 f->setParamCount (3);
108 repo->add (f);
109 f = new Function ("EFFECT", func_effective);
110 f->setParamCount (2);
111 repo->add (f);
112 f = new Function ("EFFECTIVE", func_effective);
113 f->setParamCount (2);
114 repo->add (f);
115 f = new Function ("EURO", func_euro); // KSpread-specific, Gnumeric-compatible
116 f->setParamCount (1);
117 repo->add (f);
118 f = new Function ("FV", func_fv);
119 f->setParamCount (3);
120 repo->add (f);
121 f = new Function ("FV_ANNUITY", func_fv_annuity);
122 f->setParamCount (3);
123 repo->add (f);
124 f = new Function ("INTRATE", func_intrate);
125 f->setParamCount (4, 5);
126 repo->add (f);
127 f = new Function ("IPMT", func_ipmt);
128 f->setParamCount (4, 6);
129 repo->add (f);
130 f = new Function ("ISPMT", func_ispmt);
131 f->setParamCount (4);
132 repo->add (f);
133 f = new Function ("LEVEL_COUPON", func_level_coupon);
134 f->setParamCount (5);
135 repo->add (f);
136 f = new Function ("NOMINAL", func_nominal);
137 f->setParamCount (2);
138 repo->add (f);
139 f = new Function ("NPER", func_nper);
140 f->setParamCount (3, 5);
141 repo->add (f);
142 f = new Function ("PMT", func_pmt);
143 f->setParamCount (3, 5);
144 repo->add (f);
145 f = new Function ("PPMT", func_ppmt);
146 f->setParamCount (4, 6);
147 repo->add (f);
148 f = new Function ("PV", func_pv);
149 f->setParamCount (3);
150 repo->add (f);
151 f = new Function ("PV_ANNUITY", func_pv_annuity);
152 f->setParamCount (3);
153 repo->add (f);
154 f = new Function ("RECEIVED", func_received);
155 f->setParamCount (4, 5);
156 repo->add (f);
157 f = new Function ("SLN", func_sln);
158 f->setParamCount (3);
159 repo->add (f);
160 f = new Function ("SYD", func_syd);
161 f->setParamCount (4);
162 repo->add (f);
163 f = new Function ("TBILLEQ", func_tbilleq);
164 f->setParamCount (3);
165 repo->add (f);
166 f = new Function ("TBILLPRICE", func_tbillprice);
167 f->setParamCount (3);
168 repo->add (f);
169 f = new Function ("TBILLYIELD", func_tbillyield);
170 f->setParamCount (3);
171 repo->add (f);
172 f = new Function ("ZERO_COUPON", func_zero_coupon);
173 f->setParamCount (3);
174 repo->add (f);
175}
176
177static Value getPay (ValueCalc *calc, Value rate,
178 Value nper, Value pv, Value fv, Value type)
179{
180 Value pvif, fvifa;
181
182 if (calc->isZero (rate)) return Value::errorVALUE();
183
184 //pvif = pow( 1 + rate, nper );
185 //fvifa = ( pvif - 1 ) / rate;
186 pvif = calc->pow (calc->add (rate, 1), nper);
187 fvifa = calc->div (calc->sub (pvif, 1), rate);
188
189 // ( -pv * pvif - fv ) / ( ( 1.0 + rate * type ) * fvifa );
190 Value val1 = calc->sub (calc->mul (calc->mul (-1, pv), pvif), fv);
191 Value val2 = calc->mul (calc->add (1.0, calc->mul (rate, type)),
192 fvifa);
193 return calc->div (val1, val2);
194}
195
196static Value getPrinc (ValueCalc *calc, Value start,
197 Value pay, Value rate, Value period)
198{
199 // val1 = pow( 1 + rate, period )
200 Value val1 = calc->pow (calc->add (rate, 1), period);
201 // val2 = start * val1
202 Value val2 = calc->mul (start, val1);
203 // val3 = pay * ( ( val1 - 1 ) / rate )
204 Value val3 = calc->mul (pay, calc->div (calc->sub (val1, 1), rate));
205 // result = val2 + val3
206 return calc->add (val2, val3);
207}
208
209// Function: COUPNUM - taken from GNUMERIC
210Value func_coupnum (valVector args, ValueCalc *calc, FuncExtra *)
211{
212 // dates and integers only - don't need high-precision for this
213 TQDate settlement = calc->conv()->asDate (args[0]).asDate();
214 TQDate maturity = calc->conv()->asDate (args[1]).asDate();
215 int frequency = calc->conv()->asInteger (args[2]).asInteger();
216 int basis = 0;
217 bool eom = true;
218 if (args.count() > 3)
219 basis = calc->conv()->asInteger (args[3]).asInteger();
220 if (args.count() == 5)
221 eom = calc->conv()->asBoolean (args[4]).asBoolean();
222
223 if (basis < 0 || basis > 5 || ( frequency == 0 ) || ( 12 % frequency != 0 )
224 || settlement.daysTo( maturity ) <= 0)
225 return Value::errorVALUE();
226
227 double result;
228 TQDate cDate( maturity );
229
230 int months = maturity.month() - settlement.month()
231 + 12 * ( maturity.year() - settlement.year() );
232
233 cDate = calc->conv()->locale()->calendar()->addMonths (cDate, -months);
234
235 if ( eom && maturity.daysInMonth() == maturity.day() )
236 {
237 while( cDate.daysInMonth() != cDate.day() )
238 cDate.addDays( 1 );
239 }
240
241 if ( settlement.day() >= cDate.day() )
242 --months;
243
244 result = ( 1 + months / ( 12 / frequency ) );
245
246 return Value (result);
247}
248
249// Function: ACCRINT
250Value func_accrint (valVector args, ValueCalc *calc, FuncExtra *)
251{
252 TQDate maturity = calc->conv()->asDate (args[0]).asDate();
253 TQDate firstInterest = calc->conv()->asDate (args[1]).asDate();
254 TQDate settlement = calc->conv()->asDate (args[2]).asDate();
255
256 Value rate = args[3];
257 Value par = args[4];
258 int frequency = calc->conv()->asInteger (args[5]).asInteger();
259
260 int basis = 0;
261 if (args.count() == 7)
262 basis = calc->conv()->asInteger (args[6]).asInteger();
263
264 if ( basis < 0 || basis > 4 || (calc->isZero (frequency)) ||
265 (12 % frequency != 0))
266 return Value::errorVALUE();
267
268 if ( ( settlement.daysTo( firstInterest ) < 0 )
269 || ( firstInterest.daysTo( maturity ) > 0 ) )
270 return Value::errorVALUE();
271
272 double d = daysBetweenDates (maturity, settlement, basis);
273 double y = daysPerYear (maturity, basis);
274
275 if ( d < 0 || y <= 0 || calc->lower (par, 0) || calc->lower (rate, 0) ||
276 calc->isZero (rate))
277 return Value::errorVALUE();
278
279 Value coeff = calc->div (calc->mul (par, rate), frequency);
280 double n = d / y;
281
282 return calc->mul (coeff, n * frequency);
283}
284
285// Function: ACCRINTM
286Value func_accrintm (valVector args, ValueCalc *calc, FuncExtra *)
287{
288 TQDate issue = calc->conv()->asDate (args[0]).asDate();
289 TQDate maturity = calc->conv()->asDate (args[1]).asDate();
290 Value rate = args[2];
291
292 Value par = 1000;
293 int basis = 0;
294 if (args.count() > 3)
295 par = args[3];
296 if (args.count() == 5)
297 basis = calc->conv()->asInteger (args[4]).asInteger ();
298
299 double d = daysBetweenDates (issue, maturity, basis);
300 double y = daysPerYear (issue, basis);
301
302 if (d < 0 || y <= 0 || calc->isZero (par) || calc->isZero (rate) ||
303 calc->lower (par, 0) || calc->lower (rate, 0) || basis < 0 || basis > 4)
304 return Value::errorVALUE();
305
306 // par*date * d/y
307 return calc->mul (calc->mul (par, rate), d / y);
308}
309
310// Function: DISC
311Value func_disc (valVector args, ValueCalc *calc, FuncExtra *)
312{
313 TQDate settlement = calc->conv()->asDate (args[0]).asDate();
314 TQDate maturity = calc->conv()->asDate (args[1]).asDate();
315
316 Value par = args[2];
317 Value redemp = args[3];
318
319 int basis = 0;
320 if (args.count() == 5)
321 basis = calc->conv()->asInteger (args[4]).asInteger();
322
323 double y = daysPerYear (settlement, basis);
324 double d = daysBetweenDates (settlement, maturity, basis);
325
326 if ( y <= 0 || d <= 0 || basis < 0 || basis > 4 || calc->isZero (redemp) )
327 return false;
328
329 // (redemp - par) / redemp * (y / d)
330 return calc->mul (calc->div (calc->sub (redemp, par), redemp), y / d);
331}
332
333
334// Function: TBILLPRICE
335Value func_tbillprice (valVector args, ValueCalc *calc, FuncExtra *)
336{
337 TQDate settlement = calc->conv()->asDate (args[0]).asDate();
338 TQDate maturity = calc->conv()->asDate (args[1]).asDate();
339
340 Value discount = args[2];
341
342 double days = settlement.daysTo( maturity );
343
344 if (settlement > maturity || calc->lower (discount, 0) || days > 265)
345 return Value::errorVALUE();
346
347 // (discount * days) / 360.0
348 Value val = calc->div (calc->mul (discount, days), 360.0);
349 // 100 * (1.0 - val);
350 return calc->mul (calc->sub (1.0, val), 100);
351}
352
353// Function: TBILLYIELD
354Value func_tbillyield (valVector args, ValueCalc *calc, FuncExtra *)
355{
356 TQDate settlement = calc->conv()->asDate (args[0]).asDate();
357 TQDate maturity = calc->conv()->asDate (args[1]).asDate();
358
359 Value rate = args[2];
360
361 double days = settlement.daysTo( maturity );
362
363 if (settlement > maturity || calc->isZero (rate) || calc->lower (rate, 0)
364 || days > 265)
365 return Value::errorVALUE();
366
367 // (100.0 - rate) / rate * (360.0 / days);
368 return calc->mul (calc->div (calc->sub (100.0, rate), rate), 360.0 / days);
369}
370
371// Function: TBILLEQ
372Value func_tbilleq (valVector args, ValueCalc *calc, FuncExtra *)
373{
374 TQDate settlement = calc->conv()->asDate (args[0]).asDate();
375 TQDate maturity = calc->conv()->asDate (args[1]).asDate();
376
377 Value discount = args[2];
378
379 double days = settlement.daysTo( maturity );
380
381 if (settlement > maturity || calc->lower (discount, 0) || days > 265)
382 return Value::errorVALUE();
383
384 // 360 - discount*days
385 Value divisor = calc->sub (360.0, calc->mul (discount, days));
386 if (calc->isZero (divisor))
387 return Value::errorVALUE();
388
389 // 365.0 * discount / divisor
390 return calc->mul (calc->div (discount, divisor), 356.0);
391}
392
393// Function: RECEIVED
394Value func_received (valVector args, ValueCalc *calc, FuncExtra *)
395{
396
397 TQDate settlement = calc->conv()->asDate (args[0]).asDate();
398 TQDate maturity = calc->conv()->asDate (args[1]).asDate();
399
400 Value investment = args[2];
401 Value discount = args[3];
402
403 int basis = 0;
404 if (args.count() == 5)
405 basis = calc->conv()->asInteger (args[4]).asInteger();
406
407 double d = daysBetweenDates( settlement, maturity, basis );
408 double y = daysPerYear( settlement, basis );
409
410 if ( d <= 0 || y <= 0 || basis < 0 || basis > 4 )
411 return false;
412
413 // 1.0 - ( discount * d / y )
414 Value x = calc->sub (1.0, (calc->mul (discount, d / y)));
415
416 if (calc->isZero (x))
417 return Value::errorVALUE();
418 return calc->div (investment, x);
419}
420
421// Function: DOLLARDE
422Value func_dollarde (valVector args, ValueCalc *calc, FuncExtra *)
423{
424 Value d = args[0];
425 Value f = args[1];
426
427 if (!calc->greater (f, 0))
428 return Value::errorVALUE();
429
430 Value tmp = f;
431 int n = 0;
432 while (calc->greater (tmp, 0))
433 {
434 tmp = calc->div (tmp, 10);
435 ++n;
436 }
437
438 Value fl = calc->roundDown (d);
439 Value r = calc->sub (d, fl);
440
441 // fl + (r * pow(10.0, n) / f)
442 return calc->add (fl, calc->div (calc->mul (r, pow (10.0, n)), f));
443}
444
445// Function: DOLLARFR
446Value func_dollarfr (valVector args, ValueCalc *calc, FuncExtra *)
447{
448 Value d = args[0];
449 Value f = args[1];
450
451 if (!calc->greater (f, 0))
452 return Value::errorVALUE();
453
454 Value tmp = f;
455 int n = 0;
456 while (calc->greater (tmp, 0))
457 {
458 tmp = calc->div (tmp, 10);
459 ++n;
460 }
461
462 Value fl = calc->roundDown (d);
463 Value r = calc->sub (d, fl);
464
465 // fl + ((r * f) / pow (10.0, n));
466 return calc->add (fl, calc->div (calc->mul (r, f), pow (10.0, n)));
467}
468
470
471// Function: INTRATE
472Value func_intrate (valVector args, ValueCalc *calc, FuncExtra *)
473{
474 TQDate settlement = calc->conv()->asDate (args[0]).asDate();
475 TQDate maturity = calc->conv()->asDate (args[1]).asDate();
476
477 Value invest = args[2];
478 Value redemption = args[3];
479
480 int basis = 0;
481 if (args.count() == 5)
482 basis = calc->conv()->asInteger (args[4]).asInteger();
483
484 double d = daysBetweenDates (settlement, maturity, basis);
485 double y = daysPerYear (settlement, basis);
486
487 if ( d <= 0 || y <= 0 || calc->isZero (invest) || basis < 0 || basis > 4 )
488 return Value::errorVALUE();
489
490 // (redemption - invest) / invest * (y / d)
491 return calc->mul (calc->div (calc->sub (redemption, invest), invest), y/d);
492}
493
494
495// Function: DURATION
496Value func_duration (valVector args, ValueCalc *calc, FuncExtra *)
497{
498 Value rate = args[0];
499 Value pv = args[1];
500 Value fv = args[2];
501
502 if (!calc->greater (rate, 0.0))
503 return Value::errorVALUE();
504 if (calc->isZero (fv) || calc->isZero (pv))
505 return Value::errorDIV0();
506
507 if (calc->lower (calc->div (fv, pv), 0))
508 return Value::errorVALUE();
509
510 // log(fv / pv) / log(1.0 + rate)
511 return calc->div (calc->ln (calc->div (fv, pv)),
512 calc->ln (calc->add (rate, 1.0)));
513}
514
515// Function: PMT
516Value func_pmt (valVector args, ValueCalc *calc, FuncExtra *)
517{
518 Value rate = args[0];
519 Value nper = args[1];
520 Value pv = args[2];
521 Value fv = 0.0;
522 Value type = 0;
523 if (args.count() > 3) fv = args[3];
524 if (args.count() == 5) type = args[4];
525
526 return getPay (calc, rate, nper, pv, fv, type);
527}
528
529// Function: NPER
530Value func_nper (valVector args, ValueCalc *calc, FuncExtra *)
531{
532 Value rate = args[0];
533 Value pmt = args[1];
534 Value pv = args[2];
535 Value fv = 0.0;
536 Value type = 0;
537 if (args.count() > 3) fv = args[3];
538 if (args.count() == 5) type = args[4];
539
540 if (!calc->greater (rate, 0.0))
541 return Value::errorVALUE();
542
543 // taken from Gnumeric
544 // v = 1.0 + rate * type
545 // d1 = pmt * v - fv * rate
546 // d2 = pmt * v - pv * rate
547 // res = d1 / d2;
548 Value v = calc->add (calc->mul (rate, type), 1.0);
549 Value d1 = calc->sub (calc->mul (pmt, v), calc->mul (fv, rate));
550 Value d2 = calc->add (calc->mul (pmt, v), calc->mul (pv, rate));
551 Value res = calc->div (d1, d2);
552
553 if (!calc->greater (res, 0.0)) // res must be >0
554 return Value::errorVALUE();
555
556 // ln (res) / ln (rate + 1.0)
557 return calc->div (calc->ln (res), calc->ln (calc->add (rate, 1.0)));
558}
559
560// Function: ISPMT
561Value func_ispmt (valVector args, ValueCalc *calc, FuncExtra *)
562{
563 Value rate = args[0];
564 Value per = args[1];
565 Value nper = args[2];
566 Value pv = args[3];
567
568 if (calc->lower (per, 1) || calc->greater (per, nper))
569 return Value::errorVALUE();
570
571 // d = -pv * rate
572 Value d = calc->mul (calc->mul (pv, -1), rate);
573
574 // d - (d / nper * per)
575 return calc->sub (d, calc->mul (calc->div (d, nper), per));
576}
577
578// Function: IPMT
579Value func_ipmt (valVector args, ValueCalc *calc, FuncExtra *)
580{
581 Value rate = args[0];
582 Value per = args[1];
583 Value nper = args[2];
584 Value pv = args[3];
585
586 Value fv = 0.0;
587 Value type = 0;
588 if (args.count() > 4) fv = args[4];
589 if (args.count() == 6) type = args[5];
590
591 Value payment = getPay (calc, rate, nper, pv, fv, type);
592 Value ineg = getPrinc (calc, pv, payment, rate, calc->sub (per, 1));
593
594 // -ineg * rate
595 return calc->mul (calc->mul (ineg, -1), rate);
596}
597
598// Function: PPMT
599// Uses IPMT.
600Value func_ppmt (valVector args, ValueCalc *calc, FuncExtra *)
601{
602 /*
603Docs partly copied from OO.
604Syntax
605PPMT(Rate;Period;NPER;PV;FV;Type)
606
607Rate is the periodic interest rate.
608Period is the amortizement period. P=1 for the first and P=NPER for the last period.
609NPER is the total number of periods during which annuity is paid.
610PV is the present value in the sequence of payments.
611FV (optional) is the desired (future) value.
612Type (optional) defines the due date. F=1 for payment at the beginning of a period and F=0 for payment at the end of a period.
613 */
614
615 Value rate = args[0];
616 Value per = args[1];
617 Value nper = args[2];
618 Value pv = args[3];
619 Value fv = 0.0;
620 Value type = 0;
621 if (args.count() > 4) fv = args[4];
622 if (args.count() == 6) type = args[5];
623
624 Value pay = getPay (calc, rate, nper, pv, fv, type);
625 Value ipmt = func_ipmt (args, calc, 0);
626 return calc->sub (pay, ipmt);
627}
628
629// Function: FV
630/* Returns future value, given current value, interest rate and time */
631Value func_fv (valVector args, ValueCalc *calc, FuncExtra *)
632{
633 Value present = args[0];
634 Value interest = args[1];
635 Value periods = args[2];
636
637 // present * pow (1 + interest, periods)
638 return calc->mul (present, calc->pow (calc->add (interest, 1), periods));
639}
640
641// Function: compound
642/* Returns value after compounded interest, given principal, rate, periods
643per year and year */
644 Value func_compound (valVector args, ValueCalc *calc, FuncExtra *)
645{
646 Value principal = args[0];
647 Value interest = args[1];
648 Value periods = args[2];
649 Value years = args[3];
650
651 // principal * pow(1+ (interest / periods), periods*years);
652 Value base = calc->add (calc->div (interest, periods), 1);
653 return calc->mul (principal, calc->pow (base, calc->mul (periods, years)));
654}
655
656// Function: continuous
657/* Returns value after continuous compounding of interest, given principal,
658rate and years */
659Value func_continuous (valVector args, ValueCalc *calc, FuncExtra *)
660{
661 // If you still don't understand this, let me know! ;-) jsinger@leeta.net
662 Value principal = args[0];
663 Value interest = args[1];
664 Value years = args[2];
665
666 // principal * exp(interest * years)
667 return calc->mul (principal, calc->exp (calc->mul (interest, years)));
668}
669
670// Function: PV
671Value func_pv (valVector args, ValueCalc *calc, FuncExtra *)
672{
673/* Returns presnt value, given future value, interest rate and years */
674 Value future = args[0];
675 Value interest = args[1];
676 Value periods = args[2];
677
678 // future / pow(1+interest, periods)
679 return calc->div (future, calc->pow (calc->add (interest, 1), periods));
680}
681
682// Function: PV_annuity
683Value func_pv_annuity (valVector args, ValueCalc *calc, FuncExtra *)
684{
685 Value amount = args[0];
686 Value interest = args[1];
687 Value periods = args[2];
688
689 // recpow = 1 / pow (1 + interest, periods)
690 // result = amount * (1 - recpow) / interest;
691 Value recpow;
692 recpow = calc->div (1, calc->pow (calc->add (interest, 1), periods));
693 return calc->mul (amount, calc->div (calc->sub (1, recpow), interest));
694}
695
696// Function: FV_annnuity
697Value func_fv_annuity (valVector args, ValueCalc *calc, FuncExtra *)
698{
699 /* Returns future value of an annuity or cash flow, given payment, interest
700 rate and periods */
701
702 Value amount = args[0];
703 Value interest = args[1];
704 Value periods = args[2];
705
706 // pw = pow (1 + interest, periods)
707 // result = amount * ((pw - 1) / interest)
708 Value pw = calc->pow (calc->add (interest, 1), periods);
709 return calc->mul (amount, calc->div (calc->sub (pw, 1), interest));
710}
711
712// Function: effective
713Value func_effective (valVector args, ValueCalc *calc, FuncExtra *)
714{
715 // Returns effective interest rate given nominal rate and periods per year
716
717 Value nominal = args[0];
718 Value periods = args[1];
719
720 // base = 1 + (nominal / periods)
721 // result = pow (base, periods) - 1
722 Value base = calc->add (calc->div (nominal, periods), 1);
723 return calc->sub (calc->pow (base, periods), 1);
724}
725
726// Function: zero_coupon
727Value func_zero_coupon (valVector args, ValueCalc *calc, FuncExtra *)
728{
729 // Returns effective interest rate given nominal rate and periods per year
730
731 Value face = args[0];
732 Value rate = args[1];
733 Value years = args[2];
734
735 // face / pow(1 + rate, years)
736 return calc->div (face, calc->pow (calc->add (rate, 1), years));
737}
738
739// Function: level_coupon
740Value func_level_coupon (valVector args, ValueCalc *calc, FuncExtra *)
741{
742 // Returns effective interest rate given nominal rate and periods per year
743 Value face = args[0];
744 Value coupon_rate = args[1];
745 Value coupon_year = args[2];
746 Value years = args[3];
747 Value market_rate = args[4];
748
749 Value coupon, interest, pw, pv_annuity;
750 // coupon = coupon_rate * face / coupon_year
751 // interest = market_rate / coupon_year
752 // pw = pow(1 + interest, years * coupon_year)
753 // pv_annuity = (1 - 1 / pw) / interest
754 // result = coupon * pv_annuity + face / pw
755 coupon = calc->mul (coupon_rate, calc->div (face, coupon_year));
756 interest = calc->div (market_rate, coupon_year);
757 pw = calc->pow (calc->add (interest, 1), calc->mul (years, coupon_year));
758 pv_annuity = calc->div (calc->sub (1, calc->div (1, pw)), interest);
759 return calc->add (calc->mul (coupon, pv_annuity), calc->div (face, pw));
760}
761
762// Function: nominal
763Value func_nominal (valVector args, ValueCalc *calc, FuncExtra *)
764{
765 Value effective = args[0];
766 Value periods = args[1];
767
768 if (calc->isZero (periods)) // Check null
769 return Value::errorDIV0();
770
771 // pw = pow (effective + 1, 1 / periods)
772 // result = periods * (pw - 1);
773 Value pw;
774 pw = calc->pow (calc->add (effective, 1), calc->div (1, periods));
775 return calc->mul (periods, calc->sub (pw, 1));
776}
777
778// Function: SLN
779/* straight-line depreciation for a single period */
780Value func_sln (valVector args, ValueCalc *calc, FuncExtra *)
781{
782 Value cost = args[0];
783 Value salvage_value = args[1];
784 Value life = args[2];
785
786 // sentinel check
787 if (!calc->greater (life, 0.0))
788 return Value::errorVALUE();
789
790 // (cost - salvage_value) / life
791 return calc->div (calc->sub (cost, salvage_value), life);
792}
793
794// Function: SYD
795/* sum-of-years digits depreciation */
796Value func_syd (valVector args, ValueCalc *calc, FuncExtra *)
797{
798 Value cost = args[0];
799 Value salvage_value = args[1];
800 Value life = args[2];
801 Value period = args[3];
802
803 // sentinel check
804 if (!calc->greater (life, 0.0))
805 return Value::errorVALUE();
806
807 // v1 = cost - salvage_value
808 // v2 = life - period + 1
809 // v3 = life * (life + 1.0)
810 // result = (v1 * v2 * 2) / v3
811 Value v1, v2, v3;
812 v1 = calc->sub (cost, salvage_value);
813 v2 = calc->add (calc->sub (life, period), 1);
814 v3 = calc->mul (life, calc->add (life, 1.0));
815 return calc->div (calc->mul (calc->mul (v1, v2), 2), v3);
816}
817
818// Function: DB
819/* fixed-declining depreciation */
820Value func_db (valVector args, ValueCalc *calc, FuncExtra *)
821{
822 // This function doesn't support extended datatypes, it simply
823 // converts everything to double - because it does quite a bit
824 // of computing, and, well, I'm lazy to convert it all (Tomas)
825 double cost = calc->conv()->asFloat (args[0]).asFloat();
826 double salvage = calc->conv()->asFloat (args[1]).asFloat();
827 double life = calc->conv()->asFloat (args[2]).asFloat();
828 double period = calc->conv()->asFloat (args[3]).asFloat();
829 double month = 12;
830 if (args.count() == 5)
831 month = calc->conv()->asFloat (args[4]).asFloat();
832
833 // sentinel check
834 if (cost == 0 || life <= 0.0)
835 return Value::errorVALUE ();
836
837 if (calc->lower (calc->div (salvage, cost), 0))
838 return Value::errorVALUE ();
839
840 double rate = 1000 * (1 - pow( (salvage/cost), (1/life) ));
841 rate = floor( rate + 0.5 ) / 1000;
842
843 double total = cost * rate * month / 12;
844
845 if( period == 1 )
846 return Value (total);
847
848 for (int i = 1; i < life; ++i)
849 if (i == period - 1)
850 return Value (rate * (cost-total));
851 else total += rate * (cost-total);
852
853 return Value ((cost-total) * rate * (12-month)/12);
854}
855
856// Function: DDB
857/* depreciation per period */
858Value func_ddb (valVector args, ValueCalc *calc, FuncExtra *)
859{
860 double cost = calc->conv()->asFloat (args[0]).asFloat();
861 double salvage = calc->conv()->asFloat (args[1]).asFloat();
862 double life = calc->conv()->asFloat (args[2]).asFloat();
863 double period = calc->conv()->asFloat (args[3]).asFloat();
864 double factor = 2;
865 if (args.count() == 5)
866 factor = calc->conv()->asFloat (args[4]).asFloat();
867
868 double total = 0.0;
869
870 if ( cost < 0.0 || salvage < 0.0 || life <= 0.0 || period < 0.0 || factor < 0.0 )
871 return Value::errorVALUE();
872
873 for( int i = 0; i < life-1; ++i )
874 {
875 double periodDep = ( cost - total ) * ( factor / life );
876 if ( i == period - 1 )
877 return Value (periodDep);
878 else
879 total += periodDep;
880 }
881
882 return Value (cost - total - salvage);
883}
884
885// Function: EURO
886Value func_euro (valVector args, ValueCalc *calc, FuncExtra *)
887{
888 TQString currency = calc->conv()->asString (args[0]).asString().upper();
889 double result = -1;
890
891 if( currency == "ATS" ) result = 13.7603; // Austria
892 else if( currency == "BEF" ) result = 40.3399; // Belgium
893 else if( currency == "DEM" ) result = 1.95583; // Germany
894 else if( currency == "ESP" ) result = 166.386; // Spain
895 else if( currency == "FIM" ) result = 5.94573; // Finland
896 else if( currency == "FRF" ) result = 6.55957; // France
897 else if( currency == "GRD" ) result = 340.75; // Greece
898 else if( currency == "IEP" ) result = 0.787564; // Ireland
899 else if( currency == "ITL" ) result = 1936.27; // Italy
900 else if( currency == "LUX" ) result = 40.3399; // Luxemburg
901 else if( currency == "NLG" ) result = 2.20371; // Nederland
902 else if( currency == "PTE" ) result = 200.482; // Portugal
903 else
904 return Value::errorVALUE();
905
906 return Value (result);
907}
KSpread::ValueCalc
The ValueCalc class is used to perform all sorts of calculations.
Definition valuecalc.h:69
KSpread::ValueCalc::greater
bool greater(const Value &a, const Value &b)
numerical comparison
Definition valuecalc.cpp:502
KSpread::ValueCalc::isZero
bool isZero(const Value &a)
comparison and related
Definition valuecalc.cpp:403
KSpread::ValueCalc::roundDown
Value roundDown(const Value &a, const Value &digits)
rounding
Definition valuecalc.cpp:519
KSpread::ValueCalc::lower
bool lower(const Value &a, const Value &b)
numerical comparison
Definition valuecalc.cpp:514
KSpread::ValueCalc::add
Value add(const Value &a, const Value &b)
basic arithmetic operations
Definition valuecalc.cpp:176
KSpread::Value
Provides a wrapper for cell value.
Definition kspread_value.h:42
KSpread::Value::errorDIV0
static const Value & errorDIV0()
Returns constant reference to #DIV/0! error.
Definition kspread_value.cpp:667
KSpread::Value::asDate
TQDate asDate() const
Returns the date representation of this value.
Definition kspread_value.cpp:598
KSpread::Value::asFloat
double asFloat() const
Returns the floating-point value of this value.
Definition kspread_value.cpp:501
KSpread::Value::asInteger
long asInteger() const
Returns the integer value of this value.
Definition kspread_value.cpp:473
KSpread::Value::asString
TQString asString() const
Returns the string value of this value.
Definition kspread_value.cpp:524
KSpread::Value::errorVALUE
static const Value & errorVALUE()
Returns constant reference to #VALUE! error.
Definition kspread_value.cpp:715
KSpread::Value::asBoolean
bool asBoolean() const
Returns the boolean value of this value.
Definition kspread_value.cpp:444
KSpread
The KSpread namespace.
Definition commands.h:39