ILIAS  release_5-2 Revision v5.2.25-18-g3f80b828510
Static Public Member Functions | Static Private Member Functions
PHPExcel_Calculation_Financial Class Reference
+ Collaboration diagram for PHPExcel_Calculation_Financial:

Static Public Member Functions

static ACCRINT ($issue, $firstinterest, $settlement, $rate, $par=1000, $frequency=1, $basis=0)
 
static ACCRINTM ($issue, $settlement, $rate, $par=1000, $basis=0)
 
static AMORDEGRC ($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis=0)
 
static AMORLINC ($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis=0)
 
static COUPDAYBS ($settlement, $maturity, $frequency, $basis=0)
 
static COUPDAYS ($settlement, $maturity, $frequency, $basis=0)
 
static COUPDAYSNC ($settlement, $maturity, $frequency, $basis=0)
 
static COUPNCD ($settlement, $maturity, $frequency, $basis=0)
 
static COUPNUM ($settlement, $maturity, $frequency, $basis=0)
 
static COUPPCD ($settlement, $maturity, $frequency, $basis=0)
 
static CUMIPMT ($rate, $nper, $pv, $start, $end, $type=0)
 
static CUMPRINC ($rate, $nper, $pv, $start, $end, $type=0)
 
static DB ($cost, $salvage, $life, $period, $month=12)
 
static DDB ($cost, $salvage, $life, $period, $factor=2.0)
 
static DISC ($settlement, $maturity, $price, $redemption, $basis=0)
 
static DOLLARDE ($fractional_dollar=Null, $fraction=0)
 
static DOLLARFR ($decimal_dollar=Null, $fraction=0)
 
static EFFECT ($nominal_rate=0, $npery=0)
 
static FV ($rate=0, $nper=0, $pmt=0, $pv=0, $type=0)
 
static FVSCHEDULE ($principal, $schedule)
 FVSCHEDULE. More...
 
static INTRATE ($settlement, $maturity, $investment, $redemption, $basis=0)
 INTRATE. More...
 
static IPMT ($rate, $per, $nper, $pv, $fv=0, $type=0)
 IPMT. More...
 
static IRR ($values, $guess=0.1)
 IRR. More...
 
static ISPMT ()
 ISPMT. More...
 
static MIRR ($values, $finance_rate, $reinvestment_rate)
 MIRR. More...
 
static NOMINAL ($effect_rate=0, $npery=0)
 NOMINAL. More...
 
static NPER ($rate=0, $pmt=0, $pv=0, $fv=0, $type=0)
 NPER. More...
 
static NPV ()
 NPV. More...
 
static PMT ($rate=0, $nper=0, $pv=0, $fv=0, $type=0)
 PMT. More...
 
static PPMT ($rate, $per, $nper, $pv, $fv=0, $type=0)
 PPMT. More...
 
static PRICE ($settlement, $maturity, $rate, $yield, $redemption, $frequency, $basis=0)
 
static PRICEDISC ($settlement, $maturity, $discount, $redemption, $basis=0)
 PRICEDISC. More...
 
static PRICEMAT ($settlement, $maturity, $issue, $rate, $yield, $basis=0)
 PRICEMAT. More...
 
static PV ($rate=0, $nper=0, $pmt=0, $fv=0, $type=0)
 PV. More...
 
static RATE ($nper, $pmt, $pv, $fv=0.0, $type=0, $guess=0.1)
 
static RECEIVED ($settlement, $maturity, $investment, $discount, $basis=0)
 RECEIVED. More...
 
static SLN ($cost, $salvage, $life)
 SLN. More...
 
static SYD ($cost, $salvage, $life, $period)
 SYD. More...
 
static TBILLEQ ($settlement, $maturity, $discount)
 TBILLEQ. More...
 
static TBILLPRICE ($settlement, $maturity, $discount)
 TBILLPRICE. More...
 
static TBILLYIELD ($settlement, $maturity, $price)
 TBILLYIELD. More...
 
static XIRR ($values, $dates, $guess=0.1)
 
static XNPV ($rate, $values, $dates)
 XNPV. More...
 
static YIELDDISC ($settlement, $maturity, $price, $redemption, $basis=0)
 YIELDDISC. More...
 
static YIELDMAT ($settlement, $maturity, $issue, $rate, $price, $basis=0)
 YIELDMAT. More...
 

Static Private Member Functions

static _lastDayOfMonth ($testDate)
 _lastDayOfMonth More...
 
static _firstDayOfMonth ($testDate)
 _firstDayOfMonth More...
 
static _coupFirstPeriodDate ($settlement, $maturity, $frequency, $next)
 
static _validFrequency ($frequency)
 
static _daysPerYear ($year, $basis=0)
 _daysPerYear More...
 
static _interestAndPrincipal ($rate=0, $per=0, $nper=0, $pv=0, $fv=0, $type=0)
 

