Subversion Repositories eFlore/Applications.cel

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2388 jpm 1
<?php
2
/**
3
 * PHPExcel
4
 *
5
 * Copyright (c) 2006 - 2013 PHPExcel
6
 *
7
 * This library is free software; you can redistribute it and/or
8
 * modify it under the terms of the GNU Lesser General Public
9
 * License as published by the Free Software Foundation; either
10
 * version 2.1 of the License, or (at your option) any later version.
11
 *
12
 * This library is distributed in the hope that it will be useful,
13
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15
 * Lesser General Public License for more details.
16
 *
17
 * You should have received a copy of the GNU Lesser General Public
18
 * License along with this library; if not, write to the Free Software
19
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
20
 *
21
 * @category	PHPExcel
22
 * @package		PHPExcel_Calculation
23
 * @copyright	Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
24
 * @license		http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
25
 * @version		##VERSION##, ##DATE##
26
 */
27
 
28
 
29
/** PHPExcel root directory */
30
if (!defined('PHPEXCEL_ROOT')) {
31
	/**
32
	 * @ignore
33
	 */
34
	define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35
	require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36
}
37
 
38
 
39
/** FINANCIAL_MAX_ITERATIONS */
40
define('FINANCIAL_MAX_ITERATIONS', 128);
41
 
42
/** FINANCIAL_PRECISION */
43
define('FINANCIAL_PRECISION', 1.0e-08);
44
 
45
 
46
/**
47
 * PHPExcel_Calculation_Financial
48
 *
49
 * @category	PHPExcel
50
 * @package		PHPExcel_Calculation
51
 * @copyright	Copyright (c) 2006 - 2013 PHPExcel (http://www.codeplex.com/PHPExcel)
52
 */
53
class PHPExcel_Calculation_Financial {
54
 
55
	/**
56
	 * _lastDayOfMonth
57
	 *
58
	 * Returns a boolean TRUE/FALSE indicating if this date is the last date of the month
59
	 *
60
	 * @param	DateTime	$testDate	The date for testing
61
	 * @return	boolean
62
	 */
63
	private static function _lastDayOfMonth($testDate)
64
	{
65
		return ($testDate->format('d') == $testDate->format('t'));
66
	}	//	function _lastDayOfMonth()
67
 
68
 
69
	/**
70
	 * _firstDayOfMonth
71
	 *
72
	 * Returns a boolean TRUE/FALSE indicating if this date is the first date of the month
73
	 *
74
	 * @param	DateTime	$testDate	The date for testing
75
	 * @return	boolean
76
	 */
77
	private static function _firstDayOfMonth($testDate)
78
	{
79
		return ($testDate->format('d') == 1);
80
	}	//	function _firstDayOfMonth()
81
 
82
 
83
	private static function _coupFirstPeriodDate($settlement, $maturity, $frequency, $next)
84
	{
85
		$months = 12 / $frequency;
86
 
87
		$result = PHPExcel_Shared_Date::ExcelToPHPObject($maturity);
88
		$eom = self::_lastDayOfMonth($result);
89
 
90
		while ($settlement < PHPExcel_Shared_Date::PHPToExcel($result)) {
91
			$result->modify('-'.$months.' months');
92
		}
93
		if ($next) {
94
			$result->modify('+'.$months.' months');
95
		}
96
 
97
		if ($eom) {
98
			$result->modify('-1 day');
99
		}
100
 
101
		return PHPExcel_Shared_Date::PHPToExcel($result);
102
	}	//	function _coupFirstPeriodDate()
103
 
104
 
105
	private static function _validFrequency($frequency)
106
	{
107
		if (($frequency == 1) || ($frequency == 2) || ($frequency == 4)) {
108
			return true;
109
		}
110
		if ((PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) &&
111
			(($frequency == 6) || ($frequency == 12))) {
112
			return true;
113
		}
114
		return false;
115
	}	//	function _validFrequency()
116
 
117
 
118
	/**
119
	 * _daysPerYear
120
	 *
121
	 * Returns the number of days in a specified year, as defined by the "basis" value
122
	 *
123
	 * @param	integer		$year	The year against which we're testing
124
	 * @param   integer		$basis	The type of day count:
125
	 *									0 or omitted US (NASD)	360
126
	 *									1						Actual (365 or 366 in a leap year)
127
	 *									2						360
128
	 *									3						365
129
	 *									4						European 360
130
	 * @return	integer
131
	 */
132
	private static function _daysPerYear($year, $basis=0)
133
	{
134
		switch ($basis) {
135
			case 0 :
136
			case 2 :
137
			case 4 :
138
				$daysPerYear = 360;
139
				break;
140
			case 3 :
141
				$daysPerYear = 365;
142
				break;
143
			case 1 :
144
				$daysPerYear = (PHPExcel_Calculation_DateTime::_isLeapYear($year)) ? 366 : 365;
145
				break;
146
			default	:
147
				return PHPExcel_Calculation_Functions::NaN();
148
		}
149
		return $daysPerYear;
150
	}	//	function _daysPerYear()
151
 
152
 
153
	private static function _interestAndPrincipal($rate=0, $per=0, $nper=0, $pv=0, $fv=0, $type=0)
154
	{
155
		$pmt = self::PMT($rate, $nper, $pv, $fv, $type);
156
		$capital = $pv;
157
		for ($i = 1; $i<= $per; ++$i) {
158
			$interest = ($type && $i == 1) ? 0 : -$capital * $rate;
159
			$principal = $pmt - $interest;
160
			$capital += $principal;
161
		}
162
		return array($interest, $principal);
163
	}	//	function _interestAndPrincipal()
164
 
165
 
166
	/**
167
	 * ACCRINT
168
	 *
169
	 * Returns the accrued interest for a security that pays periodic interest.
170
	 *
171
	 * Excel Function:
172
	 *		ACCRINT(issue,firstinterest,settlement,rate,par,frequency[,basis])
173
	 *
174
	 * @access	public
175
	 * @category Financial Functions
176
	 * @param	mixed	$issue			The security's issue date.
177
	 * @param	mixed	$firstinterest	The security's first interest date.
178
	 * @param	mixed	$settlement		The security's settlement date.
179
	 *									The security settlement date is the date after the issue date
180
	 *									when the security is traded to the buyer.
181
	 * @param	float	$rate			The security's annual coupon rate.
182
	 * @param	float	$par			The security's par value.
183
	 *									If you omit par, ACCRINT uses $1,000.
184
	 * @param	integer	$frequency		the number of coupon payments per year.
185
	 *									Valid frequency values are:
186
	 *										1	Annual
187
	 *										2	Semi-Annual
188
	 *										4	Quarterly
189
	 *									If working in Gnumeric Mode, the following frequency options are
190
	 *									also available
191
	 *										6	Bimonthly
192
	 *										12	Monthly
193
	 * @param	integer	$basis			The type of day count to use.
194
	 *										0 or omitted	US (NASD) 30/360
195
	 *										1				Actual/actual
196
	 *										2				Actual/360
197
	 *										3				Actual/365
198
	 *										4				European 30/360
199
	 * @return	float
200
	 */
201
	public static function ACCRINT($issue, $firstinterest, $settlement, $rate, $par=1000, $frequency=1, $basis=0)
202
	{
203
		$issue		= PHPExcel_Calculation_Functions::flattenSingleValue($issue);
204
		$firstinterest	= PHPExcel_Calculation_Functions::flattenSingleValue($firstinterest);
205
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
206
		$rate		= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
207
		$par		= (is_null($par))		? 1000 :	PHPExcel_Calculation_Functions::flattenSingleValue($par);
208
		$frequency	= (is_null($frequency))	? 1	: 		PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
209
		$basis		= (is_null($basis))		? 0	:		PHPExcel_Calculation_Functions::flattenSingleValue($basis);
210
 
211
		//	Validate
212
		if ((is_numeric($rate)) && (is_numeric($par))) {
213
			$rate	= (float) $rate;
214
			$par	= (float) $par;
215
			if (($rate <= 0) || ($par <= 0)) {
216
				return PHPExcel_Calculation_Functions::NaN();
217
			}
218
			$daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
219
			if (!is_numeric($daysBetweenIssueAndSettlement)) {
220
				//	return date error
221
				return $daysBetweenIssueAndSettlement;
222
			}
223
 
224
			return $par * $rate * $daysBetweenIssueAndSettlement;
225
		}
226
		return PHPExcel_Calculation_Functions::VALUE();
227
	}	//	function ACCRINT()
228
 
229
 
230
	/**
231
	 * ACCRINTM
232
	 *
233
	 * Returns the accrued interest for a security that pays interest at maturity.
234
	 *
235
	 * Excel Function:
236
	 *		ACCRINTM(issue,settlement,rate[,par[,basis]])
237
	 *
238
	 * @access	public
239
	 * @category Financial Functions
240
	 * @param	mixed	issue		The security's issue date.
241
	 * @param	mixed	settlement	The security's settlement (or maturity) date.
242
	 * @param	float	rate		The security's annual coupon rate.
243
	 * @param	float	par			The security's par value.
244
	 *									If you omit par, ACCRINT uses $1,000.
245
	 * @param	integer	basis		The type of day count to use.
246
	 *										0 or omitted	US (NASD) 30/360
247
	 *										1				Actual/actual
248
	 *										2				Actual/360
249
	 *										3				Actual/365
250
	 *										4				European 30/360
251
	 * @return	float
252
	 */
253
	public static function ACCRINTM($issue, $settlement, $rate, $par=1000, $basis=0) {
254
		$issue		= PHPExcel_Calculation_Functions::flattenSingleValue($issue);
255
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
256
		$rate		= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
257
		$par		= (is_null($par))	? 1000 :	PHPExcel_Calculation_Functions::flattenSingleValue($par);
258
		$basis		= (is_null($basis))	? 0 :		PHPExcel_Calculation_Functions::flattenSingleValue($basis);
259
 
260
		//	Validate
261
		if ((is_numeric($rate)) && (is_numeric($par))) {
262
			$rate	= (float) $rate;
263
			$par	= (float) $par;
264
			if (($rate <= 0) || ($par <= 0)) {
265
				return PHPExcel_Calculation_Functions::NaN();
266
			}
267
			$daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
268
			if (!is_numeric($daysBetweenIssueAndSettlement)) {
269
				//	return date error
270
				return $daysBetweenIssueAndSettlement;
271
			}
272
			return $par * $rate * $daysBetweenIssueAndSettlement;
273
		}
274
		return PHPExcel_Calculation_Functions::VALUE();
275
	}	//	function ACCRINTM()
276
 
277
 
278
	/**
279
	 * AMORDEGRC
280
	 *
281
	 * Returns the depreciation for each accounting period.
282
	 * This function is provided for the French accounting system. If an asset is purchased in
283
	 * the middle of the accounting period, the prorated depreciation is taken into account.
284
	 * The function is similar to AMORLINC, except that a depreciation coefficient is applied in
285
	 * the calculation depending on the life of the assets.
286
	 * This function will return the depreciation until the last period of the life of the assets
287
	 * or until the cumulated value of depreciation is greater than the cost of the assets minus
288
	 * the salvage value.
289
	 *
290
	 * Excel Function:
291
	 *		AMORDEGRC(cost,purchased,firstPeriod,salvage,period,rate[,basis])
292
	 *
293
	 * @access	public
294
	 * @category Financial Functions
295
	 * @param	float	cost		The cost of the asset.
296
	 * @param	mixed	purchased	Date of the purchase of the asset.
297
	 * @param	mixed	firstPeriod	Date of the end of the first period.
298
	 * @param	mixed	salvage		The salvage value at the end of the life of the asset.
299
	 * @param	float	period		The period.
300
	 * @param	float	rate		Rate of depreciation.
301
	 * @param	integer	basis		The type of day count to use.
302
	 *										0 or omitted	US (NASD) 30/360
303
	 *										1				Actual/actual
304
	 *										2				Actual/360
305
	 *										3				Actual/365
306
	 *										4				European 30/360
307
	 * @return	float
308
	 */
309
	public static function AMORDEGRC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis=0) {
310
		$cost			= PHPExcel_Calculation_Functions::flattenSingleValue($cost);
311
		$purchased		= PHPExcel_Calculation_Functions::flattenSingleValue($purchased);
312
		$firstPeriod	= PHPExcel_Calculation_Functions::flattenSingleValue($firstPeriod);
313
		$salvage		= PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
314
		$period			= floor(PHPExcel_Calculation_Functions::flattenSingleValue($period));
315
		$rate			= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
316
		$basis			= (is_null($basis))	? 0 :	(int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
317
 
318
		//	The depreciation coefficients are:
319
		//	Life of assets (1/rate)		Depreciation coefficient
320
		//	Less than 3 years			1
321
		//	Between 3 and 4 years		1.5
322
		//	Between 5 and 6 years		2
323
		//	More than 6 years			2.5
324
		$fUsePer = 1.0 / $rate;
325
		if ($fUsePer < 3.0) {
326
			$amortiseCoeff = 1.0;
327
		} elseif ($fUsePer < 5.0) {
328
			$amortiseCoeff = 1.5;
329
		} elseif ($fUsePer <= 6.0) {
330
			$amortiseCoeff = 2.0;
331
		} else {
332
			$amortiseCoeff = 2.5;
333
		}
334
 
335
		$rate *= $amortiseCoeff;
336
		$fNRate = round(PHPExcel_Calculation_DateTime::YEARFRAC($purchased, $firstPeriod, $basis) * $rate * $cost,0);
337
		$cost -= $fNRate;
338
		$fRest = $cost - $salvage;
339
 
340
		for ($n = 0; $n < $period; ++$n) {
341
			$fNRate = round($rate * $cost,0);
342
			$fRest -= $fNRate;
343
 
344
			if ($fRest < 0.0) {
345
				switch ($period - $n) {
346
					case 0	:
347
					case 1	: return round($cost * 0.5, 0);
348
							  break;
349
					default	: return 0.0;
350
							  break;
351
				}
352
			}
353
			$cost -= $fNRate;
354
		}
355
		return $fNRate;
356
	}	//	function AMORDEGRC()
357
 
358
 
359
	/**
360
	 * AMORLINC
361
	 *
362
	 * Returns the depreciation for each accounting period.
363
	 * This function is provided for the French accounting system. If an asset is purchased in
364
	 * the middle of the accounting period, the prorated depreciation is taken into account.
365
	 *
366
	 * Excel Function:
367
	 *		AMORLINC(cost,purchased,firstPeriod,salvage,period,rate[,basis])
368
	 *
369
	 * @access	public
370
	 * @category Financial Functions
371
	 * @param	float	cost		The cost of the asset.
372
	 * @param	mixed	purchased	Date of the purchase of the asset.
373
	 * @param	mixed	firstPeriod	Date of the end of the first period.
374
	 * @param	mixed	salvage		The salvage value at the end of the life of the asset.
375
	 * @param	float	period		The period.
376
	 * @param	float	rate		Rate of depreciation.
377
	 * @param	integer	basis		The type of day count to use.
378
	 *										0 or omitted	US (NASD) 30/360
379
	 *										1				Actual/actual
380
	 *										2				Actual/360
381
	 *										3				Actual/365
382
	 *										4				European 30/360
383
	 * @return	float
384
	 */
385
	public static function AMORLINC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis=0) {
386
		$cost			= PHPExcel_Calculation_Functions::flattenSingleValue($cost);
387
		$purchased		= PHPExcel_Calculation_Functions::flattenSingleValue($purchased);
388
		$firstPeriod	= PHPExcel_Calculation_Functions::flattenSingleValue($firstPeriod);
389
		$salvage		= PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
390
		$period			= PHPExcel_Calculation_Functions::flattenSingleValue($period);
391
		$rate			= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
392
		$basis			= (is_null($basis))	? 0 :	(int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
393
 
394
		$fOneRate = $cost * $rate;
395
		$fCostDelta = $cost - $salvage;
396
		//	Note, quirky variation for leap years on the YEARFRAC for this function
397
		$purchasedYear = PHPExcel_Calculation_DateTime::YEAR($purchased);
398
		$yearFrac = PHPExcel_Calculation_DateTime::YEARFRAC($purchased, $firstPeriod, $basis);
399
 
400
		if (($basis == 1) && ($yearFrac < 1) && (PHPExcel_Calculation_DateTime::_isLeapYear($purchasedYear))) {
401
			$yearFrac *= 365 / 366;
402
		}
403
 
404
		$f0Rate = $yearFrac * $rate * $cost;
405
		$nNumOfFullPeriods = intval(($cost - $salvage - $f0Rate) / $fOneRate);
406
 
407
		if ($period == 0) {
408
			return $f0Rate;
409
		} elseif ($period <= $nNumOfFullPeriods) {
410
			return $fOneRate;
411
		} elseif ($period == ($nNumOfFullPeriods + 1)) {
412
            return ($fCostDelta - $fOneRate * $nNumOfFullPeriods - $f0Rate);
413
		} else {
414
			return 0.0;
415
		}
416
	}	//	function AMORLINC()
417
 
418
 
419
	/**
420
	 * COUPDAYBS
421
	 *
422
	 * Returns the number of days from the beginning of the coupon period to the settlement date.
423
	 *
424
	 * Excel Function:
425
	 *		COUPDAYBS(settlement,maturity,frequency[,basis])
426
	 *
427
	 * @access	public
428
	 * @category Financial Functions
429
	 * @param	mixed	settlement	The security's settlement date.
430
	 *								The security settlement date is the date after the issue
431
	 *								date when the security is traded to the buyer.
432
	 * @param	mixed	maturity	The security's maturity date.
433
	 *								The maturity date is the date when the security expires.
434
	 * @param	mixed	frequency	the number of coupon payments per year.
435
	 *									Valid frequency values are:
436
	 *										1	Annual
437
	 *										2	Semi-Annual
438
	 *										4	Quarterly
439
	 *									If working in Gnumeric Mode, the following frequency options are
440
	 *									also available
441
	 *										6	Bimonthly
442
	 *										12	Monthly
443
	 * @param	integer		basis		The type of day count to use.
444
	 *										0 or omitted	US (NASD) 30/360
445
	 *										1				Actual/actual
446
	 *										2				Actual/360
447
	 *										3				Actual/365
448
	 *										4				European 30/360
449
	 * @return	float
450
	 */
451
	public static function COUPDAYBS($settlement, $maturity, $frequency, $basis=0) {
452
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
453
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
454
		$frequency	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
455
		$basis		= (is_null($basis))	? 0 :	(int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
456
 
457
		if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
458
			return PHPExcel_Calculation_Functions::VALUE();
459
		}
460
		if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
461
			return PHPExcel_Calculation_Functions::VALUE();
462
		}
463
 
464
		if (($settlement > $maturity) ||
465
			(!self::_validFrequency($frequency)) ||
466
			(($basis < 0) || ($basis > 4))) {
467
			return PHPExcel_Calculation_Functions::NaN();
468
		}
469
 
470
		$daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
471
		$prev = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False);
