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