Detailed Description

Definition at line 53 of file Financial.php.

Member Function Documentation

◆ _coupFirstPeriodDate()

static PHPExcel_Calculation_Financial::_coupFirstPeriodDate (   $settlement,
  $maturity,
  $frequency,
  $next 
)
staticprivate

Definition at line 83 of file Financial.php.

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()
$result
$result
Definition: CleanUpTest.php:407
PHPExcel_Calculation_Financial\_lastDayOfMonth
static _lastDayOfMonth($testDate)
_lastDayOfMonth
Definition: Financial.php:63
PHPExcel_Shared_Date\PHPToExcel
static PHPToExcel($dateValue=0, $adjustToTimezone=FALSE, $timezone=NULL)
Convert a date from PHP to Excel.
Definition: Date.php:185
PHPExcel_Shared_Date\ExcelToPHPObject
static ExcelToPHPObject($dateValue=0)
Convert a date from Excel to a PHP Date/Time object.
Definition: Date.php:160

References $result, _lastDayOfMonth(), PHPExcel_Shared_Date\ExcelToPHPObject(), and PHPExcel_Shared_Date\PHPToExcel().

Referenced by COUPDAYBS(), COUPDAYS(), COUPDAYSNC(), COUPNCD(), COUPNUM(), and COUPPCD().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ _daysPerYear()

static PHPExcel_Calculation_Financial::_daysPerYear (   $year,
  $basis = 0 
)
staticprivate

_daysPerYear

Returns the number of days in a specified year, as defined by the "basis" value

Parameters
integer$yearThe year against which we're testing
integer$basisThe type of day count: 0 or omitted US (NASD) 360 1 Actual (365 or 366 in a leap year) 2 360 3 365 4 European 360
Returns
integer

Definition at line 132 of file Financial.php.

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()
PHPExcel_Calculation_DateTime\_isLeapYear
static _isLeapYear($year)
Identify if a year is a leap year or not.
Definition: DateTime.php:54
PHPExcel_Calculation_Functions\NaN
static NaN()
Definition: Functions.php:233

References PHPExcel_Calculation_DateTime\_isLeapYear(), and PHPExcel_Calculation_Functions\NaN().

Referenced by COUPDAYBS(), COUPDAYS(), COUPDAYSNC(), PRICEMAT(), YIELDDISC(), and YIELDMAT().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ _firstDayOfMonth()

static PHPExcel_Calculation_Financial::_firstDayOfMonth (   $testDate)
staticprivate

_firstDayOfMonth

Returns a boolean TRUE/FALSE indicating if this date is the first date of the month

Parameters
DateTime$testDateThe date for testing
Returns
boolean

Definition at line 77 of file Financial.php.

78 {
79 return ($testDate->format('d') == 1);
80 } // function _firstDayOfMonth()

◆ _interestAndPrincipal()

static PHPExcel_Calculation_Financial::_interestAndPrincipal (   $rate = 0,
  $per = 0,
  $nper = 0,
  $pv = 0,
  $fv = 0,
  $type = 0 
)
staticprivate

Definition at line 153 of file Financial.php.

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()
PHPExcel_Calculation_Financial\PMT
static PMT($rate=0, $nper=0, $pv=0, $fv=0, $type=0)
PMT.
Definition: Financial.php:1565

References PMT().

Referenced by IPMT(), and PPMT().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ _lastDayOfMonth()

static PHPExcel_Calculation_Financial::_lastDayOfMonth (   $testDate)
staticprivate

_lastDayOfMonth

Returns a boolean TRUE/FALSE indicating if this date is the last date of the month

Parameters
DateTime$testDateThe date for testing
Returns
boolean

Definition at line 63 of file Financial.php.

64 {
65 return ($testDate->format('d') == $testDate->format('t'));
66 } // function _lastDayOfMonth()

Referenced by _coupFirstPeriodDate().

+ Here is the caller graph for this function:

◆ _validFrequency()

static PHPExcel_Calculation_Financial::_validFrequency (   $frequency)
staticprivate

Definition at line 105 of file Financial.php.

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()
PHPExcel_Calculation_Functions\getCompatibilityMode
static getCompatibilityMode()
Definition: Functions.php:138
PHPExcel_Calculation_Functions\COMPATIBILITY_GNUMERIC
const COMPATIBILITY_GNUMERIC
Definition: Functions.php:63

References PHPExcel_Calculation_Functions\COMPATIBILITY_GNUMERIC, and PHPExcel_Calculation_Functions\getCompatibilityMode().

+ Here is the call graph for this function:

◆ ACCRINT()

static PHPExcel_Calculation_Financial::ACCRINT (   $issue,
  $firstinterest,
  $settlement,
  $rate,
  $par = 1000,
  $frequency = 1,
  $basis = 0 
)
static

