ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
Financial.php
Go to the documentation of this file.
1 <?php
30 if (!defined('PHPEXCEL_ROOT')) {
34  define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35  require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36 }
37 
38 
40 define('FINANCIAL_MAX_ITERATIONS', 128);
41 
43 define('FINANCIAL_PRECISION', 1.0e-08);
44 
45 
53 class PHPExcel_Calculation_Financial {
54 
55  private static function _lastDayOfMonth($testDate) {
56  $date = clone $testDate;
57  $date->modify('+1 day');
58  return ($date->format('d') == 1);
59  } // function _lastDayOfMonth()
60 
61 
62  private static function _firstDayOfMonth($testDate) {
63  $date = clone $testDate;
64  return ($date->format('d') == 1);
65  } // function _lastDayOfMonth()
66 
67 
68  private static function _coupFirstPeriodDate($settlement, $maturity, $frequency, $next) {
69  $months = 12 / $frequency;
70 
71  $result = PHPExcel_Shared_Date::ExcelToPHPObject($maturity);
72  $eom = self::_lastDayOfMonth($result);
73 
74  while ($settlement < PHPExcel_Shared_Date::PHPToExcel($result)) {
75  $result->modify('-'.$months.' months');
76  }
77  if ($next) {
78  $result->modify('+'.$months.' months');
79  }
80 
81  if ($eom) {
82  $result->modify('-1 day');
83  }
84 
85  return PHPExcel_Shared_Date::PHPToExcel($result);
86  } // function _coupFirstPeriodDate()
87 
88 
89  private static function _validFrequency($frequency) {
90  if (($frequency == 1) || ($frequency == 2) || ($frequency == 4)) {
91  return true;
92  }
93  if ((PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) &&
94  (($frequency == 6) || ($frequency == 12))) {
95  return true;
96  }
97  return false;
98  } // function _validFrequency()
99 
100 
101  private static function _daysPerYear($year,$basis) {
102  switch ($basis) {
103  case 0 :
104  case 2 :
105  case 4 :
106  $daysPerYear = 360;
107  break;
108  case 3 :
109  $daysPerYear = 365;
110  break;
111  case 1 :
112  if (PHPExcel_Calculation_DateTime::_isLeapYear($year)) {
113  $daysPerYear = 366;
114  } else {
115  $daysPerYear = 365;
116  }
117  break;
118  default :
119  return PHPExcel_Calculation_Functions::NaN();
120  }
121  return $daysPerYear;
122  } // function _daysPerYear()
123 
124 
125  private static function _interestAndPrincipal($rate=0, $per=0, $nper=0, $pv=0, $fv=0, $type=0) {
126  $pmt = self::PMT($rate, $nper, $pv, $fv, $type);
127  $capital = $pv;
128  for ($i = 1; $i<= $per; ++$i) {
129  $interest = ($type && $i == 1) ? 0 : -$capital * $rate;
130  $principal = $pmt - $interest;
131  $capital += $principal;
132  }
133  return array($interest, $principal);
134  } // function _interestAndPrincipal()
135 
136 
155  public static function ACCRINT($issue, $firstinter, $settlement, $rate, $par=1000, $frequency=1, $basis=0) {
156  $issue = PHPExcel_Calculation_Functions::flattenSingleValue($issue);
157  $firstinter = PHPExcel_Calculation_Functions::flattenSingleValue($firstinter);
158  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
159  $rate = (float) PHPExcel_Calculation_Functions::flattenSingleValue($rate);
160  $par = (is_null($par)) ? 1000 : (float) PHPExcel_Calculation_Functions::flattenSingleValue($par);
161  $frequency = (is_null($frequency)) ? 1 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
162  $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
163 
164  // Validate
165  if ((is_numeric($rate)) && (is_numeric($par))) {
166  if (($rate <= 0) || ($par <= 0)) {
167  return PHPExcel_Calculation_Functions::NaN();
168  }
169  $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
170  if (!is_numeric($daysBetweenIssueAndSettlement)) {
171  // return date error
172  return $daysBetweenIssueAndSettlement;
173  }
174 
175  return $par * $rate * $daysBetweenIssueAndSettlement;
176  }
177  return PHPExcel_Calculation_Functions::VALUE();
178  } // function ACCRINT()
179 
180 
198  public static function ACCRINTM($issue, $settlement, $rate, $par=1000, $basis=0) {
199  $issue = PHPExcel_Calculation_Functions::flattenSingleValue($issue);
200  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
201  $rate = (float) PHPExcel_Calculation_Functions::flattenSingleValue($rate);
202  $par = (is_null($par)) ? 1000 : (float) PHPExcel_Calculation_Functions::flattenSingleValue($par);
203  $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
204 
205  // Validate
206  if ((is_numeric($rate)) && (is_numeric($par))) {
207  if (($rate <= 0) || ($par <= 0)) {
208  return PHPExcel_Calculation_Functions::NaN();
209  }
210  $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
211  if (!is_numeric($daysBetweenIssueAndSettlement)) {
212  // return date error
213  return $daysBetweenIssueAndSettlement;
214  }
215  return $par * $rate * $daysBetweenIssueAndSettlement;
216  }
217  return PHPExcel_Calculation_Functions::VALUE();
218  } // function ACCRINTM()
219 
220 
221  public static function AMORDEGRC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis=0) {
222  $cost = PHPExcel_Calculation_Functions::flattenSingleValue($cost);
223  $purchased = PHPExcel_Calculation_Functions::flattenSingleValue($purchased);
224  $firstPeriod = PHPExcel_Calculation_Functions::flattenSingleValue($firstPeriod);
225  $salvage = PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
226  $period = floor(PHPExcel_Calculation_Functions::flattenSingleValue($period));
227  $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
228  $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
229 
230  $fUsePer = 1.0 / $rate;
231 
232  if ($fUsePer < 3.0) {
233  $amortiseCoeff = 1.0;
234  } elseif ($fUsePer < 5.0) {
235  $amortiseCoeff = 1.5;
236  } elseif ($fUsePer <= 6.0) {
237  $amortiseCoeff = 2.0;
238  } else {
239  $amortiseCoeff = 2.5;
240  }
241 
242  $rate *= $amortiseCoeff;
243  $fNRate = round(PHPExcel_Calculation_DateTime::YEARFRAC($purchased, $firstPeriod, $basis) * $rate * $cost,0);
244  $cost -= $fNRate;
245  $fRest = $cost - $salvage;
246 
247  for ($n = 0; $n < $period; ++$n) {
248  $fNRate = round($rate * $cost,0);
249  $fRest -= $fNRate;
250 
251  if ($fRest < 0.0) {
252  switch ($period - $n) {
253  case 0 :
254  case 1 : return round($cost * 0.5,0);
255  break;
256  default : return 0.0;
257  break;
258  }
259  }
260  $cost -= $fNRate;
261  }
262  return $fNRate;
263  } // function AMORDEGRC()
264 
265 
266  public static function AMORLINC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis=0) {
267  $cost = PHPExcel_Calculation_Functions::flattenSingleValue($cost);
268  $purchased = PHPExcel_Calculation_Functions::flattenSingleValue($purchased);
269  $firstPeriod = PHPExcel_Calculation_Functions::flattenSingleValue($firstPeriod);
270  $salvage = PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
271  $period = PHPExcel_Calculation_Functions::flattenSingleValue($period);
272  $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
273  $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
274 
275  $fOneRate = $cost * $rate;
276  $fCostDelta = $cost - $salvage;
277  // Note, quirky variation for leap years on the YEARFRAC for this function
278  $purchasedYear = PHPExcel_Calculation_DateTime::YEAR($purchased);
279  $yearFrac = PHPExcel_Calculation_DateTime::YEARFRAC($purchased, $firstPeriod, $basis);
280 
281  if (($basis == 1) && ($yearFrac < 1) && (PHPExcel_Calculation_DateTime::_isLeapYear($purchasedYear))) {
282  $yearFrac *= 365 / 366;
283  }
284 
285  $f0Rate = $yearFrac * $rate * $cost;
286  $nNumOfFullPeriods = intval(($cost - $salvage - $f0Rate) / $fOneRate);
287 
288  if ($period == 0) {
289  return $f0Rate;
290  } elseif ($period <= $nNumOfFullPeriods) {
291  return $fOneRate;
292  } elseif ($period == ($nNumOfFullPeriods + 1)) {
293  return ($fCostDelta - $fOneRate * $nNumOfFullPeriods - $f0Rate);
294  } else {
295  return 0.0;
296  }
297  } // function AMORLINC()
298 
299 
300  public static function COUPDAYBS($settlement, $maturity, $frequency, $basis=0) {
301  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
302  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
303  $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
304  $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
305 
306  if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
307  return PHPExcel_Calculation_Functions::VALUE();
308  }
309  if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
310  return PHPExcel_Calculation_Functions::VALUE();
311  }
312 
313  if (($settlement > $maturity) ||
314  (!self::_validFrequency($frequency)) ||
315  (($basis < 0) || ($basis > 4))) {
316  return PHPExcel_Calculation_Functions::NaN();
317  }
318 
319  $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
320  $prev = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False);
321 
322  return PHPExcel_Calculation_DateTime::YEARFRAC($prev, $settlement, $basis) * $daysPerYear;
323  } // function COUPDAYBS()
324 
325 
326  public static function COUPDAYS($settlement, $maturity, $frequency, $basis=0) {
327  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
328  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
329  $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
330  $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
331 
332  if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
333  return PHPExcel_Calculation_Functions::VALUE();
334  }
335  if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
336  return PHPExcel_Calculation_Functions::VALUE();
337  }
338 
339  if (($settlement > $maturity) ||
340  (!self::_validFrequency($frequency)) ||
341  (($basis < 0) || ($basis > 4))) {
342  return PHPExcel_Calculation_Functions::NaN();
343  }
344 
345  switch ($basis) {
346  case 3: // Actual/365
347  return 365 / $frequency;
348  case 1: // Actual/actual
349  if ($frequency == 1) {
350  $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($maturity),$basis);
351  return ($daysPerYear / $frequency);
352  } else {
353  $prev = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False);
354  $next = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
355  return ($next - $prev);
356  }
357  default: // US (NASD) 30/360, Actual/360 or European 30/360
358  return 360 / $frequency;
359  }
360  return PHPExcel_Calculation_Functions::VALUE();
361  } // function COUPDAYS()
362 
363 
364  public static function COUPDAYSNC($settlement, $maturity, $frequency, $basis=0) {
365  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
366  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
367  $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
368  $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
369 
370  if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
371  return PHPExcel_Calculation_Functions::VALUE();
372  }
373  if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
374  return PHPExcel_Calculation_Functions::VALUE();
375  }
376 
377  if (($settlement > $maturity) ||
378  (!self::_validFrequency($frequency)) ||
379  (($basis < 0) || ($basis > 4))) {
380  return PHPExcel_Calculation_Functions::NaN();
381  }
382 
383  $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
384  $next = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
385 
386  return PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $next, $basis) * $daysPerYear;
387  } // function COUPDAYSNC()
388 
389 
390  public static function COUPNCD($settlement, $maturity, $frequency, $basis=0) {
391  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
392  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
393  $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
394  $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
395 
396  if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
397  return PHPExcel_Calculation_Functions::VALUE();
398  }
399  if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
400  return PHPExcel_Calculation_Functions::VALUE();
401  }
402 
403  if (($settlement > $maturity) ||
404  (!self::_validFrequency($frequency)) ||
405  (($basis < 0) || ($basis > 4))) {
406  return PHPExcel_Calculation_Functions::NaN();
407  }
408 
409  return self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
410  } // function COUPNCD()
411 
412 
413  public static function COUPNUM($settlement, $maturity, $frequency, $basis=0) {
414  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
415  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
416  $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
417  $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
418 
419  if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
420  return PHPExcel_Calculation_Functions::VALUE();
421  }
422  if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
423  return PHPExcel_Calculation_Functions::VALUE();
424  }
425 
426  if (($settlement > $maturity) ||
427  (!self::_validFrequency($frequency)) ||
428  (($basis < 0) || ($basis > 4))) {
429  return PHPExcel_Calculation_Functions::NaN();
430  }
431 
432  $settlement = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
433  $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis) * 365;
434 
435  switch ($frequency) {
436  case 1: // annual payments
437  return ceil($daysBetweenSettlementAndMaturity / 360);
438  case 2: // half-yearly
439  return ceil($daysBetweenSettlementAndMaturity / 180);
440  case 4: // quarterly
441  return ceil($daysBetweenSettlementAndMaturity / 90);
442  case 6: // bimonthly
443  return ceil($daysBetweenSettlementAndMaturity / 60);
444  case 12: // monthly
445  return ceil($daysBetweenSettlementAndMaturity / 30);
446  }
447  return PHPExcel_Calculation_Functions::VALUE();
448  } // function COUPNUM()
449 
450 
451  public static function COUPPCD($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  return self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False);
471  } // function COUPPCD()
472 
473 
488  public static function CUMIPMT($rate, $nper, $pv, $start, $end, $type = 0) {
489  $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
490  $nper = (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
491  $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
492  $start = (int) PHPExcel_Calculation_Functions::flattenSingleValue($start);
493  $end = (int) PHPExcel_Calculation_Functions::flattenSingleValue($end);
494  $type = (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
495 
496  // Validate parameters
497  if ($type != 0 && $type != 1) {
498  return PHPExcel_Calculation_Functions::NaN();
499  }
500  if ($start < 1 || $start > $end) {
501  return PHPExcel_Calculation_Functions::VALUE();
502  }
503 
504  // Calculate
505  $interest = 0;
506  for ($per = $start; $per <= $end; ++$per) {
507  $interest += self::IPMT($rate, $per, $nper, $pv, 0, $type);
508  }
509 
510  return $interest;
511  } // function CUMIPMT()
512 
513 
528  public static function CUMPRINC($rate, $nper, $pv, $start, $end, $type = 0) {
529  $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
530  $nper = (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
531  $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
532  $start = (int) PHPExcel_Calculation_Functions::flattenSingleValue($start);
533  $end = (int) PHPExcel_Calculation_Functions::flattenSingleValue($end);
534  $type = (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
535 
536  // Validate parameters
537  if ($type != 0 && $type != 1) {
538  return PHPExcel_Calculation_Functions::NaN();
539  }
540  if ($start < 1 || $start > $end) {
541  return PHPExcel_Calculation_Functions::VALUE();
542  }
543 
544  // Calculate
545  $principal = 0;
546  for ($per = $start; $per <= $end; ++$per) {
547  $principal += self::PPMT($rate, $per, $nper, $pv, 0, $type);
548  }
549 
550  return $principal;
551  } // function CUMPRINC()
552 
553 
569  public static function DB($cost, $salvage, $life, $period, $month=12) {
570  $cost = (float) PHPExcel_Calculation_Functions::flattenSingleValue($cost);
571  $salvage = (float) PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
572  $life = (int) PHPExcel_Calculation_Functions::flattenSingleValue($life);
573  $period = (int) PHPExcel_Calculation_Functions::flattenSingleValue($period);
574  $month = (int) PHPExcel_Calculation_Functions::flattenSingleValue($month);
575 
576  // Validate
577  if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period)) && (is_numeric($month))) {
578  if ($cost == 0) {
579  return 0.0;
580  } elseif (($cost < 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($month < 1)) {
581  return PHPExcel_Calculation_Functions::NaN();
582  }
583  // Set Fixed Depreciation Rate
584  $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life));
585  $fixedDepreciationRate = round($fixedDepreciationRate, 3);
586 
587  // Loop through each period calculating the depreciation
588  $previousDepreciation = 0;
589  for ($per = 1; $per <= $period; ++$per) {
590  if ($per == 1) {
591  $depreciation = $cost * $fixedDepreciationRate * $month / 12;
592  } elseif ($per == ($life + 1)) {
593  $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate * (12 - $month) / 12;
594  } else {
595  $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate;
596  }
597  $previousDepreciation += $depreciation;
598  }
599  if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
600  $depreciation = round($depreciation,2);
601  }
602  return $depreciation;
603  }
604  return PHPExcel_Calculation_Functions::VALUE();
605  } // function DB()
606 
607 
621  public static function DDB($cost, $salvage, $life, $period, $factor=2.0) {
622  $cost = (float) PHPExcel_Calculation_Functions::flattenSingleValue($cost);
623  $salvage = (float) PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
624  $life = (int) PHPExcel_Calculation_Functions::flattenSingleValue($life);
625  $period = (int) PHPExcel_Calculation_Functions::flattenSingleValue($period);
626  $factor = (float) PHPExcel_Calculation_Functions::flattenSingleValue($factor);
627 
628  // Validate
629  if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period)) && (is_numeric($factor))) {
630  if (($cost <= 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($factor <= 0.0) || ($period > $life)) {
631  return PHPExcel_Calculation_Functions::NaN();
632  }
633  // Set Fixed Depreciation Rate
634  $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life));
635  $fixedDepreciationRate = round($fixedDepreciationRate, 3);
636 
637  // Loop through each period calculating the depreciation
638  $previousDepreciation = 0;
639  for ($per = 1; $per <= $period; ++$per) {
640  $depreciation = min( ($cost - $previousDepreciation) * ($factor / $life), ($cost - $salvage - $previousDepreciation) );
641  $previousDepreciation += $depreciation;
642  }
643  if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
644  $depreciation = round($depreciation,2);
645  }
646  return $depreciation;
647  }
648  return PHPExcel_Calculation_Functions::VALUE();
649  } // function DDB()
650 
651 
671  public static function DISC($settlement, $maturity, $price, $redemption, $basis=0) {
672  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
673  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
674  $price = (float) PHPExcel_Calculation_Functions::flattenSingleValue($price);
675  $redemption = (float) PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
676  $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
677 
678  // Validate
679  if ((is_numeric($price)) && (is_numeric($redemption)) && (is_numeric($basis))) {
680  if (($price <= 0) || ($redemption <= 0)) {
681  return PHPExcel_Calculation_Functions::NaN();
682  }
683  $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
684  if (!is_numeric($daysBetweenSettlementAndMaturity)) {
685  // return date error
686  return $daysBetweenSettlementAndMaturity;
687  }
688 
689  return ((1 - $price / $redemption) / $daysBetweenSettlementAndMaturity);
690  }
691  return PHPExcel_Calculation_Functions::VALUE();
692  } // function DISC()
693 
694 
705  public static function DOLLARDE($fractional_dollar = Null, $fraction = 0) {
706  $fractional_dollar = PHPExcel_Calculation_Functions::flattenSingleValue($fractional_dollar);
707  $fraction = (int)PHPExcel_Calculation_Functions::flattenSingleValue($fraction);
708 
709  // Validate parameters
710  if (is_null($fractional_dollar) || $fraction < 0) {
711  return PHPExcel_Calculation_Functions::NaN();
712  }
713  if ($fraction == 0) {
714  return PHPExcel_Calculation_Functions::DIV0();
715  }
716 
717  $dollars = floor($fractional_dollar);
718  $cents = fmod($fractional_dollar,1);
719  $cents /= $fraction;
720  $cents *= pow(10,ceil(log10($fraction)));
721  return $dollars + $cents;
722  } // function DOLLARDE()
723 
724 
735  public static function DOLLARFR($decimal_dollar = Null, $fraction = 0) {
736  $decimal_dollar = PHPExcel_Calculation_Functions::flattenSingleValue($decimal_dollar);
737  $fraction = (int)PHPExcel_Calculation_Functions::flattenSingleValue($fraction);
738 
739  // Validate parameters
740  if (is_null($decimal_dollar) || $fraction < 0) {
741  return PHPExcel_Calculation_Functions::NaN();
742  }
743  if ($fraction == 0) {
744  return PHPExcel_Calculation_Functions::DIV0();
745  }
746 
747  $dollars = floor($decimal_dollar);
748  $cents = fmod($decimal_dollar,1);
749  $cents *= $fraction;
750  $cents *= pow(10,-ceil(log10($fraction)));
751  return $dollars + $cents;
752  } // function DOLLARFR()
753 
754 
764  public static function EFFECT($nominal_rate = 0, $npery = 0) {
765  $nominal_rate = PHPExcel_Calculation_Functions::flattenSingleValue($nominal_rate);
766  $npery = (int)PHPExcel_Calculation_Functions::flattenSingleValue($npery);
767 
768  // Validate parameters
769  if ($nominal_rate <= 0 || $npery < 1) {
770  return PHPExcel_Calculation_Functions::NaN();
771  }
772 
773  return pow((1 + $nominal_rate / $npery), $npery) - 1;
774  } // function EFFECT()
775 
776 
789  public static function FV($rate = 0, $nper = 0, $pmt = 0, $pv = 0, $type = 0) {
790  $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
791  $nper = PHPExcel_Calculation_Functions::flattenSingleValue($nper);
792  $pmt = PHPExcel_Calculation_Functions::flattenSingleValue($pmt);
793  $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
794  $type = PHPExcel_Calculation_Functions::flattenSingleValue($type);
795 
796  // Validate parameters
797  if ($type != 0 && $type != 1) {
798  return PHPExcel_Calculation_Functions::NaN();
799  }
800 
801  // Calculate
802  if (!is_null($rate) && $rate != 0) {
803  return -$pv * pow(1 + $rate, $nper) - $pmt * (1 + $rate * $type) * (pow(1 + $rate, $nper) - 1) / $rate;
804  } else {
805  return -$pv - $pmt * $nper;
806  }
807  } // function FV()
808 
809 
814  public static function FVSCHEDULE($principal, $schedule) {
815  $principal = PHPExcel_Calculation_Functions::flattenSingleValue($principal);
816  $schedule = PHPExcel_Calculation_Functions::flattenArray($schedule);
817 
818  foreach($schedule as $n) {
819  $principal *= 1 + $n;
820  }
821 
822  return $principal;
823  } // function FVSCHEDULE()
824 
825 
845  public static function INTRATE($settlement, $maturity, $investment, $redemption, $basis=0) {
846  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
847  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
848  $investment = (float) PHPExcel_Calculation_Functions::flattenSingleValue($investment);
849  $redemption = (float) PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
850  $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
851 
852  // Validate
853  if ((is_numeric($investment)) && (is_numeric($redemption)) && (is_numeric($basis))) {
854  if (($investment <= 0) || ($redemption <= 0)) {
855  return PHPExcel_Calculation_Functions::NaN();
856  }
857  $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
858  if (!is_numeric($daysBetweenSettlementAndMaturity)) {
859  // return date error
860  return $daysBetweenSettlementAndMaturity;
861  }
862 
863  return (($redemption / $investment) - 1) / ($daysBetweenSettlementAndMaturity);
864  }
865  return PHPExcel_Calculation_Functions::VALUE();
866  } // function INTRATE()
867 
868 
882  public static function IPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
883  $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
884  $per = (int) PHPExcel_Calculation_Functions::flattenSingleValue($per);
885  $nper = (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
886  $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
887  $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
888  $type = (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
889 
890  // Validate parameters
891  if ($type != 0 && $type != 1) {
892  return PHPExcel_Calculation_Functions::NaN();
893  }
894  if ($per <= 0 || $per > $nper) {
895  return PHPExcel_Calculation_Functions::VALUE();
896  }
897 
898  // Calculate
899  $interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
900  return $interestAndPrincipal[0];
901  } // function IPMT()
902 
903 
904  public static function IRR($values, $guess = 0.1) {
905  if (!is_array($values)) return PHPExcel_Calculation_Functions::VALUE();
906  $values = PHPExcel_Calculation_Functions::flattenArray($values);
907  $guess = PHPExcel_Calculation_Functions::flattenSingleValue($guess);
908 
909  // create an initial range, with a root somewhere between 0 and guess
910  $x1 = 0.0;
911  $x2 = $guess;
912  $f1 = self::NPV($x1, $values);
913  $f2 = self::NPV($x2, $values);
914  for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
915  if (($f1 * $f2) < 0.0) break;
916  if (abs($f1) < abs($f2)) {
917  $f1 = self::NPV($x1 += 1.6 * ($x1 - $x2), $values);
918  } else {
919  $f2 = self::NPV($x2 += 1.6 * ($x2 - $x1), $values);
920  }
921  }
922  if (($f1 * $f2) > 0.0) return PHPExcel_Calculation_Functions::VALUE();
923 
924  $f = self::NPV($x1, $values);
925  if ($f < 0.0) {
926  $rtb = $x1;
927  $dx = $x2 - $x1;
928  } else {
929  $rtb = $x2;
930  $dx = $x1 - $x2;
931  }
932 
933  for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
934  $dx *= 0.5;
935  $x_mid = $rtb + $dx;
936  $f_mid = self::NPV($x_mid, $values);
937  if ($f_mid <= 0.0) $rtb = $x_mid;
938  if ((abs($f_mid) < FINANCIAL_PRECISION) || (abs($dx) < FINANCIAL_PRECISION)) return $x_mid;
939  }
940  return PHPExcel_Calculation_Functions::VALUE();
941  } // function IRR()
942 
943 
960  public static function ISPMT() {
961  // Return value
962  $returnValue = 0;
963 
964  // Get the parameters
965  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
966  $interestRate = array_shift($aArgs);
967  $period = array_shift($aArgs);
968  $numberPeriods = array_shift($aArgs);
969  $principleRemaining = array_shift($aArgs);
970 
971  // Calculate
972  $principlePayment = ($principleRemaining * 1.0) / ($numberPeriods * 1.0);
973  for($i=0; $i <= $period; ++$i) {
974  $returnValue = $interestRate * $principleRemaining * -1;
975  $principleRemaining -= $principlePayment;
976  // principle needs to be 0 after the last payment, don't let floating point screw it up
977  if($i == $numberPeriods) {
978  $returnValue = 0;
979  }
980  }
981  return($returnValue);
982  } // function ISPMT()
983 
984 
985  public static function MIRR($values, $finance_rate, $reinvestment_rate) {
986  if (!is_array($values)) return PHPExcel_Calculation_Functions::VALUE();
987  $values = PHPExcel_Calculation_Functions::flattenArray($values);
988  $finance_rate = PHPExcel_Calculation_Functions::flattenSingleValue($finance_rate);
989  $reinvestment_rate = PHPExcel_Calculation_Functions::flattenSingleValue($reinvestment_rate);
990  $n = count($values);
991 
992  $rr = 1.0 + $reinvestment_rate;
993  $fr = 1.0 + $finance_rate;
994 
995  $npv_pos = $npv_neg = 0.0;
996  foreach($values as $i => $v) {
997  if ($v >= 0) {
998  $npv_pos += $v / pow($rr, $i);
999  } else {
1000  $npv_neg += $v / pow($fr, $i);
1001  }
1002  }
1003 
1004  if (($npv_neg == 0) || ($npv_pos == 0) || ($reinvestment_rate <= -1)) {
1005  return PHPExcel_Calculation_Functions::VALUE();
1006  }
1007 
1008  $mirr = pow((-$npv_pos * pow($rr, $n))
1009  / ($npv_neg * ($rr)), (1.0 / ($n - 1))) - 1.0;
1010 
1011  return (is_finite($mirr) ? $mirr : PHPExcel_Calculation_Functions::VALUE());
1012  } // function MIRR()
1013 
1014 
1024  public static function NOMINAL($effect_rate = 0, $npery = 0) {
1025  $effect_rate = PHPExcel_Calculation_Functions::flattenSingleValue($effect_rate);
1026  $npery = (int)PHPExcel_Calculation_Functions::flattenSingleValue($npery);
1027 
1028  // Validate parameters
1029  if ($effect_rate <= 0 || $npery < 1) {
1030  return PHPExcel_Calculation_Functions::NaN();
1031  }
1032 
1033  // Calculate
1034  return $npery * (pow($effect_rate + 1, 1 / $npery) - 1);
1035  } // function NOMINAL()
1036 
1037 
1050  public static function NPER($rate = 0, $pmt = 0, $pv = 0, $fv = 0, $type = 0) {
1051  $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1052  $pmt = PHPExcel_Calculation_Functions::flattenSingleValue($pmt);
1053  $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
1054  $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
1055  $type = PHPExcel_Calculation_Functions::flattenSingleValue($type);
1056 
1057  // Validate parameters
1058  if ($type != 0 && $type != 1) {
1059  return PHPExcel_Calculation_Functions::NaN();
1060  }
1061 
1062  // Calculate
1063  if (!is_null($rate) && $rate != 0) {
1064  if ($pmt == 0 && $pv == 0) {
1065  return PHPExcel_Calculation_Functions::NaN();
1066  }
1067  return log(($pmt * (1 + $rate * $type) / $rate - $fv) / ($pv + $pmt * (1 + $rate * $type) / $rate)) / log(1 + $rate);
1068  } else {
1069  if ($pmt == 0) {
1070  return PHPExcel_Calculation_Functions::NaN();
1071  }
1072  return (-$pv -$fv) / $pmt;
1073  }
1074  } // function NPER()
1075 
1076 
1086  public static function NPV() {
1087  // Return value
1088  $returnValue = 0;
1089 
1090  // Loop through arguments
1091  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
1092 
1093  // Calculate
1094  $rate = array_shift($aArgs);
1095  for ($i = 1; $i <= count($aArgs); ++$i) {
1096  // Is it a numeric value?
1097  if (is_numeric($aArgs[$i - 1])) {
1098  $returnValue += $aArgs[$i - 1] / pow(1 + $rate, $i);
1099  }
1100  }
1101 
1102  // Return
1103  return $returnValue;
1104  } // function NPV()
1105 
1106 
1119  public static function PMT($rate = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0) {
1120  $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1121  $nper = PHPExcel_Calculation_Functions::flattenSingleValue($nper);
1122  $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
1123  $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
1124  $type = PHPExcel_Calculation_Functions::flattenSingleValue($type);
1125 
1126  // Validate parameters
1127  if ($type != 0 && $type != 1) {
1128  return PHPExcel_Calculation_Functions::NaN();
1129  }
1130 
1131  // Calculate
1132  if (!is_null($rate) && $rate != 0) {
1133  return (-$fv - $pv * pow(1 + $rate, $nper)) / (1 + $rate * $type) / ((pow(1 + $rate, $nper) - 1) / $rate);
1134  } else {
1135  return (-$pv - $fv) / $nper;
1136  }
1137  } // function PMT()
1138 
1139 
1153  public static function PPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
1154  $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1155  $per = (int) PHPExcel_Calculation_Functions::flattenSingleValue($per);
1156  $nper = (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
1157  $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
1158  $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
1159  $type = (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
1160 
1161  // Validate parameters
1162  if ($type != 0 && $type != 1) {
1163  return PHPExcel_Calculation_Functions::NaN();
1164  }
1165  if ($per <= 0 || $per > $nper) {
1166  return PHPExcel_Calculation_Functions::VALUE();
1167  }
1168 
1169  // Calculate
1170  $interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
1171  return $interestAndPrincipal[1];
1172  } // function PPMT()
1173 
1174 
1175  public static function PRICE($settlement, $maturity, $rate, $yield, $redemption, $frequency, $basis=0) {
1176  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1177  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1178  $rate = (float) PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1179  $yield = (float) PHPExcel_Calculation_Functions::flattenSingleValue($yield);
1180  $redemption = (float) PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
1181  $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
1182  $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
1183 
1184  if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
1185  return PHPExcel_Calculation_Functions::VALUE();
1186  }
1187  if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
1188  return PHPExcel_Calculation_Functions::VALUE();
1189  }
1190 
1191  if (($settlement > $maturity) ||
1192  (!self::_validFrequency($frequency)) ||
1193  (($basis < 0) || ($basis > 4))) {
1194  return PHPExcel_Calculation_Functions::NaN();
1195  }
1196 
1197  $dsc = self::COUPDAYSNC($settlement, $maturity, $frequency, $basis);
1198  $e = self::COUPDAYS($settlement, $maturity, $frequency, $basis);
1199  $n = self::COUPNUM($settlement, $maturity, $frequency, $basis);
1200  $a = self::COUPDAYBS($settlement, $maturity, $frequency, $basis);
1201 
1202  $baseYF = 1.0 + ($yield / $frequency);
1203  $rfp = 100 * ($rate / $frequency);
1204  $de = $dsc / $e;
1205 
1206  $result = $redemption / pow($baseYF, (--$n + $de));
1207  for($k = 0; $k <= $n; ++$k) {
1208  $result += $rfp / (pow($baseYF, ($k + $de)));
1209  }
1210  $result -= $rfp * ($a / $e);
1211 
1212  return $result;
1213  } // function PRICE()
1214 
1215 
1235  public static function PRICEDISC($settlement, $maturity, $discount, $redemption, $basis=0) {
1236  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1237  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1238  $discount = (float) PHPExcel_Calculation_Functions::flattenSingleValue($discount);
1239  $redemption = (float) PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
1240  $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
1241 
1242  // Validate
1243  if ((is_numeric($discount)) && (is_numeric($redemption)) && (is_numeric($basis))) {
1244  if (($discount <= 0) || ($redemption <= 0)) {
1245  return PHPExcel_Calculation_Functions::NaN();
1246  }
1247  $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
1248  if (!is_numeric($daysBetweenSettlementAndMaturity)) {
1249  // return date error
1250  return $daysBetweenSettlementAndMaturity;
1251  }
1252 
1253  return $redemption * (1 - $discount * $daysBetweenSettlementAndMaturity);
1254  }
1255  return PHPExcel_Calculation_Functions::VALUE();
1256  } // function PRICEDISC()
1257 
1258 
1279  public static function PRICEMAT($settlement, $maturity, $issue, $rate, $yield, $basis=0) {
1280  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1281  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1282  $issue = PHPExcel_Calculation_Functions::flattenSingleValue($issue);
1283  $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1284  $yield = PHPExcel_Calculation_Functions::flattenSingleValue($yield);
1285  $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
1286 
1287  // Validate
1288  if (is_numeric($rate) && is_numeric($yield)) {
1289  if (($rate <= 0) || ($yield <= 0)) {
1290  return PHPExcel_Calculation_Functions::NaN();
1291  }
1292  $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
1293  if (!is_numeric($daysPerYear)) {
1294  return $daysPerYear;
1295  }
1296  $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
1297  if (!is_numeric($daysBetweenIssueAndSettlement)) {
1298  // return date error
1299  return $daysBetweenIssueAndSettlement;
1300  }
1301  $daysBetweenIssueAndSettlement *= $daysPerYear;
1302  $daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $maturity, $basis);
1303  if (!is_numeric($daysBetweenIssueAndMaturity)) {
1304  // return date error
1305  return $daysBetweenIssueAndMaturity;
1306  }
1307  $daysBetweenIssueAndMaturity *= $daysPerYear;
1308  $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
1309  if (!is_numeric($daysBetweenSettlementAndMaturity)) {
1310  // return date error
1311  return $daysBetweenSettlementAndMaturity;
1312  }
1313  $daysBetweenSettlementAndMaturity *= $daysPerYear;
1314 
1315  return ((100 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate * 100)) /
1316  (1 + (($daysBetweenSettlementAndMaturity / $daysPerYear) * $yield)) -
1317  (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate * 100));
1318  }
1319  return PHPExcel_Calculation_Functions::VALUE();
1320  } // function PRICEMAT()
1321 
1322 
1335  public static function PV($rate = 0, $nper = 0, $pmt = 0, $fv = 0, $type = 0) {
1336  $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1337  $nper = PHPExcel_Calculation_Functions::flattenSingleValue($nper);
1338  $pmt = PHPExcel_Calculation_Functions::flattenSingleValue($pmt);
1339  $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
1340  $type = PHPExcel_Calculation_Functions::flattenSingleValue($type);
1341 
1342  // Validate parameters
1343  if ($type != 0 && $type != 1) {
1344  return PHPExcel_Calculation_Functions::NaN();
1345  }
1346 
1347  // Calculate
1348  if (!is_null($rate) && $rate != 0) {
1349  return (-$pmt * (1 + $rate * $type) * ((pow(1 + $rate, $nper) - 1) / $rate) - $fv) / pow(1 + $rate, $nper);
1350  } else {
1351  return -$fv - $pmt * $nper;
1352  }
1353  } // function PV()
1354 
1355 
1360  public static function RATE($nper, $pmt, $pv, $fv = 0.0, $type = 0, $guess = 0.1) {
1361  $nper = (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
1362  $pmt = PHPExcel_Calculation_Functions::flattenSingleValue($pmt);
1363  $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
1364  $fv = (is_null($fv)) ? 0.0 : PHPExcel_Calculation_Functions::flattenSingleValue($fv);
1365  $type = (is_null($type)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
1366  $guess = (is_null($guess)) ? 0.1 : PHPExcel_Calculation_Functions::flattenSingleValue($guess);
1367 
1368  $rate = $guess;
1369  if (abs($rate) < FINANCIAL_PRECISION) {
1370  $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv;
1371  } else {
1372  $f = exp($nper * log(1 + $rate));
1373  $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
1374  }
1375  $y0 = $pv + $pmt * $nper + $fv;
1376  $y1 = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
1377 
1378  // find root by secant method
1379  $i = $x0 = 0.0;
1380  $x1 = $rate;
1381  while ((abs($y0 - $y1) > FINANCIAL_PRECISION) && ($i < FINANCIAL_MAX_ITERATIONS)) {
1382  $rate = ($y1 * $x0 - $y0 * $x1) / ($y1 - $y0);
1383  $x0 = $x1;
1384  $x1 = $rate;
1385 
1386  if (abs($rate) < FINANCIAL_PRECISION) {
1387  $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv;
1388  } else {
1389  $f = exp($nper * log(1 + $rate));
1390  $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
1391  }
1392 
1393  $y0 = $y1;
1394  $y1 = $y;
1395  ++$i;
1396  }
1397  return $rate;
1398  } // function RATE()
1399 
1400 
1420  public static function RECEIVED($settlement, $maturity, $investment, $discount, $basis=0) {
1421  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1422  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1423  $investment = (float) PHPExcel_Calculation_Functions::flattenSingleValue($investment);
1424  $discount = (float) PHPExcel_Calculation_Functions::flattenSingleValue($discount);
1425  $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
1426 
1427  // Validate
1428  if ((is_numeric($investment)) && (is_numeric($discount)) && (is_numeric($basis))) {
1429  if (($investment <= 0) || ($discount <= 0)) {
1430  return PHPExcel_Calculation_Functions::NaN();
1431  }
1432  $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
1433  if (!is_numeric($daysBetweenSettlementAndMaturity)) {
1434  // return date error
1435  return $daysBetweenSettlementAndMaturity;
1436  }
1437 
1438  return $investment / ( 1 - ($discount * $daysBetweenSettlementAndMaturity));
1439  }
1440  return PHPExcel_Calculation_Functions::VALUE();
1441  } // function RECEIVED()
1442 
1443 
1454  public static function SLN($cost, $salvage, $life) {
1455  $cost = PHPExcel_Calculation_Functions::flattenSingleValue($cost);
1456  $salvage = PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
1457  $life = PHPExcel_Calculation_Functions::flattenSingleValue($life);
1458 
1459  // Calculate
1460  if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life))) {
1461  if ($life < 0) {
1462  return PHPExcel_Calculation_Functions::NaN();
1463  }
1464  return ($cost - $salvage) / $life;
1465  }
1466  return PHPExcel_Calculation_Functions::VALUE();
1467  } // function SLN()
1468 
1469 
1481  public static function SYD($cost, $salvage, $life, $period) {
1482  $cost = PHPExcel_Calculation_Functions::flattenSingleValue($cost);
1483  $salvage = PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
1484  $life = PHPExcel_Calculation_Functions::flattenSingleValue($life);
1485  $period = PHPExcel_Calculation_Functions::flattenSingleValue($period);
1486 
1487  // Calculate
1488  if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period))) {
1489  if (($life < 1) || ($period > $life)) {
1490  return PHPExcel_Calculation_Functions::NaN();
1491  }
1492  return (($cost - $salvage) * ($life - $period + 1) * 2) / ($life * ($life + 1));
1493  }
1494  return PHPExcel_Calculation_Functions::VALUE();
1495  } // function SYD()
1496 
1497 
1510  public static function TBILLEQ($settlement, $maturity, $discount) {
1511  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1512  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1513  $discount = PHPExcel_Calculation_Functions::flattenSingleValue($discount);
1514 
1515  // Use TBILLPRICE for validation
1516  $testValue = self::TBILLPRICE($settlement, $maturity, $discount);
1517  if (is_string($testValue)) {
1518  return $testValue;
1519  }
1520 
1521  if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
1522  return PHPExcel_Calculation_Functions::VALUE();
1523  }
1524 
1525  if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
1526  ++$maturity;
1527  $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity) * 360;
1528  } else {
1529  $daysBetweenSettlementAndMaturity = (PHPExcel_Calculation_DateTime::_getDateValue($maturity) - PHPExcel_Calculation_DateTime::_getDateValue($settlement));
1530  }
1531 
1532  return (365 * $discount) / (360 - $discount * $daysBetweenSettlementAndMaturity);
1533  } // function TBILLEQ()
1534 
1535 
1548  public static function TBILLPRICE($settlement, $maturity, $discount) {
1549  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1550  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1551  $discount = PHPExcel_Calculation_Functions::flattenSingleValue($discount);
1552 
1553  if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
1554  return PHPExcel_Calculation_Functions::VALUE();
1555  }
1556 
1557  // Validate
1558  if (is_numeric($discount)) {
1559  if ($discount <= 0) {
1560  return PHPExcel_Calculation_Functions::NaN();
1561  }
1562 
1563  if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
1564  ++$maturity;
1565  $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity) * 360;
1566  if (!is_numeric($daysBetweenSettlementAndMaturity)) {
1567  // return date error
1568  return $daysBetweenSettlementAndMaturity;
1569  }
1570  } else {
1571  $daysBetweenSettlementAndMaturity = (PHPExcel_Calculation_DateTime::_getDateValue($maturity) - PHPExcel_Calculation_DateTime::_getDateValue($settlement));
1572  }
1573 
1574  if ($daysBetweenSettlementAndMaturity > 360) {
1575  return PHPExcel_Calculation_Functions::NaN();
1576  }
1577 
1578  $price = 100 * (1 - (($discount * $daysBetweenSettlementAndMaturity) / 360));
1579  if ($price <= 0) {
1580  return PHPExcel_Calculation_Functions::NaN();
1581  }
1582  return $price;
1583  }
1584  return PHPExcel_Calculation_Functions::VALUE();
1585  } // function TBILLPRICE()
1586 
1587 
1600  public static function TBILLYIELD($settlement, $maturity, $price) {
1601  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1602  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1603  $price = PHPExcel_Calculation_Functions::flattenSingleValue($price);
1604 
1605  // Validate
1606  if (is_numeric($price)) {
1607  if ($price <= 0) {
1608  return PHPExcel_Calculation_Functions::NaN();
1609  }
1610 
1611  if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
1612  ++$maturity;
1613  $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity) * 360;
1614  if (!is_numeric($daysBetweenSettlementAndMaturity)) {
1615  // return date error
1616  return $daysBetweenSettlementAndMaturity;
1617  }
1618  } else {
1619  $daysBetweenSettlementAndMaturity = (PHPExcel_Calculation_DateTime::_getDateValue($maturity) - PHPExcel_Calculation_DateTime::_getDateValue($settlement));
1620  }
1621 
1622  if ($daysBetweenSettlementAndMaturity > 360) {
1623  return PHPExcel_Calculation_Functions::NaN();
1624  }
1625 
1626  return ((100 - $price) / $price) * (360 / $daysBetweenSettlementAndMaturity);
1627  }
1628  return PHPExcel_Calculation_Functions::VALUE();
1629  } // function TBILLYIELD()
1630 
1631 
1632  public static function XIRR($values, $dates, $guess = 0.1) {
1633  if ((!is_array($values)) && (!is_array($dates))) return PHPExcel_Calculation_Functions::VALUE();
1634  $values = PHPExcel_Calculation_Functions::flattenArray($values);
1635  $dates = PHPExcel_Calculation_Functions::flattenArray($dates);
1636  $guess = PHPExcel_Calculation_Functions::flattenSingleValue($guess);
1637  if (count($values) != count($dates)) return PHPExcel_Calculation_Functions::NaN();
1638 
1639  // create an initial range, with a root somewhere between 0 and guess
1640  $x1 = 0.0;
1641  $x2 = $guess;
1642  $f1 = self::XNPV($x1, $values, $dates);
1643  $f2 = self::XNPV($x2, $values, $dates);
1644  for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
1645  if (($f1 * $f2) < 0.0) break;
1646  if (abs($f1) < abs($f2)) {
1647  $f1 = self::XNPV($x1 += 1.6 * ($x1 - $x2), $values, $dates);
1648  } else {
1649  $f2 = self::XNPV($x2 += 1.6 * ($x2 - $x1), $values, $dates);
1650  }
1651  }
1652  if (($f1 * $f2) > 0.0) return PHPExcel_Calculation_Functions::VALUE();
1653 
1654  $f = self::XNPV($x1, $values, $dates);
1655  if ($f < 0.0) {
1656  $rtb = $x1;
1657  $dx = $x2 - $x1;
1658  } else {
1659  $rtb = $x2;
1660  $dx = $x1 - $x2;
1661  }
1662 
1663  for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
1664  $dx *= 0.5;
1665  $x_mid = $rtb + $dx;
1666  $f_mid = self::XNPV($x_mid, $values, $dates);
1667  if ($f_mid <= 0.0) $rtb = $x_mid;
1668  if ((abs($f_mid) < FINANCIAL_PRECISION) || (abs($dx) < FINANCIAL_PRECISION)) return $x_mid;
1669  }
1670  return PHPExcel_Calculation_Functions::VALUE();
1671  }
1672 
1673 
1688  public static function XNPV($rate, $values, $dates) {
1689  $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1690  if (!is_numeric($rate)) return PHPExcel_Calculation_Functions::VALUE();
1691  if ((!is_array($values)) || (!is_array($dates))) return PHPExcel_Calculation_Functions::VALUE();
1692  $values = PHPExcel_Calculation_Functions::flattenArray($values);
1693  $dates = PHPExcel_Calculation_Functions::flattenArray($dates);
1694  $valCount = count($values);
1695  if ($valCount != count($dates)) return PHPExcel_Calculation_Functions::NaN();
1696  if ((min($values) > 0) || (max($values) < 0)) return PHPExcel_Calculation_Functions::VALUE();
1697 
1698  $xnpv = 0.0;
1699  for ($i = 0; $i < $valCount; ++$i) {
1700  if (!is_numeric($values[$i])) return PHPExcel_Calculation_Functions::VALUE();
1701  $xnpv += $values[$i] / pow(1 + $rate, PHPExcel_Calculation_DateTime::DATEDIF($dates[0],$dates[$i],'d') / 365);
1702  }
1703  return (is_finite($xnpv)) ? $xnpv : PHPExcel_Calculation_Functions::VALUE();
1704  } // function XNPV()
1705 
1706 
1726  public static function YIELDDISC($settlement, $maturity, $price, $redemption, $basis=0) {
1727  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1728  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1729  $price = PHPExcel_Calculation_Functions::flattenSingleValue($price);
1730  $redemption = PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
1731  $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
1732 
1733  // Validate
1734  if (is_numeric($price) && is_numeric($redemption)) {
1735  if (($price <= 0) || ($redemption <= 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  $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity,$basis);
1743  if (!is_numeric($daysBetweenSettlementAndMaturity)) {
1744  // return date error
1745  return $daysBetweenSettlementAndMaturity;
1746  }
1747  $daysBetweenSettlementAndMaturity *= $daysPerYear;
1748 
1749  return (($redemption - $price) / $price) * ($daysPerYear / $daysBetweenSettlementAndMaturity);
1750  }
1751  return PHPExcel_Calculation_Functions::VALUE();
1752  } // function YIELDDISC()
1753 
1754 
1775  public static function YIELDMAT($settlement, $maturity, $issue, $rate, $price, $basis=0) {
1776  $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
1777  $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
1778  $issue = PHPExcel_Calculation_Functions::flattenSingleValue($issue);
1779  $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
1780  $price = PHPExcel_Calculation_Functions::flattenSingleValue($price);
1781  $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
1782 
1783  // Validate
1784  if (is_numeric($rate) && is_numeric($price)) {
1785  if (($rate <= 0) || ($price <= 0)) {
1786  return PHPExcel_Calculation_Functions::NaN();
1787  }
1788  $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
1789  if (!is_numeric($daysPerYear)) {
1790  return $daysPerYear;
1791  }
1792  $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
1793  if (!is_numeric($daysBetweenIssueAndSettlement)) {
1794  // return date error
1795  return $daysBetweenIssueAndSettlement;
1796  }
1797  $daysBetweenIssueAndSettlement *= $daysPerYear;
1798  $daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $maturity, $basis);
1799  if (!is_numeric($daysBetweenIssueAndMaturity)) {
1800  // return date error
1801  return $daysBetweenIssueAndMaturity;
1802  }
1803  $daysBetweenIssueAndMaturity *= $daysPerYear;
1804  $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
1805  if (!is_numeric($daysBetweenSettlementAndMaturity)) {
1806  // return date error
1807  return $daysBetweenSettlementAndMaturity;
1808  }
1809  $daysBetweenSettlementAndMaturity *= $daysPerYear;
1810 
1811  return ((1 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate) - (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) /
1812  (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) *
1813  ($daysPerYear / $daysBetweenSettlementAndMaturity);
1814  }
1815  return PHPExcel_Calculation_Functions::VALUE();
1816  } // function YIELDMAT()
1817 
1818 } // class PHPExcel_Calculation_Financial