functions.c 72.8 KB
Newer Older
1 2 3
/*
 * fn-financial.c:  Built in financial functions and functions registration
 *
4
 * Authors:
5
 *  Vladimir Vuksan (vuksan@veus.hr)
6
 *  Jukka-Pekka Iivonen (iivonen@iki.fi)
7
 *
8

9 10
 */
#include <config.h>
11
#include <math.h>
12
#include "gnumeric.h"
13
#include "parse-util.h"
14
#include "cell.h"
15
#include "func.h"
Morten Welinder's avatar
Morten Welinder committed
16
#include "goal-seek.h"
17
#include "collect.h"
Morten Welinder's avatar
Morten Welinder committed
18
#include "auto-format.h"
Morten Welinder's avatar
Morten Welinder committed
19
#include "datetime.h"
20

21 22 23 24 25
/*

Below are some of the functions that are used quite often in
financial analysis.

26
Present value interest factor
27

Morten Welinder's avatar
Morten Welinder committed
28
	 PVIF = (1 + k) ^ n
29

30
Future value interest factor
31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

         FVIF = 1 / PVIF

Present value interest factor of annuities

                  1          1
	 PVIFA = --- - -----------
                  k     k*(1+k)^n

Future value interest factor of annuities

                  (1+k)^n - 1
         FVIFA = ----------------
	                k



48 49
	 PV * PVIF(k%, nper) + PMT * ( 1 + rate * type ) *
	      FVIFA(k%, nper) + FV = 0
50 51 52

 */

53 54
static float_t
calculate_pvif (float_t rate, float_t nper)
55
{
Morten Welinder's avatar
Morten Welinder committed
56
	return (pow (1 + rate, nper));
57 58
}

Morten Welinder's avatar
Morten Welinder committed
59
#if 0
60 61
static float_t
calculate_fvif (float_t rate, float_t nper)
62
{
63
	return (1.0 / calculate_pvif (rate,nper));
64 65
}

66 67
static float_t
calculate_pvifa (float_t rate, float_t nper)
68
{
Morten Welinder's avatar
Morten Welinder committed
69
	return ((1.0 / rate) - (1.0 / (rate * pow (1 + rate, nper))));
70
}
Morten Welinder's avatar
Morten Welinder committed
71
#endif
72

73 74
static float_t
calculate_fvifa (float_t rate, float_t nper)
75
{
Morten Welinder's avatar
Morten Welinder committed
76
	return ((pow (1 + rate, nper) - 1) / rate);
77 78
}

79 80 81 82 83 84 85 86
/*

Principal for period x is calculated using this formula

PR(x) = PR(0) * ( 1 + rate ) ^ x + PMT * ( ( 1 + rate ) ^ x - 1 ) / rate )

*/

87
static float_t
88 89
calculate_principal (float_t starting_principal, float_t payment,
		     float_t rate, float_t period)
90
{
91
	return (starting_principal * pow (1.0 + rate, period) + payment *
Morten Welinder's avatar
Morten Welinder committed
92
		((pow (1 + rate, period) - 1) / rate));
93 94
}

95 96
static float_t
calculate_pmt (float_t rate, float_t nper, float_t pv, float_t fv, int type)
97
{
98
	float_t pvif, fvifa;
99 100 101

	/* Calculate the PVIF and FVIFA */

102 103
	pvif = calculate_pvif (rate,nper);
	fvifa = calculate_fvifa (rate,nper);
104

Morten Welinder's avatar
Morten Welinder committed
105
        return ((-pv * pvif - fv ) / ((1.0 + rate * type) * fvifa));
106 107
}

108
static int
Morten Welinder's avatar
Morten Welinder committed
109
annual_year_basis (Value *value_date, int basis)
110 111 112 113 114 115 116 117
{
        GDate    *date;
        gboolean leap_year;

	switch (basis) {
	case 0:
	        return 360;
	case 1:
Morten Welinder's avatar
Morten Welinder committed
118
	        date = datetime_value_to_g (value_date);
119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136
		if (date != NULL) {
		        leap_year = g_date_is_leap_year (g_date_year (date));
			g_date_free (date);
		} else
		        return -1;
	        return leap_year ? 366 : 365;
	case 2:
	        return 360;
	case 3:
	        return 365;
	case 4:
	        return 360;
	default:
	        return -1;
	}
}

