KOffice – TDE office suite
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

908 lines
27KB

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