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