static int
Morten Welinder's avatar
Morten Welinder committed
137
days_monthly_basis (Value *issue_date, Value *maturity_date, int basis)
138
{
139 140 141 142 143 144
        GDate    *date_i, *date_m;
	int      issue_day, issue_month, issue_year;
	int      maturity_day, maturity_month, maturity_year;
        int      months, days, years;
	gboolean leap_year;
	int      maturity, issue;
145

Morten Welinder's avatar
Morten Welinder committed
146 147
	date_i = datetime_value_to_g (issue_date);
	date_m = datetime_value_to_g (maturity_date);
148
	if (date_i != NULL && date_m != NULL) {
149 150 151 152 153 154 155 156 157 158 159
	        issue_year = g_date_year (date_i);
	        issue_month = g_date_month (date_i);
	        issue_day = g_date_day (date_i);
	        maturity_year = g_date_year (date_m);
	        maturity_month = g_date_month (date_m);
	        maturity_day = g_date_day (date_m);

	        years = maturity_year - issue_year;
	        months = maturity_month - issue_month;
	        days = maturity_day - issue_day;

160
		months = years * 12 + months;
161 162
		leap_year = g_date_is_leap_year (issue_year);

163 164 165 166 167 168 169 170 171 172
		g_date_free (date_i);
		g_date_free (date_m);
	} else {
	        g_date_free (date_i);
	        g_date_free (date_m);
	        return -1;
	}

	switch (basis) {
	case 0:
173
	        if (issue_month == 2 && maturity_month != 2 &&
Morten Welinder's avatar
Morten Welinder committed
174
		    issue_year == maturity_year) {
175 176 177 178 179
			if (leap_year)
				return months * 30 + days - 1;
			else
				return months * 30 + days - 2;
		}
180 181 182 183
	        return months * 30 + days;
	case 1:
	case 2:
	case 3:
Morten Welinder's avatar
Morten Welinder committed
184 185
	        issue = datetime_value_to_serial (issue_date);
	        maturity = datetime_value_to_serial (maturity_date);
186 187 188 189 190 191 192 193
	        return maturity - issue;
	case 4:
	        return months * 30 + days;
	default:
	        return -1;
	}
}

194
/***************************************************************************/
195

196 197 198 199 200
static char *help_accrint = {
	N_("@FUNCTION=ACCRINT\n"
	   "@SYNTAX=ACCRINT(issue,first_interest,settlement,rate,par,"
	   "frequency[,basis])\n"
	   "@DESCRIPTION="
201 202 203 204
	   "ACCRINT calculates the accrued interest for a "
	   "security that pays periodic interest.  The rate is the annual "
	   "rate of the security and par is the par value of the security. "
	   "basis is the type of day counting system you want to use:\n"
205 206 207 208 209 210 211 212 213
	   "\n"
	   "0  US 30/360\n"
	   "1  actual days/actual days\n"
	   "2  actual days/360\n"
	   "3  actual days/365\n"
	   "4  European 30/360\n"
	   "\n"
	   "If @basis is omitted, US 30/360 is applied. "
	   "If issue date or settlement date is not valid, ACCRINT returns "
214 215
	   "NUM! error. "
	   "If @rate or @par is zero or negative, ACCRINT returns NUM! error. "
216 217 218 219
	   "If @basis < 0 or @basis > 4, ACCRINT returns NUM! error. "
	   "If issue date is after maturity date or they are the same, "
	   "ACCRINT returns NUM! error. "
	   "\n"
220 221
	   "@EXAMPLES=\n"
	   "\n"
222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241
	   "@SEEALSO=ACCRINTM")
};

static Value *
gnumeric_accrint (FunctionEvalInfo *ei, Value **argv)
{
	float_t rate, a, d, par, freq, coefficient, x;
	int     basis;

	rate = value_get_as_float (argv[3]);
	par = value_get_as_float (argv[4]);
	freq = value_get_as_float (argv[5]);
	if (argv[6] == NULL)
	        basis = 0;
	else
	        basis = value_get_as_int (argv[6]);

	a = days_monthly_basis (argv[0], argv[2], basis);
	d = annual_year_basis (argv[0], basis);

242 243
	if (a < 0 || d <= 0 || par <= 0 || rate <= 0 || basis < 0 || basis > 4
	    || freq == 0)
244
                return value_new_error (ei->pos, gnumeric_err_NUM);
245 246 247 248 249 250 251

	coefficient = par * rate / freq;
	x = a / d;

	return value_new_float (coefficient * freq * x);
}

252 253
/***************************************************************************/

254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271
static char *help_accrintm = {
	N_("@FUNCTION=ACCRINTM\n"
	   "@SYNTAX=ACCRINTM(issue,maturity,rate[,par,basis])\n"
	   "@DESCRIPTION="
	   "ACCRINTM calculates and returns the accrued interest for a "
	   "security from @issue to @maturity date.  @rate is the annual "
	   "rate of the security and @par is the par value of the security. "
	   "If you omit @par, ACCRINTM applies $1,000 instead.  @basis is "
	   "the type of day counting system you want to use:\n"
	   "\n"
	   "0  US 30/360\n"
	   "1  actual days/actual days\n"
	   "2  actual days/360\n"
	   "3  actual days/365\n"
	   "4  European 30/360\n"
	   "\n"
	   "If @basis is omitted, US 30/360 is applied. "
	   "If issue date or maturity date is not valid, ACCRINTM returns "
272 273
	   "NUM! error. "
	   "If @rate or @par is zero or negative, ACCRINTM returns NUM! error. "
274 275 276 277
	   "If @basis < 0 or @basis > 4, ACCRINTM returns NUM! error. "
	   "If issue date is after maturity date or they are the same, "
	   "ACCRINTM returns NUM! error. "
	   "\n"
278 279
	   "@EXAMPLES=\n"
	   "\n"
280
	   "@SEEALSO=ACCRINT")
281 282 283 284 285 286 287 288 289
};

