[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\Securities;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Exception;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Financial\Constants as FinancialConstants;
   8  use PhpOffice\PhpSpreadsheet\Calculation\Financial\Coupons;
   9  use PhpOffice\PhpSpreadsheet\Calculation\Financial\Helpers;
  10  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
  11  
  12  class Price
  13  {
  14      /**
  15       * PRICE.
  16       *
  17       * Returns the price per $100 face value of a security that pays periodic interest.
  18       *
  19       * @param mixed $settlement The security's settlement date.
  20       *                              The security settlement date is the date after the issue date when the security
  21       *                              is traded to the buyer.
  22       * @param mixed $maturity The security's maturity date.
  23       *                                The maturity date is the date when the security expires.
  24       * @param mixed $rate the security's annual coupon rate
  25       * @param mixed $yield the security's annual yield
  26       * @param mixed $redemption The number of coupon payments per year.
  27       *                              For annual payments, frequency = 1;
  28       *                              for semiannual, frequency = 2;
  29       *                              for quarterly, frequency = 4.
  30       * @param mixed $frequency
  31       * @param mixed $basis The type of day count to use.
  32       *                         0 or omitted    US (NASD) 30/360
  33       *                         1               Actual/actual
  34       *                         2               Actual/360
  35       *                         3               Actual/365
  36       *                         4               European 30/360
  37       *
  38       * @return float|string Result, or a string containing an error
  39       */
  40      public static function price(
  41          $settlement,
  42          $maturity,
  43          $rate,
  44          $yield,
  45          $redemption,
  46          $frequency,
  47          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
  48      ) {
  49          $settlement = Functions::flattenSingleValue($settlement);
  50          $maturity = Functions::flattenSingleValue($maturity);
  51          $rate = Functions::flattenSingleValue($rate);
  52          $yield = Functions::flattenSingleValue($yield);
  53          $redemption = Functions::flattenSingleValue($redemption);
  54          $frequency = Functions::flattenSingleValue($frequency);
  55          $basis = ($basis === null)
  56              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
  57              : Functions::flattenSingleValue($basis);
  58  
  59          try {
  60              $settlement = SecurityValidations::validateSettlementDate($settlement);
  61              $maturity = SecurityValidations::validateMaturityDate($maturity);
  62              SecurityValidations::validateSecurityPeriod($settlement, $maturity);
  63              $rate = SecurityValidations::validateRate($rate);
  64              $yield = SecurityValidations::validateYield($yield);
  65              $redemption = SecurityValidations::validateRedemption($redemption);
  66              $frequency = SecurityValidations::validateFrequency($frequency);
  67              $basis = SecurityValidations::validateBasis($basis);
  68          } catch (Exception $e) {
  69              return $e->getMessage();
  70          }
  71  
  72          $dsc = Coupons::COUPDAYSNC($settlement, $maturity, $frequency, $basis);
  73          $e = Coupons::COUPDAYS($settlement, $maturity, $frequency, $basis);
  74          $n = Coupons::COUPNUM($settlement, $maturity, $frequency, $basis);
  75          $a = Coupons::COUPDAYBS($settlement, $maturity, $frequency, $basis);
  76  
  77          $baseYF = 1.0 + ($yield / $frequency);
  78          $rfp = 100 * ($rate / $frequency);
  79          $de = $dsc / $e;
  80  
  81          $result = $redemption / $baseYF ** (--$n + $de);
  82          for ($k = 0; $k <= $n; ++$k) {
  83              $result += $rfp / ($baseYF ** ($k + $de));
  84          }
  85          $result -= $rfp * ($a / $e);
  86  
  87          return $result;
  88      }
  89  
  90      /**
  91       * PRICEDISC.
  92       *
  93       * Returns the price per $100 face value of a discounted security.
  94       *
  95       * @param mixed $settlement The security's settlement date.
  96       *                              The security settlement date is the date after the issue date when the security
  97       *                              is traded to the buyer.
  98       * @param mixed $maturity The security's maturity date.
  99       *                                The maturity date is the date when the security expires.
 100       * @param mixed $discount The security's discount rate
 101       * @param mixed $redemption The security's redemption value per $100 face value
 102       * @param mixed $basis The type of day count to use.
 103       *                         0 or omitted    US (NASD) 30/360
 104       *                         1               Actual/actual
 105       *                         2               Actual/360
 106       *                         3               Actual/365
 107       *                         4               European 30/360
 108       *
 109       * @return float|string Result, or a string containing an error
 110       */
 111      public static function priceDiscounted(
 112          $settlement,
 113          $maturity,
 114          $discount,
 115          $redemption,
 116          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 117      ) {
 118          $settlement = Functions::flattenSingleValue($settlement);
 119          $maturity = Functions::flattenSingleValue($maturity);
 120          $discount = Functions::flattenSingleValue($discount);
 121          $redemption = Functions::flattenSingleValue($redemption);
 122          $basis = ($basis === null)
 123              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 124              : Functions::flattenSingleValue($basis);
 125  
 126          try {
 127              $settlement = SecurityValidations::validateSettlementDate($settlement);
 128              $maturity = SecurityValidations::validateMaturityDate($maturity);
 129              SecurityValidations::validateSecurityPeriod($settlement, $maturity);
 130              $discount = SecurityValidations::validateDiscount($discount);
 131              $redemption = SecurityValidations::validateRedemption($redemption);
 132              $basis = SecurityValidations::validateBasis($basis);
 133          } catch (Exception $e) {
 134              return $e->getMessage();
 135          }
 136  
 137          $daysBetweenSettlementAndMaturity = DateTimeExcel\YearFrac::fraction($settlement, $maturity, $basis);
 138          if (!is_numeric($daysBetweenSettlementAndMaturity)) {
 139              //    return date error
 140              return $daysBetweenSettlementAndMaturity;
 141          }
 142  
 143          return $redemption * (1 - $discount * $daysBetweenSettlementAndMaturity);
 144      }
 145  
 146      /**
 147       * PRICEMAT.
 148       *
 149       * Returns the price per $100 face value of a security that pays interest at maturity.
 150       *
 151       * @param mixed $settlement The security's settlement date.
 152       *                              The security's settlement date is the date after the issue date when the
 153       *                              security is traded to the buyer.
 154       * @param mixed $maturity The security's maturity date.
 155       *                                The maturity date is the date when the security expires.
 156       * @param mixed $issue The security's issue date
 157       * @param mixed $rate The security's interest rate at date of issue
 158       * @param mixed $yield The security's annual yield
 159       * @param mixed $basis The type of day count to use.
 160       *                         0 or omitted    US (NASD) 30/360
 161       *                         1               Actual/actual
 162       *                         2               Actual/360
 163       *                         3               Actual/365
 164       *                         4               European 30/360
 165       *
 166       * @return float|string Result, or a string containing an error
 167       */
 168      public static function priceAtMaturity(
 169          $settlement,
 170          $maturity,
 171          $issue,
 172          $rate,
 173          $yield,
 174          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 175      ) {
 176          $settlement = Functions::flattenSingleValue($settlement);
 177          $maturity = Functions::flattenSingleValue($maturity);
 178          $issue = Functions::flattenSingleValue($issue);
 179          $rate = Functions::flattenSingleValue($rate);
 180          $yield = Functions::flattenSingleValue($yield);
 181          $basis = ($basis === null)
 182              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 183              : Functions::flattenSingleValue($basis);
 184  
 185          try {
 186              $settlement = SecurityValidations::validateSettlementDate($settlement);
 187              $maturity = SecurityValidations::validateMaturityDate($maturity);
 188              SecurityValidations::validateSecurityPeriod($settlement, $maturity);
 189              $issue = SecurityValidations::validateIssueDate($issue);
 190              $rate = SecurityValidations::validateRate($rate);
 191              $yield = SecurityValidations::validateYield($yield);
 192              $basis = SecurityValidations::validateBasis($basis);
 193          } catch (Exception $e) {
 194              return $e->getMessage();
 195          }
 196  
 197          $daysPerYear = Helpers::daysPerYear(DateTimeExcel\DateParts::year($settlement), $basis);
 198          if (!is_numeric($daysPerYear)) {
 199              return $daysPerYear;
 200          }
 201          $daysBetweenIssueAndSettlement = DateTimeExcel\YearFrac::fraction($issue, $settlement, $basis);
 202          if (!is_numeric($daysBetweenIssueAndSettlement)) {
 203              //    return date error
 204              return $daysBetweenIssueAndSettlement;
 205          }
 206          $daysBetweenIssueAndSettlement *= $daysPerYear;
 207          $daysBetweenIssueAndMaturity = DateTimeExcel\YearFrac::fraction($issue, $maturity, $basis);
 208          if (!is_numeric($daysBetweenIssueAndMaturity)) {
 209              //    return date error
 210              return $daysBetweenIssueAndMaturity;
 211          }
 212          $daysBetweenIssueAndMaturity *= $daysPerYear;
 213          $daysBetweenSettlementAndMaturity = DateTimeExcel\YearFrac::fraction($settlement, $maturity, $basis);
 214          if (!is_numeric($daysBetweenSettlementAndMaturity)) {
 215              //    return date error
 216              return $daysBetweenSettlementAndMaturity;
 217          }
 218          $daysBetweenSettlementAndMaturity *= $daysPerYear;
 219  
 220          return (100 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate * 100)) /
 221              (1 + (($daysBetweenSettlementAndMaturity / $daysPerYear) * $yield)) -
 222              (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate * 100);
 223      }
 224  
 225      /**
 226       * RECEIVED.
 227       *
 228       * Returns the amount received at maturity for a fully invested Security.
 229       *
 230       * @param mixed $settlement The security's settlement date.
 231       *                              The security settlement date is the date after the issue date when the security
 232       *                                  is traded to the buyer.
 233       * @param mixed $maturity The security's maturity date.
 234       *                            The maturity date is the date when the security expires.
 235       * @param mixed $investment The amount invested in the security
 236       * @param mixed $discount The security's discount rate
 237       * @param mixed $basis The type of day count to use.
 238       *                         0 or omitted    US (NASD) 30/360
 239       *                         1               Actual/actual
 240       *                         2               Actual/360
 241       *                         3               Actual/365
 242       *                         4               European 30/360
 243       *
 244       * @return float|string Result, or a string containing an error
 245       */
 246      public static function received(
 247          $settlement,
 248          $maturity,
 249          $investment,
 250          $discount,
 251          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 252      ) {
 253          $settlement = Functions::flattenSingleValue($settlement);
 254          $maturity = Functions::flattenSingleValue($maturity);
 255          $investment = Functions::flattenSingleValue($investment);
 256          $discount = Functions::flattenSingleValue($discount);
 257          $basis = ($basis === null)
 258              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 259              : Functions::flattenSingleValue($basis);
 260  
 261          try {
 262              $settlement = SecurityValidations::validateSettlementDate($settlement);
 263              $maturity = SecurityValidations::validateMaturityDate($maturity);
 264              SecurityValidations::validateSecurityPeriod($settlement, $maturity);
 265              $investment = SecurityValidations::validateFloat($investment);
 266              $discount = SecurityValidations::validateDiscount($discount);
 267              $basis = SecurityValidations::validateBasis($basis);
 268          } catch (Exception $e) {
 269              return $e->getMessage();
 270          }
 271  
 272          if ($investment <= 0) {
 273              return Functions::NAN();
 274          }
 275          $daysBetweenSettlementAndMaturity = DateTimeExcel\YearFrac::fraction($settlement, $maturity, $basis);
 276          if (!is_numeric($daysBetweenSettlementAndMaturity)) {
 277              //    return date error
 278              return $daysBetweenSettlementAndMaturity;
 279          }
 280  
 281          return $investment / (1 - ($discount * $daysBetweenSettlementAndMaturity));
 282      }
 283  }