functions.c 130 KB
Newer Older
1
/* vim: set sw=8: -*- Mode: C; tab-width: 8; indent-tabs-mode: t; c-basic-offset: 8 -*- */
2 3 4
/*
 * fn-financial.c:  Built in financial functions and functions registration
 *
5
 * Authors:
Morten Welinder's avatar
Morten Welinder committed
6 7 8 9
 *   Jukka-Pekka Iivonen (jiivonen@hutcs.cs.hut.fi)
 *   Morten Welinder (terra@diku.dk)
 *   Vladimir Vuksan (vuksan@veus.hr)
 *   Andreas J. Guelzow (aguelzow@taliesin.ca)
10
 *
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
11 12 13 14 15 16 17 18 19 20 21 22 23
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
24
 */
25 26 27 28 29
#include <gnumeric-config.h>
#include <gnumeric.h>
#include <func.h>
#include <parse-util.h>
#include <cell.h>
Morten Welinder's avatar
Morten Welinder committed
30
#include <tools/goal-seek.h>
31
#include <collect.h>
32
#include <value.h>
33
#include <str.h>
34
#include <mathfunc.h>
35
#include <gnm-format.h>
36 37
#include <workbook.h>
#include <sheet.h>
38 39
#include <gnm-datetime.h>
#include <gnm-i18n.h>
40
#include <goffice/app/go-plugin.h>
Jody Goldberg's avatar
Jody Goldberg committed
41
#include <gnm-plugin.h>
42

43 44 45 46
#include <math.h>
#include <limits.h>
#include <string.h>

47
#include "sc-fin.h"
Morten Welinder's avatar
Morten Welinder committed
48

Jody Goldberg's avatar
Jody Goldberg committed
49
GNM_PLUGIN_MODULE_HEADER;
Morten Welinder's avatar
Morten Welinder committed
50

51 52
#define is_valid_basis(B) (B >= 0 && B <= 5)
#define is_valid_freq(F) (F == 1 || F == 2 || F == 4)
53

54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
/***************************************************************************
 *
 *
 * Below are some of the functions that are used quite often in
 * financial analysis.
 *
 * Present value interest factor
 *
 *	 PVIF = (1 + k) ^ n
 *
 * Future value interest factor
 *
 *       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
 *
 *
 *
 *	 PV * PVIF(k%, nper) + PMT * ( 1 + rate * type ) *
 *	      FVIFA(k%, nper) + FV = 0
 *
85 86
 */

87 88
static gnm_float
calculate_pvif (gnm_float rate, gnm_float nper)
89
{
Morten Welinder's avatar
Morten Welinder committed
90
	return pow1p (rate, nper);
91 92
}

93 94
static gnm_float
calculate_fvifa (gnm_float rate, gnm_float nper)
95
{
96 97 98 99
	/* Removable singularity at rate == 0.  */
	if (rate == 0)
		return nper;
	else
Morten Welinder's avatar
Morten Welinder committed
100
		return pow1pm1 (rate, nper) / rate;
101 102
}

103

104 105 106
static gnm_float
calculate_interest_part (gnm_float pv, gnm_float pmt,
			 gnm_float rate, gnm_float per)
107
{
Morten Welinder's avatar
Morten Welinder committed
108 109
	return -(pv * pow1p (rate, per) * rate +
		 pmt * pow1pm1 (rate, per));
110 111
}

112 113
static gnm_float
calculate_pmt (gnm_float rate, gnm_float nper, gnm_float pv, gnm_float fv,
114
	       int type)
115
{
116
	gnm_float pvif, fvifa;
117 118 119

	/* Calculate the PVIF and FVIFA */

120 121
	pvif = calculate_pvif (rate, nper);
	fvifa = calculate_fvifa (rate, nper);
122

Morten Welinder's avatar
Morten Welinder committed
123
        return ((-pv * pvif - fv ) / ((1.0 + rate * type) * fvifa));
124 125
}

126 127 128 129 130 131 132 133 134 135 136 137 138
/***************************************************************************/

/* Returns the number of days between issue date and maturity date
 * accoring to the day counting system specified by the 'basis'
 * argument.  Basis may have one of the following values:
 *
 *	0  for US 30/360 (days in a month/days in a year)
 *	1  for actual days/actual days
 *	2  for actual days/360
 *	3  for actual days/365
 *	4  for European 30/360
 *
 */
139
static int
Jody Goldberg's avatar
Jody Goldberg committed
140 141 142
days_monthly_basis (GnmValue const *issue_date,
		    GnmValue const *maturity_date,
		    int basis, GODateConventions const *date_conv)