static Value *
gnumeric_accrintm (FunctionEvalInfo *ei, Value **argv)
{
	float_t rate, a, d, par;
	int     basis;

	rate = value_get_as_float (argv[2]);
Morten Welinder's avatar
Morten Welinder committed
290 291
	par = argv[3] ? value_get_as_float (argv[3]) : 1000;
	basis = argv[4] ? value_get_as_int (argv[4]) : 0;
292 293 294 295

	a = days_monthly_basis (argv[0], argv[1], basis);
	d = annual_year_basis (argv[0], basis);

Dom Lachowicz's avatar
Dom Lachowicz committed
296
	if (a < 0 || d <= 0 || par <= 0 || rate <= 0 || basis < 0 || basis > 4)
297
                return value_new_error (ei->pos, gnumeric_err_NUM);
298 299 300 301

	return value_new_float (par * rate * a/d);
}

302 303
/***************************************************************************/

304 305 306 307 308
static char *help_intrate = {
	N_("@FUNCTION=INTRATE\n"
	   "@SYNTAX=INTRATE(settlement,maturity,investment,redemption"
	   "[,basis])\n"
	   "@DESCRIPTION="
309 310
	   "INTRATE calculates and returns the interest rate of a fully "
	   "vested security. "
311 312 313 314 315 316 317 318 319 320 321 322 323
	   "@investment is the prize of the security paid at @settlement "
	   "date and @redemption is the amount to be received at @maturity "
	   "date.  @basis is the type of day counting system you want to "
	   "use:\n"
	   "\n"
	   "0  US 30/360\n"
	   "1  actual days/actual days\n"
	   "2  actual days/360\n"
	   "3  actual days/365\n"
	   "4  European 30/360\n"
	   "\n"
	   "If @basis is omitted, US 30/360 is applied. "
	   "If settlement date or maturity date is not valid, INTRATE returns "
324
	   "NUM! error. "
325 326 327 328
	   "If @basis < 0 or @basis > 4, INTRATE returns NUM! error. "
	   "If settlement date is after maturity date or they are the same, "
	   "INTRATE returns NUM! error. "
	   "\n"
329 330
	   "@EXAMPLES=\n"
	   "\n"
331 332 333 334 335 336 337 338 339
	   "If you had a bond with a settlement date of April 15, 2000, "
	   "maturity date September 30, 2000, investment of $100,000, "
	   "redemption value $103,525, using the actual/actual basis, the "
	   "bond discount rate is:"
	   "\n"
	   "=INTRATE(36631, 36799, 100000, 103525, 1) which equals 0.0648 "
	   "or 6.48%"
	   "\n"
	   "@SEEALSO=RECEIVED, DATE")
340 341 342 343 344 345 346 347 348 349
};

static Value *
gnumeric_intrate (FunctionEvalInfo *ei, Value **argv)
{
	float_t investment, redemption, a, d;
	int     basis;

	investment = value_get_as_float (argv[2]);
	redemption = value_get_as_float (argv[3]);
Morten Welinder's avatar
Morten Welinder committed
350
	basis = argv[4] ? value_get_as_int (argv[4]) : 0;
351 352 353 354

	a = days_monthly_basis (argv[0], argv[1], basis);
	d = annual_year_basis (argv[0], basis);

Dom Lachowicz's avatar
Dom Lachowicz committed
355
	if (basis < 0 || basis > 4 || a <= 0 || d <= 0 || investment == 0)
356
                return value_new_error (ei->pos, gnumeric_err_NUM);
357 358 359 360 361

	return value_new_float ((redemption - investment) / investment *
				(d / a));
}

362 363
/***************************************************************************/

364 365
static char *help_received = {
	N_("@FUNCTION=RECEIVED\n"
366
	   "@SYNTAX=RECEIVED(settlement,maturity,investment,rate[,basis])\n"
367 368 369 370 371 372 373 374 375 376 377 378 379 380
	   "@DESCRIPTION="
	   "RECEIVED calculates and returns the amount to be received at "
	   "@maturity date for a security bond. "
	   "@basis is the type of day counting system you want to "
	   "use:\n"
	   "\n"
	   "0  US 30/360\n"
	   "1  actual days/actual days\n"
	   "2  actual days/360\n"
	   "3  actual days/365\n"
	   "4  European 30/360\n"
	   "\n"
	   "If @basis is omitted, US 30/360 is applied. "
	   "If settlement date or maturity date is not valid, RECEIVED "
381
	   "returns NUM! error. "
382 383 384 385
	   "If @basis < 0 or @basis > 4, RECEIVED returns NUM! error. "
	   "If settlement date is after maturity date or they are the same, "
	   "RECEIVED returns NUM! error. "
	   "\n"
386 387
	   "@EXAMPLES=\n"
	   "\n"
388 389 390 391 392 393
	   "@SEEALSO=INTRATE")
};

static Value *
gnumeric_received (FunctionEvalInfo *ei, Value **argv)
{
394
	float_t investment, discount, a, d, n;
395 396 397 398
	int     basis;

	investment = value_get_as_float (argv[2]);
	discount = value_get_as_float (argv[3]);
Morten Welinder's avatar
Morten Welinder committed
399
	basis = argv[4] ? value_get_as_int (argv[4]) : 0;
400 401 402 403 404

	a = days_monthly_basis (argv[0], argv[1], basis);
	d = annual_year_basis (argv[0], basis);

	if (a <= 0 || d <= 0 || basis < 0 || basis > 4)
405
                return value_new_error (ei->pos, gnumeric_err_NUM);
406

407 408 409 410 411
	n = 1.0 - (discount * a/d);
	if (n == 0)
	        return value_new_error (ei->pos, gnumeric_err_NUM);

	return value_new_float (investment / n);
412 413
}

