1<?php 2 3namespace PhpOffice\PhpSpreadsheet\Calculation; 4 5use PhpOffice\PhpSpreadsheet\Shared\Date; 6 7class 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 $settlement = self::couponFirstPeriodDate($settlement, $maturity, $frequency, true); 677 $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis) * 365; 678 679 switch ($frequency) { 680 case 1: // annual payments 681 return ceil($daysBetweenSettlementAndMaturity / 360); 682 case 2: // half-yearly 683 return ceil($daysBetweenSettlementAndMaturity / 180); 684 case 4: // quarterly 685 return ceil($daysBetweenSettlementAndMaturity / 90); 686 case 6: // bimonthly 687 return ceil($daysBetweenSettlementAndMaturity / 60); 688 case 12: // monthly 689 return ceil($daysBetweenSettlementAndMaturity / 30); 690 } 691 692 return Functions::VALUE(); 693 } 694 695 /** 696 * COUPPCD. 697 * 698 * Returns the previous coupon date before the settlement date. 699 * 700 * Excel Function: 701 * COUPPCD(settlement,maturity,frequency[,basis]) 702 * 703 * @category Financial Functions 704 * 705 * @param mixed $settlement The security's settlement date. 706 * The security settlement date is the date after the issue 707 * date when the security is traded to the buyer. 708 * @param mixed $maturity The security's maturity date. 709 * The maturity date is the date when the security expires. 710 * @param mixed $frequency the number of coupon payments per year. 711 * Valid frequency values are: 712 * 1 Annual 713 * 2 Semi-Annual 714 * 4 Quarterly 715 * If working in Gnumeric Mode, the following frequency options are 716 * also available 717 * 6 Bimonthly 718 * 12 Monthly 719 * @param int $basis The type of day count to use. 720 * 0 or omitted US (NASD) 30/360 721 * 1 Actual/actual 722 * 2 Actual/360 723 * 3 Actual/365 724 * 4 European 30/360 725 * 726 * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, 727 * depending on the value of the ReturnDateType flag 728 */ 729 public static function COUPPCD($settlement, $maturity, $frequency, $basis = 0) 730 { 731 $settlement = Functions::flattenSingleValue($settlement); 732 $maturity = Functions::flattenSingleValue($maturity); 733 $frequency = (int) Functions::flattenSingleValue($frequency); 734 $basis = ($basis === null) ? 0 : (int) Functions::flattenSingleValue($basis); 735 736 if (is_string($settlement = DateTime::getDateValue($settlement))) { 737 return Functions::VALUE(); 738 } 739 if (is_string($maturity = DateTime::getDateValue($maturity))) { 740 return Functions::VALUE(); 741 } 742 743 if (($settlement > $maturity) || 744 (!self::isValidFrequency($frequency)) || 745 (($basis < 0) || ($basis > 4))) { 746 return Functions::NAN(); 747 } 748 749 return self::couponFirstPeriodDate($settlement, $maturity, $frequency, false); 750 } 751 752 /** 753 * CUMIPMT. 754 * 755 * Returns the cumulative interest paid on a loan between the start and end periods. 756 * 757 * Excel Function: 758 * CUMIPMT(rate,nper,pv,start,end[,type]) 759 * 760 * @category Financial Functions 761 * 762 * @param float $rate The Interest rate 763 * @param int $nper The total number of payment periods 764 * @param float $pv Present Value 765 * @param int $start The first period in the calculation. 766 * Payment periods are numbered beginning with 1. 767 * @param int $end the last period in the calculation 768 * @param int $type A number 0 or 1 and indicates when payments are due: 769 * 0 or omitted At the end of the period. 770 * 1 At the beginning of the period. 771 * 772 * @return float|string 773 */ 774 public static function CUMIPMT($rate, $nper, $pv, $start, $end, $type = 0) 775 { 776 $rate = Functions::flattenSingleValue($rate); 777 $nper = (int) Functions::flattenSingleValue($nper); 778 $pv = Functions::flattenSingleValue($pv); 779 $start = (int) Functions::flattenSingleValue($start); 780 $end = (int) Functions::flattenSingleValue($end); 781 $type = (int) Functions::flattenSingleValue($type); 782 783 // Validate parameters 784 if ($type != 0 && $type != 1) { 785 return Functions::NAN(); 786 } 787 if ($start < 1 || $start > $end) { 788 return Functions::VALUE(); 789 } 790 791 // Calculate 792 $interest = 0; 793 for ($per = $start; $per <= $end; ++$per) { 794 $interest += self::IPMT($rate, $per, $nper, $pv, 0, $type); 795 } 796 797 return $interest; 798 } 799 800 /** 801 * CUMPRINC. 802 * 803 * Returns the cumulative principal paid on a loan between the start and end periods. 804 * 805 * Excel Function: 806 * CUMPRINC(rate,nper,pv,start,end[,type]) 807 * 808 * @category Financial Functions 809 * 810 * @param float $rate The Interest rate 811 * @param int $nper The total number of payment periods 812 * @param float $pv Present Value 813 * @param int $start The first period in the calculation. 814 * Payment periods are numbered beginning with 1. 815 * @param int $end the last period in the calculation 816 * @param int $type A number 0 or 1 and indicates when payments are due: 817 * 0 or omitted At the end of the period. 818 * 1 At the beginning of the period. 819 * 820 * @return float|string 821 */ 822 public static function CUMPRINC($rate, $nper, $pv, $start, $end, $type = 0) 823 { 824 $rate = Functions::flattenSingleValue($rate); 825 $nper = (int) Functions::flattenSingleValue($nper); 826 $pv = Functions::flattenSingleValue($pv); 827 $start = (int) Functions::flattenSingleValue($start); 828 $end = (int) Functions::flattenSingleValue($end); 829 $type = (int) Functions::flattenSingleValue($type); 830 831 // Validate parameters 832 if ($type != 0 && $type != 1) { 833 return Functions::NAN(); 834 } 835 if ($start < 1 || $start > $end) { 836 return Functions::VALUE(); 837 } 838 839 // Calculate 840 $principal = 0; 841 for ($per = $start; $per <= $end; ++$per) { 842 $principal += self::PPMT($rate, $per, $nper, $pv, 0, $type); 843 } 844 845 return $principal; 846 } 847 848 /** 849 * DB. 850 * 851 * Returns the depreciation of an asset for a specified period using the 852 * fixed-declining balance method. 853 * This form of depreciation is used if you want to get a higher depreciation value 854 * at the beginning of the depreciation (as opposed to linear depreciation). The 855 * depreciation value is reduced with every depreciation period by the depreciation 856 * already deducted from the initial cost. 857 * 858 * Excel Function: 859 * DB(cost,salvage,life,period[,month]) 860 * 861 * @category Financial Functions 862 * 863 * @param float $cost Initial cost of the asset 864 * @param float $salvage Value at the end of the depreciation. 865 * (Sometimes called the salvage value of the asset) 866 * @param int $life Number of periods over which the asset is depreciated. 867 * (Sometimes called the useful life of the asset) 868 * @param int $period The period for which you want to calculate the 869 * depreciation. Period must use the same units as life. 870 * @param int $month Number of months in the first year. If month is omitted, 871 * it defaults to 12. 872 * 873 * @return float|string 874 */ 875 public static function DB($cost, $salvage, $life, $period, $month = 12) 876 { 877 $cost = Functions::flattenSingleValue($cost); 878 $salvage = Functions::flattenSingleValue($salvage); 879 $life = Functions::flattenSingleValue($life); 880 $period = Functions::flattenSingleValue($period); 881 $month = Functions::flattenSingleValue($month); 882 883 // Validate 884 if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period)) && (is_numeric($month))) { 885 $cost = (float) $cost; 886 $salvage = (float) $salvage; 887 $life = (int) $life; 888 $period = (int) $period; 889 $month = (int) $month; 890 if ($cost == 0) { 891 return 0.0; 892 } elseif (($cost < 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($month < 1)) { 893 return Functions::NAN(); 894 } 895 // Set Fixed Depreciation Rate 896 $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life)); 897 $fixedDepreciationRate = round($fixedDepreciationRate, 3); 898 899 // Loop through each period calculating the depreciation 900 $previousDepreciation = 0; 901 for ($per = 1; $per <= $period; ++$per) { 902 if ($per == 1) { 903 $depreciation = $cost * $fixedDepreciationRate * $month / 12; 904 } elseif ($per == ($life + 1)) { 905 $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate * (12 - $month) / 12; 906 } else { 907 $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate; 908 } 909 $previousDepreciation += $depreciation; 910 } 911 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { 912 $depreciation = round($depreciation, 2); 913 } 914 915 return $depreciation; 916 } 917 918 return Functions::VALUE(); 919 } 920 921 /** 922 * DDB. 923 * 924 * Returns the depreciation of an asset for a specified period using the 925 * double-declining balance method or some other method you specify. 926 * 927 * Excel Function: 928 * DDB(cost,salvage,life,period[,factor]) 929 * 930 * @category Financial Functions 931 * 932 * @param float $cost Initial cost of the asset 933 * @param float $salvage Value at the end of the depreciation. 934 * (Sometimes called the salvage value of the asset) 935 * @param int $life Number of periods over which the asset is depreciated. 936 * (Sometimes called the useful life of the asset) 937 * @param int $period The period for which you want to calculate the 938 * depreciation. Period must use the same units as life. 939 * @param float $factor The rate at which the balance declines. 940 * If factor is omitted, it is assumed to be 2 (the 941 * double-declining balance method). 942 * 943 * @return float|string 944 */ 945 public static function DDB($cost, $salvage, $life, $period, $factor = 2.0) 946 { 947 $cost = Functions::flattenSingleValue($cost); 948 $salvage = Functions::flattenSingleValue($salvage); 949 $life = Functions::flattenSingleValue($life); 950 $period = Functions::flattenSingleValue($period); 951 $factor = Functions::flattenSingleValue($factor); 952 953 // Validate 954 if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period)) && (is_numeric($factor))) { 955 $cost = (float) $cost; 956 $salvage = (float) $salvage; 957 $life = (int) $life; 958 $period = (int) $period; 959 $factor = (float) $factor; 960 if (($cost <= 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($factor <= 0.0) || ($period > $life)) { 961 return Functions::NAN(); 962 } 963 // Set Fixed Depreciation Rate 964 $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life)); 965 $fixedDepreciationRate = round($fixedDepreciationRate, 3); 966 967 // Loop through each period calculating the depreciation 968 $previousDepreciation = 0; 969 for ($per = 1; $per <= $period; ++$per) { 970 $depreciation = min(($cost - $previousDepreciation) * ($factor / $life), ($cost - $salvage - $previousDepreciation)); 971 $previousDepreciation += $depreciation; 972 } 973 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { 974 $depreciation = round($depreciation, 2); 975 } 976 977 return $depreciation; 978 } 979 980 return Functions::VALUE(); 981 } 982 983 /** 984 * DISC. 985 * 986 * Returns the discount rate for a security. 987 * 988 * Excel Function: 989 * DISC(settlement,maturity,price,redemption[,basis]) 990 * 991 * @category Financial Functions 992 * 993 * @param mixed $settlement The security's settlement date. 994 * The security settlement date is the date after the issue 995 * date when the security is traded to the buyer. 996 * @param mixed $maturity The security's maturity date. 997 * The maturity date is the date when the security expires. 998 * @param int $price The security's price per $100 face value 999 * @param int $redemption The security's redemption value per $100 face value 1000 * @param int $basis The type of day count to use. 1001 * 0 or omitted US (NASD) 30/360 1002 * 1 Actual/actual 1003 * 2 Actual/360 1004 * 3 Actual/365 1005 * 4 European 30/360 1006 * 1007 * @return float|string 1008 */ 1009 public static function DISC($settlement, $maturity, $price, $redemption, $basis = 0) 1010 { 1011 $settlement = Functions::flattenSingleValue($settlement); 1012 $maturity = Functions::flattenSingleValue($maturity); 1013 $price = Functions::flattenSingleValue($price); 1014 $redemption = Functions::flattenSingleValue($redemption); 1015 $basis = Functions::flattenSingleValue($basis); 1016 1017 // Validate 1018 if ((is_numeric($price)) && (is_numeric($redemption)) && (is_numeric($basis))) { 1019 $price = (float) $price; 1020 $redemption = (float) $redemption; 1021 $basis = (int) $basis; 1022 if (($price <= 0) || ($redemption <= 0)) { 1023 return Functions::NAN(); 1024 } 1025 $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); 1026 if (!is_numeric($daysBetweenSettlementAndMaturity)) { 1027 // return date error 1028 return $daysBetweenSettlementAndMaturity; 1029 } 1030 1031 return (1 - $price / $redemption) / $daysBetweenSettlementAndMaturity; 1032 } 1033 1034 return Functions::VALUE(); 1035 } 1036 1037 /** 1038 * DOLLARDE. 1039 * 1040 * Converts a dollar price expressed as an integer part and a fraction 1041 * part into a dollar price expressed as a decimal number. 1042 * Fractional dollar numbers are sometimes used for security prices. 1043 * 1044 * Excel Function: 1045 * DOLLARDE(fractional_dollar,fraction) 1046 * 1047 * @category Financial Functions 1048 * 1049 * @param float $fractional_dollar Fractional Dollar 1050 * @param int $fraction Fraction 1051 * 1052 * @return float|string 1053 */ 1054 public static function DOLLARDE($fractional_dollar = null, $fraction = 0) 1055 { 1056 $fractional_dollar = Functions::flattenSingleValue($fractional_dollar); 1057 $fraction = (int) Functions::flattenSingleValue($fraction); 1058 1059 // Validate parameters 1060 if ($fractional_dollar === null || $fraction < 0) { 1061 return Functions::NAN(); 1062 } 1063 if ($fraction == 0) { 1064 return Functions::DIV0(); 1065 } 1066 1067 $dollars = floor($fractional_dollar); 1068 $cents = fmod($fractional_dollar, 1); 1069 $cents /= $fraction; 1070 $cents *= pow(10, ceil(log10($fraction))); 1071 1072 return $dollars + $cents; 1073 } 1074 1075 /** 1076 * DOLLARFR. 1077 * 1078 * Converts a dollar price expressed as a decimal number into a dollar price 1079 * expressed as a fraction. 1080 * Fractional dollar numbers are sometimes used for security prices. 1081 * 1082 * Excel Function: 1083 * DOLLARFR(decimal_dollar,fraction) 1084 * 1085 * @category Financial Functions 1086 * 1087 * @param float $decimal_dollar Decimal Dollar 1088 * @param int $fraction Fraction 1089 * 1090 * @return float|string 1091 */ 1092 public static function DOLLARFR($decimal_dollar = null, $fraction = 0) 1093 { 1094 $decimal_dollar = Functions::flattenSingleValue($decimal_dollar); 1095 $fraction = (int) Functions::flattenSingleValue($fraction); 1096 1097 // Validate parameters 1098 if ($decimal_dollar === null || $fraction < 0) { 1099 return Functions::NAN(); 1100 } 1101 if ($fraction == 0) { 1102 return Functions::DIV0(); 1103 } 1104 1105 $dollars = floor($decimal_dollar); 1106 $cents = fmod($decimal_dollar, 1); 1107 $cents *= $fraction; 1108 $cents *= pow(10, -ceil(log10($fraction))); 1109 1110 return $dollars + $cents; 1111 } 1112 1113 /** 1114 * EFFECT. 1115 * 1116 * Returns the effective interest rate given the nominal rate and the number of 1117 * compounding payments per year. 1118 * 1119 * Excel Function: 1120 * EFFECT(nominal_rate,npery) 1121 * 1122 * @category Financial Functions 1123 * 1124 * @param float $nominal_rate Nominal interest rate 1125 * @param int $npery Number of compounding payments per year 1126 * 1127 * @return float|string 1128 */ 1129 public static function EFFECT($nominal_rate = 0, $npery = 0) 1130 { 1131 $nominal_rate = Functions::flattenSingleValue($nominal_rate); 1132 $npery = (int) Functions::flattenSingleValue($npery); 1133 1134 // Validate parameters 1135 if ($nominal_rate <= 0 || $npery < 1) { 1136 return Functions::NAN(); 1137 } 1138 1139 return pow((1 + $nominal_rate / $npery), $npery) - 1; 1140 } 1141 1142 /** 1143 * FV. 1144 * 1145 * Returns the Future Value of a cash flow with constant payments and interest rate (annuities). 1146 * 1147 * Excel Function: 1148 * FV(rate,nper,pmt[,pv[,type]]) 1149 * 1150 * @category Financial Functions 1151 * 1152 * @param float $rate The interest rate per period 1153 * @param int $nper Total number of payment periods in an annuity 1154 * @param float $pmt The payment made each period: it cannot change over the 1155 * life of the annuity. Typically, pmt contains principal 1156 * and interest but no other fees or taxes. 1157 * @param float $pv present Value, or the lump-sum amount that a series of 1158 * future payments is worth right now 1159 * @param int $type A number 0 or 1 and indicates when payments are due: 1160 * 0 or omitted At the end of the period. 1161 * 1 At the beginning of the period. 1162 * 1163 * @return float|string 1164 */ 1165 public static function FV($rate = 0, $nper = 0, $pmt = 0, $pv = 0, $type = 0) 1166 { 1167 $rate = Functions::flattenSingleValue($rate); 1168 $nper = Functions::flattenSingleValue($nper); 1169 $pmt = Functions::flattenSingleValue($pmt); 1170 $pv = Functions::flattenSingleValue($pv); 1171 $type = Functions::flattenSingleValue($type); 1172 1173 // Validate parameters 1174 if ($type != 0 && $type != 1) { 1175 return Functions::NAN(); 1176 } 1177 1178 // Calculate 1179 if ($rate !== null && $rate != 0) { 1180 return -$pv * pow(1 + $rate, $nper) - $pmt * (1 + $rate * $type) * (pow(1 + $rate, $nper) - 1) / $rate; 1181 } 1182 1183 return -$pv - $pmt * $nper; 1184 } 1185 1186 /** 1187 * FVSCHEDULE. 1188 * 1189 * Returns the future value of an initial principal after applying a series of compound interest rates. 1190 * Use FVSCHEDULE to calculate the future value of an investment with a variable or adjustable rate. 1191 * 1192 * Excel Function: 1193 * FVSCHEDULE(principal,schedule) 1194 * 1195 * @param float $principal the present value 1196 * @param float[] $schedule an array of interest rates to apply 1197 * 1198 * @return float 1199 */ 1200 public static function FVSCHEDULE($principal, $schedule) 1201 { 1202 $principal = Functions::flattenSingleValue($principal); 1203 $schedule = Functions::flattenArray($schedule); 1204 1205 foreach ($schedule as $rate) { 1206 $principal *= 1 + $rate; 1207 } 1208 1209 return $principal; 1210 } 1211 1212 /** 1213 * INTRATE. 1214 * 1215 * Returns the interest rate for a fully invested security. 1216 * 1217 * Excel Function: 1218 * INTRATE(settlement,maturity,investment,redemption[,basis]) 1219 * 1220 * @param mixed $settlement The security's settlement date. 1221 * The security settlement date is the date after the issue date when the security is traded to the buyer. 1222 * @param mixed $maturity The security's maturity date. 1223 * The maturity date is the date when the security expires. 1224 * @param int $investment the amount invested in the security 1225 * @param int $redemption the amount to be received at maturity 1226 * @param int $basis The type of day count to use. 1227 * 0 or omitted US (NASD) 30/360 1228 * 1 Actual/actual 1229 * 2 Actual/360 1230 * 3 Actual/365 1231 * 4 European 30/360 1232 * 1233 * @return float|string 1234 */ 1235 public static function INTRATE($settlement, $maturity, $investment, $redemption, $basis = 0) 1236 { 1237 $settlement = Functions::flattenSingleValue($settlement); 1238 $maturity = Functions::flattenSingleValue($maturity); 1239 $investment = Functions::flattenSingleValue($investment); 1240 $redemption = Functions::flattenSingleValue($redemption); 1241 $basis = Functions::flattenSingleValue($basis); 1242 1243 // Validate 1244 if ((is_numeric($investment)) && (is_numeric($redemption)) && (is_numeric($basis))) { 1245 $investment = (float) $investment; 1246 $redemption = (float) $redemption; 1247 $basis = (int) $basis; 1248 if (($investment <= 0) || ($redemption <= 0)) { 1249 return Functions::NAN(); 1250 } 1251 $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); 1252 if (!is_numeric($daysBetweenSettlementAndMaturity)) { 1253 // return date error 1254 return $daysBetweenSettlementAndMaturity; 1255 } 1256 1257 return (($redemption / $investment) - 1) / ($daysBetweenSettlementAndMaturity); 1258 } 1259 1260 return Functions::VALUE(); 1261 } 1262 1263 /** 1264 * IPMT. 1265 * 1266 * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. 1267 * 1268 * Excel Function: 1269 * IPMT(rate,per,nper,pv[,fv][,type]) 1270 * 1271 * @param float $rate Interest rate per period 1272 * @param int $per Period for which we want to find the interest 1273 * @param int $nper Number of periods 1274 * @param float $pv Present Value 1275 * @param float $fv Future Value 1276 * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period 1277 * 1278 * @return float|string 1279 */ 1280 public static function IPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) 1281 { 1282 $rate = Functions::flattenSingleValue($rate); 1283 $per = (int) Functions::flattenSingleValue($per); 1284 $nper = (int) Functions::flattenSingleValue($nper); 1285 $pv = Functions::flattenSingleValue($pv); 1286 $fv = Functions::flattenSingleValue($fv); 1287 $type = (int) Functions::flattenSingleValue($type); 1288 1289 // Validate parameters 1290 if ($type != 0 && $type != 1) { 1291 return Functions::NAN(); 1292 } 1293 if ($per <= 0 || $per > $nper) { 1294 return Functions::VALUE(); 1295 } 1296 1297 // Calculate 1298 $interestAndPrincipal = self::interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type); 1299 1300 return $interestAndPrincipal[0]; 1301 } 1302 1303 /** 1304 * IRR. 1305 * 1306 * Returns the internal rate of return for a series of cash flows represented by the numbers in values. 1307 * These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur 1308 * at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received 1309 * for an investment consisting of payments (negative values) and income (positive values) that occur at regular 1310 * periods. 1311 * 1312 * Excel Function: 1313 * IRR(values[,guess]) 1314 * 1315 * @param float[] $values An array or a reference to cells that contain numbers for which you want 1316 * to calculate the internal rate of return. 1317 * Values must contain at least one positive value and one negative value to 1318 * calculate the internal rate of return. 1319 * @param float $guess A number that you guess is close to the result of IRR 1320 * 1321 * @return float|string 1322 */ 1323 public static function IRR($values, $guess = 0.1) 1324 { 1325 if (!is_array($values)) { 1326 return Functions::VALUE(); 1327 } 1328 $values = Functions::flattenArray($values); 1329 $guess = Functions::flattenSingleValue($guess); 1330 1331 // create an initial range, with a root somewhere between 0 and guess 1332 $x1 = 0.0; 1333 $x2 = $guess; 1334 $f1 = self::NPV($x1, $values); 1335 $f2 = self::NPV($x2, $values); 1336 for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) { 1337 if (($f1 * $f2) < 0.0) { 1338 break; 1339 } 1340 if (abs($f1) < abs($f2)) { 1341 $f1 = self::NPV($x1 += 1.6 * ($x1 - $x2), $values); 1342 } else { 1343 $f2 = self::NPV($x2 += 1.6 * ($x2 - $x1), $values); 1344 } 1345 } 1346 if (($f1 * $f2) > 0.0) { 1347 return Functions::VALUE(); 1348 } 1349 1350 $f = self::NPV($x1, $values); 1351 if ($f < 0.0) { 1352 $rtb = $x1; 1353 $dx = $x2 - $x1; 1354 } else { 1355 $rtb = $x2; 1356 $dx = $x1 - $x2; 1357 } 1358 1359 for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) { 1360 $dx *= 0.5; 1361 $x_mid = $rtb + $dx; 1362 $f_mid = self::NPV($x_mid, $values); 1363 if ($f_mid <= 0.0) { 1364 $rtb = $x_mid; 1365 } 1366 if ((abs($f_mid) < self::FINANCIAL_PRECISION) || (abs($dx) < self::FINANCIAL_PRECISION)) { 1367 return $x_mid; 1368 } 1369 } 1370 1371 return Functions::VALUE(); 1372 } 1373 1374 /** 1375 * ISPMT. 1376 * 1377 * Returns the interest payment for an investment based on an interest rate and a constant payment schedule. 1378 * 1379 * Excel Function: 1380 * =ISPMT(interest_rate, period, number_payments, PV) 1381 * 1382 * interest_rate is the interest rate for the investment 1383 * 1384 * period is the period to calculate the interest rate. It must be betweeen 1 and number_payments. 1385 * 1386 * number_payments is the number of payments for the annuity 1387 * 1388 * PV is the loan amount or present value of the payments 1389 */ 1390 public static function ISPMT(...$args) 1391 { 1392 // Return value 1393 $returnValue = 0; 1394 1395 // Get the parameters 1396 $aArgs = Functions::flattenArray($args); 1397 $interestRate = array_shift($aArgs); 1398 $period = array_shift($aArgs); 1399 $numberPeriods = array_shift($aArgs); 1400 $principleRemaining = array_shift($aArgs); 1401 1402 // Calculate 1403 $principlePayment = ($principleRemaining * 1.0) / ($numberPeriods * 1.0); 1404 for ($i = 0; $i <= $period; ++$i) { 1405 $returnValue = $interestRate * $principleRemaining * -1; 1406 $principleRemaining -= $principlePayment; 1407 // principle needs to be 0 after the last payment, don't let floating point screw it up 1408 if ($i == $numberPeriods) { 1409 $returnValue = 0; 1410 } 1411 } 1412 1413 return $returnValue; 1414 } 1415 1416 /** 1417 * MIRR. 1418 * 1419 * Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both 1420 * the cost of the investment and the interest received on reinvestment of cash. 1421 * 1422 * Excel Function: 1423 * MIRR(values,finance_rate, reinvestment_rate) 1424 * 1425 * @param float[] $values An array or a reference to cells that contain a series of payments and 1426 * income occurring at regular intervals. 1427 * Payments are negative value, income is positive values. 1428 * @param float $finance_rate The interest rate you pay on the money used in the cash flows 1429 * @param float $reinvestment_rate The interest rate you receive on the cash flows as you reinvest them 1430 * 1431 * @return float|string 1432 */ 1433 public static function MIRR($values, $finance_rate, $reinvestment_rate) 1434 { 1435 if (!is_array($values)) { 1436 return Functions::VALUE(); 1437 } 1438 $values = Functions::flattenArray($values); 1439 $finance_rate = Functions::flattenSingleValue($finance_rate); 1440 $reinvestment_rate = Functions::flattenSingleValue($reinvestment_rate); 1441 $n = count($values); 1442 1443 $rr = 1.0 + $reinvestment_rate; 1444 $fr = 1.0 + $finance_rate; 1445 1446 $npv_pos = $npv_neg = 0.0; 1447 foreach ($values as $i => $v) { 1448 if ($v >= 0) { 1449 $npv_pos += $v / pow($rr, $i); 1450 } else { 1451 $npv_neg += $v / pow($fr, $i); 1452 } 1453 } 1454 1455 if (($npv_neg == 0) || ($npv_pos == 0) || ($reinvestment_rate <= -1)) { 1456 return Functions::VALUE(); 1457 } 1458 1459 $mirr = pow((-$npv_pos * pow($rr, $n)) 1460 / ($npv_neg * ($rr)), (1.0 / ($n - 1))) - 1.0; 1461 1462 return is_finite($mirr) ? $mirr : Functions::VALUE(); 1463 } 1464 1465 /** 1466 * NOMINAL. 1467 * 1468 * Returns the nominal interest rate given the effective rate and the number of compounding payments per year. 1469 * 1470 * @param float $effect_rate Effective interest rate 1471 * @param int $npery Number of compounding payments per year 1472 * 1473 * @return float|string 1474 */ 1475 public static function NOMINAL($effect_rate = 0, $npery = 0) 1476 { 1477 $effect_rate = Functions::flattenSingleValue($effect_rate); 1478 $npery = (int) Functions::flattenSingleValue($npery); 1479 1480 // Validate parameters 1481 if ($effect_rate <= 0 || $npery < 1) { 1482 return Functions::NAN(); 1483 } 1484 1485 // Calculate 1486 return $npery * (pow($effect_rate + 1, 1 / $npery) - 1); 1487 } 1488 1489 /** 1490 * NPER. 1491 * 1492 * Returns the number of periods for a cash flow with constant periodic payments (annuities), and interest rate. 1493 * 1494 * @param float $rate Interest rate per period 1495 * @param int $pmt Periodic payment (annuity) 1496 * @param float $pv Present Value 1497 * @param float $fv Future Value 1498 * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period 1499 * 1500 * @return float|string 1501 */ 1502 public static function NPER($rate = 0, $pmt = 0, $pv = 0, $fv = 0, $type = 0) 1503 { 1504 $rate = Functions::flattenSingleValue($rate); 1505 $pmt = Functions::flattenSingleValue($pmt); 1506 $pv = Functions::flattenSingleValue($pv); 1507 $fv = Functions::flattenSingleValue($fv); 1508 $type = Functions::flattenSingleValue($type); 1509 1510 // Validate parameters 1511 if ($type != 0 && $type != 1) { 1512 return Functions::NAN(); 1513 } 1514 1515 // Calculate 1516 if ($rate !== null && $rate != 0) { 1517 if ($pmt == 0 && $pv == 0) { 1518 return Functions::NAN(); 1519 } 1520 1521 return log(($pmt * (1 + $rate * $type) / $rate - $fv) / ($pv + $pmt * (1 + $rate * $type) / $rate)) / log(1 + $rate); 1522 } 1523 if ($pmt == 0) { 1524 return Functions::NAN(); 1525 } 1526 1527 return (-$pv - $fv) / $pmt; 1528 } 1529 1530 /** 1531 * NPV. 1532 * 1533 * Returns the Net Present Value of a cash flow series given a discount rate. 1534 * 1535 * @return float 1536 */ 1537 public static function NPV(...$args) 1538 { 1539 // Return value 1540 $returnValue = 0; 1541 1542 // Loop through arguments 1543 $aArgs = Functions::flattenArray($args); 1544 1545 // Calculate 1546 $rate = array_shift($aArgs); 1547 $countArgs = count($aArgs); 1548 for ($i = 1; $i <= $countArgs; ++$i) { 1549 // Is it a numeric value? 1550 if (is_numeric($aArgs[$i - 1])) { 1551 $returnValue += $aArgs[$i - 1] / pow(1 + $rate, $i); 1552 } 1553 } 1554 1555 // Return 1556 return $returnValue; 1557 } 1558 1559 /** 1560 * PDURATION. 1561 * 1562 * Calculates the number of periods required for an investment to reach a specified value. 1563 * 1564 * @param float $rate Interest rate per period 1565 * @param float $pv Present Value 1566 * @param float $fv Future Value 1567 * 1568 * @return float|string 1569 */ 1570 public static function PDURATION($rate = 0, $pv = 0, $fv = 0) 1571 { 1572 $rate = Functions::flattenSingleValue($rate); 1573 $pv = Functions::flattenSingleValue($pv); 1574 $fv = Functions::flattenSingleValue($fv); 1575 1576 // Validate parameters 1577 if (!is_numeric($rate) || !is_numeric($pv) || !is_numeric($fv)) { 1578 return Functions::VALUE(); 1579 } elseif ($rate <= 0.0 || $pv <= 0.0 || $fv <= 0.0) { 1580 return Functions::NAN(); 1581 } 1582 1583 return (log($fv) - log($pv)) / log(1 + $rate); 1584 } 1585 1586 /** 1587 * PMT. 1588 * 1589 * Returns the constant payment (annuity) for a cash flow with a constant interest rate. 1590 * 1591 * @param float $rate Interest rate per period 1592 * @param int $nper Number of periods 1593 * @param float $pv Present Value 1594 * @param float $fv Future Value 1595 * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period 1596 * 1597 * @return float 1598 */ 1599 public static function PMT($rate = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0) 1600 { 1601 $rate = Functions::flattenSingleValue($rate); 1602 $nper = Functions::flattenSingleValue($nper); 1603 $pv = Functions::flattenSingleValue($pv); 1604 $fv = Functions::flattenSingleValue($fv); 1605 $type = Functions::flattenSingleValue($type); 1606 1607 // Validate parameters 1608 if ($type != 0 && $type != 1) { 1609 return Functions::NAN(); 1610 } 1611 1612 // Calculate 1613 if ($rate !== null && $rate != 0) { 1614 return (-$fv - $pv * pow(1 + $rate, $nper)) / (1 + $rate * $type) / ((pow(1 + $rate, $nper) - 1) / $rate); 1615 } 1616 1617 return (-$pv - $fv) / $nper; 1618 } 1619 1620 /** 1621 * PPMT. 1622 * 1623 * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. 1624 * 1625 * @param float $rate Interest rate per period 1626 * @param int $per Period for which we want to find the interest 1627 * @param int $nper Number of periods 1628 * @param float $pv Present Value 1629 * @param float $fv Future Value 1630 * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period 1631 * 1632 * @return float 1633 */ 1634 public static function PPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) 1635 { 1636 $rate = Functions::flattenSingleValue($rate); 1637 $per = (int) Functions::flattenSingleValue($per); 1638 $nper = (int) Functions::flattenSingleValue($nper); 1639 $pv = Functions::flattenSingleValue($pv); 1640 $fv = Functions::flattenSingleValue($fv); 1641 $type = (int) Functions::flattenSingleValue($type); 1642 1643 // Validate parameters 1644 if ($type != 0 && $type != 1) { 1645 return Functions::NAN(); 1646 } 1647 if ($per <= 0 || $per > $nper) { 1648 return Functions::VALUE(); 1649 } 1650 1651 // Calculate 1652 $interestAndPrincipal = self::interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type); 1653 1654 return $interestAndPrincipal[1]; 1655 } 1656 1657 public static function PRICE($settlement, $maturity, $rate, $yield, $redemption, $frequency, $basis = 0) 1658 { 1659 $settlement = Functions::flattenSingleValue($settlement); 1660 $maturity = Functions::flattenSingleValue($maturity); 1661 $rate = (float) Functions::flattenSingleValue($rate); 1662 $yield = (float) Functions::flattenSingleValue($yield); 1663 $redemption = (float) Functions::flattenSingleValue($redemption); 1664 $frequency = (int) Functions::flattenSingleValue($frequency); 1665 $basis = ($basis === null) ? 0 : (int) Functions::flattenSingleValue($basis); 1666 1667 if (is_string($settlement = DateTime::getDateValue($settlement))) { 1668 return Functions::VALUE(); 1669 } 1670 if (is_string($maturity = DateTime::getDateValue($maturity))) { 1671 return Functions::VALUE(); 1672 } 1673 1674 if (($settlement > $maturity) || 1675 (!self::isValidFrequency($frequency)) || 1676 (($basis < 0) || ($basis > 4))) { 1677 return Functions::NAN(); 1678 } 1679 1680 $dsc = self::COUPDAYSNC($settlement, $maturity, $frequency, $basis); 1681 $e = self::COUPDAYS($settlement, $maturity, $frequency, $basis); 1682 $n = self::COUPNUM($settlement, $maturity, $frequency, $basis); 1683 $a = self::COUPDAYBS($settlement, $maturity, $frequency, $basis); 1684 1685 $baseYF = 1.0 + ($yield / $frequency); 1686 $rfp = 100 * ($rate / $frequency); 1687 $de = $dsc / $e; 1688 1689 $result = $redemption / pow($baseYF, (--$n + $de)); 1690 for ($k = 0; $k <= $n; ++$k) { 1691 $result += $rfp / (pow($baseYF, ($k + $de))); 1692 } 1693 $result -= $rfp * ($a / $e); 1694 1695 return $result; 1696 } 1697 1698 /** 1699 * PRICEDISC. 1700 * 1701 * Returns the price per $100 face value of a discounted security. 1702 * 1703 * @param mixed $settlement The security's settlement date. 1704 * The security settlement date is the date after the issue date when the security is traded to the buyer. 1705 * @param mixed $maturity The security's maturity date. 1706 * The maturity date is the date when the security expires. 1707 * @param int $discount The security's discount rate 1708 * @param int $redemption The security's redemption value per $100 face value 1709 * @param int $basis The type of day count to use. 1710 * 0 or omitted US (NASD) 30/360 1711 * 1 Actual/actual 1712 * 2 Actual/360 1713 * 3 Actual/365 1714 * 4 European 30/360 1715 * 1716 * @return float 1717 */ 1718 public static function PRICEDISC($settlement, $maturity, $discount, $redemption, $basis = 0) 1719 { 1720 $settlement = Functions::flattenSingleValue($settlement); 1721 $maturity = Functions::flattenSingleValue($maturity); 1722 $discount = (float) Functions::flattenSingleValue($discount); 1723 $redemption = (float) Functions::flattenSingleValue($redemption); 1724 $basis = (int) Functions::flattenSingleValue($basis); 1725 1726 // Validate 1727 if ((is_numeric($discount)) && (is_numeric($redemption)) && (is_numeric($basis))) { 1728 if (($discount <= 0) || ($redemption <= 0)) { 1729 return Functions::NAN(); 1730 } 1731 $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); 1732 if (!is_numeric($daysBetweenSettlementAndMaturity)) { 1733 // return date error 1734 return $daysBetweenSettlementAndMaturity; 1735 } 1736 1737 return $redemption * (1 - $discount * $daysBetweenSettlementAndMaturity); 1738 } 1739 1740 return Functions::VALUE(); 1741 } 1742 1743 /** 1744 * PRICEMAT. 1745 * 1746 * Returns the price per $100 face value of a security that pays interest at maturity. 1747 * 1748 * @param mixed $settlement The security's settlement date. 1749 * The security's settlement date is the date after the issue date when the security is traded to the buyer. 1750 * @param mixed $maturity The security's maturity date. 1751 * The maturity date is the date when the security expires. 1752 * @param mixed $issue The security's issue date 1753 * @param int $rate The security's interest rate at date of issue 1754 * @param int $yield The security's annual yield 1755 * @param int $basis The type of day count to use. 1756 * 0 or omitted US (NASD) 30/360 1757 * 1 Actual/actual 1758 * 2 Actual/360 1759 * 3 Actual/365 1760 * 4 European 30/360 1761 * 1762 * @return float 1763 */ 1764 public static function PRICEMAT($settlement, $maturity, $issue, $rate, $yield, $basis = 0) 1765 { 1766 $settlement = Functions::flattenSingleValue($settlement); 1767 $maturity = Functions::flattenSingleValue($maturity); 1768 $issue = Functions::flattenSingleValue($issue); 1769 $rate = Functions::flattenSingleValue($rate); 1770 $yield = Functions::flattenSingleValue($yield); 1771 $basis = (int) Functions::flattenSingleValue($basis); 1772 1773 // Validate 1774 if (is_numeric($rate) && is_numeric($yield)) { 1775 if (($rate <= 0) || ($yield <= 0)) { 1776 return Functions::NAN(); 1777 } 1778 $daysPerYear = self::daysPerYear(DateTime::YEAR($settlement), $basis); 1779 if (!is_numeric($daysPerYear)) { 1780 return $daysPerYear; 1781 } 1782 $daysBetweenIssueAndSettlement = DateTime::YEARFRAC($issue, $settlement, $basis); 1783 if (!is_numeric($daysBetweenIssueAndSettlement)) { 1784 // return date error 1785 return $daysBetweenIssueAndSettlement; 1786 } 1787 $daysBetweenIssueAndSettlement *= $daysPerYear; 1788 $daysBetweenIssueAndMaturity = DateTime::YEARFRAC($issue, $maturity, $basis); 1789 if (!is_numeric($daysBetweenIssueAndMaturity)) { 1790 // return date error 1791 return $daysBetweenIssueAndMaturity; 1792 } 1793 $daysBetweenIssueAndMaturity *= $daysPerYear; 1794 $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); 1795 if (!is_numeric($daysBetweenSettlementAndMaturity)) { 1796 // return date error 1797 return $daysBetweenSettlementAndMaturity; 1798 } 1799 $daysBetweenSettlementAndMaturity *= $daysPerYear; 1800 1801 return (100 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate * 100)) / 1802 (1 + (($daysBetweenSettlementAndMaturity / $daysPerYear) * $yield)) - 1803 (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate * 100); 1804 } 1805 1806 return Functions::VALUE(); 1807 } 1808 1809 /** 1810 * PV. 1811 * 1812 * Returns the Present Value of a cash flow with constant payments and interest rate (annuities). 1813 * 1814 * @param float $rate Interest rate per period 1815 * @param int $nper Number of periods 1816 * @param float $pmt Periodic payment (annuity) 1817 * @param float $fv Future Value 1818 * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period 1819 * 1820 * @return float 1821 */ 1822 public static function PV($rate = 0, $nper = 0, $pmt = 0, $fv = 0, $type = 0) 1823 { 1824 $rate = Functions::flattenSingleValue($rate); 1825 $nper = Functions::flattenSingleValue($nper); 1826 $pmt = Functions::flattenSingleValue($pmt); 1827 $fv = Functions::flattenSingleValue($fv); 1828 $type = Functions::flattenSingleValue($type); 1829 1830 // Validate parameters 1831 if ($type != 0 && $type != 1) { 1832 return Functions::NAN(); 1833 } 1834 1835 // Calculate 1836 if ($rate !== null && $rate != 0) { 1837 return (-$pmt * (1 + $rate * $type) * ((pow(1 + $rate, $nper) - 1) / $rate) - $fv) / pow(1 + $rate, $nper); 1838 } 1839 1840 return -$fv - $pmt * $nper; 1841 } 1842 1843 /** 1844 * RATE. 1845 * 1846 * Returns the interest rate per period of an annuity. 1847 * RATE is calculated by iteration and can have zero or more solutions. 1848 * If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, 1849 * RATE returns the #NUM! error value. 1850 * 1851 * Excel Function: 1852 * RATE(nper,pmt,pv[,fv[,type[,guess]]]) 1853 * 1854 * @category Financial Functions 1855 * 1856 * @param float $nper The total number of payment periods in an annuity 1857 * @param float $pmt The payment made each period and cannot change over the life 1858 * of the annuity. 1859 * Typically, pmt includes principal and interest but no other 1860 * fees or taxes. 1861 * @param float $pv The present value - the total amount that a series of future 1862 * payments is worth now 1863 * @param float $fv The future value, or a cash balance you want to attain after 1864 * the last payment is made. If fv is omitted, it is assumed 1865 * to be 0 (the future value of a loan, for example, is 0). 1866 * @param int $type A number 0 or 1 and indicates when payments are due: 1867 * 0 or omitted At the end of the period. 1868 * 1 At the beginning of the period. 1869 * @param float $guess Your guess for what the rate will be. 1870 * If you omit guess, it is assumed to be 10 percent. 1871 * 1872 * @return float 1873 */ 1874 public static function RATE($nper, $pmt, $pv, $fv = 0.0, $type = 0, $guess = 0.1) 1875 { 1876 $nper = (int) Functions::flattenSingleValue($nper); 1877 $pmt = Functions::flattenSingleValue($pmt); 1878 $pv = Functions::flattenSingleValue($pv); 1879 $fv = ($fv === null) ? 0.0 : Functions::flattenSingleValue($fv); 1880 $type = ($type === null) ? 0 : (int) Functions::flattenSingleValue($type); 1881 $guess = ($guess === null) ? 0.1 : Functions::flattenSingleValue($guess); 1882 1883 $rate = $guess; 1884 if (abs($rate) < self::FINANCIAL_PRECISION) { 1885 $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv; 1886 } else { 1887 $f = exp($nper * log(1 + $rate)); 1888 $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv; 1889 } 1890 $y0 = $pv + $pmt * $nper + $fv; 1891 $y1 = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv; 1892 1893 // find root by secant method 1894 $i = $x0 = 0.0; 1895 $x1 = $rate; 1896 while ((abs($y0 - $y1) > self::FINANCIAL_PRECISION) && ($i < self::FINANCIAL_MAX_ITERATIONS)) { 1897 $rate = ($y1 * $x0 - $y0 * $x1) / ($y1 - $y0); 1898 $x0 = $x1; 1899 $x1 = $rate; 1900 if (($nper * abs($pmt)) > ($pv - $fv)) { 1901 $x1 = abs($x1); 1902 } 1903 if (abs($rate) < self::FINANCIAL_PRECISION) { 1904 $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv; 1905 } else { 1906 $f = exp($nper * log(1 + $rate)); 1907 $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv; 1908 } 1909 1910 $y0 = $y1; 1911 $y1 = $y; 1912 ++$i; 1913 } 1914 1915 return $rate; 1916 } 1917 1918 /** 1919 * RECEIVED. 1920 * 1921 * Returns the price per $100 face value of a discounted security. 1922 * 1923 * @param mixed $settlement The security's settlement date. 1924 * The security settlement date is the date after the issue date when the security is traded to the buyer. 1925 * @param mixed $maturity The security's maturity date. 1926 * The maturity date is the date when the security expires. 1927 * @param int $investment The amount invested in the security 1928 * @param int $discount The security's discount rate 1929 * @param int $basis The type of day count to use. 1930 * 0 or omitted US (NASD) 30/360 1931 * 1 Actual/actual 1932 * 2 Actual/360 1933 * 3 Actual/365 1934 * 4 European 30/360 1935 * 1936 * @return float 1937 */ 1938 public static function RECEIVED($settlement, $maturity, $investment, $discount, $basis = 0) 1939 { 1940 $settlement = Functions::flattenSingleValue($settlement); 1941 $maturity = Functions::flattenSingleValue($maturity); 1942 $investment = (float) Functions::flattenSingleValue($investment); 1943 $discount = (float) Functions::flattenSingleValue($discount); 1944 $basis = (int) Functions::flattenSingleValue($basis); 1945 1946 // Validate 1947 if ((is_numeric($investment)) && (is_numeric($discount)) && (is_numeric($basis))) { 1948 if (($investment <= 0) || ($discount <= 0)) { 1949 return Functions::NAN(); 1950 } 1951 $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); 1952 if (!is_numeric($daysBetweenSettlementAndMaturity)) { 1953 // return date error 1954 return $daysBetweenSettlementAndMaturity; 1955 } 1956 1957 return $investment / (1 - ($discount * $daysBetweenSettlementAndMaturity)); 1958 } 1959 1960 return Functions::VALUE(); 1961 } 1962 1963 /** 1964 * RRI. 1965 * 1966 * Calculates the interest rate required for an investment to grow to a specified future value . 1967 * 1968 * @param float $nper The number of periods over which the investment is made 1969 * @param float $pv Present Value 1970 * @param float $fv Future Value 1971 * 1972 * @return float|string 1973 */ 1974 public static function RRI($nper = 0, $pv = 0, $fv = 0) 1975 { 1976 $nper = Functions::flattenSingleValue($nper); 1977 $pv = Functions::flattenSingleValue($pv); 1978 $fv = Functions::flattenSingleValue($fv); 1979 1980 // Validate parameters 1981 if (!is_numeric($nper) || !is_numeric($pv) || !is_numeric($fv)) { 1982 return Functions::VALUE(); 1983 } elseif ($nper <= 0.0 || $pv <= 0.0 || $fv < 0.0) { 1984 return Functions::NAN(); 1985 } 1986 1987 return pow($fv / $pv, 1 / $nper) - 1; 1988 } 1989 1990 /** 1991 * SLN. 1992 * 1993 * Returns the straight-line depreciation of an asset for one period 1994 * 1995 * @param mixed $cost Initial cost of the asset 1996 * @param mixed $salvage Value at the end of the depreciation 1997 * @param mixed $life Number of periods over which the asset is depreciated 1998 * 1999 * @return float|string 2000 */ 2001 public static function SLN($cost, $salvage, $life) 2002 { 2003 $cost = Functions::flattenSingleValue($cost); 2004 $salvage = Functions::flattenSingleValue($salvage); 2005 $life = Functions::flattenSingleValue($life); 2006 2007 // Calculate 2008 if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life))) { 2009 if ($life < 0) { 2010 return Functions::NAN(); 2011 } 2012 2013 return ($cost - $salvage) / $life; 2014 } 2015 2016 return Functions::VALUE(); 2017 } 2018 2019 /** 2020 * SYD. 2021 * 2022 * Returns the sum-of-years' digits depreciation of an asset for a specified period. 2023 * 2024 * @param mixed $cost Initial cost of the asset 2025 * @param mixed $salvage Value at the end of the depreciation 2026 * @param mixed $life Number of periods over which the asset is depreciated 2027 * @param mixed $period Period 2028 * 2029 * @return float|string 2030 */ 2031 public static function SYD($cost, $salvage, $life, $period) 2032 { 2033 $cost = Functions::flattenSingleValue($cost); 2034 $salvage = Functions::flattenSingleValue($salvage); 2035 $life = Functions::flattenSingleValue($life); 2036 $period = Functions::flattenSingleValue($period); 2037 2038 // Calculate 2039 if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period))) { 2040 if (($life < 1) || ($period > $life)) { 2041 return Functions::NAN(); 2042 } 2043 2044 return (($cost - $salvage) * ($life - $period + 1) * 2) / ($life * ($life + 1)); 2045 } 2046 2047 return Functions::VALUE(); 2048 } 2049 2050 /** 2051 * TBILLEQ. 2052 * 2053 * Returns the bond-equivalent yield for a Treasury bill. 2054 * 2055 * @param mixed $settlement The Treasury bill's settlement date. 2056 * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer. 2057 * @param mixed $maturity The Treasury bill's maturity date. 2058 * The maturity date is the date when the Treasury bill expires. 2059 * @param int $discount The Treasury bill's discount rate 2060 * 2061 * @return float 2062 */ 2063 public static function TBILLEQ($settlement, $maturity, $discount) 2064 { 2065 $settlement = Functions::flattenSingleValue($settlement); 2066 $maturity = Functions::flattenSingleValue($maturity); 2067 $discount = Functions::flattenSingleValue($discount); 2068 2069 // Use TBILLPRICE for validation 2070 $testValue = self::TBILLPRICE($settlement, $maturity, $discount); 2071 if (is_string($testValue)) { 2072 return $testValue; 2073 } 2074 2075 if (is_string($maturity = DateTime::getDateValue($maturity))) { 2076 return Functions::VALUE(); 2077 } 2078 2079 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) { 2080 ++$maturity; 2081 $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity) * 360; 2082 } else { 2083 $daysBetweenSettlementAndMaturity = (DateTime::getDateValue($maturity) - DateTime::getDateValue($settlement)); 2084 } 2085 2086 return (365 * $discount) / (360 - $discount * $daysBetweenSettlementAndMaturity); 2087 } 2088 2089 /** 2090 * TBILLPRICE. 2091 * 2092 * Returns the yield for a Treasury bill. 2093 * 2094 * @param mixed $settlement The Treasury bill's settlement date. 2095 * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer. 2096 * @param mixed $maturity The Treasury bill's maturity date. 2097 * The maturity date is the date when the Treasury bill expires. 2098 * @param int $discount The Treasury bill's discount rate 2099 * 2100 * @return float 2101 */ 2102 public static function TBILLPRICE($settlement, $maturity, $discount) 2103 { 2104 $settlement = Functions::flattenSingleValue($settlement); 2105 $maturity = Functions::flattenSingleValue($maturity); 2106 $discount = Functions::flattenSingleValue($discount); 2107 2108 if (is_string($maturity = DateTime::getDateValue($maturity))) { 2109 return Functions::VALUE(); 2110 } 2111 2112 // Validate 2113 if (is_numeric($discount)) { 2114 if ($discount <= 0) { 2115 return Functions::NAN(); 2116 } 2117 2118 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) { 2119 ++$maturity; 2120 $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity) * 360; 2121 if (!is_numeric($daysBetweenSettlementAndMaturity)) { 2122 // return date error 2123 return $daysBetweenSettlementAndMaturity; 2124 } 2125 } else { 2126 $daysBetweenSettlementAndMaturity = (DateTime::getDateValue($maturity) - DateTime::getDateValue($settlement)); 2127 } 2128 2129 if ($daysBetweenSettlementAndMaturity > 360) { 2130 return Functions::NAN(); 2131 } 2132 2133 $price = 100 * (1 - (($discount * $daysBetweenSettlementAndMaturity) / 360)); 2134 if ($price <= 0) { 2135 return Functions::NAN(); 2136 } 2137 2138 return $price; 2139 } 2140 2141 return Functions::VALUE(); 2142 } 2143 2144 /** 2145 * TBILLYIELD. 2146 * 2147 * Returns the yield for a Treasury bill. 2148 * 2149 * @param mixed $settlement The Treasury bill's settlement date. 2150 * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer. 2151 * @param mixed $maturity The Treasury bill's maturity date. 2152 * The maturity date is the date when the Treasury bill expires. 2153 * @param int $price The Treasury bill's price per $100 face value 2154 * 2155 * @return float 2156 */ 2157 public static function TBILLYIELD($settlement, $maturity, $price) 2158 { 2159 $settlement = Functions::flattenSingleValue($settlement); 2160 $maturity = Functions::flattenSingleValue($maturity); 2161 $price = Functions::flattenSingleValue($price); 2162 2163 // Validate 2164 if (is_numeric($price)) { 2165 if ($price <= 0) { 2166 return Functions::NAN(); 2167 } 2168 2169 if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) { 2170 ++$maturity; 2171 $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity) * 360; 2172 if (!is_numeric($daysBetweenSettlementAndMaturity)) { 2173 // return date error 2174 return $daysBetweenSettlementAndMaturity; 2175 } 2176 } else { 2177 $daysBetweenSettlementAndMaturity = (DateTime::getDateValue($maturity) - DateTime::getDateValue($settlement)); 2178 } 2179 2180 if ($daysBetweenSettlementAndMaturity > 360) { 2181 return Functions::NAN(); 2182 } 2183 2184 return ((100 - $price) / $price) * (360 / $daysBetweenSettlementAndMaturity); 2185 } 2186 2187 return Functions::VALUE(); 2188 } 2189 2190 public static function XIRR($values, $dates, $guess = 0.1) 2191 { 2192 if ((!is_array($values)) && (!is_array($dates))) { 2193 return Functions::VALUE(); 2194 } 2195 $values = Functions::flattenArray($values); 2196 $dates = Functions::flattenArray($dates); 2197 $guess = Functions::flattenSingleValue($guess); 2198 if (count($values) != count($dates)) { 2199 return Functions::NAN(); 2200 } 2201 2202 // create an initial range, with a root somewhere between 0 and guess 2203 $x1 = 0.0; 2204 $x2 = $guess; 2205 $f1 = self::XNPV($x1, $values, $dates); 2206 $f2 = self::XNPV($x2, $values, $dates); 2207 for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) { 2208 if (($f1 * $f2) < 0.0) { 2209 break; 2210 } elseif (abs($f1) < abs($f2)) { 2211 $f1 = self::XNPV($x1 += 1.6 * ($x1 - $x2), $values, $dates); 2212 } else { 2213 $f2 = self::XNPV($x2 += 1.6 * ($x2 - $x1), $values, $dates); 2214 } 2215 } 2216 if (($f1 * $f2) > 0.0) { 2217 return Functions::VALUE(); 2218 } 2219 2220 $f = self::XNPV($x1, $values, $dates); 2221 if ($f < 0.0) { 2222 $rtb = $x1; 2223 $dx = $x2 - $x1; 2224 } else { 2225 $rtb = $x2; 2226 $dx = $x1 - $x2; 2227 } 2228 2229 for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) { 2230 $dx *= 0.5; 2231 $x_mid = $rtb + $dx; 2232 $f_mid = self::XNPV($x_mid, $values, $dates); 2233 if ($f_mid <= 0.0) { 2234 $rtb = $x_mid; 2235 } 2236 if ((abs($f_mid) < self::FINANCIAL_PRECISION) || (abs($dx) < self::FINANCIAL_PRECISION)) { 2237 return $x_mid; 2238 } 2239 } 2240 2241 return Functions::VALUE(); 2242 } 2243 2244 /** 2245 * XNPV. 2246 * 2247 * Returns the net present value for a schedule of cash flows that is not necessarily periodic. 2248 * To calculate the net present value for a series of cash flows that is periodic, use the NPV function. 2249 * 2250 * Excel Function: 2251 * =XNPV(rate,values,dates) 2252 * 2253 * @param float $rate the discount rate to apply to the cash flows 2254 * @param array of float $values A series of cash flows that corresponds to a schedule of payments in dates. 2255 * The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. 2256 * 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. 2257 * The series of values must contain at least one positive value and one negative value. 2258 * @param array of mixed $dates A schedule of payment dates that corresponds to the cash flow payments. 2259 * The first payment date indicates the beginning of the schedule of payments. 2260 * All other dates must be later than this date, but they may occur in any order. 2261 * 2262 * @return float 2263 */ 2264 public static function XNPV($rate, $values, $dates) 2265 { 2266 $rate = Functions::flattenSingleValue($rate); 2267 if (!is_numeric($rate)) { 2268 return Functions::VALUE(); 2269 } 2270 if ((!is_array($values)) || (!is_array($dates))) { 2271 return Functions::VALUE(); 2272 } 2273 $values = Functions::flattenArray($values); 2274 $dates = Functions::flattenArray($dates); 2275 $valCount = count($values); 2276 if ($valCount != count($dates)) { 2277 return Functions::NAN(); 2278 } 2279 if ((min($values) > 0) || (max($values) < 0)) { 2280 return Functions::VALUE(); 2281 } 2282 2283 $xnpv = 0.0; 2284 for ($i = 0; $i < $valCount; ++$i) { 2285 if (!is_numeric($values[$i])) { 2286 return Functions::VALUE(); 2287 } 2288 $xnpv += $values[$i] / pow(1 + $rate, DateTime::DATEDIF($dates[0], $dates[$i], 'd') / 365); 2289 } 2290 2291 return (is_finite($xnpv)) ? $xnpv : Functions::VALUE(); 2292 } 2293 2294 /** 2295 * YIELDDISC. 2296 * 2297 * Returns the annual yield of a security that pays interest at maturity. 2298 * 2299 * @param mixed $settlement The security's settlement date. 2300 * The security's settlement date is the date after the issue date when the security is traded to the buyer. 2301 * @param mixed $maturity The security's maturity date. 2302 * The maturity date is the date when the security expires. 2303 * @param int $price The security's price per $100 face value 2304 * @param int $redemption The security's redemption value per $100 face value 2305 * @param int $basis The type of day count to use. 2306 * 0 or omitted US (NASD) 30/360 2307 * 1 Actual/actual 2308 * 2 Actual/360 2309 * 3 Actual/365 2310 * 4 European 30/360 2311 * 2312 * @return float 2313 */ 2314 public static function YIELDDISC($settlement, $maturity, $price, $redemption, $basis = 0) 2315 { 2316 $settlement = Functions::flattenSingleValue($settlement); 2317 $maturity = Functions::flattenSingleValue($maturity); 2318 $price = Functions::flattenSingleValue($price); 2319 $redemption = Functions::flattenSingleValue($redemption); 2320 $basis = (int) Functions::flattenSingleValue($basis); 2321 2322 // Validate 2323 if (is_numeric($price) && is_numeric($redemption)) { 2324 if (($price <= 0) || ($redemption <= 0)) { 2325 return Functions::NAN(); 2326 } 2327 $daysPerYear = self::daysPerYear(DateTime::YEAR($settlement), $basis); 2328 if (!is_numeric($daysPerYear)) { 2329 return $daysPerYear; 2330 } 2331 $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); 2332 if (!is_numeric($daysBetweenSettlementAndMaturity)) { 2333 // return date error 2334 return $daysBetweenSettlementAndMaturity; 2335 } 2336 $daysBetweenSettlementAndMaturity *= $daysPerYear; 2337 2338 return (($redemption - $price) / $price) * ($daysPerYear / $daysBetweenSettlementAndMaturity); 2339 } 2340 2341 return Functions::VALUE(); 2342 } 2343 2344 /** 2345 * YIELDMAT. 2346 * 2347 * Returns the annual yield of a security that pays interest at maturity. 2348 * 2349 * @param mixed $settlement The security's settlement date. 2350 * The security's settlement date is the date after the issue date when the security is traded to the buyer. 2351 * @param mixed $maturity The security's maturity date. 2352 * The maturity date is the date when the security expires. 2353 * @param mixed $issue The security's issue date 2354 * @param int $rate The security's interest rate at date of issue 2355 * @param int $price The security's price per $100 face value 2356 * @param int $basis The type of day count to use. 2357 * 0 or omitted US (NASD) 30/360 2358 * 1 Actual/actual 2359 * 2 Actual/360 2360 * 3 Actual/365 2361 * 4 European 30/360 2362 * 2363 * @return float 2364 */ 2365 public static function YIELDMAT($settlement, $maturity, $issue, $rate, $price, $basis = 0) 2366 { 2367 $settlement = Functions::flattenSingleValue($settlement); 2368 $maturity = Functions::flattenSingleValue($maturity); 2369 $issue = Functions::flattenSingleValue($issue); 2370 $rate = Functions::flattenSingleValue($rate); 2371 $price = Functions::flattenSingleValue($price); 2372 $basis = (int) Functions::flattenSingleValue($basis); 2373 2374 // Validate 2375 if (is_numeric($rate) && is_numeric($price)) { 2376 if (($rate <= 0) || ($price <= 0)) { 2377 return Functions::NAN(); 2378 } 2379 $daysPerYear = self::daysPerYear(DateTime::YEAR($settlement), $basis); 2380 if (!is_numeric($daysPerYear)) { 2381 return $daysPerYear; 2382 } 2383 $daysBetweenIssueAndSettlement = DateTime::YEARFRAC($issue, $settlement, $basis); 2384 if (!is_numeric($daysBetweenIssueAndSettlement)) { 2385 // return date error 2386 return $daysBetweenIssueAndSettlement; 2387 } 2388 $daysBetweenIssueAndSettlement *= $daysPerYear; 2389 $daysBetweenIssueAndMaturity = DateTime::YEARFRAC($issue, $maturity, $basis); 2390 if (!is_numeric($daysBetweenIssueAndMaturity)) { 2391 // return date error 2392 return $daysBetweenIssueAndMaturity; 2393 } 2394 $daysBetweenIssueAndMaturity *= $daysPerYear; 2395 $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); 2396 if (!is_numeric($daysBetweenSettlementAndMaturity)) { 2397 // return date error 2398 return $daysBetweenSettlementAndMaturity; 2399 } 2400 $daysBetweenSettlementAndMaturity *= $daysPerYear; 2401 2402 return ((1 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate) - (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) / 2403 (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) * 2404 ($daysPerYear / $daysBetweenSettlementAndMaturity); 2405 } 2406 2407 return Functions::VALUE(); 2408 } 2409} 2410