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