[Summary view]

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

   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(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          } catch (Exception $e) {
 265              return $e->getMessage();
 266          }
 267  
 268          return self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_NEXT);
 269      }
 270  
 271      /**
 272       * COUPNUM.
 273       *
 274       * Returns the number of coupons payable between the settlement date and maturity date,
 275       * rounded up to the nearest whole coupon.
 276       *
 277       * Excel Function:
 278       *        COUPNUM(settlement,maturity,frequency[,basis])
 279       *
 280       * @param mixed $settlement The security's settlement date.
 281       *                              The security settlement date is the date after the issue
 282       *                                  date when the security is traded to the buyer.
 283       * @param mixed $maturity The security's maturity date.
 284       *                            The maturity date is the date when the security expires.
 285       * @param mixed $frequency The number of coupon payments per year.
 286       *                             Valid frequency values are:
 287       *                               1    Annual
 288       *                               2    Semi-Annual
 289       *                               4    Quarterly
 290       * @param mixed $basis The type of day count to use (int).
 291       *                         0 or omitted    US (NASD) 30/360
 292       *                         1               Actual/actual
 293       *                         2               Actual/360
 294       *                         3               Actual/365
 295       *                         4               European 30/360
 296       *
 297       * @return int|string
 298       */
 299      public static function COUPNUM(
 300          $settlement,
 301          $maturity,
 302          $frequency,
 303          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 304      ) {
 305          $settlement = Functions::flattenSingleValue($settlement);
 306          $maturity = Functions::flattenSingleValue($maturity);
 307          $frequency = Functions::flattenSingleValue($frequency);
 308          $basis = ($basis === null)
 309              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 310              : Functions::flattenSingleValue($basis);
 311  
 312          try {
 313              $settlement = FinancialValidations::validateSettlementDate($settlement);
 314              $maturity = FinancialValidations::validateMaturityDate($maturity);
 315              self::validateCouponPeriod($settlement, $maturity);
 316              $frequency = FinancialValidations::validateFrequency($frequency);
 317              $basis = FinancialValidations::validateBasis($basis);
 318          } catch (Exception $e) {
 319              return $e->getMessage();
 320          }
 321  
 322          $yearsBetweenSettlementAndMaturity = DateTimeExcel\YearFrac::fraction(
 323              $settlement,
 324              $maturity,
 325              FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 326          );
 327  
 328          return (int) ceil((float) $yearsBetweenSettlementAndMaturity * $frequency);
 329      }
 330  
 331      /**
 332       * COUPPCD.
 333       *
 334       * Returns the previous coupon date before the settlement date.
 335       *
 336       * Excel Function:
 337       *        COUPPCD(settlement,maturity,frequency[,basis])
 338       *
 339       * @param mixed $settlement The security's settlement date.
 340       *                              The security settlement date is the date after the issue
 341       *                              date when the security is traded to the buyer.
 342       * @param mixed $maturity The security's maturity date.
 343       *                            The maturity date is the date when the security expires.
 344       * @param mixed $frequency The number of coupon payments per year.
 345       *                             Valid frequency values are:
 346       *                               1    Annual
 347       *                               2    Semi-Annual
 348       *                               4    Quarterly
 349       * @param mixed $basis The type of day count to use (int).
 350       *                         0 or omitted    US (NASD) 30/360
 351       *                         1               Actual/actual
 352       *                         2               Actual/360
 353       *                         3               Actual/365
 354       *                         4               European 30/360
 355       *
 356       * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object,
 357       *                     depending on the value of the ReturnDateType flag
 358       */
 359      public static function COUPPCD(
 360          $settlement,
 361          $maturity,
 362          $frequency,
 363          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 364      ) {
 365          $settlement = Functions::flattenSingleValue($settlement);
 366          $maturity = Functions::flattenSingleValue($maturity);
 367          $frequency = Functions::flattenSingleValue($frequency);
 368          $basis = ($basis === null)
 369              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 370              : Functions::flattenSingleValue($basis);
 371  
 372          try {
 373              $settlement = FinancialValidations::validateSettlementDate($settlement);
 374              $maturity = FinancialValidations::validateMaturityDate($maturity);
 375              self::validateCouponPeriod($settlement, $maturity);
 376              $frequency = FinancialValidations::validateFrequency($frequency);
 377              $basis = FinancialValidations::validateBasis($basis);
 378          } catch (Exception $e) {
 379              return $e->getMessage();
 380          }
 381  
 382          return self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_PREVIOUS);
 383      }
 384  
 385      private static function monthsDiff(DateTime $result, int $months, string $plusOrMinus, int $day, bool $lastDayFlag): void
 386      {
 387          $result->setDate((int) $result->format('Y'), (int) $result->format('m'), 1);
 388          $result->modify("$plusOrMinus $months months");
 389          $daysInMonth = (int) $result->format('t');
 390          $result->setDate((int) $result->format('Y'), (int) $result->format('m'), $lastDayFlag ? $daysInMonth : min($day, $daysInMonth));
 391      }
 392  
 393      private static function couponFirstPeriodDate(float $settlement, float $maturity, int $frequency, bool $next): float
 394      {
 395          $months = 12 / $frequency;
 396  
 397          $result = Date::excelToDateTimeObject($maturity);
 398          $day = (int) $result->format('d');
 399          $lastDayFlag = Helpers::isLastDayOfMonth($result);
 400  
 401          while ($settlement < Date::PHPToExcel($result)) {
 402              self::monthsDiff($result, $months, '-', $day, $lastDayFlag);
 403          }
 404          if ($next === true) {
 405              self::monthsDiff($result, $months, '+', $day, $lastDayFlag);
 406          }
 407  
 408          return (float) Date::PHPToExcel($result);
 409      }
 410  
 411      private static function validateCouponPeriod(float $settlement, float $maturity): void
 412      {
 413          if ($settlement >= $maturity) {
 414              throw new Exception(ExcelError::NAN());
 415          }
 416      }
 417  }