414 415
/***************************************************************************/

416 417
static char *help_pricedisc = {
	N_("@FUNCTION=PRICEDISC\n"
418
	   "@SYNTAX=PRICEDISC(settlement,maturity,discount,redemption[,basis])\n"
419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434
	   "@DESCRIPTION="
	   "PRICEDISC calculates and returns the price per $100 face value "
	   "of a security bond.  The security does not pay interest at "
	   "maturity.  @discount is the rate for which the security "
	   "is discounted.  @redemption is the amount to be received on "
	   "@maturity date.  @basis is the type of day counting system you "
	   "want to use:\n"
	   "\n"
	   "0  US 30/360\n"
	   "1  actual days/actual days\n"
	   "2  actual days/360\n"
	   "3  actual days/365\n"
	   "4  European 30/360\n"
	   "\n"
	   "If @basis is omitted, US 30/360 is applied. "
	   "If settlement date or maturity date is not valid, PRICEDISC "
435
	   "returns NUM! error. "
436 437 438 439
	   "If @basis < 0 or @basis > 4, PRICEDISC returns NUM! error. "
	   "If settlement date is after maturity date or they are the same, "
	   "PRICEDISC returns NUM! error. "
	   "\n"
440 441
	   "@EXAMPLES=\n"
	   "\n"
442 443 444 445 446 447 448 449 450 451 452
	   "@SEEALSO=PRICEMAT")
};

static Value *
gnumeric_pricedisc (FunctionEvalInfo *ei, Value **argv)
{
	float_t discount, redemption, a, d;
	int     basis;

	discount = value_get_as_float (argv[2]);
	redemption = value_get_as_float (argv[3]);
Morten Welinder's avatar
Morten Welinder committed
453
	basis = argv[4] ? value_get_as_int (argv[4]) : 0;
454 455 456 457 458

	a = days_monthly_basis (argv[0], argv[1], basis);
	d = annual_year_basis (argv[0], basis);

	if (a <= 0 || d <= 0 || basis < 0 || basis > 4)
459
                return value_new_error (ei->pos, gnumeric_err_NUM);
460 461 462 463

	return value_new_float (redemption - discount * redemption * a/d);
}

464 465
/***************************************************************************/

466 467
static char *help_pricemat = {
	N_("@FUNCTION=PRICEMAT\n"
468
	   "@SYNTAX=PRICEMAT(settlement,maturity,issue,rate,yield[,basis])\n"
469 470 471 472 473 474 475 476 477 478 479 480 481
	   "@DESCRIPTION="
	   "PRICEMAT calculates and returns the price per $100 face value "
	   "of a security.  The security pays interest at maturity. "
	   "@basis is the type of day counting system you want to use:\n"
	   "\n"
	   "0  US 30/360\n"
	   "1  actual days/actual days\n"
	   "2  actual days/360\n"
	   "3  actual days/365\n"
	   "4  European 30/360\n"
	   "\n"
	   "If @basis is omitted, US 30/360 is applied. "
	   "If settlement date or maturity date is not valid, PRICEMAT "
482
	   "returns NUM! error. "
483 484 485 486
	   "If @basis < 0 or @basis > 4, PRICEMAT returns NUM! error. "
	   "If settlement date is after maturity date or they are the same, "
	   "PRICEMAT returns NUM! error. "
	   "\n"
487 488
	   "@EXAMPLES=\n"
	   "\n"
489 490 491 492 493 494
	   "@SEEALSO=PRICEDISC")
};

static Value *
gnumeric_pricemat (FunctionEvalInfo *ei, Value **argv)
{
495
	float_t discount, yield, a, b, dsm, dim, n;
496 497 498 499
	int     basis;

	discount = value_get_as_float (argv[3]);
	yield = value_get_as_float (argv[4]);
Morten Welinder's avatar
Morten Welinder committed
500
	basis = argv[5] ? value_get_as_int (argv[5]) : 0;
501 502 503 504 505 506

	dsm = days_monthly_basis (argv[0], argv[1], basis);
	dim = days_monthly_basis (argv[2], argv[1], basis);
	a = days_monthly_basis (argv[2], argv[0], basis);
	b = annual_year_basis (argv[0], basis);

Morten Welinder's avatar
Morten Welinder committed
507
	if (a <= 0 || b <= 0 || dsm <= 0 || dim <= 0 || basis < 0 ||
508
	    basis > 4)
509
                return value_new_error (ei->pos, gnumeric_err_NUM);
510

511 512 513 514
	n = 1 + (dsm/b * yield);
	if (n == 0)
	        return value_new_error (ei->pos, gnumeric_err_NUM);

515
	return value_new_float (((100 + (dim/b * discount * 100)) /
516
				 (n)) - (a/b * discount * 100));
517
}
518

519 520
/***************************************************************************/

