PageRenderTime 67ms CodeModel.GetById 25ms app.highlight 32ms RepoModel.GetById 1ms app.codeStats 1ms

/protected/extensions/phpexcel/Classes/PHPExcel/Calculation/Financial.php

https://bitbucket.org/nochallenge/exlarlistprice
PHP | 2248 lines | 1057 code | 233 blank | 958 comment | 319 complexity | 6c68dc487400cd144638d3106f1dadb6 MD5 | raw file

Large files files are truncated, but you can click here to view the full file

   1<?php
   2/**
   3 * PHPExcel
   4 *
   5 * Copyright (c) 2006 - 2012 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 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  24 * @license		http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
  25 * @version		1.7.8, 2012-10-12
  26 */
  27
  28
  29/** PHPExcel root directory */
  30if (!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 */
  40define('FINANCIAL_MAX_ITERATIONS', 128);
  41
  42/** FINANCIAL_PRECISION */
  43define('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 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  52 */
  53class 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	 */
1210	public static function FVSCHEDULE($principal, $schedule) {
1211		$principal	= PHPExcel_Calculation_Functions::flattenSingleValue($principal);
1212		$schedule	= PHPExcel_Calculation_Functions::flattenArray($schedule);
1213
1214		foreach($schedule as $n) {
1215			$principal *= 1 + $n;
1216		}
1217
1218		return $principal;
1219	}	//	function FVSCHEDULE()
1220
1221
1222	/**
1223	 * INTRATE
1224	 *
1225	 * Returns the interest rate for a fully invested security.
1226	 *
1227	 * Excel Function:
1228	 *		INTRATE(settlement,maturity,investment,redemption[,basis])
1229	 *
1230	 * @param	mixed	settlement	The security's settlement date.
1231	 *								The security settlement date is the date after the issue date when the security is traded to the buyer.
1232	 * @param	mixed	maturity	The security's maturity date.
1233	 *								The maturity date is the date when the security expires.
1234	 * @param	integer	investment	The amount invested in the security.
1235	 * @param	integer	redemption	The amount to be received at maturity.
1236	 * @param	integer	basis		The type of day count to use.
1237	 *										0 or omitted	US (NASD) 30/360
1238	 *										1				Actual/actual
1239	 *										2				Actual/360
1240	 *										3				Actual/365
1241	 *										4				European 30/360
1242	 * @return	float
1243	 */
1244	public static function INTRATE($settlement, $maturity, $investment, $redemption, $basis=0) {
1245		$settlement	= PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1246		$maturity	= PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1247		$investment	= PHPExcel_Calculation_Functions::flattenSingleValue($investment);
1248		$redemption	= PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
1249		$basis		= PHPExcel_Calculation_Functions::flattenSingleValue($basis);
1250
1251		//	Validate
1252		if ((is_numeric($investment)) && (is_numeric($redemption)) && (is_numeric($basis))) {
1253			$investment	= (float) $investment;
1254			$redemption	= (float) $redemption;
1255			$basis		= (int) $basis;
1256			if (($investment <= 0) || ($redemption <= 0)) {
1257				return PHPExcel_Calculation_Functions::NaN();
1258			}
1259			$daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
1260			if (!is_numeric($daysBetweenSettlementAndMaturity)) {
1261				//	return date error
1262				return $daysBetweenSettlementAndMaturity;
1263			}
1264
1265			return (($redemption / $investment) - 1) / ($daysBetweenSettlementAndMaturity);
1266		}
1267		return PHPExcel_Calculation_Functions::VALUE();
1268	}	//	function INTRATE()
1269
1270
1271	/**
1272	 * IPMT
1273	 *
1274	 * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
1275	 *
1276	 * @param	float	$rate	Interest rate per period
1277	 * @param	int		$per	Period for which we want to find the interest
1278	 * @param	int		$nper	Number of periods
1279	 * @param	float	$pv		Present Value
1280	 * @param	float	$fv		Future Value
1281	 * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
1282	 * @return	float
1283	 */
1284	public static function IPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
1285		$rate	= PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1286		$per	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($per);
1287		$nper	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
1288		$pv		= PHPExcel_Calculation_Functions::flattenSingleValue($pv);
1289		$fv		= PHPExcel_Calculation_Functions::flattenSingleValue($fv);
1290		$type	= (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
1291
1292		// Validate parameters
1293		if ($type != 0 && $type != 1) {
1294			return PHPExcel_Calculation_Functions::NaN();
1295		}
1296		if ($per <= 0 || $per > $nper) {
1297			return PHPExcel_Calculation_Functions::VALUE();
1298		}
1299
1300		// Calculate
1301		$interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
1302		return $interestAndPrincipal[0];
1303	}	//	function IPMT()
1304
1305
1306	public static function IRR($values, $guess = 0.1) {
1307		if (!is_array($values)) return PHPExcel_Calculation_Functions::VALUE();
1308		$values = PHPExcel_Calculation_Functions::flatteā€¦

Large files files are truncated, but you can click here to view the full file