File indexing completed on 2024-05-19 16:08:42
0001 /* This file is part of the KDE project 0002 Copyright 2006 Ariya Hidayat <ariya@kde.org> 0003 Copyright 2007 Sascha Pfau <MrPeacock@gmail.com> 0004 0005 This library is free software; you can redistribute it and/or 0006 modify it under the terms of the GNU Library General Public 0007 License as published by the Free Software Foundation; only 0008 version 2 of the License. 0009 0010 This library is distributed in the hope that it will be useful, 0011 but WITHOUT ANY WARRANTY; without even the implied warranty of 0012 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 0013 Library General Public License for more details. 0014 0015 You should have received a copy of the GNU Library General Public License 0016 along with this library; see the file COPYING.LIB. If not, write to 0017 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, 0018 Boston, MA 02110-1301, USA. 0019 */ 0020 #include "TestFinancialFunctions.h" 0021 0022 #include "TestKspreadCommon.h" 0023 0024 #include <QTest> 0025 0026 void TestFinancialFunctions::initTestCase() 0027 { 0028 FunctionModuleRegistry::instance()->loadFunctionModules(); 0029 } 0030 0031 // NOTE: we do not compare the numbers _exactly_ because it is difficult 0032 // to get one "true correct" expected values for the functions due to: 0033 // - different algorithms among spreadsheet programs 0034 // - precision limitation of floating-point number representation 0035 // - accuracy problem due to propagated error in the implementation 0036 0037 #define CHECK_EVAL(x,y) QCOMPARE(TestDouble(x,y,6),y) 0038 #define CHECK_EVAL_SHORT(x,y) QCOMPARE(TestDouble(x,y,11),y) 0039 #define CHECK_EVAL_EQUAL(x,y) QCOMPARE(TestSimple(x),y) 0040 0041 static Value TestDouble(const QString& formula, const Value& v2, int accuracy) 0042 { 0043 double epsilon = DBL_EPSILON * pow(10.0, (double)(accuracy)); 0044 0045 Formula f; 0046 QString expr = formula; 0047 if (expr[0] != '=') 0048 expr.prepend('='); 0049 f.setExpression(expr); 0050 Value result = f.eval(); 0051 0052 bool res = fabs(v2.asFloat() - result.asFloat()) < epsilon; 0053 0054 if (!res) 0055 qDebug() << "check failed -->" << "Epsilon =" << epsilon << "" << (double)v2.asFloat() << " to" << (double)result.asFloat() << " diff =" << (double)(v2.asFloat() - result.asFloat()); 0056 // else 0057 // qDebug()<<"check -->" <<" diff =" << v2.asFloat()-result.asFloat(); 0058 if (res) 0059 return v2; 0060 else 0061 return result; 0062 } 0063 0064 static Value TestSimple(const QString& formula) 0065 { 0066 Formula f; 0067 QString expr = formula; 0068 if (expr[0] != '=') 0069 expr.prepend('='); 0070 f.setExpression(expr); 0071 return f.eval(); 0072 } 0073 0074 // ACCRINT 0075 void TestFinancialFunctions::testACCRINT() 0076 { 0077 // odf test 0078 CHECK_EVAL("ACCRINT( \"1992-12-01\"; \"1993-06-01\"; \"1993-07-01\"; 0.055; 100 ; 2; 0 ) ", Value(3.2083333333)); 0079 CHECK_EVAL("ACCRINT( \"2001-02-28\"; \"2001-08-31\";\"2001-05-01\"; 0.1 ; 1000; 2; 0 )", 0080 Value(16.9444444444)); // A security is issued on 2.28.2001. 0081 // First interest is set for 8.31.2001. The settlement date is 5.1.2001. 0082 // The Rate is 0.1 or 10% and Par is 1000 currency units. Interest is paid 0083 // half-yearly (frequency is 2). The basis is the US method (0). How much interest has accrued? 0084 CHECK_EVAL("ACCRINT( \"2004-02-01\"; \"2004-04-01\"; \"2004-05-01\"; 0.1; 1000; 4; 0 )", Value(24.7222222222)); // leap year, quarterly, US (NASD) 30/360 0085 CHECK_EVAL_SHORT("ACCRINT( \"2004-02-01\"; \"2004-04-01\"; \"2004-05-01\"; 0.1; 1000; 4; 1 )", Value(24.590164)); // leap year, quarterly, actual/acual 0086 CHECK_EVAL("ACCRINT( \"2004-02-01\"; \"2004-04-01\"; \"2004-05-01\"; 0.1; 1000; 4; 2 )", Value(25)); // leap year, quarterly, actual/360 0087 CHECK_EVAL_SHORT("ACCRINT( \"2004-02-01\"; \"2004-04-01\"; \"2004-05-01\"; 0.1; 1000; 4; 3 )", Value(24.657534)); // leap year, quaterly, actual/365 0088 CHECK_EVAL("ACCRINT( \"2004-02-01\"; \"2004-04-01\"; \"2004-05-01\"; 0.1; 1000; 4; 4 )", Value(25)); // leap year, quarterly, European 30/360 0089 CHECK_EVAL("ACCRINT( \"2004-02-01\"; \"2004-04-01\"; \"2004-05-01\"; 0.1; 1000; 1 )", Value(24.7222222222)); // leap year, annual, US (NASD) 30/360 0090 CHECK_EVAL("ACCRINT( \"2004-02-01\"; \"2004-04-01\"; \"2004-05-01\"; 0.1; 1000; 2 )", Value(24.7222222222)); // leap year, semiannual, US 30/360 0091 // alternate function name 0092 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETACCRINT( \"1992-12-01\";\"1993-06-01\";\"1993-07-01\";0.055;100;2;0)", Value(3.2083333333)); 0093 } 0094 0095 // ACCRINTM 0096 void TestFinancialFunctions::testACCRINTM() 0097 { 0098 // Calligra Sheets 0099 CHECK_EVAL_SHORT("ACCRINTM( \"2001-04-01\"; \"2001-06-15\"; 0.1; 1000; 3 )", Value(20.5479454)); 0100 CHECK_EVAL_SHORT("ACCRINTM( \"2004-02-01\"; \"2004-05-01\"; 0.1; 1000; 0 )", Value(24.722222)); // leap year, US (NASD) 30/360 0101 CHECK_EVAL_SHORT("ACCRINTM( \"2004-02-01\"; \"2004-05-01\"; 0.1; 1000; 1 )", Value(24.590164)); // leap year, actual/actual 0102 CHECK_EVAL_SHORT("ACCRINTM( \"2004-02-01\"; \"2004-05-01\"; 0.1; 1000; 2 )", Value(25.0)); // leap year, actual/360 0103 CHECK_EVAL_SHORT("ACCRINTM( \"2004-02-01\"; \"2004-05-01\"; 0.1; 1000; 3 )", Value(24.657534)); // leap year, actual/365 0104 CHECK_EVAL_SHORT("ACCRINTM( \"2004-02-01\"; \"2004-05-01\"; 0.1; 1000; 4 )", Value(25.0)); // leap year, European 30/360 0105 // alternate function name 0106 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETACCRINTM( \"2001-04-01\"; \"2001-06-15\"; 0.1; 1000; 3 )", Value(20.5479454)); 0107 } 0108 0109 // AMORDEGRC 0110 void TestFinancialFunctions::testAMORDEGRC() 0111 { 0112 // Excel Formen und Funktionen 0113 CHECK_EVAL("AMORDEGRC( 2400; 34199; 34334; 300; 1; 0.15; 1 )" , Value(775)); 0114 0115 // bettersolution.com 0116 CHECK_EVAL("AMORDEGRC( 50000; \"2003-01-01\"; \"2003-12-31\"; 500; 1; 0.15; 1 )" , Value(11738)); // 0117 CHECK_EVAL("AMORDEGRC( 50000; \"2003-01-01\"; \"2003-12-31\"; 500; 2; 0.15; 1 )" , Value(7336)); // 0118 CHECK_EVAL("AMORDEGRC( 50000; \"2003-01-01\"; \"2003-12-31\"; 500; 3; 0.15; 1 )" , Value(4585)); // 0119 CHECK_EVAL("AMORDEGRC( 50000; \"2003-01-01\"; \"2003-12-31\"; 500; 4; 0.15; 1 )" , Value(2866)); // 0120 // CHECK_EVAL_SHORT( "AMORDEGRC( 50000; \"2003-01-01\"; \"2003-12-31\"; 500; 5; 0.15; 1 )" , Value( 2388 ) ); // TODO check Calligra Sheets -> 1791 0121 0122 // odf tests 0123 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 1 )" , Value(228)); // the first period (10 years life time) 0124 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 8; 0.1; 1 )" , Value(26)); // (specs. 52) the period before last (10 years) 0125 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 9; 0.1; 1 )" , Value(19)); // (specs. 52) the last period (10 years life time) 0126 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 10; 0.1; 1 )" , Value(15)); // (specs. 15) - beyond life time (10 years life time) 0127 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.25; 1 )" , Value(342)); // the first period (4 years life time) 0128 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 0 )" , Value(229)); // leap year, US (NASD) 30/360 0129 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 1 )" , Value(228)); // leap year, actual/actual 0130 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 2 )" , Value(231)); // (specs 232) leap year, actual/360 0131 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 3 )" , Value(228)); // leap year, actual/365 0132 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 4 )" , Value(228)); // leap year, European 30/360 0133 0134 // alternate function name 0135 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETAMORDEGRC(2400;34199;34334;300;1;0.15;1)", Value(775)); 0136 } 0137 0138 // AMORLINC 0139 void TestFinancialFunctions::testAMORLINC() 0140 { 0141 CHECK_EVAL_SHORT("AMORLINC( 1000; \"2004-02-01\"; \"2004-12-31\"; 10; 0; 0.1; 1 )" , Value(91.2568306011)); // the first period (10 years life time) 0142 CHECK_EVAL_SHORT("AMORLINC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 3 )" , Value(91.2328767123)); // leap year, actual/365 0143 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETAMORLINC(1000;\"2004-02-01\";\"2004-12-31\";10;0;0.1;1)", Value(91.2568306011)); 0144 } 0145 0146 // COMPOUND 0147 void TestFinancialFunctions::testCOMPOUND() 0148 { 0149 // Calligra Sheets 0150 CHECK_EVAL_SHORT("COMPOUND(5000;0.12;4;5)", Value(9030.556173)); 0151 } 0152 0153 // CONTINUOUS 0154 void TestFinancialFunctions::testCONTINUOUS() 0155 { 0156 // Calligra Sheets 0157 CHECK_EVAL_SHORT("CONTINUOUS(1000;0.1;1)", Value(1105.17091808)); 0158 } 0159 0160 // COUPDAYBS 0161 void TestFinancialFunctions::testCOUPDAYBS() 0162 { 0163 // ODF 0164 CHECK_EVAL_SHORT("COUPDAYBS( DATE(1997;11;9); DATE(1999;11;15); 2 )", Value(174)); 0165 CHECK_EVAL_SHORT("COUPDAYBS( DATE(1997;11;9); DATE(1999;11;15); 2; 1 )", Value(178)); 0166 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 4; 0 )", Value(60)); // US (NASD) 30/360 0167 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 4; 1 )", Value(60)); // actual/actual 0168 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 4; 2 )", Value(60)); // actual/360 0169 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 4; 3 )", Value(60)); // actual/365 0170 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 4; 4 )", Value(60)); // European 30/360 0171 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 1 )", Value(60)); // annual 0172 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 2 )", Value(60)); // semiannual 0173 // alternate function name 0174 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPDAYBS( DATE(1997;11;9); DATE(1999;11;15); 2 )", Value(174)); 0175 } 0176 0177 // COUPDAYS 0178 void TestFinancialFunctions::testCOUPDAYS() 0179 { 0180 // ODF 0181 CHECK_EVAL_SHORT("COUPDAYS( DATE(1997;11;9); DATE(1999;11;15); 2 )", Value(180)); 0182 CHECK_EVAL_SHORT("COUPDAYS( DATE(1997;11;9); DATE(1999;11;15); 2; 1 )", Value(184)); 0183 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 4; 0 )", Value(90)); // US (NASD) 30/360 0184 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 4; 1 )", Value(91)); // actual/actual 0185 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 4; 2 )", Value(90)); // actual/360 0186 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 4; 3 )", Value(91.25)); // actual/365 0187 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 4; 4 )", Value(90)); // European 30/360 0188 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 1 )", Value(360)); // annual 0189 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 2 )", Value(180)); // semiannual 0190 // alternate function name 0191 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPDAYS( DATE(1997;11;9); DATE(1999;11;15); 2 )", Value(180)); 0192 } 0193 0194 // COUPDAYSNC 0195 void TestFinancialFunctions::testCOUPDAYSNC() 0196 { 0197 // ODF 0198 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(1997;5;19); DATE(1999;11;15); 2 )", Value(176)); 0199 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(1997;5;19); DATE(1999;11;15); 2; 1 )", Value(180)); 0200 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 4; 0 )", Value(60)); 0201 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 4; 1 )", Value(60)); 0202 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 4; 2 )", Value(60)); 0203 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 4; 3 )", Value(60)); 0204 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 4; 4 )", Value(60)); 0205 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 1 )", Value(330)); // annual 0206 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 2 )", Value(150)); // semiannual 0207 // alternate function name 0208 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPDAYSNC( DATE(1997;5;19); DATE(1999;11;15); 2 )", Value(176)); 0209 } 0210 0211 // COUPNCD 0212 void TestFinancialFunctions::testCOUPNCD() 0213 { 0214 // ODF 0215 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2007-01-01\"; 1; 1 )=DATE(2005;01;01)", Value(true)); // Annual 0216 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2007-01-01\"; 2; 1 )=DATE(2004;07;01)", Value(true)); // Semiannual 0217 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2007-01-01\"; 4; 1 )=DATE(2004;04;01)", Value(true)); // Quarterly 0218 CHECK_EVAL_EQUAL("COUPNCD( \"2007-01-01\"; \"2007-01-01\"; 1; 1 )", Value::errorVALUE()); // settlement < maturity 0219 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2009-01-01\"; 4; 0 )=DATE(2004;04;01)", Value(true)); 0220 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2009-01-01\"; 4; 1 )=DATE(2004;04;01)", Value(true)); 0221 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2009-01-01\"; 4; 2 )=DATE(2004;04;01)", Value(true)); 0222 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2009-01-01\"; 4; 3 )=DATE(2004;04;01)", Value(true)); 0223 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2009-01-01\"; 4; 4 )=DATE(2004;04;01)", Value(true)); 0224 // alternate function name 0225 CHECK_EVAL_EQUAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPNCD( \"2004-01-01\"; \"2007-01-01\"; 1; 1 )=DATE(2005;01;01)", Value(true)); 0226 } 0227 0228 // COUPNUM 0229 void TestFinancialFunctions::testCOUPNUM() 0230 { 0231 // ODF 0232 CHECK_EVAL_SHORT("COUPNUM( \"2004-01-01\"; \"2007-01-01\"; 1; 1 )", Value(3)); // Annual 0233 CHECK_EVAL_SHORT("COUPNUM( \"2004-01-01\"; \"2007-01-01\"; 2; 1 )", Value(6)); // Semiannual 0234 CHECK_EVAL_SHORT("COUPNUM( \"2004-01-01\"; \"2007-01-01\"; 4; 1 )", Value(12)); // Quarterly 0235 CHECK_EVAL_SHORT("COUPNUM( \"2004-02-01\"; \"2009-01-01\"; 4; 0 )", Value(20)); // 0236 CHECK_EVAL_SHORT("COUPNUM( \"2004-02-01\"; \"2009-01-01\"; 4; 1 )", Value(20)); // 0237 CHECK_EVAL_SHORT("COUPNUM( \"2004-02-01\"; \"2009-01-01\"; 4; 2 )", Value(20)); // 0238 CHECK_EVAL_SHORT("COUPNUM( \"2004-02-01\"; \"2009-01-01\"; 4; 3 )", Value(20)); // 0239 CHECK_EVAL_SHORT("COUPNUM( \"2004-02-01\"; \"2009-01-01\"; 4; 4 )", Value(20)); // 0240 CHECK_EVAL_SHORT("COUPNUM( \"2004-01-01\"; \"2004-04-30\"; 12; 1 )", Value(4)); // Monthly, to end of month with less daysInMonth 0241 CHECK_EVAL_SHORT("COUPNUM( \"2004-01-01\"; \"2004-05-01\"; 12; 1 )", Value(4)); // Monthly, to begin of month 0242 // alternate function name 0243 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPNUM(\"2004-01-01\";\"2007-01-01\";1;1)", Value(3)); 0244 } 0245 0246 // COUPPCD 0247 void TestFinancialFunctions::testCOUPPCD() 0248 { 0249 // ODF 0250 CHECK_EVAL_EQUAL("COUPPCD( \"2004-12-31\"; \"2007-01-01\"; 1; 1 )=DATE(2004;1;1)", Value(true)); // Annual 0251 CHECK_EVAL_EQUAL("COUPPCD( \"2004-12-31\"; \"2007-01-01\"; 2; 1 )=DATE(2004;7;1)", Value(true)); // Semiannual 0252 CHECK_EVAL_EQUAL("COUPPCD( \"2004-12-31\"; \"2007-01-01\"; 4; 1 )=DATE(2004;10;1)", Value(true)); // Quarterly 0253 CHECK_EVAL_EQUAL("COUPPCD( \"2007-01-01\"; \"2004-01-01\"; 1; 1 )", Value::errorVALUE()); // settlement < maturity 0254 CHECK_EVAL_EQUAL("COUPPCD( \"2004-02-29\"; \"2009-01-01\"; 4; 0 )=DATE(2004;01;01)", Value(true)); 0255 CHECK_EVAL_EQUAL("COUPPCD( \"2004-02-29\"; \"2009-01-01\"; 4; 1 )=DATE(2004;01;01)", Value(true)); 0256 CHECK_EVAL_EQUAL("COUPPCD( \"2004-02-29\"; \"2009-01-01\"; 4; 2 )=DATE(2004;01;01)", Value(true)); 0257 CHECK_EVAL_EQUAL("COUPPCD( \"2004-02-29\"; \"2009-01-01\"; 4; 3 )=DATE(2004;01;01)", Value(true)); 0258 CHECK_EVAL_EQUAL("COUPPCD( \"2004-02-29\"; \"2009-01-01\"; 4; 4 )=DATE(2004;01;01)", Value(true)); 0259 // alternate function name 0260 CHECK_EVAL_EQUAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPPCD( \"2004-12-31\"; \"2007-01-01\"; 1; 1 )=DATE(2004;1;1)", Value(true)); 0261 } 0262 0263 // CUMIPMT 0264 void TestFinancialFunctions::testCUMIPMT() 0265 { 0266 // ODF 0267 CHECK_EVAL_SHORT("CUMIPMT( 0.06/12; 5*12; 100000; 5; 12; 0 )", Value(-3562.187023)); // maturity at the end of a period 0268 CHECK_EVAL_SHORT("CUMIPMT( 0.06/12; 5*12; 100000; 5; 12; 1 )", Value(-3544.464699)); // maturity at the beginning of a period 0269 CHECK_EVAL_SHORT("CUMIPMT( 0.06/12; 5*12; 100000; 0; 0; 0 )", Value(Value::errorVALUE())); // start > 0; end > 0 0270 CHECK_EVAL_SHORT("CUMIPMT( 0.06/12; 5*12; 100000; 5; 61; 0 )", Value(Value::errorVALUE())); // end > periods 0271 CHECK_EVAL_SHORT("CUMIPMT( 0.06/12; 5*12; 100000; 15; 12; 0 )", Value(Value::errorVALUE())); // start > end 0272 // alternate function name 0273 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCUMIPMT(0.06/12;5*12;100000;5;12;0)", Value(-3562.187023)); 0274 } 0275 0276 // CUMPRINC 0277 void TestFinancialFunctions::testCUMPRINC() 0278 { 0279 // ODF 0280 CHECK_EVAL_SHORT("CUMPRINC( 0.06/12; 5*12; 100000; 5; 12; 0 )", Value(-11904.054201)); // maturity at the end of a period 0281 CHECK_EVAL_SHORT("CUMPRINC( 0.06/12; 5*12; 100000; 5; 12; 1 )", Value(-11844.830051)); // maturity at the beginning of a period 0282 CHECK_EVAL_SHORT("CUMPRINC( 0.06/12; 5*12; 100000; 0; 0; 0 )", Value(Value::errorVALUE())); // start > 0; end > 0 0283 CHECK_EVAL_SHORT("CUMPRINC( 0.06/12; 5*12; 100000; 5; 61; 0 )", Value(Value::errorVALUE())); // end > periods 0284 CHECK_EVAL_SHORT("CUMPRINC( 0.06/12; 5*12; 100000;15; 12; 0 )", Value(Value::errorVALUE())); // start > end 0285 // alternate function name 0286 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCUMPRINC(0.06/12;5*12;100000;5;12;0)", Value(-11904.054201)); 0287 } 0288 0289 // Fixed-declining balance depreciation 0290 // DB(cost, salvage, life, period, month) 0291 void TestFinancialFunctions::testDB() 0292 { 0293 // Excel example: http://office.microsoft.com/en-us/excel/HP100623551033.aspx 0294 CHECK_EVAL("DB(1000000; 100000; 6; 1; 7)", Value(186083.3333333333)); 0295 CHECK_EVAL("DB(1000000; 100000; 6; 2; 7)", Value(259639.4166666667)); 0296 CHECK_EVAL("DB(1000000; 100000; 6; 3; 7)", Value(176814.4427500000)); 0297 CHECK_EVAL("DB(1000000; 100000; 6; 4; 7)", Value(120410.6355127500)); 0298 CHECK_EVAL("DB(1000000; 100000; 6; 5; 7)", Value(81999.64278418274)); 0299 CHECK_EVAL("DB(1000000; 100000; 6; 6; 7)", Value(55841.75673602846)); 0300 CHECK_EVAL("DB(1000000; 100000; 6; 7; 7)", Value(15845.09847384807)); 0301 0302 // http://www.vni.com/products/imsl/jmsl/v30/api/com/imsl/finance/dbEx1.html 0303 CHECK_EVAL("DB(2500; 500; 3; 1; 6)", Value(518.750000000000)); 0304 CHECK_EVAL("DB(2500; 500; 3; 2; 6)", Value(822.218750000000)); 0305 CHECK_EVAL("DB(2500; 500; 3; 3; 6)", Value(480.997968750000)); 0306 CHECK_EVAL("DB(2500; 500; 3; 4; 6)", Value(140.691905859375)); 0307 0308 // test cases in OpenFormula specification 0309 CHECK_EVAL("DB(4000;500;4;2)", Value(963.90)); 0310 CHECK_EVAL("DB(4000;500;4;2;2)", Value(1510.65)); 0311 CHECK_EVAL("DB(4000;500;4;5)", Value(0.0)); 0312 CHECK_EVAL("DB(0;500;4;2)", Value(Value::errorNUM())); 0313 CHECK_EVAL("DB(4000;-500;4;2)", Value(Value::errorNUM())); 0314 CHECK_EVAL("DB(4000;500;0;0)", Value(Value::errorNUM())); 0315 CHECK_EVAL("DB(4000;500;2;0)", Value(Value::errorNUM())); 0316 } 0317 0318 // Double declining balance depreciation 0319 // DDB(cost, salvage, life, period, factor) 0320 void TestFinancialFunctions::testDDB() 0321 { 0322 // Excel example: http://office.microsoft.com/en-us/excel/HP100623561033.aspx 0323 CHECK_EVAL("DDB(2400; 300; 10*365; 1; 2)", Value(1.31506849315065)); 0324 CHECK_EVAL("DDB(2400; 300; 10*12; 1; 2)", Value(40.0)); 0325 CHECK_EVAL("DDB(2400; 300; 10; 1; 2)", Value(480.0)); 0326 CHECK_EVAL("DDB(2400; 300; 10; 2; 1.5)", Value(306)); 0327 CHECK_EVAL("DDB(2400; 300; 10; 10; 2)", Value(22.1225472000002)); 0328 0329 // http://www.vni.com/products/imsl/jmsl/v30/api/com/imsl/finance/ddbEx1.html 0330 CHECK_EVAL("DDB(2500; 500; 24; 1; 2)", Value(208.333333333333)); 0331 CHECK_EVAL("DDB(2500; 500; 24; 2; 2)", Value(190.972222222222)); 0332 CHECK_EVAL("DDB(2500; 500; 24; 3; 2)", Value(175.057870370370)); 0333 CHECK_EVAL("DDB(2500; 500; 24; 4; 2)", Value(160.469714506173)); 0334 CHECK_EVAL("DDB(2500; 500; 24; 5; 2)", Value(147.097238297325)); 0335 CHECK_EVAL("DDB(2500; 500; 24; 6; 2)", Value(134.839135105881)); 0336 CHECK_EVAL("DDB(2500; 500; 24; 7; 2)", Value(123.602540513725)); 0337 CHECK_EVAL("DDB(2500; 500; 24; 8; 2)", Value(113.302328804248)); 0338 CHECK_EVAL("DDB(2500; 500; 24; 9; 2)", Value(103.860468070560)); 0339 CHECK_EVAL("DDB(2500; 500; 24; 10; 2)", Value(95.2054290646802)); 0340 CHECK_EVAL("DDB(2500; 500; 24; 11; 2)", Value(87.2716433092901)); 0341 CHECK_EVAL("DDB(2500; 500; 24; 12; 2)", Value(79.9990063668494)); 0342 CHECK_EVAL("DDB(2500; 500; 24; 13; 2)", Value(73.3324225029452)); 0343 CHECK_EVAL("DDB(2500; 500; 24; 14; 2)", Value(67.2213872943665)); 0344 CHECK_EVAL("DDB(2500; 500; 24; 15; 2)", Value(61.6196050198359)); 0345 CHECK_EVAL("DDB(2500; 500; 24; 16; 2)", Value(56.4846379348497)); 0346 CHECK_EVAL("DDB(2500; 500; 24; 17; 2)", Value(51.7775847736120)); 0347 CHECK_EVAL("DDB(2500; 500; 24; 18; 2)", Value(47.4627860424778)); 0348 CHECK_EVAL("DDB(2500; 500; 24; 19; 2)", Value(22.0906464672553)); 0349 CHECK_EVAL("DDB(2500; 500; 24; 20; 2)", Value(0)); 0350 CHECK_EVAL("DDB(2500; 500; 24; 21; 2)", Value(0)); 0351 CHECK_EVAL("DDB(2500; 500; 24; 22; 2)", Value(0)); 0352 CHECK_EVAL("DDB(2500; 500; 24; 23; 2)", Value(0)); 0353 CHECK_EVAL("DDB(2500; 500; 24; 24; 2)", Value(0)); 0354 0355 // test cases in OpenFormula specification 0356 CHECK_EVAL("DDB(4000; 500; 4; 2; 2)", Value(1000)); 0357 CHECK_EVAL("DDB(4000; 500; 4; 2)", Value(1000)); 0358 CHECK_EVAL("DDB(1100; 100; 5; 5; 2.3 )", Value(0)); 0359 0360 // try default factor (=2) 0361 CHECK_EVAL("DDB(2400; 300; 10*12; 1)", Value(40.0)); 0362 CHECK_EVAL("DDB(2400; 300; 10; 1)", Value(480.0)); 0363 CHECK_EVAL("DDB(2500; 500; 24; 22)", Value(0)); 0364 CHECK_EVAL("DDB(2500; 500; 24; 23)", Value(0)); 0365 CHECK_EVAL("DDB(2500; 500; 24; 24)", Value(0)); 0366 0367 // factor > life 0368 CHECK_EVAL("DDB(2400; 300; 10; 0.8; 20)", Value(2100)); 0369 CHECK_EVAL("DDB(2400; 300; 10; 1.0; 20)", Value(2100)); 0370 CHECK_EVAL("DDB(2400; 300; 10; 1.2; 20)", Value(0)); 0371 0372 // factor is fraction 0373 CHECK_EVAL("DDB(2400; 300; 10; 2; 2.5)", Value(450)); 0374 CHECK_EVAL("DDB(2400; 300; 10; 2; 1.5)", Value(306)); 0375 0376 // period is fraction 0377 CHECK_EVAL("DDB(2400; 300; 10; 6.7; 2)", Value(134.5408487904432)); 0378 CHECK_EVAL("DDB(2400; 300; 10; 7.7; 2)", Value(107.6326790323546)); 0379 } 0380 0381 // DISC 0382 void TestFinancialFunctions::testDISC() 0383 { 0384 // basis | day-count basis 0385 //-------+----------------------------------- 0386 // 0 | US (NASD) 30/360 0387 // 1 | Actual/actual (Euro), also known as AFB 0388 // 2 | Actual/360 0389 // 3 | Actual/365 0390 // 4 | European 30/360 0391 0392 CHECK_EVAL_SHORT("DISC( DATE(2004;02;29); date(2009;01;01); 95000; 100000; 0)", Value(0.010339)); 0393 CHECK_EVAL_SHORT("DISC( DATE(2004;02;29); date(2009;01;01); 95000; 100000; 1)", Value(0.010333)); // NOK (0.010332) 0394 CHECK_EVAL_SHORT("DISC( DATE(2004;02;29); date(2009;01;01); 95000; 100000; 2)", Value(0.010181)); 0395 CHECK_EVAL_SHORT("DISC( DATE(2004;02;29); date(2009;01;01); 95000; 100000; 3)", Value(0.010322)); 0396 CHECK_EVAL_SHORT("DISC( DATE(2004;02;29); date(2009;01;01); 95000; 100000; 4)", Value(0.010333)); 0397 CHECK_EVAL_SHORT("DISC( DATE(2006;01;01); date(2008;01;01); 200; 100; 3)", Value(-0.500000)); 0398 CHECK_EVAL_SHORT("DISC( DATE(2006;01;01); date(2005;07;01); 95000; 100000; 4)", Value(false)); 0399 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETDISC( DATE(2004;02;29); date(2009;01;01); 95000; 100000; 0)", Value(0.010339)); 0400 } 0401 0402 // DOLLARDE 0403 void TestFinancialFunctions::testDOLLARDE() 0404 { 0405 // http://publib.boulder.ibm.com/infocenter/iadthelp/v7r0/index.jsp?topic=/com.businessobjects.integration.eclipse.designer.doc/designer/Functions68.html 0406 CHECK_EVAL_SHORT("DOLLARDE( 1.1 ; 8)" , Value(1.125)); // 0407 CHECK_EVAL_SHORT("DOLLARDE( 2.13;16)" , Value(2.8125)); // 0408 CHECK_EVAL_SHORT("DOLLARDE( 2.45;16)" , Value(4.8125)); // 0409 CHECK_EVAL_SHORT("DOLLARDE( 1.16; 8)" , Value(1.2)); // 0410 0411 // http://www.bettersolutions.com/excel/EDH113/LR849116511.htm 0412 CHECK_EVAL_SHORT("DOLLARDE( 1.1 ; 2)" , Value(1.5)); // 0413 CHECK_EVAL_SHORT("DOLLARDE( 1.25; 5)" , Value(1.5)); // 0414 CHECK_EVAL_SHORT("DOLLARDE( 5.08; 4)" , Value(5.2)); // 0415 CHECK_EVAL_SHORT("DOLLARDE( 5.24; 4)" , Value(5.6)); // 0416 CHECK_EVAL_SHORT("DOLLARDE( 100.24; 4)" , Value(100.6)); // 0417 CHECK_EVAL_SHORT("DOLLARFR(DOLLARDE( 101.2; 4);4)", Value(101.2)); // for- and backward 0418 0419 // ODF 0420 CHECK_EVAL_SHORT("DOLLARDE( 1.1; 4)" , Value(1.25)); // 0421 CHECK_EVAL_SHORT("DOLLARDE( 1.1; 3)" , Value(1.333333)); // 0422 CHECK_EVAL_SHORT("DOLLARDE( -1.1;10)" , Value(-1.1)); // 0423 CHECK_EVAL_SHORT("DOLLARDE( 1.0; 5)" , Value(1)); // 0424 CHECK_EVAL_SHORT("DOLLARDE( 1.1;10)" , Value(1.1)); // 0425 CHECK_EVAL_SHORT("DOLLARDE( 1.1; 0)" , Value::errorVALUE()); // 0426 0427 // alternate function name 0428 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETDOLLARDE(1.1;8)" , Value(1.125)); 0429 } 0430 0431 // DOLLARFR 0432 void TestFinancialFunctions::testDOLLARFR() 0433 { 0434 // my tests 0435 CHECK_EVAL_SHORT("DOLLARFR( 1.1 ; 9)" , Value(1.09)); // 0436 CHECK_EVAL_SHORT("DOLLARFR( 1.1 ; 11)" , Value(1.011)); // 0437 CHECK_EVAL_SHORT("DOLLARFR( 1.1 ; 10)" , Value(1.1)); // 0438 0439 // http://www.bettersolutions.com/excel/EDH113/QR810212321.htm 0440 CHECK_EVAL_SHORT("DOLLARFR( 1.125 ; 8)" , Value(1.1)); // 0441 CHECK_EVAL_SHORT("DOLLARFR( 1.5 ; 2)" , Value(1.1)); // 0442 CHECK_EVAL_SHORT("DOLLARFR( 1.5 ; 8)" , Value(1.4)); // 0443 CHECK_EVAL_SHORT("DOLLARFR( 1.5 ; 5)" , Value(1.25)); // 0444 0445 // ODF 0446 CHECK_EVAL_SHORT("DOLLARFR( 1.1 ;10)" , Value(1.1)); // 0447 CHECK_EVAL_SHORT("DOLLARFR( 1.25; 4)" , Value(1.1)); // 0448 CHECK_EVAL_SHORT("DOLLARFR(-1.33333; 3)" , Value(-1.099999)); // ODF specs error (1.1) must be -1.1 0449 CHECK_EVAL_SHORT("DOLLARFR( 1.0; 5)" , Value(1)); // 0450 CHECK_EVAL_SHORT("DOLLARFR( 1.1; 0)" , Value::errorVALUE()); // 0451 0452 // alternate function name 0453 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETDOLLARFR( 1.1 ; 9)" , Value(1.09)); 0454 } 0455 0456 // DURATION 0457 void TestFinancialFunctions::testDURATION() 0458 { 0459 // Calligra Sheets 0460 CHECK_EVAL("DURATION( 0.1; 1000; 2000 )" , Value(7.2725408973)); // 0461 // alternate function name 0462 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETDURATION( 0.1; 1000; 2000 )" , Value(7.2725408973)); 0463 } 0464 0465 // DURATION_ADD 0466 void TestFinancialFunctions::testDURATION_ADD() 0467 { 0468 CHECK_EVAL("DURATION_ADD( \"1998-01-01\"; \"2006-01-01\"; 0.08; 0.09; 2; 1 )" , Value(5.9937749555)); // 0469 } 0470 0471 // EFFECT 0472 void TestFinancialFunctions::testEFFECT() 0473 { 0474 // Calligra Sheets 0475 CHECK_EVAL_SHORT("EFFECT(0.08;12)", Value(0.083)); 0476 // alternate function name 0477 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETEFFECT(0.08;12)", Value(0.083)); 0478 } 0479 0480 // Euro conversion 0481 // EURO(currency) 0482 void TestFinancialFunctions::testEURO() 0483 { 0484 CHECK_EVAL("EURO(\"ATS\")", Value(13.7603)); 0485 CHECK_EVAL("EURO(\"BEF\")", Value(40.3399)); 0486 CHECK_EVAL("EURO(\"DEM\")", Value(1.95583)); 0487 CHECK_EVAL("EURO(\"ESP\")", Value(166.386)); 0488 CHECK_EVAL("EURO(\"EUR\")", Value(1.0)); 0489 CHECK_EVAL("EURO(\"FIM\")", Value(5.94573)); 0490 CHECK_EVAL("EURO(\"FRF\")", Value(6.55957)); 0491 CHECK_EVAL("EURO(\"GRD\")", Value(340.75)); 0492 CHECK_EVAL("EURO(\"IEP\")", Value(0.787564)); 0493 CHECK_EVAL("EURO(\"ITL\")", Value(1936.27)); 0494 CHECK_EVAL("EURO(\"LUX\")", Value(40.3399)); 0495 CHECK_EVAL("EURO(\"NLG\")", Value(2.20371)); 0496 CHECK_EVAL("EURO(\"PTE\")", Value(200.482)); 0497 0498 // should still work with lowercase 0499 CHECK_EVAL("EURO(\"ats\")", Value(13.7603)); 0500 CHECK_EVAL("EURO(\"bef\")", Value(40.3399)); 0501 CHECK_EVAL("EURO(\"dem\")", Value(1.95583)); 0502 CHECK_EVAL("EURO(\"esp\")", Value(166.386)); 0503 CHECK_EVAL("EURO(\"eur\")", Value(1.0)); 0504 CHECK_EVAL("EURO(\"fim\")", Value(5.94573)); 0505 CHECK_EVAL("EURO(\"frf\")", Value(6.55957)); 0506 CHECK_EVAL("EURO(\"grd\")", Value(340.75)); 0507 CHECK_EVAL("EURO(\"iep\")", Value(0.787564)); 0508 CHECK_EVAL("EURO(\"itl\")", Value(1936.27)); 0509 CHECK_EVAL("EURO(\"lux\")", Value(40.3399)); 0510 CHECK_EVAL("EURO(\"nlg\")", Value(2.20371)); 0511 CHECK_EVAL("EURO(\"pte\")", Value(200.482)); 0512 0513 // should still work with mixed-case 0514 CHECK_EVAL("EURO(\"Ats\")", Value(13.7603)); 0515 CHECK_EVAL("EURO(\"Bef\")", Value(40.3399)); 0516 CHECK_EVAL("EURO(\"Dem\")", Value(1.95583)); 0517 CHECK_EVAL("EURO(\"Esp\")", Value(166.386)); 0518 CHECK_EVAL("EURO(\"Eur\")", Value(1.0)); 0519 CHECK_EVAL("EURO(\"Fim\")", Value(5.94573)); 0520 CHECK_EVAL("EURO(\"Frf\")", Value(6.55957)); 0521 CHECK_EVAL("EURO(\"GrD\")", Value(340.75)); 0522 CHECK_EVAL("EURO(\"IeP\")", Value(0.787564)); 0523 CHECK_EVAL("EURO(\"Itl\")", Value(1936.27)); 0524 CHECK_EVAL("EURO(\"luX\")", Value(40.3399)); 0525 CHECK_EVAL("EURO(\"nlG\")", Value(2.20371)); 0526 CHECK_EVAL("EURO(\"ptE\")", Value(200.482)); 0527 0528 CHECK_EVAL("EURO(\"NOMANSLAND\")", Value::errorNUM()); 0529 } 0530 0531 // Currency conversion using Euro 0532 // EUROCONVERT(number,source,target) 0533 void TestFinancialFunctions::testEUROCONVERT() 0534 { 0535 // 1 Euro to ... 0536 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"ATS\")", Value(13.7603)); 0537 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"BEF\")", Value(40.3399)); 0538 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"DEM\")", Value(1.95583)); 0539 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"ESP\")", Value(166.386)); 0540 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"EUR\")", Value(1.0)); 0541 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"FIM\")", Value(5.94573)); 0542 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"FRF\")", Value(6.55957)); 0543 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"GRD\")", Value(340.75)); 0544 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"IEP\")", Value(0.787564)); 0545 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"ITL\")", Value(1936.27)); 0546 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"LUX\")", Value(40.3399)); 0547 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"NLG\")", Value(2.20371)); 0548 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"PTE\")", Value(200.482)); 0549 0550 // identity 0551 CHECK_EVAL("EUROCONVERT(1;\"BEF\";\"bef\")", Value(1.0)); 0552 CHECK_EVAL("EUROCONVERT(1;\"DEM\";\"dem\")", Value(1.0)); 0553 CHECK_EVAL("EUROCONVERT(1;\"ESP\";\"esp\")", Value(1.0)); 0554 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"eur\")", Value(1.0)); 0555 CHECK_EVAL("EUROCONVERT(1;\"FIM\";\"fim\")", Value(1.0)); 0556 CHECK_EVAL("EUROCONVERT(1;\"FRF\";\"frf\")", Value(1.0)); 0557 CHECK_EVAL("EUROCONVERT(1;\"GRD\";\"grd\")", Value(1.0)); 0558 CHECK_EVAL("EUROCONVERT(1;\"IEP\";\"iep\")", Value(1.0)); 0559 CHECK_EVAL("EUROCONVERT(1;\"ITL\";\"itl\")", Value(1.0)); 0560 CHECK_EVAL("EUROCONVERT(1;\"LUX\";\"lux\")", Value(1.0)); 0561 CHECK_EVAL("EUROCONVERT(1;\"NLG\";\"nlg\")", Value(1.0)); 0562 CHECK_EVAL("EUROCONVERT(1;\"PTE\";\"pte\")", Value(1.0)); 0563 0564 // all other combinations 0565 CHECK_EVAL("EUROCONVERT( 2; \"ATS\"; \"bef\" )", Value(2*40.3399 / 13.7603)); 0566 CHECK_EVAL("EUROCONVERT( 3; \"ATS\"; \"dem\" )", Value(3*1.95583 / 13.7603)); 0567 CHECK_EVAL("EUROCONVERT( 4; \"ATS\"; \"esp\" )", Value(4*166.386 / 13.7603)); 0568 CHECK_EVAL("EUROCONVERT( 5; \"ATS\"; \"eur\" )", Value(5*1 / 13.7603)); 0569 CHECK_EVAL("EUROCONVERT( 6; \"ATS\"; \"fim\" )", Value(6*5.94573 / 13.7603)); 0570 CHECK_EVAL("EUROCONVERT( 7; \"ATS\"; \"frf\" )", Value(7*6.55957 / 13.7603)); 0571 CHECK_EVAL("EUROCONVERT( 8; \"ATS\"; \"grd\" )", Value(8*340.75 / 13.7603)); 0572 CHECK_EVAL("EUROCONVERT( 9; \"ATS\"; \"iep\" )", Value(9*0.787564 / 13.7603)); 0573 CHECK_EVAL("EUROCONVERT( 10; \"ATS\"; \"itl\" )", Value(10*1936.27 / 13.7603)); 0574 CHECK_EVAL("EUROCONVERT( 11; \"ATS\"; \"lux\" )", Value(11*40.3399 / 13.7603)); 0575 CHECK_EVAL("EUROCONVERT( 12; \"ATS\"; \"nlg\" )", Value(12*2.20371 / 13.7603)); 0576 CHECK_EVAL("EUROCONVERT( 13; \"ATS\"; \"pte\" )", Value(13*200.482 / 13.7603)); 0577 CHECK_EVAL("EUROCONVERT( 14; \"BEF\"; \"ats\" )", Value(14*13.7603 / 40.3399)); 0578 CHECK_EVAL("EUROCONVERT( 15; \"BEF\"; \"dem\" )", Value(15*1.95583 / 40.3399)); 0579 CHECK_EVAL("EUROCONVERT( 16; \"BEF\"; \"esp\" )", Value(16*166.386 / 40.3399)); 0580 CHECK_EVAL("EUROCONVERT( 17; \"BEF\"; \"eur\" )", Value(17*1 / 40.3399)); 0581 CHECK_EVAL("EUROCONVERT( 18; \"BEF\"; \"fim\" )", Value(18*5.94573 / 40.3399)); 0582 CHECK_EVAL("EUROCONVERT( 19; \"BEF\"; \"frf\" )", Value(19*6.55957 / 40.3399)); 0583 CHECK_EVAL("EUROCONVERT( 20; \"BEF\"; \"grd\" )", Value(20*340.75 / 40.3399)); 0584 CHECK_EVAL("EUROCONVERT( 21; \"BEF\"; \"iep\" )", Value(21*0.787564 / 40.3399)); 0585 CHECK_EVAL("EUROCONVERT( 22; \"BEF\"; \"itl\" )", Value(22*1936.27 / 40.3399)); 0586 CHECK_EVAL("EUROCONVERT( 23; \"BEF\"; \"lux\" )", Value(23*40.3399 / 40.3399)); 0587 CHECK_EVAL("EUROCONVERT( 24; \"BEF\"; \"nlg\" )", Value(24*2.20371 / 40.3399)); 0588 CHECK_EVAL("EUROCONVERT( 25; \"BEF\"; \"pte\" )", Value(25*200.482 / 40.3399)); 0589 CHECK_EVAL("EUROCONVERT( 26; \"DEM\"; \"ats\" )", Value(26*13.7603 / 1.95583)); 0590 CHECK_EVAL("EUROCONVERT( 27; \"DEM\"; \"bef\" )", Value(27*40.3399 / 1.95583)); 0591 CHECK_EVAL("EUROCONVERT( 28; \"DEM\"; \"esp\" )", Value(28*166.386 / 1.95583)); 0592 CHECK_EVAL("EUROCONVERT( 29; \"DEM\"; \"eur\" )", Value(29*1 / 1.95583)); 0593 CHECK_EVAL("EUROCONVERT( 30; \"DEM\"; \"fim\" )", Value(30*5.94573 / 1.95583)); 0594 CHECK_EVAL("EUROCONVERT( 31; \"DEM\"; \"frf\" )", Value(31*6.55957 / 1.95583)); 0595 CHECK_EVAL("EUROCONVERT( 32; \"DEM\"; \"grd\" )", Value(32*340.75 / 1.95583)); 0596 CHECK_EVAL("EUROCONVERT( 33; \"DEM\"; \"iep\" )", Value(33*0.787564 / 1.95583)); 0597 CHECK_EVAL("EUROCONVERT( 34; \"DEM\"; \"itl\" )", Value(34*1936.27 / 1.95583)); 0598 CHECK_EVAL("EUROCONVERT( 35; \"DEM\"; \"lux\" )", Value(35*40.3399 / 1.95583)); 0599 CHECK_EVAL("EUROCONVERT( 36; \"DEM\"; \"nlg\" )", Value(36*2.20371 / 1.95583)); 0600 CHECK_EVAL("EUROCONVERT( 37; \"DEM\"; \"pte\" )", Value(37*200.482 / 1.95583)); 0601 CHECK_EVAL("EUROCONVERT( 38; \"ESP\"; \"ats\" )", Value(38*13.7603 / 166.386)); 0602 CHECK_EVAL("EUROCONVERT( 39; \"ESP\"; \"bef\" )", Value(39*40.3399 / 166.386)); 0603 CHECK_EVAL("EUROCONVERT( 40; \"ESP\"; \"dem\" )", Value(40*1.95583 / 166.386)); 0604 CHECK_EVAL("EUROCONVERT( 41; \"ESP\"; \"eur\" )", Value(41*1 / 166.386)); 0605 CHECK_EVAL("EUROCONVERT( 42; \"ESP\"; \"fim\" )", Value(42*5.94573 / 166.386)); 0606 CHECK_EVAL("EUROCONVERT( 43; \"ESP\"; \"frf\" )", Value(43*6.55957 / 166.386)); 0607 CHECK_EVAL("EUROCONVERT( 44; \"ESP\"; \"grd\" )", Value(44*340.75 / 166.386)); 0608 CHECK_EVAL("EUROCONVERT( 45; \"ESP\"; \"iep\" )", Value(45*0.787564 / 166.386)); 0609 CHECK_EVAL("EUROCONVERT( 46; \"ESP\"; \"itl\" )", Value(46*1936.27 / 166.386)); 0610 CHECK_EVAL("EUROCONVERT( 47; \"ESP\"; \"lux\" )", Value(47*40.3399 / 166.386)); 0611 CHECK_EVAL("EUROCONVERT( 48; \"ESP\"; \"nlg\" )", Value(48*2.20371 / 166.386)); 0612 CHECK_EVAL("EUROCONVERT( 49; \"ESP\"; \"pte\" )", Value(49*200.482 / 166.386)); 0613 CHECK_EVAL("EUROCONVERT( 50; \"EUR\"; \"ats\" )", Value(50*13.7603 / 1)); 0614 CHECK_EVAL("EUROCONVERT( 51; \"EUR\"; \"bef\" )", Value(51*40.3399 / 1)); 0615 CHECK_EVAL("EUROCONVERT( 52; \"EUR\"; \"dem\" )", Value(52*1.95583 / 1)); 0616 CHECK_EVAL("EUROCONVERT( 53; \"EUR\"; \"esp\" )", Value(53*166.386 / 1)); 0617 CHECK_EVAL("EUROCONVERT( 54; \"EUR\"; \"fim\" )", Value(54*5.94573 / 1)); 0618 CHECK_EVAL("EUROCONVERT( 55; \"EUR\"; \"frf\" )", Value(55*6.55957 / 1)); 0619 CHECK_EVAL("EUROCONVERT( 56; \"EUR\"; \"grd\" )", Value(56*340.75 / 1)); 0620 CHECK_EVAL("EUROCONVERT( 57; \"EUR\"; \"iep\" )", Value(57*0.787564 / 1)); 0621 CHECK_EVAL("EUROCONVERT( 58; \"EUR\"; \"itl\" )", Value(58*1936.27 / 1)); 0622 CHECK_EVAL("EUROCONVERT( 59; \"EUR\"; \"lux\" )", Value(59*40.3399 / 1)); 0623 CHECK_EVAL("EUROCONVERT( 60; \"EUR\"; \"nlg\" )", Value(60*2.20371 / 1)); 0624 CHECK_EVAL("EUROCONVERT( 61; \"EUR\"; \"pte\" )", Value(61*200.482 / 1)); 0625 CHECK_EVAL("EUROCONVERT( 62; \"FIM\"; \"ats\" )", Value(62*13.7603 / 5.94573)); 0626 CHECK_EVAL("EUROCONVERT( 63; \"FIM\"; \"bef\" )", Value(63*40.3399 / 5.94573)); 0627 CHECK_EVAL("EUROCONVERT( 64; \"FIM\"; \"dem\" )", Value(64*1.95583 / 5.94573)); 0628 CHECK_EVAL("EUROCONVERT( 65; \"FIM\"; \"esp\" )", Value(65*166.386 / 5.94573)); 0629 CHECK_EVAL("EUROCONVERT( 66; \"FIM\"; \"eur\" )", Value(66*1 / 5.94573)); 0630 CHECK_EVAL("EUROCONVERT( 67; \"FIM\"; \"frf\" )", Value(67*6.55957 / 5.94573)); 0631 CHECK_EVAL("EUROCONVERT( 68; \"FIM\"; \"grd\" )", Value(68*340.75 / 5.94573)); 0632 CHECK_EVAL("EUROCONVERT( 69; \"FIM\"; \"iep\" )", Value(69*0.787564 / 5.94573)); 0633 CHECK_EVAL("EUROCONVERT( 70; \"FIM\"; \"itl\" )", Value(70*1936.27 / 5.94573)); 0634 CHECK_EVAL("EUROCONVERT( 71; \"FIM\"; \"lux\" )", Value(71*40.3399 / 5.94573)); 0635 CHECK_EVAL("EUROCONVERT( 72; \"FIM\"; \"nlg\" )", Value(72*2.20371 / 5.94573)); 0636 CHECK_EVAL("EUROCONVERT( 73; \"FIM\"; \"pte\" )", Value(73*200.482 / 5.94573)); 0637 CHECK_EVAL("EUROCONVERT( 74; \"FRF\"; \"ats\" )", Value(74*13.7603 / 6.55957)); 0638 CHECK_EVAL("EUROCONVERT( 75; \"FRF\"; \"bef\" )", Value(75*40.3399 / 6.55957)); 0639 CHECK_EVAL("EUROCONVERT( 76; \"FRF\"; \"dem\" )", Value(76*1.95583 / 6.55957)); 0640 CHECK_EVAL("EUROCONVERT( 77; \"FRF\"; \"esp\" )", Value(77*166.386 / 6.55957)); 0641 CHECK_EVAL("EUROCONVERT( 78; \"FRF\"; \"eur\" )", Value(78*1 / 6.55957)); 0642 CHECK_EVAL("EUROCONVERT( 79; \"FRF\"; \"fim\" )", Value(79*5.94573 / 6.55957)); 0643 CHECK_EVAL("EUROCONVERT( 80; \"FRF\"; \"grd\" )", Value(80*340.75 / 6.55957)); 0644 CHECK_EVAL("EUROCONVERT( 81; \"FRF\"; \"iep\" )", Value(81*0.787564 / 6.55957)); 0645 CHECK_EVAL("EUROCONVERT( 82; \"FRF\"; \"itl\" )", Value(82*1936.27 / 6.55957)); 0646 CHECK_EVAL("EUROCONVERT( 83; \"FRF\"; \"lux\" )", Value(83*40.3399 / 6.55957)); 0647 CHECK_EVAL("EUROCONVERT( 84; \"FRF\"; \"nlg\" )", Value(84*2.20371 / 6.55957)); 0648 CHECK_EVAL("EUROCONVERT( 85; \"FRF\"; \"pte\" )", Value(85*200.482 / 6.55957)); 0649 CHECK_EVAL("EUROCONVERT( 86; \"GRD\"; \"ats\" )", Value(86*13.7603 / 340.75)); 0650 CHECK_EVAL("EUROCONVERT( 87; \"GRD\"; \"bef\" )", Value(87*40.3399 / 340.75)); 0651 CHECK_EVAL("EUROCONVERT( 88; \"GRD\"; \"dem\" )", Value(88*1.95583 / 340.75)); 0652 CHECK_EVAL("EUROCONVERT( 89; \"GRD\"; \"esp\" )", Value(89*166.386 / 340.75)); 0653 CHECK_EVAL("EUROCONVERT( 90; \"GRD\"; \"eur\" )", Value(90*1 / 340.75)); 0654 CHECK_EVAL("EUROCONVERT( 91; \"GRD\"; \"fim\" )", Value(91*5.94573 / 340.75)); 0655 CHECK_EVAL("EUROCONVERT( 92; \"GRD\"; \"frf\" )", Value(92*6.55957 / 340.75)); 0656 CHECK_EVAL("EUROCONVERT( 93; \"GRD\"; \"iep\" )", Value(93*0.787564 / 340.75)); 0657 CHECK_EVAL("EUROCONVERT( 94; \"GRD\"; \"itl\" )", Value(94*1936.27 / 340.75)); 0658 CHECK_EVAL("EUROCONVERT( 95; \"GRD\"; \"lux\" )", Value(95*40.3399 / 340.75)); 0659 CHECK_EVAL("EUROCONVERT( 96; \"GRD\"; \"nlg\" )", Value(96*2.20371 / 340.75)); 0660 CHECK_EVAL("EUROCONVERT( 97; \"GRD\"; \"pte\" )", Value(97*200.482 / 340.75)); 0661 CHECK_EVAL("EUROCONVERT( 98; \"IEP\"; \"ats\" )", Value(98*13.7603 / 0.787564)); 0662 CHECK_EVAL("EUROCONVERT( 99; \"IEP\"; \"bef\" )", Value(99*40.3399 / 0.787564)); 0663 CHECK_EVAL("EUROCONVERT( 100; \"IEP\"; \"dem\" )", Value(100*1.95583 / 0.787564)); 0664 CHECK_EVAL("EUROCONVERT( 101; \"IEP\"; \"esp\" )", Value(101*166.386 / 0.787564)); 0665 CHECK_EVAL("EUROCONVERT( 102; \"IEP\"; \"eur\" )", Value(102*1 / 0.787564)); 0666 CHECK_EVAL("EUROCONVERT( 103; \"IEP\"; \"fim\" )", Value(103*5.94573 / 0.787564)); 0667 CHECK_EVAL("EUROCONVERT( 104; \"IEP\"; \"frf\" )", Value(104*6.55957 / 0.787564)); 0668 CHECK_EVAL("EUROCONVERT( 105; \"IEP\"; \"grd\" )", Value(105*340.75 / 0.787564)); 0669 CHECK_EVAL("EUROCONVERT( 106; \"IEP\"; \"itl\" )", Value(106*1936.27 / 0.787564)); 0670 CHECK_EVAL("EUROCONVERT( 107; \"IEP\"; \"lux\" )", Value(107*40.3399 / 0.787564)); 0671 CHECK_EVAL("EUROCONVERT( 108; \"IEP\"; \"nlg\" )", Value(108*2.20371 / 0.787564)); 0672 CHECK_EVAL("EUROCONVERT( 109; \"IEP\"; \"pte\" )", Value(109*200.482 / 0.787564)); 0673 CHECK_EVAL("EUROCONVERT( 110; \"ITL\"; \"ats\" )", Value(110*13.7603 / 1936.27)); 0674 CHECK_EVAL("EUROCONVERT( 111; \"ITL\"; \"bef\" )", Value(111*40.3399 / 1936.27)); 0675 CHECK_EVAL("EUROCONVERT( 112; \"ITL\"; \"dem\" )", Value(112*1.95583 / 1936.27)); 0676 CHECK_EVAL("EUROCONVERT( 113; \"ITL\"; \"esp\" )", Value(113*166.386 / 1936.27)); 0677 CHECK_EVAL("EUROCONVERT( 114; \"ITL\"; \"eur\" )", Value(114*1 / 1936.27)); 0678 CHECK_EVAL("EUROCONVERT( 115; \"ITL\"; \"fim\" )", Value(115*5.94573 / 1936.27)); 0679 CHECK_EVAL("EUROCONVERT( 116; \"ITL\"; \"frf\" )", Value(116*6.55957 / 1936.27)); 0680 CHECK_EVAL("EUROCONVERT( 117; \"ITL\"; \"grd\" )", Value(117*340.75 / 1936.27)); 0681 CHECK_EVAL("EUROCONVERT( 118; \"ITL\"; \"iep\" )", Value(118*0.787564 / 1936.27)); 0682 CHECK_EVAL("EUROCONVERT( 119; \"ITL\"; \"lux\" )", Value(119*40.3399 / 1936.27)); 0683 CHECK_EVAL("EUROCONVERT( 120; \"ITL\"; \"nlg\" )", Value(120*2.20371 / 1936.27)); 0684 CHECK_EVAL("EUROCONVERT( 121; \"ITL\"; \"pte\" )", Value(121*200.482 / 1936.27)); 0685 CHECK_EVAL("EUROCONVERT( 122; \"LUX\"; \"ats\" )", Value(122*13.7603 / 40.3399)); 0686 CHECK_EVAL("EUROCONVERT( 123; \"LUX\"; \"bef\" )", Value(123*40.3399 / 40.3399)); 0687 CHECK_EVAL("EUROCONVERT( 124; \"LUX\"; \"dem\" )", Value(124*1.95583 / 40.3399)); 0688 CHECK_EVAL("EUROCONVERT( 125; \"LUX\"; \"esp\" )", Value(125*166.386 / 40.3399)); 0689 CHECK_EVAL("EUROCONVERT( 126; \"LUX\"; \"eur\" )", Value(126*1 / 40.3399)); 0690 CHECK_EVAL("EUROCONVERT( 127; \"LUX\"; \"fim\" )", Value(127*5.94573 / 40.3399)); 0691 CHECK_EVAL("EUROCONVERT( 128; \"LUX\"; \"frf\" )", Value(128*6.55957 / 40.3399)); 0692 CHECK_EVAL("EUROCONVERT( 129; \"LUX\"; \"grd\" )", Value(129*340.75 / 40.3399)); 0693 CHECK_EVAL("EUROCONVERT( 130; \"LUX\"; \"iep\" )", Value(130*0.787564 / 40.3399)); 0694 CHECK_EVAL("EUROCONVERT( 131; \"LUX\"; \"itl\" )", Value(131*1936.27 / 40.3399)); 0695 CHECK_EVAL("EUROCONVERT( 132; \"LUX\"; \"nlg\" )", Value(132*2.20371 / 40.3399)); 0696 CHECK_EVAL("EUROCONVERT( 133; \"LUX\"; \"pte\" )", Value(133*200.482 / 40.3399)); 0697 CHECK_EVAL("EUROCONVERT( 134; \"NLG\"; \"ats\" )", Value(134*13.7603 / 2.20371)); 0698 CHECK_EVAL("EUROCONVERT( 135; \"NLG\"; \"bef\" )", Value(135*40.3399 / 2.20371)); 0699 CHECK_EVAL("EUROCONVERT( 136; \"NLG\"; \"dem\" )", Value(136*1.95583 / 2.20371)); 0700 CHECK_EVAL("EUROCONVERT( 137; \"NLG\"; \"esp\" )", Value(137*166.386 / 2.20371)); 0701 CHECK_EVAL("EUROCONVERT( 138; \"NLG\"; \"eur\" )", Value(138*1 / 2.20371)); 0702 CHECK_EVAL("EUROCONVERT( 139; \"NLG\"; \"fim\" )", Value(139*5.94573 / 2.20371)); 0703 CHECK_EVAL("EUROCONVERT( 140; \"NLG\"; \"frf\" )", Value(140*6.55957 / 2.20371)); 0704 CHECK_EVAL("EUROCONVERT( 141; \"NLG\"; \"grd\" )", Value(141*340.75 / 2.20371)); 0705 CHECK_EVAL("EUROCONVERT( 142; \"NLG\"; \"iep\" )", Value(142*0.787564 / 2.20371)); 0706 CHECK_EVAL("EUROCONVERT( 143; \"NLG\"; \"itl\" )", Value(143*1936.27 / 2.20371)); 0707 CHECK_EVAL("EUROCONVERT( 144; \"NLG\"; \"lux\" )", Value(144*40.3399 / 2.20371)); 0708 CHECK_EVAL("EUROCONVERT( 145; \"NLG\"; \"pte\" )", Value(145*200.482 / 2.20371)); 0709 CHECK_EVAL("EUROCONVERT( 146; \"PTE\"; \"ats\" )", Value(146*13.7603 / 200.482)); 0710 CHECK_EVAL("EUROCONVERT( 147; \"PTE\"; \"bef\" )", Value(147*40.3399 / 200.482)); 0711 CHECK_EVAL("EUROCONVERT( 148; \"PTE\"; \"dem\" )", Value(148*1.95583 / 200.482)); 0712 CHECK_EVAL("EUROCONVERT( 149; \"PTE\"; \"esp\" )", Value(149*166.386 / 200.482)); 0713 CHECK_EVAL("EUROCONVERT( 150; \"PTE\"; \"eur\" )", Value(150*1 / 200.482)); 0714 CHECK_EVAL("EUROCONVERT( 151; \"PTE\"; \"fim\" )", Value(151*5.94573 / 200.482)); 0715 CHECK_EVAL("EUROCONVERT( 152; \"PTE\"; \"frf\" )", Value(152*6.55957 / 200.482)); 0716 CHECK_EVAL("EUROCONVERT( 153; \"PTE\"; \"grd\" )", Value(153*340.75 / 200.482)); 0717 CHECK_EVAL("EUROCONVERT( 154; \"PTE\"; \"iep\" )", Value(154*0.787564 / 200.482)); 0718 CHECK_EVAL("EUROCONVERT( 155; \"PTE\"; \"itl\" )", Value(155*1936.27 / 200.482)); 0719 CHECK_EVAL("EUROCONVERT( 156; \"PTE\"; \"lux\" )", Value(156*40.3399 / 200.482)); 0720 CHECK_EVAL("EUROCONVERT( 157; \"PTE\"; \"nlg\" )", Value(157*2.20371 / 200.482)); 0721 } 0722 0723 // FV 0724 void TestFinancialFunctions::testFV() 0725 { 0726 // ODF 0727 CHECK_EVAL("FV(10%;12;-100;100)" , Value(1824.5855390489)); // A trivial example of FV. 0728 } 0729 0730 // FVSCHEDULE 0731 void TestFinancialFunctions::testFVSCHEDULE() 0732 { 0733 // ODF 0734 CHECK_EVAL_SHORT("FVSCHEDULE(1000000; {0.03; 0.04; 0.05})" , Value(1124760)); // A trivial example of FVSCHEDULE. 0735 // alternate function name 0736 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETFVSCHEDULE(1000000; {0.03; 0.04; 0.05})" , Value(1124760)); 0737 } 0738 0739 // INTRATE 0740 void TestFinancialFunctions::testINTRATE() 0741 { 0742 // ODF 0743 CHECK_EVAL_SHORT("INTRATE( DATE(2002; 6;8); DATE(1995;10;5); 100000; 200000; 0 )" , Value::errorVALUE()); // Settlement date must be before the maturity date. 0744 CHECK_EVAL_SHORT("INTRATE( DATE(2002; 6;8); DATE(2002; 6;8); 100000; 200000; 0 )" , Value::errorVALUE()); // Settlement date must be before the maturity date. 0745 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 50)" , Value::errorVALUE()); // Unknown Basis returns Error. 0746 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 0 )" , Value(0.1498127341)); // An example of INTRATE. 0747 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000 )" , Value(0.1498127341)); // Basis defaults to 0. 0748 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 1 )" , Value(0.1497128794)); // 0749 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 2 )" , Value(0.1476620180)); // 0750 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 3 )" , Value(0.1497128794)); // 0751 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 4 )" , Value(0.1498127341)); // 0752 // alternate function name 0753 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETINTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 0 )" , Value(0.1498127341)); 0754 } 0755 0756 // IPMT 0757 void TestFinancialFunctions::testIPMT() 0758 { 0759 // ODF 0760 CHECK_EVAL_SHORT("IPMT(5%/12;10;360;100000)", Value(-412.0850243)); // An example of IPMT. The interest payment on a 100000 unit loan 0761 // in the 10th month of a 30 year loan at 5% annual interest. 0762 CHECK_EVAL_SHORT("IPMT(5%/12;10;360;100000;0;1)", Value(-410.3751278993)); // Payments at the beginning of each period. 0763 0764 // The total payment is the principle plus the interest. 0765 CHECK_EVAL_SHORT("PPMT(5%/12;10;360;100000)+IPMT(5%/12;10;360;100000)-PMT(5%/12;360;100000)", Value(0)); 0766 } 0767 0768 // ISPMT 0769 void TestFinancialFunctions::testISPMT() 0770 { 0771 // betersolutions 0772 CHECK_EVAL("ISPMT(10%/12;1 ;36;8000000)", Value(-64814.8148148148)); // 0773 CHECK_EVAL("ISPMT(10% ;1 ;3 ;8000000)", Value(-533333.3333333333)); // 0774 0775 // ODF 0776 CHECK_EVAL("ISPMT(5%/12;12;360;100000)", Value(-402.7777777778)); // A trivial example of ISPMT. A 100000 unit investment with an 0777 // annual interest rate of 5% and a 30 year term has an interest payment 0778 // of 402.78 units in month 12. 0779 } 0780 0781 // Level-coupon bond 0782 // LEVEL_COUPON(faceValue; couponRate; couponsPerYear; years; marketRate) 0783 void TestFinancialFunctions::testLEVELCOUPON() 0784 { 0785 CHECK_EVAL("LEVEL_COUPON(1000; .13; 1; 4; .1)", Value(1095.0959633904788)); 0786 CHECK_EVAL("LEVEL_COUPON(1000; .13; 2; 4; .1)", Value(1096.9481913913939)); 0787 CHECK_EVAL("LEVEL_COUPON(1000; .10; 1; 10; .25)", Value(464.4245094400000)); 0788 CHECK_EVAL("LEVEL_COUPON(1000; .12; 1; 10; .25)", Value(535.8345748480000)); 0789 CHECK_EVAL("LEVEL_COUPON(1000; .20; 1; 10; .25)", Value(821.4748364800000)); 0790 } 0791 0792 // MDURATION 0793 void TestFinancialFunctions::testMDURATION() 0794 { 0795 CHECK_EVAL("MDURATION(\"2004-02-01\"; \"2004-05-31\"; 0.08; 0.09; 2; 0)" , Value(0.3189792663)); // These tests go over a leap year day, 0796 // and intentionally end on May 31, which 0797 // illustrates the differences between 0798 // many bases 0799 // alternate function name 0800 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETMDURATION(\"2004-02-01\"; \"2004-05-31\"; 0.08; 0.09; 2; 0)" , Value(0.3189792663)); 0801 } 0802 0803 // MIRR 0804 void TestFinancialFunctions::testMIRR() 0805 { 0806 // ODF 0807 CHECK_EVAL("MIRR({100;200;-50;300;-200}; 5%; 6%)", Value(0.342823387842)); 0808 0809 // bettersolutions.com 0810 CHECK_EVAL("MIRR({-10;30;20;10;20};0.1;0.12)", Value(0.7712844619)); 0811 CHECK_EVAL("MIRR({-100;30;30;30;30};0.1;1)", Value(0.4564753151)); 0812 CHECK_EVAL("MIRR({-50;20;40;70};10/100;12/100)", Value(0.4090837902)); 0813 CHECK_EVAL("MIRR({-5;1;2;3;4};10/100;0.12)", Value(0.2253901556)); 0814 CHECK_EVAL("MIRR({1000;1100;1200;1500;1600};10%;12%)", Value(Value::errorDIV0())); 0815 } 0816 0817 // Yearly nominal interest rate 0818 // NOMINAL(effectiveRate, periods) 0819 void TestFinancialFunctions::testNOMINAL() 0820 { 0821 CHECK_EVAL("NOMINAL(13.5%; 12)", Value(0.1273031669590416)); 0822 CHECK_EVAL("NOMINAL(13.5%; 12)", Value(0.1273031669590416)); 0823 CHECK_EVAL("NOMINAL(25%; 12)", Value(0.2252311814580734)); 0824 CHECK_EVAL("NOMINAL(25%; 4)", Value(0.2294850537622564)); 0825 CHECK_EVAL("NOMINAL(20%; 12)", Value(0.1837136459967743)); 0826 CHECK_EVAL("NOMINAL(10%; 12)", Value(0.0956896851468452)); 0827 0828 // rate must be positive 0829 CHECK_EVAL("NOMINAL(0; 12)", Value::errorVALUE()); 0830 0831 // periods must be positive 0832 CHECK_EVAL("NOMINAL(10%; 0)", Value::errorDIV0()); 0833 CHECK_EVAL("NOMINAL(10%; -1)", Value::errorVALUE()); 0834 CHECK_EVAL("NOMINAL(10%; -2)", Value::errorVALUE()); 0835 0836 // test cases in OpenFormula specification 0837 CHECK_EVAL("NOMINAL(8%;4)", Value(0.0777061876330940)); 0838 CHECK_EVAL("NOMINAL(12.5%;12)", Value(0.118362966638538)); 0839 CHECK_EVAL("NOMINAL(1%;2)", Value(0.00997512422417790)); 0840 0841 // alternate function name 0842 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETNOMINAL(8%;4)", Value(0.0777061876330940)); 0843 } 0844 0845 // NPER 0846 void TestFinancialFunctions::testNPER() 0847 { 0848 // ODF 0849 CHECK_EVAL_SHORT("NPER(5% ;-100;1000)", Value(14.2067)); // A trivial example of NPER. 0850 CHECK_EVAL_SHORT("NPER(5% ;-100;1000;100)", Value(15.2067)); // A trivial example of NPER with non-zero FV. 0851 CHECK_EVAL_SHORT("NPER(5% ;-100;1000;100;1)", Value(14.2067)); // A trivial example of NPER with non-zero FV and PayType. 0852 CHECK_EVAL_SHORT("NPER(0 ;-100;1000)", Value(10.0000)); // TODO Rate can be zero. 0853 CHECK_EVAL_SHORT("NPER(-1%;-100;1000)", Value(9.483283066)); // TODO Rate can be negative. 0854 } 0855 0856 // Net present value 0857 // NPV(rate, values) 0858 void TestFinancialFunctions::testNPV() 0859 { 0860 CHECK_EVAL("NPV(100%; 4; 5; 7)", Value(4.125)); 0861 CHECK_EVAL("NPV(10%; 100; 200)", Value(256.198347107438)); 0862 } 0863 0864 // ODDLPRICE 0865 void TestFinancialFunctions::testODDLPRICE() 0866 { 0867 // ODF tests. Not all tests pass, but I'm not sure if the spec or the implementation is wrong 0868 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;5%;100;2)", Value( 90.9975570033 ) ); // 0869 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;5%;100;1;0)", Value( 90.9975570033 ) ); // f=1, b=0 0870 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;5%;100;2;0)", Value( 90.9975570033 ) ); // f=2, b=0 0871 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;5%;100;4;0)", Value( 90.9975570033 ) ); // f=4, b=0 0872 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;1.5%;100;1;1)", Value( 102.5120875338 ) ); // f=1, b=1 0873 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;1.5%;100;2;1)", Value( 102.510143853 ) ); // f=2, b=1 0874 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;1.5%;100;4;1)", Value( 102.509884509 ) ); // f=4, b=1 0875 QEXPECT_FAIL("", "Wrong?", Continue); 0876 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;1.5%;100;1;2)", Value( 102.512087534 ) ); // f=1, b=2 0877 QEXPECT_FAIL("", "Wrong?", Continue); 0878 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;1.5%;100;2;2)", Value( 102.510143853 ) ); // f=2, b=2 0879 QEXPECT_FAIL("", "Wrong?", Continue); 0880 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;1.5%;100;4;2)", Value( 102.509884509 ) ); // f=4, b=2 0881 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;5%;1000;1;3)", Value( 794.575995564 ) ); // f=1, b=3 0882 QEXPECT_FAIL("", "Wrong?", Continue); 0883 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;5%;1000;2;3)", Value( 794.671729071 ) ); // f=2, b=3 0884 QEXPECT_FAIL("", "Wrong?", Continue); 0885 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;5%;1000;4;3)", Value( 794.684531308 ) ); // f=4, b=3 0886 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;1.5%;1000;1;4)", Value( 932.992137337 ) ); // f=1, b=4 0887 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;1.5%;1000;2;4)", Value( 932.992137337 ) ); // f=2, b=4 0888 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;1.5%;1000;4;4)", Value( 932.992137337 ) ); // f=4, b=4 0889 } 0890 0891 // ODDLYIELD 0892 void TestFinancialFunctions::testODDLYIELD() 0893 { 0894 // ODF tests 0895 0896 // Basis 0 0897 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;91;100 ;2 )", Value( 4.997775351/100.0 ) ); // Without Basis parameter 0898 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;91;100 ;1;0)", Value( 4.997775351/100.0 ) ); // With Frequency=1 and Basis=0 0899 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;91;100 ;2;0)", Value( 4.997775351/100.0 ) ); // With Frequency=2 and Basis=0 0900 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;91;100 ;4;0)", Value( 4.997775351/100.0 ) ); // With Frequency=4 and Basis=0 0901 0902 // Basis 1 0903 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;103;100 ;1;1)", Value( 1.408788601/100.0 ) ); // With Frequency=1 and Basis=1 0904 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;103;100 ;2;1)", Value( 1.408379719/100.0 ) ); // With Frequency=2 and Basis=1 0905 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;103;100 ;4;1)", Value( 1.408325114/100.0 ) ); // With Frequency=4 and Basis=1 0906 0907 // Basis 2 0908 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;103;100;1;2)", Value( 1.408788601/100.0 ) ); // With Frequency=1 and Basis=2 0909 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;103;100 ;4;2)", Value( 1.408379719/100.0 ) ); // With Frequency=2 and Basis=2 0910 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;103;100 ;2;2)", Value( 1.408325114/100.0 ) ); // With Frequency=4 and Basis=2 0911 0912 // Basis 3 0913 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;795;1000;1;3)", Value( 4.987800402/100.0 ) ); // With Frequency=1 and Basis=3 0914 QEXPECT_FAIL("", "Wrong?", Continue); 0915 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;795;1000;2;3)", Value( 4.990550494/100.0 ) ); // With Frequency=2 and Basis=3 0916 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;795;1000;4;3)", Value( 4.990918451/100.0 ) ); // With Frequency=4 and Basis=3 0917 0918 // Basis 4 0919 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;933;1000;1;4)", Value( 1.499836493/100.0 ) ); // With Frequency=1 and Basis=4 0920 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;933;1000;2;4)", Value( 1.499836493/100.0 ) ); // With Frequency=2 and Basis=4 0921 CHECK_EVAL_SHORT( "ODDLYIELD(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);2%;933;1000;4;4)", Value( 1.499836493/100.0 ) ); // With Frequency=4 and Basis=4 0922 } 0923 0924 // PDURATION 0925 void TestFinancialFunctions::testPDURATION() 0926 { 0927 // is DURATION in Calligra Sheets 0928 CHECK_EVAL_SHORT("PDURATION( 0.1; 10; 100 )" , Value(24.158858)); // simple use case 0929 CHECK_EVAL_SHORT("PDURATION( 0.1; 100; 10 )" , Value(-24.158858)); // currentValue > desiredValue 0930 CHECK_EVAL_SHORT("PDURATION( 0; 10; 11 )" , Value::errorVALUE()); // rate > 0 0931 CHECK_EVAL_SHORT("PDURATION( 0.1; 0; 11 )" , Value::errorVALUE()); // currentValue > 0 0932 CHECK_EVAL_SHORT("PDURATION( 0.1; 10; 0 )" , Value::errorVALUE()); // desiredValue > 0 0933 } 0934 0935 // PMT 0936 void TestFinancialFunctions::testPMT() 0937 { 0938 // ODF 0939 CHECK_EVAL_SHORT("PMT(5%;12;1000)", Value(-112.8254100208)); // A trivial example of PMT. 0940 CHECK_EVAL_SHORT("PMT(5%;12;1000;100)", Value(-119.1079510229)); // A trivial example of PMT with non-zero FV. 0941 CHECK_EVAL_SHORT("PMT(5%;12;1000;100;1)", Value(-113.4361438313)); // A trivial example of PMT with non-zero FV and PayType. 0942 CHECK_EVAL_SHORT("PMT(0;10;1000)", Value(-100.00000)); // TODO Rate can be zero. 0943 } 0944 0945 // PPMT 0946 void TestFinancialFunctions::testPPMT() 0947 { 0948 // bettersolution.com 0949 CHECK_EVAL("PPMT(10%/12;1;24;2000)", Value(-75.6231860084)); // A simple test case 0950 CHECK_EVAL("PPMT(8%;10;10;200000)", Value(-27598.0534624214)); // A simple test case 0951 0952 // ODF 0953 CHECK_EVAL("PPMT(3%;1;12;100)", Value(-7.0462085473)); // A simple test case 0954 CHECK_EVAL("PPMT(8%;5;24;10000;0)", Value(-203.7735140493)); // With nPer=5 and Future=0 0955 CHECK_EVAL("PPMT(8%;10;24;10000;2000)", Value(-359.2921746011)); // With nPer=10 and Future=2000 0956 CHECK_EVAL("PPMT(8%;10;24;10000;2000;1)", Value(-332.6779394454)); // With Type=1 0957 0958 // these tests seems to be wrong in specs. remove superfluous parameter "1". 0959 CHECK_EVAL("PPMT(3%;1;12;100;200)", Value(-21.1386256419)); // With future value 0960 CHECK_EVAL("PPMT(3%;1;12;100;200;1)", Value(-20.5229375164)); // With future value and type 0961 } 0962 0963 // PRICEMAT 0964 void TestFinancialFunctions::testPRICEMAT() 0965 { 0966 // ODF - TODO expand to 10 signif. 0967 CHECK_EVAL_SHORT("PRICEMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);6%;5% )", Value(103.819218241)); // Without Basis parameter 0968 CHECK_EVAL_SHORT("PRICEMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);6%;5%;1)", Value(103.824693325)); // With Basis=1 specs 103.824693325 0969 CHECK_EVAL_SHORT("PRICEMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);6%;5%;2)", Value(103.858482159)); // With Basis=2 0970 CHECK_EVAL_SHORT("PRICEMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);6%;5%;3)", Value(103.824693325)); // With Basis=3 0971 CHECK_EVAL_SHORT("PRICEMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);6%;5%;4)", Value(103.819218241)); // With Basis=4 0972 CHECK_EVAL_SHORT("PRICEMAT(DATE(1990;6;1);DATE(1992;12;31);DATE(1990;1;1);3%;2%;0)", Value(102.395007924)); // 0973 CHECK_EVAL_SHORT("PRICEMAT(DATE(1990;6;1);DATE(1992;12;31);DATE(1990;1;1);5%;3%;2)", Value(104.709020052)); // 0974 // alternate function name 0975 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETPRICEMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);6%;5% )", Value(103.819218241)); 0976 } 0977 0978 // PV 0979 void TestFinancialFunctions::testPV() 0980 { 0981 // TODO check type > 1, check div0 0982 // ODF 0983 CHECK_EVAL_SHORT("PV(10%;12;-100;100)", Value(649.5061005186)); // A trivial example of PV. 0984 } 0985 0986 // PV_ANNUITY 0987 void TestFinancialFunctions::testPV_ANNUITY() 0988 { 0989 // Calligra Sheets 0990 CHECK_EVAL_SHORT("PV_ANNUITY(1000;0.05;5)", Value(4329.47667063)); 0991 } 0992 0993 // RATE 0994 void TestFinancialFunctions::testRATE() 0995 { 0996 CHECK_EVAL_SHORT("RATE(4*12;-200;8000)", Value(0.00770147)); 0997 } 0998 0999 // RECEIVED 1000 void TestFinancialFunctions::testRECEIVED() 1001 { 1002 // ODF 1003 CHECK_EVAL_SHORT("RECEIVED(DATE(1990;6;1);DATE(1990;12;31);10000;5%)" , Value(10300.4291845494)); // Without Basis parameter 1004 // alternate function name 1005 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETRECEIVED(DATE(1990;6;1);DATE(1990;12;31);10000;5%)" , Value(10300.4291845494)); 1006 } 1007 1008 // RRI 1009 void TestFinancialFunctions::testRRI() 1010 { 1011 CHECK_EVAL_SHORT("RRI(1;100;200)" , Value(1)); // A trivial example of RRI. 1012 CHECK_EVAL_SHORT("RRI(12;5000;10000)" , Value(0.05946309436)); // RRI, practical example 1013 CHECK_EVAL_SHORT("RRI(12;10000;5000)" , Value(-0.056125687)); // If future value is less than present value, resultant rate is negative 1014 CHECK_EVAL_SHORT("RRI(0;100;200)" , Value(Value::errorVALUE())); // N must be greater than 0. 1015 } 1016 1017 // Straight-line depreciation 1018 // SLN(cost, salvage, life) 1019 void TestFinancialFunctions::testSLN() 1020 { 1021 // Excel example: http://office.microsoft.com/en-us/excel/HP100623811033.aspx 1022 CHECK_EVAL("SLN(30000; 7500; 10)", Value(2250.0)); 1023 1024 // http://www.vni.com/products/imsl/jmsl/v30/api/com/imsl/finance/slnEx1.html 1025 CHECK_EVAL("SLN(2500; 500; 24)", Value(83.3333333333333)); 1026 1027 // http://www.gnome.org/projects/gnumeric/doc/gnumeric-SLN.shtml 1028 CHECK_EVAL("SLN(10000; 700; 10)", Value(930)); 1029 1030 // test cases in OpenFormula specification 1031 CHECK_EVAL("SLN(4000;500;4)", Value(875)); 1032 } 1033 1034 // Sum-of-years' digits depreciation 1035 // SYD(cost, salvage, life, period) 1036 void TestFinancialFunctions::testSYD() 1037 { 1038 // Excel example: http://office.microsoft.com/en-us/excel/HP100623821033.aspx 1039 CHECK_EVAL("SYD(30000; 7500; 10; 1)", Value(4090.909090909090)); 1040 CHECK_EVAL("SYD(30000; 7500; 10; 10)", Value(409.0909090909090)); 1041 1042 // http://www.vni.com/products/imsl/jmsl/v30/api/com/imsl/finance/sydEx1.html 1043 CHECK_EVAL("SYD(25000; 5000; 15; 14)", Value(333.3333333333333)); 1044 1045 // http://www.gnome.org/projects/gnumeric/doc/gnumeric-SYD.shtml 1046 CHECK_EVAL("SYD(5000; 200; 5; 2)", Value(1280)); 1047 1048 // test cases in OpenFormula specification 1049 CHECK_EVAL("SYD(4000;500;4;2)", Value(1050)); 1050 } 1051 1052 // TBILLEQ 1053 void TestFinancialFunctions::testTBILLEQ() 1054 { 1055 // TODO check function, check OOo-2.2.1 1056 1057 // ODF 1058 CHECK_EVAL("TBILLEQ(DATE(1996;01;01);DATE(1996;02;01);5%)", Value(0.0509136560)); // 1059 CHECK_EVAL("TBILLEQ(DATE(1995;12;31);DATE(1996;02;01);5%)", Value(0.0509207589)); // specs 0.050920759 1060 CHECK_EVAL("TBILLEQ(DATE(1995;12;31);DATE(1996;07;01);5%)", Value(0.0520091194)); // specs 0.052016531 1061 CHECK_EVAL("TBILLEQ(DATE(1995;12;31);DATE(1996;12;31);5%)", Value(Value::errorVALUE())); // specs 0.053409423 OOo-2.2.1 Error(#VALUE!) 361 days 1062 CHECK_EVAL("TBILLEQ(DATE(1996;01;01);DATE(1996;06;30);5%)", Value(0.0519943020)); // specs 0.052001710 1063 CHECK_EVAL("TBILLEQ(DATE(1996;01;01);DATE(1996;07;01);5%)", Value(0.0520017096)); // specs 0.052009119 1064 CHECK_EVAL("TBILLEQ(DATE(1996;01;01);DATE(1996;12;31);5%)", Value(0.0533625731)); // specs 0.053401609 1065 CHECK_EVAL("TBILLEQ(DATE(1996;01;01);DATE(1997;01;01);5%)", Value(Value::errorVALUE())); // specs 0.053409423 OOo-2.2.1 Error(#VALUE!) days 361 1066 CHECK_EVAL("TBILLEQ(DATE(1996;07;01);DATE(1997;07;01);5%)", Value(Value::errorVALUE())); // specs 0.053401609 OOo-2.2.1 Error(#VALUE!) days 361 1067 // alternate function name 1068 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETTBILLEQ(DATE(1996;01;01);DATE(1996;02;01);5%)", Value(0.0509136560)); 1069 } 1070 1071 // TBILLPRICE 1072 void TestFinancialFunctions::testTBILLPRICE() 1073 { 1074 // ODF 1075 CHECK_EVAL("TBILLPRICE(DATE(1996;01;01);DATE(1996;02;01);5%)", Value(99.5694444444)); // 1076 CHECK_EVAL("TBILLPRICE(DATE(1995;12;31);DATE(1996;02;01);5%)", Value(99.5555555555)); // 1077 CHECK_EVAL("TBILLPRICE(DATE(1995;12;31);DATE(1996;07;01);5%)", Value(97.4722222222)); // ODF specs 97.45833333 OOo-2.2.1 97.47222222 1078 CHECK_EVAL("TBILLPRICE(DATE(1995;12;31);DATE(1996;12;31);5%)", Value(94.9861111111)); // ODF specs 94.91666667 OOo-2.2.1 94.98611111 1079 CHECK_EVAL("TBILLPRICE(DATE(1996;01;01);DATE(1996;06;30);5%)", Value(97.5000000000)); // ODF specs 97.48611111 OOo-2.2.1 97.50000000 1080 CHECK_EVAL("TBILLPRICE(DATE(1996;01;01);DATE(1996;07;01);5%)", Value(97.4861111111)); // ODF specs 97.47222222 OOo-2.2.1 97.48611111 1081 CHECK_EVAL("TBILLPRICE(DATE(1996;01;01);DATE(1996;12;31);5%)", Value(Value::errorVALUE())); // ODF specs 94.93055556 OOo-2.2.1 Err:502 1082 CHECK_EVAL("TBILLPRICE(DATE(1996;01;01);DATE(1997;01;01);5%)", Value(94.9861111111)); // ODF specs 94.91666667 OOo-2.2.1 94.98611111 1083 CHECK_EVAL("TBILLPRICE(DATE(1996;07;01);DATE(1997;07;01);5%)", Value(94.9861111111)); // ODF specs 94.93055556 OOo-2.2.1 94.98611111 1084 // alternate function name 1085 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETTBILLPRICE(DATE(1996;01;01);DATE(1996;02;01);5%)", Value(99.5694444444)); 1086 } 1087 1088 // TBILLYIELD 1089 void TestFinancialFunctions::testTBILLYIELD() 1090 { 1091 // ODF tests. All results are taken from OOo-2.2.1 instead of results from ODF-specs 1092 1093 // new implementation uses day360(US) to get daydiff. TODO check if we should test against 361 days instead 360 in function to get Error away 1094 CHECK_EVAL("TBILLYIELD(DATE(1996;01;01);DATE(1996;02;01);99.57)", Value(0.0501511337)); // 1095 CHECK_EVAL("TBILLYIELD(DATE(1995;12;31);DATE(1996;02;01);99.56)", Value(0.0497187626)); // 1096 CHECK_EVAL("TBILLYIELD(DATE(1995;12;31);DATE(1996;07;01);97.46)", Value(0.0515511576)); // specs 0.0512695 1097 CHECK_EVAL("TBILLYIELD(DATE(1995;12;31);DATE(1996;12;31);94.92)", Value(Value::errorVALUE())); // specs 0.0526414 OOo-2.2.1 Error(#VALUE!) 1098 CHECK_EVAL("TBILLYIELD(DATE(1996;01;01);DATE(1996;06;30);97.49)", Value(0.0514924608)); // specs 0.0512080 1099 CHECK_EVAL("TBILLYIELD(DATE(1996;01;01);DATE(1996;07;01);97.47)", Value(0.0516265948)); // specs 0.0513429 1100 CHECK_EVAL("TBILLYIELD(DATE(1996;01;01);DATE(1996;12;31);94.93)", Value(Value::errorVALUE())); // specs 0.0526762 OOo-2.2.1 Error(#VALUE!) 1101 CHECK_EVAL("TBILLYIELD(DATE(1996;01;01);DATE(1997;01;01);94.92)", Value(Value::errorVALUE())); // specs 0.0526414 OOo-2.2.1 Error(#VALUE!) 1102 CHECK_EVAL("TBILLYIELD(DATE(1996;07;01);DATE(1997;07;01);94.93)", Value(Value::errorVALUE())); // specs 0.0526762 OOo-2.2.1 Error(#VALUE!) 1103 // alternate function name 1104 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETTBILLYIELD(DATE(1996;01;01);DATE(1996;02;01);99.57)", Value(0.0501511337)); 1105 } 1106 1107 // VDB 1108 void TestFinancialFunctions::testVDB() 1109 { 1110 // ODF 1111 CHECK_EVAL("VDB(10000;600;10;0 ;0.875;1.5)", Value(1312.50)); 1112 CHECK_EVAL("VDB(10000;600;10;0.875;1.875;1.5)", Value(1303.125)); 1113 CHECK_EVAL("VDB(10000;600;10;1.875;2.875;1.5)", Value(1107.65625)); 1114 CHECK_EVAL("VDB(10000;600;10;2.875;3.875;1.5)", Value(941.5078125)); 1115 CHECK_EVAL("VDB(10000;600;10;3.875;4.875;1.5)", Value(800.2816406250)); 1116 CHECK_EVAL("VDB(10000;600;10;4.875;5.875;1.5)", Value(767.7910823171)); 1117 CHECK_EVAL("VDB(10000;600;10;5.875;6.875;1.5)", Value(767.410625)); 1118 CHECK_EVAL("VDB(10000;600;10;6.875;7.875;1.5)", Value(767.410625)); 1119 CHECK_EVAL("VDB(10000;600;10;7.875;8.875;1.5)", Value(767.410625)); 1120 CHECK_EVAL("VDB(10000;600;10;8.875;9.875;1.5)", Value(767.410625)); 1121 CHECK_EVAL("VDB(10000;600;10;9.875;10 ;1.5)", Value(95.9263281250)); 1122 } 1123 1124 // XIRR 1125 void TestFinancialFunctions::testXIRR() 1126 { 1127 // ODF 1128 CHECK_EVAL_SHORT("XIRR( {-20000;4000;12000;8000}; {date(2000;01;01); date(2000;06;01); date(2000;12;30); date(2001;03;01)} )", Value(0.2115964)); // 1129 CHECK_EVAL_SHORT("XIRR( {-20000;25000}; {date(2000;01;01); date(2001;01;01)} )", Value(0.2492381)); // 1130 CHECK_EVAL_SHORT("XIRR( {-10000;4000;12000}; {date(2000;01;01); date(2002;06;01); date(2004;01;01)} )", Value(0.1405418)); // 1131 // alternate function name 1132 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETXIRR( {-20000;25000};{date(2000;01;01); date(2001;01;01)} )", Value(0.2492381)); 1133 } 1134 1135 // XNPV 1136 void TestFinancialFunctions::testXNPV() 1137 { 1138 // bettersolution.com 1139 CHECK_EVAL("XNPV(0.1; {-1000;2000;3000}; {date(2005;01;01); date(2005;01;10); date(2005;01;15)})" , Value(3984.3581140636)); // 1140 1141 // with dates {date(2005;01;01); date(2005;03;01); date(2005;10;30); date(2006;02;15)} 1142 CHECK_EVAL("XNPV(0.09; {-10000;2750;4250;3250}; {38353;38412;38655;38763})", Value(-380.3891178530)); // 1143 CHECK_EVAL("XNPV(30; {-10000;2750;4250;3250}; {38353;38412;38655;38763})", Value(-8104.7862519770)); // 1144 CHECK_EVAL("XNPV(-30; {-10000;2750;4250;3250}; {38353;38412;38655;38763})", Value(Value::errorNUM())); // 1145 CHECK_EVAL("XNPV(0.09; {-10000;2750}; {date(2005;01;01); date(2005;01;10); date(2005;01;15)})", Value(Value::errorNUM())); // 1146 CHECK_EVAL("XNPV(0.1; {-1000;2000;3000}; {\"fail\"; date(2005;01;10); date(2005;01;15)})", Value(Value::errorVALUE())); // 1147 1148 // alternate function name 1149 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETXNPV(0.09; {-10000;2750;4250;3250}; {38353;38412;38655;38763})",Value(-380.3891178530)); 1150 } 1151 1152 // YIELDDISC 1153 void TestFinancialFunctions::testYIELDDISC() 1154 { 1155 // ODF 1156 CHECK_EVAL("YIELDDISC(DATE(1990;06;01);DATE(1990;12;31);941.66667;1000 )", Value(0.1061946838)); // Without Basis parameter 1157 CHECK_EVAL("YIELDDISC(DATE(1990;06;01);DATE(1990;12;31);941.64384;1000; 1)", Value(0.1061972566)); // With Basis=1 specs 0.106238821 OOo-2.2.1 0.1061972566 1158 CHECK_EVAL("YIELDDISC(DATE(1990;06;01);DATE(1990;12;31);940.83333;1000; 2)", Value(0.1062887575)); // With Basis=2 specs 0.107807168 OOo-2.2.1 0.1062887575 1159 CHECK_EVAL("YIELDDISC(DATE(1990;06;01);DATE(1990;12;31);941.64384;1000; 3)", Value(0.1061972566)); // With Basis=3 specs 0.106238821 OOo-2.2.1 0.1061972566 1160 CHECK_EVAL("YIELDDISC(DATE(1990;06;01);DATE(1990;12;31);941.94444;1000; 4)", Value(0.1061633823)); // With Basis=4 specs 0.105657842 OOo-2.2.1 0.1061633823 1161 CHECK_EVAL("YIELDDISC(DATE(1990;01;01);DATE(1990;12;31);97.08219;100; 1)", Value(0.0301376180)); // specs 0.051522942 OOo-2.2.1 0.0301376180 1162 CHECK_EVAL("YIELDDISC(DATE(1990;06;01);DATE(1990;06;30);99.75833;100; 4)", Value(0.0300730914)); // 1163 // alternate function name 1164 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETYIELDDISC(DATE(1990;06;01);DATE(1990;12;31);941.66667;1000 )", Value(0.1061946838)); 1165 } 1166 1167 // YIELDMAT 1168 void TestFinancialFunctions::testYIELDMAT() 1169 { 1170 // ODF 1171 CHECK_EVAL_SHORT("YIELDMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1); 6%;103.819218241 )", Value(0.050000000)); // Without Basis parameter 1172 CHECK_EVAL_SHORT("YIELDMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1); 6%;103.824693325;1)", Value(0.050000000)); // With Basis=1 1173 // CHECK_EVAL_SHORT( "YIELDMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1); 6%;103.858482159;2)", Value( 0.050000000 ) ); // With Basis=2 1174 CHECK_EVAL_SHORT("YIELDMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1); 6%;103.824693325;3)", Value(0.050000000)); // With Basis=3 1175 // CHECK_EVAL_SHORT( "YIELDMAT(DATE(1990;6;1);DATE(1992;12;31);DATE(1990;1;1); 6%;103.817732653;4)", Value( 0.050000000 ) ); // With Basis=4 NOK diff = 0.0074805 1176 CHECK_EVAL_SHORT("YIELDMAT(DATE(1990;6;1);DATE(1992;12;31);DATE(1990;1;1); 3%;102.395007924;0)", Value(0.020000000)); // With Basis=0 1177 // CHECK_EVAL_SHORT( "YIELDMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1); 5%;102.967175933;2)", Value( 0.030000000 ) ); // With Basis=2 NOK diff = -0.0126036 1178 1179 // alternate function name 1180 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETYIELDMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1); 6%;103.819218241 )", Value(0.050000000)); 1181 } 1182 1183 // Zero-coupon (pure discount) bond 1184 // ZERO_COUPON(faceValue; rate; years) 1185 void TestFinancialFunctions::testZEROCOUPON() 1186 { 1187 CHECK_EVAL("ZERO_COUPON(1000;.1;20)", Value(148.6436280241434531)); 1188 CHECK_EVAL("ZERO_COUPON(1000;.2;20)", Value(26.0840533045888456)); 1189 CHECK_EVAL("ZERO_COUPON(1000;.15/12;10)", Value(883.1809261539680165)); 1190 CHECK_EVAL("ZERO_COUPON(1000;.25;1)", Value(800)); 1191 } 1192 1193 QTEST_MAIN(TestFinancialFunctions)