143
{
Jody Goldberg's avatar
Jody Goldberg committed
144
        GDate    date_i, date_m;
145 146 147 148 149
	int      issue_day, issue_month, issue_year;
	int      maturity_day, maturity_month, maturity_year;
        int      months, days, years;
	gboolean leap_year;
	int      maturity, issue;
150

151 152
	if (!datetime_value_to_g (&date_i, issue_date, date_conv) ||
	    !datetime_value_to_g (&date_m, maturity_date, date_conv))
153
	        return -1;
Jody Goldberg's avatar
Jody Goldberg committed
154 155 156 157 158 159 160 161 162 163 164 165 166 167

	issue_year = g_date_get_year (&date_i);
	issue_month = g_date_get_month (&date_i);
	issue_day = g_date_get_day (&date_i);
	maturity_year = g_date_get_year (&date_m);
	maturity_month = g_date_get_month (&date_m);
	maturity_day = g_date_get_day (&date_m);

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

	months = years * 12 + months;
	leap_year = g_date_is_leap_year (issue_year);
168 169 170

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

192 193 194 195 196
/***************************************************************************/

/* Returns the number of coupons to be paid between the settlement
 * and maturity dates.
 */
197
static gnm_float
Jody Goldberg's avatar
Jody Goldberg committed
198
coupnum (GDate const *settlement, GDate const *maturity,
199
	 GnmCouponConvention const *conv)
200
{
201
        int        months;
202
	GDate      this_coupondate = *maturity;
203

204 205 206
	months = g_date_get_month (maturity) - g_date_get_month (settlement) +
		12 *
		(g_date_get_year (maturity) - g_date_get_year  (settlement));
207 208 209

	g_date_subtract_months (&this_coupondate, months);

210
	if (conv->eom && g_date_is_last_of_month (maturity))
211
		while (!g_date_is_last_of_month (&this_coupondate))
212
			g_date_add_days (&this_coupondate, 1);
213

214
	if (g_date_get_day (settlement) >= g_date_get_day (&this_coupondate))
215 216
	        months--;

217
	return (1 + months / (12 / conv->freq));
218 219
}

220
static gnm_float
Jody Goldberg's avatar
Jody Goldberg committed
221
couppcd (GDate const *settlement, GDate const *maturity,
222
	 GnmCouponConvention const *conv)
223
{
Jody Goldberg's avatar
Jody Goldberg committed
224
	GDate date;
225 226
	coup_cd (&date, settlement, maturity, conv->freq, conv->eom, FALSE);
	return datetime_g_to_serial (&date, conv->date_conv);
227 228
}

229
static gnm_float
Jody Goldberg's avatar
Jody Goldberg committed
230
coupncd (GDate const *settlement, GDate const *maturity,
231
	 GnmCouponConvention const *conv)
232
{
Jody Goldberg's avatar
Jody Goldberg committed
233
	GDate date;
234 235
	coup_cd (&date, settlement, maturity, conv->freq, conv->eom, TRUE);
	return datetime_g_to_serial (&date, conv->date_conv);
236 237
}

238 239
static gnm_float
price (GDate *settlement, GDate *maturity, gnm_float rate, gnm_float yield,
240
       gnm_float redemption, GnmCouponConvention const *conv)
Jukka Pekka's avatar
Jukka Pekka committed
241
{
Morten Welinder's avatar
Morten Welinder committed
242
	gnm_float a, d, e, sum, den, basem1, exponent, first_term, last_term;
Jukka Pekka's avatar
Jukka Pekka committed
243 244
	gint       k, n;

245 246 247 248
	a = coupdaybs (settlement, maturity, conv);
	d = coupdaysnc (settlement, maturity, conv);
	e = coupdays (settlement, maturity, conv);
	n = coupnum (settlement, maturity, conv);
Jukka Pekka's avatar
Jukka Pekka committed
249 250

	sum = 0.0;
251
	den = 100.0 * rate / conv->freq;
Morten Welinder's avatar
Morten Welinder committed
252
	basem1 = yield / conv->freq;
Jukka Pekka's avatar
Jukka Pekka committed
253
	exponent = d / e;
Morten Welinder's avatar
Morten Welinder committed
254
	/* FIXME: Eliminate loop.  */
Jukka Pekka's avatar
Jukka Pekka committed
255
	for (k = 0; k < n; k++)
Morten Welinder's avatar
Morten Welinder committed
256
	        sum += den / pow1p (basem1, exponent + k);
Jukka Pekka's avatar
Jukka Pekka committed
257

Morten Welinder's avatar
Morten Welinder committed
258
	first_term = redemption / pow1p (basem1, (n - 1.0 + d / e));
Jukka Pekka's avatar
Jukka Pekka committed
259 260 261 262 263
	last_term = a / e * den;

	return (first_term + sum - last_term);
}

264 265
/************************************************************************
 *
266
 * Reading and verifying the arguments for the various COUP____
267 268 269 270
 * functions. Calls the passed coup_fn to do the real work
 *
 ***********************************************************************/

Jody Goldberg's avatar
Jody Goldberg committed
271
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
272
func_coup (FunctionEvalInfo *ei, GnmValue const * const *argv,
273 274
	   gnm_float (coup_fn) (GDate const *settle, GDate const *mat,
				GnmCouponConvention const *conv))
275
{
Jody Goldberg's avatar
Jody Goldberg committed
276
        GDate   settlement, maturity;
277 278
	GnmCouponConvention conv;
	gboolean err = FALSE;
279

280 281 282 283
        conv.freq  = value_get_as_int (argv[2]);
	conv.basis = argv[3] ? value_get_as_int (argv[3]) : BASIS_MSRB_30_360;
	conv.eom   = argv[4] ? value_get_as_bool (argv[4], &err) : TRUE;
	conv.date_conv = workbook_date_conv (ei->pos->sheet->workbook);
284

285 286 287
        if (err ||
	    !datetime_value_to_g (&settlement, argv[0], conv.date_conv) ||
	    !datetime_value_to_g (&maturity, argv[1], conv.date_conv))
Jody Goldberg's avatar
Jody Goldberg committed
288
		return value_new_error_VALUE (ei->pos);
289

290 291 292
	if (!is_valid_basis (conv.basis) ||
	    !is_valid_freq (conv.freq) ||
	    g_date_compare (&settlement, &maturity) >= 0)
Jody Goldberg's avatar
Jody Goldberg committed
293
		return value_new_error_NUM (ei->pos);
294

295
	return value_new_float (coup_fn (&settlement, &maturity, &conv));
296
}
297

298 299 300 301 302
/***************************************************************************
 *
 * Financial function implementations
 *
 */
303

304 305 306
static GnmFuncHelp const help_accrint[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=ACCRINT\n"
307 308 309
	   "@SYNTAX=ACCRINT(issue,first_interest,settlement,rate,par,"
	   "frequency[,basis])\n"
	   "@DESCRIPTION="
310
	   "ACCRINT calculates the accrued interest for a security that "
311
	   "pays periodic interest.\n\n"
312 313
	   "@issue is the issue date of the security.  @first_interest is "
	   "the first interest date of the security.  @settlement is the "
314
	   "settlement date of the security.  The settlement date is always "
315 316
	   "after the issue date (the date when the security is bought). "
	   "@rate is the annual rate of the security and @par is the par "
317 318 319 320 321 322
	   "value of the security. @frequency is the number of coupon "
	   "payments per year.\n\n"
	   "Allowed frequencies are:\n"
	   "  1 = annual,\n"
	   "  2 = semi,\n"
	   "  4 = quarterly.\n\n"
323
	   "@basis is the type of day counting system you want to use:\n"
324
	   "\n"
325 326 327 328 329
	   "  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"
330
	   "\n"
331 332 333 334 335 336 337 338 339
	   "* If @issue date, @first_interest date, or @settlement date is not "
	   "valid, ACCRINT returns #NUM! error.\n"
	   "* The dates must be @issue < @first_interest < @settlement, or "
	   "ACCRINT returns #NUM! error.\n"
	   "* If @rate <= 0 or @par <= 0 , ACCRINT returns #NUM! error.\n"
	   "* If @basis is omitted, US 30/360 is applied.\n"
	   "* If @basis < 0 or @basis > 4, ACCRINT returns #NUM! error.\n"
	   "* If @issue date is after @settlement date or they are the same, "
	   "ACCRINT returns #NUM! error.\n"
340
	   "\n"
341 342
	   "@EXAMPLES=\n"
	   "\n"
343
	   "@SEEALSO=ACCRINTM")
344 345
	},
	{ GNM_FUNC_HELP_END }
