kspread

kspread_functions_financial.cc

00001 /* This file is part of the KDE project
00002    Copyright (C) 1998-2002 The KSpread Team
00003                            www.koffice.org/kspread
00004    Copyright (C) 2005 Tomas Mecir <mecirt@gmail.com>
00005 
00006    This library is free software; you can redistribute it and/or
00007    modify it under the terms of the GNU Library General Public
00008    License as published by the Free Software Foundation; either
00009    version 2 of the License.
00010 
00011    This library is distributed in the hope that it will be useful,
00012    but WITHOUT ANY WARRANTY; without even the implied warranty of
00013    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
00014    Library General Public License for more details.
00015 
00016    You should have received a copy of the GNU Library General Public License
00017    along with this library; see the file COPYING.LIB.  If not, write to
00018    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
00019  * Boston, MA 02110-1301, USA.
00020 */
00021 
00022 // built-in financial functions
00023 
00024 #include <math.h>
00025 
00026 #include "functions.h"
00027 #include "kspread_functions_helper.h"
00028 #include "valuecalc.h"
00029 #include "valueconverter.h"
00030 
00031 #include <klocale.h>
00032 #include <kcalendarsystem.h>
00033 
00034 using namespace KSpread;
00035 
00036 // prototypes (sorted)
00037 Value func_accrint (valVector args, ValueCalc *calc, FuncExtra *);
00038 Value func_accrintm (valVector args, ValueCalc *calc, FuncExtra *);
00039 Value func_compound (valVector args, ValueCalc *calc, FuncExtra *);
00040 Value func_continuous (valVector args, ValueCalc *calc, FuncExtra *);
00041 Value func_coupnum (valVector args, ValueCalc *calc, FuncExtra *);
00042 Value func_db (valVector args, ValueCalc *calc, FuncExtra *);
00043 Value func_ddb (valVector args, ValueCalc *calc, FuncExtra *);
00044 Value func_disc (valVector args, ValueCalc *calc, FuncExtra *);
00045 Value func_dollarde (valVector args, ValueCalc *calc, FuncExtra *);
00046 Value func_dollarfr (valVector args, ValueCalc *calc, FuncExtra *);
00047 Value func_duration (valVector args, ValueCalc *calc, FuncExtra *);
00048 Value func_effective (valVector args, ValueCalc *calc, FuncExtra *);
00049 Value func_euro (valVector args, ValueCalc *calc, FuncExtra *);
00050 Value func_fv (valVector args, ValueCalc *calc, FuncExtra *);
00051 Value func_fv_annuity (valVector args, ValueCalc *calc, FuncExtra *);
00052 Value func_intrate (valVector args, ValueCalc *calc, FuncExtra *);
00053 Value func_ipmt (valVector args, ValueCalc *calc, FuncExtra *);
00054 Value func_ispmt (valVector args, ValueCalc *calc, FuncExtra *);
00055 Value func_level_coupon (valVector args, ValueCalc *calc, FuncExtra *);
00056 Value func_nominal (valVector args, ValueCalc *calc, FuncExtra *);
00057 Value func_nper (valVector args, ValueCalc *calc, FuncExtra *);
00058 Value func_pmt (valVector args, ValueCalc *calc, FuncExtra *);
00059 Value func_ppmt (valVector args, ValueCalc *calc, FuncExtra *);
00060 Value func_pv (valVector args, ValueCalc *calc, FuncExtra *);
00061 Value func_pv_annuity (valVector args, ValueCalc *calc, FuncExtra *);
00062 Value func_received (valVector args, ValueCalc *calc, FuncExtra *);
00063 Value func_sln (valVector args, ValueCalc *calc, FuncExtra *);
00064 Value func_syd (valVector args, ValueCalc *calc, FuncExtra *);
00065 Value func_tbilleq (valVector args, ValueCalc *calc, FuncExtra *);
00066 Value func_tbillprice (valVector args, ValueCalc *calc, FuncExtra *);
00067 Value func_tbillyield (valVector args, ValueCalc *calc, FuncExtra *);
00068 Value func_zero_coupon (valVector args, ValueCalc *calc, FuncExtra *);
00069 
00070 // registers all financial functions
00071 void RegisterFinancialFunctions()
00072 {
00073   FunctionRepository* repo = FunctionRepository::self();
00074   Function *f;
00075 
00076   f = new Function ("ACCRINT", func_accrint);
00077   f->setParamCount (6, 7);
00078   repo->add (f);
00079   f = new Function ("ACCRINTM", func_accrintm);
00080   f->setParamCount (3, 5);
00081   repo->add (f);
00082   f = new Function ("COMPOUND", func_compound);
00083   f->setParamCount (4);
00084   repo->add (f);
00085   f = new Function ("CONTINUOUS", func_continuous);
00086   f->setParamCount (3);
00087   repo->add (f);
00088   f = new Function ("COUPNUM", func_coupnum);
00089   f->setParamCount (3, 5);
00090   repo->add (f);
00091   f = new Function ("DB", func_db);
00092   f->setParamCount (4, 5);
00093   repo->add (f);
00094   f = new Function ("DDB", func_ddb);
00095   f->setParamCount (4, 5);
00096   repo->add (f);
00097   f = new Function ("DISC", func_disc);
00098   f->setParamCount (4, 5);
00099   repo->add (f);
00100   f = new Function ("DOLLARDE", func_dollarde);
00101   f->setParamCount (2);
00102   repo->add (f);
00103   f = new Function ("DOLLARFR", func_dollarfr);
00104   f->setParamCount (2);
00105   repo->add (f);
00106   f = new Function ("DURATION", func_duration);
00107   f->setParamCount (3);
00108   repo->add (f);
00109   f = new Function ("EFFECT", func_effective);
00110   f->setParamCount (2);
00111   repo->add (f);
00112   f = new Function ("EFFECTIVE", func_effective);
00113   f->setParamCount (2);
00114   repo->add (f);
00115   f = new Function ("EURO", func_euro);  // KSpread-specific, Gnumeric-compatible
00116   f->setParamCount (1);
00117   repo->add (f);
00118   f = new Function ("FV", func_fv);
00119   f->setParamCount (3);
00120   repo->add (f);
00121   f = new Function ("FV_ANNUITY", func_fv_annuity);
00122   f->setParamCount (3);
00123   repo->add (f);
00124   f = new Function ("INTRATE", func_intrate);
00125   f->setParamCount (4, 5);
00126   repo->add (f);
00127   f = new Function ("IPMT", func_ipmt);
00128   f->setParamCount (4, 6);
00129   repo->add (f);
00130   f = new Function ("ISPMT", func_ispmt);
00131   f->setParamCount (4);
00132   repo->add (f);
00133   f = new Function ("LEVEL_COUPON", func_level_coupon);
00134   f->setParamCount (5);
00135   repo->add (f);
00136   f = new Function ("NOMINAL", func_nominal);
00137   f->setParamCount (2);
00138   repo->add (f);
00139   f = new Function ("NPER", func_nper);
00140   f->setParamCount (3, 5);
00141   repo->add (f);
00142   f = new Function ("PMT", func_pmt);
00143   f->setParamCount (3, 5);
00144   repo->add (f);
00145   f = new Function ("PPMT", func_ppmt);
00146   f->setParamCount (4, 6);
00147   repo->add (f);
00148   f = new Function ("PV", func_pv);
00149   f->setParamCount (3);
00150   repo->add (f);
00151   f = new Function ("PV_ANNUITY", func_pv_annuity);
00152   f->setParamCount (3);
00153   repo->add (f);
00154   f = new Function ("RECEIVED", func_received);
00155   f->setParamCount (4, 5);
00156   repo->add (f);
00157   f = new Function ("SLN", func_sln);
00158   f->setParamCount (3);
00159   repo->add (f);
00160   f = new Function ("SYD", func_syd);
00161   f->setParamCount (4);
00162   repo->add (f);
00163   f = new Function ("TBILLEQ", func_tbilleq);
00164   f->setParamCount (3);
00165   repo->add (f);
00166   f = new Function ("TBILLPRICE", func_tbillprice);
00167   f->setParamCount (3);
00168   repo->add (f);
00169   f = new Function ("TBILLYIELD", func_tbillyield);
00170   f->setParamCount (3);
00171   repo->add (f);
00172   f = new Function ("ZERO_COUPON", func_zero_coupon);
00173   f->setParamCount (3);
00174   repo->add (f);
00175 }
00176 
00177 static Value getPay (ValueCalc *calc, Value rate,
00178     Value nper, Value pv, Value fv, Value type)
00179 {
00180   Value pvif, fvifa;
00181 
00182   if (calc->isZero (rate)) return Value::errorVALUE();
00183 
00184   //pvif  = pow( 1 + rate, nper );
00185   //fvifa = ( pvif - 1 ) / rate;
00186   pvif = calc->pow (calc->add (rate, 1), nper);
00187   fvifa = calc->div (calc->sub (pvif, 1), rate);
00188 
00189   // ( -pv * pvif - fv ) / ( ( 1.0 + rate * type ) * fvifa );
00190   Value val1 = calc->sub (calc->mul (calc->mul (-1, pv), pvif), fv);
00191   Value val2 = calc->mul (calc->add (1.0, calc->mul (rate, type)),
00192       fvifa);
00193   return calc->div (val1, val2);
00194 }
00195 
00196 static Value getPrinc (ValueCalc *calc, Value start,
00197     Value pay, Value rate, Value period)
00198 {
00199   // val1 = pow( 1 + rate, period )
00200   Value val1 = calc->pow (calc->add (rate, 1), period);
00201   // val2 = start * val1
00202   Value val2 = calc->mul (start, val1);
00203   // val3 = pay * ( ( val1 - 1 ) / rate )
00204   Value val3 = calc->mul (pay, calc->div (calc->sub (val1, 1), rate));
00205   // result = val2 + val3
00206   return calc->add (val2, val3);
00207 }
00208 
00209 // Function: COUPNUM - taken from GNUMERIC
00210 Value func_coupnum (valVector args, ValueCalc *calc, FuncExtra *)
00211 {
00212   // dates and integers only - don't need high-precision for this
00213   QDate settlement = calc->conv()->asDate (args[0]).asDate();
00214   QDate maturity = calc->conv()->asDate (args[1]).asDate();
00215   int   frequency = calc->conv()->asInteger (args[2]).asInteger();
00216   int   basis = 0;
00217   bool  eom   = true;
00218   if (args.count() > 3)
00219     basis = calc->conv()->asInteger (args[3]).asInteger();
00220   if (args.count() == 5)
00221     eom = calc->conv()->asBoolean (args[4]).asBoolean();
00222 
00223   if (basis < 0 || basis > 5 || ( frequency == 0 ) || ( 12 % frequency != 0 )
00224       || settlement.daysTo( maturity ) <= 0)
00225     return Value::errorVALUE();
00226 
00227   double result;
00228   QDate cDate( maturity );
00229 
00230   int months = maturity.month() - settlement.month()
00231     + 12 * ( maturity.year() - settlement.year() );
00232 
00233   cDate = calc->conv()->locale()->calendar()->addMonths (cDate, -months);
00234 
00235   if ( eom && maturity.daysInMonth() == maturity.day() )
00236   {
00237     while( cDate.daysInMonth() != cDate.day() )
00238       cDate.addDays( 1 );
00239   }
00240 
00241   if ( settlement.day() >= cDate.day() )
00242     --months;
00243 
00244   result = ( 1 + months / ( 12 / frequency ) );
00245 
00246   return Value (result);
00247 }
00248 
00249 // Function: ACCRINT
00250 Value func_accrint (valVector args, ValueCalc *calc, FuncExtra *)
00251 {
00252   QDate maturity = calc->conv()->asDate (args[0]).asDate();
00253   QDate firstInterest = calc->conv()->asDate (args[1]).asDate();
00254   QDate settlement = calc->conv()->asDate (args[2]).asDate();
00255   
00256   Value rate = args[3];
00257   Value par = args[4];
00258   int frequency = calc->conv()->asInteger (args[5]).asInteger();
00259 
00260   int basis = 0;
00261   if (args.count() == 7)
00262     basis = calc->conv()->asInteger (args[6]).asInteger();
00263   
00264   if ( basis < 0 || basis > 4 || (calc->isZero (frequency)) ||
00265       (12 % frequency != 0))
00266     return Value::errorVALUE();
00267 
00268   if ( ( settlement.daysTo( firstInterest ) < 0 )
00269        || ( firstInterest.daysTo( maturity ) > 0 ) )
00270     return Value::errorVALUE();
00271 
00272   double d = daysBetweenDates (maturity, settlement, basis);
00273   double y = daysPerYear (maturity, basis);
00274 
00275   if ( d < 0 || y <= 0 || calc->lower (par, 0) || calc->lower (rate, 0) ||
00276       calc->isZero (rate))
00277     return Value::errorVALUE();
00278 
00279   Value coeff = calc->div (calc->mul (par, rate), frequency);
00280   double n = d / y;
00281 
00282   return calc->mul (coeff, n * frequency);
00283 }
00284 
00285 // Function: ACCRINTM
00286 Value func_accrintm (valVector args, ValueCalc *calc, FuncExtra *)
00287 {
00288   QDate issue = calc->conv()->asDate (args[0]).asDate();
00289   QDate maturity = calc->conv()->asDate (args[1]).asDate();
00290   Value rate = args[2];
00291   
00292   Value par = 1000;
00293   int basis = 0;
00294   if (args.count() > 3)
00295     par = args[3];
00296   if (args.count() == 5)
00297     basis = calc->conv()->asInteger (args[4]).asInteger ();
00298 
00299   double d = daysBetweenDates (issue, maturity, basis);
00300   double y = daysPerYear (issue, basis);
00301 
00302   if (d < 0 || y <= 0 || calc->isZero (par) || calc->isZero (rate) ||
00303       calc->lower (par, 0) || calc->lower (rate, 0) || basis < 0 || basis > 4)
00304     return Value::errorVALUE();
00305 
00306   // par*date * d/y
00307   return calc->mul (calc->mul (par, rate), d / y);
00308 }
00309 
00310 // Function: DISC
00311 Value func_disc (valVector args, ValueCalc *calc, FuncExtra *)
00312 {
00313   QDate settlement = calc->conv()->asDate (args[0]).asDate();
00314   QDate maturity = calc->conv()->asDate (args[1]).asDate();
00315 
00316   Value par = args[2];
00317   Value redemp = args[3];
00318   
00319   int basis = 0;
00320   if (args.count() == 5)
00321     basis = calc->conv()->asInteger (args[4]).asInteger();
00322 
00323   double y = daysPerYear (settlement, basis);
00324   double d = daysBetweenDates (settlement, maturity, basis);
00325 
00326   if ( y <= 0 || d <= 0 || basis < 0 || basis > 4 || calc->isZero (redemp) )
00327     return false;
00328 
00329   // (redemp - par) / redemp * (y / d)
00330   return calc->mul (calc->div (calc->sub (redemp, par), redemp), y / d);
00331 }
00332 
00333 
00334 // Function: TBILLPRICE
00335 Value func_tbillprice (valVector args, ValueCalc *calc, FuncExtra *)
00336 {
00337   QDate settlement = calc->conv()->asDate (args[0]).asDate();
00338   QDate maturity = calc->conv()->asDate (args[1]).asDate();
00339 
00340   Value discount = args[2];
00341 
00342   double days = settlement.daysTo( maturity );
00343 
00344   if (settlement > maturity || calc->lower (discount, 0) || days > 265)
00345     return Value::errorVALUE();
00346 
00347   // (discount * days) / 360.0
00348   Value val = calc->div (calc->mul (discount, days), 360.0);
00349   // 100 * (1.0 - val);
00350   return calc->mul (calc->sub (1.0, val), 100);
00351 }
00352 
00353 // Function: TBILLYIELD
00354 Value func_tbillyield (valVector args, ValueCalc *calc, FuncExtra *)
00355 {
00356   QDate settlement = calc->conv()->asDate (args[0]).asDate();
00357   QDate maturity = calc->conv()->asDate (args[1]).asDate();
00358 
00359   Value rate = args[2];
00360 
00361   double days = settlement.daysTo( maturity );
00362 
00363   if (settlement > maturity || calc->isZero (rate) || calc->lower (rate, 0)
00364       || days > 265)
00365     return Value::errorVALUE();
00366 
00367   // (100.0 - rate) / rate * (360.0 / days);
00368   return calc->mul (calc->div (calc->sub (100.0, rate), rate), 360.0 / days);
00369 }
00370 
00371 // Function: TBILLEQ
00372 Value func_tbilleq (valVector args, ValueCalc *calc, FuncExtra *)
00373 {
00374   QDate settlement = calc->conv()->asDate (args[0]).asDate();
00375   QDate maturity = calc->conv()->asDate (args[1]).asDate();
00376 
00377   Value discount = args[2];
00378 
00379   double days = settlement.daysTo( maturity );
00380 
00381   if (settlement > maturity || calc->lower (discount, 0) || days > 265)
00382     return Value::errorVALUE();
00383 
00384   // 360 - discount*days
00385   Value divisor = calc->sub (360.0, calc->mul (discount, days));
00386   if (calc->isZero (divisor))
00387     return Value::errorVALUE();
00388 
00389   // 365.0 * discount / divisor
00390   return calc->mul (calc->div (discount, divisor), 356.0);
00391 }
00392 
00393 // Function: RECEIVED
00394 Value func_received (valVector args, ValueCalc *calc, FuncExtra *)
00395 {
00396 
00397   QDate settlement = calc->conv()->asDate (args[0]).asDate();
00398   QDate maturity = calc->conv()->asDate (args[1]).asDate();
00399 
00400   Value investment = args[2];
00401   Value discount = args[3];
00402 
00403   int basis = 0;
00404   if (args.count() == 5)
00405     basis = calc->conv()->asInteger (args[4]).asInteger();
00406 
00407   double d = daysBetweenDates( settlement, maturity, basis );
00408   double y = daysPerYear( settlement, basis );
00409 
00410   if ( d <= 0 || y <= 0 || basis < 0 || basis > 4 )
00411     return false;
00412 
00413   // 1.0 - ( discount * d / y )
00414   Value x = calc->sub (1.0, (calc->mul (discount, d / y)));
00415 
00416   if (calc->isZero (x))
00417     return Value::errorVALUE();
00418   return calc->div (investment, x);
00419 }
00420 
00421 // Function: DOLLARDE
00422 Value func_dollarde (valVector args, ValueCalc *calc, FuncExtra *)
00423 {
00424   Value d = args[0];
00425   Value f = args[1];
00426 
00427   if (!calc->greater (f, 0))
00428     return Value::errorVALUE();
00429 
00430   Value tmp = f;
00431   int n = 0;
00432   while (calc->greater (tmp, 0))
00433   {
00434     tmp = calc->div (tmp, 10);
00435     ++n;
00436   }
00437 
00438   Value fl = calc->roundDown (d);
00439   Value r = calc->sub (d, fl);
00440 
00441   // fl + (r * pow(10.0, n) / f)
00442   return calc->add (fl, calc->div (calc->mul (r, pow (10.0, n)), f));
00443 }
00444 
00445 // Function: DOLLARFR
00446 Value func_dollarfr (valVector args, ValueCalc *calc, FuncExtra *)
00447 {
00448   Value d = args[0];
00449   Value f = args[1];
00450 
00451   if (!calc->greater (f, 0))
00452     return Value::errorVALUE();
00453 
00454   Value tmp = f;
00455   int n = 0;
00456   while (calc->greater (tmp, 0))
00457   {
00458     tmp = calc->div (tmp, 10);
00459     ++n;
00460   }
00461 
00462   Value fl = calc->roundDown (d);
00463   Value r = calc->sub (d, fl);
00464 
00465   // fl + ((r * f) / pow (10.0, n));
00466   return calc->add (fl, calc->div (calc->mul (r, f), pow (10.0, n)));
00467 }
00468 
00470 
00471 // Function: INTRATE
00472 Value func_intrate (valVector args, ValueCalc *calc, FuncExtra *)
00473 {
00474   QDate settlement = calc->conv()->asDate (args[0]).asDate();
00475   QDate maturity = calc->conv()->asDate (args[1]).asDate();
00476 
00477   Value invest = args[2];
00478   Value redemption = args[3];
00479 
00480   int basis = 0;
00481   if (args.count() == 5)
00482     basis = calc->conv()->asInteger (args[4]).asInteger();
00483 
00484   double d = daysBetweenDates (settlement, maturity, basis);
00485   double y = daysPerYear (settlement, basis);
00486 
00487   if ( d <= 0 || y <= 0 || calc->isZero (invest) || basis < 0 || basis > 4 )
00488     return Value::errorVALUE();
00489 
00490   // (redemption - invest) / invest * (y / d)
00491   return calc->mul (calc->div (calc->sub (redemption, invest), invest), y/d);
00492 }
00493 
00494 
00495 // Function: DURATION
00496 Value func_duration (valVector args, ValueCalc *calc, FuncExtra *)
00497 {
00498   Value rate = args[0];
00499   Value pv   = args[1];
00500   Value fv   = args[2];
00501 
00502   if (!calc->greater (rate, 0.0))
00503     return Value::errorVALUE();
00504   if (calc->isZero (fv) || calc->isZero (pv))
00505     return Value::errorDIV0();
00506   
00507   if (calc->lower (calc->div (fv, pv), 0))
00508     return Value::errorVALUE();
00509 
00510   // log(fv / pv) / log(1.0 + rate)
00511   return calc->div (calc->ln (calc->div (fv, pv)),
00512       calc->ln (calc->add (rate, 1.0)));
00513 }
00514 
00515 // Function: PMT
00516 Value func_pmt (valVector args, ValueCalc *calc, FuncExtra *)
00517 {
00518   Value rate = args[0];
00519   Value nper = args[1];
00520   Value pv   = args[2];
00521   Value fv = 0.0;
00522   Value type = 0;
00523   if (args.count() > 3) fv = args[3];
00524   if (args.count() == 5) type = args[4];
00525 
00526   return getPay (calc, rate, nper, pv, fv, type);
00527 }
00528 
00529 // Function: NPER
00530 Value func_nper (valVector args, ValueCalc *calc, FuncExtra *)
00531 {
00532   Value rate = args[0];
00533   Value pmt  = args[1];
00534   Value pv   = args[2];
00535   Value fv = 0.0;
00536   Value type = 0;
00537   if (args.count() > 3) fv = args[3];
00538   if (args.count() == 5) type = args[4];
00539 
00540   if (!calc->greater (rate, 0.0))
00541     return Value::errorVALUE();
00542 
00543   // taken from Gnumeric
00544   // v = 1.0 + rate * type
00545   // d1 = pmt * v - fv * rate
00546   // d2 = pmt * v - pv * rate
00547   // res = d1 / d2;
00548   Value v = calc->add (calc->mul (rate, type), 1.0);
00549   Value d1 = calc->sub (calc->mul (pmt, v), calc->mul (fv, rate));
00550   Value d2 = calc->add (calc->mul (pmt, v), calc->mul (pv, rate));
00551   Value res = calc->div (d1, d2);
00552 
00553   if (!calc->greater (res, 0.0))  // res must be >0
00554     return Value::errorVALUE();
00555 
00556   // ln (res) / ln (rate + 1.0)
00557   return calc->div (calc->ln (res), calc->ln (calc->add (rate, 1.0)));
00558 }
00559 
00560 // Function: ISPMT
00561 Value func_ispmt (valVector args, ValueCalc *calc, FuncExtra *)
00562 {
00563   Value rate = args[0];
00564   Value per  = args[1];
00565   Value nper = args[2];
00566   Value pv   = args[3];
00567 
00568   if (calc->lower (per, 1) || calc->greater (per, nper))
00569     return Value::errorVALUE();
00570 
00571   // d = -pv * rate
00572   Value d = calc->mul (calc->mul (pv, -1), rate);
00573 
00574   // d - (d / nper * per)
00575   return calc->sub (d, calc->mul (calc->div (d, nper), per));
00576 }
00577 
00578 // Function: IPMT
00579 Value func_ipmt (valVector args, ValueCalc *calc, FuncExtra *)
00580 {
00581   Value rate = args[0];
00582   Value per  = args[1];
00583   Value nper = args[2];
00584   Value pv   = args[3];
00585   
00586   Value fv = 0.0;
00587   Value type = 0;
00588   if (args.count() > 4) fv = args[4];
00589   if (args.count() == 6) type = args[5];
00590 
00591   Value payment = getPay (calc, rate, nper, pv, fv, type);
00592   Value ineg = getPrinc (calc, pv, payment, rate, calc->sub (per, 1));
00593 
00594   // -ineg * rate
00595   return calc->mul (calc->mul (ineg, -1), rate);
00596 }
00597 
00598 // Function: PPMT
00599 // Uses IPMT.
00600 Value func_ppmt (valVector args, ValueCalc *calc, FuncExtra *)
00601 {
00602   /*
00603 Docs partly copied from OO.
00604 Syntax
00605 PPMT(Rate;Period;NPER;PV;FV;Type)
00606 
00607 Rate is the periodic interest rate.
00608 Period is the amortizement period. P=1 for the first and P=NPER for the last period.
00609 NPER is the total number of periods during which annuity is paid.
00610 PV is the present value in the sequence of payments.
00611 FV (optional) is the desired (future) value.
00612 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.
00613   */
00614 
00615   Value rate = args[0];
00616   Value per  = args[1];
00617   Value nper = args[2];
00618   Value pv   = args[3];
00619   Value fv = 0.0;
00620   Value type = 0;
00621   if (args.count() > 4) fv = args[4];
00622   if (args.count() == 6) type = args[5];
00623   
00624   Value pay  = getPay (calc, rate, nper, pv, fv, type);
00625   Value ipmt = func_ipmt (args, calc, 0);
00626   return calc->sub (pay, ipmt);
00627 }
00628 
00629 // Function: FV
00630 /* Returns future value, given current value, interest rate and time */
00631 Value func_fv (valVector args, ValueCalc *calc, FuncExtra *)
00632 {
00633   Value present = args[0];
00634   Value interest = args[1];
00635   Value periods = args[2];
00636 
00637   // present * pow (1 + interest, periods)
00638   return calc->mul (present, calc->pow (calc->add (interest, 1), periods));
00639 }
00640 
00641 // Function: compound
00642 /* Returns value after compounded interest, given principal, rate, periods
00643 per year and year */
00644  Value func_compound (valVector args, ValueCalc *calc, FuncExtra *)
00645 {
00646   Value principal = args[0];
00647   Value interest = args[1];
00648   Value periods = args[2];
00649   Value years = args[3];
00650 
00651   // principal * pow(1+ (interest / periods), periods*years);
00652   Value base = calc->add (calc->div (interest, periods), 1);
00653   return calc->mul (principal, calc->pow (base, calc->mul (periods, years)));
00654 }
00655 
00656 // Function: continuous
00657 /* Returns value after continuous compounding of interest, given principal,
00658 rate and years */
00659 Value func_continuous (valVector args, ValueCalc *calc, FuncExtra *)
00660 {
00661   // If you still don't understand this, let me know!  ;-)  jsinger@leeta.net
00662   Value principal = args[0];
00663   Value interest = args[1];
00664   Value years = args[2];
00665 
00666   // principal * exp(interest * years)
00667   return calc->mul (principal, calc->exp (calc->mul (interest, years)));
00668 }
00669 
00670 // Function: PV
00671 Value func_pv (valVector args, ValueCalc *calc, FuncExtra *)
00672 {
00673 /* Returns presnt value, given future value, interest rate and years */
00674   Value future = args[0];
00675   Value interest = args[1];
00676   Value periods = args[2];
00677 
00678   // future / pow(1+interest, periods)
00679   return calc->div (future, calc->pow (calc->add (interest, 1), periods));
00680 }
00681 
00682 // Function: PV_annuity
00683 Value func_pv_annuity (valVector args, ValueCalc *calc, FuncExtra *)
00684 {
00685   Value amount = args[0];
00686   Value interest = args[1];
00687   Value periods = args[2];
00688   
00689   // recpow = 1 / pow (1 + interest, periods)
00690   // result = amount * (1 - recpow) / interest;
00691   Value recpow;
00692   recpow = calc->div (1, calc->pow (calc->add (interest, 1), periods));
00693   return calc->mul (amount, calc->div (calc->sub (1, recpow), interest));
00694 }
00695 
00696 // Function: FV_annnuity
00697 Value func_fv_annuity (valVector args, ValueCalc *calc, FuncExtra *)
00698 {
00699   /* Returns future value of an annuity or cash flow, given payment, interest
00700      rate and periods */
00701 
00702   Value amount = args[0];
00703   Value interest = args[1];
00704   Value periods = args[2];
00705 
00706   // pw = pow (1 + interest, periods)
00707   // result = amount * ((pw - 1) / interest)
00708   Value pw = calc->pow (calc->add (interest, 1), periods);
00709   return calc->mul (amount, calc->div (calc->sub (pw, 1), interest));
00710 }
00711 
00712 // Function: effective
00713 Value func_effective (valVector args, ValueCalc *calc, FuncExtra *)
00714 {
00715   // Returns effective interest rate given nominal rate and periods per year
00716 
00717   Value nominal = args[0];
00718   Value periods = args[1];
00719 
00720   // base = 1 + (nominal / periods)
00721   // result = pow (base, periods) - 1
00722   Value base = calc->add (calc->div (nominal, periods), 1);
00723   return calc->sub (calc->pow (base, periods), 1);
00724 }
00725 
00726 // Function: zero_coupon
00727 Value func_zero_coupon (valVector args, ValueCalc *calc, FuncExtra *)
00728 {
00729   // Returns effective interest rate given nominal rate and periods per year
00730 
00731   Value face = args[0];
00732   Value rate = args[1];
00733   Value years = args[2];
00734 
00735   // face / pow(1 + rate, years)
00736   return calc->div (face, calc->pow (calc->add (rate, 1), years));
00737 }
00738 
00739 // Function: level_coupon
00740 Value func_level_coupon (valVector args, ValueCalc *calc, FuncExtra *)
00741 {
00742   // Returns effective interest rate given nominal rate and periods per year
00743   Value face = args[0];
00744   Value coupon_rate = args[1];
00745   Value coupon_year = args[2];
00746   Value years = args[3];
00747   Value market_rate = args[4];
00748 
00749   Value coupon, interest, pw, pv_annuity;
00750   // coupon = coupon_rate * face / coupon_year
00751   // interest = market_rate / coupon_year
00752   // pw = pow(1 + interest, years * coupon_year)
00753   // pv_annuity = (1 - 1 / pw) / interest
00754   // result = coupon * pv_annuity + face / pw
00755   coupon = calc->mul (coupon_rate, calc->div (face, coupon_year));
00756   interest = calc->div (market_rate, coupon_year);
00757   pw = calc->pow (calc->add (interest, 1), calc->mul (years, coupon_year));
00758   pv_annuity = calc->div (calc->sub (1, calc->div (1, pw)), interest);
00759   return calc->add (calc->mul (coupon, pv_annuity), calc->div (face, pw));
00760 }
00761 
00762 // Function: nominal
00763 Value func_nominal (valVector args, ValueCalc *calc, FuncExtra *)
00764 {
00765   Value effective = args[0];
00766   Value periods = args[1];
00767 
00768   if (calc->isZero (periods)) // Check null
00769     return Value::errorDIV0();
00770   
00771   // pw = pow (effective + 1, 1 / periods)
00772   // result = periods * (pw - 1);
00773   Value pw;
00774   pw = calc->pow (calc->add (effective, 1), calc->div (1, periods));
00775   return calc->mul (periods, calc->sub (pw, 1));
00776 }
00777 
00778 // Function: SLN
00779 /* straight-line depreciation for a single period */
00780 Value func_sln (valVector args, ValueCalc *calc, FuncExtra *)
00781 {
00782   Value cost = args[0];
00783   Value salvage_value = args[1];
00784   Value life = args[2];
00785 
00786   // sentinel check
00787   if (!calc->greater (life, 0.0))
00788     return Value::errorVALUE();
00789 
00790   // (cost - salvage_value) / life
00791   return calc->div (calc->sub (cost, salvage_value), life);
00792 }
00793 
00794 // Function: SYD
00795 /* sum-of-years digits depreciation */
00796 Value func_syd (valVector args, ValueCalc *calc, FuncExtra *)
00797 {
00798   Value cost = args[0];
00799   Value salvage_value = args[1];
00800   Value life = args[2];
00801   Value period = args[3];
00802 
00803   // sentinel check
00804   if (!calc->greater (life, 0.0))
00805     return Value::errorVALUE();
00806 
00807   // v1 = cost - salvage_value
00808   // v2 = life - period + 1
00809   // v3 = life * (life + 1.0)
00810   // result = (v1 * v2 * 2) / v3
00811   Value v1, v2, v3;
00812   v1 = calc->sub (cost, salvage_value);
00813   v2 = calc->add (calc->sub (life, period), 1);
00814   v3 = calc->mul (life, calc->add (life, 1.0));
00815   return calc->div (calc->mul (calc->mul (v1, v2), 2), v3);
00816 }
00817 
00818 // Function: DB
00819 /* fixed-declining depreciation */
00820 Value func_db (valVector args, ValueCalc *calc, FuncExtra *)
00821 {
00822   // This function doesn't support extended datatypes, it simply
00823   // converts everything to double - because it does quite a bit
00824   // of computing, and, well, I'm lazy to convert it all (Tomas)
00825   double cost = calc->conv()->asFloat (args[0]).asFloat();
00826   double salvage = calc->conv()->asFloat (args[1]).asFloat();
00827   double life = calc->conv()->asFloat (args[2]).asFloat();
00828   double period = calc->conv()->asFloat (args[3]).asFloat();
00829   double month = 12;
00830   if (args.count() == 5)
00831     month = calc->conv()->asFloat (args[4]).asFloat();
00832 
00833   // sentinel check
00834   if (cost == 0 || life <= 0.0)
00835     return Value::errorVALUE ();
00836 
00837   if (calc->lower (calc->div (salvage, cost), 0))
00838     return Value::errorVALUE ();
00839 
00840   double rate = 1000 * (1 - pow( (salvage/cost), (1/life) ));
00841   rate = floor( rate + 0.5 )  / 1000;
00842 
00843   double total = cost * rate * month / 12;
00844 
00845   if( period == 1 )
00846     return Value (total);
00847 
00848   for (int i = 1; i < life; ++i)
00849     if (i == period - 1)
00850       return Value (rate * (cost-total));
00851     else total += rate * (cost-total);
00852 
00853   return Value ((cost-total) * rate * (12-month)/12);
00854 }
00855 
00856 // Function: DDB
00857 /* depreciation per period */
00858 Value func_ddb (valVector args, ValueCalc *calc, FuncExtra *)
00859 {
00860   double cost = calc->conv()->asFloat (args[0]).asFloat();
00861   double salvage = calc->conv()->asFloat (args[1]).asFloat();
00862   double life = calc->conv()->asFloat (args[2]).asFloat();
00863   double period = calc->conv()->asFloat (args[3]).asFloat();
00864   double factor = 2;
00865   if (args.count() == 5)
00866     factor = calc->conv()->asFloat (args[4]).asFloat();
00867   
00868   double total   = 0.0;
00869 
00870   if ( cost < 0.0 || salvage < 0.0 || life <= 0.0 || period < 0.0 || factor < 0.0 )
00871     return Value::errorVALUE();
00872 
00873   for( int i = 0; i < life; ++i )
00874   {
00875     double periodDep = ( cost - total ) * ( factor / life );
00876     if ( i == period - 1 )
00877       return Value (periodDep);
00878     else
00879       total += periodDep;
00880   }
00881 
00882   return Value (cost - total - salvage);
00883 }
00884 
00885 // Function: EURO
00886 Value func_euro (valVector args, ValueCalc *calc, FuncExtra *)
00887 {
00888   QString currency = calc->conv()->asString (args[0]).asString().upper();
00889   double result = -1;
00890 
00891   if( currency == "ATS" ) result = 13.7603;  // Austria
00892   else if( currency == "BEF" ) result = 40.3399;  // Belgium
00893   else if( currency == "DEM" ) result = 1.95583;  // Germany
00894   else if( currency == "ESP" ) result = 166.386;  // Spain
00895   else if( currency == "FIM" ) result = 5.94573;  // Finland
00896   else if( currency == "FRF" ) result = 6.55957;  // France
00897   else if( currency == "GRD" ) result = 340.75;   // Greece
00898   else if( currency == "IEP" ) result = 0.787564; // Ireland
00899   else if( currency == "ITL" ) result = 1936.27;  // Italy
00900   else if( currency == "LUX" ) result = 40.3399;  // Luxemburg
00901   else if( currency == "NLG" ) result = 2.20371;  // Nederland
00902   else if( currency == "PTE" ) result = 200.482;  // Portugal
00903   else
00904     return Value::errorVALUE();
00905 
00906   return Value (result);
00907 }
KDE Home | KDE Accessibility Home | Description of Access Keys