472
 
473
		return PHPExcel_Calculation_DateTime::YEARFRAC($prev, $settlement, $basis) * $daysPerYear;
474
	}	//	function COUPDAYBS()
475
 
476
 
477
	/**
478
	 * COUPDAYS
479
	 *
480
	 * Returns the number of days in the coupon period that contains the settlement date.
481
	 *
482
	 * Excel Function:
483
	 *		COUPDAYS(settlement,maturity,frequency[,basis])
484
	 *
485
	 * @access	public
486
	 * @category Financial Functions
487
	 * @param	mixed	settlement	The security's settlement date.
488
	 *								The security settlement date is the date after the issue
489
	 *								date when the security is traded to the buyer.
490
	 * @param	mixed	maturity	The security's maturity date.
491
	 *								The maturity date is the date when the security expires.
492
	 * @param	mixed	frequency	the number of coupon payments per year.
493
	 *									Valid frequency values are:
494
	 *										1	Annual
495
	 *										2	Semi-Annual
496
	 *										4	Quarterly
497
	 *									If working in Gnumeric Mode, the following frequency options are
498
	 *									also available
499
	 *										6	Bimonthly
500
	 *										12	Monthly
501
	 * @param	integer		basis		The type of day count to use.
502
	 *										0 or omitted	US (NASD) 30/360
503
	 *										1				Actual/actual
504
	 *										2				Actual/360
505
	 *										3				Actual/365
506
	 *										4				European 30/360
507
	 * @return	float
508
	 */
509
	public static function COUPDAYS($settlement, $maturity, $frequency, $basis=0) {
510
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
511
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
512
		$frequency	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
513
		$basis		= (is_null($basis))	? 0 :	(int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
514
 
515
		if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
516
			return PHPExcel_Calculation_Functions::VALUE();
517
		}
518
		if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
519
			return PHPExcel_Calculation_Functions::VALUE();
520
		}
521
 
522
		if (($settlement > $maturity) ||
523
			(!self::_validFrequency($frequency)) ||
524
			(($basis < 0) || ($basis > 4))) {
525
			return PHPExcel_Calculation_Functions::NaN();
526
		}
527
 
528
		switch ($basis) {
529
			case 3: // Actual/365
530
					return 365 / $frequency;
531
			case 1: // Actual/actual
532
					if ($frequency == 1) {
533
						$daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($maturity),$basis);
534
						return ($daysPerYear / $frequency);
535
					} else {
536
						$prev = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False);
537
						$next = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
538
						return ($next - $prev);
539
					}
540
			default: // US (NASD) 30/360, Actual/360 or European 30/360
541
					return 360 / $frequency;
542
		}
543
		return PHPExcel_Calculation_Functions::VALUE();
544
	}	//	function COUPDAYS()
545
 
546
 
547
	/**
548
	 * COUPDAYSNC
549
	 *
550
	 * Returns the number of days from the settlement date to the next coupon date.
551
	 *
552
	 * Excel Function:
553
	 *		COUPDAYSNC(settlement,maturity,frequency[,basis])
554
	 *
555
	 * @access	public
556
	 * @category Financial Functions
557
	 * @param	mixed	settlement	The security's settlement date.
558
	 *								The security settlement date is the date after the issue
559
	 *								date when the security is traded to the buyer.
560
	 * @param	mixed	maturity	The security's maturity date.
561
	 *								The maturity date is the date when the security expires.
562
	 * @param	mixed	frequency	the number of coupon payments per year.
563
	 *									Valid frequency values are:
564
	 *										1	Annual
565
	 *										2	Semi-Annual
566
	 *										4	Quarterly
567
	 *									If working in Gnumeric Mode, the following frequency options are
568
	 *									also available
569
	 *										6	Bimonthly
570
	 *										12	Monthly
571
	 * @param	integer		basis		The type of day count to use.
572
	 *										0 or omitted	US (NASD) 30/360
573
	 *										1				Actual/actual
574
	 *										2				Actual/360
575
	 *										3				Actual/365
576
	 *										4				European 30/360
577
	 * @return	float
578
	 */
579
	public static function COUPDAYSNC($settlement, $maturity, $frequency, $basis=0) {
580
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
581
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
582
		$frequency	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
583
		$basis		= (is_null($basis))	? 0 :	(int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
584
 
585
		if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
586
			return PHPExcel_Calculation_Functions::VALUE();
587
		}
588
		if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
589
			return PHPExcel_Calculation_Functions::VALUE();
590
		}
591
 
592
		if (($settlement > $maturity) ||
593
			(!self::_validFrequency($frequency)) ||
594
			(($basis < 0) || ($basis > 4))) {
595
			return PHPExcel_Calculation_Functions::NaN();
596
		}
597
 
598
		$daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
599
		$next = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
600
 
601
		return PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $next, $basis) * $daysPerYear;
602
	}	//	function COUPDAYSNC()
603
 
604
 