346 347
};

Jody Goldberg's avatar
Jody Goldberg committed
348
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
349
gnumeric_accrint (FunctionEvalInfo *ei, GnmValue const * const *argv)
350
{
Jody Goldberg's avatar
Jody Goldberg committed
351 352
        GDate      issue, first_interest, settlement;
	gnm_float rate, a, d, par, freq;
353
	int        basis;
354
	GODateConventions const *date_conv =
355
		workbook_date_conv (ei->pos->sheet->workbook);
356

Jody Goldberg's avatar
Jody Goldberg committed
357 358 359 360 361
        if (!datetime_value_to_g (&issue, argv[0], date_conv) ||
	    !datetime_value_to_g (&first_interest, argv[1], date_conv) ||
	    !datetime_value_to_g (&settlement, argv[2], date_conv))
		return value_new_error_VALUE (ei->pos);

362 363 364 365 366
	rate           = value_get_as_float (argv[3]);
	par            = value_get_as_float (argv[4]);
	freq           = value_get_as_float (argv[5]);
	basis          = argv[6] ? value_get_as_int (argv[6]) : 0;

Jody Goldberg's avatar
Jody Goldberg committed
367 368 369 370 371
        if (rate <= 0.	||
	    par <= 0.	||
	    !is_valid_freq (freq)	||
	    !is_valid_basis (basis)	||
	    g_date_compare (&issue, &settlement) >= 0)
Jody Goldberg's avatar
Jody Goldberg committed
372
		return value_new_error_NUM (ei->pos);
373

374 375
	a = days_monthly_basis (argv[0], argv[2], basis, date_conv);
	d = annual_year_basis (argv[0], basis, date_conv);
Jody Goldberg's avatar
Jody Goldberg committed
376
	if (a < 0 || d <= 0)
Jody Goldberg's avatar
Jody Goldberg committed
377
		return value_new_error_NUM (ei->pos);
378

Jody Goldberg's avatar
Jody Goldberg committed
379 380 381 382 383 384 385 386 387 388
	/* FIXME : According to XL docs
	 *
	 * NC = number of quasi-coupon periods that fit in odd period. If this
	 * 	number contains a fraction, raise it to the next whole number. 
	 * Ai = number of accrued days for the ith quasi-coupon period within odd period. 
	 * NLi = normal length in days of the ith quasi-coupon period within odd period. 
	 *
	 * XL == par * (rate/freq) * Sum (1..NC of Ai / NLi
	 */
	return value_new_float (par * rate * a / d);
389 390
}

391 392
/***************************************************************************/

393 394 395
static GnmFuncHelp const help_accrintm[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=ACCRINTM\n"
396 397 398
	   "@SYNTAX=ACCRINTM(issue,maturity,rate[,par,basis])\n"
	   "@DESCRIPTION="
	   "ACCRINTM calculates and returns the accrued interest for a "
399
	   "security from @issue to @maturity date.\n\n"
400 401
	   "@issue is the issue date of the security.  @maturity is "
	   "the maturity date of the security.  @rate is the annual "
402
	   "rate of the security and @par is the par value of the security. "
403 404
	   "If you omit @par, ACCRINTM applies $1,000 instead.  "
	   "@basis is the type of day counting system you want to use:\n"
405
	   "\n"
406 407 408 409 410
	   "  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"
411
	   "\n"
412 413 414 415 416 417 418
	   "* If @issue date or @maturity date is not valid, ACCRINTM returns "
	   "#NUM! error.\n"
	   "* If @rate <= 0 or @par <= 0, ACCRINTM returns #NUM! error.\n"
	   "* If @basis is omitted, US 30/360 is applied.\n"
	   "* If @basis < 0 or @basis > 4, ACCRINTM returns #NUM! error.\n"
	   "* If @issue date is after @maturity date or they are the same, "
	   "ACCRINTM returns #NUM! error.\n"
419
	   "\n"
420 421
	   "@EXAMPLES=\n"
	   "\n"
422
	   "@SEEALSO=ACCRINT")
