1 /* This file is part of the KDE project
2 Copyright 2006 Ariya Hidayat <ariya@kde.org>
3 Copyright 2007 Sascha Pfau <MrPeacock@gmail.com>
4
5 This library is free software; you can redistribute it and/or
6 modify it under the terms of the GNU Library General Public
7 License as published by the Free Software Foundation; only
8 version 2 of the License.
9
10 This library is distributed in the hope that it will be useful,
11 but WITHOUT ANY WARRANTY; without even the implied warranty of
12 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13 Library General Public License for more details.
14
15 You should have received a copy of the GNU Library General Public License
16 along with this library; see the file COPYING.LIB. If not, write to
17 the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
18 Boston, MA 02110-1301, USA.
19 */
20 #include "TestFinancialFunctions.h"
21
22 #include "TestKspreadCommon.h"
23
24 #include <QTest>
25
initTestCase()26 void TestFinancialFunctions::initTestCase()
27 {
28 FunctionModuleRegistry::instance()->loadFunctionModules();
29 }
30
31 // NOTE: we do not compare the numbers _exactly_ because it is difficult
32 // to get one "true correct" expected values for the functions due to:
33 // - different algorithms among spreadsheet programs
34 // - precision limitation of floating-point number representation
35 // - accuracy problem due to propagated error in the implementation
36
37 #define CHECK_EVAL(x,y) QCOMPARE(TestDouble(x,y,6),y)
38 #define CHECK_EVAL_SHORT(x,y) QCOMPARE(TestDouble(x,y,11),y)
39 #define CHECK_EVAL_EQUAL(x,y) QCOMPARE(TestSimple(x),y)
40
TestDouble(const QString & formula,const Value & v2,int accuracy)41 static Value TestDouble(const QString& formula, const Value& v2, int accuracy)
42 {
43 double epsilon = DBL_EPSILON * pow(10.0, (double)(accuracy));
44
45 Formula f;
46 QString expr = formula;
47 if (expr[0] != '=')
48 expr.prepend('=');
49 f.setExpression(expr);
50 Value result = f.eval();
51
52 bool res = fabs(v2.asFloat() - result.asFloat()) < epsilon;
53
54 if (!res)
55 qDebug() << "check failed -->" << "Epsilon =" << epsilon << "" << (double)v2.asFloat() << " to" << (double)result.asFloat() << " diff =" << (double)(v2.asFloat() - result.asFloat());
56 // else
57 // qDebug()<<"check -->" <<" diff =" << v2.asFloat()-result.asFloat();
58 if (res)
59 return v2;
60 else
61 return result;
62 }
63
TestSimple(const QString & formula)64 static Value TestSimple(const QString& formula)
65 {
66 Formula f;
67 QString expr = formula;
68 if (expr[0] != '=')
69 expr.prepend('=');
70 f.setExpression(expr);
71 return f.eval();
72 }
73
74 // ACCRINT
testACCRINT()75 void TestFinancialFunctions::testACCRINT()
76 {
77 // odf test
78 CHECK_EVAL("ACCRINT( \"1992-12-01\"; \"1993-06-01\"; \"1993-07-01\"; 0.055; 100 ; 2; 0 ) ", Value(3.2083333333));
79 CHECK_EVAL("ACCRINT( \"2001-02-28\"; \"2001-08-31\";\"2001-05-01\"; 0.1 ; 1000; 2; 0 )",
80 Value(16.9444444444)); // A security is issued on 2.28.2001.
81 // First interest is set for 8.31.2001. The settlement date is 5.1.2001.
82 // The Rate is 0.1 or 10% and Par is 1000 currency units. Interest is paid
83 // half-yearly (frequency is 2). The basis is the US method (0). How much interest has accrued?
84 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
85 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
86 CHECK_EVAL("ACCRINT( \"2004-02-01\"; \"2004-04-01\"; \"2004-05-01\"; 0.1; 1000; 4; 2 )", Value(25)); // leap year, quarterly, actual/360
87 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
88 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
89 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
90 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
91 // alternate function name
92 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));
93 }
94
95 // ACCRINTM
testACCRINTM()96 void TestFinancialFunctions::testACCRINTM()
97 {
98 // Calligra Sheets
99 CHECK_EVAL_SHORT("ACCRINTM( \"2001-04-01\"; \"2001-06-15\"; 0.1; 1000; 3 )", Value(20.5479454));
100 CHECK_EVAL_SHORT("ACCRINTM( \"2004-02-01\"; \"2004-05-01\"; 0.1; 1000; 0 )", Value(24.722222)); // leap year, US (NASD) 30/360
101 CHECK_EVAL_SHORT("ACCRINTM( \"2004-02-01\"; \"2004-05-01\"; 0.1; 1000; 1 )", Value(24.590164)); // leap year, actual/actual
102 CHECK_EVAL_SHORT("ACCRINTM( \"2004-02-01\"; \"2004-05-01\"; 0.1; 1000; 2 )", Value(25.0)); // leap year, actual/360
103 CHECK_EVAL_SHORT("ACCRINTM( \"2004-02-01\"; \"2004-05-01\"; 0.1; 1000; 3 )", Value(24.657534)); // leap year, actual/365
104 CHECK_EVAL_SHORT("ACCRINTM( \"2004-02-01\"; \"2004-05-01\"; 0.1; 1000; 4 )", Value(25.0)); // leap year, European 30/360
105 // alternate function name
106 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETACCRINTM( \"2001-04-01\"; \"2001-06-15\"; 0.1; 1000; 3 )", Value(20.5479454));
107 }
108
109 // AMORDEGRC
testAMORDEGRC()110 void TestFinancialFunctions::testAMORDEGRC()
111 {
112 // Excel Formen und Funktionen
113 CHECK_EVAL("AMORDEGRC( 2400; 34199; 34334; 300; 1; 0.15; 1 )" , Value(775));
114
115 // bettersolution.com
116 CHECK_EVAL("AMORDEGRC( 50000; \"2003-01-01\"; \"2003-12-31\"; 500; 1; 0.15; 1 )" , Value(11738)); //
117 CHECK_EVAL("AMORDEGRC( 50000; \"2003-01-01\"; \"2003-12-31\"; 500; 2; 0.15; 1 )" , Value(7336)); //
118 CHECK_EVAL("AMORDEGRC( 50000; \"2003-01-01\"; \"2003-12-31\"; 500; 3; 0.15; 1 )" , Value(4585)); //
119 CHECK_EVAL("AMORDEGRC( 50000; \"2003-01-01\"; \"2003-12-31\"; 500; 4; 0.15; 1 )" , Value(2866)); //
120 // CHECK_EVAL_SHORT( "AMORDEGRC( 50000; \"2003-01-01\"; \"2003-12-31\"; 500; 5; 0.15; 1 )" , Value( 2388 ) ); // TODO check Calligra Sheets -> 1791
121
122 // odf tests
123 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 1 )" , Value(228)); // the first period (10 years life time)
124 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)
125 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)
126 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)
127 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.25; 1 )" , Value(342)); // the first period (4 years life time)
128 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 0 )" , Value(229)); // leap year, US (NASD) 30/360
129 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 1 )" , Value(228)); // leap year, actual/actual
130 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 2 )" , Value(231)); // (specs 232) leap year, actual/360
131 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 3 )" , Value(228)); // leap year, actual/365
132 CHECK_EVAL("AMORDEGRC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 4 )" , Value(228)); // leap year, European 30/360
133
134 // alternate function name
135 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETAMORDEGRC(2400;34199;34334;300;1;0.15;1)", Value(775));
136 }
137
138 // AMORLINC
testAMORLINC()139 void TestFinancialFunctions::testAMORLINC()
140 {
141 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)
142 CHECK_EVAL_SHORT("AMORLINC( 1000; \"2006-02-01\"; \"2006-12-31\"; 10; 0; 0.1; 3 )" , Value(91.2328767123)); // leap year, actual/365
143 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));
144 }
145
146 // COMPOUND
testCOMPOUND()147 void TestFinancialFunctions::testCOMPOUND()
148 {
149 // Calligra Sheets
150 CHECK_EVAL_SHORT("COMPOUND(5000;0.12;4;5)", Value(9030.556173));
151 }
152
153 // CONTINUOUS
testCONTINUOUS()154 void TestFinancialFunctions::testCONTINUOUS()
155 {
156 // Calligra Sheets
157 CHECK_EVAL_SHORT("CONTINUOUS(1000;0.1;1)", Value(1105.17091808));
158 }
159
160 // COUPDAYBS
testCOUPDAYBS()161 void TestFinancialFunctions::testCOUPDAYBS()
162 {
163 // ODF
164 CHECK_EVAL_SHORT("COUPDAYBS( DATE(1997;11;9); DATE(1999;11;15); 2 )", Value(174));
165 CHECK_EVAL_SHORT("COUPDAYBS( DATE(1997;11;9); DATE(1999;11;15); 2; 1 )", Value(178));
166 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 4; 0 )", Value(60)); // US (NASD) 30/360
167 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 4; 1 )", Value(60)); // actual/actual
168 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 4; 2 )", Value(60)); // actual/360
169 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 4; 3 )", Value(60)); // actual/365
170 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 4; 4 )", Value(60)); // European 30/360
171 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 1 )", Value(60)); // annual
172 CHECK_EVAL_SHORT("COUPDAYBS( DATE(2004;3;1); DATE(2009;1;1); 2 )", Value(60)); // semiannual
173 // alternate function name
174 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPDAYBS( DATE(1997;11;9); DATE(1999;11;15); 2 )", Value(174));
175 }
176
177 // COUPDAYS
testCOUPDAYS()178 void TestFinancialFunctions::testCOUPDAYS()
179 {
180 // ODF
181 CHECK_EVAL_SHORT("COUPDAYS( DATE(1997;11;9); DATE(1999;11;15); 2 )", Value(180));
182 CHECK_EVAL_SHORT("COUPDAYS( DATE(1997;11;9); DATE(1999;11;15); 2; 1 )", Value(184));
183 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 4; 0 )", Value(90)); // US (NASD) 30/360
184 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 4; 1 )", Value(91)); // actual/actual
185 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 4; 2 )", Value(90)); // actual/360
186 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 4; 3 )", Value(91.25)); // actual/365
187 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 4; 4 )", Value(90)); // European 30/360
188 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 1 )", Value(360)); // annual
189 CHECK_EVAL_SHORT("COUPDAYS( DATE(2004;2;1); DATE(2009;1;1); 2 )", Value(180)); // semiannual
190 // alternate function name
191 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPDAYS( DATE(1997;11;9); DATE(1999;11;15); 2 )", Value(180));
192 }
193
194 // COUPDAYSNC
testCOUPDAYSNC()195 void TestFinancialFunctions::testCOUPDAYSNC()
196 {
197 // ODF
198 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(1997;5;19); DATE(1999;11;15); 2 )", Value(176));
199 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(1997;5;19); DATE(1999;11;15); 2; 1 )", Value(180));
200 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 4; 0 )", Value(60));
201 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 4; 1 )", Value(60));
202 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 4; 2 )", Value(60));
203 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 4; 3 )", Value(60));
204 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 4; 4 )", Value(60));
205 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 1 )", Value(330)); // annual
206 CHECK_EVAL_SHORT("COUPDAYSNC( DATE(2004;2;1); DATE(2009;1;1); 2 )", Value(150)); // semiannual
207 // alternate function name
208 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPDAYSNC( DATE(1997;5;19); DATE(1999;11;15); 2 )", Value(176));
209 }
210
211 // COUPNCD
testCOUPNCD()212 void TestFinancialFunctions::testCOUPNCD()
213 {
214 // ODF
215 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2007-01-01\"; 1; 1 )=DATE(2005;01;01)", Value(true)); // Annual
216 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2007-01-01\"; 2; 1 )=DATE(2004;07;01)", Value(true)); // Semiannual
217 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2007-01-01\"; 4; 1 )=DATE(2004;04;01)", Value(true)); // Quarterly
218 CHECK_EVAL_EQUAL("COUPNCD( \"2007-01-01\"; \"2007-01-01\"; 1; 1 )", Value::errorVALUE()); // settlement < maturity
219 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2009-01-01\"; 4; 0 )=DATE(2004;04;01)", Value(true));
220 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2009-01-01\"; 4; 1 )=DATE(2004;04;01)", Value(true));
221 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2009-01-01\"; 4; 2 )=DATE(2004;04;01)", Value(true));
222 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2009-01-01\"; 4; 3 )=DATE(2004;04;01)", Value(true));
223 CHECK_EVAL_EQUAL("COUPNCD( \"2004-01-01\"; \"2009-01-01\"; 4; 4 )=DATE(2004;04;01)", Value(true));
224 // alternate function name
225 CHECK_EVAL_EQUAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPNCD( \"2004-01-01\"; \"2007-01-01\"; 1; 1 )=DATE(2005;01;01)", Value(true));
226 }
227
228 // COUPNUM
testCOUPNUM()229 void TestFinancialFunctions::testCOUPNUM()
230 {
231 // ODF
232 CHECK_EVAL_SHORT("COUPNUM( \"2004-01-01\"; \"2007-01-01\"; 1; 1 )", Value(3)); // Annual
233 CHECK_EVAL_SHORT("COUPNUM( \"2004-01-01\"; \"2007-01-01\"; 2; 1 )", Value(6)); // Semiannual
234 CHECK_EVAL_SHORT("COUPNUM( \"2004-01-01\"; \"2007-01-01\"; 4; 1 )", Value(12)); // Quarterly
235 CHECK_EVAL_SHORT("COUPNUM( \"2004-02-01\"; \"2009-01-01\"; 4; 0 )", Value(20)); //
236 CHECK_EVAL_SHORT("COUPNUM( \"2004-02-01\"; \"2009-01-01\"; 4; 1 )", Value(20)); //
237 CHECK_EVAL_SHORT("COUPNUM( \"2004-02-01\"; \"2009-01-01\"; 4; 2 )", Value(20)); //
238 CHECK_EVAL_SHORT("COUPNUM( \"2004-02-01\"; \"2009-01-01\"; 4; 3 )", Value(20)); //
239 CHECK_EVAL_SHORT("COUPNUM( \"2004-02-01\"; \"2009-01-01\"; 4; 4 )", Value(20)); //
240 CHECK_EVAL_SHORT("COUPNUM( \"2004-01-01\"; \"2004-04-30\"; 12; 1 )", Value(4)); // Monthly, to end of month with less daysInMonth
241 CHECK_EVAL_SHORT("COUPNUM( \"2004-01-01\"; \"2004-05-01\"; 12; 1 )", Value(4)); // Monthly, to begin of month
242 // alternate function name
243 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPNUM(\"2004-01-01\";\"2007-01-01\";1;1)", Value(3));
244 }
245
246 // COUPPCD
testCOUPPCD()247 void TestFinancialFunctions::testCOUPPCD()
248 {
249 // ODF
250 CHECK_EVAL_EQUAL("COUPPCD( \"2004-12-31\"; \"2007-01-01\"; 1; 1 )=DATE(2004;1;1)", Value(true)); // Annual
251 CHECK_EVAL_EQUAL("COUPPCD( \"2004-12-31\"; \"2007-01-01\"; 2; 1 )=DATE(2004;7;1)", Value(true)); // Semiannual
252 CHECK_EVAL_EQUAL("COUPPCD( \"2004-12-31\"; \"2007-01-01\"; 4; 1 )=DATE(2004;10;1)", Value(true)); // Quarterly
253 CHECK_EVAL_EQUAL("COUPPCD( \"2007-01-01\"; \"2004-01-01\"; 1; 1 )", Value::errorVALUE()); // settlement < maturity
254 CHECK_EVAL_EQUAL("COUPPCD( \"2004-02-29\"; \"2009-01-01\"; 4; 0 )=DATE(2004;01;01)", Value(true));
255 CHECK_EVAL_EQUAL("COUPPCD( \"2004-02-29\"; \"2009-01-01\"; 4; 1 )=DATE(2004;01;01)", Value(true));
256 CHECK_EVAL_EQUAL("COUPPCD( \"2004-02-29\"; \"2009-01-01\"; 4; 2 )=DATE(2004;01;01)", Value(true));
257 CHECK_EVAL_EQUAL("COUPPCD( \"2004-02-29\"; \"2009-01-01\"; 4; 3 )=DATE(2004;01;01)", Value(true));
258 CHECK_EVAL_EQUAL("COUPPCD( \"2004-02-29\"; \"2009-01-01\"; 4; 4 )=DATE(2004;01;01)", Value(true));
259 // alternate function name
260 CHECK_EVAL_EQUAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCOUPPCD( \"2004-12-31\"; \"2007-01-01\"; 1; 1 )=DATE(2004;1;1)", Value(true));
261 }
262
263 // CUMIPMT
testCUMIPMT()264 void TestFinancialFunctions::testCUMIPMT()
265 {
266 // ODF
267 CHECK_EVAL_SHORT("CUMIPMT( 0.06/12; 5*12; 100000; 5; 12; 0 )", Value(-3562.187023)); // maturity at the end of a period
268 CHECK_EVAL_SHORT("CUMIPMT( 0.06/12; 5*12; 100000; 5; 12; 1 )", Value(-3544.464699)); // maturity at the beginning of a period
269 CHECK_EVAL_SHORT("CUMIPMT( 0.06/12; 5*12; 100000; 0; 0; 0 )", Value(Value::errorVALUE())); // start > 0; end > 0
270 CHECK_EVAL_SHORT("CUMIPMT( 0.06/12; 5*12; 100000; 5; 61; 0 )", Value(Value::errorVALUE())); // end > periods
271 CHECK_EVAL_SHORT("CUMIPMT( 0.06/12; 5*12; 100000; 15; 12; 0 )", Value(Value::errorVALUE())); // start > end
272 // alternate function name
273 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCUMIPMT(0.06/12;5*12;100000;5;12;0)", Value(-3562.187023));
274 }
275
276 // CUMPRINC
testCUMPRINC()277 void TestFinancialFunctions::testCUMPRINC()
278 {
279 // ODF
280 CHECK_EVAL_SHORT("CUMPRINC( 0.06/12; 5*12; 100000; 5; 12; 0 )", Value(-11904.054201)); // maturity at the end of a period
281 CHECK_EVAL_SHORT("CUMPRINC( 0.06/12; 5*12; 100000; 5; 12; 1 )", Value(-11844.830051)); // maturity at the beginning of a period
282 CHECK_EVAL_SHORT("CUMPRINC( 0.06/12; 5*12; 100000; 0; 0; 0 )", Value(Value::errorVALUE())); // start > 0; end > 0
283 CHECK_EVAL_SHORT("CUMPRINC( 0.06/12; 5*12; 100000; 5; 61; 0 )", Value(Value::errorVALUE())); // end > periods
284 CHECK_EVAL_SHORT("CUMPRINC( 0.06/12; 5*12; 100000;15; 12; 0 )", Value(Value::errorVALUE())); // start > end
285 // alternate function name
286 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETCUMPRINC(0.06/12;5*12;100000;5;12;0)", Value(-11904.054201));
287 }
288
289 // Fixed-declining balance depreciation
290 // DB(cost, salvage, life, period, month)
testDB()291 void TestFinancialFunctions::testDB()
292 {
293 // Excel example: http://office.microsoft.com/en-us/excel/HP100623551033.aspx
294 CHECK_EVAL("DB(1000000; 100000; 6; 1; 7)", Value(186083.3333333333));
295 CHECK_EVAL("DB(1000000; 100000; 6; 2; 7)", Value(259639.4166666667));
296 CHECK_EVAL("DB(1000000; 100000; 6; 3; 7)", Value(176814.4427500000));
297 CHECK_EVAL("DB(1000000; 100000; 6; 4; 7)", Value(120410.6355127500));
298 CHECK_EVAL("DB(1000000; 100000; 6; 5; 7)", Value(81999.64278418274));
299 CHECK_EVAL("DB(1000000; 100000; 6; 6; 7)", Value(55841.75673602846));
300 CHECK_EVAL("DB(1000000; 100000; 6; 7; 7)", Value(15845.09847384807));
301
302 // http://www.vni.com/products/imsl/jmsl/v30/api/com/imsl/finance/dbEx1.html
303 CHECK_EVAL("DB(2500; 500; 3; 1; 6)", Value(518.750000000000));
304 CHECK_EVAL("DB(2500; 500; 3; 2; 6)", Value(822.218750000000));
305 CHECK_EVAL("DB(2500; 500; 3; 3; 6)", Value(480.997968750000));
306 CHECK_EVAL("DB(2500; 500; 3; 4; 6)", Value(140.691905859375));
307
308 // test cases in OpenFormula specification
309 CHECK_EVAL("DB(4000;500;4;2)", Value(963.90));
310 CHECK_EVAL("DB(4000;500;4;2;2)", Value(1510.65));
311 CHECK_EVAL("DB(4000;500;4;5)", Value(0.0));
312 CHECK_EVAL("DB(0;500;4;2)", Value(Value::errorNUM()));
313 CHECK_EVAL("DB(4000;-500;4;2)", Value(Value::errorNUM()));
314 CHECK_EVAL("DB(4000;500;0;0)", Value(Value::errorNUM()));
315 CHECK_EVAL("DB(4000;500;2;0)", Value(Value::errorNUM()));
316 }
317
318 // Double declining balance depreciation
319 // DDB(cost, salvage, life, period, factor)
testDDB()320 void TestFinancialFunctions::testDDB()
321 {
322 // Excel example: http://office.microsoft.com/en-us/excel/HP100623561033.aspx
323 CHECK_EVAL("DDB(2400; 300; 10*365; 1; 2)", Value(1.31506849315065));
324 CHECK_EVAL("DDB(2400; 300; 10*12; 1; 2)", Value(40.0));
325 CHECK_EVAL("DDB(2400; 300; 10; 1; 2)", Value(480.0));
326 CHECK_EVAL("DDB(2400; 300; 10; 2; 1.5)", Value(306));
327 CHECK_EVAL("DDB(2400; 300; 10; 10; 2)", Value(22.1225472000002));
328
329 // http://www.vni.com/products/imsl/jmsl/v30/api/com/imsl/finance/ddbEx1.html
330 CHECK_EVAL("DDB(2500; 500; 24; 1; 2)", Value(208.333333333333));
331 CHECK_EVAL("DDB(2500; 500; 24; 2; 2)", Value(190.972222222222));
332 CHECK_EVAL("DDB(2500; 500; 24; 3; 2)", Value(175.057870370370));
333 CHECK_EVAL("DDB(2500; 500; 24; 4; 2)", Value(160.469714506173));
334 CHECK_EVAL("DDB(2500; 500; 24; 5; 2)", Value(147.097238297325));
335 CHECK_EVAL("DDB(2500; 500; 24; 6; 2)", Value(134.839135105881));
336 CHECK_EVAL("DDB(2500; 500; 24; 7; 2)", Value(123.602540513725));
337 CHECK_EVAL("DDB(2500; 500; 24; 8; 2)", Value(113.302328804248));
338 CHECK_EVAL("DDB(2500; 500; 24; 9; 2)", Value(103.860468070560));
339 CHECK_EVAL("DDB(2500; 500; 24; 10; 2)", Value(95.2054290646802));
340 CHECK_EVAL("DDB(2500; 500; 24; 11; 2)", Value(87.2716433092901));
341 CHECK_EVAL("DDB(2500; 500; 24; 12; 2)", Value(79.9990063668494));
342 CHECK_EVAL("DDB(2500; 500; 24; 13; 2)", Value(73.3324225029452));
343 CHECK_EVAL("DDB(2500; 500; 24; 14; 2)", Value(67.2213872943665));
344 CHECK_EVAL("DDB(2500; 500; 24; 15; 2)", Value(61.6196050198359));
345 CHECK_EVAL("DDB(2500; 500; 24; 16; 2)", Value(56.4846379348497));
346 CHECK_EVAL("DDB(2500; 500; 24; 17; 2)", Value(51.7775847736120));
347 CHECK_EVAL("DDB(2500; 500; 24; 18; 2)", Value(47.4627860424778));
348 CHECK_EVAL("DDB(2500; 500; 24; 19; 2)", Value(22.0906464672553));
349 CHECK_EVAL("DDB(2500; 500; 24; 20; 2)", Value(0));
350 CHECK_EVAL("DDB(2500; 500; 24; 21; 2)", Value(0));
351 CHECK_EVAL("DDB(2500; 500; 24; 22; 2)", Value(0));
352 CHECK_EVAL("DDB(2500; 500; 24; 23; 2)", Value(0));
353 CHECK_EVAL("DDB(2500; 500; 24; 24; 2)", Value(0));
354
355 // test cases in OpenFormula specification
356 CHECK_EVAL("DDB(4000; 500; 4; 2; 2)", Value(1000));
357 CHECK_EVAL("DDB(4000; 500; 4; 2)", Value(1000));
358 CHECK_EVAL("DDB(1100; 100; 5; 5; 2.3 )", Value(0));
359
360 // try default factor (=2)
361 CHECK_EVAL("DDB(2400; 300; 10*12; 1)", Value(40.0));
362 CHECK_EVAL("DDB(2400; 300; 10; 1)", Value(480.0));
363 CHECK_EVAL("DDB(2500; 500; 24; 22)", Value(0));
364 CHECK_EVAL("DDB(2500; 500; 24; 23)", Value(0));
365 CHECK_EVAL("DDB(2500; 500; 24; 24)", Value(0));
366
367 // factor > life
368 CHECK_EVAL("DDB(2400; 300; 10; 0.8; 20)", Value(2100));
369 CHECK_EVAL("DDB(2400; 300; 10; 1.0; 20)", Value(2100));
370 CHECK_EVAL("DDB(2400; 300; 10; 1.2; 20)", Value(0));
371
372 // factor is fraction
373 CHECK_EVAL("DDB(2400; 300; 10; 2; 2.5)", Value(450));
374 CHECK_EVAL("DDB(2400; 300; 10; 2; 1.5)", Value(306));
375
376 // period is fraction
377 CHECK_EVAL("DDB(2400; 300; 10; 6.7; 2)", Value(134.5408487904432));
378 CHECK_EVAL("DDB(2400; 300; 10; 7.7; 2)", Value(107.6326790323546));
379 }
380
381 // DISC
testDISC()382 void TestFinancialFunctions::testDISC()
383 {
384 // basis | day-count basis
385 //-------+-----------------------------------
386 // 0 | US (NASD) 30/360
387 // 1 | Actual/actual (Euro), also known as AFB
388 // 2 | Actual/360
389 // 3 | Actual/365
390 // 4 | European 30/360
391
392 CHECK_EVAL_SHORT("DISC( DATE(2004;02;29); date(2009;01;01); 95000; 100000; 0)", Value(0.010339));
393 CHECK_EVAL_SHORT("DISC( DATE(2004;02;29); date(2009;01;01); 95000; 100000; 1)", Value(0.010333)); // NOK (0.010332)
394 CHECK_EVAL_SHORT("DISC( DATE(2004;02;29); date(2009;01;01); 95000; 100000; 2)", Value(0.010181));
395 CHECK_EVAL_SHORT("DISC( DATE(2004;02;29); date(2009;01;01); 95000; 100000; 3)", Value(0.010322));
396 CHECK_EVAL_SHORT("DISC( DATE(2004;02;29); date(2009;01;01); 95000; 100000; 4)", Value(0.010333));
397 CHECK_EVAL_SHORT("DISC( DATE(2006;01;01); date(2008;01;01); 200; 100; 3)", Value(-0.500000));
398 CHECK_EVAL_SHORT("DISC( DATE(2006;01;01); date(2005;07;01); 95000; 100000; 4)", Value(false));
399 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETDISC( DATE(2004;02;29); date(2009;01;01); 95000; 100000; 0)", Value(0.010339));
400 }
401
402 // DOLLARDE
testDOLLARDE()403 void TestFinancialFunctions::testDOLLARDE()
404 {
405 // http://publib.boulder.ibm.com/infocenter/iadthelp/v7r0/index.jsp?topic=/com.businessobjects.integration.eclipse.designer.doc/designer/Functions68.html
406 CHECK_EVAL_SHORT("DOLLARDE( 1.1 ; 8)" , Value(1.125)); //
407 CHECK_EVAL_SHORT("DOLLARDE( 2.13;16)" , Value(2.8125)); //
408 CHECK_EVAL_SHORT("DOLLARDE( 2.45;16)" , Value(4.8125)); //
409 CHECK_EVAL_SHORT("DOLLARDE( 1.16; 8)" , Value(1.2)); //
410
411 // http://www.bettersolutions.com/excel/EDH113/LR849116511.htm
412 CHECK_EVAL_SHORT("DOLLARDE( 1.1 ; 2)" , Value(1.5)); //
413 CHECK_EVAL_SHORT("DOLLARDE( 1.25; 5)" , Value(1.5)); //
414 CHECK_EVAL_SHORT("DOLLARDE( 5.08; 4)" , Value(5.2)); //
415 CHECK_EVAL_SHORT("DOLLARDE( 5.24; 4)" , Value(5.6)); //
416 CHECK_EVAL_SHORT("DOLLARDE( 100.24; 4)" , Value(100.6)); //
417 CHECK_EVAL_SHORT("DOLLARFR(DOLLARDE( 101.2; 4);4)", Value(101.2)); // for- and backward
418
419 // ODF
420 CHECK_EVAL_SHORT("DOLLARDE( 1.1; 4)" , Value(1.25)); //
421 CHECK_EVAL_SHORT("DOLLARDE( 1.1; 3)" , Value(1.333333)); //
422 CHECK_EVAL_SHORT("DOLLARDE( -1.1;10)" , Value(-1.1)); //
423 CHECK_EVAL_SHORT("DOLLARDE( 1.0; 5)" , Value(1)); //
424 CHECK_EVAL_SHORT("DOLLARDE( 1.1;10)" , Value(1.1)); //
425 CHECK_EVAL_SHORT("DOLLARDE( 1.1; 0)" , Value::errorVALUE()); //
426
427 // alternate function name
428 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETDOLLARDE(1.1;8)" , Value(1.125));
429 }
430
431 // DOLLARFR
testDOLLARFR()432 void TestFinancialFunctions::testDOLLARFR()
433 {
434 // my tests
435 CHECK_EVAL_SHORT("DOLLARFR( 1.1 ; 9)" , Value(1.09)); //
436 CHECK_EVAL_SHORT("DOLLARFR( 1.1 ; 11)" , Value(1.011)); //
437 CHECK_EVAL_SHORT("DOLLARFR( 1.1 ; 10)" , Value(1.1)); //
438
439 // http://www.bettersolutions.com/excel/EDH113/QR810212321.htm
440 CHECK_EVAL_SHORT("DOLLARFR( 1.125 ; 8)" , Value(1.1)); //
441 CHECK_EVAL_SHORT("DOLLARFR( 1.5 ; 2)" , Value(1.1)); //
442 CHECK_EVAL_SHORT("DOLLARFR( 1.5 ; 8)" , Value(1.4)); //
443 CHECK_EVAL_SHORT("DOLLARFR( 1.5 ; 5)" , Value(1.25)); //
444
445 // ODF
446 CHECK_EVAL_SHORT("DOLLARFR( 1.1 ;10)" , Value(1.1)); //
447 CHECK_EVAL_SHORT("DOLLARFR( 1.25; 4)" , Value(1.1)); //
448 CHECK_EVAL_SHORT("DOLLARFR(-1.33333; 3)" , Value(-1.099999)); // ODF specs error (1.1) must be -1.1
449 CHECK_EVAL_SHORT("DOLLARFR( 1.0; 5)" , Value(1)); //
450 CHECK_EVAL_SHORT("DOLLARFR( 1.1; 0)" , Value::errorVALUE()); //
451
452 // alternate function name
453 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETDOLLARFR( 1.1 ; 9)" , Value(1.09));
454 }
455
456 // DURATION
testDURATION()457 void TestFinancialFunctions::testDURATION()
458 {
459 // Calligra Sheets
460 CHECK_EVAL("DURATION( 0.1; 1000; 2000 )" , Value(7.2725408973)); //
461 // alternate function name
462 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETDURATION( 0.1; 1000; 2000 )" , Value(7.2725408973));
463 }
464
465 // DURATION_ADD
testDURATION_ADD()466 void TestFinancialFunctions::testDURATION_ADD()
467 {
468 CHECK_EVAL("DURATION_ADD( \"1998-01-01\"; \"2006-01-01\"; 0.08; 0.09; 2; 1 )" , Value(5.9937749555)); //
469 }
470
471 // EFFECT
testEFFECT()472 void TestFinancialFunctions::testEFFECT()
473 {
474 // Calligra Sheets
475 CHECK_EVAL_SHORT("EFFECT(0.08;12)", Value(0.083));
476 // alternate function name
477 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETEFFECT(0.08;12)", Value(0.083));
478 }
479
480 // Euro conversion
481 // EURO(currency)
testEURO()482 void TestFinancialFunctions::testEURO()
483 {
484 CHECK_EVAL("EURO(\"ATS\")", Value(13.7603));
485 CHECK_EVAL("EURO(\"BEF\")", Value(40.3399));
486 CHECK_EVAL("EURO(\"DEM\")", Value(1.95583));
487 CHECK_EVAL("EURO(\"ESP\")", Value(166.386));
488 CHECK_EVAL("EURO(\"EUR\")", Value(1.0));
489 CHECK_EVAL("EURO(\"FIM\")", Value(5.94573));
490 CHECK_EVAL("EURO(\"FRF\")", Value(6.55957));
491 CHECK_EVAL("EURO(\"GRD\")", Value(340.75));
492 CHECK_EVAL("EURO(\"IEP\")", Value(0.787564));
493 CHECK_EVAL("EURO(\"ITL\")", Value(1936.27));
494 CHECK_EVAL("EURO(\"LUX\")", Value(40.3399));
495 CHECK_EVAL("EURO(\"NLG\")", Value(2.20371));
496 CHECK_EVAL("EURO(\"PTE\")", Value(200.482));
497
498 // should still work with lowercase
499 CHECK_EVAL("EURO(\"ats\")", Value(13.7603));
500 CHECK_EVAL("EURO(\"bef\")", Value(40.3399));
501 CHECK_EVAL("EURO(\"dem\")", Value(1.95583));
502 CHECK_EVAL("EURO(\"esp\")", Value(166.386));
503 CHECK_EVAL("EURO(\"eur\")", Value(1.0));
504 CHECK_EVAL("EURO(\"fim\")", Value(5.94573));
505 CHECK_EVAL("EURO(\"frf\")", Value(6.55957));
506 CHECK_EVAL("EURO(\"grd\")", Value(340.75));
507 CHECK_EVAL("EURO(\"iep\")", Value(0.787564));
508 CHECK_EVAL("EURO(\"itl\")", Value(1936.27));
509 CHECK_EVAL("EURO(\"lux\")", Value(40.3399));
510 CHECK_EVAL("EURO(\"nlg\")", Value(2.20371));
511 CHECK_EVAL("EURO(\"pte\")", Value(200.482));
512
513 // should still work with mixed-case
514 CHECK_EVAL("EURO(\"Ats\")", Value(13.7603));
515 CHECK_EVAL("EURO(\"Bef\")", Value(40.3399));
516 CHECK_EVAL("EURO(\"Dem\")", Value(1.95583));
517 CHECK_EVAL("EURO(\"Esp\")", Value(166.386));
518 CHECK_EVAL("EURO(\"Eur\")", Value(1.0));
519 CHECK_EVAL("EURO(\"Fim\")", Value(5.94573));
520 CHECK_EVAL("EURO(\"Frf\")", Value(6.55957));
521 CHECK_EVAL("EURO(\"GrD\")", Value(340.75));
522 CHECK_EVAL("EURO(\"IeP\")", Value(0.787564));
523 CHECK_EVAL("EURO(\"Itl\")", Value(1936.27));
524 CHECK_EVAL("EURO(\"luX\")", Value(40.3399));
525 CHECK_EVAL("EURO(\"nlG\")", Value(2.20371));
526 CHECK_EVAL("EURO(\"ptE\")", Value(200.482));
527
528 CHECK_EVAL("EURO(\"NOMANSLAND\")", Value::errorNUM());
529 }
530
531 // Currency conversion using Euro
532 // EUROCONVERT(number,source,target)
testEUROCONVERT()533 void TestFinancialFunctions::testEUROCONVERT()
534 {
535 // 1 Euro to ...
536 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"ATS\")", Value(13.7603));
537 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"BEF\")", Value(40.3399));
538 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"DEM\")", Value(1.95583));
539 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"ESP\")", Value(166.386));
540 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"EUR\")", Value(1.0));
541 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"FIM\")", Value(5.94573));
542 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"FRF\")", Value(6.55957));
543 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"GRD\")", Value(340.75));
544 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"IEP\")", Value(0.787564));
545 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"ITL\")", Value(1936.27));
546 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"LUX\")", Value(40.3399));
547 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"NLG\")", Value(2.20371));
548 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"PTE\")", Value(200.482));
549
550 // identity
551 CHECK_EVAL("EUROCONVERT(1;\"BEF\";\"bef\")", Value(1.0));
552 CHECK_EVAL("EUROCONVERT(1;\"DEM\";\"dem\")", Value(1.0));
553 CHECK_EVAL("EUROCONVERT(1;\"ESP\";\"esp\")", Value(1.0));
554 CHECK_EVAL("EUROCONVERT(1;\"EUR\";\"eur\")", Value(1.0));
555 CHECK_EVAL("EUROCONVERT(1;\"FIM\";\"fim\")", Value(1.0));
556 CHECK_EVAL("EUROCONVERT(1;\"FRF\";\"frf\")", Value(1.0));
557 CHECK_EVAL("EUROCONVERT(1;\"GRD\";\"grd\")", Value(1.0));
558 CHECK_EVAL("EUROCONVERT(1;\"IEP\";\"iep\")", Value(1.0));
559 CHECK_EVAL("EUROCONVERT(1;\"ITL\";\"itl\")", Value(1.0));
560 CHECK_EVAL("EUROCONVERT(1;\"LUX\";\"lux\")", Value(1.0));
561 CHECK_EVAL("EUROCONVERT(1;\"NLG\";\"nlg\")", Value(1.0));
562 CHECK_EVAL("EUROCONVERT(1;\"PTE\";\"pte\")", Value(1.0));
563
564 // all other combinations
565 CHECK_EVAL("EUROCONVERT( 2; \"ATS\"; \"bef\" )", Value(2*40.3399 / 13.7603));
566 CHECK_EVAL("EUROCONVERT( 3; \"ATS\"; \"dem\" )", Value(3*1.95583 / 13.7603));
567 CHECK_EVAL("EUROCONVERT( 4; \"ATS\"; \"esp\" )", Value(4*166.386 / 13.7603));
568 CHECK_EVAL("EUROCONVERT( 5; \"ATS\"; \"eur\" )", Value(5*1 / 13.7603));
569 CHECK_EVAL("EUROCONVERT( 6; \"ATS\"; \"fim\" )", Value(6*5.94573 / 13.7603));
570 CHECK_EVAL("EUROCONVERT( 7; \"ATS\"; \"frf\" )", Value(7*6.55957 / 13.7603));
571 CHECK_EVAL("EUROCONVERT( 8; \"ATS\"; \"grd\" )", Value(8*340.75 / 13.7603));
572 CHECK_EVAL("EUROCONVERT( 9; \"ATS\"; \"iep\" )", Value(9*0.787564 / 13.7603));
573 CHECK_EVAL("EUROCONVERT( 10; \"ATS\"; \"itl\" )", Value(10*1936.27 / 13.7603));
574 CHECK_EVAL("EUROCONVERT( 11; \"ATS\"; \"lux\" )", Value(11*40.3399 / 13.7603));
575 CHECK_EVAL("EUROCONVERT( 12; \"ATS\"; \"nlg\" )", Value(12*2.20371 / 13.7603));
576 CHECK_EVAL("EUROCONVERT( 13; \"ATS\"; \"pte\" )", Value(13*200.482 / 13.7603));
577 CHECK_EVAL("EUROCONVERT( 14; \"BEF\"; \"ats\" )", Value(14*13.7603 / 40.3399));
578 CHECK_EVAL("EUROCONVERT( 15; \"BEF\"; \"dem\" )", Value(15*1.95583 / 40.3399));
579 CHECK_EVAL("EUROCONVERT( 16; \"BEF\"; \"esp\" )", Value(16*166.386 / 40.3399));
580 CHECK_EVAL("EUROCONVERT( 17; \"BEF\"; \"eur\" )", Value(17*1 / 40.3399));
581 CHECK_EVAL("EUROCONVERT( 18; \"BEF\"; \"fim\" )", Value(18*5.94573 / 40.3399));
582 CHECK_EVAL("EUROCONVERT( 19; \"BEF\"; \"frf\" )", Value(19*6.55957 / 40.3399));
583 CHECK_EVAL("EUROCONVERT( 20; \"BEF\"; \"grd\" )", Value(20*340.75 / 40.3399));
584 CHECK_EVAL("EUROCONVERT( 21; \"BEF\"; \"iep\" )", Value(21*0.787564 / 40.3399));
585 CHECK_EVAL("EUROCONVERT( 22; \"BEF\"; \"itl\" )", Value(22*1936.27 / 40.3399));
586 CHECK_EVAL("EUROCONVERT( 23; \"BEF\"; \"lux\" )", Value(23*40.3399 / 40.3399));
587 CHECK_EVAL("EUROCONVERT( 24; \"BEF\"; \"nlg\" )", Value(24*2.20371 / 40.3399));
588 CHECK_EVAL("EUROCONVERT( 25; \"BEF\"; \"pte\" )", Value(25*200.482 / 40.3399));
589 CHECK_EVAL("EUROCONVERT( 26; \"DEM\"; \"ats\" )", Value(26*13.7603 / 1.95583));
590 CHECK_EVAL("EUROCONVERT( 27; \"DEM\"; \"bef\" )", Value(27*40.3399 / 1.95583));
591 CHECK_EVAL("EUROCONVERT( 28; \"DEM\"; \"esp\" )", Value(28*166.386 / 1.95583));
592 CHECK_EVAL("EUROCONVERT( 29; \"DEM\"; \"eur\" )", Value(29*1 / 1.95583));
593 CHECK_EVAL("EUROCONVERT( 30; \"DEM\"; \"fim\" )", Value(30*5.94573 / 1.95583));
594 CHECK_EVAL("EUROCONVERT( 31; \"DEM\"; \"frf\" )", Value(31*6.55957 / 1.95583));
595 CHECK_EVAL("EUROCONVERT( 32; \"DEM\"; \"grd\" )", Value(32*340.75 / 1.95583));
596 CHECK_EVAL("EUROCONVERT( 33; \"DEM\"; \"iep\" )", Value(33*0.787564 / 1.95583));
597 CHECK_EVAL("EUROCONVERT( 34; \"DEM\"; \"itl\" )", Value(34*1936.27 / 1.95583));
598 CHECK_EVAL("EUROCONVERT( 35; \"DEM\"; \"lux\" )", Value(35*40.3399 / 1.95583));
599 CHECK_EVAL("EUROCONVERT( 36; \"DEM\"; \"nlg\" )", Value(36*2.20371 / 1.95583));
600 CHECK_EVAL("EUROCONVERT( 37; \"DEM\"; \"pte\" )", Value(37*200.482 / 1.95583));
601 CHECK_EVAL("EUROCONVERT( 38; \"ESP\"; \"ats\" )", Value(38*13.7603 / 166.386));
602 CHECK_EVAL("EUROCONVERT( 39; \"ESP\"; \"bef\" )", Value(39*40.3399 / 166.386));
603 CHECK_EVAL("EUROCONVERT( 40; \"ESP\"; \"dem\" )", Value(40*1.95583 / 166.386));
604 CHECK_EVAL("EUROCONVERT( 41; \"ESP\"; \"eur\" )", Value(41*1 / 166.386));
605 CHECK_EVAL("EUROCONVERT( 42; \"ESP\"; \"fim\" )", Value(42*5.94573 / 166.386));
606 CHECK_EVAL("EUROCONVERT( 43; \"ESP\"; \"frf\" )", Value(43*6.55957 / 166.386));
607 CHECK_EVAL("EUROCONVERT( 44; \"ESP\"; \"grd\" )", Value(44*340.75 / 166.386));
608 CHECK_EVAL("EUROCONVERT( 45; \"ESP\"; \"iep\" )", Value(45*0.787564 / 166.386));
609 CHECK_EVAL("EUROCONVERT( 46; \"ESP\"; \"itl\" )", Value(46*1936.27 / 166.386));
610 CHECK_EVAL("EUROCONVERT( 47; \"ESP\"; \"lux\" )", Value(47*40.3399 / 166.386));
611 CHECK_EVAL("EUROCONVERT( 48; \"ESP\"; \"nlg\" )", Value(48*2.20371 / 166.386));
612 CHECK_EVAL("EUROCONVERT( 49; \"ESP\"; \"pte\" )", Value(49*200.482 / 166.386));
613 CHECK_EVAL("EUROCONVERT( 50; \"EUR\"; \"ats\" )", Value(50*13.7603 / 1));
614 CHECK_EVAL("EUROCONVERT( 51; \"EUR\"; \"bef\" )", Value(51*40.3399 / 1));
615 CHECK_EVAL("EUROCONVERT( 52; \"EUR\"; \"dem\" )", Value(52*1.95583 / 1));
616 CHECK_EVAL("EUROCONVERT( 53; \"EUR\"; \"esp\" )", Value(53*166.386 / 1));
617 CHECK_EVAL("EUROCONVERT( 54; \"EUR\"; \"fim\" )", Value(54*5.94573 / 1));
618 CHECK_EVAL("EUROCONVERT( 55; \"EUR\"; \"frf\" )", Value(55*6.55957 / 1));
619 CHECK_EVAL("EUROCONVERT( 56; \"EUR\"; \"grd\" )", Value(56*340.75 / 1));
620 CHECK_EVAL("EUROCONVERT( 57; \"EUR\"; \"iep\" )", Value(57*0.787564 / 1));
621 CHECK_EVAL("EUROCONVERT( 58; \"EUR\"; \"itl\" )", Value(58*1936.27 / 1));
622 CHECK_EVAL("EUROCONVERT( 59; \"EUR\"; \"lux\" )", Value(59*40.3399 / 1));
623 CHECK_EVAL("EUROCONVERT( 60; \"EUR\"; \"nlg\" )", Value(60*2.20371 / 1));
624 CHECK_EVAL("EUROCONVERT( 61; \"EUR\"; \"pte\" )", Value(61*200.482 / 1));
625 CHECK_EVAL("EUROCONVERT( 62; \"FIM\"; \"ats\" )", Value(62*13.7603 / 5.94573));
626 CHECK_EVAL("EUROCONVERT( 63; \"FIM\"; \"bef\" )", Value(63*40.3399 / 5.94573));
627 CHECK_EVAL("EUROCONVERT( 64; \"FIM\"; \"dem\" )", Value(64*1.95583 / 5.94573));
628 CHECK_EVAL("EUROCONVERT( 65; \"FIM\"; \"esp\" )", Value(65*166.386 / 5.94573));
629 CHECK_EVAL("EUROCONVERT( 66; \"FIM\"; \"eur\" )", Value(66*1 / 5.94573));
630 CHECK_EVAL("EUROCONVERT( 67; \"FIM\"; \"frf\" )", Value(67*6.55957 / 5.94573));
631 CHECK_EVAL("EUROCONVERT( 68; \"FIM\"; \"grd\" )", Value(68*340.75 / 5.94573));
632 CHECK_EVAL("EUROCONVERT( 69; \"FIM\"; \"iep\" )", Value(69*0.787564 / 5.94573));
633 CHECK_EVAL("EUROCONVERT( 70; \"FIM\"; \"itl\" )", Value(70*1936.27 / 5.94573));
634 CHECK_EVAL("EUROCONVERT( 71; \"FIM\"; \"lux\" )", Value(71*40.3399 / 5.94573));
635 CHECK_EVAL("EUROCONVERT( 72; \"FIM\"; \"nlg\" )", Value(72*2.20371 / 5.94573));
636 CHECK_EVAL("EUROCONVERT( 73; \"FIM\"; \"pte\" )", Value(73*200.482 / 5.94573));
637 CHECK_EVAL("EUROCONVERT( 74; \"FRF\"; \"ats\" )", Value(74*13.7603 / 6.55957));
638 CHECK_EVAL("EUROCONVERT( 75; \"FRF\"; \"bef\" )", Value(75*40.3399 / 6.55957));
639 CHECK_EVAL("EUROCONVERT( 76; \"FRF\"; \"dem\" )", Value(76*1.95583 / 6.55957));
640 CHECK_EVAL("EUROCONVERT( 77; \"FRF\"; \"esp\" )", Value(77*166.386 / 6.55957));
641 CHECK_EVAL("EUROCONVERT( 78; \"FRF\"; \"eur\" )", Value(78*1 / 6.55957));
642 CHECK_EVAL("EUROCONVERT( 79; \"FRF\"; \"fim\" )", Value(79*5.94573 / 6.55957));
643 CHECK_EVAL("EUROCONVERT( 80; \"FRF\"; \"grd\" )", Value(80*340.75 / 6.55957));
644 CHECK_EVAL("EUROCONVERT( 81; \"FRF\"; \"iep\" )", Value(81*0.787564 / 6.55957));
645 CHECK_EVAL("EUROCONVERT( 82; \"FRF\"; \"itl\" )", Value(82*1936.27 / 6.55957));
646 CHECK_EVAL("EUROCONVERT( 83; \"FRF\"; \"lux\" )", Value(83*40.3399 / 6.55957));
647 CHECK_EVAL("EUROCONVERT( 84; \"FRF\"; \"nlg\" )", Value(84*2.20371 / 6.55957));
648 CHECK_EVAL("EUROCONVERT( 85; \"FRF\"; \"pte\" )", Value(85*200.482 / 6.55957));
649 CHECK_EVAL("EUROCONVERT( 86; \"GRD\"; \"ats\" )", Value(86*13.7603 / 340.75));
650 CHECK_EVAL("EUROCONVERT( 87; \"GRD\"; \"bef\" )", Value(87*40.3399 / 340.75));
651 CHECK_EVAL("EUROCONVERT( 88; \"GRD\"; \"dem\" )", Value(88*1.95583 / 340.75));
652 CHECK_EVAL("EUROCONVERT( 89; \"GRD\"; \"esp\" )", Value(89*166.386 / 340.75));
653 CHECK_EVAL("EUROCONVERT( 90; \"GRD\"; \"eur\" )", Value(90*1 / 340.75));
654 CHECK_EVAL("EUROCONVERT( 91; \"GRD\"; \"fim\" )", Value(91*5.94573 / 340.75));
655 CHECK_EVAL("EUROCONVERT( 92; \"GRD\"; \"frf\" )", Value(92*6.55957 / 340.75));
656 CHECK_EVAL("EUROCONVERT( 93; \"GRD\"; \"iep\" )", Value(93*0.787564 / 340.75));
657 CHECK_EVAL("EUROCONVERT( 94; \"GRD\"; \"itl\" )", Value(94*1936.27 / 340.75));
658 CHECK_EVAL("EUROCONVERT( 95; \"GRD\"; \"lux\" )", Value(95*40.3399 / 340.75));
659 CHECK_EVAL("EUROCONVERT( 96; \"GRD\"; \"nlg\" )", Value(96*2.20371 / 340.75));
660 CHECK_EVAL("EUROCONVERT( 97; \"GRD\"; \"pte\" )", Value(97*200.482 / 340.75));
661 CHECK_EVAL("EUROCONVERT( 98; \"IEP\"; \"ats\" )", Value(98*13.7603 / 0.787564));
662 CHECK_EVAL("EUROCONVERT( 99; \"IEP\"; \"bef\" )", Value(99*40.3399 / 0.787564));
663 CHECK_EVAL("EUROCONVERT( 100; \"IEP\"; \"dem\" )", Value(100*1.95583 / 0.787564));
664 CHECK_EVAL("EUROCONVERT( 101; \"IEP\"; \"esp\" )", Value(101*166.386 / 0.787564));
665 CHECK_EVAL("EUROCONVERT( 102; \"IEP\"; \"eur\" )", Value(102*1 / 0.787564));
666 CHECK_EVAL("EUROCONVERT( 103; \"IEP\"; \"fim\" )", Value(103*5.94573 / 0.787564));
667 CHECK_EVAL("EUROCONVERT( 104; \"IEP\"; \"frf\" )", Value(104*6.55957 / 0.787564));
668 CHECK_EVAL("EUROCONVERT( 105; \"IEP\"; \"grd\" )", Value(105*340.75 / 0.787564));
669 CHECK_EVAL("EUROCONVERT( 106; \"IEP\"; \"itl\" )", Value(106*1936.27 / 0.787564));
670 CHECK_EVAL("EUROCONVERT( 107; \"IEP\"; \"lux\" )", Value(107*40.3399 / 0.787564));
671 CHECK_EVAL("EUROCONVERT( 108; \"IEP\"; \"nlg\" )", Value(108*2.20371 / 0.787564));
672 CHECK_EVAL("EUROCONVERT( 109; \"IEP\"; \"pte\" )", Value(109*200.482 / 0.787564));
673 CHECK_EVAL("EUROCONVERT( 110; \"ITL\"; \"ats\" )", Value(110*13.7603 / 1936.27));
674 CHECK_EVAL("EUROCONVERT( 111; \"ITL\"; \"bef\" )", Value(111*40.3399 / 1936.27));
675 CHECK_EVAL("EUROCONVERT( 112; \"ITL\"; \"dem\" )", Value(112*1.95583 / 1936.27));
676 CHECK_EVAL("EUROCONVERT( 113; \"ITL\"; \"esp\" )", Value(113*166.386 / 1936.27));
677 CHECK_EVAL("EUROCONVERT( 114; \"ITL\"; \"eur\" )", Value(114*1 / 1936.27));
678 CHECK_EVAL("EUROCONVERT( 115; \"ITL\"; \"fim\" )", Value(115*5.94573 / 1936.27));
679 CHECK_EVAL("EUROCONVERT( 116; \"ITL\"; \"frf\" )", Value(116*6.55957 / 1936.27));
680 CHECK_EVAL("EUROCONVERT( 117; \"ITL\"; \"grd\" )", Value(117*340.75 / 1936.27));
681 CHECK_EVAL("EUROCONVERT( 118; \"ITL\"; \"iep\" )", Value(118*0.787564 / 1936.27));
682 CHECK_EVAL("EUROCONVERT( 119; \"ITL\"; \"lux\" )", Value(119*40.3399 / 1936.27));
683 CHECK_EVAL("EUROCONVERT( 120; \"ITL\"; \"nlg\" )", Value(120*2.20371 / 1936.27));
684 CHECK_EVAL("EUROCONVERT( 121; \"ITL\"; \"pte\" )", Value(121*200.482 / 1936.27));
685 CHECK_EVAL("EUROCONVERT( 122; \"LUX\"; \"ats\" )", Value(122*13.7603 / 40.3399));
686 CHECK_EVAL("EUROCONVERT( 123; \"LUX\"; \"bef\" )", Value(123*40.3399 / 40.3399));
687 CHECK_EVAL("EUROCONVERT( 124; \"LUX\"; \"dem\" )", Value(124*1.95583 / 40.3399));
688 CHECK_EVAL("EUROCONVERT( 125; \"LUX\"; \"esp\" )", Value(125*166.386 / 40.3399));
689 CHECK_EVAL("EUROCONVERT( 126; \"LUX\"; \"eur\" )", Value(126*1 / 40.3399));
690 CHECK_EVAL("EUROCONVERT( 127; \"LUX\"; \"fim\" )", Value(127*5.94573 / 40.3399));
691 CHECK_EVAL("EUROCONVERT( 128; \"LUX\"; \"frf\" )", Value(128*6.55957 / 40.3399));
692 CHECK_EVAL("EUROCONVERT( 129; \"LUX\"; \"grd\" )", Value(129*340.75 / 40.3399));
693 CHECK_EVAL("EUROCONVERT( 130; \"LUX\"; \"iep\" )", Value(130*0.787564 / 40.3399));
694 CHECK_EVAL("EUROCONVERT( 131; \"LUX\"; \"itl\" )", Value(131*1936.27 / 40.3399));
695 CHECK_EVAL("EUROCONVERT( 132; \"LUX\"; \"nlg\" )", Value(132*2.20371 / 40.3399));
696 CHECK_EVAL("EUROCONVERT( 133; \"LUX\"; \"pte\" )", Value(133*200.482 / 40.3399));
697 CHECK_EVAL("EUROCONVERT( 134; \"NLG\"; \"ats\" )", Value(134*13.7603 / 2.20371));
698 CHECK_EVAL("EUROCONVERT( 135; \"NLG\"; \"bef\" )", Value(135*40.3399 / 2.20371));
699 CHECK_EVAL("EUROCONVERT( 136; \"NLG\"; \"dem\" )", Value(136*1.95583 / 2.20371));
700 CHECK_EVAL("EUROCONVERT( 137; \"NLG\"; \"esp\" )", Value(137*166.386 / 2.20371));
701 CHECK_EVAL("EUROCONVERT( 138; \"NLG\"; \"eur\" )", Value(138*1 / 2.20371));
702 CHECK_EVAL("EUROCONVERT( 139; \"NLG\"; \"fim\" )", Value(139*5.94573 / 2.20371));
703 CHECK_EVAL("EUROCONVERT( 140; \"NLG\"; \"frf\" )", Value(140*6.55957 / 2.20371));
704 CHECK_EVAL("EUROCONVERT( 141; \"NLG\"; \"grd\" )", Value(141*340.75 / 2.20371));
705 CHECK_EVAL("EUROCONVERT( 142; \"NLG\"; \"iep\" )", Value(142*0.787564 / 2.20371));
706 CHECK_EVAL("EUROCONVERT( 143; \"NLG\"; \"itl\" )", Value(143*1936.27 / 2.20371));
707 CHECK_EVAL("EUROCONVERT( 144; \"NLG\"; \"lux\" )", Value(144*40.3399 / 2.20371));
708 CHECK_EVAL("EUROCONVERT( 145; \"NLG\"; \"pte\" )", Value(145*200.482 / 2.20371));
709 CHECK_EVAL("EUROCONVERT( 146; \"PTE\"; \"ats\" )", Value(146*13.7603 / 200.482));
710 CHECK_EVAL("EUROCONVERT( 147; \"PTE\"; \"bef\" )", Value(147*40.3399 / 200.482));
711 CHECK_EVAL("EUROCONVERT( 148; \"PTE\"; \"dem\" )", Value(148*1.95583 / 200.482));
712 CHECK_EVAL("EUROCONVERT( 149; \"PTE\"; \"esp\" )", Value(149*166.386 / 200.482));
713 CHECK_EVAL("EUROCONVERT( 150; \"PTE\"; \"eur\" )", Value(150*1 / 200.482));
714 CHECK_EVAL("EUROCONVERT( 151; \"PTE\"; \"fim\" )", Value(151*5.94573 / 200.482));
715 CHECK_EVAL("EUROCONVERT( 152; \"PTE\"; \"frf\" )", Value(152*6.55957 / 200.482));
716 CHECK_EVAL("EUROCONVERT( 153; \"PTE\"; \"grd\" )", Value(153*340.75 / 200.482));
717 CHECK_EVAL("EUROCONVERT( 154; \"PTE\"; \"iep\" )", Value(154*0.787564 / 200.482));
718 CHECK_EVAL("EUROCONVERT( 155; \"PTE\"; \"itl\" )", Value(155*1936.27 / 200.482));
719 CHECK_EVAL("EUROCONVERT( 156; \"PTE\"; \"lux\" )", Value(156*40.3399 / 200.482));
720 CHECK_EVAL("EUROCONVERT( 157; \"PTE\"; \"nlg\" )", Value(157*2.20371 / 200.482));
721 }
722
723 // FV
testFV()724 void TestFinancialFunctions::testFV()
725 {
726 // ODF
727 CHECK_EVAL("FV(10%;12;-100;100)" , Value(1824.5855390489)); // A trivial example of FV.
728 }
729
730 // FVSCHEDULE
testFVSCHEDULE()731 void TestFinancialFunctions::testFVSCHEDULE()
732 {
733 // ODF
734 CHECK_EVAL_SHORT("FVSCHEDULE(1000000; {0.03; 0.04; 0.05})" , Value(1124760)); // A trivial example of FVSCHEDULE.
735 // alternate function name
736 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETFVSCHEDULE(1000000; {0.03; 0.04; 0.05})" , Value(1124760));
737 }
738
739 // INTRATE
testINTRATE()740 void TestFinancialFunctions::testINTRATE()
741 {
742 // ODF
743 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.
744 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.
745 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 50)" , Value::errorVALUE()); // Unknown Basis returns Error.
746 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 0 )" , Value(0.1498127341)); // An example of INTRATE.
747 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000 )" , Value(0.1498127341)); // Basis defaults to 0.
748 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 1 )" , Value(0.1497128794)); //
749 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 2 )" , Value(0.1476620180)); //
750 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 3 )" , Value(0.1497128794)); //
751 CHECK_EVAL_SHORT("INTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 4 )" , Value(0.1498127341)); //
752 // alternate function name
753 CHECK_EVAL_SHORT("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETINTRATE( DATE(1995;10;5); DATE(2002; 6;8); 100000; 200000; 0 )" , Value(0.1498127341));
754 }
755
756 // IPMT
testIPMT()757 void TestFinancialFunctions::testIPMT()
758 {
759 // ODF
760 CHECK_EVAL_SHORT("IPMT(5%/12;10;360;100000)", Value(-412.0850243)); // An example of IPMT. The interest payment on a 100000 unit loan
761 // in the 10th month of a 30 year loan at 5% annual interest.
762 CHECK_EVAL_SHORT("IPMT(5%/12;10;360;100000;0;1)", Value(-410.3751278993)); // Payments at the beginning of each period.
763
764 // The total payment is the principle plus the interest.
765 CHECK_EVAL_SHORT("PPMT(5%/12;10;360;100000)+IPMT(5%/12;10;360;100000)-PMT(5%/12;360;100000)", Value(0));
766 }
767
768 // ISPMT
testISPMT()769 void TestFinancialFunctions::testISPMT()
770 {
771 // betersolutions
772 CHECK_EVAL("ISPMT(10%/12;1 ;36;8000000)", Value(-64814.8148148148)); //
773 CHECK_EVAL("ISPMT(10% ;1 ;3 ;8000000)", Value(-533333.3333333333)); //
774
775 // ODF
776 CHECK_EVAL("ISPMT(5%/12;12;360;100000)", Value(-402.7777777778)); // A trivial example of ISPMT. A 100000 unit investment with an
777 // annual interest rate of 5% and a 30 year term has an interest payment
778 // of 402.78 units in month 12.
779 }
780
781 // Level-coupon bond
782 // LEVEL_COUPON(faceValue; couponRate; couponsPerYear; years; marketRate)
testLEVELCOUPON()783 void TestFinancialFunctions::testLEVELCOUPON()
784 {
785 CHECK_EVAL("LEVEL_COUPON(1000; .13; 1; 4; .1)", Value(1095.0959633904788));
786 CHECK_EVAL("LEVEL_COUPON(1000; .13; 2; 4; .1)", Value(1096.9481913913939));
787 CHECK_EVAL("LEVEL_COUPON(1000; .10; 1; 10; .25)", Value(464.4245094400000));
788 CHECK_EVAL("LEVEL_COUPON(1000; .12; 1; 10; .25)", Value(535.8345748480000));
789 CHECK_EVAL("LEVEL_COUPON(1000; .20; 1; 10; .25)", Value(821.4748364800000));
790 }
791
792 // MDURATION
testMDURATION()793 void TestFinancialFunctions::testMDURATION()
794 {
795 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,
796 // and intentionally end on May 31, which
797 // illustrates the differences between
798 // many bases
799 // alternate function name
800 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETMDURATION(\"2004-02-01\"; \"2004-05-31\"; 0.08; 0.09; 2; 0)" , Value(0.3189792663));
801 }
802
803 // MIRR
testMIRR()804 void TestFinancialFunctions::testMIRR()
805 {
806 // ODF
807 CHECK_EVAL("MIRR({100;200;-50;300;-200}; 5%; 6%)", Value(0.342823387842));
808
809 // bettersolutions.com
810 CHECK_EVAL("MIRR({-10;30;20;10;20};0.1;0.12)", Value(0.7712844619));
811 CHECK_EVAL("MIRR({-100;30;30;30;30};0.1;1)", Value(0.4564753151));
812 CHECK_EVAL("MIRR({-50;20;40;70};10/100;12/100)", Value(0.4090837902));
813 CHECK_EVAL("MIRR({-5;1;2;3;4};10/100;0.12)", Value(0.2253901556));
814 CHECK_EVAL("MIRR({1000;1100;1200;1500;1600};10%;12%)", Value(Value::errorDIV0()));
815 }
816
817 // Yearly nominal interest rate
818 // NOMINAL(effectiveRate, periods)
testNOMINAL()819 void TestFinancialFunctions::testNOMINAL()
820 {
821 CHECK_EVAL("NOMINAL(13.5%; 12)", Value(0.1273031669590416));
822 CHECK_EVAL("NOMINAL(13.5%; 12)", Value(0.1273031669590416));
823 CHECK_EVAL("NOMINAL(25%; 12)", Value(0.2252311814580734));
824 CHECK_EVAL("NOMINAL(25%; 4)", Value(0.2294850537622564));
825 CHECK_EVAL("NOMINAL(20%; 12)", Value(0.1837136459967743));
826 CHECK_EVAL("NOMINAL(10%; 12)", Value(0.0956896851468452));
827
828 // rate must be positive
829 CHECK_EVAL("NOMINAL(0; 12)", Value::errorVALUE());
830
831 // periods must be positive
832 CHECK_EVAL("NOMINAL(10%; 0)", Value::errorDIV0());
833 CHECK_EVAL("NOMINAL(10%; -1)", Value::errorVALUE());
834 CHECK_EVAL("NOMINAL(10%; -2)", Value::errorVALUE());
835
836 // test cases in OpenFormula specification
837 CHECK_EVAL("NOMINAL(8%;4)", Value(0.0777061876330940));
838 CHECK_EVAL("NOMINAL(12.5%;12)", Value(0.118362966638538));
839 CHECK_EVAL("NOMINAL(1%;2)", Value(0.00997512422417790));
840
841 // alternate function name
842 CHECK_EVAL("COM.SUN.STAR.SHEET.ADDIN.ANALYSIS.GETNOMINAL(8%;4)", Value(0.0777061876330940));
843 }
844
845 // NPER
testNPER()846 void TestFinancialFunctions::testNPER()
847 {
848 // ODF
849 CHECK_EVAL_SHORT("NPER(5% ;-100;1000)", Value(14.2067)); // A trivial example of NPER.
850 CHECK_EVAL_SHORT("NPER(5% ;-100;1000;100)", Value(15.2067)); // A trivial example of NPER with non-zero FV.
851 CHECK_EVAL_SHORT("NPER(5% ;-100;1000;100;1)", Value(14.2067)); // A trivial example of NPER with non-zero FV and PayType.
852 CHECK_EVAL_SHORT("NPER(0 ;-100;1000)", Value(10.0000)); // TODO Rate can be zero.
853 CHECK_EVAL_SHORT("NPER(-1%;-100;1000)", Value(9.483283066)); // TODO Rate can be negative.
854 }
855
856 // Net present value
857 // NPV(rate, values)
testNPV()858 void TestFinancialFunctions::testNPV()
859 {
860 CHECK_EVAL("NPV(100%; 4; 5; 7)", Value(4.125));
861 CHECK_EVAL("NPV(10%; 100; 200)", Value(256.198347107438));
862 }
863
864 // ODDLPRICE
testODDLPRICE()865 void TestFinancialFunctions::testODDLPRICE()
866 {
867 // ODF tests. Not all tests pass, but I'm not sure if the spec or the implementation is wrong
868 CHECK_EVAL_SHORT( "ODDLPRICE(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);3%;5%;100;2)", Value( 90.9975570033 ) ); //
869 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
870 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
871 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
872 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
873 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
874 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
875 QEXPECT_FAIL("", "Wrong?", Continue);
876 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
877 QEXPECT_FAIL("", "Wrong?", Continue);
878 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
879 QEXPECT_FAIL("", "Wrong?", Continue);
880 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
881 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
882 QEXPECT_FAIL("", "Wrong?", Continue);
883 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
884 QEXPECT_FAIL("", "Wrong?", Continue);
885 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
886 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
887 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
888 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
889 }
890
891 // ODDLYIELD
testODDLYIELD()892 void TestFinancialFunctions::testODDLYIELD()
893 {
894 // ODF tests
895
896 // Basis 0
897 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
898 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
899 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
900 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
901
902 // Basis 1
903 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
904 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
905 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
906
907 // Basis 2
908 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
909 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
910 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
911
912 // Basis 3
913 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
914 QEXPECT_FAIL("", "Wrong?", Continue);
915 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
916 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
917
918 // Basis 4
919 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
920 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
921 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
922 }
923
924 // PDURATION
testPDURATION()925 void TestFinancialFunctions::testPDURATION()
926 {
927 // is DURATION in Calligra Sheets
928 CHECK_EVAL_SHORT("PDURATION( 0.1; 10; 100 )" , Value(24.158858)); // simple use case
929 CHECK_EVAL_SHORT("PDURATION( 0.1; 100; 10 )" , Value(-24.158858)); // currentValue > desiredValue
930 CHECK_EVAL_SHORT("PDURATION( 0; 10; 11 )" , Value::errorVALUE()); // rate > 0
931 CHECK_EVAL_SHORT("PDURATION( 0.1; 0; 11 )" , Value::errorVALUE()); // currentValue > 0
932 CHECK_EVAL_SHORT("PDURATION( 0.1; 10; 0 )" , Value::errorVALUE()); // desiredValue > 0
933 }
934
935 // PMT
testPMT()936 void TestFinancialFunctions::testPMT()
937 {
938 // ODF
939 CHECK_EVAL_SHORT("PMT(5%;12;1000)", Value(-112.8254100208)); // A trivial example of PMT.
940 CHECK_EVAL_SHORT("PMT(5%;12;1000;100)", Value(-119.1079510229)); // A trivial example of PMT with non-zero FV.
941 CHECK_EVAL_SHORT("PMT(5%;12;1000;100;1)", Value(-113.4361438313)); // A trivial example of PMT with non-zero FV and PayType.
942 CHECK_EVAL_SHORT("PMT(0;10;1000)", Value(-100.00000)); // TODO Rate can be zero.
943 }
944
945 // PPMT
testPPMT()946 void TestFinancialFunctions::testPPMT()
947 {
948 // bettersolution.com
949 CHECK_EVAL("PPMT(10%/12;1;24;2000)", Value(-75.6231860084)); // A simple test case
950 CHECK_EVAL("PPMT(8%;10;10;200000)", Value(-27598.0534624214)); // A simple test case
951
952 // ODF
953 CHECK_EVAL("PPMT(3%;1;12;100)", Value(-7.0462085473)); // A simple test case
954 CHECK_EVAL("PPMT(8%;5;24;10000;0)", Value(-203.7735140493)); // With nPer=5 and Future=0
955 CHECK_EVAL("PPMT(8%;10;24;10000;2000)", Value(-359.2921746011)); // With nPer=10 and Future=2000
956 CHECK_EVAL("PPMT(8%;10;24;10000;2000;1)", Value(-332.6779394454)); // With Type=1
957
958 // these tests seems to be wrong in specs. remove superfluous parameter "1".
959 CHECK_EVAL("PPMT(3%;1;12;100;200)", Value(-21.1386256419)); // With future value
960 CHECK_EVAL("PPMT(3%;1;12;100;200;1)", Value(-20.5229375164)); // With future value and type
961 }
962
963 // PRICEMAT
testPRICEMAT()964 void TestFinancialFunctions::testPRICEMAT()
965 {
966 // ODF - TODO expand to 10 signif.
967 CHECK_EVAL_SHORT("PRICEMAT(DATE(1990;6;1);DATE(1995;12;31);DATE(1990;1;1);6%;5% )", Value(103.819218241)); // Without Basis parameter
968 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
969 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
970 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
971 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
972 CHECK_EVAL_SHORT("PRICEMAT(DATE(1990;6;1);DATE(1992;12;31);DATE(1990;1;1);3%;2%;0)", Value(102.395007924)); //
973 CHECK_EVAL_SHORT("PRICEMAT(DATE(1990;6;1);DATE(1992;12;31);DATE(1990;1;1);5%;3%;2)", Value(104.709020052)); //
974 // alternate function name
975 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));
976 }
977
978 // PV
testPV()979 void TestFinancialFunctions::testPV()
980 {
981 // TODO check type > 1, check div0
982 // ODF
983 CHECK_EVAL_SHORT("PV(10%;12;-100;100)", Value(649.5061005186)); // A trivial example of PV.
984 }
985
986 // PV_ANNUITY
testPV_ANNUITY()987 void TestFinancialFunctions::testPV_ANNUITY()
988 {
989 // Calligra Sheets
990 CHECK_EVAL_SHORT("PV_ANNUITY(1000;0.05;5)", Value(4329.47667063));
991 }
992
993 // RATE
testRATE()994 void TestFinancialFunctions::testRATE()
995 {
996 CHECK_EVAL_SHORT("RATE(4*12;-200;8000)", Value(0.00770147));
997 }
998
999 // RECEIVED
testRECEIVED()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
testRRI()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)
testSLN()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)
testSYD()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
testTBILLEQ()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
testTBILLPRICE()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
testTBILLYIELD()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
testVDB()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
testXIRR()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
testXNPV()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
testYIELDDISC()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
testYIELDMAT()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)
testZEROCOUPON()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)
1194