?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Sheet: add new sheet_date_conv convenience function.
[gnumeric.git] / plugins / fn-financial / functions.c
blobc3c7428ba02e5c25841cab625ab7fee99c88e4c0
1 /* vim: set sw=8: -*- Mode: C; tab-width: 8; indent-tabs-mode: t; c-basic-offset: 8 -*- */
2 /*
3 * fn-financial.c: Built in financial functions and functions registration
4 *
5 * Authors:
6 * Jukka-Pekka Iivonen (jiivonen@hutcs.cs.hut.fi)
7 * Morten Welinder (terra@gnome.org)
8 * Vladimir Vuksan (vuksan@veus.hr)
9 * Andreas J. Guelzow (aguelzow@pyrshep.ca)
10 *
11 * This program is free software; you can redistribute it and/or modify
12 * it under the terms of the GNU General Public License as published by
13 * the Free Software Foundation; either version 2 of the License, or
14 * (at your option) any later version.
15 *
16 * This program is distributed in the hope that it will be useful,
17 * but WITHOUT ANY WARRANTY; without even the implied warranty of
18 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19 * GNU General Public License for more details.
20 *
21 * You should have received a copy of the GNU General Public License
22 * along with this program; if not, see <https://www.gnu.org/licenses/>.
23 */
24 #include <gnumeric-config.h>
25 #include <gnumeric.h>
26 #include <func.h>
27 #include <parse-util.h>
28 #include <cell.h>
29 #include <tools/goal-seek.h>
30 #include <collect.h>
31 #include <value.h>
32 #include <mathfunc.h>
33 #include <gnm-format.h>
34 #include <workbook.h>
35 #include <sheet.h>
36 #include <gnm-datetime.h>
37 #include <gnm-i18n.h>
38 #include <goffice/goffice.h>
39 #include <gnm-plugin.h>
40
41 #include <math.h>
42 #include <limits.h>
43 #include <string.h>
44
45 #include "sc-fin.h"
46
47 GNM_PLUGIN_MODULE_HEADER;
48
49 /***************************************************************************/
50
51 #define FREQ_HELP \
52 { GNM_FUNC_HELP_NOTE, F_("@{frequency} may be 1 (annual), 2 (semi-annual), or 4 (quarterly).") }
53
54 #define TYPE_HELP \
55 { GNM_FUNC_HELP_NOTE, F_("If @{type} is 0, the default, payment is at the end of each period. If @{type} is 1, payment is at the beginning of each period.") }
56
57
58 /***************************************************************************/
59
60 #define is_valid_basis(B) ((B) >= 0 && (B) <= 5)
61 #define is_valid_freq(F) ((F) == 1 || (F) == 2 || (F) == 4)
62 #define is_valid_paytype(t) ((t) == 0 || (t) == 1)
63
64 static int
65 value_get_basis (GnmValue const *v, int defalt)
66 {
67 if (v) {
68 gnm_float b = value_get_as_float (v);
69
70 if (b < 0 || b >= 6)
71 return -1;
72 return (int)b;
73 } else
74 return defalt;
75 }
76
77 static int
78 value_get_freq (GnmValue const *v)
79 {
80 gnm_float f;
81
82 g_return_val_if_fail (v != NULL, -1);
83
84 f = value_get_as_float (v);
85 if (f < 1 || f >= 5)
86 return -1;
87 else {
88 int i = (int)f;
89 return i == 3 ? -1 : i;
90 }
91 }
92
93 static int
94 value_get_paytype (GnmValue const *v)
95 {
96 return (v == NULL || value_is_zero (v)) ? 0 : 1;
97 }
98
99 /***************************************************************************
100 *
101 *
102 * Below are some of the functions that are used quite often in
103 * financial analysis.
104 *
105 * Present value interest factor
106 *
107 * PVIF = (1 + k) ^ n
108 *
109 * Future value interest factor
110 *
111 * FVIF = 1 / PVIF
112 *
113 * Present value interest factor of annuities
114 *
115 * 1 1
116 * PVIFA = --- - -----------
117 * k k*(1+k)^n
118 *
119 * Future value interest factor of annuities
120 *
121 * (1+k)^n - 1
122 * FVIFA = ----------------
123 * k
124 *
125 *
126 *
127 * PV * PVIF(k%, nper) + PMT * ( 1 + rate * type ) *
128 * FVIFA(k%, nper) + FV = 0
129 *
130 */
131
132 static gnm_float
133 calculate_pvif (gnm_float rate, gnm_float nper)
134 {
135 return pow1p (rate, nper);
136 }
137
138 static gnm_float
139 calculate_fvifa (gnm_float rate, gnm_float nper)
140 {
141 /* Removable singularity at rate == 0. */
142 if (rate == 0)
143 return nper;
144 else
145 return pow1pm1 (rate, nper) / rate;
146 }
147
148
149 static gnm_float
150 calculate_pmt (gnm_float rate, gnm_float nper, gnm_float pv, gnm_float fv,
151 int type)
152 {
153 gnm_float pvif, fvifa;
154
155 /* Calculate the PVIF and FVIFA */
156
157 pvif = calculate_pvif (rate, nper);
158 fvifa = calculate_fvifa (rate, nper);
159
160 return ((-pv * pvif - fv ) / ((1.0 + rate * type) * fvifa));
161 }
162
163 static gnm_float
164 calculate_ipmt (gnm_float rate, gnm_float per, gnm_float nper,
165 gnm_float pv, gnm_float fv, int type)
166 {
167 gnm_float pmt = calculate_pmt (rate, nper, pv, fv, /*type*/ 0);
168 gnm_float ipmt = -(pv * pow1p (rate, per - 1) * rate +
169 pmt * pow1pm1 (rate, per - 1));
170
171 return (type == 0) ? ipmt : ipmt / (1 + rate);
172 }
173
174 /***************************************************************************/
175
176 /* Returns the number of days between issue date and maturity date
177 * accoring to the day counting system specified by the 'basis'
178 * argument. Basis may have one of the following values:
179 *
180 * 0 for US 30/360 (days in a month/days in a year)
181 * 1 for actual days/actual days
182 * 2 for actual days/360
183 * 3 for actual days/365
184 * 4 for European 30/360
185 *
186 */
187 static int
188 days_monthly_basis (GnmValue const *issue_date,
189 GnmValue const *maturity_date,
190 int basis, GODateConventions const *date_conv)
191 {
192 GDate date_i, date_m;
193 int issue_day, issue_month, issue_year;
194 int maturity_day, maturity_month, maturity_year;
195 int months, days, years;
196 gboolean leap_year;
197 int maturity, issue;
198
199 if (!datetime_value_to_g (&date_i, issue_date, date_conv) ||
200 !datetime_value_to_g (&date_m, maturity_date, date_conv))
201 return -1;
202
203 issue_year = g_date_get_year (&date_i);
204 issue_month = g_date_get_month (&date_i);
205 issue_day = g_date_get_day (&date_i);
206 maturity_year = g_date_get_year (&date_m);
207 maturity_month = g_date_get_month (&date_m);
208 maturity_day = g_date_get_day (&date_m);
209
210 years = maturity_year - issue_year;
211 months = maturity_month - issue_month;
212 days = maturity_day - issue_day;
213
214 months = years * 12 + months;
215 leap_year = g_date_is_leap_year (issue_year);
216
217 switch (basis) {
218 case 0:
219 if (issue_month == 2 && maturity_month != 2 &&
220 issue_year == maturity_year) {
221 if (leap_year)
222 return months * 30 + days - 1;
223 else
224 return months * 30 + days - 2;
225 }
226 return months * 30 + days;
227 case 1:
228 case 2:
229 case 3:
230 issue = datetime_value_to_serial (issue_date, date_conv);
231 maturity = datetime_value_to_serial (maturity_date, date_conv);
232 return maturity - issue;
233 case 4:
234 return months * 30 + days;
235 default:
236 return -1;
237 }
238 }
239
240 /***************************************************************************/
241
242 /* Returns the number of coupons to be paid between the settlement
243 * and maturity dates.
244 */
245 static gnm_float
246 coupnum (GDate const *settlement, GDate const *maturity,
247 GoCouponConvention const *conv)
248 {
249 int months;
250 GDate this_coupondate = *maturity;
251
252 if (!g_date_valid (maturity) || !g_date_valid (settlement))
253 return gnm_nan;
254
255 months = g_date_get_month (maturity) - g_date_get_month (settlement) +
256 12 *
257 (g_date_get_year (maturity) - g_date_get_year (settlement));
258
259 gnm_date_add_months (&this_coupondate, -months);
260
261 if (conv->eom && g_date_is_last_of_month (maturity))
262 while (g_date_valid (&this_coupondate) &&
263 !g_date_is_last_of_month (&this_coupondate))
264 gnm_date_add_days (&this_coupondate, 1);
265
266 if (!g_date_valid (&this_coupondate))
267 return gnm_nan;
268
269 if (g_date_get_day (settlement) >= g_date_get_day (&this_coupondate))
270 months--;
271
272 return (1 + months / (12 / conv->freq));
273 }
274
275 static gnm_float
276 couppcd (GDate const *settlement, GDate const *maturity,
277 GoCouponConvention const *conv)
278 {
279 GDate date;
280 go_coup_cd (&date, settlement, maturity, conv->freq, conv->eom, FALSE);
281 return go_date_g_to_serial (&date, conv->date_conv);
282 }
283
284 static gnm_float
285 coupncd (GDate const *settlement, GDate const *maturity,
286 GoCouponConvention const *conv)
287 {
288 GDate date;
289 go_coup_cd (&date, settlement, maturity, conv->freq, conv->eom, TRUE);
290 return go_date_g_to_serial (&date, conv->date_conv);
291 }
292
293 static gnm_float
294 price (GDate *settlement, GDate *maturity, gnm_float rate, gnm_float yield,
295 gnm_float redemption, GoCouponConvention const *conv)
296 {
297 gnm_float a, d, e, sum, den, basem1, exponent, first_term, last_term;
298 int n;
299
300 a = go_coupdaybs (settlement, maturity, conv);
301 d = go_coupdaysnc (settlement, maturity, conv);
302 e = go_coupdays (settlement, maturity, conv);
303 n = coupnum (settlement, maturity, conv);
304
305 den = 100.0 * rate / conv->freq;
306 basem1 = yield / conv->freq;
307 exponent = d / e;
308
309 if (n == 1)
310 return (redemption + den) / (1 + exponent * basem1) -
311 a / e * den;
312
313 sum = den * pow1p (basem1, 1 - n - exponent) *
314 pow1pm1 (basem1, n) / basem1;
315
316 first_term = redemption / pow1p (basem1, (n - 1.0 + d / e));
317 last_term = a / e * den;
318
319 return (first_term + sum - last_term);
320 }
321
322 /************************************************************************
323 *
324 * Reading and verifying the arguments for the various COUP____
325 * functions. Calls the passed coup_fn to do the real work
326 *
327 ***********************************************************************/
328
329 static GnmValue *
330 func_coup (GnmFuncEvalInfo *ei, GnmValue const * const *argv,
331 gnm_float (coup_fn) (GDate const *settle, GDate const *mat,
332 GoCouponConvention const *conv))
333 {
334 GDate settlement, maturity;
335 GoCouponConvention conv;
336
337 conv.freq = value_get_freq (argv[2]);
338 conv.basis = value_get_basis (argv[3], GO_BASIS_MSRB_30_360);
339 conv.eom = argv[4] ? value_get_as_checked_bool (argv[4]) : TRUE;
340 conv.date_conv = sheet_date_conv (ei->pos->sheet);
341
342 if (!datetime_value_to_g (&settlement, argv[0], conv.date_conv) ||
343 !datetime_value_to_g (&maturity, argv[1], conv.date_conv))
344 return value_new_error_VALUE (ei->pos);
345
346 if (!is_valid_basis (conv.basis) ||
347 !is_valid_freq (conv.freq) ||
348 g_date_compare (&settlement, &maturity) >= 0)
349 return value_new_error_NUM (ei->pos);
350
351 return value_new_float (coup_fn (&settlement, &maturity, &conv));
352 }
353
354 /***************************************************************************
355 *
356 * Financial function implementations
357 *
358 */
359
360 static GnmFuncHelp const help_accrint[] = {
361 { GNM_FUNC_HELP_NAME, F_("ACCRINT:accrued interest")},
362 { GNM_FUNC_HELP_ARG, F_("issue:date of issue")},
363 { GNM_FUNC_HELP_ARG, F_("first_interest:date of first interest payment")},
364 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
365 { GNM_FUNC_HELP_ARG, F_("rate:nominal annual interest rate")},
366 { GNM_FUNC_HELP_ARG, F_("par:par value, defaults to $1000")},
367 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
368 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis, defaults to 0")},
369 { GNM_FUNC_HELP_ARG, F_("calc_method:calculation method, defaults to TRUE")},
370 { GNM_FUNC_HELP_DESCRIPTION,
371 F_("If @{first_interest} < @{settlement} and @{calc_method} is "
372 "TRUE, then ACCRINT returns the sum of the"
373 " interest accrued in all coupon periods from @{issue} "
374 " date until @{settlement} date.") },
375 { GNM_FUNC_HELP_DESCRIPTION,
376 F_("If @{first_interest} < @{settlement} and @{calc_method} is "
377 "FALSE, then ACCRINT returns the sum of the"
378 " interest accrued in all coupon periods from @{first_interest} "
379 " date until @{settlement} date.") },
380 { GNM_FUNC_HELP_DESCRIPTION,
381 F_("Otherwise ACCRINT returns the sum of the"
382 " interest accrued in all coupon periods from @{issue} "
383 " date until @{settlement} date.") },
384 { GNM_FUNC_HELP_NOTE, F_("@{frequency} must be one of 1, 2 or 4, but the exact value"
385 " does not affect the result.") },
386 { GNM_FUNC_HELP_NOTE, F_("@{issue} must precede both @{first_interest}"
387 " and @{settlement}.") },
388 FREQ_HELP,
389 GNM_DATE_BASIS_HELP
390 { GNM_FUNC_HELP_EXAMPLES, "=ACCRINT(DATE(2008,3,1),DATE(2008,8,31),DATE(2008,5,1),10%,1000,2,0)" },
391 { GNM_FUNC_HELP_SEEALSO, "ACCRINTM"},
392 { GNM_FUNC_HELP_END }
393 };
394
395 static GnmValue *
396 gnumeric_accrint (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
397 {
398 GDate issue, first_interest, settlement;
399 gnm_float rate, a, d, par, freq;
400 int basis;
401 gboolean calc_method;
402
403 GODateConventions const *date_conv =
404 sheet_date_conv (ei->pos->sheet);
405
406 if (!datetime_value_to_g (&issue, argv[0], date_conv) ||
407 !datetime_value_to_g (&first_interest, argv[1], date_conv) ||
408 !datetime_value_to_g (&settlement, argv[2], date_conv))
409 return value_new_error_VALUE (ei->pos);
410
411 if (argv[5] == NULL)
412 return value_new_error_NUM (ei->pos);
413
414 rate = value_get_as_float (argv[3]);
415 par = argv[4] ? value_get_as_float (argv[4]) : 1000;
416 freq = value_get_freq (argv[5]);
417 basis = value_get_basis (argv[6], GO_BASIS_MSRB_30_360);
418 calc_method = argv[6] ? value_get_as_int (argv[6]) : 1;
419
420 if (rate <= 0. ||
421 par <= 0. ||
422 !is_valid_freq (freq) ||
423 !is_valid_basis (basis) ||
424 g_date_compare (&issue, &settlement) >= 0)
425 return value_new_error_NUM (ei->pos);
426 if (g_date_compare (&first_interest, &settlement) >= 0 || calc_method)
427 a = days_monthly_basis (argv[0], argv[2], basis, date_conv);
428 else
429 a = days_monthly_basis (argv[1], argv[2], basis, date_conv);
430 d = annual_year_basis (argv[2], basis, date_conv);
431 if (a < 0 || d <= 0)
432 return value_new_error_NUM (ei->pos);
433
434 /* FIXME : According to XL docs
435 *
436 * NC = number of quasi-coupon periods that fit in odd period. If this
437 * number contains a fraction, raise it to the next whole number.
438 * Ai = number of accrued days for the ith quasi-coupon period within odd period.
439 * NLi = normal length in days of the ith quasi-coupon period within odd period.
440 *
441 * XL == par * (rate/freq) * Sum (1..NC of Ai / NLi
442 */
443 return value_new_float (par * rate * a / d);
444 }
445
446 /***************************************************************************/
447
448 static GnmFuncHelp const help_accrintm[] = {
449 { GNM_FUNC_HELP_NAME, F_("ACCRINTM:accrued interest")},
450 { GNM_FUNC_HELP_ARG, F_("issue:date of issue")},
451 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
452 { GNM_FUNC_HELP_ARG, F_("rate:nominal annual interest rate")},
453 { GNM_FUNC_HELP_ARG, F_("par:par value")},
454 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
455 { GNM_FUNC_HELP_DESCRIPTION, F_("ACCRINTM calculates the accrued interest from @{issue} to @{maturity}.") },
456 { GNM_FUNC_HELP_NOTE, F_("@{par} defaults to $1000.") },
457 GNM_DATE_BASIS_HELP
458 { GNM_FUNC_HELP_EXAMPLES, "=ACCRINTM(DATE(2008,4,1),DATE(2008,6,15),10%,1000,3)" },
459 { GNM_FUNC_HELP_SEEALSO, "ACCRINT"},
460 { GNM_FUNC_HELP_END }
461 };
462
463 static GnmValue *
464 gnumeric_accrintm (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
465 {
466 gnm_float rate, a, d, par;
467 int basis;
468 GODateConventions const *date_conv =
469 sheet_date_conv (ei->pos->sheet);
470
471 rate = value_get_as_float (argv[2]);
472 par = argv[3] ? value_get_as_float (argv[3]) : 1000;
473 basis = value_get_basis (argv[4], GO_BASIS_MSRB_30_360);
474
475 a = days_monthly_basis (argv[0], argv[1], basis, date_conv);
476 d = annual_year_basis (argv[0], basis, date_conv);
477
478 if (a < 0 || d <= 0 || par <= 0 || rate <= 0
479 || !is_valid_basis (basis))
480 return value_new_error_NUM (ei->pos);
481
482 return value_new_float (par * rate * a/d);
483 }
484
485 /***************************************************************************/
486
487 static GnmFuncHelp const help_intrate[] = {
488 { GNM_FUNC_HELP_NAME, F_("INTRATE:interest rate")},
489 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
490 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
491 { GNM_FUNC_HELP_ARG, F_("investment:amount paid on settlement")},
492 { GNM_FUNC_HELP_ARG, F_("redemption:amount received at maturity")},
493 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
494 { GNM_FUNC_HELP_DESCRIPTION, F_("INTRATE calculates the interest of a fully vested security.") },
495 GNM_DATE_BASIS_HELP
496 { GNM_FUNC_HELP_EXAMPLES, "=INTRATE(DATE(2008,4,15),DATE(2008,9,30),100000,103525,1)" },
497 { GNM_FUNC_HELP_SEEALSO, "RECEIVED"},
498 { GNM_FUNC_HELP_END }
499 };
500
501 static GnmValue *
502 gnumeric_intrate (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
503 {
504 gnm_float investment, redemption, a, d;
505 int basis;
506 GODateConventions const *date_conv =
507 sheet_date_conv (ei->pos->sheet);
508
509 investment = value_get_as_float (argv[2]);
510 redemption = value_get_as_float (argv[3]);
511 basis = value_get_basis (argv[4], GO_BASIS_MSRB_30_360);
512
513 a = days_monthly_basis (argv[0], argv[1], basis, date_conv);
514 d = annual_year_basis (argv[0], basis, date_conv);
515
516 if (!is_valid_basis (basis) || a <= 0 || d <= 0 || investment == 0)
517 return value_new_error_NUM (ei->pos);
518
519 return value_new_float ((redemption - investment) / investment *
520 (d / a));
521 }
522
523 /***************************************************************************/
524
525 static GnmFuncHelp const help_received[] = {
526 { GNM_FUNC_HELP_NAME, F_("RECEIVED:amount to be received at maturity")},
527 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
528 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
529 { GNM_FUNC_HELP_ARG, F_("investment:amount paid on settlement")},
530 { GNM_FUNC_HELP_ARG, F_("rate:nominal annual interest rate")},
531 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
532 { GNM_FUNC_HELP_DESCRIPTION, F_("RECEIVED calculates the amount to be received when a security matures.") },
533 GNM_DATE_BASIS_HELP
534 { GNM_FUNC_HELP_EXAMPLES, "=RECEIVED(DATE(2008,4,15),DATE(2008,9,30),100000,4%,1)" },
535 { GNM_FUNC_HELP_SEEALSO, "INTRATE"},
536 { GNM_FUNC_HELP_END }
537 };
538
539 static GnmValue *
540 gnumeric_received (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
541 {
542 gnm_float investment, discount, a, d, n;
543 int basis;
544 GODateConventions const *date_conv =
545 sheet_date_conv (ei->pos->sheet);
546
547 investment = value_get_as_float (argv[2]);
548 discount = value_get_as_float (argv[3]);
549 basis = value_get_basis (argv[4], GO_BASIS_MSRB_30_360);
550
551 a = days_monthly_basis (argv[0], argv[1], basis, date_conv);
552 d = annual_year_basis (argv[0], basis, date_conv);
553
554 if (a <= 0 || d <= 0 || !is_valid_basis (basis))
555 return value_new_error_NUM (ei->pos);
556
557 n = 1.0 - (discount * a/d);
558 if (n == 0)
559 return value_new_error_NUM (ei->pos);
560
561 return value_new_float (investment / n);
562 }
563
564 /***************************************************************************/
565
566 static GnmFuncHelp const help_pricedisc[] = {
567 { GNM_FUNC_HELP_NAME, F_("PRICEDISC:discounted price")},
568 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
569 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
570 { GNM_FUNC_HELP_ARG, F_("discount:annual rate at which to discount")},
571 { GNM_FUNC_HELP_ARG, F_("redemption:amount received at maturity")},
572 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
573 { GNM_FUNC_HELP_DESCRIPTION, F_("PRICEDISC calculates the price per $100 face value of a bond that does not pay interest at maturity.") },
574 GNM_DATE_BASIS_HELP
575 { GNM_FUNC_HELP_SEEALSO, "PRICEMAT"},
576 { GNM_FUNC_HELP_END }
577 };
578
579 static GnmValue *
580 gnumeric_pricedisc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
581 {
582 gnm_float discount, redemption, a, d;
583 int basis;
584 GODateConventions const *date_conv =
585 sheet_date_conv (ei->pos->sheet);
586
587 discount = value_get_as_float (argv[2]);
588 redemption = value_get_as_float (argv[3]);
589 basis = value_get_basis (argv[4], GO_BASIS_MSRB_30_360);
590
591 a = days_monthly_basis (argv[0], argv[1], basis, date_conv);
592 d = annual_year_basis (argv[0], basis, date_conv);
593
594 if (a <= 0 || d <= 0 || !is_valid_basis (basis))
595 return value_new_error_NUM (ei->pos);
596
597 return value_new_float (redemption - discount * redemption * a/d);
598 }
599
600 /***************************************************************************/
601
602 static GnmFuncHelp const help_pricemat[] = {
603 { GNM_FUNC_HELP_NAME, F_("PRICEMAT:price at maturity")},
604 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
605 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
606 { GNM_FUNC_HELP_ARG, F_("issue:date of issue")},
607 { GNM_FUNC_HELP_ARG, F_("discount:annual rate at which to discount")},
608 { GNM_FUNC_HELP_ARG, F_("yield:annual yield of security")},
609 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
610 { GNM_FUNC_HELP_DESCRIPTION, F_("PRICEMAT calculates the price per $100 face value of a bond that pays interest at maturity.") },
611 GNM_DATE_BASIS_HELP
612 { GNM_FUNC_HELP_SEEALSO, "PRICEDISC"},
613 { GNM_FUNC_HELP_END }
614 };
615
616 static GnmValue *
617 gnumeric_pricemat (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
618 {
619 gnm_float discount, yield, a, b, dsm, dim, n;
620 int basis;
621 GODateConventions const *date_conv =
622 sheet_date_conv (ei->pos->sheet);
623
624 discount = value_get_as_float (argv[3]);
625 yield = value_get_as_float (argv[4]);
626 basis = value_get_basis (argv[5], GO_BASIS_MSRB_30_360);
627
628 dsm = days_monthly_basis (argv[0], argv[1], basis, date_conv);
629 dim = days_monthly_basis (argv[2], argv[1], basis, date_conv);
630 a = days_monthly_basis (argv[2], argv[0], basis, date_conv);
631 b = annual_year_basis (argv[0], basis, date_conv);
632
633 if (a <= 0 || b <= 0 || dsm <= 0 || dim <= 0
634 || !is_valid_basis (basis))
635 return value_new_error_NUM (ei->pos);
636
637 n = 1 + (dsm/b * yield);
638 if (n == 0)
639 return value_new_error_NUM (ei->pos);
640
641 return value_new_float (((100 + (dim/b * discount * 100)) /
642 (n)) - (a/b * discount * 100));
643 }
644
645 /***************************************************************************/
646
647 static GnmFuncHelp const help_disc[] = {
648 { GNM_FUNC_HELP_NAME, F_("DISC:discount rate")},
649 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
650 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
651 { GNM_FUNC_HELP_ARG, F_("par:price per $100 face value")},
652 { GNM_FUNC_HELP_ARG, F_("redemption:amount received at maturity")},
653 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
654 { GNM_FUNC_HELP_DESCRIPTION, F_("DISC calculates the discount rate for a security.") },
655 { GNM_FUNC_HELP_NOTE, F_("@{redemption} is the redemption value per $100 face value.") },
656 GNM_DATE_BASIS_HELP
657 { GNM_FUNC_HELP_SEEALSO, "PRICEMAT"},
658 { GNM_FUNC_HELP_END }
659 };
660
661 static GnmValue *
662 gnumeric_disc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
663 {
664 gnm_float par, redemption, dsm, b;
665 int basis;
666 GODateConventions const *date_conv =
667 sheet_date_conv (ei->pos->sheet);
668
669 par = value_get_as_float (argv[2]);
670 redemption = value_get_as_float (argv[3]);
671 basis = value_get_basis (argv[4], GO_BASIS_MSRB_30_360);
672
673 b = annual_year_basis (argv[0], basis, date_conv);
674 dsm = days_monthly_basis (argv[0], argv[1], basis, date_conv);
675
676 if (dsm <= 0 || b <= 0 || dsm <= 0 || !is_valid_basis (basis)
677 || redemption == 0)
678 return value_new_error_NUM (ei->pos);
679
680 return value_new_float ((redemption - par) / redemption * (b / dsm));
681 }
682
683 /***************************************************************************/
684
685 static GnmFuncHelp const help_effect[] = {
686 { GNM_FUNC_HELP_NAME, F_("EFFECT:effective interest rate")},
687 { GNM_FUNC_HELP_ARG, F_("rate:nominal annual interest rate")},
688 { GNM_FUNC_HELP_ARG, F_("nper:number of periods used for compounding")},
689 { GNM_FUNC_HELP_DESCRIPTION, F_("EFFECT calculates the effective interest rate using the formula (1+@{rate}/@{nper})^@{nper}-1.") },
690 { GNM_FUNC_HELP_EXAMPLES, "=EFFECT(19%,12)"},
691 { GNM_FUNC_HELP_SEEALSO, "NOMINAL"},
692 { GNM_FUNC_HELP_END }
693 };
694
695 static GnmValue *
696 gnumeric_effect (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
697 {
698 gnm_float rate = value_get_as_float (argv[0]);
699 gnm_float nper = gnm_floor (value_get_as_float (argv[1]));
700
701 /* I don't know why Excel disallows 0% for rate. */
702 if (rate <= 0 || nper < 1)
703 return value_new_error_NUM (ei->pos);
704
705 return value_new_float (pow1pm1 (rate / nper, nper));
706 }
707
708 /***************************************************************************/
709
710 static GnmFuncHelp const help_nominal[] = {
711 { GNM_FUNC_HELP_NAME, F_("NOMINAL:nominal interest rate")},
712 { GNM_FUNC_HELP_ARG, F_("rate:effective annual interest rate")},
713 { GNM_FUNC_HELP_ARG, F_("nper:number of periods used for compounding")},
714 { GNM_FUNC_HELP_DESCRIPTION, F_("NOMINAL calculates the nominal interest rate from the effective rate.") },
715 { GNM_FUNC_HELP_EXAMPLES, "=NOMINAL(10%,6)" },
716 { GNM_FUNC_HELP_SEEALSO, "EFFECT"},
717 { GNM_FUNC_HELP_END }
718 };
719
720 static GnmValue *
721 gnumeric_nominal (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
722 {
723 gnm_float rate = value_get_as_float (argv[0]);
724 gnm_float nper = gnm_floor (value_get_as_float (argv[1]));
725
726 /* I don't know why Excel disallows 0% for rate. */
727 if (rate <= 0 || nper < 1)
728 return value_new_error_NUM (ei->pos);
729
730 return value_new_float (nper * pow1pm1 (rate, 1.0 / nper));
731 }
732
733 /***************************************************************************/
734
735 static GnmFuncHelp const help_ispmt[] = {
736 { GNM_FUNC_HELP_NAME, F_("ISPMT:interest payment for period")},
737 { GNM_FUNC_HELP_ARG, F_("rate:effective annual interest rate")},
738 { GNM_FUNC_HELP_ARG, F_("per:period number")},
739 { GNM_FUNC_HELP_ARG, F_("nper:number of periods")},
740 { GNM_FUNC_HELP_ARG, F_("pv:present value")},
741 { GNM_FUNC_HELP_DESCRIPTION, F_("ISPMT calculates the interest payment for period number @{per}.") },
742 { GNM_FUNC_HELP_EXAMPLES, "=ISPMT(10%,4,10,1e6)" },
743 { GNM_FUNC_HELP_SEEALSO, "PV"},
744 { GNM_FUNC_HELP_END }
745 };
746
747 static GnmValue *
748 gnumeric_ispmt (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
749 {
750 gnm_float tmp;
751
752 gnm_float rate = value_get_as_float (argv[0]);
753 gnm_float per = value_get_as_int (argv[1]);
754 gnm_float nper = value_get_as_int (argv[2]);
755 gnm_float pv = value_get_as_float (argv[3]);
756
757 /*
758 * It seems that with 20 periods, a period number of 20.99 is
759 * valid in XL.
760 */
761 if (per < 1 || per >= nper + 1)
762 return value_new_error_NUM (ei->pos);
763
764 tmp = -pv * rate;
765
766 return value_new_float (tmp - (tmp / nper * per));
767 }
768
769 /***************************************************************************/
770
771 static GnmFuncHelp const help_db[] = {
772 { GNM_FUNC_HELP_NAME, F_("DB:depreciation of an asset")},
773 { GNM_FUNC_HELP_ARG, F_("cost:initial cost of asset")},
774 { GNM_FUNC_HELP_ARG, F_("salvage:value after depreciation")},
775 { GNM_FUNC_HELP_ARG, F_("life:number of periods")},
776 { GNM_FUNC_HELP_ARG, F_("period:subject period")},
777 { GNM_FUNC_HELP_ARG, F_("month:number of months in first year of depreciation")},
778 { GNM_FUNC_HELP_DESCRIPTION, F_("DB calculates the depreciation of an asset for a given period using the fixed-declining balance method.") },
779 { GNM_FUNC_HELP_SEEALSO, "DDB,SLN,SYD"},
780 { GNM_FUNC_HELP_END }
781 };
782
783 static GnmValue *
784 gnumeric_db (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
785 {
786 gnm_float rate;
787 gnm_float cost, salvage, life, period, month;
788 gnm_float total;
789 int i;
790
791 cost = value_get_as_float (argv[0]);
792 salvage = value_get_as_float (argv[1]);
793 life = value_get_as_float (argv[2]);
794 period = value_get_as_float (argv[3]);
795 month = argv[4] ? value_get_as_float (argv[4]) : 12;
796
797 /* The third disjunct is a bit of a guess -- MW. */
798 if (cost == 0 || life <= 0 || salvage / cost < 0)
799 return value_new_error_NUM (ei->pos);
800
801 rate = 1 - gnm_pow ((salvage / cost), (1 / life));
802 rate *= 1000;
803 rate = gnm_floor (rate + 0.5) / 1000;
804
805 total = cost * rate * month / 12;
806
807 if (period == 1)
808 return value_new_float (total);
809
810 for (i = 1; i < life; i++)
811 if (i == period - 1)
812 return value_new_float ((cost - total) * rate);
813 else
814 total += (cost - total) * rate;
815
816 return value_new_float (((cost - total) * rate * (12 - month)) / 12);
817 }
818
819 /***************************************************************************/
820
821 static GnmFuncHelp const help_ddb[] = {
822 { GNM_FUNC_HELP_NAME, F_("DDB:depreciation of an asset")},
823 { GNM_FUNC_HELP_ARG, F_("cost:initial cost of asset")},
824 { GNM_FUNC_HELP_ARG, F_("salvage:value after depreciation")},
825 { GNM_FUNC_HELP_ARG, F_("life:number of periods")},
826 { GNM_FUNC_HELP_ARG, F_("period:subject period")},
827 { GNM_FUNC_HELP_ARG, F_("factor:factor at which the balance declines")},
828 { GNM_FUNC_HELP_DESCRIPTION, F_("DDB calculates the depreciation of an asset for a given period using the double-declining balance method.") },
829 { GNM_FUNC_HELP_SEEALSO, "DB,SLN,SYD"},
830 { GNM_FUNC_HELP_END }
831 };
832
833 static GnmValue *
834 gnumeric_ddb (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
835 {
836 gnm_float cost, salvage, life, period, factor;
837 gnm_float f, prior, dep;
838
839 cost = value_get_as_float (argv[0]);
840 salvage = value_get_as_float (argv[1]);
841 life = value_get_as_float (argv[2]);
842 period = value_get_as_float (argv[3]);
843 factor = argv[4] ? value_get_as_float (argv[4]) : 2;
844
845 if (cost < 0 || salvage < 0 || life <= 0 ||
846 period <= 0 || period > life ||
847 factor <= 0)
848 return value_new_error_NUM (ei->pos);
849
850 if (salvage >= cost)
851 return value_new_int (0);
852
853 if (period < 1) {
854 period = 1;
855 if (period > life)
856 return value_new_float (cost - salvage);
857 }
858
859 f = factor / life;
860 prior = -cost * pow1pm1 (-f, period - 1);
861 dep = (cost - prior) * f;
862
863 /* Depreciation cannot exceed book value. */
864 dep = MIN (dep, MAX (0, cost - prior - salvage));
865 return value_new_float (dep);
866 }
867
868 /***************************************************************************/
869
870 static GnmFuncHelp const help_sln[] = {
871 { GNM_FUNC_HELP_NAME, F_("SLN:depreciation of an asset")},
872 { GNM_FUNC_HELP_ARG, F_("cost:initial cost of asset")},
873 { GNM_FUNC_HELP_ARG, F_("salvage:value after depreciation")},
874 { GNM_FUNC_HELP_ARG, F_("life:number of periods")},
875 { GNM_FUNC_HELP_DESCRIPTION, F_("SLN calculates the depreciation of an asset using the straight-line method.") },
876 { GNM_FUNC_HELP_EXAMPLES, "=SLN(10000,700,10)" },
877 { GNM_FUNC_HELP_SEEALSO, "DB,DDB,SYD"},
878 { GNM_FUNC_HELP_END }
879 };
880
881
882 static GnmValue *
883 gnumeric_sln (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
884 {
885 gnm_float cost,salvage_value,life;
886
887 cost = value_get_as_float (argv[0]);
888 salvage_value = value_get_as_float (argv[1]);
889 life = value_get_as_float (argv[2]);
890
891 /* Life of an asset cannot be negative */
892 if (life <= 0)
893 return value_new_error_NUM (ei->pos);
894
895 return value_new_float ((cost - salvage_value) / life);
896 }
897
898 /***************************************************************************/
899
900 static GnmFuncHelp const help_syd[] = {
901 { GNM_FUNC_HELP_NAME, F_("SYD:sum-of-years depreciation")},
902 { GNM_FUNC_HELP_ARG, F_("cost:initial cost of asset")},
903 { GNM_FUNC_HELP_ARG, F_("salvage:value after depreciation")},
904 { GNM_FUNC_HELP_ARG, F_("life:number of periods")},
905 { GNM_FUNC_HELP_ARG, F_("period:subject period")},
906 { GNM_FUNC_HELP_DESCRIPTION, F_("SYD calculates the depreciation of an asset using the sum-of-years method.") },
907 { GNM_FUNC_HELP_EXAMPLES, "=SYD(5000,200,5,2)" },
908 { GNM_FUNC_HELP_SEEALSO, "DB,DDB,SLN"},
909 { GNM_FUNC_HELP_END }
910 };
911
912 static GnmValue *
913 gnumeric_syd (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
914 {
915 gnm_float cost, salvage_value, life, period;
916
917 cost = value_get_as_float (argv[0]);
918 salvage_value = value_get_as_float (argv[1]);
919 life = value_get_as_float (argv[2]);
920 period = value_get_as_float (argv[3]);
921
922 /* Life of an asset cannot be negative */
923 if (life <= 0)
924 return value_new_error_NUM (ei->pos);
925
926 return value_new_float (((cost - salvage_value) *
927 (life - period + 1) * 2) /
928 (life * (life + 1.0)));
929 }
930
931 /***************************************************************************/
932
933 static GnmFuncHelp const help_dollarde[] = {
934 { GNM_FUNC_HELP_NAME, F_("DOLLARDE:convert to decimal dollar amount")},
935 { GNM_FUNC_HELP_ARG, F_("fractional_dollar:amount to convert")},
936 { GNM_FUNC_HELP_ARG, F_("fraction:denominator")},
937 { GNM_FUNC_HELP_DESCRIPTION, F_("DOLLARDE converts a fractional dollar amount into a decimal amount. This is the inverse of the DOLLARFR function.") },
938 { GNM_FUNC_HELP_EXAMPLES, "=TEXT(DOLLARDE(0.03,16),\"0.0000\")" },
939 { GNM_FUNC_HELP_SEEALSO, "DOLLARFR" },
940 { GNM_FUNC_HELP_END }
941 };
942
943 static GnmValue *
944 gnumeric_dollarde (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
945 {
946 gnm_float x = value_get_as_float (argv[0]);
947 gnm_float f = gnm_floor (value_get_as_float (argv[1]));
948 gnm_float negative = FALSE;
949 gnm_float fdigits;
950 gnm_float res;
951
952 if (f < 0)
953 return value_new_error_NUM (ei->pos);
954 if (f == 0)
955 return value_new_error_DIV0 (ei->pos);
956
957 if (x < 0) {
958 negative = TRUE;
959 x = gnm_abs (x);
960 }
961
962 /*
963 * For a power of 10, this is actually one less than the
964 * number of digits.
965 */
966 fdigits = 1 + gnm_floor (gnm_log10 (f - 0.5));
967
968 res = gnm_floor (x);
969
970 /* If f=9, then .45 means 4.5/9 */
971 res += (x - res) * gnm_pow10 (fdigits) / f;
972
973 if (negative)
974 res = 0 - res;
975
976 return value_new_float (res);
977 }
978
979 /***************************************************************************/
980
981 static GnmFuncHelp const help_dollarfr[] = {
982 { GNM_FUNC_HELP_NAME, F_("DOLLARFR:convert to dollar fraction")},
983 { GNM_FUNC_HELP_ARG, F_("decimal_dollar:amount to convert")},
984 { GNM_FUNC_HELP_ARG, F_("fraction:denominator")},
985 { GNM_FUNC_HELP_DESCRIPTION, F_("DOLLARFR converts a decimal dollar amount into a fractional amount which is represented as the digits after the decimal point. For example, 2/8 would be represented as .2 while 3/16 would be represented as .03. This is the inverse of the DOLLARDE function.") },
986 { GNM_FUNC_HELP_EXAMPLES, "=DOLLARFR(0.25,8)" },
987 { GNM_FUNC_HELP_SEEALSO, "DOLLARDE"},
988 { GNM_FUNC_HELP_END }
989 };
990
991 static GnmValue *
992 gnumeric_dollarfr (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
993 {
994 gnm_float x = value_get_as_float (argv[0]);
995 gnm_float f = gnm_floor (value_get_as_float (argv[1]));
996 gnm_float negative = FALSE;
997 gnm_float fdigits;
998 gnm_float res;
999
1000 if (f < 0)
1001 return value_new_error_NUM (ei->pos);
1002 if (f == 0)
1003 return value_new_error_DIV0 (ei->pos);
1004
1005 if (x < 0) {
1006 negative = TRUE;
1007 x = gnm_abs (x);
1008 }
1009
1010 /*
1011 * For a power of 10, this is actually one less than the
1012 * number of digits.
1013 */
1014 fdigits = 1 + gnm_floor (gnm_log10 (f - 0.5));
1015
1016 res = gnm_floor (x);
1017 res += (x - res) * f / gnm_pow10 (fdigits);
1018
1019 if (negative)
1020 res = 0 - res;
1021
1022 return value_new_float (res);
1023 }
1024
1025 /***************************************************************************/
1026
1027 static GnmFuncHelp const help_mirr[] = {
1028 { GNM_FUNC_HELP_NAME, F_("MIRR:modified internal rate of return")},
1029 { GNM_FUNC_HELP_ARG, F_("values:cash flow")},
1030 { GNM_FUNC_HELP_ARG, F_("finance_rate:interest rate for financing cost")},
1031 { GNM_FUNC_HELP_ARG, F_("reinvest_rate:interest rate for reinvestments")},
1032 { GNM_FUNC_HELP_DESCRIPTION, F_("MIRR calculates the modified internal rate of return of a periodic cash flow.") },
1033 { GNM_FUNC_HELP_SEEALSO, "IRR,XIRR"},
1034 { GNM_FUNC_HELP_END }
1035 };
1036
1037 static GnmValue *
1038 gnumeric_mirr (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1039 {
1040 gnm_float frate, rrate, npv_neg, npv_pos;
1041 gnm_float *values = NULL, res;
1042 GnmValue *result = NULL;
1043 int i, n;
1044
1045 frate = value_get_as_float (argv[1]);
1046 rrate = value_get_as_float (argv[2]);
1047
1048 values = collect_floats_value (argv[0], ei->pos,
1049 COLLECT_IGNORE_STRINGS |
1050 COLLECT_IGNORE_BLANKS,
1051 &n, &result);
1052 if (result)
1053 goto out;
1054
1055 for (i = 0, npv_pos = npv_neg = 0; i < n; i++) {
1056 gnm_float v = values[i];
1057 if (v >= 0)
1058 npv_pos += v / pow1p (rrate, i);
1059 else
1060 npv_neg += v / pow1p (frate, i);
1061 }
1062
1063 if (npv_neg == 0 || npv_pos == 0 || rrate <= -1) {
1064 result = value_new_error_DIV0 (ei->pos);
1065 goto out;
1066 }
1067
1068 /*
1069 * I have my doubts about this formula, but it sort of looks like
1070 * the one Microsoft claims to use and it produces the results
1071 * that Excel does. -- MW.
1072 */
1073 res = gnm_pow ((-npv_pos * pow1p (rrate, n)) / (npv_neg * (1 + rrate)),
1074 (1.0 / (n - 1))) - 1.0;
1075
1076 result = value_new_float (res);
1077 out:
1078 g_free (values);
1079
1080 return result;
1081 }
1082
1083 /***************************************************************************/
1084
1085 static GnmFuncHelp const help_tbilleq[] = {
1086 { GNM_FUNC_HELP_NAME, F_("TBILLEQ:bond-equivalent yield for a treasury bill")},
1087 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
1088 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
1089 { GNM_FUNC_HELP_ARG, F_("discount:annual rate at which to discount")},
1090 { GNM_FUNC_HELP_DESCRIPTION, F_("TBILLEQ calculates the bond-equivalent yield for a treasury bill.") },
1091 { GNM_FUNC_HELP_SEEALSO, "TBILLPRICE,TBILLYIELD"},
1092 { GNM_FUNC_HELP_END }
1093 };
1094
1095 static GnmValue *
1096 gnumeric_tbilleq (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1097 {
1098 gnm_float settlement, maturity, discount;
1099 gnm_float dsm, divisor;
1100 GODateConventions const *date_conv =
1101 sheet_date_conv (ei->pos->sheet);
1102
1103 settlement = datetime_value_to_serial (argv[0], date_conv);
1104 maturity = datetime_value_to_serial (argv[1], date_conv);
1105 discount = value_get_as_float (argv[2]);
1106
1107 dsm = maturity - settlement;
1108
1109 if (settlement > maturity || discount < 0 || dsm > 365)
1110 return value_new_error_NUM (ei->pos);
1111
1112 divisor = 360 - discount * dsm;
1113 /* This test probably isn't right, but it is better that not checking
1114 at all. --MW. */
1115 if (divisor == 0)
1116 return value_new_error_DIV0 (ei->pos);
1117
1118 return value_new_float ((365 * discount) / divisor);
1119 }
1120
1121 /***************************************************************************/
1122
1123 static GnmFuncHelp const help_tbillprice[] = {
1124 { GNM_FUNC_HELP_NAME, F_("TBILLPRICE:price of a treasury bill")},
1125 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
1126 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
1127 { GNM_FUNC_HELP_ARG, F_("discount:annual rate at which to discount")},
1128 { GNM_FUNC_HELP_DESCRIPTION, F_("TBILLPRICE calculates the price per $100 face value for a treasury bill.") },
1129 { GNM_FUNC_HELP_SEEALSO, "TBILLEQ,TBILLYIELD"},
1130 { GNM_FUNC_HELP_END }
1131 };
1132
1133 static GnmValue *
1134 gnumeric_tbillprice (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1135 {
1136 gnm_float settlement, maturity, discount;
1137 gnm_float res, dsm;
1138 GODateConventions const *date_conv =
1139 sheet_date_conv (ei->pos->sheet);
1140
1141 settlement = datetime_value_to_serial (argv[0], date_conv);
1142 maturity = datetime_value_to_serial (argv[1], date_conv);
1143 discount = value_get_as_float (argv[2]);
1144
1145 dsm = maturity - settlement;
1146
1147 if (settlement > maturity || discount < 0 || dsm > 365)
1148 return value_new_error_NUM (ei->pos);
1149
1150 res = 100 * (1.0 - (discount * dsm) / 360.0);
1151
1152 return value_new_float (res);
1153 }
1154
1155 /***************************************************************************/
1156
1157 static GnmFuncHelp const help_tbillyield[] = {
1158 { GNM_FUNC_HELP_NAME, F_("TBILLYIELD:yield of a treasury bill")},
1159 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
1160 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
1161 { GNM_FUNC_HELP_ARG, F_("price:price")},
1162 { GNM_FUNC_HELP_DESCRIPTION, F_("TBILLYIELD calculates the yield of a treasury bill.") },
1163 { GNM_FUNC_HELP_SEEALSO, "TBILLEQ,TBILLPRICE"},
1164 { GNM_FUNC_HELP_END }
1165 };
1166
1167 static GnmValue *
1168 gnumeric_tbillyield (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1169 {
1170 gnm_float settlement, maturity, pr;
1171 gnm_float res, dsm;
1172 GODateConventions const *date_conv =
1173 sheet_date_conv (ei->pos->sheet);
1174
1175 settlement = datetime_value_to_serial (argv[0], date_conv);
1176 maturity = datetime_value_to_serial (argv[1], date_conv);
1177 pr = value_get_as_float (argv[2]);
1178
1179 dsm = maturity - settlement;
1180
1181 if (pr <= 0 || dsm <= 0 || dsm > 365)
1182 return value_new_error_NUM (ei->pos);
1183
1184 res = (100.0 - pr) / pr * (360.0 / dsm);
1185
1186 return value_new_float (res);
1187 }
1188
1189 /***************************************************************************/
1190
1191 static GnmFuncHelp const help_rate[] = {
1192 { GNM_FUNC_HELP_NAME, F_("RATE:rate of investment")},
1193 { GNM_FUNC_HELP_ARG, F_("nper:number of periods")},
1194 { GNM_FUNC_HELP_ARG, F_("pmt:payment at each period")},
1195 { GNM_FUNC_HELP_ARG, F_("pv:present value")},
1196 { GNM_FUNC_HELP_ARG, F_("fv:future value")},
1197 { GNM_FUNC_HELP_ARG, F_("type:payment type")},
1198 { GNM_FUNC_HELP_ARG, F_("guess:an estimate of what the result should be")},
1199 { GNM_FUNC_HELP_DESCRIPTION, F_("RATE calculates the rate of return.") },
1200 TYPE_HELP,
1201 { GNM_FUNC_HELP_NOTE, F_("The optional @{guess} is needed because there can be more than one valid result. It defaults to 10%.") },
1202 { GNM_FUNC_HELP_EXAMPLES, "=RATE(10,-1500,10000,0)" },
1203 { GNM_FUNC_HELP_SEEALSO, "PV,FV"},
1204 { GNM_FUNC_HELP_END }
1205 };
1206
1207 typedef struct {
1208 int type;
1209 gnm_float nper, pv, fv, pmt;
1210 } gnumeric_rate_t;
1211
1212 static GoalSeekStatus
1213 gnumeric_rate_f (gnm_float rate, gnm_float *y, void *user_data)
1214 {
1215 if (rate > -1.0 && rate != 0) {
1216 gnumeric_rate_t *data = user_data;
1217
1218 *y = data->pv * calculate_pvif (rate, data->nper) +
1219 data->pmt * (1 + rate * data->type) *
1220 calculate_fvifa (rate, data->nper) +
1221 data->fv;
1222 return GOAL_SEEK_OK;
1223 } else
1224 return GOAL_SEEK_ERROR;
1225 }
1226
1227 /* The derivative of the above function with respect to rate. */
1228 static GoalSeekStatus
1229 gnumeric_rate_df (gnm_float rate, gnm_float *y, void *user_data)
1230 {
1231 if (rate > -1.0 && rate != 0.0) {
1232 gnumeric_rate_t *data = user_data;
1233
1234 *y = -data->pmt * calculate_fvifa (rate, data->nper) / rate +
1235 calculate_pvif (rate, data->nper - 1) * data->nper *
1236 (data->pv + data->pmt * (data->type + 1 / rate));
1237 return GOAL_SEEK_OK;
1238 } else
1239 return GOAL_SEEK_ERROR;
1240 }
1241
1242
1243 static GnmValue *
1244 gnumeric_rate (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1245 {
1246 GoalSeekData data;
1247 GoalSeekStatus status;
1248 gnumeric_rate_t udata;
1249 gnm_float rate0;
1250
1251 udata.nper = value_get_as_int (argv[0]);
1252 /* YES ZERO, it's sick but it's XL compatible */
1253 udata.pmt = argv[1] ? value_get_as_float (argv[1]) : 0.0;
1254 udata.pv = value_get_as_float (argv[2]);
1255 udata.fv = argv[3] ? value_get_as_float (argv[3]) : 0.0;
1256 udata.type = value_get_paytype (argv[4]);
1257 rate0 = argv[5] ? value_get_as_float (argv[5]) : 0.1;
1258
1259 if (udata.nper <= 0)
1260 return value_new_error_NUM (ei->pos);
1261
1262 if (!is_valid_paytype (udata.type))
1263 return value_new_error_VALUE (ei->pos);
1264
1265 #if 0
1266 g_printerr ("Guess = %.15g\n", rate0);
1267 #endif
1268 goal_seek_initialize (&data);
1269
1270 data.xmin = MAX (data.xmin,
1271 -gnm_pow (DBL_MAX / 1e10, 1.0 / udata.nper) + 1);
1272 data.xmax = MIN (data.xmax,
1273 gnm_pow (DBL_MAX / 1e10, 1.0 / udata.nper) - 1);
1274
1275 /* Newton search from guess. */
1276 status = goal_seek_newton (&gnumeric_rate_f, &gnumeric_rate_df,
1277 &data, &udata, rate0);
1278
1279 if (status != GOAL_SEEK_OK) {
1280 int factor;
1281 /* Lay a net of test points around the guess. */
1282 for (factor = 2; !(data.havexneg && data.havexpos)
1283 && factor < 100; factor *= 2) {
1284 goal_seek_point (&gnumeric_rate_f, &data, &udata,
1285 rate0 * factor);
1286 goal_seek_point (&gnumeric_rate_f, &data, &udata,
1287 rate0 / factor);
1288 }
1289
1290 /* Pray we got both sides of the root. */
1291 status = goal_seek_bisection (&gnumeric_rate_f, &data, &udata);
1292 }
1293
1294 if (status == GOAL_SEEK_OK) {
1295 #if 0
1296 g_printerr ("Root = %.15g\n\n", data.root);
1297 #endif
1298 return value_new_float (data.root);
1299 } else
1300 return value_new_error_NUM (ei->pos);
1301 }
1302
1303 /***************************************************************************/
1304
1305 static GnmFuncHelp const help_rri[] = {
1306 { GNM_FUNC_HELP_NAME, F_("RRI:equivalent interest rate for an investment increasing in value")},
1307 { GNM_FUNC_HELP_ARG, F_("p:number of periods")},
1308 { GNM_FUNC_HELP_ARG, F_("pv:present value")},
1309 { GNM_FUNC_HELP_ARG, F_("fv:future value")},
1310 { GNM_FUNC_HELP_DESCRIPTION, F_("RRI determines an equivalent interest rate for an investment that increases in value. The interest is compounded after each complete period.") },
1311 TYPE_HELP,
1312 { GNM_FUNC_HELP_NOTE, F_("Note that @{p} need not be an integer but for fractional value the calculated rate is only approximate.") },
1313 { GNM_FUNC_HELP_ODF, F_("This function is OpenFormula compatible.") },
1314 { GNM_FUNC_HELP_EXAMPLES, "=RRI(12,5000,10000)" },
1315 { GNM_FUNC_HELP_SEEALSO, "PV,FV,RATE"},
1316 { GNM_FUNC_HELP_END }
1317 };
1318
1319
1320 static GnmValue *
1321 gnumeric_rri (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1322 {
1323 gnm_float per, pv, fv;
1324
1325 per = value_get_as_float (argv[0]);
1326 pv = value_get_as_float (argv[1]);
1327 fv = value_get_as_float (argv[2]);
1328
1329 if (per < 0)
1330 return value_new_error_NUM (ei->pos);
1331 if (pv == 0. || per == 0.)
1332 return value_new_error_DIV0 (ei->pos);
1333
1334 return value_new_float (gnm_pow(fv/pv,1/per)-1.);
1335 }
1336
1337
1338 /***************************************************************************/
1339
1340 static GnmFuncHelp const help_irr[] = {
1341 { GNM_FUNC_HELP_NAME, F_("IRR:internal rate of return")},
1342 { GNM_FUNC_HELP_ARG, F_("values:cash flow")},
1343 { GNM_FUNC_HELP_ARG, F_("guess:an estimate of what the result should be")},
1344 { GNM_FUNC_HELP_DESCRIPTION, F_("IRR calculates the internal rate of return of a cash flow with periodic payments. @{values} lists the payments (negative values) and receipts (positive values) for each period.") },
1345 { GNM_FUNC_HELP_EXAMPLES, "=IRR({100;100;200;-450})" },
1346 { GNM_FUNC_HELP_NOTE, F_("The optional @{guess} is needed because there can be more than one valid result. It defaults to 10%.") },
1347 { GNM_FUNC_HELP_SEEALSO, "XIRR"},
1348 { GNM_FUNC_HELP_END }
1349 };
1350
1351 typedef struct {
1352 int n;
1353 gnm_float *values;
1354 } gnumeric_irr_t;
1355
1356 static GoalSeekStatus
1357 irr_npv (gnm_float rate, gnm_float *y, void *user_data)
1358 {
1359 const gnumeric_irr_t *p = user_data;
1360 const gnm_float *values = p->values;
1361 int n = p->n;
1362 gnm_float sum = 0;
1363 gnm_float f = 1;
1364 gnm_float ff = 1 / (rate + 1);
1365 int i;
1366
1367 for (i = 0; i < n; i++) {
1368 sum += values[i] * f;
1369 f *= ff;
1370 }
1371
1372 *y = sum;
1373 return gnm_finite (sum) ? GOAL_SEEK_OK : GOAL_SEEK_ERROR;
1374 }
1375
1376 static GoalSeekStatus
1377 irr_npv_df (gnm_float rate, gnm_float *y, void *user_data)
1378 {
1379 const gnumeric_irr_t *p = user_data;
1380 const gnm_float *values = p->values;
1381 int n = p->n;
1382 gnm_float sum = 0;
1383 gnm_float f = 1;
1384 gnm_float ff = 1 / (rate + 1);
1385 int i;
1386
1387 for (i = 1; i < n; i++) {
1388 sum += values[i] * (-i) * f;
1389 f *= ff;
1390 }
1391
1392 *y = sum;
1393 return gnm_finite (sum) ? GOAL_SEEK_OK : GOAL_SEEK_ERROR;
1394 }
1395
1396 static GnmValue *
1397 gnumeric_irr (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1398 {
1399 GoalSeekData data;
1400 GoalSeekStatus status;
1401 GnmValue *result = NULL;
1402 gnumeric_irr_t p;
1403 gnm_float rate0;
1404
1405 rate0 = argv[1] ? value_get_as_float (argv[1]) : 0.1;
1406
1407 p.values = collect_floats_value (argv[0], ei->pos,
1408 COLLECT_IGNORE_STRINGS |
1409 COLLECT_IGNORE_BLANKS,
1410 &p.n, &result);
1411 if (result != NULL) {
1412 g_free (p.values);
1413 return result;
1414 }
1415
1416 goal_seek_initialize (&data);
1417
1418 data.xmin = -1;
1419 data.xmax = MIN (data.xmax,
1420 gnm_pow (DBL_MAX / 1e10, 1.0 / p.n) - 1);
1421
1422 status = goal_seek_newton (&irr_npv, &irr_npv_df, &data, &p, rate0);
1423 if (status != GOAL_SEEK_OK) {
1424 int i;
1425 gnm_float s;
1426
1427 /* Lay a net of test points around the guess. */
1428 for (i = 0, s = 2; !(data.havexneg && data.havexpos) && i < 10; i++, s *= 2) {
1429 goal_seek_point (&irr_npv, &data, &p, rate0 * s);
1430 goal_seek_point (&irr_npv, &data, &p, rate0 / s);
1431 }
1432
1433 /*
1434 * If the root is negative and the guess is positive it
1435 * is possible to get thrown out to the left of -100%
1436 * by the Newton method.
1437 */
1438 if (!(data.havexneg && data.havexpos))
1439 goal_seek_newton (&irr_npv, &irr_npv_df, &data, &p, -0.99);
1440 if (!(data.havexneg && data.havexpos))
1441 goal_seek_point (&irr_npv, &data, &p, 1 - GNM_EPSILON);
1442
1443 /* Pray we got both sides of the root. */
1444 status = goal_seek_bisection (&irr_npv, &data, &p);
1445 }
1446
1447 g_free (p.values);
1448
1449 if (status == GOAL_SEEK_OK)
1450 return value_new_float (data.root);
1451 else
1452 return value_new_error_NUM (ei->pos);
1453 }
1454
1455 /***************************************************************************/
1456
1457 static GnmFuncHelp const help_pv[] = {
1458 { GNM_FUNC_HELP_NAME, F_("PV:present value")},
1459 { GNM_FUNC_HELP_ARG, F_("rate:effective interest rate per period")},
1460 { GNM_FUNC_HELP_ARG, F_("nper:number of periods")},
1461 { GNM_FUNC_HELP_ARG, F_("pmt:payment at each period")},
1462 { GNM_FUNC_HELP_ARG, F_("fv:future value")},
1463 { GNM_FUNC_HELP_ARG, F_("type:payment type")},
1464 { GNM_FUNC_HELP_DESCRIPTION, F_("PV calculates the present value of @{fv} which is @{nper} periods into the future, assuming a periodic payment of @{pmt} and an interest rate of @{rate} per period.") },
1465 TYPE_HELP,
1466 { GNM_FUNC_HELP_EXAMPLES, "=PV(10%,10,1000,20000,0)" },
1467 { GNM_FUNC_HELP_SEEALSO, "FV"},
1468 { GNM_FUNC_HELP_END }
1469 };
1470
1471 static GnmValue *
1472 gnumeric_pv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1473 {
1474 gnm_float rate = value_get_as_float (argv[0]);
1475 gnm_float nper = value_get_as_float (argv[1]);
1476 gnm_float pmt = value_get_as_float (argv[2]);
1477 gnm_float fv = argv[3] ? value_get_as_float (argv[3]) : 0;
1478 int type = value_get_paytype (argv[4]);
1479 gnm_float pvif, fvifa;
1480
1481 if (!is_valid_paytype (type))
1482 return value_new_error_VALUE (ei->pos);
1483
1484 /* Calculate the PVIF and FVIFA */
1485 pvif = calculate_pvif (rate, nper);
1486 fvifa = calculate_fvifa (rate, nper);
1487
1488 if (pvif == 0)
1489 return value_new_error_DIV0 (ei->pos);
1490
1491 return value_new_float ((-fv - pmt * (1.0 + rate * type) * fvifa) /
1492 pvif);
1493 }
1494
1495 /***************************************************************************/
1496
1497 static GnmFuncHelp const help_npv[] = {
1498 { GNM_FUNC_HELP_NAME, F_("NPV:net present value")},
1499 { GNM_FUNC_HELP_ARG, F_("rate:effective interest rate per period")},
1500 { GNM_FUNC_HELP_ARG, F_("value1:cash flow for period 1")},
1501 { GNM_FUNC_HELP_ARG, F_("value2:cash flow for period 2")},
1502 { GNM_FUNC_HELP_DESCRIPTION, F_("NPV calculates the net present value of a cash flow.") },
1503 { GNM_FUNC_HELP_EXAMPLES, "=NPV(10%,100,100,-250)" },
1504 { GNM_FUNC_HELP_SEEALSO, "PV"},
1505 { GNM_FUNC_HELP_END }
1506 };
1507
1508 static int
1509 range_npv (gnm_float const *xs, int n, gnm_float *res)
1510 {
1511 if (n == 0 || xs[0] == -1)
1512 return 1;
1513 else {
1514 gnm_float sum = 0;
1515 gnm_float f = 1;
1516 gnm_float ff = 1 / (1 + xs[0]);
1517 int i;
1518
1519 for (i = 1; i < n; i++) {
1520 f *= ff;
1521 sum += xs[i] * f;
1522 }
1523 *res = sum;
1524 return 0;
1525 }
1526 }
1527
1528 static GnmValue *
1529 gnumeric_npv (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
1530 {
1531 return float_range_function (argc, argv, ei,
1532 range_npv,
1533 COLLECT_IGNORE_STRINGS |
1534 COLLECT_IGNORE_BOOLS |
1535 COLLECT_IGNORE_BLANKS,
1536 GNM_ERROR_DIV0);
1537 }
1538
1539 /***************************************************************************/
1540
1541 static GnmFuncHelp const help_xnpv[] = {
1542 { GNM_FUNC_HELP_NAME, F_("XNPV:net present value")},
1543 { GNM_FUNC_HELP_ARG, F_("rate:effective annual interest rate")},
1544 { GNM_FUNC_HELP_ARG, F_("values:cash flow")},
1545 { GNM_FUNC_HELP_ARG, F_("dates:dates of cash flow")},
1546 { GNM_FUNC_HELP_DESCRIPTION, F_("XNPV calculates the net present value of a cash flow at irregular times.") },
1547 TYPE_HELP,
1548 { GNM_FUNC_HELP_SEEALSO, "NPV"},
1549 { GNM_FUNC_HELP_END }
1550 };
1551
1552 static GnmValue *
1553 gnumeric_xnpv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1554 {
1555 gnm_float rate, *payments = NULL, *dates = NULL;
1556 gnm_float sum;
1557 int p_n, d_n, i;
1558 GnmValue *result = NULL;
1559
1560 rate = value_get_as_float (argv[0]);
1561 sum = 0;
1562
1563 /* FIXME: clearly the values should be collected as pairs so
1564 missing entries are lined up. */
1565
1566 payments = collect_floats_value (argv[1], ei->pos,
1567 COLLECT_COERCE_STRINGS,
1568 &p_n, &result);
1569 if (result)
1570 goto out;
1571
1572 dates = collect_floats_value (argv[2], ei->pos,
1573 COLLECT_COERCE_STRINGS,
1574 &d_n, &result);
1575 if (result)
1576 goto out;
1577
1578 if (p_n != d_n) {
1579 result = value_new_error_NUM (ei->pos);
1580 goto out;
1581 }
1582
1583 for (i = 0; i < p_n; i++)
1584 sum += payments[i] /
1585 pow1p (rate, (dates[i] - dates[0]) / 365.0);
1586
1587 result = value_new_float (sum);
1588 out:
1589 g_free (payments);
1590 g_free (dates);
1591
1592 return result;
1593 }
1594
1595 /***************************************************************************/
1596
1597 static GnmFuncHelp const help_xirr[] = {
1598 { GNM_FUNC_HELP_NAME, F_("XIRR:internal rate of return")},
1599 { GNM_FUNC_HELP_ARG, F_("values:cash flow")},
1600 { GNM_FUNC_HELP_ARG, F_("dates:dates of cash flow")},
1601 { GNM_FUNC_HELP_ARG, F_("guess:an estimate of what the result should be")},
1602 { GNM_FUNC_HELP_DESCRIPTION, F_("XIRR calculates the annualized internal rate of return of a cash flow at arbitrary points in time. @{values} lists the payments (negative values) and receipts (positive values) with one value for each entry in @{dates}.") },
1603 { GNM_FUNC_HELP_NOTE, F_("The optional @{guess} is needed because there can be more than one valid result. It defaults to 10%.") },
1604 { GNM_FUNC_HELP_SEEALSO, "IRR"},
1605 { GNM_FUNC_HELP_END }
1606 };
1607
1608 typedef struct {
1609 int n;
1610 const gnm_float *values;
1611 const gnm_float *dates;
1612 } gnumeric_xirr_t;
1613
1614 static GoalSeekStatus
1615 xirr_npv (gnm_float rate, gnm_float *y, void *user_data)
1616 {
1617 const gnumeric_xirr_t *p = user_data;
1618 gnm_float sum = 0;
1619 int i;
1620
1621 for (i = 0; i < p->n; i++) {
1622 gnm_float d = p->dates[i] - p->dates[0];
1623
1624 if (d < 0)
1625 return GOAL_SEEK_ERROR;
1626 sum += p->values[i] / pow1p (rate, d / 365.0);
1627 }
1628
1629 *y = sum;
1630 return GOAL_SEEK_OK;
1631 }
1632
1633 static int
1634 gnm_range_xirr (gnm_float const *xs, const gnm_float *ys,
1635 int n, gnm_float *res, gpointer user)
1636 {
1637 gnumeric_xirr_t p;
1638 gnm_float rate0 = *(gnm_float *)user;
1639 GoalSeekData data;
1640 GoalSeekStatus status;
1641
1642 p.dates = ys;
1643 p.values = xs;
1644 p.n = n;
1645
1646 goal_seek_initialize (&data);
1647 data.xmin = -1;
1648 data.xmax = MIN (1000, data.xmax);
1649
1650 status = goal_seek_newton (&xirr_npv, NULL, &data, &p, rate0);
1651 if (status != GOAL_SEEK_OK) {
1652 int i;
1653
1654 /* This is likely to be on the left side of the root. */
1655 (void)goal_seek_point (&xirr_npv, &data, &p, -1);
1656
1657 for (i = 1; i <= 1024; i += i) {
1658 (void)goal_seek_point (&xirr_npv, &data, &p, -1 + 10.0 / (i + 9));
1659 (void)goal_seek_point (&xirr_npv, &data, &p, i);
1660 status = goal_seek_bisection (&xirr_npv, &data, &p);
1661 if (status == GOAL_SEEK_OK)
1662 break;
1663 }
1664 }
1665
1666 if (status == GOAL_SEEK_OK) {
1667 *res = data.root;
1668 return 0;
1669 }
1670
1671 return 1;
1672 }
1673
1674
1675 static GnmValue *
1676 gnumeric_xirr (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1677 {
1678 gnm_float rate0 = argv[2] ? value_get_as_float (argv[2]) : 0.1;
1679
1680 return float_range_function2d (argv[0], argv[1],
1681 ei,
1682 gnm_range_xirr,
1683 COLLECT_IGNORE_BLANKS |
1684 COLLECT_COERCE_STRINGS,
1685 GNM_ERROR_VALUE,
1686 &rate0);
1687 }
1688
1689 /***************************************************************************/
1690
1691 static GnmFuncHelp const help_fv[] = {
1692 { GNM_FUNC_HELP_NAME, F_("FV:future value")},
1693 { GNM_FUNC_HELP_ARG, F_("rate:effective interest rate per period")},
1694 { GNM_FUNC_HELP_ARG, F_("nper:number of periods")},
1695 { GNM_FUNC_HELP_ARG, F_("pmt:payment at each period")},
1696 { GNM_FUNC_HELP_ARG, F_("pv:present value")},
1697 { GNM_FUNC_HELP_ARG, F_("type:payment type")},
1698 { GNM_FUNC_HELP_DESCRIPTION, F_("FV calculates the future value of @{pv} moved @{nper} periods into the future, assuming a periodic payment of @{pmt} and an interest rate of @{rate} per period.") },
1699 TYPE_HELP,
1700 { GNM_FUNC_HELP_EXAMPLES, "=FV(10%,10,1000,20000,0)" },
1701 { GNM_FUNC_HELP_SEEALSO, "PV"},
1702 { GNM_FUNC_HELP_END }
1703 };
1704
1705 static GnmValue *
1706 gnumeric_fv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1707 {
1708 gnm_float rate = value_get_as_float (argv[0]);
1709 gnm_float nper = value_get_as_float (argv[1]);
1710 gnm_float pmt = value_get_as_float (argv[2]);
1711 gnm_float pv = argv[3] ? value_get_as_float (argv[3]) : 0.;
1712 int type = value_get_paytype (argv[4]);
1713 gnm_float pvif, fvifa;
1714
1715 if (!is_valid_paytype (type))
1716 return value_new_error_VALUE (ei->pos);
1717
1718 pvif = calculate_pvif (rate, nper);
1719 fvifa = calculate_fvifa (rate, nper);
1720
1721 return value_new_float (-((pv * pvif) + pmt *
1722 (1.0 + rate * type) * fvifa));
1723 }
1724
1725 /***************************************************************************/
1726
1727 static GnmFuncHelp const help_pmt[] = {
1728 { GNM_FUNC_HELP_NAME, F_("PMT:payment for annuity")},
1729 { GNM_FUNC_HELP_ARG, F_("rate:effective annual interest rate")},
1730 { GNM_FUNC_HELP_ARG, F_("nper:number of periods")},
1731 { GNM_FUNC_HELP_ARG, F_("pv:present value")},
1732 { GNM_FUNC_HELP_ARG, F_("fv:future value")},
1733 { GNM_FUNC_HELP_ARG, F_("type:payment type")},
1734 { GNM_FUNC_HELP_DESCRIPTION, F_("PMT calculates the payment amount for an annuity.") },
1735 TYPE_HELP,
1736 { GNM_FUNC_HELP_SEEALSO, "PV,FV,RATE,ISPMT"},
1737 { GNM_FUNC_HELP_END }
1738 };
1739
1740 static GnmValue *
1741 gnumeric_pmt (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1742 {
1743 gnm_float rate = value_get_as_float (argv[0]);
1744 gnm_float nper = value_get_as_float (argv[1]);
1745 gnm_float pv = value_get_as_float (argv[2]);
1746 gnm_float fv = argv[3] ? value_get_as_float (argv[3]) : 0;
1747 int type = value_get_paytype (argv[4]);
1748
1749 if (!is_valid_paytype (type))
1750 return value_new_error_VALUE (ei->pos);
1751
1752 return value_new_float (calculate_pmt (rate, nper, pv, fv, type));
1753 }
1754
1755 /***************************************************************************/
1756
1757 static GnmFuncHelp const help_ipmt[] = {
1758 { GNM_FUNC_HELP_NAME, F_("IPMT:interest payment for period")},
1759 { GNM_FUNC_HELP_ARG, F_("rate:effective annual interest rate")},
1760 { GNM_FUNC_HELP_ARG, F_("per:period number")},
1761 { GNM_FUNC_HELP_ARG, F_("nper:number of periods")},
1762 { GNM_FUNC_HELP_ARG, F_("pv:present value")},
1763 { GNM_FUNC_HELP_ARG, F_("fv:future value")},
1764 { GNM_FUNC_HELP_ARG, F_("type:payment type")},
1765 { GNM_FUNC_HELP_DESCRIPTION, F_("IPMT calculates the interest part of an annuity's payment for period number @{per}.") },
1766 TYPE_HELP,
1767 { GNM_FUNC_HELP_EXAMPLES, "=IPMT(10%,4,10,1e6)" },
1768 { GNM_FUNC_HELP_SEEALSO, "PPMT"},
1769 { GNM_FUNC_HELP_END }
1770 };
1771
1772 static GnmValue *
1773 gnumeric_ipmt (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1774 {
1775 gnm_float rate = value_get_as_float (argv[0]);
1776 gnm_float per = value_get_as_float (argv[1]);
1777 gnm_float nper = value_get_as_float (argv[2]);
1778 gnm_float pv = value_get_as_float (argv[3]);
1779 gnm_float fv = argv[4] ? value_get_as_float (argv[4]) : 0;
1780 int type = value_get_paytype (argv[5]);
1781
1782 /*
1783 * It seems that with 20 periods, a period number of 20.99 is
1784 * valid in XL.
1785 */
1786 if (per < 1 || per >= nper + 1)
1787 return value_new_error_NUM (ei->pos);
1788
1789 if (!is_valid_paytype (type))
1790 return value_new_error_VALUE (ei->pos);
1791
1792 return value_new_float (calculate_ipmt (rate, per, nper, pv, fv, type));
1793 }
1794
1795 /***************************************************************************/
1796
1797 static GnmFuncHelp const help_ppmt[] = {
1798 { GNM_FUNC_HELP_NAME, F_("PPMT:interest payment for period")},
1799 { GNM_FUNC_HELP_ARG, F_("rate:effective annual interest rate")},
1800 { GNM_FUNC_HELP_ARG, F_("per:period number")},
1801 { GNM_FUNC_HELP_ARG, F_("nper:number of periods")},
1802 { GNM_FUNC_HELP_ARG, F_("pv:present value")},
1803 { GNM_FUNC_HELP_ARG, F_("fv:future value")},
1804 { GNM_FUNC_HELP_ARG, F_("type:payment type")},
1805 { GNM_FUNC_HELP_DESCRIPTION, F_("PPMT calculates the principal part of an annuity's payment for period number @{per}.") },
1806 TYPE_HELP,
1807 { GNM_FUNC_HELP_EXAMPLES, "=PPMT(10%,4,10,1e6)" },
1808 { GNM_FUNC_HELP_SEEALSO, "IPMT"},
1809 { GNM_FUNC_HELP_END }
1810 };
1811
1812 static GnmValue *
1813 gnumeric_ppmt (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1814 {
1815 gnm_float rate = value_get_as_float (argv[0]);
1816 gnm_float per = value_get_as_float (argv[1]);
1817 gnm_float nper = value_get_as_float (argv[2]);
1818 gnm_float pv = value_get_as_float (argv[3]);
1819 gnm_float fv = argv[4] ? value_get_as_float (argv[4]) : 0;
1820 int type = value_get_paytype (argv[5]);
1821
1822 /*
1823 * It seems that with 20 periods, a period number of 20.99 is
1824 * valid in XL.
1825 */
1826 if (per < 1 || per >= nper + 1)
1827 return value_new_error_NUM (ei->pos);
1828
1829 if (!is_valid_paytype (type))
1830 return value_new_error_VALUE (ei->pos);
1831
1832 {
1833 gnm_float pmt = calculate_pmt (rate, nper, pv, fv, type);
1834 gnm_float ipmt = calculate_ipmt (rate, per, nper, pv, fv, type);
1835 return value_new_float (pmt - ipmt);
1836 }
1837 }
1838
1839 /***************************************************************************/
1840
1841 static GnmFuncHelp const help_nper[] = {
1842 { GNM_FUNC_HELP_NAME, F_("NPER:number of periods")},
1843 { GNM_FUNC_HELP_ARG, F_("rate:effective annual interest rate")},
1844 { GNM_FUNC_HELP_ARG, F_("pmt:payment at each period")},
1845 { GNM_FUNC_HELP_ARG, F_("pv:present value")},
1846 { GNM_FUNC_HELP_ARG, F_("fv:future value")},
1847 { GNM_FUNC_HELP_ARG, F_("type:payment type")},
1848 { GNM_FUNC_HELP_DESCRIPTION, F_("NPER calculates the number of periods of an investment based on periodic constant payments and a constant interest rate.") },
1849 TYPE_HELP,
1850 { GNM_FUNC_HELP_EXAMPLES, "=NPER(6%,0,-10000,20000,0)" },
1851 { GNM_FUNC_HELP_SEEALSO, "PV,FV"},
1852 { GNM_FUNC_HELP_END }
1853 };
1854
1855 static GnmValue *
1856 gnumeric_nper (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1857 {
1858 gnm_float tmp;
1859
1860 gnm_float rate = value_get_as_float (argv[0]);
1861 gnm_float pmt = value_get_as_float (argv[1]);
1862 gnm_float pv = value_get_as_float (argv[2]);
1863 gnm_float fv = argv[3] ? value_get_as_float (argv[3]) : 0;
1864 int type = value_get_paytype (argv[4]);
1865
1866 if (rate == 0) {
1867 if (pmt == 0)
1868 return value_new_error_DIV0 (ei->pos);
1869 else
1870 return value_new_float (-(fv + pv) / pmt);
1871 }
1872
1873 if (rate <= -1)
1874 return value_new_error_NUM (ei->pos);
1875
1876 if (!is_valid_paytype (type))
1877 return value_new_error_VALUE (ei->pos);
1878
1879 tmp = (pmt * (1.0 + rate * type) - fv * rate) /
1880 (pv * rate + pmt * (1.0 + rate * type));
1881 if (tmp <= 0.0)
1882 return value_new_error_VALUE (ei->pos);
1883
1884 return value_new_float (gnm_log (tmp) / gnm_log1p (rate));
1885 }
1886
1887 /***************************************************************************/
1888
1889 static GnmFuncHelp const help_duration[] = {
1890 { GNM_FUNC_HELP_NAME, F_("DURATION:the (Macaulay) duration of a security")},
1891 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
1892 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
1893 { GNM_FUNC_HELP_ARG, F_("coupon:annual coupon rate")},
1894 { GNM_FUNC_HELP_ARG, F_("yield:annual yield of security")},
1895 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
1896 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
1897 { GNM_FUNC_HELP_DESCRIPTION, F_("DURATION calculates the (Macaulay) duration of a security.") },
1898 FREQ_HELP,
1899 GNM_DATE_BASIS_HELP
1900 { GNM_FUNC_HELP_EXAMPLES, "=DURATION(TODAY(),TODAY()+365,0.05,0.08,4)"},
1901 { GNM_FUNC_HELP_EXAMPLES, "=DURATION(TODAY(),TODAY()+366,0.05,0.08,4)"},
1902 { GNM_FUNC_HELP_SEEALSO, "MDURATION, G_DURATION"},
1903 { GNM_FUNC_HELP_END }
1904 };
1905
1906 static GnmValue *
1907 gnumeric_duration (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1908 {
1909 GDate nSettle, nMat;
1910 gnm_float fCoup, fYield;
1911 gnm_float fNumOfCoups;
1912 GoCouponConvention conv;
1913
1914 conv.date_conv = sheet_date_conv (ei->pos->sheet);
1915 conv.eom = TRUE;
1916
1917 fCoup = value_get_as_float (argv[2]);
1918 fYield = value_get_as_float (argv[3]);
1919 conv.freq = value_get_freq (argv[4]);
1920 conv.basis = value_get_basis (argv[5], GO_BASIS_MSRB_30_360);
1921
1922 if (!datetime_value_to_g (&nSettle, argv[0], conv.date_conv) ||
1923 !datetime_value_to_g (&nMat, argv[1], conv.date_conv) ||
1924 !is_valid_basis (conv.basis) ||
1925 !is_valid_freq (conv.freq))
1926 return value_new_error_NUM (ei->pos);
1927
1928 fNumOfCoups = coupnum (&nSettle, &nMat, &conv);
1929 return get_duration (&nSettle, &nMat, fCoup, fYield, conv.freq,
1930 conv.basis, fNumOfCoups);
1931 }
1932
1933 /***************************************************************************/
1934
1935 static GnmFuncHelp const help_g_duration[] = {
1936 { GNM_FUNC_HELP_NAME, F_("G_DURATION:the duration of a investment") },
1937 { GNM_FUNC_HELP_ARG, F_("rate:effective annual interest rate")},
1938 { GNM_FUNC_HELP_ARG, F_("pv:present value")},
1939 { GNM_FUNC_HELP_ARG, F_("fv:future value")},
1940 { GNM_FUNC_HELP_DESCRIPTION, F_("G_DURATION calculates the number of periods needed for an investment to attain a desired value.") },
1941 { GNM_FUNC_HELP_ODF, F_("G_DURATION is the OpenFormula function PDURATION.") },
1942 { GNM_FUNC_HELP_EXAMPLES, "=G_DURATION(0.08,1000,2000)"},
1943 { GNM_FUNC_HELP_SEEALSO, "FV,PV,DURATION,MDURATION"},
1944 { GNM_FUNC_HELP_END }
1945 };
1946
1947 static GnmValue *
1948 gnumeric_g_duration (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1949 {
1950 gnm_float rate, pv, fv;
1951
1952 rate = value_get_as_float (argv[0]);
1953 pv = value_get_as_float (argv[1]);
1954 fv = value_get_as_float (argv[2]);
1955
1956 if (rate <= 0)
1957 return value_new_error_DIV0 (ei->pos);
1958 else if (fv == 0 || pv == 0)
1959 return value_new_error_DIV0 (ei->pos);
1960 else if (fv / pv < 0)
1961 return value_new_error_VALUE (ei->pos);
1962
1963 return value_new_float (gnm_log (fv / pv) / gnm_log1p (rate));
1964
1965 }
1966
1967 /***************************************************************************/
1968
1969 static GnmFuncHelp const help_fvschedule[] = {
1970 { GNM_FUNC_HELP_NAME, F_("FVSCHEDULE:future value")},
1971 { GNM_FUNC_HELP_ARG, F_("principal:initial value")},
1972 { GNM_FUNC_HELP_ARG, F_("schedule:range of interest rates")},
1973 { GNM_FUNC_HELP_DESCRIPTION, F_("FVSCHEDULE calculates the future value of @{principal} after applying a range of interest rates with compounding.") },
1974 { GNM_FUNC_HELP_EXAMPLES, "=FVSCHEDULE(1000,{0.1;0.02;0.1})" },
1975 { GNM_FUNC_HELP_SEEALSO, "FV"},
1976 { GNM_FUNC_HELP_END }
1977 };
1978
1979 static GnmValue *
1980 gnumeric_fvschedule (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1981 {
1982 gnm_float pv, *schedule = NULL;
1983 GnmValue *result = NULL;
1984 int i, n;
1985
1986 pv = value_get_as_float (argv[0]);
1987 schedule = collect_floats_value (argv[1], ei->pos,
1988 COLLECT_IGNORE_BLANKS, &n, &result);
1989 if (result)
1990 goto out;
1991
1992 for (i = 0; i < n; i++)
1993 pv *= 1 + schedule[i];
1994
1995 result = value_new_float (pv);
1996 out:
1997 g_free (schedule);
1998
1999 return result;
2000
2001 }
2002
2003 /***************************************************************************/
2004
2005 static GnmFuncHelp const help_euro[] = {
2006 { GNM_FUNC_HELP_NAME, F_("EURO:equivalent of 1 EUR")},
2007 { GNM_FUNC_HELP_ARG, F_("currency:three-letter currency code")},
2008 { GNM_FUNC_HELP_DESCRIPTION, F_("EURO calculates the national currency amount corresponding to 1 EUR for any of the national currencies that were replaced by the Euro on its introduction.") },
2009 { GNM_FUNC_HELP_NOTE, F_("@{currency} must be one of "
2010 "ATS (Austria), "
2011 "BEF (Belgium), "
2012 "CYP (Cyprus), "
2013 "DEM (Germany), "
2014 "EEK (Estonia), "
2015 "ESP (Spain), "
2016 "EUR (Euro), "
2017 "FIM (Finland), "
2018 "FRF (France), "
2019 "GRD (Greece), "
2020 "IEP (Ireland), "
2021 "ITL (Italy), "
2022 "LUF (Luxembourg), "
2023 "MTL (Malta), "
2024 "NLG (The Netherlands), "
2025 "PTE (Portugal), "
2026 "SIT (Slovenia), or "
2027 "SKK (Slovakia).") },
2028 { GNM_FUNC_HELP_NOTE, F_("This function is not likely to be useful anymore.") },
2029 { GNM_FUNC_HELP_EXAMPLES, "=EURO(\"DEM\")" },
2030 { GNM_FUNC_HELP_SEEALSO, "EUROCONVERT"},
2031 { GNM_FUNC_HELP_END }
2032 };
2033
2034 /*
2035 * Returns one euro as a given national currency. On error, negative
2036 * value is returned.
2037 */
2038 static gnm_float
2039 one_euro (char const *str)
2040 {
2041 switch (*str) {
2042 case 'A':
2043 if (strncmp ("ATS", str, 3) == 0)
2044 return GNM_const (13.7603);
2045 break;
2046 case 'B':
2047 if (strncmp ("BEF", str, 3) == 0)
2048 return GNM_const (40.3399);
2049 break;
2050 case 'C':
2051 if (strncmp ("CYP", str, 3) == 0)
2052 return GNM_const (0.585274);
2053 break;
2054 case 'D':
2055 if (strncmp ("DEM", str, 3) == 0)
2056 return GNM_const (1.95583);
2057 break;
2058 case 'E':
2059 if (strncmp ("ESP", str, 3) == 0)
2060 return GNM_const (166.386);
2061 else if (strncmp ("EEK", str, 3) == 0)
2062 return GNM_const (15.6466);
2063 else if (strncmp ("EUR", str, 3) == 0)
2064 return GNM_const (1.0);
2065 break;
2066 case 'F':
2067 if (strncmp ("FIM", str, 3) == 0)
2068 return GNM_const (5.94573);
2069 else if (strncmp ("FRF", str, 3) == 0)
2070 return GNM_const (6.55957);
2071 break;
2072 case 'G':
2073 if (strncmp ("GRD", str, 3) == 0)
2074 return GNM_const (340.75);
2075 break;
2076 case 'I':
2077 if (strncmp ("IEP", str, 3) == 0)
2078 return GNM_const (0.787564);
2079 else if (strncmp ("ITL", str, 3) == 0)
2080 return GNM_const (1936.27);
2081 break;
2082 case 'L':
2083 if (strncmp ("LUF", str, 3) == 0)
2084 return GNM_const (40.3399);
2085 break;
2086 case 'M':
2087 if (strncmp ("MTL", str, 3) == 0)
2088 return GNM_const (0.429300);
2089 break;
2090 case 'N':
2091 if (strncmp ("NLG", str, 3) == 0)
2092 return GNM_const (2.20371);
2093 break;
2094 case 'P':
2095 if (strncmp ("PTE", str, 3) == 0)
2096 return GNM_const (200.482);
2097 break;
2098 case 'S':
2099 if (strncmp ("SIT", str, 3) == 0)
2100 return GNM_const (239.640);
2101 else if (strncmp ("SKK", str, 3) == 0)
2102 return GNM_const (30.1260);
2103 break;
2104 default:
2105 break;
2106 }
2107
2108 return -1;
2109 }
2110
2111 static GnmValue *
2112 gnumeric_euro (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2113 {
2114 char const *str = value_peek_string (argv[0]);
2115 gnm_float v = one_euro (str);
2116
2117 if (v >= 0)
2118 return value_new_float (v);
2119 else
2120 return value_new_error_NUM (ei->pos);
2121 }
2122
2123 /*
2124 * Returns one euro as a given national currency. On error, negative
2125 * value is returned.
2126 */
2127 static int
2128 euro_local_rounding (char const *str)
2129 {
2130 switch (*str) {
2131 case 'A':
2132 /* if (strncmp ("ATS", str, 3) == 0) */
2133 /* return 2; */
2134 break;
2135 case 'B':
2136 if (strncmp ("BEF", str, 3) == 0)
2137 return 0;
2138 break;
2139 case 'C':
2140 /* if (strncmp ("CYP", str, 3) == 0) */
2141 /* return 2; /\*??*\/ */
2142 break;
2143 case 'D':
2144 /* if (strncmp ("DEM", str, 3) == 0) */
2145 /* return 2; */
2146 break;
2147 case 'E':
2148 if (strncmp ("ESP", str, 3) == 0)
2149 return 0;
2150 /* else if (strncmp ("EEK", str, 3) == 0) */
2151 /* return 2; */
2152 /* else if (strncmp ("EUR", str, 3) == 0) */
2153 /* return 2; */
2154 break;
2155 case 'F':
2156 /* if (strncmp ("FIM", str, 3) == 0) */
2157 /* return 2 */
2158 /* else if (strncmp ("FRF", str, 3) == 0) */
2159 /* return 2; */
2160 break;
2161 case 'G':
2162 if (strncmp ("GRD", str, 3) == 0)
2163 return 0;
2164 break;
2165 case 'I':
2166 if (strncmp ("ITL", str, 3) == 0)
2167 return 0;
2168 /* else if (strncmp ("IEP", str, 3) == 0) */
2169 /* return 2; */
2170 break;
2171 case 'L':
2172 if (strncmp ("LUF", str, 3) == 0)
2173 return 0;
2174 break;
2175 case 'M':
2176 /* if (strncmp ("MTL", str, 3) == 0) */
2177 /* return 2; /\* ?? *\/ */
2178 break;
2179 case 'N':
2180 /* if (strncmp ("NLG", str, 3) == 0) */
2181 /* return 2; */
2182 break;
2183 case 'P':
2184 if (strncmp ("PTE", str, 3) == 0)
2185 return 0;
2186 break;
2187 case 'S':
2188 /* if (strncmp ("SIT", str, 3) == 0) */
2189 /* return 2; */
2190 /* else if (strncmp ("SKK", str, 3) == 0) */
2191 /* return 2; /\* ?? *\/ */
2192 break;
2193 default:
2194 break;
2195 }
2196
2197 return 2;
2198 }
2199
2200 /***************************************************************************/
2201
2202 static GnmFuncHelp const help_euroconvert[] = {
2203 { GNM_FUNC_HELP_NAME, F_("EUROCONVERT:pre-Euro amount from one currency to another")},
2204 { GNM_FUNC_HELP_ARG, F_("n:amount")},
2205 { GNM_FUNC_HELP_ARG, F_("source:three-letter source currency code")},
2206 { GNM_FUNC_HELP_ARG, F_("target:three-letter target currency code")},
2207 { GNM_FUNC_HELP_ARG, F_("full_precision:whether to provide the full precision; defaults to false")},
2208 { GNM_FUNC_HELP_ARG, F_("triangulation_precision:number of digits (at least 3) to be rounded to after conversion of the source currency to euro; defaults to no rounding")},
2209 { GNM_FUNC_HELP_DESCRIPTION, F_("EUROCONVERT converts @{n} units of currency @{source} to currency @{target}. The rates used are the official ones used on the introduction of the Euro.") },
2210 { GNM_FUNC_HELP_NOTE, F_("If @{full_precision} is true, the result is not rounded; if it false the result is rounded to 0 or 2 decimals depending on the target currency; defaults to false.")},
2211 { GNM_FUNC_HELP_NOTE, F_("@{source} and @{target} must be one of the currencies listed for the EURO function.") },
2212 { GNM_FUNC_HELP_NOTE, F_("This function is not likely to be useful anymore.") },
2213 { GNM_FUNC_HELP_EXAMPLES, "=EUROCONVERT(1,\"DEM\",\"ITL\")" },
2214 { GNM_FUNC_HELP_EXAMPLES, "=EUROCONVERT(1,\"DEM\",\"ITL\",FALSE)" },
2215 { GNM_FUNC_HELP_EXAMPLES, "=EUROCONVERT(1,\"DEM\",\"ITL\",FALSE,3)" },
2216 { GNM_FUNC_HELP_SEEALSO, "EURO"},
2217 { GNM_FUNC_HELP_END }
2218 };
2219
2220
2221
2222 static GnmValue *
2223 gnumeric_euroconvert (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2224 {
2225 gnm_float c1 = one_euro (value_peek_string (argv[1]));
2226 gnm_float c2 = one_euro (value_peek_string (argv[2]));
2227
2228 if (c1 >= 0 && c2 >= 0) {
2229 gnm_float n = value_get_as_float (argv[0]);
2230 gnm_float inter = n / c1;
2231 gboolean err = FALSE;
2232 if (argv[3] != NULL && argv[4] != NULL) {
2233 int decimals = value_get_as_int (argv[4]);
2234 if (decimals < 3 || decimals > GNM_MAX_EXP)
2235 return value_new_error_VALUE (ei->pos);
2236 else {
2237 gnm_float p10 = gnm_pow10 (decimals);
2238 inter = gnm_fake_trunc (inter * p10 + 0.5) / p10;
2239 }
2240 }
2241 inter = inter * c2;
2242 if (argv[3] != NULL && !value_get_as_bool (argv[3], &err) && !err) {
2243 int decimals = euro_local_rounding (value_peek_string (argv[2]));
2244 gnm_float p10 = gnm_pow10 (decimals);
2245 inter = gnm_fake_trunc (inter * p10 + 0.5) / p10;
2246 }
2247 return value_new_float (inter);
2248 } else
2249 return value_new_error_VALUE (ei->pos);
2250 }
2251
2252 /***************************************************************************/
2253
2254 static GnmFuncHelp const help_price[] = {
2255 { GNM_FUNC_HELP_NAME, F_("PRICE:price of a security")},
2256 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
2257 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
2258 { GNM_FUNC_HELP_ARG, F_("rate:nominal annual interest rate")},
2259 { GNM_FUNC_HELP_ARG, F_("yield:annual yield of security")},
2260 { GNM_FUNC_HELP_ARG, F_("redemption:amount received at maturity")},
2261 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
2262 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
2263 { GNM_FUNC_HELP_DESCRIPTION, F_("PRICE calculates the price per $100 face value of a security that pays periodic interest.") },
2264 FREQ_HELP,
2265 GNM_DATE_BASIS_HELP
2266 { GNM_FUNC_HELP_SEEALSO, "YIELD,DURATION"},
2267 { GNM_FUNC_HELP_END }
2268 };
2269
2270 static GnmValue *
2271 gnumeric_price (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2272 {
2273 GDate settlement, maturity;
2274 gnm_float rate, yield, redemption;
2275 GoCouponConvention conv;
2276
2277 conv.date_conv = sheet_date_conv (ei->pos->sheet);
2278
2279 rate = value_get_as_float (argv[2]);
2280 yield = value_get_as_float (argv[3]);
2281 redemption = value_get_as_float (argv[4]);
2282 conv.freq = value_get_freq (argv[5]);
2283 conv.eom = TRUE;
2284 conv.basis = value_get_basis (argv[6], GO_BASIS_MSRB_30_360);
2285
2286 if (!datetime_value_to_g (&settlement, argv[0], conv.date_conv) ||
2287 !datetime_value_to_g (&maturity, argv[1], conv.date_conv))
2288 return value_new_error_VALUE (ei->pos);
2289
2290 if (!is_valid_basis (conv.basis)
2291 || !is_valid_freq (conv.freq)
2292 || g_date_compare (&settlement, &maturity) > 0)
2293 return value_new_error_NUM (ei->pos);
2294
2295 if (rate < 0.0 || yield < 0.0 || redemption <= 0.0)
2296 return value_new_error_NUM (ei->pos);
2297
2298 return value_new_float (price (&settlement, &maturity, rate, yield,
2299 redemption, &conv));
2300 }
2301
2302 /***************************************************************************/
2303
2304 static GnmFuncHelp const help_yield[] = {
2305 { GNM_FUNC_HELP_NAME, F_("YIELD:yield of a security")},
2306 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
2307 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
2308 { GNM_FUNC_HELP_ARG, F_("rate:nominal annual interest rate")},
2309 { GNM_FUNC_HELP_ARG, F_("price:price of security")},
2310 { GNM_FUNC_HELP_ARG, F_("redemption:amount received at maturity")},
2311 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
2312 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
2313 { GNM_FUNC_HELP_DESCRIPTION, F_("YIELD calculates the yield of a security that pays periodic interest.") },
2314 FREQ_HELP,
2315 GNM_DATE_BASIS_HELP
2316 { GNM_FUNC_HELP_SEEALSO, "PRICE,DURATION"},
2317 { GNM_FUNC_HELP_END }
2318 };
2319
2320 typedef struct {
2321 GDate settlement, maturity;
2322 gnm_float rate, redemption, par;
2323 GoCouponConvention conv;
2324 } gnumeric_yield_t;
2325
2326 static GoalSeekStatus
2327 gnumeric_yield_f (gnm_float yield, gnm_float *y, void *user_data)
2328 {
2329 gnumeric_yield_t *data = user_data;
2330
2331 *y = price (&data->settlement, &data->maturity, data->rate, yield,
2332 data->redemption, &data->conv)
2333 - data->par;
2334 return GOAL_SEEK_OK;
2335 }
2336
2337
2338 static GnmValue *
2339 gnumeric_yield (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2340 {
2341 gnm_float n;
2342 gnumeric_yield_t udata;
2343
2344 udata.rate = value_get_as_float (argv[2]);
2345 udata.par = value_get_as_float (argv[3]);
2346 udata.redemption = value_get_as_float (argv[4]);
2347 udata.conv.freq = value_get_freq (argv[5]);
2348 udata.conv.basis = value_get_basis (argv[6], GO_BASIS_MSRB_30_360);
2349 udata.conv.eom = TRUE;
2350 udata.conv.date_conv = sheet_date_conv (ei->pos->sheet);
2351
2352 if (!datetime_value_to_g (&udata.settlement, argv[0], udata.conv.date_conv) ||
2353 !datetime_value_to_g (&udata.maturity, argv[1], udata.conv.date_conv))
2354 return value_new_error_VALUE (ei->pos);
2355
2356 if (!is_valid_basis (udata.conv.basis)
2357 || !is_valid_freq (udata.conv.freq)
2358 || g_date_compare (&udata.settlement, &udata.maturity) > 0)
2359 return value_new_error_NUM (ei->pos);
2360
2361 if (udata.rate < 0.0 || udata.par < 0.0 || udata.redemption <= 0.0)
2362 return value_new_error_NUM (ei->pos);
2363
2364 n = coupnum (&udata.settlement, &udata.maturity, &udata.conv);
2365 if (n <= 1.0) {
2366 gnm_float a = go_coupdaybs (&udata.settlement, &udata.maturity,
2367 &udata.conv);
2368 gnm_float d = go_coupdaysnc (&udata.settlement, &udata.maturity,
2369 &udata.conv);
2370 gnm_float e = go_coupdays (&udata.settlement, &udata.maturity,
2371 &udata.conv);
2372
2373 gnm_float coeff = udata.conv.freq * e / d;
2374 gnm_float num = (udata.redemption / 100.0 +
2375 udata.rate / udata.conv.freq)
2376 - (udata.par / 100.0 + (a / e *
2377 udata.rate / udata.conv.freq));
2378 gnm_float den = udata.par / 100.0 + (a / e * udata.rate /
2379 udata.conv.freq);
2380
2381 return value_new_float (num / den * coeff);
2382 } else {
2383 GoalSeekData data;
2384 GoalSeekStatus status;
2385 gnm_float yield0 = 0.1;
2386
2387 goal_seek_initialize (&data);
2388 data.xmin = MAX (data.xmin, 0);
2389 data.xmax = MIN (data.xmax, 1000);
2390
2391 /* Newton search from guess. */
2392 status = goal_seek_newton (&gnumeric_yield_f, NULL,
2393 &data, &udata, yield0);
2394
2395 if (status != GOAL_SEEK_OK) {
2396 for (yield0 = 1e-10; yield0 < data.xmax; yield0 *= 2)
2397 goal_seek_point (&gnumeric_yield_f, &data,
2398 &udata, yield0);
2399
2400 /* Pray we got both sides of the root. */
2401 status = goal_seek_bisection (&gnumeric_yield_f, &data,
2402 &udata);
2403 }
2404
2405 if (status != GOAL_SEEK_OK)
2406 return value_new_error_NUM (ei->pos);
2407 return value_new_float (data.root);
2408 }
2409 }
2410
2411 /***************************************************************************/
2412
2413 static GnmFuncHelp const help_yielddisc[] = {
2414 { GNM_FUNC_HELP_NAME, F_("YIELDDISC:yield of a discounted security")},
2415 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
2416 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
2417 { GNM_FUNC_HELP_ARG, F_("price:price of security")},
2418 { GNM_FUNC_HELP_ARG, F_("redemption:amount received at maturity")},
2419 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
2420 { GNM_FUNC_HELP_DESCRIPTION, F_("YIELDDISC calculates the yield of a discounted security.") },
2421 GNM_DATE_BASIS_HELP
2422 { GNM_FUNC_HELP_SEEALSO, "PRICE,DURATION"},
2423 { GNM_FUNC_HELP_END }
2424 };
2425
2426 static GnmValue *
2427 gnumeric_yielddisc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2428 {
2429 GDate settlement, maturity;
2430 gnm_float fPrice, fRedemp;
2431 gint basis;
2432 gnm_float ret, yfrac;
2433 GODateConventions const *date_conv =
2434 sheet_date_conv (ei->pos->sheet);
2435
2436 fPrice = value_get_as_float (argv[2]);
2437 fRedemp = value_get_as_float (argv[3]);
2438 basis = value_get_basis (argv[4], GO_BASIS_MSRB_30_360);
2439
2440 if (!is_valid_basis (basis) ||
2441 !datetime_value_to_g (&settlement, argv[0], date_conv) ||
2442 !datetime_value_to_g (&maturity, argv[1], date_conv))
2443 return value_new_error_NUM (ei->pos);
2444
2445 if (fRedemp <= 0 ||
2446 fPrice <= 0 ||
2447 g_date_compare (&settlement, &maturity) >= 0)
2448 return value_new_error_NUM (ei->pos);
2449
2450 ret = (fRedemp / fPrice) - 1;
2451 yfrac = yearfrac (&settlement, &maturity, basis);
2452
2453 return value_new_float (ret / yfrac);
2454 }
2455
2456 /***************************************************************************/
2457
2458 static GnmFuncHelp const help_yieldmat[] = {
2459 { GNM_FUNC_HELP_NAME, F_("YIELDMAT:yield of a security")},
2460 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
2461 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
2462 { GNM_FUNC_HELP_ARG, F_("issue:date of issue")},
2463 { GNM_FUNC_HELP_ARG, F_("rate:nominal annual interest rate")},
2464 { GNM_FUNC_HELP_ARG, F_("price:price of security")},
2465 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
2466 { GNM_FUNC_HELP_DESCRIPTION, F_("YIELDMAT calculates the yield of a security for which the interest is paid at maturity date.") },
2467 GNM_DATE_BASIS_HELP
2468 { GNM_FUNC_HELP_SEEALSO, "YIELDDISC,YIELD"},
2469 { GNM_FUNC_HELP_END }
2470 };
2471
2472 static GnmValue *
2473 gnumeric_yieldmat (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2474 {
2475 GDate nSettle, nMat, nIssue;
2476 gnm_float fRate, fPrice;
2477 gint basis;
2478 GODateConventions const *date_conv =
2479 sheet_date_conv (ei->pos->sheet);
2480
2481 fRate = value_get_as_float (argv[3]);
2482 fPrice = value_get_as_float (argv[4]);
2483 basis = value_get_basis (argv[5], GO_BASIS_MSRB_30_360);
2484
2485 if (!is_valid_basis (basis) ||
2486 fRate < 0 ||
2487 !datetime_value_to_g (&nSettle, argv[0], date_conv) ||
2488 !datetime_value_to_g (&nMat, argv[1], date_conv) ||
2489 !datetime_value_to_g (&nIssue, argv[2], date_conv))
2490 return value_new_error_NUM (ei->pos);
2491
2492 return get_yieldmat (&nSettle, &nMat, &nIssue, fRate, fPrice, basis);
2493 }
2494
2495 /***************************************************************************/
2496
2497 static GnmFuncHelp const help_oddfprice[] = {
2498 { GNM_FUNC_HELP_NAME, F_("ODDFPRICE:price of a security that has an odd first period")},
2499 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
2500 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
2501 { GNM_FUNC_HELP_ARG, F_("issue:date of issue")},
2502 { GNM_FUNC_HELP_ARG, F_("first_interest:first interest date")},
2503 { GNM_FUNC_HELP_ARG, F_("rate:nominal annual interest rate")},
2504 { GNM_FUNC_HELP_ARG, F_("yield:annual yield of security")},
2505 { GNM_FUNC_HELP_ARG, F_("redemption:amount received at maturity")},
2506 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
2507 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
2508 { GNM_FUNC_HELP_DESCRIPTION, F_("ODDFPRICE calculates the price per $100 face value of a security that pays periodic interest, but has an odd first period.") },
2509 FREQ_HELP,
2510 GNM_DATE_BASIS_HELP
2511 { GNM_FUNC_HELP_SEEALSO, "ODDLPRICE,ODDFYIELD"},
2512 { GNM_FUNC_HELP_END }
2513 };
2514
2515 static gnm_float
2516 date_ratio (GDate const *d1, const GDate *d2, const GDate *d3,
2517 GoCouponConvention const *conv)
2518 {
2519 GDate next_coupon, prev_coupon;
2520 gnm_float res;
2521
2522 if (!g_date_valid (d1) || !g_date_valid (d2) || !g_date_valid (d3))
2523 return gnm_nan;
2524
2525 go_coup_cd (&next_coupon, d1, d3, conv->freq, conv->eom, TRUE);
2526 go_coup_cd (&prev_coupon, d1, d3, conv->freq, conv->eom, FALSE);
2527
2528 if (!g_date_valid (&prev_coupon) || !g_date_valid (&next_coupon))
2529 return gnm_nan;
2530
2531 if (g_date_compare (&next_coupon, d2) >= 0)
2532 return go_date_days_between_basis (d1, d2, conv->basis) /
2533 go_coupdays (&prev_coupon, &next_coupon, conv);
2534
2535 res = go_date_days_between_basis (d1, &next_coupon, conv->basis) /
2536 go_coupdays (&prev_coupon, &next_coupon, conv);
2537 while (1) {
2538 prev_coupon = next_coupon;
2539 gnm_date_add_months (&next_coupon, 12 / conv->freq);
2540 if (!g_date_valid (&next_coupon))
2541 return gnm_nan;
2542 if (g_date_compare (&next_coupon, d2) >= 0) {
2543 res += go_date_days_between_basis (&prev_coupon, d2, conv->basis) /
2544 go_coupdays (&prev_coupon, &next_coupon, conv);
2545 return res;
2546 }
2547 res += 1;
2548 }
2549 }
2550
2551 static gnm_float
2552 calc_oddfprice (const GDate *settlement, const GDate *maturity,
2553 const GDate *issue, const GDate *first_coupon,
2554 gnm_float rate, gnm_float yield, gnm_float redemption,
2555 GoCouponConvention const *conv)
2556
2557 {
2558 gnm_float a = go_date_days_between_basis (issue, settlement, conv->basis);
2559 gnm_float ds = go_date_days_between_basis (settlement, first_coupon, conv->basis);
2560 gnm_float df = go_date_days_between_basis (issue, first_coupon, conv->basis);
2561 gnm_float e = go_coupdays (settlement, maturity, conv);
2562 int n = (int)coupnum (settlement, maturity, conv);
2563 gnm_float scale = 100.0 * rate / conv->freq;
2564 gnm_float f = 1.0 + yield / conv->freq;
2565 gnm_float sum, term1, term2;
2566
2567 if (ds > e) {
2568 /* Odd-long corrections. */
2569 switch (conv->basis) {
2570 case GO_BASIS_MSRB_30_360:
2571 case GO_BASIS_30E_360: {
2572 int cdays = go_date_days_between_basis (first_coupon, maturity, conv->basis);
2573 n = 1 + (int)gnm_ceil (cdays / e);
2574 break;
2575 }
2576
2577 default: {
2578 GDate d = *first_coupon;
2579
2580 for (n = 0; 1; n++) {
2581 GDate prev_date = d;
2582 gnm_date_add_months (&d, 12 / conv->freq);
2583 if (g_date_compare (&d, maturity) >= 0) {
2584 n += (int)gnm_ceil (go_date_days_between_basis (&prev_date, maturity, conv->basis) /
2585 go_coupdays (&prev_date, &d, conv))
2586 + 1;
2587 break;
2588 }
2589 }
2590 a = e * date_ratio (issue, settlement, first_coupon, conv);
2591 ds = e * date_ratio (settlement, first_coupon, first_coupon, conv);
2592 df = e * date_ratio (issue, first_coupon, first_coupon, conv);
2593 }
2594 }
2595 }
2596
2597 term1 = redemption / gnm_pow (f, n - 1.0 + ds / e);
2598 term2 = (df / e) / gnm_pow (f, ds / e);
2599 sum = gnm_pow (f, -ds / e) *
2600 (gnm_pow (f, -n) - 1 / f) / (1 / f - 1);
2601
2602 return term1 + scale * (term2 + sum - a / e);
2603 }
2604
2605
2606
2607 static GnmValue *
2608 gnumeric_oddfprice (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2609 {
2610 GDate settlement, maturity, issue, first_coupon;
2611 gnm_float rate, yield, redemption;
2612 GoCouponConvention conv;
2613
2614 rate = value_get_as_float (argv[4]);
2615 yield = value_get_as_float (argv[5]);
2616 redemption = value_get_as_float (argv[6]);
2617
2618 conv.eom = TRUE;
2619 conv.freq = value_get_freq (argv[7]);
2620 conv.basis = value_get_basis (argv[8], GO_BASIS_MSRB_30_360);
2621 conv.date_conv = sheet_date_conv (ei->pos->sheet);
2622
2623 if (!datetime_value_to_g (&settlement, argv[0], conv.date_conv) ||
2624 !datetime_value_to_g (&maturity, argv[1], conv.date_conv) ||
2625 !datetime_value_to_g (&issue, argv[2], conv.date_conv) ||
2626 !datetime_value_to_g (&first_coupon, argv[3], conv.date_conv))
2627 return value_new_error_VALUE (ei->pos);
2628
2629 if (!is_valid_basis (conv.basis)
2630 || !is_valid_freq (conv.freq)
2631 || g_date_compare (&issue, &settlement) > 0
2632 || g_date_compare (&settlement, &first_coupon) > 0
2633 || g_date_compare (&first_coupon, &maturity) > 0)
2634 return value_new_error_NUM (ei->pos);
2635
2636 if (rate < 0.0 || yield < 0.0 || redemption <= 0.0)
2637 return value_new_error_NUM (ei->pos);
2638
2639 return value_new_float
2640 (calc_oddfprice
2641 (&settlement, &maturity, &issue, &first_coupon,
2642 rate, yield, redemption, &conv));
2643 }
2644
2645 /***************************************************************************/
2646
2647 static GnmFuncHelp const help_oddfyield[] = {
2648 { GNM_FUNC_HELP_NAME, F_("ODDFYIELD:yield of a security that has an odd first period")},
2649 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
2650 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
2651 { GNM_FUNC_HELP_ARG, F_("issue:date of issue")},
2652 { GNM_FUNC_HELP_ARG, F_("first_interest:first interest date")},
2653 { GNM_FUNC_HELP_ARG, F_("rate:nominal annual interest rate")},
2654 { GNM_FUNC_HELP_ARG, F_("price:price of security")},
2655 { GNM_FUNC_HELP_ARG, F_("redemption:amount received at maturity")},
2656 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
2657 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
2658 { GNM_FUNC_HELP_DESCRIPTION, F_("ODDFYIELD calculates the yield of a security that pays periodic interest, but has an odd first period.") },
2659 FREQ_HELP,
2660 GNM_DATE_BASIS_HELP
2661 { GNM_FUNC_HELP_SEEALSO, "ODDFPRICE,ODDLYIELD"},
2662 { GNM_FUNC_HELP_END }
2663 };
2664
2665 struct gnumeric_oddyield_f {
2666 GDate settlement, maturity, issue, first_coupon;
2667 gnm_float rate, price, redemption;
2668 GoCouponConvention conv;
2669 };
2670
2671 static GoalSeekStatus
2672 gnumeric_oddyield_f (gnm_float yield, gnm_float *y, void *user_data)
2673 {
2674 struct gnumeric_oddyield_f *data = user_data;
2675
2676 *y = calc_oddfprice (&data->settlement, &data->maturity,
2677 &data->issue, &data->first_coupon,
2678 data->rate, yield,
2679 data->redemption, &data->conv)
2680 - data->price;
2681 return GOAL_SEEK_OK;
2682 }
2683
2684 static GnmValue *
2685 gnumeric_oddfyield (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2686 {
2687 struct gnumeric_oddyield_f udata;
2688 GoalSeekData data;
2689 GoalSeekStatus status;
2690 gnm_float yield0 = 0.1;
2691
2692 udata.rate = value_get_as_float (argv[4]);
2693 udata.price = value_get_as_float (argv[5]);
2694 udata.redemption = value_get_as_float (argv[6]);
2695
2696 udata.conv.eom = TRUE;
2697 udata.conv.freq = value_get_freq (argv[7]);
2698 udata.conv.basis = value_get_basis (argv[8], GO_BASIS_MSRB_30_360);
2699 udata.conv.date_conv = sheet_date_conv (ei->pos->sheet);
2700
2701 if (!datetime_value_to_g (&udata.settlement, argv[0], udata.conv.date_conv) ||
2702 !datetime_value_to_g (&udata.maturity, argv[1], udata.conv.date_conv) ||
2703 !datetime_value_to_g (&udata.issue, argv[2], udata.conv.date_conv) ||
2704 !datetime_value_to_g (&udata.first_coupon, argv[3], udata.conv.date_conv))
2705 return value_new_error_VALUE (ei->pos);
2706
2707 if (!is_valid_basis (udata.conv.basis)
2708 || !is_valid_freq (udata.conv.freq)
2709 || g_date_compare (&udata.issue, &udata.settlement) > 0
2710 || g_date_compare (&udata.settlement, &udata.first_coupon) > 0
2711 || g_date_compare (&udata.first_coupon, &udata.maturity) > 0)
2712 return value_new_error_NUM (ei->pos);
2713
2714 if (udata.rate < 0.0 || udata.price <= 0.0 || udata.redemption <= 0.0)
2715 return value_new_error_NUM (ei->pos);
2716
2717 goal_seek_initialize (&data);
2718 data.xmin = MAX (data.xmin, 0);
2719 data.xmax = MIN (data.xmax, 1000);
2720
2721 /* Newton search from guess. */
2722 status = goal_seek_newton (&gnumeric_oddyield_f, NULL,
2723 &data, &udata, yield0);
2724
2725 if (status != GOAL_SEEK_OK) {
2726 for (yield0 = 1e-10; yield0 < data.xmax; yield0 *= 2)
2727 goal_seek_point (&gnumeric_oddyield_f, &data,
2728 &udata, yield0);
2729
2730 /* Pray we got both sides of the root. */
2731 status = goal_seek_bisection (&gnumeric_oddyield_f, &data,
2732 &udata);
2733 }
2734
2735 if (status != GOAL_SEEK_OK)
2736 return value_new_error_NUM (ei->pos);
2737
2738 return value_new_float (data.root);
2739 }
2740
2741 /***************************************************************************/
2742
2743 static GnmFuncHelp const help_oddlprice[] = {
2744 { GNM_FUNC_HELP_NAME, F_("ODDLPRICE:price of a security that has an odd last period")},
2745 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
2746 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
2747 { GNM_FUNC_HELP_ARG, F_("last_interest:last interest date")},
2748 { GNM_FUNC_HELP_ARG, F_("rate:nominal annual interest rate")},
2749 { GNM_FUNC_HELP_ARG, F_("yield:annual yield of security")},
2750 { GNM_FUNC_HELP_ARG, F_("redemption:amount received at maturity")},
2751 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
2752 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
2753 { GNM_FUNC_HELP_DESCRIPTION, F_("ODDLPRICE calculates the price per $100 face value of a security that pays periodic interest, but has an odd last period.") },
2754 FREQ_HELP,
2755 GNM_DATE_BASIS_HELP
2756 { GNM_FUNC_HELP_SEEALSO, "YIELD,DURATION"},
2757 { GNM_FUNC_HELP_END }
2758 };
2759
2760 static gnm_float
2761 calc_oddlprice (const GDate *settlement, const GDate *maturity,
2762 const GDate *last_interest,
2763 gnm_float rate, gnm_float yield, gnm_float redemption,
2764 GoCouponConvention *conv)
2765 {
2766 GDate d = *last_interest;
2767 gnm_float x1, x2, x3;
2768
2769 do {
2770 gnm_date_add_months (&d, 12 / conv->freq);
2771 } while (g_date_valid (&d) && g_date_compare (&d, maturity) < 0);
2772
2773 x1 = date_ratio (last_interest, settlement, &d, conv);
2774 x2 = date_ratio (last_interest, maturity, &d, conv);
2775 x3 = date_ratio (settlement, maturity, &d, conv);
2776
2777 return (redemption * conv->freq +
2778 100 * rate * (x2 - x1 * (1 + yield * x3 / conv->freq))) /
2779 (yield * x3 + conv->freq);
2780 }
2781
2782
2783 static GnmValue *
2784 gnumeric_oddlprice (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2785 {
2786 GDate settlement, maturity, last_interest;
2787 gnm_float rate, yield, redemption;
2788 GoCouponConvention conv;
2789
2790 rate = value_get_as_float (argv[3]);
2791 yield = value_get_as_float (argv[4]);
2792 redemption = value_get_as_float (argv[5]);
2793
2794 conv.eom = TRUE;
2795 conv.freq = value_get_freq (argv[6]);
2796 conv.basis = value_get_basis (argv[7], GO_BASIS_MSRB_30_360);
2797 conv.date_conv = sheet_date_conv (ei->pos->sheet);
2798
2799 if (!datetime_value_to_g (&settlement, argv[0], conv.date_conv) ||
2800 !datetime_value_to_g (&maturity, argv[1], conv.date_conv) ||
2801 !datetime_value_to_g (&last_interest, argv[2], conv.date_conv))
2802 return value_new_error_VALUE (ei->pos);
2803
2804 if (!is_valid_basis (conv.basis) ||
2805 !is_valid_freq (conv.freq) ||
2806 g_date_compare (&settlement, &maturity) > 0 ||
2807 g_date_compare (&last_interest, &settlement) > 0)
2808 return value_new_error_NUM (ei->pos);
2809
2810 if (rate < 0.0 || yield < 0.0 || redemption <= 0.0)
2811 return value_new_error_NUM (ei->pos);
2812
2813 return value_new_float
2814 (calc_oddlprice
2815 (&settlement, &maturity, &last_interest,
2816 rate, yield, redemption, &conv));
2817 }
2818
2819 /***************************************************************************/
2820
2821 static GnmFuncHelp const help_oddlyield[] = {
2822 { GNM_FUNC_HELP_NAME, F_("ODDLYIELD:yield of a security that has an odd last period")},
2823 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
2824 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
2825 { GNM_FUNC_HELP_ARG, F_("last_interest:last interest date")},
2826 { GNM_FUNC_HELP_ARG, F_("rate:nominal annual interest rate")},
2827 { GNM_FUNC_HELP_ARG, F_("price:price of security")},
2828 { GNM_FUNC_HELP_ARG, F_("redemption:amount received at maturity")},
2829 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
2830 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
2831 { GNM_FUNC_HELP_DESCRIPTION, F_("ODDLYIELD calculates the yield of a security that pays periodic interest, but has an odd last period.") },
2832 FREQ_HELP,
2833 GNM_DATE_BASIS_HELP
2834 { GNM_FUNC_HELP_SEEALSO, "YIELD,DURATION"},
2835 { GNM_FUNC_HELP_END }
2836 };
2837
2838
2839 static gnm_float
2840 calc_oddlyield (GDate const *settlement, GDate const *maturity,
2841 GDate const *last_interest,
2842 gnm_float rate, gnm_float price, gnm_float redemption,
2843 GoCouponConvention const *conv)
2844 {
2845 GDate d = *last_interest;
2846 gnm_float x1, x2, x3;
2847
2848 do {
2849 gnm_date_add_months (&d, 12 / conv->freq);
2850 } while (g_date_valid (&d) && g_date_compare (&d, maturity) < 0);
2851
2852 x1 = date_ratio (last_interest, settlement, &d, conv);
2853 x2 = date_ratio (last_interest, maturity, &d, conv);
2854 x3 = date_ratio (settlement, maturity, &d, conv);
2855
2856 return (conv->freq * (redemption - price) + 100 * rate * (x2 - x1)) /
2857 (x3 * price + 100 * rate * x1 * x3 / conv->freq);
2858 }
2859
2860
2861 static GnmValue *
2862 gnumeric_oddlyield (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2863 {
2864 GDate settlement, maturity, last_interest;
2865 gnm_float rate, price, redemption;
2866 GoCouponConvention conv;
2867
2868 rate = value_get_as_float (argv[3]);
2869 price = value_get_as_float (argv[4]);
2870 redemption = value_get_as_float (argv[5]);
2871
2872 conv.eom = TRUE;
2873 conv.freq = value_get_freq (argv[6]);
2874 conv.basis = value_get_basis (argv[7], GO_BASIS_MSRB_30_360);
2875 conv.date_conv = sheet_date_conv (ei->pos->sheet);
2876
2877 if (!datetime_value_to_g (&settlement, argv[0], conv.date_conv) ||
2878 !datetime_value_to_g (&maturity, argv[1], conv.date_conv) ||
2879 !datetime_value_to_g (&last_interest, argv[2], conv.date_conv))
2880 return value_new_error_VALUE (ei->pos);
2881
2882 if (!is_valid_basis (conv.basis) ||
2883 !is_valid_freq (conv.freq) ||
2884 g_date_compare (&settlement, &maturity) > 0 ||
2885 g_date_compare (&last_interest, &settlement) > 0)
2886 return value_new_error_NUM (ei->pos);
2887
2888 if (rate < 0.0 || price <= 0.0 || redemption <= 0.0)
2889 return value_new_error_NUM (ei->pos);
2890
2891 return value_new_float
2892 (calc_oddlyield
2893 (&settlement, &maturity, &last_interest,
2894 rate, price, redemption, &conv));
2895 }
2896
2897 /***************************************************************************/
2898
2899 static GnmFuncHelp const help_amordegrc[] = {
2900 { GNM_FUNC_HELP_NAME, F_("AMORDEGRC:depreciation of an asset using French accounting conventions")},
2901 { GNM_FUNC_HELP_ARG, F_("cost:initial cost of asset")},
2902 { GNM_FUNC_HELP_ARG, F_("purchase_date:date of purchase")},
2903 { GNM_FUNC_HELP_ARG, F_("first_period:end of first period")},
2904 { GNM_FUNC_HELP_ARG, F_("salvage:value after depreciation")},
2905 { GNM_FUNC_HELP_ARG, F_("period:subject period")},
2906 { GNM_FUNC_HELP_ARG, F_("rate:depreciation rate")},
2907 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
2908 { GNM_FUNC_HELP_DESCRIPTION, F_(
2909 "AMORDEGRC calculates the depreciation of an asset using French accounting conventions. "
2910 "Assets purchased in the middle of a period take prorated depreciation into account. "
2911 "This is similar to AMORLINC, except that a depreciation coefficient is applied in the "
2912 "calculation depending on the life of the assets.") },
2913 { GNM_FUNC_HELP_DESCRIPTION, F_("The depreciation coefficient used is:\n"
2914 "1.0 for an expected lifetime less than 3 years,\n"
2915 "1.5 for an expected lifetime of at least 3 years but less than 5 years,\n"
2916 "2.0 for an expected lifetime of at least 5 years but at most 6 years,\n"
2917 "2.5 for an expected lifetime of more than 6 years.") },
2918 { GNM_FUNC_HELP_NOTE, F_("Special depreciation rules are applied for the last two periods resulting in a possible total "
2919 "depreciation exceeding the difference of @{cost} - @{salvage}.") },
2920 { GNM_FUNC_HELP_NOTE, F_("Named for AMORtissement DEGRessif Comptabilite.") },
2921 GNM_DATE_BASIS_HELP
2922 { GNM_FUNC_HELP_EXAMPLES, "=AMORDEGRC(2400,DATE(1998,8,19),DATE(1998,12,30),300,1,0.14,1)" },
2923 { GNM_FUNC_HELP_SEEALSO, "AMORLINC"},
2924 { GNM_FUNC_HELP_END }
2925 };
2926
2927
2928 static GnmValue *
2929 gnumeric_amordegrc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2930 {
2931 GDate nDate, nFirstPer;
2932 gnm_float fRestVal, fRate, fCost;
2933 gint basis, nPer;
2934 GODateConventions const *date_conv =
2935 sheet_date_conv (ei->pos->sheet);
2936
2937 fCost = value_get_as_float (argv[0]);
2938 fRestVal = value_get_as_float (argv[3]);
2939 nPer = value_get_as_int (argv[4]);
2940 fRate = value_get_as_float (argv[5]);
2941 basis = value_get_basis (argv[6], GO_BASIS_MSRB_30_360);
2942
2943 if (!is_valid_basis (basis) ||
2944 fRate < 0 ||
2945 !datetime_value_to_g (&nDate, argv[1], date_conv) ||
2946 !datetime_value_to_g (&nFirstPer, argv[2], date_conv))
2947 return value_new_error_NUM (ei->pos);
2948
2949 return get_amordegrc (fCost, &nDate, &nFirstPer,
2950 fRestVal, nPer, fRate, basis);
2951 }
2952
2953 /***************************************************************************/
2954
2955 static GnmFuncHelp const help_amorlinc[] = {
2956 { GNM_FUNC_HELP_NAME, F_("AMORLINC:depreciation of an asset using French accounting conventions")},
2957 { GNM_FUNC_HELP_ARG, F_("cost:initial cost of asset")},
2958 { GNM_FUNC_HELP_ARG, F_("purchase_date:date of purchase")},
2959 { GNM_FUNC_HELP_ARG, F_("first_period:end of first period")},
2960 { GNM_FUNC_HELP_ARG, F_("salvage:value after depreciation")},
2961 { GNM_FUNC_HELP_ARG, F_("period:subject period")},
2962 { GNM_FUNC_HELP_ARG, F_("rate:depreciation rate")},
2963 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
2964 { GNM_FUNC_HELP_DESCRIPTION, F_(
2965 "AMORLINC calculates the depreciation of an asset using French accounting conventions. "
2966 "Assets purchased in the middle of a period take prorated depreciation into account.") },
2967 { GNM_FUNC_HELP_NOTE, F_("Named for AMORtissement LINeaire Comptabilite.") },
2968 GNM_DATE_BASIS_HELP
2969 { GNM_FUNC_HELP_EXAMPLES, "=AMORLINC(2400,DATE(1998,8,19),DATE(1998,12,30),300,1,0.14,1)" },
2970 { GNM_FUNC_HELP_SEEALSO, "AMORDEGRC"},
2971 { GNM_FUNC_HELP_END }
2972 };
2973
2974 static GnmValue *
2975 gnumeric_amorlinc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2976 {
2977 GDate nDate, nFirstPer;
2978 gnm_float fCost, fRestVal, fRate;
2979 gint nPer, basis;
2980 GODateConventions const *date_conv =
2981 sheet_date_conv (ei->pos->sheet);
2982
2983 fCost = value_get_as_float (argv[0]);
2984 fRestVal = value_get_as_float (argv[3]);
2985 nPer = value_get_as_int (argv[4]);
2986 fRate = value_get_as_float (argv[5]);
2987 basis = value_get_basis (argv[6], GO_BASIS_MSRB_30_360);
2988
2989 if (!is_valid_basis (basis) ||
2990 fRate < 0 ||
2991 !datetime_value_to_g (&nDate, argv[1], date_conv) ||
2992 !datetime_value_to_g (&nFirstPer, argv[2], date_conv))
2993 return value_new_error_NUM (ei->pos);
2994
2995 return get_amorlinc (fCost, &nDate, &nFirstPer,
2996 fRestVal, nPer, fRate, basis);
2997 }
2998
2999 /***************************************************************************/
3000
3001 static GnmFuncHelp const help_coupdaybs[] = {
3002 { GNM_FUNC_HELP_NAME, F_("COUPDAYBS:number of days from coupon period to settlement")},
3003 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
3004 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
3005 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
3006 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
3007 { GNM_FUNC_HELP_ARG, F_("eom:end-of-month flag")},
3008 { GNM_FUNC_HELP_DESCRIPTION, F_("COUPDAYBS calculates the number of days from the beginning of the coupon period to the settlement date.") },
3009 FREQ_HELP,
3010 GNM_DATE_BASIS_HELP
3011 { GNM_FUNC_HELP_EXAMPLES, "=COUPDAYBS(DATE(2002,11,29),DATE(2004,2,29),4,0)" },
3012 { GNM_FUNC_HELP_EXAMPLES, "=COUPDAYBS(DATE(2002,11,29),DATE(2004,2,29),4,0,FALSE)" },
3013 { GNM_FUNC_HELP_SEEALSO, "COUPDAYS"},
3014 { GNM_FUNC_HELP_END }
3015 };
3016
3017 static GnmValue *
3018 gnumeric_coupdaybs (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3019 {
3020 return func_coup (ei, argv, go_coupdaybs);
3021 }
3022
3023 /***************************************************************************/
3024
3025 static GnmFuncHelp const help_coupdays[] = {
3026 { GNM_FUNC_HELP_NAME, F_("COUPDAYS:number of days in the coupon period of the settlement date")},
3027 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
3028 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
3029 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
3030 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
3031 { GNM_FUNC_HELP_ARG, F_("eom:end-of-month flag")},
3032 { GNM_FUNC_HELP_DESCRIPTION, F_("COUPDAYS calculates the number of days in the coupon period of the settlement date.") },
3033 FREQ_HELP,
3034 GNM_DATE_BASIS_HELP
3035 { GNM_FUNC_HELP_EXAMPLES, "=COUPDAYS(DATE(2002,11,29),DATE(2004,2,29),4,0)" },
3036 { GNM_FUNC_HELP_EXAMPLES, "=COUPDAYS(DATE(2002,11,29),DATE(2004,2,29),4,0,FALSE)" },
3037 { GNM_FUNC_HELP_SEEALSO, "COUPDAYBS,COUPDAYSNC"},
3038 { GNM_FUNC_HELP_END }
3039 };
3040
3041 static GnmValue *
3042 gnumeric_coupdays (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3043 {
3044 return func_coup (ei, argv, go_coupdays);
3045 }
3046
3047 /***************************************************************************/
3048
3049 static GnmFuncHelp const help_coupdaysnc[] = {
3050 { GNM_FUNC_HELP_NAME, F_("COUPDAYSNC:number of days from the settlement date to the next coupon period")},
3051 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
3052 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
3053 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
3054 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
3055 { GNM_FUNC_HELP_ARG, F_("eom:end-of-month flag")},
3056 { GNM_FUNC_HELP_DESCRIPTION, F_("COUPDAYSNC calculates number of days from the settlement date to the next coupon period.") },
3057 FREQ_HELP,
3058 GNM_DATE_BASIS_HELP
3059 { GNM_FUNC_HELP_EXAMPLES, "=COUPDAYSNC(DATE(2002,11,29),DATE(2004,2,29),4,0)" },
3060 { GNM_FUNC_HELP_EXAMPLES, "=COUPDAYSNC(DATE(2002,11,29),DATE(2004,2,29),4,0,FALSE)" },
3061 { GNM_FUNC_HELP_SEEALSO, "COUPDAYS,COUPDAYBS"},
3062 { GNM_FUNC_HELP_END }
3063 };
3064
3065 static GnmValue *
3066 gnumeric_coupdaysnc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3067 {
3068 return func_coup (ei, argv, go_coupdaysnc);
3069 }
3070
3071 /***************************************************************************/
3072
3073 static GnmFuncHelp const help_coupncd[] = {
3074 { GNM_FUNC_HELP_NAME, F_("COUPNCD:the next coupon date after settlement")},
3075 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
3076 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
3077 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
3078 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
3079 { GNM_FUNC_HELP_ARG, F_("eom:end-of-month flag")},
3080 { GNM_FUNC_HELP_DESCRIPTION, F_("COUPNCD calculates the coupon date following settlement.") },
3081 FREQ_HELP,
3082 GNM_DATE_BASIS_HELP
3083 { GNM_FUNC_HELP_EXAMPLES, "=COUPNCD(DATE(2002,11,29),DATE(2004,2,29),4,0)" },
3084 { GNM_FUNC_HELP_EXAMPLES, "=COUPNCD(DATE(2002,11,29),DATE(2004,2,29),4,0,FALSE)" },
3085 { GNM_FUNC_HELP_SEEALSO, "COUPPCD,COUPDAYS,COUPDAYBS"},
3086 { GNM_FUNC_HELP_END }
3087 };
3088
3089 static GnmValue *
3090 gnumeric_coupncd (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3091 {
3092 GnmValue *res = func_coup (ei, argv, coupncd);
3093 value_set_fmt (res, go_format_default_date ());
3094 return res;
3095 }
3096
3097 /***************************************************************************/
3098
3099 static GnmFuncHelp const help_couppcd[] = {
3100 { GNM_FUNC_HELP_NAME, F_("COUPPCD:the last coupon date before settlement")},
3101 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
3102 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
3103 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
3104 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
3105 { GNM_FUNC_HELP_ARG, F_("eom:end-of-month flag")},
3106 { GNM_FUNC_HELP_DESCRIPTION, F_("COUPPCD calculates the coupon date preceding settlement.") },
3107 FREQ_HELP,
3108 GNM_DATE_BASIS_HELP
3109 { GNM_FUNC_HELP_EXAMPLES, "=COUPPCD(DATE(2002,11,29),DATE(2004,2,29),4,0)" },
3110 { GNM_FUNC_HELP_EXAMPLES, "=COUPPCD(DATE(2002,11,29),DATE(2004,2,29),4,0,FALSE)" },
3111 { GNM_FUNC_HELP_SEEALSO, "COUPNCD,COUPDAYS,COUPDAYBS"},
3112 { GNM_FUNC_HELP_END }
3113 };
3114
3115 static GnmValue *
3116 gnumeric_couppcd (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3117 {
3118 GnmValue *res = func_coup (ei, argv, couppcd);
3119 value_set_fmt (res, go_format_default_date ());
3120 return res;
3121 }
3122
3123 /***************************************************************************/
3124
3125 static GnmFuncHelp const help_coupnum[] = {
3126 { GNM_FUNC_HELP_NAME, F_("COUPNUM:number of coupons")},
3127 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
3128 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
3129 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
3130 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
3131 { GNM_FUNC_HELP_ARG, F_("eom:end-of-month flag")},
3132 { GNM_FUNC_HELP_DESCRIPTION, F_("COUPNUM calculates the number of coupons to be paid between the settlement and maturity dates, rounded up.") },
3133 FREQ_HELP,
3134 GNM_DATE_BASIS_HELP
3135 { GNM_FUNC_HELP_EXAMPLES, "=COUPNUM(DATE(2002,11,29),DATE(2004,2,29),4,0)" },
3136 { GNM_FUNC_HELP_EXAMPLES, "=COUPNUM(DATE(2002,11,29),DATE(2004,2,29),4,0,FALSE)" },
3137 { GNM_FUNC_HELP_SEEALSO, "COUPNCD,COUPPCD"},
3138 { GNM_FUNC_HELP_END }
3139 };
3140
3141 static GnmValue *
3142 gnumeric_coupnum (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3143 {
3144 return func_coup (ei, argv, coupnum);
3145 }
3146
3147 /***************************************************************************/
3148
3149 static GnmFuncHelp const help_cumipmt[] = {
3150 { GNM_FUNC_HELP_NAME, F_("CUMIPMT:cumulative interest payment")},
3151 { GNM_FUNC_HELP_ARG, F_("rate:interest rate per period")},
3152 { GNM_FUNC_HELP_ARG, F_("nper:number of periods")},
3153 { GNM_FUNC_HELP_ARG, F_("pv:present value")},
3154 { GNM_FUNC_HELP_ARG, F_("start_period:first period to accumulate for")},
3155 { GNM_FUNC_HELP_ARG, F_("end_period:last period to accumulate for")},
3156 { GNM_FUNC_HELP_ARG, F_("type:payment type")},
3157 { GNM_FUNC_HELP_DESCRIPTION, F_("CUMIPMT calculates the cumulative interest paid on a loan from @{start_period} to @{end_period}.") },
3158 TYPE_HELP,
3159 { GNM_FUNC_HELP_SEEALSO, "IPMT"},
3160 { GNM_FUNC_HELP_END }
3161 };
3162
3163 static GnmValue *
3164 gnumeric_cumipmt (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3165 {
3166 gnm_float fRate, fVal;
3167 gint nNumPeriods, nStartPer, nEndPer, nPayType;
3168 GnmValue *result;
3169
3170 fRate = value_get_as_float (argv[0]);
3171 nNumPeriods = value_get_as_int (argv[1]);
3172 fVal = value_get_as_float (argv[2]);
3173 nStartPer = value_get_as_int (argv[3]);
3174 nEndPer = value_get_as_int (argv[4]);
3175 nPayType = value_get_paytype (argv[5]);
3176
3177 if ( nStartPer < 1 || nEndPer < nStartPer || fRate <= 0
3178 || nEndPer > nNumPeriods || nNumPeriods <= 0
3179 || fVal <= 0 || !is_valid_paytype (nPayType) ) {
3180 result = value_new_error_NUM (ei->pos);
3181 goto out;
3182 }
3183
3184 result = get_cumipmt (fRate, nNumPeriods, fVal, nStartPer, nEndPer,
3185 nPayType);
3186
3187 out:
3188 return result;
3189 }
3190
3191 /***************************************************************************/
3192
3193 static GnmFuncHelp const help_cumprinc[] = {
3194 { GNM_FUNC_HELP_NAME, F_("CUMPRINC:cumulative principal")},
3195 { GNM_FUNC_HELP_ARG, F_("rate:interest rate per period")},
3196 { GNM_FUNC_HELP_ARG, F_("nper:number of periods")},
3197 { GNM_FUNC_HELP_ARG, F_("pv:present value")},
3198 { GNM_FUNC_HELP_ARG, F_("start_period:first period to accumulate for")},
3199 { GNM_FUNC_HELP_ARG, F_("end_period:last period to accumulate for")},
3200 { GNM_FUNC_HELP_ARG, F_("type:payment type")},
3201 { GNM_FUNC_HELP_DESCRIPTION, F_("CUMPRINC calculates the cumulative principal paid on a loan from @{start_period} to @{end_period}.") },
3202 TYPE_HELP,
3203 { GNM_FUNC_HELP_SEEALSO, "PPMT"},
3204 { GNM_FUNC_HELP_END }
3205 };
3206
3207 static GnmValue *
3208 gnumeric_cumprinc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3209 {
3210 gnm_float fRate, fVal;
3211 gint nNumPeriods, nStartPer, nEndPer, nPayType;
3212 GnmValue *result;
3213
3214 fRate = value_get_as_float (argv[0]);
3215 nNumPeriods = value_get_as_int (argv[1]);
3216 fVal = value_get_as_float (argv[2]);
3217 nStartPer = value_get_as_int (argv[3]);
3218 nEndPer = value_get_as_int (argv[4]);
3219 nPayType = value_get_paytype (argv[5]);
3220
3221 if ( nStartPer < 1 || nEndPer < nStartPer || fRate <= 0
3222 || nEndPer > nNumPeriods || nNumPeriods <= 0
3223 || fVal <= 0 || !is_valid_paytype (nPayType)) {
3224 result = value_new_error_NUM (ei->pos);
3225 goto out;
3226 }
3227
3228 result = get_cumprinc (fRate, nNumPeriods, fVal, nStartPer, nEndPer,
3229 nPayType);
3230
3231 out:
3232 return result;
3233 }
3234
3235 /***************************************************************************/
3236
3237 static GnmFuncHelp const help_mduration[] = {
3238 { GNM_FUNC_HELP_NAME, F_("MDURATION:the modified (Macaulay) duration of a security")},
3239 { GNM_FUNC_HELP_ARG, F_("settlement:settlement date")},
3240 { GNM_FUNC_HELP_ARG, F_("maturity:maturity date")},
3241 { GNM_FUNC_HELP_ARG, F_("coupon:annual coupon rate")},
3242 { GNM_FUNC_HELP_ARG, F_("yield:annual yield of security")},
3243 { GNM_FUNC_HELP_ARG, F_("frequency:number of interest payments per year")},
3244 { GNM_FUNC_HELP_ARG, F_("basis:calendar basis")},
3245 { GNM_FUNC_HELP_DESCRIPTION, F_("MDURATION calculates the modified (Macaulay) duration of a security.") },
3246 FREQ_HELP,
3247 GNM_DATE_BASIS_HELP
3248 { GNM_FUNC_HELP_EXAMPLES, "=MDURATION(TODAY(),TODAY()+365,0.05,0.08,4)"},
3249 { GNM_FUNC_HELP_EXAMPLES, "=MDURATION(TODAY(),TODAY()+366,0.05,0.08,4)"},
3250 { GNM_FUNC_HELP_SEEALSO, "DURATION,G_DURATION"},
3251 { GNM_FUNC_HELP_END }
3252 };
3253
3254 static GnmValue *
3255 gnumeric_mduration (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3256 {
3257 GDate nSettle, nMat;
3258 gnm_float fCoup, fYield;
3259 gnm_float fNumOfCoups;
3260 GoCouponConvention conv;
3261
3262 conv.date_conv = sheet_date_conv (ei->pos->sheet);
3263 conv.eom = TRUE;
3264
3265 fCoup = value_get_as_float (argv[2]);
3266 fYield = value_get_as_float (argv[3]);
3267 conv.freq = value_get_freq (argv[4]);
3268 conv.basis = value_get_basis (argv[5], GO_BASIS_MSRB_30_360);
3269 conv.eom = FALSE;
3270
3271 if (!is_valid_basis (conv.basis) ||
3272 !is_valid_freq (conv.freq) ||
3273 !datetime_value_to_g (&nSettle, argv[0], conv.date_conv) ||
3274 !datetime_value_to_g (&nMat, argv[1], conv.date_conv))
3275 return value_new_error_NUM (ei->pos);
3276
3277 fNumOfCoups = coupnum (&nSettle, &nMat, &conv);
3278 return get_mduration (&nSettle, &nMat, fCoup, fYield, conv.freq,
3279 conv.basis, fNumOfCoups);
3280 }
3281
3282 /***************************************************************************/
3283
3284 static GnmFuncHelp const help_vdb[] = {
3285 { GNM_FUNC_HELP_NAME, F_("VDB:depreciation of an asset")},
3286 { GNM_FUNC_HELP_ARG, F_("cost:initial cost of asset")},
3287 { GNM_FUNC_HELP_ARG, F_("salvage:value after depreciation")},
3288 { GNM_FUNC_HELP_ARG, F_("life:number of periods")},
3289 { GNM_FUNC_HELP_ARG, F_("start_period:first period to accumulate for")},
3290 { GNM_FUNC_HELP_ARG, F_("end_period:last period to accumulate for")},
3291 { GNM_FUNC_HELP_ARG, F_("factor:factor at which the balance declines")},
3292 { GNM_FUNC_HELP_ARG, F_("no_switch:do not switch to straight-line depreciation")},
3293 { GNM_FUNC_HELP_DESCRIPTION, F_("VDB calculates the depreciation of an asset for a given period range using the variable-rate declining balance method.") },
3294 { GNM_FUNC_HELP_NOTE, F_("If @{no_switch} is FALSE, the calculation switches to straight-line depreciation when depreciation is greater than the declining balance calculation.") },
3295 { GNM_FUNC_HELP_SEEALSO, "DB,DDB"},
3296 { GNM_FUNC_HELP_END }
3297 };
3298
3299 static GnmValue *
3300 gnumeric_vdb (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3301 {
3302 gnm_float cost, salvage, life, factor, start_period, end_period;
3303 gboolean bflag;
3304
3305 cost = value_get_as_float (argv[0]);
3306 salvage = value_get_as_float (argv[1]);
3307 life = value_get_as_float (argv[2]);
3308 start_period = value_get_as_float (argv[3]);
3309 end_period = value_get_as_float (argv[4]);
3310 factor = argv[5] ? value_get_as_float (argv[5]) : 2;
3311 bflag = argv[6] ? value_get_as_int (argv[6]) : 0;
3312
3313 if ( start_period < 0 || end_period < start_period
3314 || end_period > life || cost < 0 || salvage > cost
3315 || factor <= 0)
3316 return value_new_error_NUM (ei->pos);
3317
3318 return get_vdb (cost, salvage, life, start_period, end_period, factor,
3319 bflag);
3320 }
3321
3322 /***************************************************************************/
3323
3324 GnmFuncDescriptor const financial_functions[] = {
3325 { "accrint", "ffff|fffb",
3326 help_accrint, gnumeric_accrint, NULL, NULL, NULL,
3327 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3328 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3329 { "accrintm", "fff|ff",
3330 help_accrintm, gnumeric_accrintm, NULL, NULL, NULL,
3331 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3332 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3333 { "amordegrc", "fffffff",
3334 help_amordegrc, gnumeric_amordegrc, NULL, NULL, NULL,
3335 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3336 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3337 { "amorlinc", "fffffff",
3338 help_amorlinc, gnumeric_amorlinc, NULL, NULL, NULL,
3339 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3340 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3341 { "coupdaybs", "fff|fb",
3342 help_coupdaybs, gnumeric_coupdaybs, NULL, NULL, NULL,
3343 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3344 { "coupdays", "fff|fb",
3345 help_coupdays, gnumeric_coupdays, NULL, NULL, NULL,
3346 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3347 { "coupdaysnc", "fff|fb",
3348 help_coupdaysnc, gnumeric_coupdaysnc, NULL, NULL, NULL,
3349 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3350 { "coupncd", "fff|fb",
3351 help_coupncd, gnumeric_coupncd, NULL, NULL, NULL,
3352 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_DATE,
3353 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3354 { "coupnum", "fff|fb",
3355 help_coupnum, gnumeric_coupnum, NULL, NULL, NULL,
3356 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3357 { "couppcd", "fff|fb",
3358 help_couppcd, gnumeric_couppcd, NULL, NULL, NULL,
3359 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_DATE,
3360 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3361 { "cumipmt", "ffffff",
3362 help_cumipmt, gnumeric_cumipmt, NULL, NULL, NULL,
3363 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3364 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3365 { "cumprinc", "ffffff",
3366 help_cumprinc, gnumeric_cumprinc, NULL, NULL, NULL,
3367 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3368 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3369 { "db", "ffff|f",
3370 help_db, gnumeric_db, NULL, NULL, NULL,
3371 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3372 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3373 { "ddb", "ffff|f",
3374 help_ddb, gnumeric_ddb, NULL, NULL, NULL,
3375 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3376 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3377 { "disc", "ffff|f",
3378 help_disc, gnumeric_disc, NULL, NULL, NULL,
3379 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3380 { "dollarde", "ff",
3381 help_dollarde, gnumeric_dollarde, NULL, NULL, NULL,
3382 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3383 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3384 { "dollarfr", "ff",
3385 help_dollarfr, gnumeric_dollarfr, NULL, NULL, NULL,
3386 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3387 { "duration", "fffff|f",
3388 help_duration, gnumeric_duration, NULL, NULL, NULL,
3389 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3390 { "effect", "ff",
3391 help_effect, gnumeric_effect, NULL, NULL, NULL,
3392 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_PERCENT,
3393 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3394 { "euro", "s",
3395 help_euro, gnumeric_euro, NULL, NULL, NULL,
3396 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_BASIC },
3397 { "euroconvert", "fss|bf",
3398 help_euroconvert, gnumeric_euroconvert, NULL, NULL, NULL,
3399 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3400 { "fv", "fff|ff",
3401 help_fv, gnumeric_fv, NULL, NULL, NULL,
3402 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3403 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3404 { "fvschedule", "fA",
3405 help_fvschedule, gnumeric_fvschedule, NULL, NULL, NULL,
3406 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3407 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3408 { "g_duration", "fff",
3409 help_g_duration, gnumeric_g_duration, NULL, NULL, NULL,
3410 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_UNIQUE_TO_GNUMERIC, GNM_FUNC_TEST_STATUS_BASIC },
3411 { "intrate", "ffff|f",
3412 help_intrate, gnumeric_intrate, NULL, NULL, NULL,
3413 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_PERCENT,
3414 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3415 { "ipmt", "ffff|ff",
3416 help_ipmt, gnumeric_ipmt, NULL, NULL, NULL,
3417 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3418 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3419 { "irr", "A|f",
3420 help_irr, gnumeric_irr, NULL, NULL, NULL,
3421 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_PERCENT,
3422 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3423 { "ispmt", "ffff",
3424 help_ispmt, gnumeric_ispmt, NULL, NULL, NULL,
3425 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3426 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3427 { "mduration", "fffff|f",
3428 help_mduration, gnumeric_mduration, NULL, NULL, NULL,
3429 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3430 { "mirr", "Aff",
3431 help_mirr, gnumeric_mirr, NULL, NULL, NULL,
3432 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_PERCENT,
3433 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3434 { "nominal", "ff",
3435 help_nominal, gnumeric_nominal, NULL, NULL, NULL,
3436 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_PERCENT,
3437 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3438 { "nper", "fff|ff",
3439 help_nper, gnumeric_nper, NULL, NULL, NULL,
3440 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3441 { "npv", NULL,
3442 help_npv, NULL, gnumeric_npv, NULL, NULL,
3443 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3444 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3445 { "oddfprice", "fffffffff",
3446 help_oddfprice, gnumeric_oddfprice, NULL, NULL, NULL,
3447 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3448
3449 { "oddfyield", "fffffffff",
3450 help_oddfyield, gnumeric_oddfyield, NULL, NULL, NULL,
3451 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3452
3453 { "oddlprice", "ffffffff",
3454 help_oddlprice, gnumeric_oddlprice, NULL, NULL, NULL,
3455 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3456 { "oddlyield", "ffffffff",
3457 help_oddlyield, gnumeric_oddlyield, NULL, NULL, NULL,
3458 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3459 { "pmt", "fff|ff",
3460 help_pmt, gnumeric_pmt, NULL, NULL, NULL,
3461 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3462 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3463 { "ppmt", "ffff|ff",
3464 help_ppmt, gnumeric_ppmt, NULL, NULL, NULL,
3465 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3466 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3467 { "price", "ffffff|f",
3468 help_price, gnumeric_price, NULL, NULL, NULL,
3469 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3470 { "pricedisc", "ffff|f",
3471 help_pricedisc, gnumeric_pricedisc, NULL, NULL, NULL,
3472 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3473 { "pricemat", "fffff|f",
3474 help_pricemat, gnumeric_pricemat, NULL, NULL, NULL,
3475 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3476 { "pv", "fff|ff",
3477 help_pv, gnumeric_pv, NULL, NULL, NULL,
3478 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3479 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3480 { "rate", "fff|fff",
3481 help_rate, gnumeric_rate, NULL, NULL, NULL,
3482 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_PERCENT,
3483 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3484 { "rri", "fff",
3485 help_rri, gnumeric_rri, NULL, NULL, NULL,
3486 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_PERCENT,
3487 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_NO_TESTSUITE },
3488 { "received", "ffff|f",
3489 help_received, gnumeric_received, NULL, NULL, NULL,
3490 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3491 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3492 { "sln", "fff",
3493 help_sln, gnumeric_sln, NULL, NULL, NULL,
3494 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3495 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3496 { "syd", "ffff",
3497 help_syd, gnumeric_syd, NULL, NULL, NULL,
3498 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3499 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3500 { "tbilleq", "fff",
3501 help_tbilleq, gnumeric_tbilleq, NULL, NULL, NULL,
3502 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3503 { "tbillprice", "fff",
3504 help_tbillprice, gnumeric_tbillprice, NULL, NULL, NULL,
3505 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3506 { "tbillyield", "fff",
3507 help_tbillyield, gnumeric_tbillyield, NULL, NULL, NULL,
3508 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_PERCENT,
3509 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3510 { "vdb", "fffff|ff",
3511 help_vdb, gnumeric_vdb, NULL, NULL, NULL,
3512 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3513 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3514 { "xirr", "AA|f",
3515 help_xirr, gnumeric_xirr, NULL, NULL, NULL,
3516 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_PERCENT,
3517 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3518 { "xnpv", "fAA",
3519 help_xnpv, gnumeric_xnpv, NULL, NULL, NULL,
3520 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_MONETARY,
3521 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3522 { "yield", "ffffff|f",
3523 help_yield, gnumeric_yield, NULL, NULL, NULL,
3524 GNM_FUNC_SIMPLE + GNM_FUNC_AUTO_PERCENT,
3525 GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3526 { "yielddisc", "ffff|f",
3527 help_yielddisc, gnumeric_yielddisc, NULL, NULL, NULL,
3528 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3529 { "yieldmat", "fffff|f",
3530 help_yieldmat, gnumeric_yieldmat, NULL, NULL, NULL,
3531 GNM_FUNC_SIMPLE, GNM_FUNC_IMPL_STATUS_COMPLETE, GNM_FUNC_TEST_STATUS_BASIC },
3532 {NULL}
3533 };
gnumeric mirror