423 424
	},
	{ GNM_FUNC_HELP_END }
425 426
};

Jody Goldberg's avatar
Jody Goldberg committed
427
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
428
gnumeric_accrintm (FunctionEvalInfo *ei, GnmValue const * const *argv)
429
{
430
	gnm_float rate, a, d, par;
Morten Welinder's avatar
Morten Welinder committed
431
	int basis;
432
	GODateConventions const *date_conv =
433
		workbook_date_conv (ei->pos->sheet->workbook);
434

435 436
	rate  = value_get_as_float (argv[2]);
	par   = argv[3] ? value_get_as_float (argv[3]) : 1000;
Morten Welinder's avatar
Morten Welinder committed
437
	basis = argv[4] ? value_get_as_int (argv[4]) : 0;
438

439 440
	a = days_monthly_basis (argv[0], argv[1], basis, date_conv);
	d = annual_year_basis (argv[0], basis, date_conv);
441

442 443
	if (a < 0 || d <= 0 || par <= 0 || rate <= 0
	    || !is_valid_basis (basis))
444
                return value_new_error_NUM (ei->pos);
445 446 447 448

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

449 450
/***************************************************************************/

451 452 453
static GnmFuncHelp const help_intrate[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=INTRATE\n"
454
	   "@SYNTAX=INTRATE(settlement,maturity,investment,redemption[,basis])\n"
455
	   "@DESCRIPTION="
456
	   "INTRATE calculates and returns the interest rate of a fully "
457 458
	   "vested security.\n\n"
	   "@settlement is the settlement date of the security.  @maturity "
459
	   "is the maturity date of the security. @investment is the price "
460 461
	   "of the security paid at @settlement date and @redemption is "
	   "the amount to be received at @maturity date.\n\n"
462
	   "@basis is the type of day counting system you want to use:\n"
463
	   "\n"
464 465 466 467 468
	   "  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"
469
	   "\n"
470 471 472 473 474 475
	   "* If @settlement date or @maturity date is not valid, INTRATE "
	   "returns #NUM! error.\n"
	   "* If @basis is omitted, US 30/360 is applied.\n"
	   "* If @basis < 0 or @basis > 4, INTRATE returns #NUM! error.\n"
	   "* If @settlement date is after @maturity date or they are the "
	   "same, INTRATE returns #NUM! error.\n"
476
	   "\n"
477 478
	   "@EXAMPLES=\n"
	   "\n"
479 480 481 482 483 484 485 486 487
	   "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")
488 489
	},
	{ GNM_FUNC_HELP_END }
490 491
};

Jody Goldberg's avatar
Jody Goldberg committed
492
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
493
gnumeric_intrate (FunctionEvalInfo *ei, GnmValue const * const *argv)
494
{
495
	gnm_float investment, redemption, a, d;
Morten Welinder's avatar
Morten Welinder committed
496
	int basis;
497
	GODateConventions const *date_conv =
498
		workbook_date_conv (ei->pos->sheet->workbook);
499 500 501

	investment = value_get_as_float (argv[2]);
	redemption = value_get_as_float (argv[3]);
502
	basis      = argv[4] ? value_get_as_int (argv[4]) : 0;
503

504 505
	a = days_monthly_basis (argv[0], argv[1], basis, date_conv);
	d = annual_year_basis (argv[0], basis, date_conv);
506

507
	if (!is_valid_basis (basis) || a <= 0 || d <= 0 || investment == 0)
508
                return value_new_error_NUM (ei->pos);
509 510 511 512 513

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

514 515
/***************************************************************************/

516 517 518
static GnmFuncHelp const help_received[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=RECEIVED\n"
519
	   "@SYNTAX=RECEIVED(settlement,maturity,investment,rate[,basis])\n"
520 521
	   "@DESCRIPTION="
	   "RECEIVED calculates and returns the amount to be received at "
522 523
	   "maturity date for a security bond.\n"
	   "\n"
524 525
	   "@settlement is the settlement date of the security.  "
	   "@maturity is the maturity date of the security.  The amount "
Jody Goldberg's avatar
Jody Goldberg committed
526
	   "of investment is specified in @investment.  @rate is the "
527
	   "security's discount rate.\n\n"
528 529 530
	   "@basis is the type of day counting system you want to "
	   "use:\n"
	   "\n"
531 532 533 534 535
	   "  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"
536
	   "\n"
537 538 539 540 541 542
	   "* If @settlement date or @maturity date is not valid, RECEIVED "
	   "returns #NUM! error.\n"
	   "* If @basis is omitted, US 30/360 is applied.\n"
	   "* If @basis < 0 or @basis > 4, RECEIVED returns #NUM! error.\n"
	   "* If @settlement date is after @maturity date or they are the "
	   "same, RECEIVED returns #NUM! error.\n"
543
	   "\n"
544 545
	   "@EXAMPLES=\n"
	   "\n"
546
	   "@SEEALSO=INTRATE")
547 548
	},
	{ GNM_FUNC_HELP_END }
549 550
};

