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