sheets/functions/financial.cpp

0001 /* This file is part of the KDE project
0002    Copyright (C) 2007 Sascha Pfau <MrPeacock@gmail.com>
0003    Copyright (C) 2002,2006 Ariya Hidayat <ariya@kde.org>
0004    Copyright (C) 2006 Stefan Nikolaus <stefan.nikolaus@kdemail.net>
0005    Copyright (C) 2005 Tomas Mecir <mecirt@gmail.com>
0006    Copyright (C) 2002 Norbert Andres <nandres@web.de>
0007    Copyright (C) 1999-2000 Laurent Montel <montel@kde.org>
0008    Copyright (C) 1999-2000 Laurent Montel <montel@kde.org>
0009    Copyright (C) 1999-2000 Jonathan Singer <jsinger@genome.wi.mit.edu>
0010    Copyright (C) 1998-1999 Torben Weis <weis@kde.org>
0011
0012    This library is free software; you can redistribute it and/or
0013    modify it under the terms of the GNU Library General Public
0014    License as published by the Free Software Foundation; only
0015    version 2 of the License.
0016
0017    This library is distributed in the hope that it will be useful,
0018    but WITHOUT ANY WARRANTY; without even the implied warranty of
0019    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
0020    Library General Public License for more details.
0021
0022    You should have received a copy of the GNU Library General Public License
0023    along with this library; see the file COPYING.LIB.  If not, write to
0024    the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
0025    Boston, MA 02110-1301, USA.
0026 */
0027
0028 // built-in financial functions
0029
0030 #include "FinancialModule.h"
0031
0032 #include "CalculationSettings.h"
0033 #include "Function.h"
0034 #include "FunctionModuleRegistry.h"
0035 #include "functions/helper.h"
0036 #include "ValueCalc.h"
0037 #include "ValueConverter.h"
0038
0039 #include <kcalendarsystem.h>
0040
0041 #include <math.h>
0042
0043 using namespace Calligra::Sheets;
0044
0045 // prototypes (sorted)
0046 Value func_accrint(valVector args, ValueCalc *calc, FuncExtra *);
0047 Value func_accrintm(valVector args, ValueCalc *calc, FuncExtra *);
0048 Value func_amordegrc(valVector args, ValueCalc *calc, FuncExtra *);
0049 Value func_amorlinc(valVector args, ValueCalc *calc, FuncExtra *);
0050 Value func_compound(valVector args, ValueCalc *calc, FuncExtra *);
0051 Value func_continuous(valVector args, ValueCalc *calc, FuncExtra *);
0052 Value func_coupdaybs (valVector args, ValueCalc *calc, FuncExtra *);
0053 Value func_coupdays (valVector args, ValueCalc *calc, FuncExtra *);
0054 Value func_coupdaysnc (valVector args, ValueCalc *calc, FuncExtra *);
0055 Value func_coupncd (valVector args, ValueCalc *calc, FuncExtra *);
0056 Value func_coupnum(valVector args, ValueCalc *calc, FuncExtra *);
0057 Value func_couppcd (valVector args, ValueCalc *calc, FuncExtra *);
0058 Value func_cumipmt(valVector args, ValueCalc *calc, FuncExtra *);
0059 Value func_cumprinc(valVector args, ValueCalc *calc, FuncExtra *);
0060 Value func_db(valVector args, ValueCalc *calc, FuncExtra *);
0061 Value func_ddb(valVector args, ValueCalc *calc, FuncExtra *);
0062 Value func_disc(valVector args, ValueCalc *calc, FuncExtra *);
0063 Value func_dollarde(valVector args, ValueCalc *calc, FuncExtra *);
0064 Value func_dollarfr(valVector args, ValueCalc *calc, FuncExtra *);
0065 Value func_duration(valVector args, ValueCalc *calc, FuncExtra *);
0066 Value func_duration_add(valVector args, ValueCalc *calc, FuncExtra *);
0067 Value func_effective(valVector args, ValueCalc *calc, FuncExtra *);
0068 Value func_euro(valVector args, ValueCalc *calc, FuncExtra *);
0069 Value func_euroconvert(valVector args, ValueCalc *calc, FuncExtra *);
0070 Value func_fv(valVector args, ValueCalc *calc, FuncExtra *);
0071 Value func_fvschedule(valVector args, ValueCalc *calc, FuncExtra *);
0072 Value func_fv_annuity(valVector args, ValueCalc *calc, FuncExtra *);
0073 Value func_intrate(valVector args, ValueCalc *calc, FuncExtra *);
0074 Value func_ipmt(valVector args, ValueCalc *calc, FuncExtra *);
0075 Value func_irr (valVector args, ValueCalc *calc, FuncExtra *);
0076 Value func_ispmt(valVector args, ValueCalc *calc, FuncExtra *);
0077 Value func_level_coupon(valVector args, ValueCalc *calc, FuncExtra *);
0078 Value func_mduration(valVector args, ValueCalc *calc, FuncExtra *);
0079 Value func_mirr(valVector args, ValueCalc *calc, FuncExtra *);
0080 Value func_nominal(valVector args, ValueCalc *calc, FuncExtra *);
0081 Value func_nper(valVector args, ValueCalc *calc, FuncExtra *);
0082 Value func_npv(valVector args, ValueCalc *calc, FuncExtra *);
0083 // Value func_oddfprice (valVector args, ValueCalc *calc, FuncExtra *);
0084 // Value func_oddfyield (valVector args, ValueCalc *calc, FuncExtra *);
0085 Value func_oddlprice(valVector args, ValueCalc *calc, FuncExtra *);
0086 Value func_oddlyield(valVector args, ValueCalc *calc, FuncExtra *);
0087 Value func_pmt(valVector args, ValueCalc *calc, FuncExtra *);
0088 Value func_ppmt(valVector args, ValueCalc *calc, FuncExtra *);
0089 // Value func_price (valVector args, ValueCalc *calc, FuncExtra *);
0090 // Value func_pricedisc (valVector args, ValueCalc *calc, FuncExtra *);
0091 Value func_pricemat(valVector args, ValueCalc *calc, FuncExtra *);
0092 Value func_pv(valVector args, ValueCalc *calc, FuncExtra *);
0093 Value func_pv_annuity(valVector args, ValueCalc *calc, FuncExtra *);
0094 Value func_rate(valVector args, ValueCalc *calc, FuncExtra *);
0095 Value func_received(valVector args, ValueCalc *calc, FuncExtra *);
0096 Value func_rri(valVector args, ValueCalc *calc, FuncExtra *);
0097 Value func_sln(valVector args, ValueCalc *calc, FuncExtra *);
0098 Value func_syd(valVector args, ValueCalc *calc, FuncExtra *);
0099 Value func_tbilleq(valVector args, ValueCalc *calc, FuncExtra *);
0100 Value func_tbillprice(valVector args, ValueCalc *calc, FuncExtra *);
0101 Value func_tbillyield(valVector args, ValueCalc *calc, FuncExtra *);
0102 Value func_vdb(valVector args, ValueCalc *calc, FuncExtra *);
0103 Value func_xirr(valVector args, ValueCalc *calc, FuncExtra *);
0104 Value func_xnpv(valVector args, ValueCalc *calc, FuncExtra *);
0105 // Value func_yield (valVector args, ValueCalc *calc, FuncExtra *);
0106 Value func_yielddisc(valVector args, ValueCalc *calc, FuncExtra *);
0107 Value func_yieldmat(valVector args, ValueCalc *calc, FuncExtra *);
0108 Value func_zero_coupon(valVector args, ValueCalc *calc, FuncExtra *);
0109
0110
0111 CALLIGRA_SHEETS_EXPORT_FUNCTION_MODULE("kspreadfinancialmodule.json", FinancialModule)
0112
0113 FinancialModule::FinancialModule(QObject* parent, const QVariantList&)
0114         : FunctionModule(parent)
0115 {
0116     Function *f;
0117
0118     f = new Function("ACCRINT", func_accrint);
0119     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETACCRINT");
0120     f->setParamCount(6, 7);
0121     add(f);
0122     f = new Function("ACCRINTM", func_accrintm);
0123     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETACCRINTM");
0124     f->setParamCount(3, 5);
0125     add(f);
0126     f = new Function("AMORDEGRC", func_amordegrc);
0127     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETAMORDEGRC");
0128     f->setParamCount(6, 7);
0129     add(f);
0130     f = new Function("AMORLINC", func_amorlinc);
0131     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETAMORLINC");
0132     f->setParamCount(6, 7);
0133     add(f);
0134     f = new Function("COMPOUND", func_compound);
0135     f->setParamCount(4);
0136     add(f);
0137     f = new Function("CONTINUOUS", func_continuous);
0138     f->setParamCount(3);
0139     add(f);
0140     f = new Function ("COUPDAYBS", func_coupdaybs);
0141     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPDAYBS");
0142     f->setParamCount (3, 4);
0143     add(f);
0144     f = new Function ("COUPDAYS", func_coupdays);
0145     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPDAYS");
0146     f->setParamCount (3, 4);
0147     add(f);
0148     f = new Function ("COUPDAYSNC", func_coupdaysnc);
0149     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPDAYSNC");
0150     f->setParamCount (3, 4);
0151     add(f);
0152     f = new Function ("COUPNCD", func_coupncd);
0153     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPNCD");
0154     f->setParamCount (3, 4);
0155     add(f);
0156     f = new Function("COUPNUM", func_coupnum);
0157     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPNUM");
0158     f->setParamCount(3, 5);
0159     add(f);
0160     f = new Function ("COUPPCD", func_couppcd);
0161     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPPCD");
0162     f->setParamCount (3, 4);
0163     add(f);
0164     f = new Function("CUMIPMT", func_cumipmt);
0165     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCUMIPMT");
0166     f->setParamCount(6);
0167     add(f);
0168     f = new Function("CUMPRINC", func_cumprinc);
0169     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCUMPRINC");
0170     f->setParamCount(6);
0171     add(f);
0172     f = new Function("DB", func_db);
0173     f->setParamCount(4, 5);
0174     add(f);
0175     f = new Function("DDB", func_ddb);
0176     f->setParamCount(4, 5);
0177     add(f);
0178     f = new Function("DISC", func_disc);
0179     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETDISC");
0180     f->setParamCount(4, 5);
0181     add(f);
0182     f = new Function("DOLLARDE", func_dollarde);
0183     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETDOLLARDE");
0184     f->setParamCount(2);
0185     add(f);
0186     f = new Function("DOLLARFR", func_dollarfr);
0187     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETDOLLARFR");
0188     f->setParamCount(2);
0189     add(f);
0190     f = new Function("DURATION", func_duration);
0191     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETDURATION");
0192     f->setParamCount(3);
0193     add(f);
0194     f = new Function("DURATION_ADD", func_duration_add);
0195     f->setParamCount(5, 6);
0196     add(f);
0197     f = new Function("EFFECT", func_effective);
0198     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETEFFECT");
0199     f->setParamCount(2);
0200     add(f);
0201     f = new Function("EFFECTIVE", func_effective);
0202     f->setParamCount(2);
0203     add(f);
0204     f = new Function("EURO", func_euro);   // KSpread-specific, Gnumeric-compatible
0205     f->setParamCount(1);
0206     add(f);
0207     f = new Function("EUROCONVERT", func_euroconvert);
0208     f->setParamCount(3);
0209     add(f);
0210     f = new Function("FV", func_fv);
0211     f->setParamCount(3, 5);
0212     add(f);
0213     f = new Function("FVSCHEDULE", func_fvschedule);
0214     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETFVSCHEDULE");
0215     f->setParamCount(2);
0216     f->setAcceptArray();
0217     add(f);
0218     f = new Function("FV_ANNUITY", func_fv_annuity);
0219     f->setParamCount(3);
0220     add(f);
0221     f = new Function("INTRATE", func_intrate);
0222     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETINTRATE");
0223     f->setParamCount(4, 5);
0224     add(f);
0225     f = new Function("IPMT", func_ipmt);
0226     f->setParamCount(4, 6);
0227     add(f);
0228     f = new Function ("IRR", func_irr);
0229     f->setParamCount (1, 2);
0230     f->setAcceptArray();
0231   add(f);
0232     f = new Function("ISPMT", func_ispmt);
0233     f->setParamCount(4);
0234     add(f);
0235     f = new Function("LEVEL_COUPON", func_level_coupon);
0236     f->setParamCount(5);
0237     add(f);
0238     f = new Function("MDURATION", func_mduration);
0239     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETMDURATION");
0240     f->setParamCount(5, 6);
0241     add(f);
0242     f = new Function("MIRR", func_mirr);
0243     f->setParamCount(3);
0244     f->setAcceptArray();
0245     add(f);
0246     f = new Function("NOMINAL", func_nominal);
0247     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETNOMINAL");
0248     f->setParamCount(2);
0249     add(f);
0250     f = new Function("NPER", func_nper);
0251     f->setParamCount(3, 5);
0252     add(f);
0253     f = new Function("NPV", func_npv);
0254     f->setParamCount(2, -1);
0255     f->setAcceptArray();
0256     add(f);
0257 //   f = new Function ("ODDFPRICE", func_oddfprice);
0258 //   f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETODDFPRICE");
0259 //   f->setParamCount (8, 9);
0260 //   add(f);
0261 //   f = new Function ("ODDFYIELD", func_oddfyield);
0262 //   f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETODDFYIELD");
0263 //   f->setParamCount (8, 9);
0264 //   add(f);
0265     f = new Function("ODDLPRICE", func_oddlprice);
0266     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETODDLPRICE");
0267     f->setParamCount(7, 8);
0268     add(f);
0269     f = new Function("ODDLYIELD", func_oddlyield);
0270     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETODDLYIELD");
0271     f->setParamCount(7, 8);
0272     add(f);
0273     f = new Function("PDURATION", func_duration);
0274     f->setParamCount(3);
0275     add(f);
0276     f = new Function("PMT", func_pmt);
0277     f->setParamCount(3, 5);
0278     add(f);
0279     f = new Function("PPMT", func_ppmt);
0280     f->setParamCount(4, 6);
0281     add(f);
0282 //   f = new Function ("PRICE", func_price);
0283 //   f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETPRICE");
0284 //   f->setParamCount (6, 7);
0285 //   add(f);
0286 //   f = new Function ("PRICEDISC", func_pricedisc);
0287 //   f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETPRICEDISC");
0288 //   f->setParamCount (4, 5);
0289 //   add(f);
0290     f = new Function("PRICEMAT", func_pricemat);
0291     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETPRICEMAT");
0292     f->setParamCount(5, 6);
0293     add(f);
0294     f = new Function("PV", func_pv);
0295     f->setParamCount(3, 5);
0296     add(f);
0297     f = new Function("PV_ANNUITY", func_pv_annuity);
0298     f->setParamCount(3);
0299     add(f);
0300     f = new Function ("RATE", func_rate);
0301     f->setParamCount (3, 6);
0302     add(f);
0303     f = new Function("RECEIVED", func_received);
0304     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETRECEIVED");
0305     f->setParamCount(4, 5);
0306     add(f);
0307     f = new Function("RRI", func_rri);
0308     f->setParamCount(3);
0309     add(f);
0310     f = new Function("SLN", func_sln);
0311     f->setParamCount(3);
0312     add(f);
0313     f = new Function("SYD", func_syd);
0314     f->setParamCount(4);
0315     add(f);
0316     f = new Function("TBILLEQ", func_tbilleq);
0317     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETTBILLEQ");
0318     f->setParamCount(3);
0319     add(f);
0320     f = new Function("TBILLPRICE", func_tbillprice);
0321     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETTBILLPRICE");
0322     f->setParamCount(3);
0323     add(f);
0324     f = new Function("TBILLYIELD", func_tbillyield);
0325     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETTBILLYIELD");
0326     f->setParamCount(3);
0327     add(f);
0328     f = new Function("VDB", func_vdb);
0329     f->setParamCount(5, 7);
0330     add(f);
0331     f = new Function("XIRR", func_xirr);
0332     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETXIRR");
0333     f->setParamCount(2, 3);
0334     f->setAcceptArray();
0335     add(f);
0336     f = new Function("XNPV", func_xnpv);
0337     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETXNPV");
0338     f->setParamCount(3);
0339     f->setAcceptArray();
0340     add(f);
0341 //   f = new Function ("YIELD", func_yield);
0342 //   f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETYIELD");
0343 //   f->setParamCount (6, 7);
0344 //   add(f);
0345     f = new Function("YIELDDISC", func_yielddisc);
0346     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETYIELDDISC");
0347     f->setParamCount(4, 5);
0348     add(f);
0349     f = new Function("YIELDMAT", func_yieldmat);
0350     f->setAlternateName("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETYIELDMAT");
0351     f->setParamCount(5, 6);
0352     add(f);
0353     f = new Function("ZERO_COUPON", func_zero_coupon);
0354     f->setParamCount(3);
0355     add(f);
0356 }
0357
0358 QString FinancialModule::descriptionFileName() const
0359 {
0360     return QString("financial.xml");
0361 }
0362
0363
0364 ///////////////////////////////////////////////////////////////////////////
0365
0366 //
0367 // helper: awNpv
0368 //
0369 void awNpv(ValueCalc *c, Value &res, Value val, Value rate)
0370 {
0371     Value result = c->conv()->asFloat(res.element(0, 0));
0372     Value value = c->conv()->asFloat(val);
0373     Value i = c->conv()->asFloat(res.element(1, 0));
0374     res.setElement(0, 0, c->add(result, c->div(value, c->pow(c->add(Value(1.0), rate), i))));
0375     res.setElement(1, 0, c->add(i, Value(1.0)));     // increment counter
0376 }
0377
0378 //
0379 // helper: calc_fvifa
0380 //
0381 static Value calc_fvifa(ValueCalc * /*calc*/, Value rate,
0382                         Value nper)
0383 {
0384     Value res;
0385
0386     if (rate.isZero())
0387         return (nper);
0388     else
0389         return Value(pow1pm1(rate.asFloat(), nper.asFloat()) / rate.asFloat());
0390 }
0391
0392
0393 //
0394 // helper: getPay
0395 //
0396 static Value getPay(ValueCalc *calc, Value rate,
0397                     Value nper, Value pv, Value fv, Value type)
0398 {
0399     Value pvif, fvifa;
0400
0401     // TODO This is for PMT. Check constraints of the other function using this
0402     //if (calc->isZero (rate)) return Value::errorVALUE();
0403     if (calc->isZero(rate))
0404         return calc->mul(calc->div(calc->add(pv, fv), nper), Value(-1.0));
0405
0406     //pvif  = pow( 1 + rate, nper );
0407     //fvifa = ( pvif - 1 ) / rate;
0408     pvif = Value(pow1p(rate.asFloat(), nper.asFloat()));
0409 //   fvifa = calc->div (calc->sub (pvif, 1), rate);
0410     fvifa = calc_fvifa(calc, rate, nper);
0411
0412     // ( -pv * pvif - fv ) / ( ( 1.0 + rate * type ) * fvifa );
0413     Value val1 = calc->sub(calc->mul(calc->mul(Value(-1), pv), pvif), fv);
0414     Value val2 = calc->mul(calc->add(Value(1.0), calc->mul(rate, type)),
0415                            fvifa);
0416
0417
0418     debugSheets << "(1.0 + " << rate << "*" << type << ")*" << fvifa << ") =" << val2;
0419     return calc->div(val1, val2);
0420 }
0421
0422 #if 0 // Not used?
0423 //
0424 // helper: getPrinc
0425 //
0426 static Value getPrinc(ValueCalc *calc, Value start,
0427                       Value pay, Value rate, Value period)
0428 {
0429     // val1 = pow( 1 + rate, period )
0430     Value val1 = calc->pow(calc->add(rate, 1), period);
0431     // val2 = start * val1
0432     Value val2 = calc->mul(start, val1);
0433     // val3 = pay * ( ( val1 - 1 ) / rate )
0434     Value val3 = calc->mul(pay, calc->div(calc->sub(val1, Value(1)), rate));
0435     // result = val2 + val3
0436     return calc->add(val2, val3);
0437 }
0438 #endif
0439 //
0440 // helper: eurofactor
0441 //
0442 static double helper_eurofactor(const QString& currency)
0443 {
0444     QString cur = currency.toUpper();
0445     double result = -1;
0446
0447     if (cur == "ATS") result = 13.7603;        // Austria
0448     else if (cur == "BEF") result = 40.3399;   // Belgium
0449     else if (cur == "CYP") result = 0.585274;  // Cyprus
0450     else if (cur == "DEM") result = 1.95583;   // Germany
0451     else if (cur == "EEK") result = 15.6466;   // Estonia
0452     else if (cur == "ESP") result = 166.386;   // Spain
0453     else if (cur == "EUR") result = 1.0;       // Euro
0454     else if (cur == "FIM") result = 5.94573;   // Finland
0455     else if (cur == "FRF") result = 6.55957;   // France
0456     else if (cur == "GRD") result = 340.75;    // Greece
0457     else if (cur == "IEP") result = 0.787564;  // Ireland
0458     else if (cur == "ITL") result = 1936.27;   // Italy
0459     else if (cur == "LTL") result = 3.45280;   // Lithuania
0460     else if (cur == "LUX") result = 40.3399;   // Luxembourg <-- This is the wrong code only here for compatibility with old versions
0461     else if (cur == "LUF") result = 40.3399;   // Luxembourg
0462     else if (cur == "LVL") result = 0.702804;  // Latvia
0463     else if (cur == "MTL") result = 0.4293;    // Malta
0464     else if (cur == "NLG") result = 2.20371;   // Netherlands
0465     else if (cur == "PTE") result = 200.482;   // Portugal
0466     else if (cur == "SIT") result = 239.64;    // Slovenia
0467     else if (cur == "SKK") result = 30.126;    // Slovakia
0468
0469     return result;
0470 }
0471
0472 //
0473 // helper for IPMT and CUMIPMT is calculation of IPMT
0474 //
0475 static Value helper_ipmt(ValueCalc* calc, Value rate, Value per, Value nper, Value pv, Value fv, Value type)
0476 {
0477 //     const Value payment = getPay (calc, rate, nper, pv, fv, type);
0478 //     const Value ineg = getPrinc (calc, pv, payment, rate, calc->sub (per, Value(1)));
0479 //     // -ineg * rate
0480 //     return calc->mul (calc->mul (ineg, Value(-1)), rate);
0481
0482     const Value pmt = getPay(calc, rate, nper, pv, fv, Value(0));  // Type 0
0483
0484     // pow1p (rate, per-1)
0485     const Value val1(pow1p(rate.asFloat(), calc->sub(per, Value(1)).asFloat()));
0486     // pow1pm1 (rate, per-1)
0487     const Value val2(pow1pm1(rate.asFloat(), calc->sub(per, Value(1)).asFloat()));
0488
0489     Value ipmt;
0490     // -1*(pv * pow1p(rate, per-1)*rate + pmt* pow1pm1(rate, per-1))
0491     ipmt = calc->mul(Value(-1), calc->add(calc->mul(calc->mul(pv, val1), rate), calc->mul(pmt, val2)));
0492
0493     return (type.isZero()) ? ipmt : calc->div(ipmt, calc->add(Value(1), rate));
0494 }
0495
0496
0497 //
0498 // helper vdbGetGDA
0499 //
0500 static double vdbGetGDA(const double cost, const double salvage, const double life,
0501                         const double period, const double depreciationFactor)
0502 {
0503     double res, rate, oldCost, newCost;
0504
0505     rate = depreciationFactor / life;
0506     if (rate >= 1.0) {
0507         rate = 1.0;
0508         if (period == 1.0)
0509             oldCost = cost;
0510         else
0511             oldCost = 0.0;
0512     } else
0513         oldCost = cost * pow(1.0 - rate, period - 1.0);
0514
0515     newCost = cost * pow(1.0 - rate, period);
0516
0517     if (newCost < salvage)
0518         res = oldCost - salvage;
0519     else
0520         res = oldCost - newCost;
0521
0522     if (res < 0.0)
0523         res = 0.0;
0524
0525     return (res);
0526 }
0527
0528
0529 //
0530 // helper vdbInterVDB
0531 //
0532 static double vdbInterVDB(const double cost, const double salvage,
0533                           const double life, const double life1,
0534                           const double period, const double depreciationFactor)
0535 {
0536     double res = 0.0;
0537
0538     double intEnd         = ceil(period);
0539     unsigned long loopEnd = (unsigned long) intEnd;
0540
0541     double term, lia = 0;
0542     double balance = cost - salvage;
0543     bool nowLia = false;
0544     double gda;
0545     unsigned long i;
0546
0547     for (i = 1; i <= loopEnd; ++i) {
0548         if (!nowLia) {
0549             gda = vdbGetGDA(cost, salvage, life, (double) i, depreciationFactor);
0550             lia = balance / (life1 - (double)(i - 1));
0551
0552             if (lia > gda) {
0553                 term = lia;
0554                 nowLia = true;
0555             } else {
0556                 term = gda;
0557                 balance -= gda;
0558             }
0559         } else {
0560             term = lia;
0561         }
0562
0563         if (i == loopEnd)
0564             term *= (period + 1.0 - intEnd);
0565
0566         res += term;
0567     }
0568
0569     return (res);
0570 }
0571
0572
0573 //
0574 // helper: xirrResult
0575 //
0576 // args[0] = values
0577 // args[1] = dates
0578 //
0579 static double xirrResult(valVector& args, ValueCalc *calc, double& rate)
0580 {
0581     QDate date;
0582
0583     QDate date0 = calc->conv()->asDate(args[1].element(0)).asDate(calc->settings());
0584
0585     double r = rate + 1.0;
0586     double res = calc->conv()->asFloat(args[0].element(0)).asFloat();
0587
0588     for (int i = 1, count = args[0].count(); i < count; ++i) {
0589         date = calc->conv()->asDate(args[1].element(i)).asDate(calc->settings());
0590         double e_i = (date0.daysTo(date)) / 365.0;
0591         double val = calc->conv()->asFloat(args[0].element(i)).asFloat();
0592
0593         res += val / pow(r, e_i);
0594     }
0595
0596     return res;
0597 }
0598
0599
0600 //
0601 // helper: xirrResultDerive
0602 //
0603 // args[0] = values
0604 // args[1] = dates
0605 //
0606 static double xirrResultDerive(valVector& args, ValueCalc *calc, double& rate)
0607 {
0608     QDate date;
0609
0610     QDate date0 = calc->conv()->asDate(args[1].element(0)).asDate(calc->settings());
0611
0612     double r = rate + 1.0;
0613     double res = 0.0;
0614
0615     for (int i = 1, count = args[0].count(); i < count; ++i) {
0616         date = calc->conv()->asDate(args[1].element(i)).asDate(calc->settings());
0617         double e_i = (date0.daysTo(date)) / 365.0;
0618         double val = calc->conv()->asFloat(args[0].element(i)).asFloat();
0619
0620         res -= e_i * val / pow(r, e_i + 1.0);
0621     }
0622
0623     return res;
0624 }
0625
0626
0627 ///////////////////////////////////////////////////////////////////////////////////////////////
0628
0629
0630 //
0631 // Function: ACCRINT
0632 //
0633 Value func_accrint(valVector args, ValueCalc *calc, FuncExtra *)
0634 {
0635     QDate maturity = calc->conv()->asDate(args[0]).asDate(calc->settings());
0636     QDate settlement = calc->conv()->asDate(args[2]).asDate(calc->settings());
0637
0638     Value rate = args[3];
0639     Value par = args[4];
0640     int frequency = calc->conv()->asInteger(args[5]).asInteger();
0641
0642     int basis = 0;
0643     if (args.count() == 7)
0644         basis = calc->conv()->asInteger(args[6]).asInteger();
0645
0646     if (basis < 0 || basis > 4 || (calc->isZero(Value(frequency))) ||
0647             (12 % frequency != 0))
0648         return Value::errorVALUE();
0649
0650     if (maturity >= settlement) {
0651         debugSheets << "maturity >= settlement";
0652         return Value::errorVALUE();
0653     }
0654
0655     double d = daysBetweenDates(maturity, settlement, basis);
0656     double y = daysPerYear(maturity, basis);
0657
0658     if (d < 0 || y <= 0 || calc->lower(par, Value(0)) || calc->lower(rate, Value(0)) ||
0659             calc->isZero(rate))
0660         return Value::errorVALUE();
0661
0662     Value coeff = calc->div(calc->mul(par, rate), frequency);
0663     double n = d / y;
0664
0665     return calc->mul(coeff, n * frequency);
0666 }
0667
0668
0669 //
0670 // Function: ACCRINTM
0671 //
0672 Value func_accrintm(valVector args, ValueCalc *calc, FuncExtra *)
0673 {
0674     QDate issue = calc->conv()->asDate(args[0]).asDate(calc->settings());
0675     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
0676     Value rate = args[2];
0677
0678     Value par = Value(1000);
0679     int basis = 0;
0680     if (args.count() > 3)
0681         par = args[3];
0682     if (args.count() == 5)
0683         basis = calc->conv()->asInteger(args[4]).asInteger();
0684
0685     double d = daysBetweenDates(issue, maturity, basis);
0686     double y = daysPerYear(issue, basis);
0687
0688     if (d < 0 || y <= 0 || calc->isZero(par) || calc->isZero(rate) ||
0689             calc->lower(par, Value(0)) || calc->lower(rate, Value(0)) || basis < 0 || basis > 4)
0690         return Value::errorVALUE();
0691
0692     // par*date * d/y
0693     return calc->mul(calc->mul(par, rate), d / y);
0694 }
0695
0696
0697 //
0698 // Function: AMORDEGRC
0699 //
0700 // AMORDEGRC( Cost; purchaseDate; firstPeriodEndDate; salvage; period; basis)
0701 //
0702 Value func_amordegrc(valVector args, ValueCalc *calc, FuncExtra *)
0703 {
0704     double cost = calc->conv()->asFloat(args[0]).asFloat();
0705     QDate purchaseDate = calc->conv()->asDate(args[1]).asDate(calc->settings());
0706     QDate firstPeriodEndDate = calc->conv()->asDate(args[2]).asDate(calc->settings());
0707     double salvage = calc->conv()->asFloat(args[3]).asFloat();
0708     int period = calc->conv()->asInteger(args[4]).asInteger();
0709     double rate = calc->conv()->asFloat(args[5]).asFloat();
0710
0711     int basis = 0;
0712     if (args.count() > 6)
0713         basis = calc->conv()->asInteger(args[6]).asInteger();
0714
0715     int n;
0716     double amorCoeff, nRate, rest, usePer;
0717
0718 #define ROUND(x,y) (floor ((x) + 0.5))
0719
0720     usePer = 1.0 / rate;
0721
0722     if (usePer < 3.0)
0723         amorCoeff = 1.0;
0724     else if (usePer < 5.0)
0725         amorCoeff = 1.5;
0726     else if (usePer <= 6.0)
0727         amorCoeff = 2.0;
0728     else
0729         amorCoeff = 2.5;
0730
0731     QDate date0 = calc->settings()->referenceDate(); // referenceDat
0732
0733     rate *= amorCoeff;
0734     nRate = ROUND(yearFrac(date0, purchaseDate, firstPeriodEndDate, basis) * rate * cost, 0);
0735     cost -= nRate;
0736     rest = cost - salvage;
0737
0738     for (n = 0 ; n < period ; ++n) {
0739         nRate = ROUND(rate * cost, 0);
0740         rest -= nRate;
0741
0742         if (rest < 0.0) {
0743             switch (period - n) {
0744             case 0:
0745             case 1:
0746                 return Value(ROUND(cost * 0.5, 0));
0747             default:
0748                 return Value(0.0);
0749             }
0750         }
0751
0752         cost -= nRate;
0753     }
0754
0755     return Value(nRate);
0756 #undef ROUND
0757 }
0758
0759 //
0760 // Function: AMORLINC
0761 //
0762 // AMORLINC( Cost; purchaseDate; firstPeriodEndDate; salvage; period; basis)
0763 //
0764 Value func_amorlinc(valVector args, ValueCalc *calc, FuncExtra *)
0765 {
0766     double cost = calc->conv()->asFloat(args[0]).asFloat();
0767     QDate purchaseDate = calc->conv()->asDate(args[1]).asDate(calc->settings());
0768     QDate firstPeriodEndDate = calc->conv()->asDate(args[2]).asDate(calc->settings());
0769     double salvage = calc->conv()->asFloat(args[3]).asFloat();
0770     int period = calc->conv()->asInteger(args[4]).asInteger();
0771     double rate = calc->conv()->asFloat(args[5]).asFloat();
0772
0773     int basis = 0;
0774     if (args.count() > 6)
0775         basis = calc->conv()->asInteger(args[6]).asInteger();
0776
0777     QDate date0 = calc->settings()->referenceDate(); // referenceDate
0778
0779     double oneRate = cost * rate;
0780     double costDelta = cost - salvage;
0781     double nullRate = yearFrac(date0, purchaseDate, firstPeriodEndDate, basis) * rate * cost;
0782     int numOfFullPeriods = (cost - salvage - nullRate) / oneRate;
0783
0784     double res ;
0785
0786     if (period == 0)
0787         res = nullRate;
0788     else if (period <= numOfFullPeriods)
0789         res = oneRate;
0790     else if (period == numOfFullPeriods + 1)
0791         res = costDelta - oneRate * numOfFullPeriods - nullRate;
0792     else
0793         res = 0.0;
0794
0795     return Value(res);
0796 #undef ROUND
0797 }
0798
0799 //
0800 // Function: compound
0801 //
0802 // Returns value after compounded interest, given principal, rate, periods
0803 // per year and year
0804 //
0805 Value func_compound(valVector args, ValueCalc *calc, FuncExtra *)
0806 {
0807     Value principal = args[0];
0808     Value interest = args[1];
0809     Value periods = args[2];
0810     Value years = args[3];
0811
0812     // principal * pow(1+ (interest / periods), periods*years);
0813     Value base = calc->add(calc->div(interest, periods), 1);
0814     return calc->mul(principal, calc->pow(base, calc->mul(periods, years)));
0815 }
0816
0817
0818 //
0819 // Function: continuous
0820 //
0821 // Returns value after continuous compounding of interest, given principal,
0822 // rate and years
0823 //
0824 Value func_continuous(valVector args, ValueCalc *calc, FuncExtra *)
0825 {
0826     // If you still don't understand this, let me know!  ;-)  jsinger@leeta.net
0827     Value principal = args[0];
0828     Value interest = args[1];
0829     Value years = args[2];
0830
0831     // principal * exp(interest * years)
0832     return calc->mul(principal, calc->exp(calc->mul(interest, years)));
0833 }
0834
0835
0836 enum CoupBasis {
0837     BASIS_MSRB_30_360     = 0,
0838     BASIS_ACT_ACT         = 1,
0839     BASIS_ACT_360         = 2,
0840     BASIS_ACT_365         = 3,
0841     BASIS_30E_360         = 4,
0842     BASIS_30Ep_360        = 5
0843 };
0844
0845 struct CoupSettings {
0846     int frequency; // times-per-year
0847     CoupBasis basis;
0848     bool eom; // not sure what this is...
0849 };
0850
0851 static Value coup_checkparams(valVector args, ValueCalc *calc, QDate& settlement, QDate& maturity, CoupSettings& conf)
0852 {
0853     settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
0854     maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
0855     conf.frequency = calc->conv()->asInteger(args[2]).asInteger();
0856
0857     conf.basis = BASIS_MSRB_30_360;
0858     conf.eom = true;
0859
0860     if (args.count() > 3)
0861         conf.basis = static_cast<CoupBasis>(calc->conv()->asInteger(args[3]).asInteger());
0862     if (args.count() > 4)
0863         conf.eom = calc->conv()->asBoolean(args[4]).asBoolean();
0864
0865     if (conf.basis < 0 || conf.basis > 5 || (conf.frequency == 0) || (12 % conf.frequency != 0)
0866             || settlement.daysTo(maturity) <= 0)
0867         return Value::errorVALUE();
0868
0869     return Value();
0870 }
0871
0872
0873 static QDate coup_cd(const QDate& settlement, const QDate& maturity, int freq, bool eom, bool next)
0874 {
0875     bool is_eom_special = eom && maturity.day() == maturity.daysInMonth();
0876
0877     int months = 12 / freq;
0878     int periods = maturity.year() - settlement.year();
0879     if (periods > 0)
0880         periods = (periods - 1) * freq;
0881
0882     QDate result;
0883     do {
0884         ++periods;
0885         result = maturity.addMonths(-periods * months);
0886         if (is_eom_special) {
0887             result.setDate(result.year(), result.month(), result.daysInMonth());
0888         }
0889     } while (settlement < result);
0890
0891     if (next) {
0892         periods--;
0893         result = maturity.addMonths(-periods * months);
0894         if (is_eom_special) {
0895             result.setDate(result.year(), result.month(), result.daysInMonth());
0896         }
0897     }
0898
0899     return result;
0900 }
0901
0902 static int daysBetween_30E_360(const QDate& from, const QDate& to)
0903 {
0904     int y1 = from.year();
0905     int m1 = from.month();
0906     int d1 = from.day();
0907     int y2 = to.year();
0908     int m2 = to.month();
0909     int d2 = to.day();
0910
0911     if (d1 == 31)
0912             d1 = 30;
0913     if (d2 == 31)
0914             d2 = 30;
0915
0916     return (y2 - y1) * 360 + (m2 - m1) * 30 + (d2 - d1);
0917 }
0918
0919 static int daysBetween_30Ep_360(const QDate& from, const QDate& to)
0920 {
0921     int y1 = from.year();
0922     int m1 = from.month();
0923     int d1 = from.day();
0924     int y2 = to.year();
0925     int m2 = to.month();
0926     int d2 = to.day();
0927
0928     if (d1 == 31)
0929             d1 = 30;
0930     if (d2 == 31) {
0931             d2 = 1;
0932             ++m2;
0933             /* No need to check for m2 == 13 since 12*30 == 360 */
0934     }
0935
0936     return (y2 - y1) * 360 + (m2 - m1) * 30 + (d2 - d1);
0937 }
0938
0939 static int daysBetween_MSRB_30_360(const QDate& from, const QDate& to)
0940 {
0941     int y1 = from.year();
0942     int m1 = from.month();
0943     int d1 = from.day();
0944     int y2 = to.year();
0945     int m2 = to.month();
0946     int d2 = to.day();
0947
0948     if (m1 == 2 && d1 == from.daysInMonth())
0949             d1 = 30;
0950     if (m2 == 2 && d2 == to.daysInMonth())
0951             d2 = 30;
0952     if (d2 == 31 && d1 >= 30)
0953             d2 = 30;
0954     if (d1 == 31)
0955             d1 = 30;
0956
0957     return (y2 - y1) * 360 + (m2 - m1) * 30 + (d2 - d1);
0958 }
0959
0960 static int daysBetweenBasis(const QDate& from, const QDate& to, CoupBasis basis)
0961 {
0962     const int sign = from < to ? 1 : -1;
0963     const QDate f = qMin(from, to);
0964     const QDate t = qMax(from, to);
0965
0966     switch (basis) {
0967     case BASIS_ACT_ACT:
0968     case BASIS_ACT_360:
0969     case BASIS_ACT_365:
0970         return sign * f.daysTo(t);
0971     case BASIS_30E_360:
0972         return sign * daysBetween_30E_360(f, t);
0973     case BASIS_30Ep_360:
0974         return sign * daysBetween_30Ep_360(f, t);
0975     case BASIS_MSRB_30_360:
0976     default:
0977         return sign * daysBetween_MSRB_30_360(f, t);
0978     }
0979 }
0980
0981
0982 //
0983 // Function: COUPDAYBS
0984 //
0985 Value func_coupdaybs(valVector args, ValueCalc *calc, FuncExtra *)
0986 {
0987     QDate settlement, maturity;
0988     CoupSettings conf;
0989
0990     Value res = coup_checkparams(args, calc, settlement, maturity, conf);
0991     if (res.isError()) {
0992         return res;
0993     }
0994
0995     QDate d = coup_cd(settlement, maturity, conf.frequency, conf.eom, false);
0996
0997     return Value(daysBetweenBasis(d, settlement, conf.basis));
0998 }
0999
1000
1001 static double coupdays(const QDate& settlement, const QDate& maturity, const CoupSettings& conf)
1002 {
1003     switch (conf.basis) {
1004     case BASIS_MSRB_30_360:
1005     case BASIS_ACT_360:
1006     case BASIS_30E_360:
1007     case BASIS_30Ep_360:
1008         return 360.0 / conf.frequency;
1009     case BASIS_ACT_365:
1010         return 365.0 / conf.frequency;
1011     case BASIS_ACT_ACT:
1012     default:
1013             QDate next = coup_cd(settlement, maturity, conf.frequency, conf.eom, true);
1014             QDate prev = coup_cd(settlement, maturity, conf.frequency, conf.eom, false);
1015             return daysBetweenBasis(prev, next, BASIS_ACT_ACT);
1016     }
1017 }
1018
1019 //
1020 // Function: COUPDAYS
1021 //
1022 Value func_coupdays(valVector args, ValueCalc *calc, FuncExtra *)
1023 {
1024     QDate settlement, maturity;
1025     CoupSettings conf;
1026
1027     Value res = coup_checkparams(args, calc, settlement, maturity, conf);
1028     if (res.isError()) {
1029         return res;
1030     }
1031
1032
1033     return Value(coupdays(settlement, maturity, conf));
1034 }
1035
1036
1037 //
1038 // Function: COUPDAYSNC
1039 //
1040 Value func_coupdaysnc(valVector args, ValueCalc *calc, FuncExtra *)
1041 {
1042     QDate settlement, maturity;
1043     CoupSettings conf;
1044
1045     Value res = coup_checkparams(args, calc, settlement, maturity, conf);
1046     if (res.isError()) {
1047         return res;
1048     }
1049
1050     QDate d = coup_cd(settlement, maturity, conf.frequency, conf.eom, true);
1051
1052     return Value(daysBetweenBasis(settlement, d, conf.basis));
1053 }
1054
1055
1056 //
1057 // Function: COUPNCD
1058 //
1059 Value func_coupncd(valVector args, ValueCalc *calc, FuncExtra *)
1060 {
1061     QDate settlement, maturity;
1062     CoupSettings conf;
1063
1064     Value res = coup_checkparams(args, calc, settlement, maturity, conf);
1065     if (res.isError()) {
1066         return res;
1067     }
1068
1069     return Value(coup_cd(settlement, maturity, conf.frequency, conf.eom, true), calc->settings());
1070 }
1071
1072
1073 //
1074 // Function: COUPNUM - taken from GNUMERIC
1075 //
1076 //
1077 // COUPNUM ( settlement, maturity, freq, [ basis = 0 ], [ eom ] )
1078 //
1079 Value func_coupnum(valVector args, ValueCalc *calc, FuncExtra *)
1080 {
1081     // dates and integers only - don't need high-precision for this
1082     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
1083     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
1084     int   frequency = calc->conv()->asInteger(args[2]).asInteger();
1085
1086     // defaults
1087     int   basis = 0;
1088     bool  eom   = true;
1089
1090     if (args.count() > 3)
1091         basis = calc->conv()->asInteger(args[3]).asInteger();
1092     if (args.count() == 5)
1093         eom = calc->conv()->asBoolean(args[4]).asBoolean();
1094
1095     if (basis < 0 || basis > 5 || (frequency == 0) || (12 % frequency != 0)
1096             || settlement.daysTo(maturity) <= 0)
1097         return Value::errorVALUE();
1098
1099     double result;
1100     QDate cDate(maturity);
1101
1102     int months = maturity.month() - settlement.month()
1103                  + 12 * (maturity.year() - settlement.year());
1104
1105     cDate = calc->settings()->locale()->calendar()->addMonths(cDate, -months);
1106
1107     if (eom && maturity.daysInMonth() == maturity.day()) {
1108         while (cDate.daysInMonth() != cDate.day())
1109             cDate = cDate.addDays(1);
1110     }
1111
1112     if (settlement.day() >= cDate.day())
1113         --months;
1114
1115     result = (1 + months / (12 / frequency));
1116
1117     return Value(result);
1118 }
1119
1120
1121 //
1122 // Function: COUPPCD
1123 //
1124 Value func_couppcd(valVector args, ValueCalc *calc, FuncExtra *)
1125 {
1126     QDate settlement, maturity;
1127     CoupSettings conf;
1128
1129     Value res = coup_checkparams(args, calc, settlement, maturity, conf);
1130     if (res.isError()) {
1131         return res;
1132     }
1133
1134     return Value(coup_cd(settlement, maturity, conf.frequency, conf.eom, false), calc->settings());
1135 }
1136
1137
1138 //
1139 // Function: CUMIPMT
1140 //
1141 Value func_cumipmt(valVector args, ValueCalc *calc, FuncExtra *)
1142 {
1143     const Value rate = args[0];
1144     if (rate.asFloat() <= 0.0)
1145         return Value::errorVALUE();
1146     const Value nper = args[1];
1147     const int periods = nper.asInteger();
1148     if (periods <= 0)
1149         return Value::errorVALUE();
1150     const Value pv = args[2];
1151     if (pv.asFloat() <= 0.0)
1152         return Value::errorVALUE();
1153     const Value v1(calc->conv()->asInteger(args[3]));
1154     if (v1.isError())
1155         return Value::errorVALUE();
1156     const int start = v1.asInteger();
1157     if (start <= 0 || start > periods)
1158         return Value::errorVALUE();
1159     const Value v2(calc->conv()->asInteger(args[4]));
1160     if (v2.isError())
1161         return Value::errorVALUE();
1162     const int end = v2.asInteger();
1163     if (end < start || end > periods)
1164         return Value::errorVALUE();
1165     const Value type(calc->conv()->asInteger(args[5]));
1166     if (type.isError())
1167         return Value::errorVALUE();
1168
1169     Value result(0.0);
1170     for (int per = start; per <= end; ++per)
1171         result = calc->add(result, helper_ipmt(calc, rate, Value(per), nper, pv, Value(0.0), type));
1172
1173     return result;
1174 }
1175
1176
1177 //
1178 // Function: CUMPRINC
1179 //
1180 Value func_cumprinc(valVector args, ValueCalc *calc, FuncExtra *)
1181 {
1182     const Value rate = args[0];
1183     if (rate.asFloat() <= 0.0)
1184         return Value::errorVALUE();
1185     const Value nper = args[1];
1186     const int periods = nper.asInteger();
1187     if (periods <= 0)
1188         return Value::errorVALUE();
1189     const Value pv = args[2];
1190     if (pv.asFloat() <= 0.0)
1191         return Value::errorVALUE();
1192     const Value v1(calc->conv()->asInteger(args[3]));
1193     if (v1.isError())
1194         return Value::errorVALUE();
1195     const int start = v1.asInteger();
1196     if (start <= 0 || start > periods)
1197         return Value::errorVALUE();
1198     const Value v2(calc->conv()->asInteger(args[4]));
1199     if (v2.isError())
1200         return Value::errorVALUE();
1201     const int end = v2.asInteger();
1202     if (end <= 0 || end < start || end > periods)
1203         return Value::errorVALUE();
1204     const Value type(calc->conv()->asInteger(args[5]));
1205     if (type.isError())
1206         return Value::errorVALUE();
1207
1208     const Value pay = getPay(calc, rate, nper, pv, Value(0.0), type);
1209     const Value cumipmt = func_cumipmt(args, calc, 0);
1210
1211     return calc->sub(calc->mul(pay, Value(end - start + 1)), cumipmt);
1212 }
1213
1214
1215 //
1216 // Function: DB
1217 //
1218 // fixed-declining depreciation
1219 //
1220 Value func_db(valVector args, ValueCalc *calc, FuncExtra *)
1221 {
1222     // This function doesn't support extended datatypes, it simply
1223     // converts everything to double - because it does quite a bit
1224     // of computing, and, well, I'm lazy to convert it all (Tomas)
1225     double cost = numToDouble(calc->conv()->toFloat(args[0]));
1226     double salvage = numToDouble(calc->conv()->toFloat(args[1]));
1227     double life = numToDouble(calc->conv()->toFloat(args[2]));
1228     double period = numToDouble(calc->conv()->toFloat(args[3]));
1229     double month = 12;
1230     if (args.count() == 5)
1231         month = numToDouble(calc->conv()->toFloat(args[4]));
1232
1233     // sentinel check
1234     if (cost == 0 || life <= 0.0 || period == 0)
1235         return Value::errorNUM();
1236
1237     if (calc->lower(calc->div(Value(salvage), Value(cost)), Value(0)))
1238         return Value::errorNUM();
1239
1240     double rate = 1000 * (1 - pow((salvage / cost), (1 / life)));
1241     rate = floor(rate + 0.5)  / 1000;
1242
1243     double total = cost * rate * month / 12;
1244
1245     if (period == 1)
1246         return Value(total);
1247
1248     for (int i = 1; i < life; ++i)
1249         if (i == period - 1)
1250             return Value(rate *(cost - total));
1251         else total += rate * (cost - total);
1252
1253     return Value((cost - total) * rate *(12 - month) / 12);
1254 }
1255
1256
1257 //
1258 // Function: DDB
1259 //
1260 // depreciation per period
1261 //
1262 Value func_ddb(valVector args, ValueCalc *calc, FuncExtra *)
1263 {
1264     double cost = numToDouble(calc->conv()->toFloat(args[0]));
1265     double salvage = numToDouble(calc->conv()->toFloat(args[1]));
1266     double life = numToDouble(calc->conv()->toFloat(args[2]));
1267     double period = numToDouble(calc->conv()->toFloat(args[3]));
1268     double factor = 2;
1269     if (args.count() == 5)
1270         factor = numToDouble(calc->conv()->toFloat(args[4]));
1271
1272     if (cost < 0.0 || salvage < 0.0 || life <= 0.0 || period < 0.0 || factor < 0.0)
1273         return Value::errorVALUE();
1274
1275     double result = 0.0;
1276
1277     if (factor >= life)
1278         // special case: amazingly gigantic depreciating rate
1279         result = (period > 1) ? 0 : (cost < salvage) ? 0 : cost - salvage;
1280     else {
1281         // depreciation is the value between two periods
1282         double invrate = (life - factor) / life;
1283         double current = (period == 1) ? invrate : pow(invrate, period);
1284         double previous = current / invrate;
1285         current *= cost;
1286         previous *= cost;
1287         result = previous - current;
1288
1289         // should not be more than the salvage
1290         if (current < salvage)
1291             result = previous - salvage;
1292     }
1293
1294     // can't be negative
1295     if (result < 0.0)
1296         result = 0.0;
1297
1298     return Value(result);
1299 }
1300
1301
1302 //
1303 // Function: DISC
1304 //
1305 Value func_disc(valVector args, ValueCalc *calc, FuncExtra *)
1306 {
1307     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
1308     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
1309
1310 // TODO sascha fix error value checking
1311 //   // check dates
1312 //   if ( settlement > maturity || )
1313 //     return Value(false);
1314
1315     Value par = args[2];
1316     Value redemp = args[3];
1317
1318     // check parameters
1319     if (settlement > maturity || redemp.asFloat() <= 0.0 || par.asFloat() <= 0.0)
1320         return Value(false);
1321
1322     int basis = 0;
1323     if (args.count() == 5)
1324         basis = calc->conv()->asInteger(args[4]).asInteger();
1325
1326     /*  double y = daysPerYear (settlement, basis);
1327       double d = daysBetweenDates (settlement, maturity, basis);
1328
1329       if ( y <= 0 || d <= 0 || basis < 0 || basis > 4 || calc->isZero (redemp) )
1330         return Value(false);*/
1331
1332     QDate date0 = calc->settings()->referenceDate(); // referenceDate
1333
1334     // res=(1-(price/redemption)/yearfrac)
1335     return Value((1.0 - par.asFloat() / redemp.asFloat()) / yearFrac(date0, settlement, maturity, basis));
1336 }
1337
1338
1339 //
1340 // Function: DOLLARDE
1341 //
1342 Value func_dollarde(valVector args, ValueCalc *calc, FuncExtra *)
1343 {
1344     double dollarFrac = args[0].asFloat();
1345     double frac = calc->conv()->asInteger(args[1]).asInteger();
1346
1347     if (frac <= 0)
1348         return Value::errorVALUE();
1349
1350     double fl;
1351     double res = modf(dollarFrac, &fl);
1352
1353     res /= frac;
1354     res *= pow(10.0, ceil(log10(frac)));
1355     res += fl;
1356
1357     return Value(res);
1358 }
1359
1360
1361 //
1362 // Function: DOLLARFR
1363 //
1364 Value func_dollarfr(valVector args, ValueCalc *calc, FuncExtra *)
1365 {
1366     double dollarFrac = args[0].asFloat();
1367     double frac = calc->conv()->asInteger(args[1]).asInteger();
1368
1369     if (frac <= 0)
1370         return Value::errorVALUE();
1371
1372     double fl;
1373     double res = modf(dollarFrac, &fl);
1374
1375     res *= frac;
1376     res *= pow(10.0, -ceil(log10(frac)));
1377     res += fl;
1378
1379     return Value(res);
1380 }
1381
1382
1383 //
1384 // Function: DURATION
1385 //
1386 //
1387 // duration( rate, pv, fv )
1388 //
1389 Value func_duration(valVector args, ValueCalc *calc, FuncExtra *)
1390 {
1391     Value rate = args[0];
1392     Value pv   = args[1];
1393     Value fv   = args[2];
1394
1395     if (!calc->greater(rate, Value(0.0)))
1396         return Value::errorVALUE();
1397     if (calc->isZero(fv) || calc->isZero(pv))
1398         return Value::errorDIV0();
1399
1400     if (calc->lower(calc->div(fv, pv), Value(0)))
1401         return Value::errorVALUE();
1402
1403     // log(fv / pv) / log(1.0 + rate)
1404     return calc->div(calc->ln(calc->div(fv, pv)),
1405                      calc->ln(calc->add(rate, Value(1.0))));
1406 }
1407
1408
1409 //
1410 // Function: DURATION
1411 //
1412 //
1413 // duration( settlement, maturity, coup, yield, freq, [basis = 0] )
1414 //
1415 Value func_duration_add(valVector args, ValueCalc *calc, FuncExtra *)
1416 {
1417     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
1418     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
1419
1420     double coup = numToDouble(calc->conv()->toFloat(args[2]));
1421     double yield = numToDouble(calc->conv()->toFloat(args[3]));
1422     int freq = calc->conv()->asInteger(args[4]).asInteger();
1423
1424     int basis = 0;
1425     if (args.count() > 5)
1426         basis = calc->conv()->asInteger(args[3]).asInteger();
1427
1428     // TODO add chk_freq
1429     if (coup < 0.0 || yield < 0.0)
1430         return Value::errorVALUE();
1431
1432     QDate date0 = calc->settings()->referenceDate(); // referenceDate
1433
1434     valVector param;
1435     param.append(args[0]);
1436     param.append(args[1]);
1437     param.append(args[4]);
1438     param.append(Value(basis));
1439
1440     int numOfCoups = Value(func_coupnum(param, calc, 0)).asInteger();
1441
1442     debugSheetsFormula << "DURATION";
1443     debugSheetsFormula << "numOfCoup =" << numOfCoups;
1444
1445
1446     return Value(duration(date0, settlement, maturity, coup, yield, freq, basis, numOfCoups));
1447 }
1448
1449
1450 //
1451 // Function: effective
1452 //
1453 Value func_effective(valVector args, ValueCalc *calc, FuncExtra *)
1454 {
1455     // Returns effective interest rate given nominal rate and periods per year
1456
1457     Value nominal = args[0];
1458     Value periods = args[1];
1459
1460     // base = 1 + (nominal / periods)
1461     // result = pow (base, periods) - 1
1462     Value base = calc->add(calc->div(nominal, periods), 1);
1463     return calc->sub(calc->pow(base, periods), 1);
1464 }
1465
1466
1467 //
1468 // Function: EURO
1469 //
1470 Value func_euro(valVector args, ValueCalc *calc, FuncExtra *)
1471 {
1472     QString currency = calc->conv()->asString(args[0]).asString();
1473     double result = helper_eurofactor(currency);
1474     if (result < 0)
1475         return Value::errorNUM();
1476
1477     return Value(result);
1478 }
1479
1480
1481 //
1482 // EUROCONVERT(number,source,target)
1483 //
1484 Value func_euroconvert(valVector args, ValueCalc *calc, FuncExtra *)
1485 {
1486     double number = numToDouble(calc->conv()->toFloat(args[0]));
1487     QString source = calc->conv()->asString(args[1]).asString();
1488     QString target = calc->conv()->asString(args[2]).asString();
1489
1490     double factor1 = helper_eurofactor(source);
1491     double factor2 = helper_eurofactor(target);
1492
1493     if (factor1 < 0)
1494         return Value::errorNUM();
1495     if (factor2 < 0)
1496         return Value::errorNUM();
1497
1498     double result = number * factor2 / factor1;
1499
1500     return Value(result);
1501 }
1502
1503
1504 //
1505 // Function: FV
1506 //
1507 // Returns future value, given current value, interest rate and time
1508 //
1509 Value func_fv(valVector args, ValueCalc *calc, FuncExtra *)
1510 {
1511     Value rate = args[0];
1512     Value nper = args[1];
1513     Value pmt  = args[2];
1514
1515     // defaults
1516     Value pv(0.0);
1517     int type = 0;
1518
1519     if (args.count() > 3)
1520         pv = Value(calc->conv()->asFloat(args[3]).asFloat());
1521
1522     if (args.count() == 5)
1523         type = calc->conv()->asInteger(args[4]).asInteger();
1524
1525     //TODO check payType
1526
1527     Value pvif = Value(pow1p(rate.asFloat(), nper.asFloat()));
1528     Value fvifa = calc_fvifa(calc, rate, nper);
1529
1530     Value res(calc->mul(Value(-1), calc->add(calc->mul(pv, pvif), calc->mul(pmt, calc->mul(calc->add(Value(1), calc->mul(rate, type)), fvifa)))));
1531
1532     return (res);
1533     // present * pow (1 + interest, periods)
1534 //   return calc->mul (present, calc->pow (calc->add (interest, 1), periods));
1535 }
1536
1537 //
1538 // Function: FVSCHEDULE
1539 //
1540 // Returns future value
1541 //
1542 Value func_fvschedule(valVector args, ValueCalc *calc, FuncExtra *)
1543 {
1544     Value pv       = args[0];
1545     Value schedule = args[1];
1546
1547     int n = schedule.count();
1548     int i;
1549
1550     Value v;
1551     Value res(pv);
1552
1553     for (i = 0; i < n; ++i) {
1554         v = args[1].element(i);
1555         res = Value(calc->mul(res, calc->add(Value(1), v)));
1556     }
1557
1558     return (res);
1559 }
1560
1561
1562 //
1563 // Function: FV_annuity
1564 //
1565 Value func_fv_annuity(valVector args, ValueCalc *calc, FuncExtra *)
1566 {
1567     /* Returns future value of an annuity or cash flow, given payment, interest
1568        rate and periods */
1569
1570     Value amount = args[0];
1571     Value interest = args[1];
1572     Value periods = args[2];
1573
1574     // pw = pow (1 + interest, periods)
1575     // result = amount * ((pw - 1) / interest)
1576     Value pw = calc->pow(calc->add(interest, 1), periods);
1577     return calc->mul(amount, calc->div(calc->sub(pw, 1), interest));
1578 }
1579
1580
1581 //
1582 // Function: INTRATE
1583 //
1584 Value func_intrate(valVector args, ValueCalc *calc, FuncExtra *)
1585 {
1586     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
1587     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
1588
1589     Value invest = args[2];
1590     Value redemption = args[3];
1591
1592     int basis = 0;
1593     if (args.count() == 5)
1594         basis = calc->conv()->asInteger(args[4]).asInteger();
1595
1596     double d = daysBetweenDates(settlement, maturity, basis);
1597     double y = daysPerYear(settlement, basis);
1598
1599     if (d <= 0 || y <= 0 || calc->isZero(invest) || basis < 0 || basis > 4)
1600         return Value::errorVALUE();
1601
1602     // (redemption - invest) / invest * (y / d)
1603     return calc->mul(calc->div(calc->sub(redemption, invest), invest), y / d);
1604 }
1605
1606
1607 //
1608 // Function: IPMT
1609 //
1610 Value func_ipmt(valVector args, ValueCalc *calc, FuncExtra *)
1611 {
1612     Value rate = args[0];
1613     Value per  = args[1];
1614     Value nper = args[2];
1615     Value pv   = args[3];
1616
1617     Value fv = Value(0.0);
1618     Value type = Value(0);
1619     if (args.count() > 4) fv = args[4];
1620     if (args.count() == 6) type = args[5];
1621
1622     return helper_ipmt(calc, rate, per, nper, pv, fv, type);
1623 }
1624
1625 static double irrResult(Value sec, ValueCalc *calc, double rate)
1626 {
1627     double res = 0;
1628     for (unsigned i = 0; i < sec.count(); ++i) {
1629         double val = calc->conv()->asFloat(sec.element(i)).asFloat();
1630         res += val / pow(1.0 + rate, double(i));
1631     }
1632     return res;
1633 }
1634
1635 static double irrResultDerive(Value sec, ValueCalc *calc, double rate)
1636 {
1637     double res = 0;
1638     for (unsigned i = 0; i < sec.count(); ++i) {
1639         double val = calc->conv()->asFloat(sec.element(i)).asFloat();
1640         res += -double(i) * val / pow(1.0 + rate, double(i + 1));
1641     }
1642     return res;
1643 }
1644
1645 //
1646 // Function: IRR
1647 //
1648 Value func_irr(valVector args, ValueCalc *calc, FuncExtra *)
1649 {
1650     static const double maxEpsilon = 1e-10;
1651     static const int maxIter = 50;
1652
1653     Value seq = args[0];
1654
1655     double rate = 0.1;
1656     if (args.count() > 1) rate = calc->conv()->asFloat(args[1]).asFloat();
1657
1658     bool contLoop;
1659     int i = 0;
1660     do {
1661         double newRate = rate - irrResult(seq, calc, rate) / irrResultDerive(seq, calc, rate);
1662         double rateEpsilon = fabs(newRate - rate);
1663         rate = newRate;
1664         contLoop = (rateEpsilon > maxEpsilon) && (fabs(rate) > maxEpsilon);
1665     } while (contLoop && (++i < maxIter));
1666
1667     return Value(rate);
1668 }
1669
1670 //
1671 // Function: ISPMT
1672 //
1673 Value func_ispmt(valVector args, ValueCalc *calc, FuncExtra *)
1674 {
1675     Value rate = args[0];
1676     Value per  = args[1];
1677     Value nper = args[2];
1678     Value pv   = args[3];
1679
1680     if (calc->lower(per, Value(1)) || calc->greater(per, nper))
1681         return Value::errorVALUE();
1682
1683     // d = -pv * rate
1684     Value d = calc->mul(calc->mul(pv, Value(-1)), rate);
1685
1686     // d - (d / nper * per)
1687     return calc->sub(d, calc->mul(calc->div(d, nper), per));
1688 }
1689
1690
1691 //
1692 // Function: MDURATION
1693 //
1694 //
1695 // duration( settlement, maturity, coup, yield, freq, [basis = 0] )
1696 //
1697 Value func_mduration(valVector args, ValueCalc *calc, FuncExtra *)
1698 {
1699     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
1700     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
1701
1702     double coup = numToDouble(calc->conv()->toFloat(args[2]));
1703     double yield = numToDouble(calc->conv()->toFloat(args[3]));
1704     int freq = calc->conv()->asInteger(args[4]).asInteger();
1705
1706     int basis = 0;
1707     if (args.count() > 5)
1708         basis = calc->conv()->asInteger(args[3]).asInteger();
1709
1710     // TODO add chk_freq
1711     if (coup < 0.0 || yield < 0.0)
1712         return Value::errorVALUE();
1713
1714     QDate date0 = calc->settings()->referenceDate(); // referenceDate
1715
1716     valVector param;
1717     param.append(args[0]);
1718     param.append(args[1]);
1719     param.append(args[4]);
1720     param.append(Value(basis));
1721
1722     int numOfCoups = Value(func_coupnum(param, calc, 0)).asInteger();
1723
1724     double res = duration(date0, settlement, maturity, coup, yield, freq, basis, numOfCoups);
1725     res /= 1.0 + (yield / double(freq));
1726
1727     return Value(res);
1728 }
1729
1730
1731 //
1732 // Function: MIRR
1733 //
1734 Value func_mirr(valVector args, ValueCalc *calc, FuncExtra *)
1735 {
1736     long double inv = calc->conv()->asFloat(args[1]).asFloat();
1737     long double reInvRate = calc->conv()->asFloat(args[2]).asFloat();
1738
1739     long double npv_pos, npv_neg;
1740     Value v;
1741     int n = args[0].count();
1742     int i;
1743
1744     for (i = 0, npv_pos = npv_neg = 0; i < n; ++i) {
1745         v = args[0].element(i);
1746         if (v.asFloat() >= 0)
1747             npv_pos += v.asFloat() / pow1p(reInvRate, i);
1748         else
1749             npv_neg += v.asFloat() / pow1p(inv, i);
1750     }
1751
1752     if (npv_neg == 0 || npv_pos == 0 || reInvRate <= -1.0l)
1753         return Value::errorVALUE();
1754
1755     long double res = std::pow((-npv_pos * pow1p(reInvRate, n)) / (npv_neg * (1 + reInvRate)), (1.0l / (n - 1))) - 1.0l;
1756
1757     return Value(res);
1758 }
1759
1760
1761 //
1762 // Function: level_coupon
1763 //
1764 Value func_level_coupon(valVector args, ValueCalc *calc, FuncExtra *)
1765 {
1766     // Returns effective interest rate given nominal rate and periods per year
1767     Value face = args[0];
1768     Value coupon_rate = args[1];
1769     Value coupon_year = args[2];
1770     Value years = args[3];
1771     Value market_rate = args[4];
1772
1773     Value coupon, interest, pw, pv_annuity;
1774     // coupon = coupon_rate * face / coupon_year
1775     // interest = market_rate / coupon_year
1776     // pw = pow(1 + interest, years * coupon_year)
1777     // pv_annuity = (1 - 1 / pw) / interest
1778     // result = coupon * pv_annuity + face / pw
1779     coupon = calc->mul(coupon_rate, calc->div(face, coupon_year));
1780     interest = calc->div(market_rate, coupon_year);
1781     pw = calc->pow(calc->add(interest, Value(1)), calc->mul(years, coupon_year));
1782     pv_annuity = calc->div(calc->sub(Value(1), calc->div(Value(1), pw)), interest);
1783     return calc->add(calc->mul(coupon, pv_annuity), calc->div(face, pw));
1784 }
1785
1786
1787 //
1788 // Function: nominal
1789 //
1790 Value func_nominal(valVector args, ValueCalc *calc, FuncExtra *)
1791 {
1792     Value effective = args[0];
1793     Value periods = args[1];
1794
1795     // sentinel checks
1796     if (calc->isZero(periods))
1797         return Value::errorDIV0();
1798     if (!calc->greater(periods, Value(0.0)))
1799         return Value::errorVALUE();
1800     if (calc->isZero(effective))
1801         return Value::errorVALUE();
1802     if (!calc->greater(effective, Value(0.0)))
1803         return Value::errorVALUE();
1804
1805     // pw = pow (effective + 1, 1 / periods)
1806     // result = periods * (pw - 1);
1807     Value pw;
1808     pw = calc->pow(calc->add(effective, Value(1)), calc->div(Value(1), periods));
1809     return calc->mul(periods, calc->sub(pw, Value(1)));
1810 }
1811
1812
1813 //
1814 // Function: NPER
1815 //
1816 Value func_nper(valVector args, ValueCalc *calc, FuncExtra *)
1817 {
1818     double rate = calc->conv()->asFloat(args[0]).asFloat();
1819     double pmt = calc->conv()->asFloat(args[1]).asFloat();
1820     double pv = calc->conv()->asFloat(args[2]).asFloat();
1821
1822     // defaults
1823     double fv = 0.0;
1824     double type = 0;
1825
1826     // opt. params
1827     if (args.count() > 3) fv = calc->conv()->asFloat(args[3]).asFloat();
1828     if (args.count() == 5) type = calc->conv()->asFloat(args[4]).asFloat();
1829
1830     // if rate is 0, then NPER solves this
1831     //   PV = -FV -( Payment*NPER )
1832     if (rate == 0.0)
1833         return Value(-(pv + fv) / pmt);
1834
1835     if (type > 0)
1836         return Value(log(-(rate*fv - pmt*(1.0 + rate)) / (rate*pv + pmt*(1.0 + rate))) / log(1.0 + rate));
1837     else
1838         return Value(log(-(rate*fv - pmt) / (rate*pv + pmt)) / log(1.0 + rate));
1839 }
1840
1841
1842 //
1843 // NPV
1844 //
1845 Value func_npv(valVector args, ValueCalc* calc, FuncExtra*)
1846 {
1847     Value result(Value::Array);
1848     result.setElement(0, 0, Value(0.0));     // actual result
1849     result.setElement(1, 0, Value(1.0));     // counter
1850     if (args.count() == 2) {
1851         Value vector = args[1]; // may be an array
1852         calc->arrayWalk(vector, result, awNpv, calc->conv()->asFloat(args[0]));
1853     } else {
1854         valVector vector = args.mid(1);
1855         calc->arrayWalk(vector, result, awNpv, calc->conv()->asFloat(args[0]));
1856     }
1857     return result.element(0, 0);
1858 }
1859
1860 static double date_ratio(const QDate& d1, const QDate& d2, const QDate& d3, const CoupSettings& conv)
1861 {
1862     QDate next = coup_cd(d1, d3, conv.frequency, conv.eom, true);
1863     QDate prev = coup_cd(d1, d3, conv.frequency, conv.eom, false);
1864
1865     if (next >= d2) {
1866         return daysBetweenBasis(d1, d2, conv.basis) /
1867                 coupdays(prev, next, conv);
1868     }
1869
1870     double res = daysBetweenBasis(d1, next, conv.basis) /
1871             coupdays(prev, next, conv);
1872
1873     while (true) {
1874         prev = next;
1875         next = next.addMonths(12 / conv.frequency);
1876         //if (!next.isValid())
1877             //
1878         if (next >= d2) {
1879             res += daysBetweenBasis(prev, d2, conv.basis) /
1880                         coupdays(prev, next, conv);
1881             return res;
1882         }
1883         res += 1;
1884     }
1885 }
1886
1887 //
1888 // ODDLPRICE
1889 //
1890 Value func_oddlprice(valVector args, ValueCalc *calc, FuncExtra *)
1891 {
1892     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
1893     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
1894     QDate last = calc->conv()->asDate(args[2]).asDate(calc->settings());
1895     double rate = calc->conv()->asFloat(args[3]).asFloat();
1896     double yield = calc->conv()->asFloat(args[4]).asFloat();
1897     double redemp = calc->conv()->asFloat(args[5]).asFloat();
1898     double freq = calc->conv()->asFloat(args[6]).asFloat();
1899
1900     // opt. basis
1901     int basis = 0;
1902     if (args.count() > 7)
1903         basis = calc->conv()->asInteger(args[7]).asInteger();
1904
1905     CoupSettings conv;
1906     conv.basis = static_cast<CoupBasis>(basis);
1907     conv.frequency = freq;
1908     conv.eom = true;
1909
1910 //   debugSheetsFormula<<"ODDLPRICE";
1911 //   debugSheetsFormula<<"settlement ="<<settlement<<" maturity="<<maturity<<" last="<<last<<" rate="<<rate<<" yield="<<yield<<" redemp="<<redemp<<" freq="<<freq<<" basis="<<basis;
1912
1913     // TODO check frequency
1914     if (yield <= 0.0 || rate <= 0.0 || maturity <= settlement || settlement <= last)
1915         return Value::errorVALUE();
1916
1917     QDate d = last;
1918     do {
1919         d = d.addMonths(12 / conv.frequency);
1920     } while (d.isValid() && d < maturity);
1921
1922     double x1 = date_ratio(last, settlement, d, conv);
1923     double x2 = date_ratio(last, maturity, d, conv);
1924     double x3 = date_ratio(settlement, maturity, d, conv);
1925
1926     return Value((redemp * conv.frequency +
1927               100 * rate * (x2 - x1 * (1 + yield * x3 / conv.frequency))) /
1928               (yield * x3 + conv.frequency));
1929 }
1930
1931
1932 //
1933 // ODDLYIELD
1934 //
1935 Value func_oddlyield(valVector args, ValueCalc *calc, FuncExtra *)
1936 {
1937     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
1938     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
1939     QDate last = calc->conv()->asDate(args[2]).asDate(calc->settings());
1940     double rate = calc->conv()->asFloat(args[3]).asFloat();
1941     double price = calc->conv()->asFloat(args[4]).asFloat();
1942     double redemp = calc->conv()->asFloat(args[5]).asFloat();
1943     double freq = calc->conv()->asFloat(args[6]).asFloat();
1944
1945     // opt. basis
1946     int basis = 0;
1947     if (args.count() > 7)
1948         basis = calc->conv()->asInteger(args[7]).asInteger();
1949
1950     CoupSettings conv;
1951     conv.basis = static_cast<CoupBasis>(basis);
1952     conv.frequency = freq;
1953     conv.eom = true;
1954
1955 //   debugSheetsFormula<<"ODDLYIELD";
1956 //   debugSheetsFormula<<"settlement ="<<settlement<<" maturity="<<maturity<<" last="<<last<<" rate="<<rate<<" price="<<price<<" redemp="<<redemp<<" freq="<<freq<<" basis="<<basis;
1957
1958     // TODO check frequency
1959     if (rate < 0.0 || price <= 0.0 || maturity <= settlement || settlement <= last)
1960         return Value::errorVALUE();
1961
1962
1963     QDate d = last;
1964     do {
1965         d = d.addMonths(12 / conv.frequency);
1966     } while (d.isValid() && d < maturity);
1967
1968     double x1 = date_ratio(last, settlement, d, conv);
1969     double x2 = date_ratio(last, maturity, d, conv);
1970     double x3 = date_ratio(settlement, maturity, d, conv);
1971
1972
1973     return Value((conv.frequency * (redemp - price) + 100 * rate * (x2 - x1)) /
1974             (x3 * price + 100 * rate * x1 * x3 / conv.frequency));
1975 }
1976
1977
1978 //
1979 // Function: PMT
1980 //
1981 Value func_pmt(valVector args, ValueCalc *calc, FuncExtra *)
1982 {
1983     Value rate = args[0];
1984     Value nper = args[1];
1985     Value pv   = args[2];
1986     Value fv = Value(0.0);
1987     Value type = Value(0);
1988     if (args.count() > 3) fv = args[3];
1989     if (args.count() == 5) type = args[4];
1990
1991     return getPay(calc, rate, nper, pv, fv, type);
1992 }
1993
1994
1995 //
1996 // Function: PPMT
1997 //
1998 // Uses IPMT.
1999 //
2000 Value func_ppmt(valVector args, ValueCalc *calc, FuncExtra *)
2001 {
2002     // Docs partly copied from OO.
2003     //
2004     // PPMT(Rate;Period;NPER;PV;FV;Type)
2005     //
2006     // Rate is the periodic interest rate.
2007     // Period is the amortizement period. P=1 for the first and P=NPER for the last period.
2008     // NPER is the total number of periods during which annuity is paid.
2009     // PV is the present value in the sequence of payments.
2010     // FV (optional) is the desired (future) value.
2011     // 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.
2012     //
2013
2014     Value rate = args[0];
2015     Value per  = args[1];
2016     Value nper = args[2];
2017     Value pv   = args[3];
2018
2019     // defaults
2020     Value fv = Value(0.0);
2021     Value type = Value(0);
2022
2023     if (args.count() > 4) fv = args[4];
2024     if (args.count() == 6) type = args[5];
2025
2026     debugSheets << "Type=" << type;
2027
2028     Value pay  = getPay(calc, rate, nper, pv, fv, type);
2029     Value ipmt = func_ipmt(args, calc, 0);
2030     return calc->sub(pay, ipmt);
2031 }
2032
2033
2034 //
2035 // PRICEMAT
2036 //
2037 Value func_pricemat(valVector args, ValueCalc *calc, FuncExtra *)
2038 {
2039     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
2040     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
2041     QDate issue = calc->conv()->asDate(args[2]).asDate(calc->settings());
2042     double rate = calc->conv()->asFloat(args[3]).asFloat();
2043     double yield = calc->conv()->asFloat(args[4]).asFloat();
2044
2045     // opt. basis
2046     int basis = 0;
2047     if (args.count() > 5)
2048         basis = calc->conv()->asInteger(args[5]).asInteger();
2049
2050     //debugSheetsFormula<<"PRICEMAT";
2051     //debugSheetsFormula<<"settlement ="<<settlement<<" maturity="<<maturity<<" issue="<<issue<<" rate="<<rate<<" yield="<<yield<<" basis="<<basis;
2052
2053     if (rate < 0.0 || yield < 0.0 || settlement >= maturity)
2054         return Value::errorVALUE();
2055
2056     long double y = daysPerYear(settlement, basis);
2057     if (!y) return Value::errorVALUE();
2058     long double issMat = daysBetweenDates(issue, maturity, basis) / y;
2059     long double issSet = daysBetweenDates(issue, settlement, basis) / y;
2060     long double setMat = daysBetweenDates(settlement, maturity, basis) / y;
2061
2062     long double res = 1.0l + issMat * rate;
2063     res /= 1.0l + setMat * yield;
2064     res -= issSet * rate;
2065     res *= 100.0l;
2066
2067     return Value(res);
2068 }
2069
2070
2071 //
2072 // Function: PV
2073 //
2074 // Returns present value, given future value, interest rate and years
2075 //
2076 Value func_pv(valVector args, ValueCalc *calc, FuncExtra *)
2077 {
2078     double rate = calc->conv()->asFloat(args[0]).asFloat();
2079     double nper = calc->conv()->asFloat(args[1]).asFloat();
2080     double pmt = calc->conv()->asFloat(args[2]).asFloat();
2081
2082     double fv = 0;
2083     int type = 0;
2084
2085     if (args.count() > 3)
2086         fv = calc->conv()->asFloat(args[3]).asFloat();
2087     if (args.count() > 4)
2088         type = calc->conv()->asInteger(args[4]).asInteger();
2089     // TODO error Value checking for type
2090
2091
2092     double pvif = pow(1 + rate, nper);
2093     double fvifa = (pvif - 1) / rate;
2094
2095     if (pvif == 0)
2096         return Value::errorDIV0();
2097
2098     double res = (-fv - pmt * (1.0 + rate * type) * fvifa) / pvif;
2099
2100     return Value(res);
2101 }
2102
2103
2104 //
2105 // Function: PV_annuity
2106 //
2107 Value func_pv_annuity(valVector args, ValueCalc *calc, FuncExtra *)
2108 {
2109     Value amount = args[0];
2110     Value interest = args[1];
2111     Value periods = args[2];
2112
2113     // recpow = 1 / pow (1 + interest, periods)
2114     // result = amount * (1 - recpow) / interest;
2115     Value recpow;
2116     recpow = calc->div(Value(1), calc->pow(calc->add(interest, Value(1)), periods));
2117     return calc->mul(amount, calc->div(calc->sub(Value(1), recpow), interest));
2118 }
2119
2120
2121 //
2122 // Function: RATE
2123 //
2124 Value func_rate(valVector args, ValueCalc *calc, FuncExtra *)
2125 {
2126     const Value nper = args[0];
2127     double fNper = calc->conv()->asFloat(nper).asFloat();
2128     double fPayment = calc->conv()->asFloat(args[1]).asFloat();
2129     double fPv = calc->conv()->asFloat(args[2]).asFloat();
2130     double fFv = (args.count() > 3) ? calc->conv()->asFloat(args[3]).asFloat() : 0;
2131     double fPayType = (args.count() > 4) ? calc->conv()->asFloat(args[4]).asFloat() : 0;
2132     double fGuess = (args.count() > 5) ? calc->conv()->asFloat(args[5]).asFloat() : 0.1;
2133
2134     if (fNper <= 0.0)
2135         return Value::errorVALUE();
2136
2137     bool bValid = true, bFound = false;
2138     double fX, fXnew, fTerm, fTermDerivation;
2139     double fGeoSeries, fGeoSeriesDerivation;
2140     const unsigned short nIterationsMax = 150;
2141     unsigned short nCount = 0;
2142     const double fEpsilonSmall = 1.0E-14;
2143     const double fEpsilon = 1.0E-7;
2144
2145     fFv -= fPayment * fPayType;
2146     fPv += fPayment * fPayType;
2147     if (nper.isInteger()) {
2148         fX = fGuess;
2149         double fPowN, fPowNminus1;
2150         while (!bFound && nCount < nIterationsMax) {
2151             fPowNminus1 = pow( 1.0+fX, fNper-1.0);
2152             fPowN = fPowNminus1 * (1.0+fX);
2153             if (calc->approxEqual(Value(fabs(fX)), Value(0.0))) {
2154                 fGeoSeries = fNper;
2155                 fGeoSeriesDerivation = fNper * (fNper-1.0)/2.0;
2156             } else {
2157                 fGeoSeries = (fPowN-1.0)/fX;
2158                 fGeoSeriesDerivation = fNper * fPowNminus1 / fX - fGeoSeries / fX;
2159             }
2160             fTerm = fFv + fPv *fPowN+ fPayment * fGeoSeries;
2161             fTermDerivation = fPv * fNper * fPowNminus1 + fPayment * fGeoSeriesDerivation;
2162             if (fabs(fTerm) < fEpsilonSmall) {
2163                 bFound = true;
2164             } else {
2165                 if (calc->approxEqual(Value(fabs(fTermDerivation)), Value(0.0)))
2166                     fXnew = fX + 1.1 * fEpsilon;
2167                 else
2168                     fXnew = fX - fTerm / fTermDerivation;
2169                 ++nCount;
2170                 bFound = (fabs(fXnew - fX) < fEpsilon);
2171                 fX = fXnew;
2172             }
2173         }
2174         bValid =(fX >=-1.0);
2175     } else {
2176         fX = (fGuess < -1.0) ? -1.0 : fGuess;
2177         while (bValid && !bFound && nCount < nIterationsMax) {
2178             if (calc->approxEqual(Value(fabs(fX)), Value(0.0))) {
2179                 fGeoSeries = fNper;
2180                 fGeoSeriesDerivation = fNper * (fNper-1.0)/2.0;
2181             } else {
2182                 fGeoSeries = (pow( 1.0+fX, fNper) - 1.0) / fX;
2183                 fGeoSeriesDerivation = fNper * pow( 1.0+fX, fNper-1.0) / fX - fGeoSeries / fX;
2184             }
2185             fTerm = fFv + fPv *pow(1.0 + fX,fNper)+ fPayment * fGeoSeries;
2186             fTermDerivation = fPv * fNper * pow(1.0+fX, fNper-1.0) + fPayment * fGeoSeriesDerivation;
2187             if (fabs(fTerm) < fEpsilonSmall) {
2188                 bFound = true;
2189             } else {
2190                 if (calc->approxEqual(Value(fabs(fTermDerivation)), Value(0.0)))
2191                     fXnew = fX + 1.1 * fEpsilon;
2192                 else
2193                     fXnew = fX - fTerm / fTermDerivation;
2194                 ++nCount;
2195                 bFound = (fabs(fXnew - fX) < fEpsilon);
2196                 fX = fXnew;
2197                 bValid = (fX >= -1.0);
2198             }
2199         }
2200     }
2201     fGuess = fX;
2202     // ODF specs do not say that we should return an error in that case
2203     //if (!bValid || !bFound) return Value::errorVALUE();
2204     return Value(fGuess);
2205 }
2206
2207
2208 //
2209 // Function: RECEIVED
2210 //
2211 Value func_received(valVector args, ValueCalc *calc, FuncExtra *)
2212 {
2213     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
2214     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
2215
2216     Value investment = args[2];
2217     Value discount = args[3];
2218
2219     int basis = 0;
2220     if (args.count() == 5)
2221         basis = calc->conv()->asInteger(args[4]).asInteger();
2222
2223     double d = daysBetweenDates(settlement, maturity, basis);
2224     double y = daysPerYear(settlement, basis);
2225
2226     if (d <= 0 || y <= 0 || basis < 0 || basis > 4)
2227         return Value(false);
2228
2229     // 1.0 - ( discount * d / y )
2230     Value x = calc->sub(Value(1.0), (calc->mul(discount, d / y)));
2231
2232     if (calc->isZero(x))
2233         return Value::errorVALUE();
2234     return calc->div(investment, x);
2235 }
2236
2237
2238 //
2239 // Function: RRI
2240 //
2241 Value func_rri(valVector args, ValueCalc *calc, FuncExtra *)
2242 {
2243     double p = calc->conv()->asFloat(args[0]).asFloat();
2244     double pv = calc->conv()->asFloat(args[1]).asFloat();
2245     double fv = calc->conv()->asFloat(args[2]).asFloat();
2246
2247     // constraints N>0
2248     if (p < 1)
2249         return Value::errorVALUE();
2250
2251     double res = pow((fv / pv), 1 / p) - 1;
2252     return Value(res);
2253 }
2254
2255
2256 //
2257 // Function: SLN
2258 //
2259 // straight-line depreciation for a single period
2260 //
2261 Value func_sln(valVector args, ValueCalc *calc, FuncExtra *)
2262 {
2263     Value cost = args[0];
2264     Value salvage_value = args[1];
2265     Value life = args[2];
2266
2267     // sentinel check
2268     if (!calc->greater(life, Value(0.0)))
2269         return Value::errorVALUE();
2270
2271     // (cost - salvage_value) / life
2272     return calc->div(calc->sub(cost, salvage_value), life);
2273 }
2274
2275
2276 //
2277 // Function: SYD
2278 //
2279 // sum-of-years digits depreciation
2280 //
2281 Value func_syd(valVector args, ValueCalc *calc, FuncExtra *)
2282 {
2283     Value cost = args[0];
2284     Value salvage_value = args[1];
2285     Value life = args[2];
2286     Value period = args[3];
2287
2288     // sentinel check
2289     if (!calc->greater(life, Value(0.0)))
2290         return Value::errorVALUE();
2291
2292     // v1 = cost - salvage_value
2293     // v2 = life - period + 1
2294     // v3 = life * (life + 1.0)
2295     // result = (v1 * v2 * 2) / v3
2296     Value v1, v2, v3;
2297     v1 = calc->sub(cost, salvage_value);
2298     v2 = calc->add(calc->sub(life, period), 1);
2299     v3 = calc->mul(life, calc->add(life, 1.0));
2300     return calc->div(calc->mul(calc->mul(v1, v2), 2), v3);
2301 }
2302
2303
2304 //
2305 // Function: TBILLEQ
2306 //
2307 Value func_tbilleq(valVector args, ValueCalc *calc, FuncExtra *)
2308 {
2309     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
2310     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
2311     double discount = calc->conv()->asFloat(args[2]).asFloat();
2312
2313     maturity = maturity.addDays(1);
2314     int days = days360(settlement, maturity, false);  // false -> US
2315
2316     if (settlement >= maturity || discount <= 0.0 || days > 360)
2317         return Value::errorVALUE();
2318
2319     double res = (365 * discount) / (360 - (discount * double(days)));
2320
2321     return Value(res);
2322 }
2323
2324
2325 //
2326 // Function: TBILLPRICE
2327 //
2328 Value func_tbillprice(valVector args, ValueCalc *calc, FuncExtra *)
2329 {
2330     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
2331     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
2332     Value discount = args[2];
2333
2334     QDate date0 = calc->settings()->referenceDate(); // referenceDate
2335
2336     double fraction = yearFrac(date0, settlement, maturity.addDays(1), 0); // basis: USA 30/360
2337     double dummy;
2338
2339     if (modf(fraction, &dummy) == 0.0)
2340         return Value::errorVALUE();
2341
2342 //   double days = settlement.daysTo( maturity );
2343 // TODO error value checking
2344 //   if (settlement > maturity || calc->lower (discount, Value(0)) || days > 265)
2345 //     return Value::errorVALUE();
2346
2347     return Value(100.0*(1.0 - discount.asFloat()*fraction));
2348 }
2349
2350
2351 //
2352 // Function: TBILLYIELD
2353 //
2354 Value func_tbillyield(valVector args, ValueCalc *calc, FuncExtra *)
2355 {
2356     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
2357     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
2358     double price = calc->conv()->asFloat(args[2]).asFloat();
2359
2360     double days = days360(settlement, maturity, false);  // false -> US
2361     ++days;
2362
2363     if (settlement >= maturity || days > 360 || price <= 0.0)
2364         return Value::errorVALUE();
2365
2366     double res = 100.0;
2367     res /= price;
2368     res--;
2369     res /= days;
2370     res *= 360.0;
2371
2372     return Value(res);
2373 }
2374
2375
2376 //
2377 // Function: VDB
2378 //
2379 // VDB( cost; salvage; life; startPeriod; endPeriod [; depreciation-factor = 2 [; straight-line depreciation = TRUE ] )
2380 //
2381 Value func_vdb(valVector args, ValueCalc *calc, FuncExtra *)
2382 {
2383     double cost = calc->conv()->asFloat(args[0]).asFloat();
2384     double salvage = calc->conv()->asFloat(args[1]).asFloat();
2385     double life = calc->conv()->asFloat(args[2]).asFloat();
2386     double startPeriod = calc->conv()->asFloat(args[3]).asFloat();
2387     double endPeriod = calc->conv()->asFloat(args[4]).asFloat();
2388
2389     // defaults
2390     double depreciationFactor = 2;
2391     bool flag = false;
2392
2393     // opt. parameter
2394     if (args.count() > 6)
2395         flag = calc->conv()->asInteger(args[6]).asInteger();
2396     if (args.count() >= 5)
2397         depreciationFactor = calc->conv()->asFloat(args[5]).asFloat();
2398
2399     // check if parameters are valid
2400     if (cost < 0.0 || endPeriod < startPeriod || endPeriod > life || cost < 0.0 || salvage > cost || depreciationFactor <= 0.0)
2401         return Value::errorVALUE();
2402
2403     // calc loop start and end
2404     double         intStart = floor(startPeriod);
2405     double         intEnd   = ceil(endPeriod);
2406     unsigned long loopStart = (unsigned long) intStart;
2407     unsigned long loopEnd   = (unsigned long) intEnd;
2408
2409     double res = 0.0;
2410
2411     if (flag) {
2412         // no straight-line depreciation
2413         for (unsigned long i = loopStart + 1; i <= loopEnd; ++i) {
2414             double term = vdbGetGDA(cost, salvage, life, (double) i, depreciationFactor);
2415
2416             //
2417             if (i == loopStart + 1)
2418                 term *= (fmin(endPeriod, intStart + 1.0) - startPeriod);
2419             else if (i == loopEnd)
2420                 term *= (endPeriod + 1.0 - intEnd);
2421
2422             res += term;
2423         }
2424     } else {
2425         double life1 = life;
2426         double part;
2427
2428         if (startPeriod != floor(startPeriod)) {
2429             if (depreciationFactor > 1) {
2430                 if (startPeriod >= life / 2 || startPeriod == life / 2) {
2431                     part        = startPeriod - life / 2;
2432                     startPeriod = life / 2;
2433                     endPeriod  -= part;
2434                     life1      += 1;
2435                 }
2436             }
2437         }
2438
2439         cost -= vdbInterVDB(cost, salvage, life, life1, startPeriod, depreciationFactor);
2440         res   = vdbInterVDB(cost, salvage, life, life - startPeriod, endPeriod - startPeriod, depreciationFactor);
2441     } // end not flag
2442
2443     return Value(res);
2444 }
2445
2446
2447 //
2448 // Function: XIRR
2449 //
2450 // Compute the internal rate of return for a non-periodic series of cash flows.
2451 //
2452 // XIRR ( Values; Dates; [ Guess = 0.1 ] )
2453 //
2454
2455 // TODO sascha check dates
2456 Value func_xirr(valVector args, ValueCalc *calc, FuncExtra *)
2457 {
2458     double resultRate = 0.1;
2459     if (args.count() > 2)
2460         resultRate = calc->conv()->asFloat(args[2]).asFloat();
2461
2462     // check pairs and count >= 2 and guess > -1.0
2463     if (args[0].count() != args[1].count() || args[1].count() < 2 || resultRate <= -1.0)
2464         return Value::errorVALUE();
2465
2466     // define max epsilon
2467     static const double maxEpsilon = 1e-10;
2468
2469     // max number of iterations
2470     static const int maxIter = 50;
2471
2472     // Newton's method - try to find a res, with a accuracy of maxEpsilon
2473     double newRate, rateEpsilon, resultValue;
2474     int i = 0;
2475     bool contLoop;
2476
2477     do {
2478         resultValue = xirrResult(args, calc, resultRate);
2479         newRate =  resultRate - resultValue / xirrResultDerive(args, calc, resultRate);
2480         rateEpsilon = fabs(newRate - resultRate);
2481         resultRate = newRate;
2482         contLoop = (rateEpsilon > maxEpsilon) && (fabs(resultValue) > maxEpsilon);
2483     } while (contLoop && (++i < maxIter));
2484
2485     if (contLoop)
2486         return Value::errorVALUE();
2487
2488     return Value(resultRate);
2489 }
2490
2491 //
2492 // Function: xnpv
2493 //
2494 // Compute the net present value of a series of cash flows.
2495 //
2496 // XNPV ( Rate; Values; Dates )
2497 //
2498 Value func_xnpv(valVector args, ValueCalc *calc, FuncExtra *)
2499 {
2500     double rate = calc->conv()->asFloat(args[0]).asFloat();
2501     ++rate;
2502
2503     int numValues = args[1].count();
2504     int numDates  = args[2].count();
2505
2506     double res = 0;
2507
2508     // check pairs
2509     if (numValues != numDates || numValues < 2)
2510         return Value::errorVALUE();
2511     // check rate
2512     if (rate < -1.0)
2513         return Value::errorNUM();
2514
2515     QDate date0 = calc->conv()->asDate(args[2].element(0)).asDate(calc->settings());
2516     double val;
2517     QDate date;
2518
2519     for (int i = 0; i < numValues; ++i) {
2520         val  = calc->conv()->asFloat(args[1].element(i)).asFloat();
2521         Value tmpDate(calc->conv()->asDate(args[2].element(i)));
2522
2523         if (tmpDate.isError()) return tmpDate;
2524         date = tmpDate.asDate(calc->settings());
2525
2526         // check if date is valid
2527         if (!date.isValid())
2528             return Value::errorNUM();
2529
2530         int days = date0.daysTo(date);
2531
2532         res += val / pow(rate, days / 365.0);
2533     }
2534
2535     return Value(res);
2536 }
2537
2538
2539 //
2540 // YIELDDISC
2541 //
2542 Value func_yielddisc(valVector args, ValueCalc *calc, FuncExtra *)
2543 {
2544     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
2545     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
2546     double price = calc->conv()->asFloat(args[2]).asFloat();
2547     double redemp = calc->conv()->asFloat(args[3]).asFloat();
2548
2549     // opt. basis
2550     int basis = 0;
2551     if (args.count() > 4)
2552         basis = calc->conv()->asInteger(args[4]).asInteger();
2553
2554     if (price <= 0.0 || redemp <= 0.0 || settlement >= maturity)
2555         return Value::errorVALUE();
2556
2557     QDate date0 = calc->settings()->referenceDate(); // referenceDate
2558
2559     double res = (redemp / price) - 1.0;
2560     res /= yearFrac(date0, settlement, maturity, basis);
2561
2562     return Value(res);
2563 }
2564
2565
2566 //
2567 // YIELDMAT
2568 //
2569 Value func_yieldmat(valVector args, ValueCalc *calc, FuncExtra *)
2570 {
2571     QDate settlement = calc->conv()->asDate(args[0]).asDate(calc->settings());
2572     QDate maturity = calc->conv()->asDate(args[1]).asDate(calc->settings());
2573     QDate issue = calc->conv()->asDate(args[2]).asDate(calc->settings());
2574     long double rate = calc->conv()->asFloat(args[3]).asFloat();
2575     long double price = calc->conv()->asFloat(args[4]).asFloat();
2576
2577     // opt. basis
2578     int basis = 0;
2579     if (args.count() > 5)
2580         basis = calc->conv()->asInteger(args[5]).asInteger();
2581
2582     if (price <= 0.0 || rate <= 0.0 || settlement >= maturity)
2583         return Value::errorVALUE();
2584
2585     QDate date0 = calc->settings()->referenceDate(); // referenceDate
2586
2587     long double issMat = yearFrac(date0, issue, maturity, basis);
2588     long double issSet = yearFrac(date0, issue, settlement, basis);
2589     long double setMat = yearFrac(date0, settlement, maturity, basis);
2590
2591     long double res = 1.0l + issMat * rate;
2592     res /= price / 100.0l + issSet * rate;
2593     res--;
2594     res /= setMat;
2595
2596     return Value(res);
2597 }
2598
2599 //
2600 // Function: zero_coupon
2601 //
2602 Value func_zero_coupon(valVector args, ValueCalc *calc, FuncExtra *)
2603 {
2604     // Returns effective interest rate given nominal rate and periods per year
2605
2606     Value face = args[0];
2607     Value rate = args[1];
2608     Value years = args[2];
2609
2610     // face / pow(1 + rate, years)
2611     return calc->div(face, calc->pow(calc->add(rate, 1), years));
2612 }
2613
2614 #include "financial.moc"