Jody Goldberg's avatar
Jody Goldberg committed
551
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
552
gnumeric_received (FunctionEvalInfo *ei, GnmValue const * const *argv)
553
{
554
	gnm_float investment, discount, a, d, n;
Morten Welinder's avatar
Morten Welinder committed
555
	int basis;
556
	GODateConventions const *date_conv =
557
		workbook_date_conv (ei->pos->sheet->workbook);
558 559

	investment = value_get_as_float (argv[2]);
560 561
	discount   = value_get_as_float (argv[3]);
	basis      = argv[4] ? value_get_as_int (argv[4]) : 0;
562

563 564
	a = days_monthly_basis (argv[0], argv[1], basis, date_conv);
	d = annual_year_basis (argv[0], basis, date_conv);
565

566
	if (a <= 0 || d <= 0 || !is_valid_basis (basis))
567
                return value_new_error_NUM (ei->pos);
568

569 570
	n = 1.0 - (discount * a/d);
	if (n == 0)
571
	        return value_new_error_NUM (ei->pos);
572 573

	return value_new_float (investment / n);
574 575
}

576 577
/***************************************************************************/

578 579 580
static GnmFuncHelp const help_pricedisc[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=PRICEDISC\n"
581
	   "@SYNTAX=PRICEDISC(settlement,maturity,discount,redemption[,basis])\n"
582 583 584
	   "@DESCRIPTION="
	   "PRICEDISC calculates and returns the price per $100 face value "
	   "of a security bond.  The security does not pay interest at "
585 586
	   "maturity.\n\n"
	   "@settlement is the settlement date of the security. "
587 588
	   "@maturity is the maturity date of the security.  @discount is "
	   "the rate for which the security is discounted.  @redemption is "
589
	   "the amount to be received on @maturity date.\n\n"
590
	   "@basis is the type of day counting system you want to use:\n"
591
	   "\n"
592 593 594 595 596
	   "  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"
597
	   "\n"
598 599 600 601 602 603
	   "* If @settlement date or @maturity date is not valid, PRICEDISC "
	   "returns #NUM! error.\n"
	   "* If @basis is omitted, US 30/360 is applied.\n"
	   "* If @basis < 0 or @basis > 4, PRICEDISC returns #NUM! error.\n"
	   "* If @settlement date is after @maturity date or they are the "
	   "same, PRICEDISC returns #NUM! error.\n"
604
	   "\n"
605 606
	   "@EXAMPLES=\n"
	   "\n"
607
	   "@SEEALSO=PRICEMAT")
608 609
	},
	{ GNM_FUNC_HELP_END }
610 611
};

Jody Goldberg's avatar
Jody Goldberg committed
612
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
613
gnumeric_pricedisc (FunctionEvalInfo *ei, GnmValue const * const *argv)
614
{
615
	gnm_float discount, redemption, a, d;
Morten Welinder's avatar
Morten Welinder committed
616
	int basis;
617
	GODateConventions const *date_conv =
618
		workbook_date_conv (ei->pos->sheet->workbook);
619

620
	discount   = value_get_as_float (argv[2]);
621
	redemption = value_get_as_float (argv[3]);
622
	basis      = argv[4] ? value_get_as_int (argv[4]) : 0;
623

624 625
	a = days_monthly_basis (argv[0], argv[1], basis, date_conv);
	d = annual_year_basis (argv[0], basis, date_conv);
626

627
	if (a <= 0 || d <= 0 || !is_valid_basis (basis))
628
                return value_new_error_NUM (ei->pos);
629 630 631 632

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

633 634
/***************************************************************************/

635 636 637
static GnmFuncHelp const help_pricemat[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=PRICEMAT\n"
638
	   "@SYNTAX=PRICEMAT(settlement,maturity,issue,rate,yield[,basis])\n"
639 640
	   "@DESCRIPTION="
	   "PRICEMAT calculates and returns the price per $100 face value "
641
	   "of a security.  The security pays interest at maturity.\n\n"
642 643 644 645
	   "@settlement is the settlement date of the security.  @maturity is "
	   "the maturity date of the security.  @issue is the issue date of "
	   "the security.  @rate is the discount rate of the security. "
	   "@yield is the annual yield of the security. "
646 647
	   "@basis is the type of day counting system you want to use:\n"
	   "\n"
648 649 650 651 652
	   "  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"
653
	   "\n"
654 655 656 657 658 659
	   "* If @settlement date or @maturity date is not valid, PRICEMAT "
	   "returns #NUM! error.\n"
	   "* If @basis is omitted, US 30/360 is applied.\n"
	   "* If @basis < 0 or @basis > 4, PRICEMAT returns #NUM! error.\n"
	   "* If @settlement date is after @maturity date or they are the "
	   "same, PRICEMAT returns #NUM! error.\n"
660
	   "\n"
661 662
	   "@EXAMPLES=\n"
	   "\n"
663
	   "@SEEALSO=PRICEDISC")
664 665
	},
	{ GNM_FUNC_HELP_END }
666 667
};

Jody Goldberg's avatar
Jody Goldberg committed
668
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
669
gnumeric_pricemat (FunctionEvalInfo *ei, GnmValue const * const *argv)
670
{
671
	gnm_float discount, yield, a, b, dsm, dim, n;
Morten Welinder's avatar
Morten Welinder committed
672
	int basis;
673
	GODateConventions const *date_conv =
674
		workbook_date_conv (ei->pos->sheet->workbook);
675 676

	discount = value_get_as_float (argv[3]);
677 678
	yield    = value_get_as_float (argv[4]);
	basis    = argv[5] ? value_get_as_int (argv[5]) : 0;
679

680 681 682 683
	dsm = days_monthly_basis (argv[0], argv[1], basis, date_conv);
	dim = days_monthly_basis (argv[2], argv[1], basis, date_conv);
	a   = days_monthly_basis (argv[2], argv[0], basis, date_conv);
	b   = annual_year_basis (argv[0], basis, date_conv);
684

685 686
	if (a <= 0 || b <= 0 || dsm <= 0 || dim <= 0
	    || !is_valid_basis (basis))
687
                return value_new_error_NUM (ei->pos);
688

689 690
	n = 1 + (dsm/b * yield);
	if (n == 0)
691
	        return value_new_error_NUM (ei->pos);
692

693
	return value_new_float (((100 + (dim/b * discount * 100)) /
694
				 (n)) - (a/b * discount * 100));
695
}
696

697 698
/***************************************************************************/

699 700 701
static GnmFuncHelp const help_disc[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=DISC\n"
Jon K Hellan's avatar
Jon K Hellan committed
702
	   "@SYNTAX=DISC(settlement,maturity,par,redemption[,basis])\n"
703
	   "@DESCRIPTION="
704
	   "DISC calculates and returns the discount rate for a security. "
705 706 707
	   "@settlement is the settlement date of the security.\n\n"
	   "@maturity is the maturity date of the security.  @par is the "
	   "price per $100 face value of the security.  @redemption is the "
J.H.M. Dassen (Ray)'s avatar
J.H.M. Dassen (Ray) committed
708
	   "redemption value per $100 face value of the security.\n\n"
709 710
	   "@basis is the type of day counting system you want to use:\n"
	   "\n"
711 712 713 714 715
	   "  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"
716
	   "\n"
717 718 719 720 721 722
	   "* If @settlement date or @maturity date is not valid, DISC "
	   "returns #NUM! error.\n"
	   "* If @basis is omitted, US 30/360 is applied.\n"
	   "* If @basis < 0 or @basis > 4, DISC returns #NUM! error.\n"
	   "* If @settlement date is after @maturity date or they are the "
	   "same, DISC returns #NUM! error.\n"
723
	   "\n"
724 725
	   "@EXAMPLES=\n"
	   "\n"
726
	   "@SEEALSO=")
727 728
	},
	{ GNM_FUNC_HELP_END }
729 730
};

Jody Goldberg's avatar
Jody Goldberg committed
731
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
732
gnumeric_disc (FunctionEvalInfo *ei, GnmValue const * const *argv)
733
{
734
	gnm_float par, redemption, dsm, b;
Morten Welinder's avatar
Morten Welinder committed
735
	int basis;
736
	GODateConventions const *date_conv =
737
		workbook_date_conv (ei->pos->sheet->workbook);
738

739
	par        = value_get_as_float (argv[2]);
740
	redemption = value_get_as_float (argv[3]);
741
	basis      = argv[4] ? value_get_as_int (argv[4]) : 0;
742

743 744
	b = annual_year_basis (argv[0], basis, date_conv);
	dsm = days_monthly_basis (argv[0], argv[1], basis, date_conv);
745

746
	if (dsm <= 0 || b <= 0 || dsm <= 0 || !is_valid_basis (basis)
747
	    || redemption == 0)
748
                return value_new_error_NUM (ei->pos);
749 750 751 752

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

753 754
/***************************************************************************/

755 756 757
static GnmFuncHelp const help_effect[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=EFFECT\n"
758
	   "@SYNTAX=EFFECT(r,nper)\n"
759 760
	   "@DESCRIPTION="
	   "EFFECT calculates the effective interest rate from "
761
	   "a given nominal rate.\n\n"
762
	   "Effective interest rate is calculated using this formula:\n"
763
	   "\n"
764
           "    (1 + @r / @nper) ^ @nper - 1\n"
765 766
	   "\n"
	   "where:\n"
767
	   "\n"
768
	   "@r = nominal interest rate (stated in yearly terms)\n"
769 770 771 772
	   "@nper = number of periods used for compounding\n"
	   "\n"
	   "* If @rate < 0, EFFECT returns #NUM! error.\n"
	   "* If @nper <= 0, EFFECT returns #NUM! error.\n"
773
	   "\n"
774
	   "@EXAMPLES=\n"
775 776
	   "For example credit cards will list an APR (annual percentage "
	   "rate) which is a nominal interest rate."
777
	   "\n"
778 779 780
	   "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:"
781
	   "\n"
782 783
	   "=EFFECT(.19,12) and you would get .2075 or 20.75%. That is the "
	   "effective percentage you will pay on your loan."
784 785
	   "\n"
	   "@SEEALSO=NOMINAL")
786 787
	},
	{ GNM_FUNC_HELP_END }
788
};
789

Jody Goldberg's avatar
Jody Goldberg committed
790
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
791
gnumeric_effect (FunctionEvalInfo *ei, GnmValue const * const *argv)
792
{
793
	gnm_float rate = value_get_as_float (argv[0]);
794
	int nper = value_get_as_int (argv[1]);
795

796
	/* Rate or number of periods cannot be negative */
Morten Welinder's avatar
Morten Welinder committed
797
	if (rate < 0 || nper <= 0)
798
                return value_new_error_NUM (ei->pos);
799

Morten Welinder's avatar
Morten Welinder committed
800
        return value_new_float (pow1pm1 (rate / nper, nper));
801
}
802

803 804
/***************************************************************************/

805 806 807
static GnmFuncHelp const help_nominal[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=NOMINAL\n"
808
	   "@SYNTAX=NOMINAL(r,nper)\n"
809 810
	   "@DESCRIPTION="
	   "NOMINAL calculates the nominal interest rate from "
811
	   "a given effective rate.\n\n"
812 813
	   "Nominal interest rate is given by a formula:\n"
	   "\n"
814
           "@nper * (( 1 + @r ) ^ (1 / @nper) - 1 )"
815 816 817
	   "\n"
	   "where:\n"
	   "\n"
818
	   "@r = effective interest rate\n"
819 820 821 822
	   "@nper = number of periods used for compounding\n"
	   "\n"
	   "* If @rate < 0, NOMINAL returns #NUM! error.\n"
	   "* If @nper <= 0, NOMINAL returns #NUM! error.\n"
823
	   "\n"
824 825
	   "@EXAMPLES=\n"
	   "\n"
826
	   "@SEEALSO=EFFECT")
