[Summary view]

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