521 522
static char *help_disc = {
	N_("@FUNCTION=DISC\n"
Jon K Hellan's avatar
Jon K Hellan committed
523
	   "@SYNTAX=DISC(settlement,maturity,par,redemption[,basis])\n"
524 525 526 527 528 529 530 531 532 533 534 535
	   "@DESCRIPTION="
	   "DISC calculates and returns the discount rate for a sequrity. "
	   "@basis is the type of day counting system you want to use:\n"
	   "\n"
	   "0  US 30/360\n"
	   "1  actual days/actual days\n"
	   "2  actual days/360\n"
	   "3  actual days/365\n"
	   "4  European 30/360\n"
	   "\n"
	   "If @basis is omitted, US 30/360 is applied. "
	   "If settlement date or maturity date is not valid, DISC "
536
	   "returns NUM! error. "
537 538 539 540
	   "If @basis < 0 or @basis > 4, DISC returns NUM! error. "
	   "If settlement date is after maturity date or they are the same, "
	   "DISC returns NUM! error. "
	   "\n"
541 542
	   "@EXAMPLES=\n"
	   "\n"
543 544 545 546 547 548 549 550 551 552 553
	   "@SEEALSO=")
};

static Value *
gnumeric_disc (FunctionEvalInfo *ei, Value **argv)
{
	float_t par, redemption, dsm, b;
	int     basis;

	par = value_get_as_float (argv[2]);
	redemption = value_get_as_float (argv[3]);
Morten Welinder's avatar
Morten Welinder committed
554
	basis = argv[4] ? value_get_as_int (argv[4]) : 0;
555 556 557 558

	b = annual_year_basis (argv[0], basis);
	dsm = days_monthly_basis (argv[0], argv[1], basis);

Dom Lachowicz's avatar
Dom Lachowicz committed
559
	if (dsm <= 0 || b <= 0 || dsm <= 0 || basis < 0 || basis > 4 || redemption == 0)
560
                return value_new_error (ei->pos, gnumeric_err_NUM);
561 562 563 564

	return value_new_float ((redemption - par) / redemption * (b / dsm));
}

565 566
/***************************************************************************/

567 568
static char *help_effect = {
	N_("@FUNCTION=EFFECT\n"
569
	   "@SYNTAX=EFFECT(r,nper)\n"
570 571
	   "@DESCRIPTION="
	   "EFFECT calculates the effective interest rate from "
572 573 574 575 576 577 578 579
	   "a given nominal rate.\n"
	   "Effective interest rate is calculated using this formulae:\n"
	   "\n"
	   "         r"
           "( 1 + ------ ) ^ nper - 1"
           "       nper"
	   "\n"
	   "where:\n"
580
	   "\n"
581 582
	   "@r = nominal interest rate (stated in yearly terms)\n"
	   "@nper = number of periods used for compounding"
583
	   "\n"
584 585
	   "For example credit cards will list an APR (annual percentage "
	   "rate) which is a nominal interest rate."
586
	   "\n"
587 588 589
	   "For example if you wanted to find out how much you are actually "
	   "paying interest on your credit card that states an APR of 19% "
	   "that is compounded monthly you would type in:"
590
	   "\n"
591 592
	   "=EFFECT(.19,12) and you would get .2075 or 20.75%. That is the "
	   "effective percentage you will pay on your loan."
593
	   "\n"
594 595
	   "@EXAMPLES=\n"
	   "\n"
596
	   "@SEEALSO=NOMINAL")
597
};
598

599
static Value *
600
gnumeric_effect (FunctionEvalInfo *ei, Value **argv)
601
{
602
	float_t rate;
603 604
	int nper;

605 606
	rate = value_get_as_float (argv[0]);
	nper = value_get_as_int (argv[1]);
607

608
	/* Rate or number of periods cannot be negative */
Dom Lachowicz's avatar
Dom Lachowicz committed
609
	if ((rate < 0) || (nper <= 0))
610
		return value_new_error (ei->pos, _("effect - domain error"));
611

Morten Welinder's avatar
Morten Welinder committed
612
        return value_new_float ( pow ( (1 + rate/nper) , nper) - 1 );
613
}
614

615 616
/***************************************************************************/

617 618
static char *help_nominal = {
	N_("@FUNCTION=NOMINAL\n"
619
	   "@SYNTAX=NOMINAL(r,nper)\n"
620 621
	   "@DESCRIPTION="
	   "NOMINAL calculates the nominal interest rate from "
622 623 624 625 626 627 628
	   "a given effective rate.\n"
	   "Nominal interest rate is given by a formula:\n"
	   "\n"
           "nper * (( 1 + r ) ^ (1 / nper) - 1 )"
	   "\n"
	   "where:\n"
	   "\n"
629
	   "r = effective interest rate\n"
630 631
	   "nper = number of periods used for compounding"
	   "\n"
632 633
	   "@EXAMPLES=\n"
	   "\n"
634 635 636 637
	   "@SEEALSO=EFFECT")
};

static Value *
638
gnumeric_nominal (FunctionEvalInfo *ei, Value **argv)
639
{
640
	float_t rate;
641
	int nper;
642

643 644
	rate = value_get_as_float (argv[0]);
	nper = value_get_as_int (argv[1]);
645 646

	/* Rate or number of periods cannot be negative */
647
	if (rate < 0 || nper <= 0)
648
		return value_new_error (ei->pos, _("nominal - domain error"));
649

Morten Welinder's avatar
Morten Welinder committed
650
        return value_new_float ( nper * ( pow ( 1 + rate, 1.0/nper ) - 1 ) );
651 652 653

}

