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