Definition at line 201 of file Financial.php.

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()
PHPExcel_Calculation_DateTime\YEARFRAC
static YEARFRAC($startDate=0, $endDate=0, $method=0)
Definition: DateTime.php:791
PHPExcel_Calculation_Functions\flattenSingleValue
static flattenSingleValue($value='')
Convert an array to a single scalar value by extracting the first element.
Definition: Functions.php:662
PHPExcel_Calculation_Functions\VALUE
static VALUE()
Definition: Functions.php:289

References PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), and PHPExcel_Calculation_DateTime\YEARFRAC().

+ Here is the call graph for this function:

◆ ACCRINTM()

static PHPExcel_Calculation_Financial::ACCRINTM (   $issue,
  $settlement,
  $rate,
  $par = 1000,
  $basis = 0 
)
static

Definition at line 253 of file Financial.php.

253 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), and PHPExcel_Calculation_DateTime\YEARFRAC().

+ Here is the call graph for this function:

◆ AMORDEGRC()

static PHPExcel_Calculation_Financial::AMORDEGRC (   $cost,
  $purchased,
  $firstPeriod,
  $salvage,
  $period,
  $rate,
  $basis = 0 
)
static

Definition at line 309 of file Financial.php.

309 {
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()
$n
$n
Definition: RandomTest.php:80

References $n, PHPExcel_Calculation_Functions\flattenSingleValue(), and PHPExcel_Calculation_DateTime\YEARFRAC().

+ Here is the call graph for this function:

◆ AMORLINC()

static PHPExcel_Calculation_Financial::AMORLINC (   $cost,
  $purchased,
  $firstPeriod,
  $salvage,
  $period,
  $rate,
  $basis = 0 
)
static

Definition at line 385 of file Financial.php.

385 {
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()
PHPExcel_Calculation_DateTime\YEAR
static YEAR($dateValue=1)
YEAR.
Definition: DateTime.php:1253

References PHPExcel_Calculation_DateTime\_isLeapYear(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_DateTime\YEAR(), and PHPExcel_Calculation_DateTime\YEARFRAC().

+ Here is the call graph for this function:

◆ COUPDAYBS()

static PHPExcel_Calculation_Financial::COUPDAYBS (   $settlement,
  $maturity,
  $frequency,
  $basis = 0 
)
static

Definition at line 451 of file Financial.php.

451 {
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()
PHPExcel_Calculation_DateTime\_getDateValue
static _getDateValue($dateValue)
_getDateValue
Definition: DateTime.php:101
PHPExcel_Calculation_Financial\_daysPerYear
static _daysPerYear($year, $basis=0)
_daysPerYear
Definition: Financial.php:132
PHPExcel_Calculation_Financial\_coupFirstPeriodDate
static _coupFirstPeriodDate($settlement, $maturity, $frequency, $next)
Definition: Financial.php:83

References _coupFirstPeriodDate(), _daysPerYear(), PHPExcel_Calculation_DateTime\_getDateValue(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), PHPExcel_Calculation_DateTime\YEAR(), and PHPExcel_Calculation_DateTime\YEARFRAC().

Referenced by PRICE().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ COUPDAYS()

static PHPExcel_Calculation_Financial::COUPDAYS (   $settlement,
  $maturity,
  $frequency,
  $basis = 0 
)
static

Definition at line 509 of file Financial.php.

509 {
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()

References _coupFirstPeriodDate(), _daysPerYear(), PHPExcel_Calculation_DateTime\_getDateValue(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), and PHPExcel_Calculation_DateTime\YEAR().

Referenced by PRICE().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ COUPDAYSNC()

static PHPExcel_Calculation_Financial::COUPDAYSNC (   $settlement,
  $maturity,
  $frequency,
  $basis = 0 
)
static

Definition at line 579 of file Financial.php.

579 {
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()

References _coupFirstPeriodDate(), _daysPerYear(), PHPExcel_Calculation_DateTime\_getDateValue(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), PHPExcel_Calculation_DateTime\YEAR(), and PHPExcel_Calculation_DateTime\YEARFRAC().

Referenced by PRICE().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ COUPNCD()

static PHPExcel_Calculation_Financial::COUPNCD (   $settlement,
  $maturity,
  $frequency,
  $basis = 0 
)
static

Definition at line 638 of file Financial.php.

638 {
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()

References _coupFirstPeriodDate(), PHPExcel_Calculation_DateTime\_getDateValue(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ COUPNUM()

static PHPExcel_Calculation_Financial::COUPNUM (   $settlement,
  $maturity,
  $frequency,
  $basis = 0 
)
static

Definition at line 694 of file Financial.php.

694 {
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()

References _coupFirstPeriodDate(), PHPExcel_Calculation_DateTime\_getDateValue(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), and PHPExcel_Calculation_DateTime\YEARFRAC().

Referenced by PRICE().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ COUPPCD()

static PHPExcel_Calculation_Financial::COUPPCD (   $settlement,
  $maturity,
  $frequency,
  $basis = 0 
)
static

Definition at line 765 of file Financial.php.

765 {
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()

References _coupFirstPeriodDate(), PHPExcel_Calculation_DateTime\_getDateValue(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ CUMIPMT()

static PHPExcel_Calculation_Financial::CUMIPMT (   $rate,
  $nper,
  $pv,
  $start,
  $end,
  $type = 0 
)
static

Definition at line 809 of file Financial.php.

809 {
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()
PHPExcel_Calculation_Financial\IPMT
static IPMT($rate, $per, $nper, $pv, $fv=0, $type=0)
IPMT.
Definition: Financial.php:1296

References $start, PHPExcel_Calculation_Functions\flattenSingleValue(), IPMT(), PHPExcel_Calculation_Functions\NaN(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ CUMPRINC()

static PHPExcel_Calculation_Financial::CUMPRINC (   $rate,
  $nper,
  $pv,
  $start,
  $end,
  $type = 0 
)
static

Definition at line 856 of file Financial.php.

856 {
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()
PHPExcel_Calculation_Financial\PPMT
static PPMT($rate, $per, $nper, $pv, $fv=0, $type=0)
PPMT.
Definition: Financial.php:1599

References $start, PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PPMT(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ DB()

static PHPExcel_Calculation_Financial::DB (   $cost,
  $salvage,
  $life,
  $period,
  $month = 12 
)
static

Definition at line 908 of file Financial.php.

908 {
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()

References PHPExcel_Calculation_Functions\COMPATIBILITY_GNUMERIC, PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\getCompatibilityMode(), PHPExcel_Calculation_Functions\NaN(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ DDB()

static PHPExcel_Calculation_Financial::DDB (   $cost,
  $salvage,
  $life,
  $period,
  $factor = 2.0 
)
static

Definition at line 975 of file Financial.php.

975 {
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()

References PHPExcel_Calculation_Functions\COMPATIBILITY_GNUMERIC, PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\getCompatibilityMode(), PHPExcel_Calculation_Functions\NaN(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ DISC()

static PHPExcel_Calculation_Financial::DISC (   $settlement,
  $maturity,
  $price,
  $redemption,
  $basis = 0 
)
static

Definition at line 1036 of file Financial.php.

1036 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), and PHPExcel_Calculation_DateTime\YEARFRAC().

+ Here is the call graph for this function:

◆ DOLLARDE()

static PHPExcel_Calculation_Financial::DOLLARDE (   $fractional_dollar = Null,
  $fraction = 0 
)
static

Definition at line 1079 of file Financial.php.

1079 {
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()
PHPExcel_Calculation_Functions\DIV0
static DIV0()
Definition: Functions.php:201

References PHPExcel_Calculation_Functions\DIV0(), PHPExcel_Calculation_Functions\flattenSingleValue(), and PHPExcel_Calculation_Functions\NaN().

+ Here is the call graph for this function:

◆ DOLLARFR()

static PHPExcel_Calculation_Financial::DOLLARFR (   $decimal_dollar = Null,
  $fraction = 0 
)
static

Definition at line 1115 of file Financial.php.

1115 {
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()

References PHPExcel_Calculation_Functions\DIV0(), PHPExcel_Calculation_Functions\flattenSingleValue(), and PHPExcel_Calculation_Functions\NaN().

+ Here is the call graph for this function:

◆ EFFECT()

static PHPExcel_Calculation_Financial::EFFECT (   $nominal_rate = 0,
  $npery = 0 
)
static

Definition at line 1150 of file Financial.php.

1150 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), and PHPExcel_Calculation_Functions\NaN().

+ Here is the call graph for this function:

◆ FV()

static PHPExcel_Calculation_Financial::FV (   $rate = 0,
  $nper = 0,
  $pmt = 0,
  $pv = 0,
  $type = 0 
)
static

Definition at line 1185 of file Financial.php.

1185 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), and PHPExcel_Calculation_Functions\NaN().

+ Here is the call graph for this function:

◆ FVSCHEDULE()

static PHPExcel_Calculation_Financial::FVSCHEDULE (   $principal,
  $schedule 
)
static

FVSCHEDULE.

Returns the future value of an initial principal after applying a series of compound interest rates. Use FVSCHEDULE to calculate the future value of an investment with a variable or adjustable rate.

Excel Function: FVSCHEDULE(principal,schedule)

Parameters
float$principalThe present value.
float[]$scheduleAn array of interest rates to apply.
Returns
float

Definition at line 1219 of file Financial.php.

1219 {
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()
PHPExcel_Calculation_Functions\flattenArray
static flattenArray($array)
Convert a multi-dimensional array to a simple 1-dimensional array.
Definition: Functions.php:598

References PHPExcel_Calculation_Functions\flattenArray(), and PHPExcel_Calculation_Functions\flattenSingleValue().

+ Here is the call graph for this function:

◆ INTRATE()

static PHPExcel_Calculation_Financial::INTRATE (   $settlement,
  $maturity,
  $investment,
  $redemption,
  $basis = 0 
)
static

INTRATE.

Returns the interest rate for a fully invested security.

Excel Function: INTRATE(settlement,maturity,investment,redemption[,basis])

Parameters
mixed$settlementThe security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
mixed$maturityThe security's maturity date. The maturity date is the date when the security expires.
integer$investmentThe amount invested in the security.
integer$redemptionThe amount to be received at maturity.
integer$basisThe type of day count to use. 0 or omitted US (NASD) 30/360 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360
Returns
float

Definition at line 1253 of file Financial.php.

1253 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), and PHPExcel_Calculation_DateTime\YEARFRAC().

+ Here is the call graph for this function:

◆ IPMT()

static PHPExcel_Calculation_Financial::IPMT (   $rate,
  $per,
  $nper,
  $pv,
  $fv = 0,
  $type = 0 
)
static

IPMT.

Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.

Excel Function: IPMT(rate,per,nper,pv[,fv][,type])

Parameters
float$rateInterest rate per period
int$perPeriod for which we want to find the interest
int$nperNumber of periods
float$pvPresent Value
float$fvFuture Value
int$typePayment type: 0 = at the end of each period, 1 = at the beginning of each period
Returns
float

Definition at line 1296 of file Financial.php.

1296 {
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()
PHPExcel_Calculation_Financial\_interestAndPrincipal
static _interestAndPrincipal($rate=0, $per=0, $nper=0, $pv=0, $fv=0, $type=0)
Definition: Financial.php:153

References _interestAndPrincipal(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), and PHPExcel_Calculation_Functions\VALUE().

Referenced by CUMIPMT().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ IRR()

static PHPExcel_Calculation_Financial::IRR (   $values,
  $guess = 0.1 
)
static

IRR.

Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.

Excel Function: IRR(values[,guess])

Parameters
float[]$valuesAn array or a reference to cells that contain numbers for which you want to calculate the internal rate of return. Values must contain at least one positive value and one negative value to calculate the internal rate of return.
float$guessA number that you guess is close to the result of IRR
Returns
float

Definition at line 1336 of file Financial.php.

1336 {
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()
FINANCIAL_PRECISION
const FINANCIAL_PRECISION
FINANCIAL_PRECISION.
Definition: Financial.php:43
FINANCIAL_MAX_ITERATIONS
const FINANCIAL_MAX_ITERATIONS(!defined('PHPEXCEL_ROOT'))
PHPExcel root directory.
Definition: Financial.php:40
PHPExcel_Calculation_Financial\NPV
static NPV()
NPV.
Definition: Financial.php:1533

References FINANCIAL_MAX_ITERATIONS, FINANCIAL_PRECISION, PHPExcel_Calculation_Functions\flattenArray(), PHPExcel_Calculation_Functions\flattenSingleValue(), NPV(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ ISPMT()

static PHPExcel_Calculation_Financial::ISPMT ( )
static

ISPMT.

Returns the interest payment for an investment based on an interest rate and a constant payment schedule.

Excel Function: =ISPMT(interest_rate, period, number_payments, PV)

interest_rate is the interest rate for the investment

period is the period to calculate the interest rate. It must be betweeen 1 and number_payments.

number_payments is the number of payments for the annuity

PV is the loan amount or present value of the payments

Definition at line 1394 of file Financial.php.

1394 {
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()

References PHPExcel_Calculation_Functions\flattenArray().

+ Here is the call graph for this function:

◆ MIRR()

static PHPExcel_Calculation_Financial::MIRR (   $values,
  $finance_rate,
  $reinvestment_rate 
)
static

MIRR.

Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of cash.

Excel Function: MIRR(values,finance_rate, reinvestment_rate)

Parameters
float[]$valuesAn array or a reference to cells that contain a series of payments and income occurring at regular intervals. Payments are negative value, income is positive values.
float$finance_rateThe interest rate you pay on the money used in the cash flows
float$reinvestment_rateThe interest rate you receive on the cash flows as you reinvest them
Returns
float

Definition at line 1435 of file Financial.php.

1435 {
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()

References $n, PHPExcel_Calculation_Functions\flattenArray(), PHPExcel_Calculation_Functions\flattenSingleValue(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ NOMINAL()

static PHPExcel_Calculation_Financial::NOMINAL (   $effect_rate = 0,
  $npery = 0 
)
static

NOMINAL.

Returns the nominal interest rate given the effective rate and the number of compounding payments per year.

Parameters
float$effect_rateEffective interest rate
int$nperyNumber of compounding payments per year
Returns
float

Definition at line 1474 of file Financial.php.

1474 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), and PHPExcel_Calculation_Functions\NaN().

+ Here is the call graph for this function:

◆ NPER()

static PHPExcel_Calculation_Financial::NPER (   $rate = 0,
  $pmt = 0,
  $pv = 0,
  $fv = 0,
  $type = 0 
)
static

NPER.

Returns the number of periods for a cash flow with constant periodic payments (annuities), and interest rate.

Parameters
float$rateInterest rate per period
int$pmtPeriodic payment (annuity)
float$pvPresent Value
float$fvFuture Value
int$typePayment type: 0 = at the end of each period, 1 = at the beginning of each period
Returns
float

Definition at line 1500 of file Financial.php.

1500 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), and PHPExcel_Calculation_Functions\NaN().

+ Here is the call graph for this function:

◆ NPV()

static PHPExcel_Calculation_Financial::NPV ( )
static

NPV.

Returns the Net Present Value of a cash flow series given a discount rate.

Returns
float

Definition at line 1533 of file Financial.php.

1533 {
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()

References PHPExcel_Calculation_Functions\flattenArray().

Referenced by IRR().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ PMT()

static PHPExcel_Calculation_Financial::PMT (   $rate = 0,
  $nper = 0,
  $pv = 0,
  $fv = 0,
  $type = 0 
)
static

PMT.

Returns the constant payment (annuity) for a cash flow with a constant interest rate.

Parameters
float$rateInterest rate per period
int$nperNumber of periods
float$pvPresent Value
float$fvFuture Value
int$typePayment type: 0 = at the end of each period, 1 = at the beginning of each period
Returns
float

Definition at line 1565 of file Financial.php.

1565 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), and PHPExcel_Calculation_Functions\NaN().

Referenced by _interestAndPrincipal().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ PPMT()

static PHPExcel_Calculation_Financial::PPMT (   $rate,
  $per,
  $nper,
  $pv,
  $fv = 0,
  $type = 0 
)
static

PPMT.

Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.

Parameters
float$rateInterest rate per period
int$perPeriod for which we want to find the interest
int$nperNumber of periods
float$pvPresent Value
float$fvFuture Value
int$typePayment type: 0 = at the end of each period, 1 = at the beginning of each period
Returns
float

Definition at line 1599 of file Financial.php.

1599 {
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()

References _interestAndPrincipal(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), and PHPExcel_Calculation_Functions\VALUE().

Referenced by CUMPRINC().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ PRICE()

static PHPExcel_Calculation_Financial::PRICE (   $settlement,
  $maturity,
  $rate,
  $yield,
  $redemption,
  $frequency,
  $basis = 0 
)
static

Definition at line 1621 of file Financial.php.

1621 {
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()
PHPExcel_Calculation_Financial\COUPDAYSNC
static COUPDAYSNC($settlement, $maturity, $frequency, $basis=0)
Definition: Financial.php:579
PHPExcel_Calculation_Financial\COUPDAYS
static COUPDAYS($settlement, $maturity, $frequency, $basis=0)
Definition: Financial.php:509
PHPExcel_Calculation_Financial\COUPDAYBS
static COUPDAYBS($settlement, $maturity, $frequency, $basis=0)
Definition: Financial.php:451
PHPExcel_Calculation_Financial\COUPNUM
static COUPNUM($settlement, $maturity, $frequency, $basis=0)
Definition: Financial.php:694

References $n, $result, PHPExcel_Calculation_DateTime\_getDateValue(), COUPDAYBS(), COUPDAYS(), COUPDAYSNC(), COUPNUM(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ PRICEDISC()

static PHPExcel_Calculation_Financial::PRICEDISC (   $settlement,
  $maturity,
  $discount,
  $redemption,
  $basis = 0 
)
static

PRICEDISC.

Returns the price per $100 face value of a discounted security.

Parameters
mixedsettlement The security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
mixedmaturity The security's maturity date. The maturity date is the date when the security expires.
intdiscount The security's discount rate.
intredemption The security's redemption value per $100 face value.
intbasis The type of day count to use. 0 or omitted US (NASD) 30/360 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360
Returns
float

Definition at line 1681 of file Financial.php.

1681 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), and PHPExcel_Calculation_DateTime\YEARFRAC().

+ Here is the call graph for this function:

◆ PRICEMAT()

static PHPExcel_Calculation_Financial::PRICEMAT (   $settlement,
  $maturity,
  $issue,
  $rate,
  $yield,
  $basis = 0 
)
static

PRICEMAT.

Returns the price per $100 face value of a security that pays interest at maturity.

Parameters
mixedsettlement The security's settlement date. The security's settlement date is the date after the issue date when the security is traded to the buyer.
mixedmaturity The security's maturity date. The maturity date is the date when the security expires.
mixedissue The security's issue date.
intrate The security's interest rate at date of issue.
intyield The security's annual yield.
intbasis The type of day count to use. 0 or omitted US (NASD) 30/360 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360
Returns
float

Definition at line 1725 of file Financial.php.

1725 {
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()

References _daysPerYear(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), PHPExcel_Calculation_DateTime\YEAR(), and PHPExcel_Calculation_DateTime\YEARFRAC().

+ Here is the call graph for this function:

◆ PV()

static PHPExcel_Calculation_Financial::PV (   $rate = 0,
  $nper = 0,
  $pmt = 0,
  $fv = 0,
  $type = 0 
)
static

PV.

Returns the Present Value of a cash flow with constant payments and interest rate (annuities).

Parameters
float$rateInterest rate per period
int$nperNumber of periods
float$pmtPeriodic payment (annuity)
float$fvFuture Value
int$typePayment type: 0 = at the end of each period, 1 = at the beginning of each period
Returns
float

Definition at line 1781 of file Financial.php.

1781 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), and PHPExcel_Calculation_Functions\NaN().

+ Here is the call graph for this function:

◆ RATE()

static PHPExcel_Calculation_Financial::RATE (   $nper,
  $pmt,
  $pv,
  $fv = 0.0,
  $type = 0,
  $guess = 0.1 
)
static

Definition at line 1832 of file Financial.php.

1832 {
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()
$y
$y
Definition: example_007.php:83

References $y, FINANCIAL_MAX_ITERATIONS, FINANCIAL_PRECISION, and PHPExcel_Calculation_Functions\flattenSingleValue().

+ Here is the call graph for this function:

◆ RECEIVED()

static PHPExcel_Calculation_Financial::RECEIVED (   $settlement,
  $maturity,
  $investment,
  $discount,
  $basis = 0 
)
static

RECEIVED.

Returns the price per $100 face value of a discounted security.

Parameters
mixedsettlement The security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
mixedmaturity The security's maturity date. The maturity date is the date when the security expires.
intinvestment The amount invested in the security.
intdiscount The security's discount rate.
intbasis The type of day count to use. 0 or omitted US (NASD) 30/360 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360
Returns
float

Definition at line 1894 of file Financial.php.

1894 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), and PHPExcel_Calculation_DateTime\YEARFRAC().

+ Here is the call graph for this function:

◆ SLN()

static PHPExcel_Calculation_Financial::SLN (   $cost,
  $salvage,
  $life 
)
static

SLN.

Returns the straight-line depreciation of an asset for one period

Parameters
costInitial cost of the asset
salvageValue at the end of the depreciation
lifeNumber of periods over which the asset is depreciated
Returns
float

Definition at line 1928 of file Financial.php.

1928 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ SYD()

static PHPExcel_Calculation_Financial::SYD (   $cost,
  $salvage,
  $life,
  $period 
)
static

SYD.

Returns the sum-of-years' digits depreciation of an asset for a specified period.

Parameters
costInitial cost of the asset
salvageValue at the end of the depreciation
lifeNumber of periods over which the asset is depreciated
periodPeriod
Returns
float

Definition at line 1955 of file Financial.php.

1955 {
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()

References PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), and PHPExcel_Calculation_Functions\VALUE().

+ Here is the call graph for this function:

◆ TBILLEQ()

static PHPExcel_Calculation_Financial::TBILLEQ (   $settlement,
  $maturity,
  $discount 
)
static

TBILLEQ.

Returns the bond-equivalent yield for a Treasury bill.

Parameters
mixedsettlement The Treasury bill's settlement date. The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
mixedmaturity The Treasury bill's maturity date. The maturity date is the date when the Treasury bill expires.
intdiscount The Treasury bill's discount rate.
Returns
float

Definition at line 1984 of file Financial.php.

1984 {
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()
PHPExcel_Calculation_Financial\TBILLPRICE
static TBILLPRICE($settlement, $maturity, $discount)
TBILLPRICE.
Definition: Financial.php:2022
PHPExcel_Calculation_Functions\COMPATIBILITY_OPENOFFICE
const COMPATIBILITY_OPENOFFICE
Definition: Functions.php:64

References PHPExcel_Calculation_DateTime\_getDateValue(), PHPExcel_Calculation_Functions\COMPATIBILITY_OPENOFFICE, PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\getCompatibilityMode(), TBILLPRICE(), PHPExcel_Calculation_Functions\VALUE(), and PHPExcel_Calculation_DateTime\YEARFRAC().

+ Here is the call graph for this function:

◆ TBILLPRICE()

static PHPExcel_Calculation_Financial::TBILLPRICE (   $settlement,
  $maturity,
  $discount 
)
static

TBILLPRICE.

Returns the yield for a Treasury bill.

Parameters
mixedsettlement The Treasury bill's settlement date. The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
mixedmaturity The Treasury bill's maturity date. The maturity date is the date when the Treasury bill expires.
intdiscount The Treasury bill's discount rate.
Returns
float

Definition at line 2022 of file Financial.php.

2022 {
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()

References PHPExcel_Calculation_DateTime\_getDateValue(), PHPExcel_Calculation_Functions\COMPATIBILITY_OPENOFFICE, PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\getCompatibilityMode(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), and PHPExcel_Calculation_DateTime\YEARFRAC().

Referenced by TBILLEQ().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ TBILLYIELD()

static PHPExcel_Calculation_Financial::TBILLYIELD (   $settlement,
  $maturity,
  $price 
)
static

TBILLYIELD.

Returns the yield for a Treasury bill.

Parameters
mixedsettlement The Treasury bill's settlement date. The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
mixedmaturity The Treasury bill's maturity date. The maturity date is the date when the Treasury bill expires.
intprice The Treasury bill's price per $100 face value.
Returns
float

Definition at line 2074 of file Financial.php.

2074 {
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()

References PHPExcel_Calculation_DateTime\_getDateValue(), PHPExcel_Calculation_Functions\COMPATIBILITY_OPENOFFICE, PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\getCompatibilityMode(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), and PHPExcel_Calculation_DateTime\YEARFRAC().

+ Here is the call graph for this function:

◆ XIRR()

static PHPExcel_Calculation_Financial::XIRR (   $values,
  $dates,
  $guess = 0.1 
)
static

Definition at line 2106 of file Financial.php.

2106 {
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 }
PHPExcel_Calculation_Financial\XNPV
static XNPV($rate, $values, $dates)
XNPV.
Definition: Financial.php:2162

References FINANCIAL_MAX_ITERATIONS, FINANCIAL_PRECISION, PHPExcel_Calculation_Functions\flattenArray(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), and XNPV().

+ Here is the call graph for this function:

◆ XNPV()

static PHPExcel_Calculation_Financial::XNPV (   $rate,
  $values,
  $dates 
)
static

XNPV.

Returns the net present value for a schedule of cash flows that is not necessarily periodic. To calculate the net present value for a series of cash flows that is periodic, use the NPV function.

Excel Function: =XNPV(rate,values,dates)

Parameters
float$rateThe discount rate to apply to the cash flows.
arrayof 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.
arrayof 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.
Returns
float

Definition at line 2162 of file Financial.php.

2162 {
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()
PHPExcel_Calculation_DateTime\DATEDIF
static DATEDIF($startDate=0, $endDate=0, $unit='D')
DATEDIF.
Definition: DateTime.php:618

References PHPExcel_Calculation_DateTime\DATEDIF(), PHPExcel_Calculation_Functions\flattenArray(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), and PHPExcel_Calculation_Functions\VALUE().

Referenced by XIRR().

+ Here is the call graph for this function:
+ Here is the caller graph for this function:

◆ YIELDDISC()

static PHPExcel_Calculation_Financial::YIELDDISC (   $settlement,
  $maturity,
  $price,
  $redemption,
  $basis = 0 
)
static

YIELDDISC.

Returns the annual yield of a security that pays interest at maturity.

Parameters
mixedsettlement The security's settlement date. The security's settlement date is the date after the issue date when the security is traded to the buyer.
mixedmaturity The security's maturity date. The maturity date is the date when the security expires.
intprice The security's price per $100 face value.
intredemption The security's redemption value per $100 face value.
intbasis The type of day count to use. 0 or omitted US (NASD) 30/360 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360
Returns
float

Definition at line 2200 of file Financial.php.

2200 {
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()

References _daysPerYear(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), PHPExcel_Calculation_DateTime\YEAR(), and PHPExcel_Calculation_DateTime\YEARFRAC().

+ Here is the call graph for this function:

◆ YIELDMAT()

static PHPExcel_Calculation_Financial::YIELDMAT (   $settlement,
  $maturity,
  $issue,
  $rate,
  $price,
  $basis = 0 
)
static

YIELDMAT.

Returns the annual yield of a security that pays interest at maturity.

Parameters
mixedsettlement The security's settlement date. The security's settlement date is the date after the issue date when the security is traded to the buyer.
mixedmaturity The security's maturity date. The maturity date is the date when the security expires.
mixedissue The security's issue date.
intrate The security's interest rate at date of issue.
intprice The security's price per $100 face value.
intbasis The type of day count to use. 0 or omitted US (NASD) 30/360 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360
Returns
float

Definition at line 2249 of file Financial.php.

2249 {
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()

References _daysPerYear(), PHPExcel_Calculation_Functions\flattenSingleValue(), PHPExcel_Calculation_Functions\NaN(), PHPExcel_Calculation_Functions\VALUE(), PHPExcel_Calculation_DateTime\YEAR(), and PHPExcel_Calculation_DateTime\YEARFRAC().

+ Here is the call graph for this function:
The documentation for this class was generated from the following file: