148
calculate_interest_part (gnm_float pv, gnm_float pmt,
149
gnm_float rate, gnm_float per)
151
return -(pv * pow1p (rate, per) * rate +
152
pmt * pow1pm1 (rate, per));
156
141
calculate_pmt (gnm_float rate, gnm_float nper, gnm_float pv, gnm_float fv,
166
151
return ((-pv * pvif - fv ) / ((1.0 + rate * type) * fvifa));
155
calculate_ipmt (gnm_float rate, gnm_float per, gnm_float nper,
156
gnm_float pv, gnm_float fv, int type)
158
gnm_float pmt = calculate_pmt (rate, nper, pv, fv, /*type*/ 0);
159
gnm_float ipmt = -(pv * pow1p (rate, per - 1) * rate +
160
pmt * pow1pm1 (rate, per - 1));
162
return (type == 0) ? ipmt : ipmt / (1 + rate);
169
165
/***************************************************************************/
171
167
/* Returns the number of days between issue date and maturity date
265
261
GnmCouponConvention const *conv)
268
coup_cd (&date, settlement, maturity, conv->freq, conv->eom, FALSE);
264
go_coup_cd (&date, settlement, maturity, conv->freq, conv->eom, FALSE);
269
265
return datetime_g_to_serial (&date, conv->date_conv);
274
270
GnmCouponConvention const *conv)
277
coup_cd (&date, settlement, maturity, conv->freq, conv->eom, TRUE);
273
go_coup_cd (&date, settlement, maturity, conv->freq, conv->eom, TRUE);
278
274
return datetime_g_to_serial (&date, conv->date_conv);
285
281
gnm_float a, d, e, sum, den, basem1, exponent, first_term, last_term;
288
a = coupdaybs (settlement, maturity, conv);
289
d = coupdaysnc (settlement, maturity, conv);
290
e = coupdays (settlement, maturity, conv);
284
a = go_coupdaybs (settlement, maturity, conv);
285
d = go_coupdaysnc (settlement, maturity, conv);
286
e = go_coupdays (settlement, maturity, conv);
291
287
n = coupnum (settlement, maturity, conv);
293
289
den = 100.0 * rate / conv->freq;
315
311
***********************************************************************/
317
313
static GnmValue *
318
func_coup (FunctionEvalInfo *ei, GnmValue const * const *argv,
314
func_coup (GnmFuncEvalInfo *ei, GnmValue const * const *argv,
319
315
gnm_float (coup_fn) (GDate const *settle, GDate const *mat,
320
316
GnmCouponConvention const *conv))
392
388
static GnmValue *
393
gnumeric_accrint (FunctionEvalInfo *ei, GnmValue const * const *argv)
389
gnumeric_accrint (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
395
391
GDate issue, first_interest, settlement;
396
392
gnm_float rate, a, d, par, freq;
471
467
static GnmValue *
472
gnumeric_accrintm (FunctionEvalInfo *ei, GnmValue const * const *argv)
468
gnumeric_accrintm (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
474
470
gnm_float rate, a, d, par;
536
532
static GnmValue *
537
gnumeric_intrate (FunctionEvalInfo *ei, GnmValue const * const *argv)
533
gnumeric_intrate (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
539
535
gnm_float investment, redemption, a, d;
595
591
static GnmValue *
596
gnumeric_received (FunctionEvalInfo *ei, GnmValue const * const *argv)
592
gnumeric_received (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
598
594
gnm_float investment, discount, a, d, n;
656
652
static GnmValue *
657
gnumeric_pricedisc (FunctionEvalInfo *ei, GnmValue const * const *argv)
653
gnumeric_pricedisc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
659
655
gnm_float discount, redemption, a, d;
712
708
static GnmValue *
713
gnumeric_pricemat (FunctionEvalInfo *ei, GnmValue const * const *argv)
709
gnumeric_pricemat (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
715
711
gnm_float discount, yield, a, b, dsm, dim, n;
775
771
static GnmValue *
776
gnumeric_disc (FunctionEvalInfo *ei, GnmValue const * const *argv)
772
gnumeric_disc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
778
774
gnm_float par, redemption, dsm, b;
834
830
static GnmValue *
835
gnumeric_effect (FunctionEvalInfo *ei, GnmValue const * const *argv)
831
gnumeric_effect (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
837
833
gnm_float rate = value_get_as_float (argv[0]);
838
834
gnm_float nper = gnm_floor (value_get_as_float (argv[1]));
875
871
static GnmValue *
876
gnumeric_nominal (FunctionEvalInfo *ei, GnmValue const * const *argv)
872
gnumeric_nominal (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
878
874
gnm_float rate = value_get_as_float (argv[0]);
879
875
gnm_float nper = gnm_floor (value_get_as_float (argv[1]));
956
952
static GnmValue *
957
gnumeric_db (FunctionEvalInfo *ei, GnmValue const * const *argv)
953
gnumeric_db (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
960
956
gnm_float cost, salvage, life, period, month;
1020
1016
static GnmValue *
1021
gnumeric_ddb (FunctionEvalInfo *ei, GnmValue const * const *argv)
1017
gnumeric_ddb (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1023
1019
gnm_float cost, salvage, life, period, factor;
1024
1020
gnm_float total;
1085
1081
static GnmValue *
1086
gnumeric_sln (FunctionEvalInfo *ei, GnmValue const * const *argv)
1082
gnumeric_sln (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1088
1084
gnm_float cost,salvage_value,life;
1143
1139
static GnmValue *
1144
gnumeric_syd (FunctionEvalInfo *ei, GnmValue const * const *argv)
1140
gnumeric_syd (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1146
1142
gnm_float cost, salvage_value, life, period;
1185
1181
static GnmValue *
1186
gnumeric_dollarde (FunctionEvalInfo *ei, GnmValue const * const *argv)
1182
gnumeric_dollarde (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1188
1184
gnm_float x = value_get_as_float (argv[0]);
1189
1185
gnm_float f = gnm_floor (value_get_as_float (argv[1]));
1241
1237
static GnmValue *
1242
gnumeric_dollarfr (FunctionEvalInfo *ei, GnmValue const * const *argv)
1238
gnumeric_dollarfr (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1244
1240
gnm_float x = value_get_as_float (argv[0]);
1245
1241
gnm_float f = gnm_floor (value_get_as_float (argv[1]));
1292
1288
static GnmValue *
1293
gnumeric_mirr (FunctionEvalInfo *ei, GnmValue const * const *argv)
1289
gnumeric_mirr (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1295
1291
gnm_float frate, rrate, npv_neg, npv_pos;
1296
1292
gnm_float *values = NULL, res;
1363
1359
static GnmValue *
1364
gnumeric_tbilleq (FunctionEvalInfo *ei, GnmValue const * const *argv)
1360
gnumeric_tbilleq (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1366
1362
gnm_float settlement, maturity, discount;
1367
1363
gnm_float dsm, divisor;
1413
1409
static GnmValue *
1414
gnumeric_tbillprice (FunctionEvalInfo *ei, GnmValue const * const *argv)
1410
gnumeric_tbillprice (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1416
1412
gnm_float settlement, maturity, discount;
1417
1413
gnm_float res, dsm;
1459
1455
static GnmValue *
1460
gnumeric_tbillyield (FunctionEvalInfo *ei, GnmValue const * const *argv)
1456
gnumeric_tbillyield (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1462
1458
gnm_float settlement, maturity, pr;
1463
1459
gnm_float res, dsm;
1487
1483
"@DESCRIPTION="
1488
1484
"RATE calculates the rate of an investment.\n"
1486
"* If @pmt is ommitted it defaults to 0\n"
1490
1487
"* If @nper <= 0, RATE returns #NUM! error.\n"
1491
1488
"* If @type != 0 and @type != 1, RATE returns #VALUE! error.\n"
1542
1539
gnm_float rate0;
1544
1541
udata.nper = value_get_as_int (argv[0]);
1545
udata.pmt = value_get_as_float (argv[1]);
1542
/* YES ZERO, it's sick but it's XL compatible */
1543
udata.pmt = argv[1] ? value_get_as_float (argv[1]) : 0.0;
1546
1544
udata.pv = value_get_as_float (argv[2]);
1547
1545
udata.fv = argv[3] ? value_get_as_float (argv[3]) : 0.0;
1548
1546
udata.type = value_get_paytype (argv[4]);
1753
1751
static GnmValue *
1754
gnumeric_pv (FunctionEvalInfo *ei, GnmValue const * const *argv)
1752
gnumeric_pv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1756
1754
gnm_float rate = value_get_as_float (argv[0]);
1757
1755
gnm_float nper = value_get_as_float (argv[1]);
1817
1815
static GnmValue *
1818
gnumeric_npv (FunctionEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
1816
gnumeric_npv (GnmFuncEvalInfo *ei, int argc, GnmExprConstPtr const *argv)
1820
1818
return float_range_function (argc, argv, ei,
1850
1848
static GnmValue *
1851
gnumeric_xnpv (FunctionEvalInfo *ei, GnmValue const * const *argv)
1849
gnumeric_xnpv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
1853
1851
gnm_float rate, *payments = NULL, *dates = NULL;
2034
2032
static GnmValue *
2035
gnumeric_fv (FunctionEvalInfo *ei, GnmValue const * const *argv)
2033
gnumeric_fv (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2037
2035
gnm_float rate = value_get_as_float (argv[0]);
2038
2036
gnm_float nper = value_get_as_float (argv[1]);
2082
2080
static GnmValue *
2083
gnumeric_pmt (FunctionEvalInfo *ei, GnmValue const * const *argv)
2081
gnumeric_pmt (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2085
2083
gnm_float rate = value_get_as_float (argv[0]);
2086
2084
gnm_float nper = value_get_as_float (argv[1]);
2126
2124
static GnmValue *
2127
gnumeric_ipmt (FunctionEvalInfo *ei, GnmValue const * const *argv)
2125
gnumeric_ipmt (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2129
2127
gnm_float rate = value_get_as_float (argv[0]);
2130
2128
gnm_float per = value_get_as_float (argv[1]);
2143
2141
if (!is_valid_paytype (type))
2144
2142
return value_new_error_VALUE (ei->pos);
2147
gnm_float pmt = calculate_pmt (rate, nper, pv, fv, type);
2148
gnm_float ipmt = calculate_interest_part (pv, pmt, rate, per - 1);
2150
return value_new_float (ipmt);
2144
return value_new_float (calculate_ipmt (rate, per, nper, pv, fv, type));
2154
2147
/***************************************************************************/
2183
2176
static GnmValue *
2184
gnumeric_ppmt (FunctionEvalInfo *ei, GnmValue const * const *argv)
2177
gnumeric_ppmt (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2186
2179
gnm_float rate = value_get_as_float (argv[0]);
2187
2180
gnm_float per = value_get_as_float (argv[1]);
2204
2197
gnm_float pmt = calculate_pmt (rate, nper, pv, fv, type);
2205
gnm_float ipmt = calculate_interest_part (pv, pmt, rate, per - 1);
2198
gnm_float ipmt = calculate_ipmt (rate, per, nper, pv, fv, type);
2206
2199
return value_new_float (pmt - ipmt);
2399
2392
static GnmValue *
2400
gnumeric_fvschedule (FunctionEvalInfo *ei, GnmValue const * const *argv)
2393
gnumeric_fvschedule (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2402
2395
gnm_float pv, *schedule = NULL;
2403
2396
GnmValue *result = NULL;
2521
2514
static GnmValue *
2522
gnumeric_euro (FunctionEvalInfo *ei, GnmValue const * const *argv)
2515
gnumeric_euro (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2524
2517
char const *str = value_peek_string (argv[0]);
2525
2518
gnm_float v = one_euro (str);
2570
2563
static GnmValue *
2571
gnumeric_euroconvert (FunctionEvalInfo *ei, GnmValue const * const *argv)
2564
gnumeric_euroconvert (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2573
2566
gnm_float c1 = one_euro (value_peek_string (argv[1]));
2574
2567
gnm_float c2 = one_euro (value_peek_string (argv[2]));
2616
2609
static GnmValue *
2617
gnumeric_price (FunctionEvalInfo *ei, GnmValue const * const *argv)
2610
gnumeric_price (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2619
2612
GDate settlement, maturity;
2620
2613
/* gnm_float a, d, e, n; */
2726
2719
n = coupnum (&udata.settlement, &udata.maturity, &udata.conv);
2727
2720
if (n <= 1.0) {
2728
gnm_float a = coupdaybs (&udata.settlement, &udata.maturity,
2730
gnm_float d = coupdaysnc (&udata.settlement, &udata.maturity,
2732
gnm_float e = coupdays (&udata.settlement, &udata.maturity,
2721
gnm_float a = go_coupdaybs (&udata.settlement, &udata.maturity,
2723
gnm_float d = go_coupdaysnc (&udata.settlement, &udata.maturity,
2725
gnm_float e = go_coupdays (&udata.settlement, &udata.maturity,
2735
2728
gnm_float coeff = udata.conv.freq * e / d;
2807
2800
static GnmValue *
2808
gnumeric_yielddisc (FunctionEvalInfo *ei, GnmValue const * const *argv)
2801
gnumeric_yielddisc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2810
2803
GDate settlement, maturity;
2811
2804
gnm_float fPrice, fRedemp;
2870
2863
static GnmValue *
2871
gnumeric_yieldmat (FunctionEvalInfo *ei, GnmValue const * const *argv)
2864
gnumeric_yieldmat (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
2873
2866
GDate nSettle, nMat, nIssue;
2874
2867
gnm_float fRate, fPrice;
2932
2925
GDate next_coupon, prev_coupon;
2935
coup_cd (&next_coupon, d1, d3, conv->freq, conv->eom, TRUE);
2936
coup_cd (&prev_coupon, d1, d3, conv->freq, conv->eom, FALSE);
2928
go_coup_cd (&next_coupon, d1, d3, conv->freq, conv->eom, TRUE);
2929
go_coup_cd (&prev_coupon, d1, d3, conv->freq, conv->eom, FALSE);
2938
2931
if (g_date_compare (&next_coupon, d2) >= 0)
2939
2932
return days_between_basis (d1, d2, conv->basis) /
2940
coupdays (&prev_coupon, &next_coupon, conv);
2933
go_coupdays (&prev_coupon, &next_coupon, conv);
2942
2935
res = days_between_basis (d1, &next_coupon, conv->basis) /
2943
coupdays (&prev_coupon, &next_coupon, conv);
2936
go_coupdays (&prev_coupon, &next_coupon, conv);
2945
2938
prev_coupon = next_coupon;
2946
2939
g_date_add_months (&next_coupon, 12 / conv->freq);
2947
2940
if (g_date_compare (&next_coupon, d2) >= 0) {
2948
2941
res += days_between_basis (&prev_coupon, d2, conv->basis) /
2949
coupdays (&prev_coupon, &next_coupon, conv);
2942
go_coupdays (&prev_coupon, &next_coupon, conv);
2963
2956
gnm_float a = days_between_basis (issue, settlement, conv->basis);
2964
2957
gnm_float ds = days_between_basis (settlement, first_coupon, conv->basis);
2965
2958
gnm_float df = days_between_basis (issue, first_coupon, conv->basis);
2966
gnm_float e = coupdays (settlement, maturity, conv);
2959
gnm_float e = go_coupdays (settlement, maturity, conv);
2967
2960
int n = (int)coupnum (settlement, maturity, conv);
2968
2961
gnm_float scale = 100.0 * rate / conv->freq;
2969
2962
gnm_float f = 1.0 + yield / conv->freq;
2987
2980
g_date_add_months (&d, 12 / conv->freq);
2988
2981
if (g_date_compare (&d, maturity) >= 0) {
2989
2982
n += (int)gnm_ceil (days_between_basis (&prev_date, maturity, conv->basis) /
2990
coupdays (&prev_date, &d, conv))
2983
go_coupdays (&prev_date, &d, conv))
3012
3005
static GnmValue *
3013
gnumeric_oddfprice (FunctionEvalInfo *ei, GnmValue const * const *argv)
3006
gnumeric_oddfprice (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3015
3008
GDate settlement, maturity, issue, first_coupon;
3016
3009
gnm_float rate, yield, redemption;
3219
3212
static GnmValue *
3220
gnumeric_oddlprice (FunctionEvalInfo *ei, GnmValue const * const *argv)
3213
gnumeric_oddlprice (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3222
3215
GDate settlement, maturity, last_interest;
3223
3216
gnm_float rate, yield, redemption;
3313
3306
static GnmValue *
3314
gnumeric_oddlyield (FunctionEvalInfo *ei, GnmValue const * const *argv)
3307
gnumeric_oddlyield (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3316
3309
GDate settlement, maturity, last_interest;
3317
3310
gnm_float rate, price, redemption;
3389
3382
static GnmValue *
3390
gnumeric_amordegrc (FunctionEvalInfo *ei, GnmValue const * const *argv)
3383
gnumeric_amordegrc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3392
3385
GDate nDate, nFirstPer;
3393
3386
gnm_float fRestVal, fRate, fCost;
3453
3446
static GnmValue *
3454
gnumeric_amorlinc (FunctionEvalInfo *ei, GnmValue const * const *argv)
3447
gnumeric_amorlinc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3456
3449
GDate nDate, nFirstPer;
3457
3450
gnm_float fCost, fRestVal, fRate;
3520
3513
static GnmValue *
3521
gnumeric_coupdaybs (FunctionEvalInfo *ei, GnmValue const * const *argv)
3514
gnumeric_coupdaybs (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3523
return func_coup (ei, argv, coupdaybs);
3516
return func_coup (ei, argv, go_coupdaybs);
3526
3519
/***************************************************************************/
3569
3562
static GnmValue *
3570
gnumeric_coupdays (FunctionEvalInfo *ei, GnmValue const * const *argv)
3563
gnumeric_coupdays (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3572
return func_coup (ei, argv, coupdays);
3565
return func_coup (ei, argv, go_coupdays);
3575
3568
/***************************************************************************/
3617
3610
static GnmValue *
3618
gnumeric_coupdaysnc (FunctionEvalInfo *ei, GnmValue const * const *argv)
3611
gnumeric_coupdaysnc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3620
return func_coup (ei, argv, coupdaysnc);
3613
return func_coup (ei, argv, go_coupdaysnc);
3623
3616
/***************************************************************************/
3664
3657
static GnmValue *
3665
gnumeric_coupncd (FunctionEvalInfo *ei, GnmValue const * const *argv)
3658
gnumeric_coupncd (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3667
3660
GnmValue *res = func_coup (ei, argv, coupncd);
3668
3661
value_set_fmt (res, go_format_default_date ());
3714
3707
static GnmValue *
3715
gnumeric_couppcd (FunctionEvalInfo *ei, GnmValue const * const *argv)
3708
gnumeric_couppcd (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3717
3710
GnmValue *res = func_coup (ei, argv, couppcd);
3718
3711
value_set_fmt (res, go_format_default_date ());
3760
3753
static GnmValue *
3761
gnumeric_coupnum (FunctionEvalInfo *ei, GnmValue const * const *argv)
3754
gnumeric_coupnum (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3763
3756
return func_coup (ei, argv, coupnum);
3791
3784
static GnmValue *
3792
gnumeric_cumipmt (FunctionEvalInfo *ei, GnmValue const * const *argv)
3785
gnumeric_cumipmt (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3794
3787
gnm_float fRate, fVal;
3795
3788
gint nNumPeriods, nStartPer, nEndPer, nPayType;
3844
3837
static GnmValue *
3845
gnumeric_cumprinc (FunctionEvalInfo *ei, GnmValue const * const *argv)
3838
gnumeric_cumprinc (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3847
3840
gnm_float fRate, fVal;
3848
3841
gint nNumPeriods, nStartPer, nEndPer, nPayType;
3957
3950
static GnmValue *
3958
gnumeric_vdb (FunctionEvalInfo *ei, GnmValue const * const *argv)
3951
gnumeric_vdb (GnmFuncEvalInfo *ei, GnmValue const * const *argv)
3960
3953
gnm_float cost, salvage, life, factor, start_period, end_period;
3961
3954
gboolean bflag;