827 828
	},
	{ GNM_FUNC_HELP_END }
829 830
};

Jody Goldberg's avatar
Jody Goldberg committed
831
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
832
gnumeric_nominal (FunctionEvalInfo *ei, GnmValue const * const *argv)
833
{
834
	gnm_float rate = value_get_as_float (argv[0]);
835
	int nper = value_get_as_int (argv[1]);
836 837

	/* Rate or number of periods cannot be negative */
838
	if (rate < 0 || nper <= 0)
839
                return value_new_error_NUM (ei->pos);
840

Morten Welinder's avatar
Morten Welinder committed
841
        return value_new_float (nper * pow1pm1 (rate, 1.0 / nper));
842 843
}

844 845
/***************************************************************************/

846 847 848
static GnmFuncHelp const help_ispmt[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=ISPMT\n"
849 850
	   "@SYNTAX=ISPMT(rate,per,nper,pv)\n"
	   "@DESCRIPTION="
851
	   "ISPMT function returns the interest paid on a given period.\n"
852
	   "\n"
853
	   "* If @per < 1 or @per > @nper, ISPMT returns #NUM! error. "
854
	   "\n"
855 856
	   "@EXAMPLES=\n"
	   "\n"
857
	   "@SEEALSO=PV")
858 859
	},
	{ GNM_FUNC_HELP_END }
860 861
};

Jody Goldberg's avatar
Jody Goldberg committed
862
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
863
gnumeric_ispmt (FunctionEvalInfo *ei, GnmValue const * const *argv)
864
{
865
	gnm_float tmp;
866

867 868 869 870
	gnm_float rate = value_get_as_float (argv[0]);
	gnm_float per = value_get_as_int (argv[1]);
	gnm_float nper = value_get_as_int (argv[2]);
	gnm_float pv = value_get_as_float (argv[3]);
871

872 873 874 875 876
	/*
	 * It seems that with 20 periods, a period number of 20.99 is
	 * valid in XL.
	 */
	if (per < 1 || per >= nper + 1)
877
                return value_new_error_NUM (ei->pos);
878

Morten Welinder's avatar
Morten Welinder committed
879
	tmp = -pv * rate;
880 881 882 883

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

884 885
/***************************************************************************/

886 887 888
static GnmFuncHelp const help_db[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=DB\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
889
	   "@SYNTAX=DB(cost,salvage,life,period[,month])\n"
890
	   "@DESCRIPTION="
891
	   "DB calculates the depreciation of an asset for a given period "
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
892
	   "using the fixed-declining balance method.  @cost is the "
893
	   "initial value of the asset.  @salvage is the value after the "
894 895
	   "depreciation.\n"
	   "\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
896 897
	   "@life is the number of periods overall.  @period is the period "
	   "for which you want the depreciation to be calculated.  @month "
898 899 900 901 902 903
	   "is the number of months in the first year of depreciation.\n"
	   "\n"
	   "* If @month is omitted, it is assumed to be 12.\n"
	   "* If @cost = 0, DB returns #NUM! error.\n"
	   "* If @life <= 0, DB returns #NUM! error.\n"
	   "* If @salvage / @cost < 0, DB returns #NUM! error.\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
904
	   "\n"
905 906
	   "@EXAMPLES=\n"
	   "\n"
Jody Goldberg's avatar
Jody Goldberg committed
907
	   "@SEEALSO=DDB,SLN,SYD")
908 909
	},
	{ GNM_FUNC_HELP_END }
910 911
};

Jody Goldberg's avatar
Jody Goldberg committed
912
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
913
gnumeric_db (FunctionEvalInfo *ei, GnmValue const * const *argv)
914
{
915 916 917
	gnm_float rate;
	gnm_float cost, salvage, life, period, month;
	gnm_float total;
918
	int        i;
919

920
	cost    = value_get_as_float (argv[0]);
921
	salvage = value_get_as_float (argv[1]);
922 923 924
	life    = value_get_as_float (argv[2]);
	period  = value_get_as_float (argv[3]);
	month   = argv[4] ? value_get_as_float (argv[4]) : 12;
925

Morten Welinder's avatar
Morten Welinder committed
926 927
	/* The third disjunct is a bit of a guess -- MW.  */
	if (cost == 0 || life <= 0 || salvage / cost < 0)
928
	        return value_new_error_NUM (ei->pos);
Dom Lachowicz's avatar
Dom Lachowicz committed
929

930
	rate  = 1 - gnm_pow ((salvage / cost), (1 / life));
931
	rate *= 1000;
932
	rate  = gnm_floor (rate + 0.5) / 1000;
933 934 935 936 937

	total = cost * rate * month / 12;

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

Morten Welinder's avatar
Morten Welinder committed
939 940
	for (i = 1; i < life; i++)
	       if (i == period - 1)
941 942 943
		       return value_new_float ((cost - total) * rate);
	       else
		       total += (cost - total) * rate;
944

945 946
	return value_new_float (((cost - total) * rate * (12 - month)) / 12);
}
947

948 949
/***************************************************************************/

