[Summary view]

Differences Between: [Versions 400 and 401]

   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\Functions;
   9  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
  10  
  11  class Rates
  12  {
  13      /**
  14       * DISC.
  15       *
  16       * Returns the discount rate for a security.
  17       *
  18       * Excel Function:
  19       *        DISC(settlement,maturity,price,redemption[,basis])
  20       *
  21       * @param mixed $settlement The security's settlement date.
  22       *                              The security settlement date is the date after the issue
  23       *                                  date when the security is traded to the buyer.
  24       * @param mixed $maturity The security's maturity date.
  25       *                            The maturity date is the date when the security expires.
  26       * @param mixed $price The security's price per $100 face value
  27       * @param mixed $redemption The security's redemption value per $100 face value
  28       * @param mixed $basis The type of day count to use.
  29       *                         0 or omitted    US (NASD) 30/360
  30       *                         1               Actual/actual
  31       *                         2               Actual/360
  32       *                         3               Actual/365
  33       *                         4               European 30/360
  34       *
  35       * @return float|string
  36       */
  37      public static function discount(
  38          $settlement,
  39          $maturity,
  40          $price,
  41          $redemption,
  42          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
  43      ) {
  44          $settlement = Functions::flattenSingleValue($settlement);
  45          $maturity = Functions::flattenSingleValue($maturity);
  46          $price = Functions::flattenSingleValue($price);
  47          $redemption = Functions::flattenSingleValue($redemption);
  48          $basis = ($basis === null)
  49              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
  50              : Functions::flattenSingleValue($basis);
  51  
  52          try {
  53              $settlement = SecurityValidations::validateSettlementDate($settlement);
  54              $maturity = SecurityValidations::validateMaturityDate($maturity);
  55              SecurityValidations::validateSecurityPeriod($settlement, $maturity);
  56              $price = SecurityValidations::validatePrice($price);
  57              $redemption = SecurityValidations::validateRedemption($redemption);
  58              $basis = SecurityValidations::validateBasis($basis);
  59          } catch (Exception $e) {
  60              return $e->getMessage();
  61          }
  62  
  63          if ($price <= 0.0) {
  64              return ExcelError::NAN();
  65          }
  66  
  67          $daysBetweenSettlementAndMaturity = Functions::scalar(DateTimeExcel\YearFrac::fraction($settlement, $maturity, $basis));
  68          if (!is_numeric($daysBetweenSettlementAndMaturity)) {
  69              //    return date error
  70              return $daysBetweenSettlementAndMaturity;
  71          }
  72  
  73          return (1 - $price / $redemption) / $daysBetweenSettlementAndMaturity;
  74      }
  75  
  76      /**
  77       * INTRATE.
  78       *
  79       * Returns the interest rate for a fully invested security.
  80       *
  81       * Excel Function:
  82       *        INTRATE(settlement,maturity,investment,redemption[,basis])
  83       *
  84       * @param mixed $settlement The security's settlement date.
  85       *                              The security settlement date is the date after the issue date when the security
  86       *                                  is traded to the buyer.
  87       * @param mixed $maturity The security's maturity date.
  88       *                            The maturity date is the date when the security expires.
  89       * @param mixed $investment the amount invested in the security
  90       * @param mixed $redemption the amount to be received at maturity
  91       * @param mixed $basis The type of day count to use.
  92       *                         0 or omitted    US (NASD) 30/360
  93       *                         1               Actual/actual
  94       *                         2               Actual/360
  95       *                         3               Actual/365
  96       *                         4               European 30/360
  97       *
  98       * @return float|string
  99       */
 100      public static function interest(
 101          $settlement,
 102          $maturity,
 103          $investment,
 104          $redemption,
 105          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 106      ) {
 107          $settlement = Functions::flattenSingleValue($settlement);
 108          $maturity = Functions::flattenSingleValue($maturity);
 109          $investment = Functions::flattenSingleValue($investment);
 110          $redemption = Functions::flattenSingleValue($redemption);
 111          $basis = ($basis === null)
 112              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 113              : Functions::flattenSingleValue($basis);
 114  
 115          try {
 116              $settlement = SecurityValidations::validateSettlementDate($settlement);
 117              $maturity = SecurityValidations::validateMaturityDate($maturity);
 118              SecurityValidations::validateSecurityPeriod($settlement, $maturity);
 119              $investment = SecurityValidations::validateFloat($investment);
 120              $redemption = SecurityValidations::validateRedemption($redemption);
 121              $basis = SecurityValidations::validateBasis($basis);
 122          } catch (Exception $e) {
 123              return $e->getMessage();
 124          }
 125  
 126          if ($investment <= 0) {
 127              return ExcelError::NAN();
 128          }
 129  
 130          $daysBetweenSettlementAndMaturity = Functions::scalar(DateTimeExcel\YearFrac::fraction($settlement, $maturity, $basis));
 131          if (!is_numeric($daysBetweenSettlementAndMaturity)) {
 132              //    return date error
 133              return $daysBetweenSettlementAndMaturity;
 134          }
 135  
 136          return (($redemption / $investment) - 1) / ($daysBetweenSettlementAndMaturity);
 137      }
 138  }