605
	/**
606
	 * COUPNCD
607
	 *
608
	 * Returns the next coupon date after the settlement date.
609
	 *
610
	 * Excel Function:
611
	 *		COUPNCD(settlement,maturity,frequency[,basis])
612
	 *
613
	 * @access	public
614
	 * @category Financial Functions
615
	 * @param	mixed	settlement	The security's settlement date.
616
	 *								The security settlement date is the date after the issue
617
	 *								date when the security is traded to the buyer.
618
	 * @param	mixed	maturity	The security's maturity date.
619
	 *								The maturity date is the date when the security expires.
620
	 * @param	mixed	frequency	the number of coupon payments per year.
621
	 *									Valid frequency values are:
622
	 *										1	Annual
623
	 *										2	Semi-Annual
624
	 *										4	Quarterly
625
	 *									If working in Gnumeric Mode, the following frequency options are
626
	 *									also available
627
	 *										6	Bimonthly
628
	 *										12	Monthly
629
	 * @param	integer		basis		The type of day count to use.
630
	 *										0 or omitted	US (NASD) 30/360
631
	 *										1				Actual/actual
632
	 *										2				Actual/360
633
	 *										3				Actual/365
634
	 *										4				European 30/360
635
	 * @return	mixed	Excel date/time serial value, PHP date/time serial value or PHP date/time object,
636
	 *						depending on the value of the ReturnDateType flag
637
	 */
638
	public static function COUPNCD($settlement, $maturity, $frequency, $basis=0) {
639
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
640
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
641
		$frequency	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
642
		$basis		= (is_null($basis))	? 0 :	(int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
643
 
644
		if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
645
			return PHPExcel_Calculation_Functions::VALUE();
646
		}
647
		if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
648
			return PHPExcel_Calculation_Functions::VALUE();
649
		}
650
 
651
		if (($settlement > $maturity) ||
652
			(!self::_validFrequency($frequency)) ||
653
			(($basis < 0) || ($basis > 4))) {
654
			return PHPExcel_Calculation_Functions::NaN();
655
		}
656
 
657
		return self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
658
	}	//	function COUPNCD()
659
 
660
 
661
	/**
662
	 * COUPNUM
663
	 *
664
	 * Returns the number of coupons payable between the settlement date and maturity date,
665
	 * rounded up to the nearest whole coupon.
666
	 *
667
	 * Excel Function:
668
	 *		COUPNUM(settlement,maturity,frequency[,basis])
669
	 *
670
	 * @access	public
671
	 * @category Financial Functions
672
	 * @param	mixed	settlement	The security's settlement date.
673
	 *								The security settlement date is the date after the issue
674
	 *								date when the security is traded to the buyer.
675
	 * @param	mixed	maturity	The security's maturity date.
676
	 *								The maturity date is the date when the security expires.
677
	 * @param	mixed	frequency	the number of coupon payments per year.
678
	 *									Valid frequency values are:
679
	 *										1	Annual
680
	 *										2	Semi-Annual
681
	 *										4	Quarterly
682
	 *									If working in Gnumeric Mode, the following frequency options are
683
	 *									also available
684
	 *										6	Bimonthly
685
	 *										12	Monthly
686
	 * @param	integer		basis		The type of day count to use.
687
	 *										0 or omitted	US (NASD) 30/360
688
	 *										1				Actual/actual
689
	 *										2				Actual/360
690
	 *										3				Actual/365
691
	 *										4				European 30/360
692
	 * @return	integer
693
	 */
694
	public static function COUPNUM($settlement, $maturity, $frequency, $basis=0) {
695
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
696
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
697
		$frequency	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
698
		$basis		= (is_null($basis))	? 0 :	(int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
699
 
700
		if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
701
			return PHPExcel_Calculation_Functions::VALUE();
702
		}
703
		if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
704
			return PHPExcel_Calculation_Functions::VALUE();
705
		}
706
 
707
		if (($settlement > $maturity) ||
708
			(!self::_validFrequency($frequency)) ||
709
			(($basis < 0) || ($basis > 4))) {
710
			return PHPExcel_Calculation_Functions::NaN();
711
		}
712
 
713
		$settlement = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
714
		$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis) * 365;
715
 
716
		switch ($frequency) {
717
			case 1: // annual payments
718
					return ceil($daysBetweenSettlementAndMaturity / 360);
719
			case 2: // half-yearly
720
					return ceil($daysBetweenSettlementAndMaturity / 180);
721
			case 4: // quarterly
722
					return ceil($daysBetweenSettlementAndMaturity / 90);
723
			case 6: // bimonthly
724
					return ceil($daysBetweenSettlementAndMaturity / 60);
725
			case 12: // monthly
726
					return ceil($daysBetweenSettlementAndMaturity / 30);
727
		}
728
		return PHPExcel_Calculation_Functions::VALUE();
729
	}	//	function COUPNUM()
730
 
731
 
732
	/**
733
	 * COUPPCD
734
	 *
735
	 * Returns the previous coupon date before the settlement date.
736
	 *
737
	 * Excel Function:
738
	 *		COUPPCD(settlement,maturity,frequency[,basis])
739
	 *
740
	 * @access	public
741
	 * @category Financial Functions
742
	 * @param	mixed	settlement	The security's settlement date.
743
	 *								The security settlement date is the date after the issue
744
	 *								date when the security is traded to the buyer.
745
	 * @param	mixed	maturity	The security's maturity date.
746
	 *								The maturity date is the date when the security expires.
747
	 * @param	mixed	frequency	the number of coupon payments per year.
748
	 *									Valid frequency values are:
749
	 *										1	Annual
750
	 *										2	Semi-Annual
751
	 *										4	Quarterly
752
	 *									If working in Gnumeric Mode, the following frequency options are
753
	 *									also available
754
	 *										6	Bimonthly
755
	 *										12	Monthly
756
	 * @param	integer		basis		The type of day count to use.
757
	 *										0 or omitted	US (NASD) 30/360
758
	 *										1				Actual/actual
759
	 *										2				Actual/360
760
	 *										3				Actual/365
761
	 *										4				European 30/360
762
	 * @return	mixed	Excel date/time serial value, PHP date/time serial value or PHP date/time object,
763
	 *						depending on the value of the ReturnDateType flag
764
	 */
765
	public static function COUPPCD($settlement, $maturity, $frequency, $basis=0) {
766
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
767
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
768
		$frequency	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
769
		$basis		= (is_null($basis))	? 0 :	(int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
770
 
771
		if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
772
			return PHPExcel_Calculation_Functions::VALUE();
773
		}
774
		if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
775
			return PHPExcel_Calculation_Functions::VALUE();
776
		}
777
 
778
		if (($settlement > $maturity) ||
779
			(!self::_validFrequency($frequency)) ||
780
			(($basis < 0) || ($basis > 4))) {
781
			return PHPExcel_Calculation_Functions::NaN();
782
		}
783
 
784
		return self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False);
785
	}	//	function COUPPCD()
786
 
787
 
788
	/**
789
	 * CUMIPMT
790
	 *
791
	 * Returns the cumulative interest paid on a loan between the start and end periods.
792
	 *
793
	 * Excel Function:
794
	 *		CUMIPMT(rate,nper,pv,start,end[,type])
795
	 *
796
	 * @access	public
797
	 * @category Financial Functions
798
	 * @param	float	$rate	The Interest rate
799
	 * @param	integer	$nper	The total number of payment periods
800
	 * @param	float	$pv		Present Value
801
	 * @param	integer	$start	The first period in the calculation.
802
	 *							Payment periods are numbered beginning with 1.
803
	 * @param	integer	$end	The last period in the calculation.
804
	 * @param	integer	$type	A number 0 or 1 and indicates when payments are due:
805
	 *								0 or omitted	At the end of the period.
806
	 *								1				At the beginning of the period.
807
	 * @return	float
808
	 */
809
	public static function CUMIPMT($rate, $nper, $pv, $start, $end, $type = 0) {
810
		$rate	= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
811
		$nper	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
812
		$pv		= PHPExcel_Calculation_Functions::flattenSingleValue($pv);
813
		$start	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($start);
814
		$end	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($end);
815
		$type	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
816
 
817
		// Validate parameters
818
		if ($type != 0 && $type != 1) {
819
			return PHPExcel_Calculation_Functions::NaN();
820
		}
821
		if ($start < 1 || $start > $end) {
822
			return PHPExcel_Calculation_Functions::VALUE();
823
		}
824
 
825
		// Calculate
826
		$interest = 0;
827
		for ($per = $start; $per <= $end; ++$per) {
828
			$interest += self::IPMT($rate, $per, $nper, $pv, 0, $type);
829
		}
830
 
831
		return $interest;
832
	}	//	function CUMIPMT()
833
 
834
 
835
	/**
836
	 * CUMPRINC
837
	 *
838
	 * Returns the cumulative principal paid on a loan between the start and end periods.
839
	 *
840
	 * Excel Function:
841
	 *		CUMPRINC(rate,nper,pv,start,end[,type])
842
	 *
843
	 * @access	public
844
	 * @category Financial Functions
845
	 * @param	float	$rate	The Interest rate
846
	 * @param	integer	$nper	The total number of payment periods
847
	 * @param	float	$pv		Present Value
848
	 * @param	integer	$start	The first period in the calculation.
849
	 *							Payment periods are numbered beginning with 1.
850
	 * @param	integer	$end	The last period in the calculation.
851
	 * @param	integer	$type	A number 0 or 1 and indicates when payments are due:
852
	 *								0 or omitted	At the end of the period.
853
	 *								1				At the beginning of the period.
854
	 * @return	float
855
	 */
856
	public static function CUMPRINC($rate, $nper, $pv, $start, $end, $type = 0) {
857
		$rate	= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
858
		$nper	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
859
		$pv		= PHPExcel_Calculation_Functions::flattenSingleValue($pv);
860
		$start	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($start);
861
		$end	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($end);
862
		$type	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
863
 
864
		// Validate parameters
865
		if ($type != 0 && $type != 1) {
866
			return PHPExcel_Calculation_Functions::NaN();
867
		}
868
		if ($start < 1 || $start > $end) {
869
			return PHPExcel_Calculation_Functions::VALUE();
870
		}
871
 
872
		// Calculate
873
		$principal = 0;
874
		for ($per = $start; $per <= $end; ++$per) {
875
			$principal += self::PPMT($rate, $per, $nper, $pv, 0, $type);
876
		}
877
 
878
		return $principal;
879
	}	//	function CUMPRINC()
880
 
881
 
882
	/**
883
	 * DB
884
	 *
885
	 * Returns the depreciation of an asset for a specified period using the
886
	 * fixed-declining balance method.
887
	 * This form of depreciation is used if you want to get a higher depreciation value
888
	 * at the beginning of the depreciation (as opposed to linear depreciation). The
889
	 * depreciation value is reduced with every depreciation period by the depreciation
890
	 * already deducted from the initial cost.
891
	 *
892
	 * Excel Function:
893
	 *		DB(cost,salvage,life,period[,month])
894
	 *
895
	 * @access	public
896
	 * @category Financial Functions
897
	 * @param	float	cost		Initial cost of the asset.
898
	 * @param	float	salvage		Value at the end of the depreciation.
899
	 *								(Sometimes called the salvage value of the asset)
900
	 * @param	integer	life		Number of periods over which the asset is depreciated.
901
	 *								(Sometimes called the useful life of the asset)
902
	 * @param	integer	period		The period for which you want to calculate the
903
	 *								depreciation. Period must use the same units as life.
904
	 * @param	integer	month		Number of months in the first year. If month is omitted,
905
	 *								it defaults to 12.
906
	 * @return	float
907
	 */
908
	public static function DB($cost, $salvage, $life, $period, $month=12) {
909
		$cost		= PHPExcel_Calculation_Functions::flattenSingleValue($cost);
910
		$salvage	= PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
911
		$life		= PHPExcel_Calculation_Functions::flattenSingleValue($life);
912
		$period		= PHPExcel_Calculation_Functions::flattenSingleValue($period);
913
		$month		= PHPExcel_Calculation_Functions::flattenSingleValue($month);
914
 
915
		//	Validate
916
		if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period)) && (is_numeric($month))) {
917
			$cost		= (float) $cost;
918
			$salvage	= (float) $salvage;
919
			$life		= (int) $life;
920
			$period		= (int) $period;
921
			$month		= (int) $month;
922
			if ($cost == 0) {
923
				return 0.0;
924
			} elseif (($cost < 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($month < 1)) {
925
				return PHPExcel_Calculation_Functions::NaN();
926
			}
927
			//	Set Fixed Depreciation Rate
928
			$fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life));
929
			$fixedDepreciationRate = round($fixedDepreciationRate, 3);
930
 
931
			//	Loop through each period calculating the depreciation
932
			$previousDepreciation = 0;
933
			for ($per = 1; $per <= $period; ++$per) {
934
				if ($per == 1) {
935
					$depreciation = $cost * $fixedDepreciationRate * $month / 12;
936
				} elseif ($per == ($life + 1)) {
937
					$depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate * (12 - $month) / 12;
938
				} else {
939
					$depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate;
940
				}
941
				$previousDepreciation += $depreciation;
942
			}
943
			if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
944
				$depreciation = round($depreciation,2);
945
			}
946
			return $depreciation;
947
		}
948
		return PHPExcel_Calculation_Functions::VALUE();
949
	}	//	function DB()
950
 
951
 
952
	/**
953
	 * DDB
954
	 *
955
	 * Returns the depreciation of an asset for a specified period using the
956
	 * double-declining balance method or some other method you specify.
957
	 *
958
	 * Excel Function:
959
	 *		DDB(cost,salvage,life,period[,factor])
960
	 *
961
	 * @access	public
962
	 * @category Financial Functions
963
	 * @param	float	cost		Initial cost of the asset.
964
	 * @param	float	salvage		Value at the end of the depreciation.
965
	 *								(Sometimes called the salvage value of the asset)
966
	 * @param	integer	life		Number of periods over which the asset is depreciated.
967
	 *								(Sometimes called the useful life of the asset)
968
	 * @param	integer	period		The period for which you want to calculate the
969
	 *								depreciation. Period must use the same units as life.
970
	 * @param	float	factor		The rate at which the balance declines.
971
	 *								If factor is omitted, it is assumed to be 2 (the
972
	 *								double-declining balance method).
973
	 * @return	float
974
	 */
975
	public static function DDB($cost, $salvage, $life, $period, $factor=2.0) {
976
		$cost		= PHPExcel_Calculation_Functions::flattenSingleValue($cost);
977
		$salvage	= PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
978
		$life		= PHPExcel_Calculation_Functions::flattenSingleValue($life);
979
		$period		= PHPExcel_Calculation_Functions::flattenSingleValue($period);
980
		$factor		= PHPExcel_Calculation_Functions::flattenSingleValue($factor);
981
 
982
		//	Validate
983
		if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period)) && (is_numeric($factor))) {
984
			$cost		= (float) $cost;
985
			$salvage	= (float) $salvage;
986
			$life		= (int) $life;
987
			$period		= (int) $period;
988
			$factor		= (float) $factor;
989
			if (($cost <= 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($factor <= 0.0) || ($period > $life)) {
990
				return PHPExcel_Calculation_Functions::NaN();
991
			}
992
			//	Set Fixed Depreciation Rate
993
			$fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life));
994
			$fixedDepreciationRate = round($fixedDepreciationRate, 3);
995
 
996
			//	Loop through each period calculating the depreciation
997
			$previousDepreciation = 0;
998
			for ($per = 1; $per <= $period; ++$per) {
999
				$depreciation = min( ($cost - $previousDepreciation) * ($factor / $life), ($cost - $salvage - $previousDepreciation) );
1000
				$previousDepreciation += $depreciation;
1001
			}
1002
			if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
1003
				$depreciation = round($depreciation,2);
1004
			}
1005
			return $depreciation;
1006
		}
1007
		return PHPExcel_Calculation_Functions::VALUE();
1008
	}	//	function DDB()
1009
 
1010
 
1011
	/**
1012
	 * DISC
1013
	 *
1014
	 * Returns the discount rate for a security.
1015
	 *
1016
	 * Excel Function:
1017
	 *		DISC(settlement,maturity,price,redemption[,basis])
1018
	 *
1019
	 * @access	public
1020
	 * @category Financial Functions
1021
	 * @param	mixed	settlement	The security's settlement date.
1022
	 *								The security settlement date is the date after the issue
1023
	 *								date when the security is traded to the buyer.
1024
	 * @param	mixed	maturity	The security's maturity date.
1025
	 *								The maturity date is the date when the security expires.
1026
	 * @param	integer	price		The security's price per $100 face value.
1027
	 * @param	integer	redemption	The security's redemption value per $100 face value.
1028
	 * @param	integer	basis		The type of day count to use.
1029
	 *										0 or omitted	US (NASD) 30/360
1030
	 *										1				Actual/actual
1031
	 *										2				Actual/360
1032
	 *										3				Actual/365
1033
	 *										4				European 30/360
1034
	 * @return	float
1035
	 */
1036
	public static function DISC($settlement, $maturity, $price, $redemption, $basis=0) {
1037
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1038
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1039
		$price		= PHPExcel_Calculation_Functions::flattenSingleValue($price);
1040
		$redemption	= PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
1041
		$basis		= PHPExcel_Calculation_Functions::flattenSingleValue($basis);
1042
 
1043
		//	Validate
1044
		if ((is_numeric($price)) && (is_numeric($redemption)) && (is_numeric($basis))) {
1045
			$price		= (float) $price;
1046
			$redemption	= (float) $redemption;
1047
			$basis		= (int) $basis;
1048
			if (($price <= 0) || ($redemption <= 0)) {
1049
				return PHPExcel_Calculation_Functions::NaN();
1050
			}
1051
			$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
1052
			if (!is_numeric($daysBetweenSettlementAndMaturity)) {
1053
				//	return date error
1054
				return $daysBetweenSettlementAndMaturity;
1055
			}
1056
 
1057
			return ((1 - $price / $redemption) / $daysBetweenSettlementAndMaturity);
1058
		}
1059
		return PHPExcel_Calculation_Functions::VALUE();
1060
	}	//	function DISC()
1061
 
1062
 
1063
	/**
1064
	 * DOLLARDE
1065
	 *
1066
	 * Converts a dollar price expressed as an integer part and a fraction
1067
	 *		part into a dollar price expressed as a decimal number.
1068
	 * Fractional dollar numbers are sometimes used for security prices.
1069
	 *
1070
	 * Excel Function:
1071
	 *		DOLLARDE(fractional_dollar,fraction)
1072
	 *
1073
	 * @access	public
1074
	 * @category Financial Functions
1075
	 * @param	float	$fractional_dollar	Fractional Dollar
1076
	 * @param	integer	$fraction			Fraction
1077
	 * @return	float
1078
	 */
1079
	public static function DOLLARDE($fractional_dollar = Null, $fraction = 0) {
1080
		$fractional_dollar	= PHPExcel_Calculation_Functions::flattenSingleValue($fractional_dollar);
1081
		$fraction			= (int)PHPExcel_Calculation_Functions::flattenSingleValue($fraction);
1082
 
1083
		// Validate parameters
1084
		if (is_null($fractional_dollar) || $fraction < 0) {
1085
			return PHPExcel_Calculation_Functions::NaN();
1086
		}
1087
		if ($fraction == 0) {
1088
			return PHPExcel_Calculation_Functions::DIV0();
1089
		}
1090
 
1091
		$dollars = floor($fractional_dollar);
1092
		$cents = fmod($fractional_dollar,1);
1093
		$cents /= $fraction;
1094
		$cents *= pow(10,ceil(log10($fraction)));
1095
		return $dollars + $cents;
1096
	}	//	function DOLLARDE()
1097
 
1098
 
1099
	/**
1100
	 * DOLLARFR
1101
	 *
1102
	 * Converts a dollar price expressed as a decimal number into a dollar price
1103
	 *		expressed as a fraction.
1104
	 * Fractional dollar numbers are sometimes used for security prices.
1105
	 *
1106
	 * Excel Function:
1107
	 *		DOLLARFR(decimal_dollar,fraction)
1108
	 *
1109
	 * @access	public
1110
	 * @category Financial Functions
1111
	 * @param	float	$decimal_dollar		Decimal Dollar
1112
	 * @param	integer	$fraction			Fraction
1113
	 * @return	float
1114
	 */
1115
	public static function DOLLARFR($decimal_dollar = Null, $fraction = 0) {
1116
		$decimal_dollar	= PHPExcel_Calculation_Functions::flattenSingleValue($decimal_dollar);
1117
		$fraction		= (int)PHPExcel_Calculation_Functions::flattenSingleValue($fraction);
1118
 
1119
		// Validate parameters
1120
		if (is_null($decimal_dollar) || $fraction < 0) {
1121
			return PHPExcel_Calculation_Functions::NaN();
1122
		}
1123
		if ($fraction == 0) {
1124
			return PHPExcel_Calculation_Functions::DIV0();
1125
		}
1126
 
1127
		$dollars = floor($decimal_dollar);
1128
		$cents = fmod($decimal_dollar,1);
1129
		$cents *= $fraction;
1130
		$cents *= pow(10,-ceil(log10($fraction)));
1131
		return $dollars + $cents;
1132
	}	//	function DOLLARFR()
1133
 
1134
 
1135
	/**
1136
	 * EFFECT
1137
	 *
1138
	 * Returns the effective interest rate given the nominal rate and the number of
1139
	 *		compounding payments per year.
1140
	 *
1141
	 * Excel Function:
1142
	 *		EFFECT(nominal_rate,npery)
1143
	 *
1144
	 * @access	public
1145
	 * @category Financial Functions
1146
	 * @param	float	$nominal_rate		Nominal interest rate
1147
	 * @param	integer	$npery				Number of compounding payments per year
1148
	 * @return	float
1149
	 */
1150
	public static function EFFECT($nominal_rate = 0, $npery = 0) {
1151
		$nominal_rate	= PHPExcel_Calculation_Functions::flattenSingleValue($nominal_rate);
1152
		$npery			= (int)PHPExcel_Calculation_Functions::flattenSingleValue($npery);
1153
 
1154
		// Validate parameters
1155
		if ($nominal_rate <= 0 || $npery < 1) {
1156
			return PHPExcel_Calculation_Functions::NaN();
1157
		}
1158
 
1159
		return pow((1 + $nominal_rate / $npery), $npery) - 1;
1160
	}	//	function EFFECT()
1161
 
1162
 
1163
	/**
1164
	 * FV
1165
	 *
1166
	 * Returns the Future Value of a cash flow with constant payments and interest rate (annuities).
1167
	 *
1168
	 * Excel Function:
1169
	 *		FV(rate,nper,pmt[,pv[,type]])
1170
	 *
1171
	 * @access	public
1172
	 * @category Financial Functions
1173
	 * @param	float	$rate	The interest rate per period
1174
	 * @param	int		$nper	Total number of payment periods in an annuity
1175
	 * @param	float	$pmt	The payment made each period: it cannot change over the
1176
	 *							life of the annuity. Typically, pmt contains principal
1177
	 *							and interest but no other fees or taxes.
1178
	 * @param	float	$pv		Present Value, or the lump-sum amount that a series of
1179
	 *							future payments is worth right now.
1180
	 * @param	integer	$type	A number 0 or 1 and indicates when payments are due:
1181
	 *								0 or omitted	At the end of the period.
1182
	 *								1				At the beginning of the period.
1183
	 * @return	float
1184
	 */
1185
	public static function FV($rate = 0, $nper = 0, $pmt = 0, $pv = 0, $type = 0) {
1186
		$rate	= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1187
		$nper	= PHPExcel_Calculation_Functions::flattenSingleValue($nper);
1188
		$pmt	= PHPExcel_Calculation_Functions::flattenSingleValue($pmt);
1189
		$pv		= PHPExcel_Calculation_Functions::flattenSingleValue($pv);
1190
		$type	= PHPExcel_Calculation_Functions::flattenSingleValue($type);
1191
 
1192
		// Validate parameters
1193
		if ($type != 0 && $type != 1) {
1194
			return PHPExcel_Calculation_Functions::NaN();
1195
		}
1196
 
1197
		// Calculate
1198
		if (!is_null($rate) && $rate != 0) {
1199
			return -$pv * pow(1 + $rate, $nper) - $pmt * (1 + $rate * $type) * (pow(1 + $rate, $nper) - 1) / $rate;
1200
		} else {
1201
			return -$pv - $pmt * $nper;
1202
		}
1203
	}	//	function FV()
1204
 
1205
 
1206
	/**
1207
	 * FVSCHEDULE
1208
	 *
1209
	 * Returns the future value of an initial principal after applying a series of compound interest rates.
1210
	 * Use FVSCHEDULE to calculate the future value of an investment with a variable or adjustable rate.
1211
	 *
1212
	 * Excel Function:
1213
	 *		FVSCHEDULE(principal,schedule)
1214
	 *
1215
	 * @param	float	$principal	The present value.
1216
	 * @param	float[]	$schedule	An array of interest rates to apply.
1217
	 * @return	float
1218
	 */
1219
	public static function FVSCHEDULE($principal, $schedule) {
1220
		$principal	= PHPExcel_Calculation_Functions::flattenSingleValue($principal);
1221
		$schedule	= PHPExcel_Calculation_Functions::flattenArray($schedule);
1222
 
1223
		foreach($schedule as $rate) {
1224
			$principal *= 1 + $rate;
1225
		}
1226
 
1227
		return $principal;
1228
	}	//	function FVSCHEDULE()
1229
 
1230
 
1231
	/**
1232
	 * INTRATE
1233
	 *
1234
	 * Returns the interest rate for a fully invested security.
1235
	 *
1236
	 * Excel Function:
1237
	 *		INTRATE(settlement,maturity,investment,redemption[,basis])
1238
	 *
1239
	 * @param	mixed	$settlement	The security's settlement date.
1240
	 *								The security settlement date is the date after the issue date when the security is traded to the buyer.
1241
	 * @param	mixed	$maturity	The security's maturity date.
1242
	 *								The maturity date is the date when the security expires.
1243
	 * @param	integer	$investment	The amount invested in the security.
1244
	 * @param	integer	$redemption	The amount to be received at maturity.
1245
	 * @param	integer	$basis		The type of day count to use.
1246
	 *										0 or omitted	US (NASD) 30/360
1247
	 *										1				Actual/actual
1248
	 *										2				Actual/360
1249
	 *										3				Actual/365
1250
	 *										4				European 30/360
1251
	 * @return	float
1252
	 */
1253
	public static function INTRATE($settlement, $maturity, $investment, $redemption, $basis=0) {
1254
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1255
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1256
		$investment	= PHPExcel_Calculation_Functions::flattenSingleValue($investment);
1257
		$redemption	= PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
1258
		$basis		= PHPExcel_Calculation_Functions::flattenSingleValue($basis);
1259
 
1260
		//	Validate
1261
		if ((is_numeric($investment)) && (is_numeric($redemption)) && (is_numeric($basis))) {
1262
			$investment	= (float) $investment;
1263
			$redemption	= (float) $redemption;
1264
			$basis		= (int) $basis;
1265
			if (($investment <= 0) || ($redemption <= 0)) {
1266
				return PHPExcel_Calculation_Functions::NaN();
1267
			}
1268
			$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
1269
			if (!is_numeric($daysBetweenSettlementAndMaturity)) {
1270
				//	return date error
1271
				return $daysBetweenSettlementAndMaturity;
1272
			}
1273
 
1274
			return (($redemption / $investment) - 1) / ($daysBetweenSettlementAndMaturity);
1275
		}
1276
		return PHPExcel_Calculation_Functions::VALUE();
1277
	}	//	function INTRATE()
1278
 
1279
 
1280
	/**
1281
	 * IPMT
1282
	 *
1283
	 * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
1284
	 *
1285
	 * Excel Function:
1286
	 *		IPMT(rate,per,nper,pv[,fv][,type])
1287
	 *
1288
	 * @param	float	$rate	Interest rate per period
1289
	 * @param	int		$per	Period for which we want to find the interest
1290
	 * @param	int		$nper	Number of periods
1291
	 * @param	float	$pv		Present Value
1292
	 * @param	float	$fv		Future Value
1293
	 * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
1294
	 * @return	float
1295
	 */
1296
	public static function IPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
1297
		$rate	= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1298
		$per	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($per);
1299
		$nper	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
1300
		$pv		= PHPExcel_Calculation_Functions::flattenSingleValue($pv);
1301
		$fv		= PHPExcel_Calculation_Functions::flattenSingleValue($fv);
1302
		$type	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
1303
 
1304
		// Validate parameters
1305
		if ($type != 0 && $type != 1) {
1306
			return PHPExcel_Calculation_Functions::NaN();
1307
		}
1308
		if ($per <= 0 || $per > $nper) {
1309
			return PHPExcel_Calculation_Functions::VALUE();
1310
		}
1311
 
1312
		// Calculate
1313
		$interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
1314
		return $interestAndPrincipal[0];
1315
	}	//	function IPMT()
1316
 
1317
	/**
1318
	 * IRR
1319
	 *
1320
	 * Returns the internal rate of return for a series of cash flows represented by the numbers in values.
1321
	 * These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur
1322
	 * at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received
1323
	 * for an investment consisting of payments (negative values) and income (positive values) that occur at regular
1324
	 * periods.
1325
	 *
1326
	 * Excel Function:
1327
	 *		IRR(values[,guess])
1328
	 *
1329
	 * @param	float[]	$values		An array or a reference to cells that contain numbers for which you want
1330
	 *									to calculate the internal rate of return.
1331
	 *								Values must contain at least one positive value and one negative value to
1332
	 *									calculate the internal rate of return.
1333
	 * @param	float	$guess		A number that you guess is close to the result of IRR
1334
	 * @return	float
1335
	 */
1336
	public static function IRR($values, $guess = 0.1) {
1337
		if (!is_array($values)) return PHPExcel_Calculation_Functions::VALUE();
1338
		$values = PHPExcel_Calculation_Functions::flattenArray($values);
1339
		$guess = PHPExcel_Calculation_Functions::flattenSingleValue($guess);
1340
 
1341
		// create an initial range, with a root somewhere between 0 and guess
1342
		$x1 = 0.0;
1343
		$x2 = $guess;
1344
		$f1 = self::NPV($x1, $values);
1345
		$f2 = self::NPV($x2, $values);
1346
		for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
1347
			if (($f1 * $f2) < 0.0) break;
1348
			if (abs($f1) < abs($f2)) {
1349
				$f1 = self::NPV($x1 += 1.6 * ($x1 - $x2), $values);
1350
			} else {
1351
				$f2 = self::NPV($x2 += 1.6 * ($x2 - $x1), $values);
1352
			}
1353
		}
1354
		if (($f1 * $f2) > 0.0) return PHPExcel_Calculation_Functions::VALUE();
1355
 
1356
		$f = self::NPV($x1, $values);
1357
		if ($f < 0.0) {
1358
			$rtb = $x1;
1359
			$dx = $x2 - $x1;
1360
		} else {
1361
			$rtb = $x2;
1362
			$dx = $x1 - $x2;
1363
		}
1364
 
1365
		for ($i = 0;  $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
1366
			$dx *= 0.5;
1367
			$x_mid = $rtb + $dx;
1368
			$f_mid = self::NPV($x_mid, $values);
1369
			if ($f_mid <= 0.0)
1370
				$rtb = $x_mid;
1371
			if ((abs($f_mid) < FINANCIAL_PRECISION) || (abs($dx) < FINANCIAL_PRECISION))
1372
				return $x_mid;
1373
		}
1374
		return PHPExcel_Calculation_Functions::VALUE();
1375
	}	//	function IRR()
1376
 
1377
 
1378
	/**
1379
	 * ISPMT
1380
	 *
1381
	 * Returns the interest payment for an investment based on an interest rate and a constant payment schedule.
1382
	 *
1383
	 * Excel Function:
1384
	 *     =ISPMT(interest_rate, period, number_payments, PV)
1385
	 *
1386
	 * interest_rate is the interest rate for the investment
1387
	 *
1388
	 * period is the period to calculate the interest rate.  It must be betweeen 1 and number_payments.
1389
	 *
1390
	 * number_payments is the number of payments for the annuity
1391
	 *
1392
	 * PV is the loan amount or present value of the payments
1393
	 */
1394
	public static function ISPMT() {
1395
		// Return value
1396
		$returnValue = 0;
1397
 
1398
		// Get the parameters
1399
		$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1400
		$interestRate = array_shift($aArgs);
1401
		$period = array_shift($aArgs);
1402
		$numberPeriods = array_shift($aArgs);
1403
		$principleRemaining = array_shift($aArgs);
1404
 
1405
		// Calculate
1406
		$principlePayment = ($principleRemaining * 1.0) / ($numberPeriods * 1.0);
1407
		for($i=0; $i <= $period; ++$i) {
1408
			$returnValue = $interestRate * $principleRemaining * -1;
1409
			$principleRemaining -= $principlePayment;
1410
			// principle needs to be 0 after the last payment, don't let floating point screw it up
1411
			if($i == $numberPeriods) {
1412
				$returnValue = 0;
1413
			}
1414
		}
1415
		return($returnValue);
1416
	}	//	function ISPMT()
1417
 
1418
 
1419
	/**
1420
	 * MIRR
1421
	 *
1422
	 * Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both
1423
	 *		the cost of the investment and the interest received on reinvestment of cash.
1424
	 *
1425
	 * Excel Function:
1426
	 *		MIRR(values,finance_rate, reinvestment_rate)
1427
	 *
1428
	 * @param	float[]	$values				An array or a reference to cells that contain a series of payments and
1429
	 *											income occurring at regular intervals.
1430
	 *										Payments are negative value, income is positive values.
1431
	 * @param	float	$finance_rate		The interest rate you pay on the money used in the cash flows
1432
	 * @param	float	$reinvestment_rate	The interest rate you receive on the cash flows as you reinvest them
1433
	 * @return	float
1434
	 */
1435
	public static function MIRR($values, $finance_rate, $reinvestment_rate) {
1436
		if (!is_array($values)) return PHPExcel_Calculation_Functions::VALUE();
1437
		$values				= PHPExcel_Calculation_Functions::flattenArray($values);
1438
		$finance_rate		= PHPExcel_Calculation_Functions::flattenSingleValue($finance_rate);
1439
		$reinvestment_rate	= PHPExcel_Calculation_Functions::flattenSingleValue($reinvestment_rate);
1440
		$n = count($values);
1441
 
1442
		$rr = 1.0 + $reinvestment_rate;
1443
		$fr = 1.0 + $finance_rate;
1444
 
1445
		$npv_pos = $npv_neg = 0.0;
1446
		foreach($values as $i => $v) {
1447
			if ($v >= 0) {
1448
				$npv_pos += $v / pow($rr, $i);
1449
			} else {
1450
				$npv_neg += $v / pow($fr, $i);
1451
			}
1452
		}
1453
 
1454
		if (($npv_neg == 0) || ($npv_pos == 0) || ($reinvestment_rate <= -1)) {
1455
			return PHPExcel_Calculation_Functions::VALUE();
1456
		}
1457
 
1458
		$mirr = pow((-$npv_pos * pow($rr, $n))
1459
				/ ($npv_neg * ($rr)), (1.0 / ($n - 1))) - 1.0;
1460
 
1461
		return (is_finite($mirr) ? $mirr : PHPExcel_Calculation_Functions::VALUE());
1462
	}	//	function MIRR()
1463
 
1464
 
1465
	/**
1466
	 * NOMINAL
1467
	 *
1468
	 * Returns the nominal interest rate given the effective rate and the number of compounding payments per year.
1469
	 *
1470
	 * @param	float	$effect_rate	Effective interest rate
1471
	 * @param	int		$npery			Number of compounding payments per year
1472
	 * @return	float
1473
	 */
1474
	public static function NOMINAL($effect_rate = 0, $npery = 0) {
1475
		$effect_rate	= PHPExcel_Calculation_Functions::flattenSingleValue($effect_rate);
1476
		$npery			= (int)PHPExcel_Calculation_Functions::flattenSingleValue($npery);
1477
 
1478
		// Validate parameters
1479
		if ($effect_rate <= 0 || $npery < 1) {
1480
			return PHPExcel_Calculation_Functions::NaN();
1481
		}
1482
 
1483
		// Calculate
1484
		return $npery * (pow($effect_rate + 1, 1 / $npery) - 1);
1485
	}	//	function NOMINAL()
1486
 
1487
 
1488
	/**
1489
	 * NPER
1490
	 *
1491
	 * Returns the number of periods for a cash flow with constant periodic payments (annuities), and interest rate.
1492
	 *
1493
	 * @param	float	$rate	Interest rate per period
1494
	 * @param	int		$pmt	Periodic payment (annuity)
1495
	 * @param	float	$pv		Present Value
1496
	 * @param	float	$fv		Future Value
1497
	 * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
1498
	 * @return	float
1499
	 */
1500
	public static function NPER($rate = 0, $pmt = 0, $pv = 0, $fv = 0, $type = 0) {
1501
		$rate	= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1502
		$pmt	= PHPExcel_Calculation_Functions::flattenSingleValue($pmt);
1503
		$pv		= PHPExcel_Calculation_Functions::flattenSingleValue($pv);
1504
		$fv		= PHPExcel_Calculation_Functions::flattenSingleValue($fv);
1505
		$type	= PHPExcel_Calculation_Functions::flattenSingleValue($type);
1506
 
1507
		// Validate parameters
1508
		if ($type != 0 && $type != 1) {
1509
			return PHPExcel_Calculation_Functions::NaN();
1510
		}
1511
 
1512
		// Calculate
1513
		if (!is_null($rate) && $rate != 0) {
1514
			if ($pmt == 0 && $pv == 0) {
1515
				return PHPExcel_Calculation_Functions::NaN();
1516
			}
1517
			return log(($pmt * (1 + $rate * $type) / $rate - $fv) / ($pv + $pmt * (1 + $rate * $type) / $rate)) / log(1 + $rate);
1518
		} else {
1519
			if ($pmt == 0) {
1520
				return PHPExcel_Calculation_Functions::NaN();
1521
			}
1522
			return (-$pv -$fv) / $pmt;
1523
		}
1524
	}	//	function NPER()
1525
 
1526
	/**
1527
	 * NPV
1528
	 *
1529
	 * Returns the Net Present Value of a cash flow series given a discount rate.
1530
	 *
1531
	 * @return	float
1532
	 */
1533
	public static function NPV() {
1534
		// Return value
1535
		$returnValue = 0;
1536
 
1537
		// Loop through arguments
1538
		$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1539
 
1540
		// Calculate
1541
		$rate = array_shift($aArgs);
1542
		for ($i = 1; $i <= count($aArgs); ++$i) {
1543
			// Is it a numeric value?
1544
			if (is_numeric($aArgs[$i - 1])) {
1545
				$returnValue += $aArgs[$i - 1] / pow(1 + $rate, $i);
1546
			}
1547
		}
1548
 
1549
		// Return
1550
		return $returnValue;
1551
	}	//	function NPV()
1552
 
1553
	/**
1554
	 * PMT
1555
	 *
1556
	 * Returns the constant payment (annuity) for a cash flow with a constant interest rate.
1557
	 *
1558
	 * @param	float	$rate	Interest rate per period
1559
	 * @param	int		$nper	Number of periods
1560
	 * @param	float	$pv		Present Value
1561
	 * @param	float	$fv		Future Value
1562
	 * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
1563
	 * @return	float
1564
	 */
1565
	public static function PMT($rate = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0) {
1566
		$rate	= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1567
		$nper	= PHPExcel_Calculation_Functions::flattenSingleValue($nper);
1568
		$pv		= PHPExcel_Calculation_Functions::flattenSingleValue($pv);
1569
		$fv		= PHPExcel_Calculation_Functions::flattenSingleValue($fv);
1570
		$type	= PHPExcel_Calculation_Functions::flattenSingleValue($type);
1571
 
1572
		// Validate parameters
1573
		if ($type != 0 && $type != 1) {
1574
			return PHPExcel_Calculation_Functions::NaN();
1575
		}
1576
 
1577
		// Calculate
1578
		if (!is_null($rate) && $rate != 0) {
1579
			return (-$fv - $pv * pow(1 + $rate, $nper)) / (1 + $rate * $type) / ((pow(1 + $rate, $nper) - 1) / $rate);
1580
		} else {
1581
			return (-$pv - $fv) / $nper;
1582
		}
1583
	}	//	function PMT()
1584
 
1585
 
1586
	/**
1587
	 * PPMT
1588
	 *
1589
	 * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
1590
	 *
1591
	 * @param	float	$rate	Interest rate per period
1592
	 * @param	int		$per	Period for which we want to find the interest
1593
	 * @param	int		$nper	Number of periods
1594
	 * @param	float	$pv		Present Value
1595
	 * @param	float	$fv		Future Value
1596
	 * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
1597
	 * @return	float
1598
	 */
1599
	public static function PPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
1600
		$rate	= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1601
		$per	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($per);
1602
		$nper	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
1603
		$pv		= PHPExcel_Calculation_Functions::flattenSingleValue($pv);
1604
		$fv		= PHPExcel_Calculation_Functions::flattenSingleValue($fv);
1605
		$type	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
1606
 
1607
		// Validate parameters
1608
		if ($type != 0 && $type != 1) {
1609
			return PHPExcel_Calculation_Functions::NaN();
1610
		}
1611
		if ($per <= 0 || $per > $nper) {
1612
			return PHPExcel_Calculation_Functions::VALUE();
1613
		}
1614
 
1615
		// Calculate
1616
		$interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
1617
		return $interestAndPrincipal[1];
1618
	}	//	function PPMT()
1619
 
1620
 
1621
	public static function PRICE($settlement, $maturity, $rate, $yield, $redemption, $frequency, $basis=0) {
1622
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1623
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1624
		$rate		= (float) PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1625
		$yield		= (float) PHPExcel_Calculation_Functions::flattenSingleValue($yield);
1626
		$redemption	= (float) PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
1627
		$frequency	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
1628
		$basis		= (is_null($basis))	? 0 :	(int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
1629
 
1630
		if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
1631
			return PHPExcel_Calculation_Functions::VALUE();
1632
		}
1633
		if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
1634
			return PHPExcel_Calculation_Functions::VALUE();
1635
		}
1636
 
1637
		if (($settlement > $maturity) ||
1638
			(!self::_validFrequency($frequency)) ||
1639
			(($basis < 0) || ($basis > 4))) {
1640
			return PHPExcel_Calculation_Functions::NaN();
1641
		}
1642
 
1643
		$dsc = self::COUPDAYSNC($settlement, $maturity, $frequency, $basis);
1644
		$e = self::COUPDAYS($settlement, $maturity, $frequency, $basis);
1645
		$n = self::COUPNUM($settlement, $maturity, $frequency, $basis);
1646
		$a = self::COUPDAYBS($settlement, $maturity, $frequency, $basis);
1647
 
1648
		$baseYF	= 1.0 + ($yield / $frequency);
1649
		$rfp	= 100 * ($rate / $frequency);
1650
		$de	= $dsc / $e;
1651
 
1652
		$result = $redemption / pow($baseYF, (--$n + $de));
1653
		for($k = 0; $k <= $n; ++$k) {
1654
			$result += $rfp / (pow($baseYF, ($k + $de)));
1655
		}
1656
		$result -= $rfp * ($a / $e);
1657
 
1658
		return $result;
1659
	}	//	function PRICE()
1660
 
1661
 
1662
	/**
1663
	 * PRICEDISC
1664
	 *
1665
	 * Returns the price per $100 face value of a discounted security.
1666
	 *
1667
	 * @param	mixed	settlement	The security's settlement date.
1668
	 *								The security settlement date is the date after the issue date when the security is traded to the buyer.
1669
	 * @param	mixed	maturity	The security's maturity date.
1670
	 *								The maturity date is the date when the security expires.
1671
	 * @param	int		discount	The security's discount rate.
1672
	 * @param	int		redemption	The security's redemption value per $100 face value.
1673
	 * @param	int		basis		The type of day count to use.
1674
	 *										0 or omitted	US (NASD) 30/360
1675
	 *										1				Actual/actual
1676
	 *										2				Actual/360
1677
	 *										3				Actual/365
1678
	 *										4				European 30/360
1679
	 * @return	float
1680
	 */
1681
	public static function PRICEDISC($settlement, $maturity, $discount, $redemption, $basis=0) {
1682
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1683
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1684
		$discount	= (float) PHPExcel_Calculation_Functions::flattenSingleValue($discount);
1685
		$redemption	= (float) PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
1686
		$basis		= (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
1687
 
1688
		//	Validate
1689
		if ((is_numeric($discount)) && (is_numeric($redemption)) && (is_numeric($basis))) {
1690
			if (($discount <= 0) || ($redemption <= 0)) {
1691
				return PHPExcel_Calculation_Functions::NaN();
1692
			}
1693
			$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
1694
			if (!is_numeric($daysBetweenSettlementAndMaturity)) {
1695
				//	return date error
1696
				return $daysBetweenSettlementAndMaturity;
1697
			}
1698
 
1699
			return $redemption * (1 - $discount * $daysBetweenSettlementAndMaturity);
1700
		}
1701
		return PHPExcel_Calculation_Functions::VALUE();
1702
	}	//	function PRICEDISC()
1703
 
1704
 
1705
	/**
1706
	 * PRICEMAT
1707
	 *
1708
	 * Returns the price per $100 face value of a security that pays interest at maturity.
1709
	 *
1710
	 * @param	mixed	settlement	The security's settlement date.
1711
	 *								The security's settlement date is the date after the issue date when the security is traded to the buyer.
1712
	 * @param	mixed	maturity	The security's maturity date.
1713
	 *								The maturity date is the date when the security expires.
1714
	 * @param	mixed	issue		The security's issue date.
1715
	 * @param	int		rate		The security's interest rate at date of issue.
1716
	 * @param	int		yield		The security's annual yield.
1717
	 * @param	int		basis		The type of day count to use.
1718
	 *										0 or omitted	US (NASD) 30/360
1719
	 *										1				Actual/actual
1720
	 *										2				Actual/360
1721
	 *										3				Actual/365
1722
	 *										4				European 30/360
1723
	 * @return	float
1724
	 */
1725
	public static function PRICEMAT($settlement, $maturity, $issue, $rate, $yield, $basis=0) {
1726
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1727
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1728
		$issue		= PHPExcel_Calculation_Functions::flattenSingleValue($issue);
1729
		$rate		= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1730
		$yield		= PHPExcel_Calculation_Functions::flattenSingleValue($yield);
1731
		$basis		= (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
1732
 
1733
		//	Validate
1734
		if (is_numeric($rate) && is_numeric($yield)) {
1735
			if (($rate <= 0) || ($yield <= 0)) {
1736
				return PHPExcel_Calculation_Functions::NaN();
1737
			}
1738
			$daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
1739
			if (!is_numeric($daysPerYear)) {
1740
				return $daysPerYear;
1741
			}
1742
			$daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
1743
			if (!is_numeric($daysBetweenIssueAndSettlement)) {
1744
				//	return date error
1745
				return $daysBetweenIssueAndSettlement;
1746
			}
1747
			$daysBetweenIssueAndSettlement *= $daysPerYear;
1748
			$daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $maturity, $basis);
1749
			if (!is_numeric($daysBetweenIssueAndMaturity)) {
1750
				//	return date error
1751
				return $daysBetweenIssueAndMaturity;
1752
			}
1753
			$daysBetweenIssueAndMaturity *= $daysPerYear;
1754
			$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
1755
			if (!is_numeric($daysBetweenSettlementAndMaturity)) {
1756
				//	return date error
1757
				return $daysBetweenSettlementAndMaturity;
1758
			}
1759
			$daysBetweenSettlementAndMaturity *= $daysPerYear;
1760
 
1761
			return ((100 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate * 100)) /
1762
				   (1 + (($daysBetweenSettlementAndMaturity / $daysPerYear) * $yield)) -
1763
				   (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate * 100));
1764
		}
1765
		return PHPExcel_Calculation_Functions::VALUE();
1766
	}	//	function PRICEMAT()
1767
 
1768
 
1769
	/**
1770
	 * PV
1771
	 *
1772
	 * Returns the Present Value of a cash flow with constant payments and interest rate (annuities).
1773
	 *
1774
	 * @param	float	$rate	Interest rate per period
1775
	 * @param	int		$nper	Number of periods
1776
	 * @param	float	$pmt	Periodic payment (annuity)
1777
	 * @param	float	$fv		Future Value
1778
	 * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
1779
	 * @return	float
1780
	 */
1781
	public static function PV($rate = 0, $nper = 0, $pmt = 0, $fv = 0, $type = 0) {
1782
		$rate	= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1783
		$nper	= PHPExcel_Calculation_Functions::flattenSingleValue($nper);
1784
		$pmt	= PHPExcel_Calculation_Functions::flattenSingleValue($pmt);
1785
		$fv		= PHPExcel_Calculation_Functions::flattenSingleValue($fv);
1786
		$type	= PHPExcel_Calculation_Functions::flattenSingleValue($type);
1787
 
1788
		// Validate parameters
1789
		if ($type != 0 && $type != 1) {
1790
			return PHPExcel_Calculation_Functions::NaN();
1791
		}
1792
 
1793
		// Calculate
1794
		if (!is_null($rate) && $rate != 0) {
1795
			return (-$pmt * (1 + $rate * $type) * ((pow(1 + $rate, $nper) - 1) / $rate) - $fv) / pow(1 + $rate, $nper);
1796
		} else {
1797
			return -$fv - $pmt * $nper;
1798
		}
1799
	}	//	function PV()
1800
 
1801
 
1802
	/**
1803
	 * RATE
1804
	 *
1805
	 * Returns the interest rate per period of an annuity.
1806
	 * RATE is calculated by iteration and can have zero or more solutions.
1807
	 * If the successive results of RATE do not converge to within 0.0000001 after 20 iterations,
1808
	 * RATE returns the #NUM! error value.
1809
	 *
1810
	 * Excel Function:
1811
	 *		RATE(nper,pmt,pv[,fv[,type[,guess]]])
1812
	 *
1813
	 * @access	public
1814
	 * @category Financial Functions
1815
	 * @param	float	nper		The total number of payment periods in an annuity.
1816
	 * @param	float	pmt			The payment made each period and cannot change over the life
1817
	 *									of the annuity.
1818
	 *								Typically, pmt includes principal and interest but no other
1819
	 *									fees or taxes.
1820
	 * @param	float	pv			The present value - the total amount that a series of future
1821
	 *									payments is worth now.
1822
	 * @param	float	fv			The future value, or a cash balance you want to attain after
1823
	 *									the last payment is made. If fv is omitted, it is assumed
1824
	 *									to be 0 (the future value of a loan, for example, is 0).
1825
	 * @param	integer	type		A number 0 or 1 and indicates when payments are due:
1826
	 *										0 or omitted	At the end of the period.
1827
	 *										1				At the beginning of the period.
1828
	 * @param	float	guess		Your guess for what the rate will be.
1829
	 *									If you omit guess, it is assumed to be 10 percent.
1830
	 * @return	float
1831
	 **/
1832
	public static function RATE($nper, $pmt, $pv, $fv = 0.0, $type = 0, $guess = 0.1) {
1833
		$nper	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
1834
		$pmt	= PHPExcel_Calculation_Functions::flattenSingleValue($pmt);
1835
		$pv		= PHPExcel_Calculation_Functions::flattenSingleValue($pv);
1836
		$fv		= (is_null($fv))	? 0.0	:	PHPExcel_Calculation_Functions::flattenSingleValue($fv);
1837
		$type	= (is_null($type))	? 0		:	(int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
1838
		$guess	= (is_null($guess))	? 0.1	:	PHPExcel_Calculation_Functions::flattenSingleValue($guess);
1839
 
1840
		$rate = $guess;
1841
		if (abs($rate) < FINANCIAL_PRECISION) {
1842
			$y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv;
1843
		} else {
1844
			$f = exp($nper * log(1 + $rate));
1845
			$y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
1846
		}
1847
		$y0 = $pv + $pmt * $nper + $fv;
1848
		$y1 = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
1849
 
1850
		// find root by secant method
1851
		$i  = $x0 = 0.0;
1852
		$x1 = $rate;
1853
		while ((abs($y0 - $y1) > FINANCIAL_PRECISION) && ($i < FINANCIAL_MAX_ITERATIONS)) {
1854
			$rate = ($y1 * $x0 - $y0 * $x1) / ($y1 - $y0);
1855
			$x0 = $x1;
1856
			$x1 = $rate;
1857
			if (($nper * abs($pmt)) > ($pv - $fv))
1858
				$x1 = abs($x1);
1859
 
1860
			if (abs($rate) < FINANCIAL_PRECISION) {
1861
				$y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv;
1862
			} else {
1863
				$f = exp($nper * log(1 + $rate));
1864
				$y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
1865
			}
1866
 
1867
			$y0 = $y1;
1868
			$y1 = $y;
1869
			++$i;
1870
		}
1871
		return $rate;
1872
	}	//	function RATE()
1873
 
1874
 
1875
	/**
1876
	 * RECEIVED
1877
	 *
1878
	 * Returns the price per $100 face value of a discounted security.
1879
	 *
1880
	 * @param	mixed	settlement	The security's settlement date.
1881
	 *								The security settlement date is the date after the issue date when the security is traded to the buyer.
1882
	 * @param	mixed	maturity	The security's maturity date.
1883
	 *								The maturity date is the date when the security expires.
1884
	 * @param	int		investment	The amount invested in the security.
1885
	 * @param	int		discount	The security's discount rate.
1886
	 * @param	int		basis		The type of day count to use.
1887
	 *										0 or omitted	US (NASD) 30/360
1888
	 *										1				Actual/actual
1889
	 *										2				Actual/360
1890
	 *										3				Actual/365
1891
	 *										4				European 30/360
1892
	 * @return	float
1893
	 */
1894
	public static function RECEIVED($settlement, $maturity, $investment, $discount, $basis=0) {
1895
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1896
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1897
		$investment	= (float) PHPExcel_Calculation_Functions::flattenSingleValue($investment);
1898
		$discount	= (float) PHPExcel_Calculation_Functions::flattenSingleValue($discount);
1899
		$basis		= (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
1900
 
1901
		//	Validate
1902
		if ((is_numeric($investment)) && (is_numeric($discount)) && (is_numeric($basis))) {
1903
			if (($investment <= 0) || ($discount <= 0)) {
1904
				return PHPExcel_Calculation_Functions::NaN();
1905
			}
1906
			$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
1907
			if (!is_numeric($daysBetweenSettlementAndMaturity)) {
1908
				//	return date error
1909
				return $daysBetweenSettlementAndMaturity;
1910
			}
1911
 
1912
			return $investment / ( 1 - ($discount * $daysBetweenSettlementAndMaturity));
1913
		}
1914
		return PHPExcel_Calculation_Functions::VALUE();
1915
	}	//	function RECEIVED()
1916
 
1917
 
1918
	/**
1919
	 * SLN
1920
	 *
1921
	 * Returns the straight-line depreciation of an asset for one period
1922
	 *
1923
	 * @param	cost		Initial cost of the asset
1924
	 * @param	salvage		Value at the end of the depreciation
1925
	 * @param	life		Number of periods over which the asset is depreciated
1926
	 * @return	float
1927
	 */
1928
	public static function SLN($cost, $salvage, $life) {
1929
		$cost		= PHPExcel_Calculation_Functions::flattenSingleValue($cost);
1930
		$salvage	= PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
1931
		$life		= PHPExcel_Calculation_Functions::flattenSingleValue($life);
1932
 
1933
		// Calculate
1934
		if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life))) {
1935
			if ($life < 0) {
1936
				return PHPExcel_Calculation_Functions::NaN();
1937
			}
1938
			return ($cost - $salvage) / $life;
1939
		}
1940
		return PHPExcel_Calculation_Functions::VALUE();
1941
	}	//	function SLN()
1942
 
1943
 
1944
	/**
1945
	 * SYD
1946
	 *
1947
	 * Returns the sum-of-years' digits depreciation of an asset for a specified period.
1948
	 *
1949
	 * @param	cost		Initial cost of the asset
1950
	 * @param	salvage		Value at the end of the depreciation
1951
	 * @param	life		Number of periods over which the asset is depreciated
1952
	 * @param	period		Period
1953
	 * @return	float
1954
	 */
1955
	public static function SYD($cost, $salvage, $life, $period) {
1956
		$cost		= PHPExcel_Calculation_Functions::flattenSingleValue($cost);
1957
		$salvage	= PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
1958
		$life		= PHPExcel_Calculation_Functions::flattenSingleValue($life);
1959
		$period		= PHPExcel_Calculation_Functions::flattenSingleValue($period);
1960
 
1961
		// Calculate
1962
		if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period))) {
1963
			if (($life < 1) || ($period > $life)) {
1964
				return PHPExcel_Calculation_Functions::NaN();
1965
			}
1966
			return (($cost - $salvage) * ($life - $period + 1) * 2) / ($life * ($life + 1));
1967
		}
1968
		return PHPExcel_Calculation_Functions::VALUE();
1969
	}	//	function SYD()
1970
 
1971
 
1972
	/**
1973
	 * TBILLEQ
1974
	 *
1975
	 * Returns the bond-equivalent yield for a Treasury bill.
1976
	 *
1977
	 * @param	mixed	settlement	The Treasury bill's settlement date.
1978
	 *								The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
1979
	 * @param	mixed	maturity	The Treasury bill's maturity date.
1980
	 *								The maturity date is the date when the Treasury bill expires.
1981
	 * @param	int		discount	The Treasury bill's discount rate.
1982
	 * @return	float
1983
	 */
1984
	public static function TBILLEQ($settlement, $maturity, $discount) {
1985
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1986
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1987
		$discount	= PHPExcel_Calculation_Functions::flattenSingleValue($discount);
1988
 
1989
		//	Use TBILLPRICE for validation
1990
		$testValue = self::TBILLPRICE($settlement, $maturity, $discount);
1991
		if (is_string($testValue)) {
1992
			return $testValue;
1993
		}
1994
 
1995
		if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
1996
			return PHPExcel_Calculation_Functions::VALUE();
1997
		}
1998
 
1999
		if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
2000
			++$maturity;
2001
			$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity) * 360;
2002
		} else {
2003
			$daysBetweenSettlementAndMaturity = (PHPExcel_Calculation_DateTime::_getDateValue($maturity) - PHPExcel_Calculation_DateTime::_getDateValue($settlement));
2004
		}
2005
 
2006
		return (365 * $discount) / (360 - $discount * $daysBetweenSettlementAndMaturity);
2007
	}	//	function TBILLEQ()
2008
 
2009
 
2010
	/**
2011
	 * TBILLPRICE
2012
	 *
2013
	 * Returns the yield for a Treasury bill.
2014
	 *
2015
	 * @param	mixed	settlement	The Treasury bill's settlement date.
2016
	 *								The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
2017
	 * @param	mixed	maturity	The Treasury bill's maturity date.
2018
	 *								The maturity date is the date when the Treasury bill expires.
2019
	 * @param	int		discount	The Treasury bill's discount rate.
2020
	 * @return	float
2021
	 */
2022
	public static function TBILLPRICE($settlement, $maturity, $discount) {
2023
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
2024
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
2025
		$discount	= PHPExcel_Calculation_Functions::flattenSingleValue($discount);
2026
 
2027
		if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
2028
			return PHPExcel_Calculation_Functions::VALUE();
2029
		}
2030
 
2031
		//	Validate
2032
		if (is_numeric($discount)) {
2033
			if ($discount <= 0) {
2034
				return PHPExcel_Calculation_Functions::NaN();
2035
			}
2036
 
2037
			if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
2038
				++$maturity;
2039
				$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity) * 360;
2040
				if (!is_numeric($daysBetweenSettlementAndMaturity)) {
2041
					//	return date error
2042
					return $daysBetweenSettlementAndMaturity;
2043
				}
2044
			} else {
2045
				$daysBetweenSettlementAndMaturity = (PHPExcel_Calculation_DateTime::_getDateValue($maturity) - PHPExcel_Calculation_DateTime::_getDateValue($settlement));
2046
			}
2047
 
2048
			if ($daysBetweenSettlementAndMaturity > 360) {
2049
				return PHPExcel_Calculation_Functions::NaN();
2050
			}
2051
 
2052
			$price = 100 * (1 - (($discount * $daysBetweenSettlementAndMaturity) / 360));
2053
			if ($price <= 0) {
2054
				return PHPExcel_Calculation_Functions::NaN();
2055
			}
2056
			return $price;
2057
		}
2058
		return PHPExcel_Calculation_Functions::VALUE();
2059
	}	//	function TBILLPRICE()
2060
 
2061
 
2062
	/**
2063
	 * TBILLYIELD
2064
	 *
2065
	 * Returns the yield for a Treasury bill.
2066
	 *
2067
	 * @param	mixed	settlement	The Treasury bill's settlement date.
2068
	 *								The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
2069
	 * @param	mixed	maturity	The Treasury bill's maturity date.
2070
	 *								The maturity date is the date when the Treasury bill expires.
2071
	 * @param	int		price		The Treasury bill's price per $100 face value.
2072
	 * @return	float
2073
	 */
2074
	public static function TBILLYIELD($settlement, $maturity, $price) {
2075
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
2076
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
2077
		$price		= PHPExcel_Calculation_Functions::flattenSingleValue($price);
2078
 
2079
		//	Validate
2080
		if (is_numeric($price)) {
2081
			if ($price <= 0) {
2082
				return PHPExcel_Calculation_Functions::NaN();
2083
			}
2084
 
2085
			if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
2086
				++$maturity;
2087
				$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity) * 360;
2088
				if (!is_numeric($daysBetweenSettlementAndMaturity)) {
2089
					//	return date error
2090
					return $daysBetweenSettlementAndMaturity;
2091
				}
2092
			} else {
2093
				$daysBetweenSettlementAndMaturity = (PHPExcel_Calculation_DateTime::_getDateValue($maturity) - PHPExcel_Calculation_DateTime::_getDateValue($settlement));
2094
			}
2095
 
2096
			if ($daysBetweenSettlementAndMaturity > 360) {
2097
				return PHPExcel_Calculation_Functions::NaN();
2098
			}
2099
 
2100
			return ((100 - $price) / $price) * (360 / $daysBetweenSettlementAndMaturity);
2101
		}
2102
		return PHPExcel_Calculation_Functions::VALUE();
2103
	}	//	function TBILLYIELD()
2104
 
2105
 
2106
	public static function XIRR($values, $dates, $guess = 0.1) {
2107
		if ((!is_array($values)) && (!is_array($dates))) return PHPExcel_Calculation_Functions::VALUE();
2108
		$values	= PHPExcel_Calculation_Functions::flattenArray($values);
2109
		$dates	= PHPExcel_Calculation_Functions::flattenArray($dates);
2110
		$guess = PHPExcel_Calculation_Functions::flattenSingleValue($guess);
2111
		if (count($values) != count($dates)) return PHPExcel_Calculation_Functions::NaN();
2112
 
2113
		// create an initial range, with a root somewhere between 0 and guess
2114
		$x1 = 0.0;
2115
		$x2 = $guess;
2116
		$f1 = self::XNPV($x1, $values, $dates);
2117
		$f2 = self::XNPV($x2, $values, $dates);
2118
		for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
2119
			if (($f1 * $f2) < 0.0) break;
2120
			if (abs($f1) < abs($f2)) {
2121
				$f1 = self::XNPV($x1 += 1.6 * ($x1 - $x2), $values, $dates);
2122
			} else {
2123
				$f2 = self::XNPV($x2 += 1.6 * ($x2 - $x1), $values, $dates);
2124
			}
2125
		}
2126
		if (($f1 * $f2) > 0.0) return PHPExcel_Calculation_Functions::VALUE();
2127
 
2128
		$f = self::XNPV($x1, $values, $dates);
2129
		if ($f < 0.0) {
2130
			$rtb = $x1;
2131
			$dx = $x2 - $x1;
2132
		} else {
2133
			$rtb = $x2;
2134
			$dx = $x1 - $x2;
2135
		}
2136
 
2137
		for ($i = 0;  $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
2138
			$dx *= 0.5;
2139
			$x_mid = $rtb + $dx;
2140
			$f_mid = self::XNPV($x_mid, $values, $dates);
2141
			if ($f_mid <= 0.0) $rtb = $x_mid;
2142
			if ((abs($f_mid) < FINANCIAL_PRECISION) || (abs($dx) < FINANCIAL_PRECISION)) return $x_mid;
2143
		}
2144
		return PHPExcel_Calculation_Functions::VALUE();
2145
	}
2146
 
2147
 
2148
	/**
2149
	 * XNPV
2150
	 *
2151
	 * Returns the net present value for a schedule of cash flows that is not necessarily periodic.
2152
	 * To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
2153
	 *
2154
	 * Excel Function:
2155
	 *		=XNPV(rate,values,dates)
2156
	 *
2157
	 * @param	float			$rate		The discount rate to apply to the cash flows.
2158
	 * @param	array of float	$values		A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive value and one negative value.
2159
	 * @param	array of mixed	$dates		A schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order.
2160
	 * @return	float
2161
	 */
2162
	public static function XNPV($rate, $values, $dates) {
2163
		$rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
2164
		if (!is_numeric($rate)) return PHPExcel_Calculation_Functions::VALUE();
2165
		if ((!is_array($values)) || (!is_array($dates))) return PHPExcel_Calculation_Functions::VALUE();
2166
		$values	= PHPExcel_Calculation_Functions::flattenArray($values);
2167
		$dates	= PHPExcel_Calculation_Functions::flattenArray($dates);
2168
		$valCount = count($values);
2169
		if ($valCount != count($dates)) return PHPExcel_Calculation_Functions::NaN();
2170
		if ((min($values) > 0) || (max($values) < 0)) return PHPExcel_Calculation_Functions::VALUE();
2171
 
2172
		$xnpv = 0.0;
2173
		for ($i = 0; $i < $valCount; ++$i) {
2174
			if (!is_numeric($values[$i])) return PHPExcel_Calculation_Functions::VALUE();
2175
			$xnpv += $values[$i] / pow(1 + $rate, PHPExcel_Calculation_DateTime::DATEDIF($dates[0],$dates[$i],'d') / 365);
2176
		}
2177
		return (is_finite($xnpv)) ? $xnpv : PHPExcel_Calculation_Functions::VALUE();
2178
	}	//	function XNPV()
2179
 
2180
 
2181
	/**
2182
	 * YIELDDISC
2183
	 *
2184
	 * Returns the annual yield of a security that pays interest at maturity.
2185
	 *
2186
	 * @param	mixed	settlement	The security's settlement date.
2187
	 *								The security's settlement date is the date after the issue date when the security is traded to the buyer.
2188
	 * @param	mixed	maturity	The security's maturity date.
2189
	 *								The maturity date is the date when the security expires.
2190
	 * @param	int		price		The security's price per $100 face value.
2191
	 * @param	int		redemption	The security's redemption value per $100 face value.
2192
	 * @param	int		basis		The type of day count to use.
2193
	 *										0 or omitted	US (NASD) 30/360
2194
	 *										1				Actual/actual
2195
	 *										2				Actual/360
2196
	 *										3				Actual/365
2197
	 *										4				European 30/360
2198
	 * @return	float
2199
	 */
2200
	public static function YIELDDISC($settlement, $maturity, $price, $redemption, $basis=0) {
2201
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
2202
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
2203
		$price		= PHPExcel_Calculation_Functions::flattenSingleValue($price);
2204
		$redemption	= PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
2205
		$basis		= (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
2206
 
2207
		//	Validate
2208
		if (is_numeric($price) && is_numeric($redemption)) {
2209
			if (($price <= 0) || ($redemption <= 0)) {
2210
				return PHPExcel_Calculation_Functions::NaN();
2211
			}
2212
			$daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
2213
			if (!is_numeric($daysPerYear)) {
2214
				return $daysPerYear;
2215
			}
2216
			$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity,$basis);
2217
			if (!is_numeric($daysBetweenSettlementAndMaturity)) {
2218
				//	return date error
2219
				return $daysBetweenSettlementAndMaturity;
2220
			}
2221
			$daysBetweenSettlementAndMaturity *= $daysPerYear;
2222
 
2223
			return (($redemption - $price) / $price) * ($daysPerYear / $daysBetweenSettlementAndMaturity);
2224
		}
2225
		return PHPExcel_Calculation_Functions::VALUE();
2226
	}	//	function YIELDDISC()
2227
 
2228
 
2229
	/**
2230
	 * YIELDMAT
2231
	 *
2232
	 * Returns the annual yield of a security that pays interest at maturity.
2233
	 *
2234
	 * @param	mixed	settlement	The security's settlement date.
2235
	 *								The security's settlement date is the date after the issue date when the security is traded to the buyer.
2236
	 * @param	mixed	maturity	The security's maturity date.
2237
	 *								The maturity date is the date when the security expires.
2238
	 * @param	mixed	issue		The security's issue date.
2239
	 * @param	int		rate		The security's interest rate at date of issue.
2240
	 * @param	int		price		The security's price per $100 face value.
2241
	 * @param	int		basis		The type of day count to use.
2242
	 *										0 or omitted	US (NASD) 30/360
2243
	 *										1				Actual/actual
2244
	 *										2				Actual/360
2245
	 *										3				Actual/365
2246
	 *										4				European 30/360
2247
	 * @return	float
2248
	 */
2249
	public static function YIELDMAT($settlement, $maturity, $issue, $rate, $price, $basis=0) {
2250
		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
2251
		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
2252
		$issue		= PHPExcel_Calculation_Functions::flattenSingleValue($issue);
2253
		$rate		= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
2254
		$price		= PHPExcel_Calculation_Functions::flattenSingleValue($price);
2255
		$basis		= (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
2256
 
2257
		//	Validate
2258
		if (is_numeric($rate) && is_numeric($price)) {
2259
			if (($rate <= 0) || ($price <= 0)) {
2260
				return PHPExcel_Calculation_Functions::NaN();
2261
			}
2262
			$daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
2263
			if (!is_numeric($daysPerYear)) {
2264
				return $daysPerYear;
2265
			}
2266
			$daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
2267
			if (!is_numeric($daysBetweenIssueAndSettlement)) {
2268
				//	return date error
2269
				return $daysBetweenIssueAndSettlement;
2270
			}
2271
			$daysBetweenIssueAndSettlement *= $daysPerYear;
2272
			$daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $maturity, $basis);
2273
			if (!is_numeric($daysBetweenIssueAndMaturity)) {
2274
				//	return date error
2275
				return $daysBetweenIssueAndMaturity;
2276
			}
2277
			$daysBetweenIssueAndMaturity *= $daysPerYear;
2278
			$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
2279
			if (!is_numeric($daysBetweenSettlementAndMaturity)) {
2280
				//	return date error
2281
				return $daysBetweenSettlementAndMaturity;
2282
			}
2283
			$daysBetweenSettlementAndMaturity *= $daysPerYear;
2284
 
2285
			return ((1 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate) - (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) /
2286
				   (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) *
2287
				   ($daysPerYear / $daysBetweenSettlementAndMaturity);
2288
		}
2289
		return PHPExcel_Calculation_Functions::VALUE();
2290
	}	//	function YIELDMAT()
2291
 
2292
}	//	class PHPExcel_Calculation_Financial