950 951 952
static GnmFuncHelp const help_ddb[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=DDB\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
953 954 955 956
	   "@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 "
957 958 959
	   "method you specify.\n"
	   "\n"
	   "@cost is the initial value of the asset, "
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
960 961 962
	   "@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 "
963 964 965 966 967
	   "which the balance declines.\n"
	   "\n"
	   "* If @factor is omitted, it is assumed to be two "
	   "(double-declining balance method).\n"
	   "* If @life <= 0, DDB returns #NUM! error.\n"
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
968
	   "\n"
969 970
	   "@EXAMPLES=\n"
	   "\n"
Jody Goldberg's avatar
Jody Goldberg committed
971
	   "@SEEALSO=SLN,SYD")
972 973
	},
	{ GNM_FUNC_HELP_END }
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
974 975
};

Jody Goldberg's avatar
Jody Goldberg committed
976
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
977
gnumeric_ddb (FunctionEvalInfo *ei, GnmValue const * const *argv)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
978
{
979 980
	gnm_float cost, salvage, life, period, factor;
	gnm_float total;
981
	int        i;
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
982

983
	cost    = value_get_as_float (argv[0]);
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
984
	salvage = value_get_as_float (argv[1]);
985 986 987
	life    = value_get_as_float (argv[2]);
	period  = value_get_as_float (argv[3]);
	factor  = argv[4] ? value_get_as_float (argv[4]) : 2;
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
988

Dom Lachowicz's avatar
Dom Lachowicz committed
989
	if (life <= 0)
990
	        return value_new_error_NUM (ei->pos);
Dom Lachowicz's avatar
Dom Lachowicz committed
991

Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
992
	total = 0;
Morten Welinder's avatar
Morten Welinder committed
993
	for (i = 0; i < life - 1; i++) {
994
	        gnm_float period_dep = (cost - total) * (factor / life);
Morten Welinder's avatar
Morten Welinder committed
995
		if (period - 1 == i)
Jukka-Pekka Iivonen's avatar
Jukka-Pekka Iivonen committed
996 997 998 999 1000 1001 1002 1003
		        return value_new_float (period_dep);
		else
		        total += period_dep;
	}

	return value_new_float (cost - total - salvage);
}

1004 1005
/***************************************************************************/

1006 1007 1008
static GnmFuncHelp const help_sln[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=SLN\n"
1009
	   "@SYNTAX=SLN(cost,salvage_value,life)\n"
1010
	   "@DESCRIPTION="
1011
	   "SLN function will determine the straight line depreciation "
1012 1013
	   "of an asset for a single period.\n"
	   "\n"
1014
	   "The formula is:\n"
1015
	   "\n"
Jody Goldberg's avatar
Jody Goldberg committed
1016
	   "Depreciation expense = ( @cost - @salvage_value ) / @life\n"
1017
	   "\n"
1018
	   "@cost is the cost of an asset when acquired (market value).\n"
1019
	   "@salvage_value is the amount you get when asset is sold at the end "
Jody Goldberg's avatar
Jody Goldberg committed
1020
	   "of the asset's useful life.\n"
1021
	   "@life is the anticipated life of an asset.\n"
1022 1023
	   "\n"
	   "* If @life <= 0, SLN returns #NUM! error.\n"
1024
	   "\n"
1025
	   "@EXAMPLES=\n"
1026 1027 1028 1029 1030 1031 1032 1033
	   "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."
1034
	   "\n"
1035
	   "@SEEALSO=SYD")
1036 1037
	},
	{ GNM_FUNC_HELP_END }
1038 1039 1040
};


Jody Goldberg's avatar
Jody Goldberg committed
1041
static GnmValue *
Jody Goldberg's avatar
Jody Goldberg committed
1042
gnumeric_sln (FunctionEvalInfo *ei, GnmValue const * const *argv)
1043
{
1044
	gnm_float cost,salvage_value,life;
1045

1046
	cost          = value_get_as_float (argv[0]);
1047
	salvage_value = value_get_as_float (argv[1]);
1048
	life          = value_get_as_float (argv[2]);
1049 1050

	/* Life of an asset cannot be negative */
1051
	if (life <= 0)
1052
	        return value_new_error_NUM (ei->pos);
1053

1054
        return value_new_float ((cost - salvage_value) / life);
1055 1056
}

1057 1058
/***************************************************************************/

1059 1060 1061
static GnmFuncHelp const help_syd[] = {
	{ GNM_FUNC_HELP_OLD,
	F_("@FUNCTION=SYD\n"
1062
	   "@SYNTAX=SYD(cost,salvage_value,life,period)\n"
1063
	   "@DESCRIPTION="
1064
	   "SYD function calculates the sum-of-years digits depreciation "
1065 1066 1067 1068 1069
	   "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 "
1070
	   "number of periods (typically years) over which the asset is "
1071
	   "depreciated.\n"
1072
	   "\n"
1073
	   "The Formula used for sum-of-years digits depreciation is:\n"
1074
	   "\n"
Jody Goldberg's avatar
Jody Goldberg committed
1075
	   "Depreciation expense =\n\n\t ( @cost - @salvage_value ) * "
1076
	   "(@life - @period + 1) * 2 / @life * (@life + 1).\n"
1077
	   "\n"
1078 1079
	   "@cost is the cost of an asset when acquired (market value).\n"
	   "@salvage_value is the amount you get when asset sold at the end of "
1080
	   "its useful life.\n"
1081 1082
	   "@life is the anticipated life of an asset.\n"
	   "@period is the period for which we need the expense.\n"
1083 1084
	   "\n"
	   "* If @life <= 0, SYD returns #NUM! error.\n"
1085
	   "\n"
1086
	   "@EXAMPLES=\n"
1087
	   "For example say a company purchases a new computer for $5000 "