654 655
/***************************************************************************/

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
656 657 658 659
static char *help_ispmt = {
	N_("@FUNCTION=ISPMT\n"
	   "@SYNTAX=ISPMT(rate,per,nper,pv)\n"
	   "@DESCRIPTION="
Morten Welinder's avatar
Morten Welinder committed
660
	   "ISPMT function returns the interest paid on a given period. "
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
661 662 663
	   "\n"
	   "If @per < 1 or @per > @nper, ISPMT returns #NUM! error. "
	   "\n"
664 665
	   "@EXAMPLES=\n"
	   "\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
666 667 668 669 670 671 672 673 674 675 676 677 678 679 680
	   "@SEEALSO=PV")
};

static Value *
gnumeric_ispmt (FunctionEvalInfo *ei, Value **argv)
{
	float_t rate, pv, tmp;
	int     nper, per;

	rate = value_get_as_float (argv[0]);
	per = value_get_as_int (argv[1]);
	nper = value_get_as_int (argv[2]);
	pv = value_get_as_float (argv[3]);

	if (per < 1 || per > nper)
681
                return value_new_error (ei->pos, gnumeric_err_NUM);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
682 683 684 685 686 687

	tmp = - pv * rate;

	return value_new_float (tmp - (tmp / nper * per));
}

688 689
/***************************************************************************/

690 691
static char *help_db = {
	N_("@FUNCTION=DB\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
692
	   "@SYNTAX=DB(cost,salvage,life,period[,month])\n"
693
	   "@DESCRIPTION="
694
	   "DB calculates the depreciation of an asset for a given period "
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
695 696 697 698 699 700 701
	   "using the fixed-declining balance method.  @cost is the "
	   "initial value of the asset.  @salvage after the depreciation. "
	   "@life is the number of periods overall.  @period is the period "
	   "for which you want the depreciation to be calculated.  @month "
	   "is the number of months in the first year of depreciation. "
	   "If @month is omitted, it is assumed to be 12. "
	   "\n"
702 703
	   "@EXAMPLES=\n"
	   "\n"
Jody Goldberg's avatar
Jody Goldberg committed
704
	   "@SEEALSO=DDB,SLN,SYD")
705 706 707 708 709 710 711 712 713 714 715 716 717 718
};

static Value *
gnumeric_db (FunctionEvalInfo *ei, Value **argv)
{
	float_t rate;
	float_t cost, salvage, life, period, month;
	float_t total;
	int     i;

	cost = value_get_as_float (argv[0]);
	salvage = value_get_as_float (argv[1]);
	life = value_get_as_float (argv[2]);
	period = value_get_as_float (argv[3]);
Morten Welinder's avatar
Morten Welinder committed
719
	month = argv[4] ? value_get_as_float (argv[4]) : 12;
720

721
	if (cost == 0 || life <= 0)
Dom Lachowicz's avatar
Dom Lachowicz committed
722 723
	        return value_new_error (ei->pos, gnumeric_err_NUM);

Morten Welinder's avatar
Morten Welinder committed
724
	rate = 1 - pow ((salvage / cost), (1 / life));
725 726 727 728 729 730 731
	rate *= 1000;
	rate = floor(rate+0.5) / 1000;

	total = cost * rate * month / 12;

        if (period == 1)
	       return value_new_float (total);
732

Morten Welinder's avatar
Morten Welinder committed
733 734
	for (i = 1; i < life; i++)
	       if (i == period - 1)
735 736 737
		       return value_new_float ((cost - total) * rate);
	       else
		       total += (cost - total) * rate;
738

739 740
	return value_new_float (((cost - total) * rate * (12 - month)) / 12);
}
741

742 743
/***************************************************************************/

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
744 745 746 747 748 749 750 751 752 753 754 755 756
static char *help_ddb = {
	N_("@FUNCTION=DDB\n"
	   "@SYNTAX=DDB(cost,salvage,life,period[,factor])\n"
	   "@DESCRIPTION="
	   "DDB returns the depreciation of an asset for a given period "
	   "using the double-declining balance method or some other similar "
	   "method you specify.  @cost is the initial value of the asset, "
	   "@salvage is the value after the last period, @life is the "
	   "number of periods, @period is the period for which you want the "
	   "depreciation to be calculated, and @factor is the factor at "
	   "which the balance declines.  If @factor is omitted, it is "
	   "assumed to be two (double-declining balance method). "
	   "\n"
757 758
	   "@EXAMPLES=\n"
	   "\n"
Jody Goldberg's avatar
Jody Goldberg committed
759
	   "@SEEALSO=SLN,SYD")
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
760 761 762 763 764 765 766 767 768 769 770 771 772
};

static Value *
gnumeric_ddb (FunctionEvalInfo *ei, Value **argv)
{
	float_t cost, salvage, life, period, factor;
	float_t total;
	int     i;

	cost = value_get_as_float (argv[0]);
	salvage = value_get_as_float (argv[1]);
	life = value_get_as_float (argv[2]);
	period = value_get_as_float (argv[3]);
Morten Welinder's avatar
Morten Welinder committed
773
	factor = argv[4] ? value_get_as_float (argv[4]) : 2;
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
774

Dom Lachowicz's avatar
Dom Lachowicz committed
775 776 777
	if (life <= 0)
	        return value_new_error (ei->pos, gnumeric_err_NUM);

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
778
	total = 0;
Morten Welinder's avatar
Morten Welinder committed
779 780 781
	for (i = 0; i < life - 1; i++) {
	        float_t period_dep = (cost - total) * (factor / life);
		if (period - 1 == i)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
782 783 784 785 786 787 788 789
		        return value_new_float (period_dep);
		else
		        total += period_dep;
	}

	return value_new_float (cost - total - salvage);
}

