[Summary view]

Differences Between: [Versions 400 and 402] [Versions 401 and 402]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\Financial;
   4  
   5  use DateTime;
   6  use PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Exception;
   8  use PhpOffice\PhpSpreadsheet\Calculation\Financial\Constants as FinancialConstants;
   9  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
  10  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
  11  use PhpOffice\PhpSpreadsheet\Shared\Date;
  12  
  13  class Coupons
  14  {
  15      private const PERIOD_DATE_PREVIOUS = false;
  16      private const PERIOD_DATE_NEXT = true;
  17  
  18      /**
  19       * COUPDAYBS.
  20       *
  21       * Returns the number of days from the beginning of the coupon period to the settlement date.
  22       *
  23       * Excel Function:
  24       *        COUPDAYBS(settlement,maturity,frequency[,basis])
  25       *
  26       * @param mixed $settlement The security's settlement date.
  27       *                              The security settlement date is the date after the issue
  28       *                                  date when the security is traded to the buyer.
  29       * @param mixed $maturity The security's maturity date.
  30       *                            The maturity date is the date when the security expires.
  31       * @param mixed $frequency The number of coupon payments per year (int).
  32       *                             Valid frequency values are:
  33       *                               1    Annual
  34       *                               2    Semi-Annual
  35       *                               4    Quarterly
  36       * @param mixed $basis The type of day count to use (int).
  37       *                         0 or omitted    US (NASD) 30/360
  38       *                         1               Actual/actual
  39       *                         2               Actual/360
  40       *                         3               Actual/365
  41       *                         4               European 30/360
  42       *
  43       * @return float|string
  44       */
  45      public static function COUPDAYBS(
  46          $settlement,
  47          $maturity,
  48          $frequency,
  49          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
  50      ) {
  51          $settlement = Functions::flattenSingleValue($settlement);
  52          $maturity = Functions::flattenSingleValue($maturity);
  53          $frequency = Functions::flattenSingleValue($frequency);
  54          $basis = ($basis === null)
  55              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
  56              : Functions::flattenSingleValue($basis);
  57  
  58          try {
  59              $settlement = FinancialValidations::validateSettlementDate($settlement);
  60              $maturity = FinancialValidations::validateMaturityDate($maturity);
  61              self::validateCouponPeriod($settlement, $maturity);
  62              $frequency = FinancialValidations::validateFrequency($frequency);
  63              $basis = FinancialValidations::validateBasis($basis);
  64          } catch (Exception $e) {
  65              return $e->getMessage();
  66          }
  67  
  68          $daysPerYear = Helpers::daysPerYear(Functions::scalar(DateTimeExcel\DateParts::year($settlement)), $basis);
  69          if (is_string($daysPerYear)) {
  70              return ExcelError::VALUE();
  71          }
  72          $prev = self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_PREVIOUS);
  73  
  74          if ($basis === FinancialConstants::BASIS_DAYS_PER_YEAR_ACTUAL) {
  75              return abs((float) DateTimeExcel\Days::between($prev, $settlement));
  76          }
  77  
  78          return (float) DateTimeExcel\YearFrac::fraction($prev, $settlement, $basis) * $daysPerYear;
  79      }
  80  
  81      /**
  82       * COUPDAYS.
  83       *
  84       * Returns the number of days in the coupon period that contains the settlement date.
  85       *
  86       * Excel Function:
  87       *        COUPDAYS(settlement,maturity,frequency[,basis])
  88       *
  89       * @param mixed $settlement The security's settlement date.
  90       *                              The security settlement date is the date after the issue
  91       *                                  date when the security is traded to the buyer.
  92       * @param mixed $maturity The security's maturity date.
  93       *                            The maturity date is the date when the security expires.
  94       * @param mixed $frequency The number of coupon payments per year.
  95       *                             Valid frequency values are:
  96       *                               1    Annual
  97       *                               2    Semi-Annual
  98       *                               4    Quarterly
  99       * @param mixed $basis The type of day count to use (int).
 100       *                         0 or omitted    US (NASD) 30/360
 101       *                         1               Actual/actual
 102       *                         2               Actual/360
 103       *                         3               Actual/365
 104       *                         4               European 30/360
 105       *
 106       * @return float|string
 107       */
 108      public static function COUPDAYS(
 109          $settlement,
 110          $maturity,
 111          $frequency,
 112          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 113      ) {
 114          $settlement = Functions::flattenSingleValue($settlement);
 115          $maturity = Functions::flattenSingleValue($maturity);
 116          $frequency = Functions::flattenSingleValue($frequency);
 117          $basis = ($basis === null)
 118              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 119              : Functions::flattenSingleValue($basis);
 120  
 121          try {
 122              $settlement = FinancialValidations::validateSettlementDate($settlement);
 123              $maturity = FinancialValidations::validateMaturityDate($maturity);
 124              self::validateCouponPeriod($settlement, $maturity);
 125              $frequency = FinancialValidations::validateFrequency($frequency);
 126              $basis = FinancialValidations::validateBasis($basis);
 127          } catch (Exception $e) {
 128              return $e->getMessage();
 129          }
 130  
 131          switch ($basis) {
 132              case FinancialConstants::BASIS_DAYS_PER_YEAR_365:
 133                  // Actual/365
 134                  return 365 / $frequency;
 135              case FinancialConstants::BASIS_DAYS_PER_YEAR_ACTUAL:
 136                  // Actual/actual
 137                  if ($frequency == FinancialConstants::FREQUENCY_ANNUAL) {
 138                      $daysPerYear = (int) Helpers::daysPerYear(Functions::scalar(DateTimeExcel\DateParts::year($settlement)), $basis);
 139  
 140                      return $daysPerYear / $frequency;
 141                  }
 142                  $prev = self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_PREVIOUS);
 143                  $next = self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_NEXT);
 144  
 145                  return $next - $prev;
 146              default:
 147                  // US (NASD) 30/360, Actual/360 or European 30/360
 148                  return 360 / $frequency;
 149          }
 150      }
 151  
 152      /**
 153       * COUPDAYSNC.
 154       *
 155       * Returns the number of days from the settlement date to the next coupon date.
 156       *
 157       * Excel Function:
 158       *        COUPDAYSNC(settlement,maturity,frequency[,basis])
 159       *
 160       * @param mixed $settlement The security's settlement date.
 161       *                              The security settlement date is the date after the issue
 162       *                                  date when the security is traded to the buyer.
 163       * @param mixed $maturity The security's maturity date.
 164       *                            The maturity date is the date when the security expires.
 165       * @param mixed $frequency The number of coupon payments per year.
 166       *                             Valid frequency values are:
 167       *                               1    Annual
 168       *                               2    Semi-Annual
 169       *                               4    Quarterly
 170       * @param mixed $basis The type of day count to use (int) .
 171       *                         0 or omitted    US (NASD) 30/360
 172       *                         1               Actual/actual
 173       *                         2               Actual/360
 174       *                         3               Actual/365
 175       *                         4               European 30/360
 176       *
 177       * @return float|string
 178       */
 179      public static function COUPDAYSNC(
 180          $settlement,
 181          $maturity,
 182          $frequency,
 183          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 184      ) {
 185          $settlement = Functions::flattenSingleValue($settlement);
 186          $maturity = Functions::flattenSingleValue($maturity);
 187          $frequency = Functions::flattenSingleValue($frequency);
 188          $basis = ($basis === null)
 189              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 190              : Functions::flattenSingleValue($basis);
 191  
 192          try {
 193              $settlement = FinancialValidations::validateSettlementDate($settlement);
 194              $maturity = FinancialValidations::validateMaturityDate($maturity);
 195              self::validateCouponPeriod($settlement, $maturity);
 196              $frequency = FinancialValidations::validateFrequency($frequency);
 197              $basis = FinancialValidations::validateBasis($basis);
 198          } catch (Exception $e) {
 199              return $e->getMessage();
 200          }
 201  
 202          /** @var int */
 203          $daysPerYear = Helpers::daysPerYear(Functions::Scalar(DateTimeExcel\DateParts::year($settlement)), $basis);
 204          $next = self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_NEXT);
 205  
 206          if ($basis === FinancialConstants::BASIS_DAYS_PER_YEAR_NASD) {
 207              $settlementDate = Date::excelToDateTimeObject($settlement);
 208              $settlementEoM = Helpers::isLastDayOfMonth($settlementDate);
 209              if ($settlementEoM) {
 210                  ++$settlement;
 211              }
 212          }
 213  
 214          return (float) DateTimeExcel\YearFrac::fraction($settlement, $next, $basis) * $daysPerYear;
 215      }
 216  
 217      /**
 218       * COUPNCD.
 219       *
 220       * Returns the next coupon date after the settlement date.
 221       *
 222       * Excel Function:
 223       *        COUPNCD(settlement,maturity,frequency[,basis])
 224       *
 225       * @param mixed $settlement The security's settlement date.
 226       *                              The security settlement date is the date after the issue
 227       *                                  date when the security is traded to the buyer.
 228       * @param mixed $maturity The security's maturity date.
 229       *                            The maturity date is the date when the security expires.
 230       * @param mixed $frequency The number of coupon payments per year.
 231       *                             Valid frequency values are:
 232       *                               1    Annual
 233       *                               2    Semi-Annual
 234       *                               4    Quarterly
 235       * @param mixed $basis The type of day count to use (int).
 236       *                         0 or omitted    US (NASD) 30/360
 237       *                         1               Actual/actual
 238       *                         2               Actual/360
 239       *                         3               Actual/365
 240       *                         4               European 30/360
 241       *
 242       * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
 243       *                     depending on the value of the ReturnDateType flag
 244       */
 245      public static function COUPNCD(
 246          $settlement,
 247          $maturity,
 248          $frequency,
 249          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 250      ) {
 251          $settlement = Functions::flattenSingleValue($settlement);
 252          $maturity = Functions::flattenSingleValue($maturity);
 253          $frequency = Functions::flattenSingleValue($frequency);
 254          $basis = ($basis === null)
 255              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 256              : Functions::flattenSingleValue($basis);
 257  
 258          try {
 259              $settlement = FinancialValidations::validateSettlementDate($settlement);
 260              $maturity = FinancialValidations::validateMaturityDate($maturity);
 261              self::validateCouponPeriod($settlement, $maturity);
 262              $frequency = FinancialValidations::validateFrequency($frequency);
 263              $basis = FinancialValidations::validateBasis($basis);
 264              self::doNothing($basis);
 265          } catch (Exception $e) {
 266              return $e->getMessage();
 267          }
 268  
 269          return self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_NEXT);
 270      }
 271  
 272      /**
 273       * COUPNUM.
 274       *
 275       * Returns the number of coupons payable between the settlement date and maturity date,
 276       * rounded up to the nearest whole coupon.
 277       *
 278       * Excel Function:
 279       *        COUPNUM(settlement,maturity,frequency[,basis])
 280       *
 281       * @param mixed $settlement The security's settlement date.
 282       *                              The security settlement date is the date after the issue
 283       *                                  date when the security is traded to the buyer.
 284       * @param mixed $maturity The security's maturity date.
 285       *                            The maturity date is the date when the security expires.
 286       * @param mixed $frequency The number of coupon payments per year.
 287       *                             Valid frequency values are:
 288       *                               1    Annual
 289       *                               2    Semi-Annual
 290       *                               4    Quarterly
 291       * @param mixed $basis The type of day count to use (int).
 292       *                         0 or omitted    US (NASD) 30/360
 293       *                         1               Actual/actual
 294       *                         2               Actual/360
 295       *                         3               Actual/365
 296       *                         4               European 30/360
 297       *
 298       * @return int|string
 299       */
 300      public static function COUPNUM(
 301          $settlement,
 302          $maturity,
 303          $frequency,
 304          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 305      ) {
 306          $settlement = Functions::flattenSingleValue($settlement);
 307          $maturity = Functions::flattenSingleValue($maturity);
 308          $frequency = Functions::flattenSingleValue($frequency);
 309          $basis = ($basis === null)
 310              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 311              : Functions::flattenSingleValue($basis);
 312  
 313          try {
 314              $settlement = FinancialValidations::validateSettlementDate($settlement);
 315              $maturity = FinancialValidations::validateMaturityDate($maturity);
 316              self::validateCouponPeriod($settlement, $maturity);
 317              $frequency = FinancialValidations::validateFrequency($frequency);
 318              $basis = FinancialValidations::validateBasis($basis);
 319              self::doNothing($basis);
 320          } catch (Exception $e) {
 321              return $e->getMessage();
 322          }
 323  
 324          $yearsBetweenSettlementAndMaturity = DateTimeExcel\YearFrac::fraction(
 325              $settlement,
 326              $maturity,
 327              FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 328          );
 329  
 330          return (int) ceil((float) $yearsBetweenSettlementAndMaturity * $frequency);
 331      }
 332  
 333      /**
 334       * COUPPCD.
 335       *
 336       * Returns the previous coupon date before the settlement date.
 337       *
 338       * Excel Function:
 339       *        COUPPCD(settlement,maturity,frequency[,basis])
 340       *
 341       * @param mixed $settlement The security's settlement date.
 342       *                              The security settlement date is the date after the issue
 343       *                              date when the security is traded to the buyer.
 344       * @param mixed $maturity The security's maturity date.
 345       *                            The maturity date is the date when the security expires.
 346       * @param mixed $frequency The number of coupon payments per year.
 347       *                             Valid frequency values are:
 348       *                               1    Annual
 349       *                               2    Semi-Annual
 350       *                               4    Quarterly
 351       * @param mixed $basis The type of day count to use (int).
 352       *                         0 or omitted    US (NASD) 30/360
 353       *                         1               Actual/actual
 354       *                         2               Actual/360
 355       *                         3               Actual/365
 356       *                         4               European 30/360
 357       *
 358       * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
 359       *                     depending on the value of the ReturnDateType flag
 360       */
 361      public static function COUPPCD(
 362          $settlement,
 363          $maturity,
 364          $frequency,
 365          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 366      ) {
 367          $settlement = Functions::flattenSingleValue($settlement);
 368          $maturity = Functions::flattenSingleValue($maturity);
 369          $frequency = Functions::flattenSingleValue($frequency);
 370          $basis = ($basis === null)
 371              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 372              : Functions::flattenSingleValue($basis);
 373  
 374          try {
 375              $settlement = FinancialValidations::validateSettlementDate($settlement);
 376              $maturity = FinancialValidations::validateMaturityDate($maturity);
 377              self::validateCouponPeriod($settlement, $maturity);
 378              $frequency = FinancialValidations::validateFrequency($frequency);
 379              $basis = FinancialValidations::validateBasis($basis);
 380              self::doNothing($basis);
 381          } catch (Exception $e) {
 382              return $e->getMessage();
 383          }
 384  
 385          return self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_PREVIOUS);
 386      }
 387  
 388      private static function monthsDiff(DateTime $result, int $months, string $plusOrMinus, int $day, bool $lastDayFlag): void
 389      {
 390          $result->setDate((int) $result->format('Y'), (int) $result->format('m'), 1);
 391          $result->modify("$plusOrMinus $months months");
 392          $daysInMonth = (int) $result->format('t');
 393          $result->setDate((int) $result->format('Y'), (int) $result->format('m'), $lastDayFlag ? $daysInMonth : min($day, $daysInMonth));
 394      }
 395  
 396      private static function couponFirstPeriodDate(float $settlement, float $maturity, int $frequency, bool $next): float
 397      {
 398          $months = 12 / $frequency;
 399  
 400          $result = Date::excelToDateTimeObject($maturity);
 401          $day = (int) $result->format('d');
 402          $lastDayFlag = Helpers::isLastDayOfMonth($result);
 403  
 404          while ($settlement < Date::PHPToExcel($result)) {
 405              self::monthsDiff($result, $months, '-', $day, $lastDayFlag);
 406          }
 407          if ($next === true) {
 408              self::monthsDiff($result, $months, '+', $day, $lastDayFlag);
 409          }
 410  
 411          return (float) Date::PHPToExcel($result);
 412      }
 413  
 414      private static function validateCouponPeriod(float $settlement, float $maturity): void
 415      {
 416          if ($settlement >= $maturity) {
 417              throw new Exception(ExcelError::NAN());
 418          }
 419      }
 420  
 421      /** @param mixed $basis */
 422      private static function doNothing($basis): bool
 423      {
 424          return $basis;
 425      }
 426  }