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