Context Navigation

← Previous Revision
Latest Revision
Next Revision →
Blame
Revision Log

Financial.php @ 461

Last change on this file since 461 was 289, checked in by dungnv, 11 years ago

File size: 85.6 KB

Line
1	<?php
2	/**
3	* PHPExcel
4	*
5	* Copyright (c) 2006 - 2014 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 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
24	* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25	* @version 1.8.0, 2014-03-02
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 - 2014 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

Note: See TracBrowser for help on using the repository browser.

Context Navigation

source: pro-violet-viettel/sourcecode/application/libraries/PHPExcel/Calculation/Financial.php @ 461

Download in other formats: