[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\CashFlow\Variable;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Exception;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   8  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   9  
  10  class NonPeriodic
  11  {
  12      const FINANCIAL_MAX_ITERATIONS = 128;
  13  
  14      const FINANCIAL_PRECISION = 1.0e-08;
  15  
  16      const DEFAULT_GUESS = 0.1;
  17  
  18      /**
  19       * XIRR.
  20       *
  21       * Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
  22       *
  23       * Excel Function:
  24       *        =XIRR(values,dates,guess)
  25       *
  26       * @param float[] $values     A series of cash flow payments
  27       *                                The series of values must contain at least one positive value & one negative value
  28       * @param mixed[] $dates      A series of payment dates
  29       *                                The first payment date indicates the beginning of the schedule of payments
  30       *                                All other dates must be later than this date, but they may occur in any order
  31       * @param mixed $guess        An optional guess at the expected answer
  32       *
  33       * @return float|string
  34       */
  35      public static function rate($values, $dates, $guess = self::DEFAULT_GUESS)
  36      {
  37          $rslt = self::xirrPart1($values, $dates);
  38          if ($rslt !== '') {
  39              return $rslt;
  40          }
  41  
  42          // create an initial range, with a root somewhere between 0 and guess
  43          $guess = Functions::flattenSingleValue($guess) ?? self::DEFAULT_GUESS;
  44          if (!is_numeric($guess)) {
  45              return ExcelError::VALUE();
  46          }
  47          $guess = ($guess + 0.0) ?: self::DEFAULT_GUESS;
  48          $x1 = 0.0;
  49          $x2 = $guess + 0.0;
  50          $f1 = self::xnpvOrdered($x1, $values, $dates, false);
  51          $f2 = self::xnpvOrdered($x2, $values, $dates, false);
  52          $found = false;
  53          for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
  54              if (!is_numeric($f1) || !is_numeric($f2)) {
  55                  break;
  56              }
  57              $f1 = (float) $f1;
  58              $f2 = (float) $f2;
  59              if (($f1 * $f2) < 0.0) {
  60                  $found = true;
  61  
  62                  break;
  63              } elseif (abs($f1) < abs($f2)) {
  64                  $x1 += 1.6 * ($x1 - $x2);
  65                  $f1 = self::xnpvOrdered($x1, $values, $dates, false);
  66              } else {
  67                  $x2 += 1.6 * ($x2 - $x1);
  68                  $f2 = self::xnpvOrdered($x2, $values, $dates, false);
  69              }
  70          }
  71          if (!$found) {
  72              return ExcelError::NAN();
  73          }
  74  
  75          return self::xirrPart3($values, $dates, $x1, $x2);
  76      }
  77  
  78      /**
  79       * XNPV.
  80       *
  81       * Returns the net present value for a schedule of cash flows that is not necessarily periodic.
  82       * To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
  83       *
  84       * Excel Function:
  85       *        =XNPV(rate,values,dates)
  86       *
  87       * @param float $rate the discount rate to apply to the cash flows
  88       * @param float[] $values A series of cash flows that corresponds to a schedule of payments in dates.
  89       *                          The first payment is optional and corresponds to a cost or payment that occurs
  90       *                              at the beginning of the investment.
  91       *                          If the first value is a cost or payment, it must be a negative value.
  92       *                             All succeeding payments are discounted based on a 365-day year.
  93       *                          The series of values must contain at least one positive value and one negative value.
  94       * @param mixed[] $dates A schedule of payment dates that corresponds to the cash flow payments.
  95       *                         The first payment date indicates the beginning of the schedule of payments.
  96       *                         All other dates must be later than this date, but they may occur in any order.
  97       *
  98       * @return float|string
  99       */
 100      public static function presentValue($rate, $values, $dates)
 101      {
 102          return self::xnpvOrdered($rate, $values, $dates, true);
 103      }
 104  
 105      private static function bothNegAndPos(bool $neg, bool $pos): bool
 106      {
 107          return $neg && $pos;
 108      }
 109  
 110      /**
 111       * @param mixed $values
 112       * @param mixed $dates
 113       */
 114      private static function xirrPart1(&$values, &$dates): string
 115      {
 116          $values = Functions::flattenArray($values);
 117          $dates = Functions::flattenArray($dates);
 118          $valuesIsArray = count($values) > 1;
 119          $datesIsArray = count($dates) > 1;
 120          if (!$valuesIsArray && !$datesIsArray) {
 121              return ExcelError::NA();
 122          }
 123          if (count($values) != count($dates)) {
 124              return ExcelError::NAN();
 125          }
 126  
 127          $datesCount = count($dates);
 128          for ($i = 0; $i < $datesCount; ++$i) {
 129              try {
 130                  $dates[$i] = DateTimeExcel\Helpers::getDateValue($dates[$i]);
 131              } catch (Exception $e) {
 132                  return $e->getMessage();
 133              }
 134          }
 135  
 136          return self::xirrPart2($values);
 137      }
 138  
 139      private static function xirrPart2(array &$values): string
 140      {
 141          $valCount = count($values);
 142          $foundpos = false;
 143          $foundneg = false;
 144          for ($i = 0; $i < $valCount; ++$i) {
 145              $fld = $values[$i];
 146              if (!is_numeric($fld)) {
 147                  return ExcelError::VALUE();
 148              } elseif ($fld > 0) {
 149                  $foundpos = true;
 150              } elseif ($fld < 0) {
 151                  $foundneg = true;
 152              }
 153          }
 154          if (!self::bothNegAndPos($foundneg, $foundpos)) {
 155              return ExcelError::NAN();
 156          }
 157  
 158          return '';
 159      }
 160  
 161      /**
 162       * @return float|string
 163       */
 164      private static function xirrPart3(array $values, array $dates, float $x1, float $x2)
 165      {
 166          $f = self::xnpvOrdered($x1, $values, $dates, false);
 167          if ($f < 0.0) {
 168              $rtb = $x1;
 169              $dx = $x2 - $x1;
 170          } else {
 171              $rtb = $x2;
 172              $dx = $x1 - $x2;
 173          }
 174  
 175          $rslt = ExcelError::VALUE();
 176          for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
 177              $dx *= 0.5;
 178              $x_mid = $rtb + $dx;
 179              $f_mid = (float) self::xnpvOrdered($x_mid, $values, $dates, false);
 180              if ($f_mid <= 0.0) {
 181                  $rtb = $x_mid;
 182              }
 183              if ((abs($f_mid) < self::FINANCIAL_PRECISION) || (abs($dx) < self::FINANCIAL_PRECISION)) {
 184                  $rslt = $x_mid;
 185  
 186                  break;
 187              }
 188          }
 189  
 190          return $rslt;
 191      }
 192  
 193      /**
 194       * @param mixed $rate
 195       * @param mixed $values
 196       * @param mixed $dates
 197       *
 198       * @return float|string
 199       */
 200      private static function xnpvOrdered($rate, $values, $dates, bool $ordered = true)
 201      {
 202          $rate = Functions::flattenSingleValue($rate);
 203          $values = Functions::flattenArray($values);
 204          $dates = Functions::flattenArray($dates);
 205          $valCount = count($values);
 206  
 207          try {
 208              self::validateXnpv($rate, $values, $dates);
 209              $date0 = DateTimeExcel\Helpers::getDateValue($dates[0]);
 210          } catch (Exception $e) {
 211              return $e->getMessage();
 212          }
 213  
 214          $xnpv = 0.0;
 215          for ($i = 0; $i < $valCount; ++$i) {
 216              if (!is_numeric($values[$i])) {
 217                  return ExcelError::VALUE();
 218              }
 219  
 220              try {
 221                  $datei = DateTimeExcel\Helpers::getDateValue($dates[$i]);
 222              } catch (Exception $e) {
 223                  return $e->getMessage();
 224              }
 225              if ($date0 > $datei) {
 226                  $dif = $ordered ? ExcelError::NAN() : -((int) DateTimeExcel\Difference::interval($datei, $date0, 'd'));
 227              } else {
 228                  $dif = DateTimeExcel\Difference::interval($date0, $datei, 'd');
 229              }
 230              if (!is_numeric($dif)) {
 231                  return $dif;
 232              }
 233              if ($rate <= -1.0) {
 234                  $xnpv += -abs($values[$i]) / (-1 - $rate) ** ($dif / 365);
 235              } else {
 236                  $xnpv += $values[$i] / (1 + $rate) ** ($dif / 365);
 237              }
 238          }
 239  
 240          return is_finite($xnpv) ? $xnpv : ExcelError::VALUE();
 241      }
 242  
 243      /**
 244       * @param mixed $rate
 245       */
 246      private static function validateXnpv($rate, array $values, array $dates): void
 247      {
 248          if (!is_numeric($rate)) {
 249              throw new Exception(ExcelError::VALUE());
 250          }
 251          $valCount = count($values);
 252          if ($valCount != count($dates)) {
 253              throw new Exception(ExcelError::NAN());
 254          }
 255          if ($valCount > 1 && ((min($values) > 0) || (max($values) < 0))) {
 256              throw new Exception(ExcelError::NAN());
 257          }
 258      }
 259  }