790 791
/***************************************************************************/

792 793
static char *help_sln = {
	N_("@FUNCTION=SLN\n"
794
	   "@SYNTAX=SLN(cost,salvage_value,life)\n"
795

796
	   "@DESCRIPTION="
797 798 799 800 801 802
	   "The SLN function will determine the straight line depreciation "
	   "of an assest for a single period. The amount you paid for the "
	   "asset is the cost, salvage is the value of the asset at the "
	   "end of its useful life, and life is the number of periods over "
	   "which an the asset is depreciated. This method of deprecition "
	   "devides the cost evenly ofver the life of an asset."
803
	   "\n"
804
	   "The formula used for straight line depriciation is:"
805
	   "\n"
806
	   "Depriciation expense = ( cost - salvage_value ) / life"
807
	   "\n"
808
	   "\t@cost = cost of an asset when acquired (market value)."
809
	   "\t@salvage_value = amount you get when asset sold at the end "
810 811
	   "of the assets's useful life."
	   "\t@life = anticipated life of an asset."
812
	   "\n"
813
	   "@EXAMPLES=\n"
814 815 816 817 818 819 820 821
	   "For example, lets suppose your company purchases a new machine "
	   "for $10,000, which has a salvage value of $700 and will have a "
	   "useful life of 10 years. The SLN yearly depreciation is "
	   "computed as follows:"
	   "\n"
	   "=SLN(10000, 700, 10)"
	   "\n"
	   "This will return the yearly depreciation figure of $930."
822
	   "\n"
823
	   "@SEEALSO=SYD")
824 825 826 827
};


static Value *
828
gnumeric_sln (FunctionEvalInfo *ei, Value **argv)
829
{
830
	float_t cost,salvage_value,life;
831

832 833 834
	cost = value_get_as_float (argv[0]);
	salvage_value = value_get_as_int (argv[1]);
	life = value_get_as_float (argv[2]);
835 836

	/* Life of an asset cannot be negative */
837
	if (life <= 0)
838
		return value_new_error (ei->pos, _("sln - domain error"));
839

840
        return value_new_float ((cost - salvage_value) / life);
841 842
}

843 844
/***************************************************************************/

845 846
static char *help_syd = {
	N_("@FUNCTION=SYD\n"
847
	   "@SYNTAX=SYD(cost,salvage_value,life,period)\n"
848

849
	   "@DESCRIPTION="
850 851 852 853 854 855 856 857
	   "The SYD function calculates the sum-of-years digits depriciation "
	   "for an asset based on its cost, salvage value, anticipated life "
	   "and a particular period. This method accelerates the rate of the "
	   "depreciation, so that more depreciation expense occurs in "
	   "earlier periods than in later ones. The depreciable cost is the "
	   "actual cost minus the salvage value. The useful life is the "
	   "number of periods (typically years) over with the asset is "
	   "depreciated."
858
	   "\n"
859
	   "The Formula used for sum-of-years digits depriciation is:"
860
	   "\n"
861 862
	   "Depriciation expense = ( cost - salvage_value ) * (life - period "
	   "+ 1) * 2 / life * (life + 1)."
863
	   "\n"
864
	   "\t@cost = cost of an asset when acquired (market value)."
865
	   "\t@salvage_value = amount you get when asset sold at the end of "
866 867 868
	   "its useful life."
	   "\t@life = anticipated life of an asset."
	   "\t@period = period for which we need the expense."
869
	   "\n"
870
	   "@EXAMPLES=\n"
871 872 873 874 875 876
	   "For example say a company purchases a new computer for $5000 "
	   "which has a salvage value of $200, and a useful life of three "
	   "years. We would use the following to calculate the second "
	   "year's depreciation using the SYD method:"
	   "\n"
	   "=SYD(5000, 200, 5, 2) which returns 1,280.00."
877
	   "\n"
878
	   "@SEEALSO=SLN")
879 880 881
};

static Value *
882
gnumeric_syd (FunctionEvalInfo *ei, Value **argv)
883
{
884
	float_t cost, salvage_value, life, period;
885

Morten Welinder's avatar
Morten Welinder committed
886 887 888 889
	cost   = value_get_as_float (argv[0]);
	salvage_value = value_get_as_int (argv[1]);
	life   = value_get_as_float (argv[2]);
	period = value_get_as_float (argv[3]);
890

891
	/* Life of an asset cannot be negative */
892
	if (life <= 0)
893
		return value_new_error (ei->pos, _("syd - domain error"));
894

895 896 897
        return value_new_float (((cost - salvage_value) *
				 (life - period + 1) * 2) /
				(life * (life + 1.0)));
898 899
}

900 901
/***************************************************************************/

902 903 904
static char *help_dollarde = {
	N_("@FUNCTION=DOLLARDE\n"
	   "@SYNTAX=DOLLARDE(fractional_dollar,fraction)\n"
905 906
	   "@DESCRIPTION="
	   "DOLLARDE converts a dollar price expressed as a "
907 908
	   "fraction into a dollar price expressed as a decimal number. "
	   "\n"
909 910
	   "If @fraction is non-integer it is truncated. "
	   "If @fraction<=0 DOLLARDE returns #NUM! error. "
911
	   "\n"
912 913
	   "@EXAMPLES=\n"
	   "\n"
914 915 916 917
	   "@SEEALSO=DOLLARFR")
};

static Value *
918
gnumeric_dollarde (FunctionEvalInfo *ei, Value **argv)
919 920 921 922 923
{
        float_t fractional_dollar;
	int     fraction, n, tmp;
	float_t floored, rest;

Morten Welinder's avatar
Morten Welinder committed
924 925
	fractional_dollar = value_get_as_float (argv[0]);
	fraction = value_get_as_int (argv[1]);
926

927
	if (fraction <= 0)
928
                return value_new_error (ei->pos, gnumeric_err_NUM);
929 930 931 932 933 934

	tmp = fraction;
	/* Count digits in fraction */
	for (n=0; tmp; n++)
	        tmp /= 10;

935
	floored = floor (fractional_dollar);
936 937
	rest = fractional_dollar - floored;

Morten Welinder's avatar
Morten Welinder committed
938
	return value_new_float (floored + ((float_t) rest * pow (10,n) /
939
					   fraction));
940 941
}

942 943
/***************************************************************************/

944 945 946
static char *help_dollarfr = {
	N_("@FUNCTION=DOLLARFR\n"
	   "@SYNTAX=DOLLARFR(decimal_dollar,fraction)\n"
947 948
	   "@DESCRIPTION="
	   "DOLLARFR converts a decimal dollar price into "
949 950
	   "a dollar price expressed as a fraction. "
	   "\n"
951
	   "If @fraction is non-integer it is truncated. "
Kjartan Maraas's avatar
Kjartan Maraas committed
952
	   "If @fraction <= 0 DOLLARFR returns #NUM! error. "
953
	   "\n"
954 955
	   "@EXAMPLES=\n"
	   "\n"
956 957 958 959
	   "@SEEALSO=DOLLARDE")
};

static Value *
960
gnumeric_dollarfr (FunctionEvalInfo *ei, Value **argv)
961 962 963 964 965
{
        float_t fractional_dollar;
	int     fraction, n, tmp;
	float_t floored, rest;

Morten Welinder's avatar
Morten Welinder committed
966 967
	fractional_dollar = value_get_as_float (argv[0]);
	fraction = value_get_as_int (argv[1]);
968

969
	if (fraction <= 0)
970
                return value_new_error (ei->pos, gnumeric_err_NUM);
971 972

	/* Count digits in fraction */
973
	tmp = fraction;
974 975 976
	for (n=0; tmp; n++)
	        tmp /= 10;

977
	floored = floor (fractional_dollar);
978 979
	rest = fractional_dollar - floored;

980
	return value_new_float (floored + ((float_t) (rest*fraction) /
Morten Welinder's avatar
Morten Welinder committed
981
					   pow (10, n)));
982
}
983

984 985
/***************************************************************************/

986 987 988 989 990 991 992
static char *help_mirr = {
	N_("@FUNCTION=MIRR\n"
	   "@SYNTAX=MIRR(values,finance_rate,reinvest_rate)\n"
	   "@DESCRIPTION="
	   "MIRR function returns the modified internal rate of return "
	   "for a given periodic cash flow. "
	   "\n"
993 994
	   "@EXAMPLES=\n"
	   "\n"
995 996 997 998 999 1000 1001
	   "@SEEALSO=NPV")
};

static Value *
gnumeric_mirr (FunctionEvalInfo *ei, Value **argv)
{
	float_t frate, rrate, npv_neg, npv_pos;
1002
	float_t *values = NULL, res;
1003
	Value   *result = NULL;
1004
	int     i, n;
1005 1006 1007 1008

	frate = value_get_as_float (argv[1]);
	rrate = value_get_as_float (argv[2]);

1009 1010 1011
	values = collect_floats_value (argv[0], ei->pos,
				       COLLECT_IGNORE_STRINGS,
				       &n, &result);
1012 1013 1014
	if (result)
		goto out;

1015 1016 1017 1018 1019 1020 1021
	for (i = 0, npv_pos = npv_neg = 0; i < n; i++) {
		float_t v = values[i];
		if (v >= 0)
			npv_pos += v / pow (1 + rrate, i);
		else
			npv_neg += v / pow (1 + frate, i);
	}
Dom Lachowicz's avatar
Dom Lachowicz committed
1022

1023 1024 1025 1026
	if (npv_neg == 0 || npv_pos == 0 || rrate <= -1) {
		result = value_new_error (ei->pos, gnumeric_err_DIV0);
		goto out;
	}
Dom Lachowicz's avatar
Dom Lachowicz committed
1027

1028 1029 1030 1031 1032 1033
	/*
	 * I have my doubts about this formula, but it sort of looks like
	 * the one Microsoft claims to use and it produces the results
	 * that Excel does.  -- MW.
	 */
	res = pow ((-npv_pos * pow (1 + rrate, n)) / (npv_neg * (1 + rrate)),
Morten Welinder's avatar
Morten Welinder committed
1034
		  (1.0 /