[Summary view]

Differences Between: [Versions 400 and 403] [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)) {
  55                  return $f1;
  56              }
  57              if (!is_numeric($f2)) {
  58                  return $f2;
  59              }
  60              $f1 = (float) $f1;
  61              $f2 = (float) $f2;
  62              if (($f1 * $f2) < 0.0) {
  63                  $found = true;
  64  
  65                  break;
  66              } elseif (abs($f1) < abs($f2)) {
  67                  $x1 += 1.6 * ($x1 - $x2);
  68                  $f1 = self::xnpvOrdered($x1, $values, $dates, false);
  69              } else {
  70                  $x2 += 1.6 * ($x2 - $x1);
  71                  $f2 = self::xnpvOrdered($x2, $values, $dates, false);
  72              }
  73          }
  74          if ($found) {
  75              return self::xirrPart3($values, $dates, $x1, $x2);
  76          }
  77  
  78          // Newton-Raphson didn't work - try bisection
  79          $x1 = $guess - 0.5;
  80          $x2 = $guess + 0.5;
  81          for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
  82              $f1 = self::xnpvOrdered($x1, $values, $dates, false, true);
  83              $f2 = self::xnpvOrdered($x2, $values, $dates, false, true);
  84              if (!is_numeric($f1) || !is_numeric($f2)) {
  85                  break;
  86              }
  87              if ($f1 * $f2 <= 0) {
  88                  $found = true;
  89  
  90                  break;
  91              }
  92              $x1 -= 0.5;
  93              $x2 += 0.5;
  94          }
  95          if ($found) {
  96              return self::xirrBisection($values, $dates, $x1, $x2);
  97          }
  98  
  99          return ExcelError::NAN();
 100      }
 101  
 102      /**
 103       * XNPV.
 104       *
 105       * Returns the net present value for a schedule of cash flows that is not necessarily periodic.
 106       * To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
 107       *
 108       * Excel Function:
 109       *        =XNPV(rate,values,dates)
 110       *
 111       * @param float $rate the discount rate to apply to the cash flows
 112       * @param float[] $values A series of cash flows that corresponds to a schedule of payments in dates.
 113       *                          The first payment is optional and corresponds to a cost or payment that occurs
 114       *                              at the beginning of the investment.
 115       *                          If the first value is a cost or payment, it must be a negative value.
 116       *                             All succeeding payments are discounted based on a 365-day year.
 117       *                          The series of values must contain at least one positive value and one negative value.
 118       * @param mixed[] $dates A schedule of payment dates that corresponds to the cash flow payments.
 119       *                         The first payment date indicates the beginning of the schedule of payments.
 120       *                         All other dates must be later than this date, but they may occur in any order.
 121       *
 122       * @return float|string
 123       */
 124      public static function presentValue($rate, $values, $dates)
 125      {
 126          return self::xnpvOrdered($rate, $values, $dates, true);
 127      }
 128  
 129      private static function bothNegAndPos(bool $neg, bool $pos): bool
 130      {
 131          return $neg && $pos;
 132      }
 133  
 134      /**
 135       * @param mixed $values
 136       * @param mixed $dates
 137       */
 138      private static function xirrPart1(&$values, &$dates): string
 139      {
 140          $values = Functions::flattenArray($values);
 141          $dates = Functions::flattenArray($dates);
 142          $valuesIsArray = count($values) > 1;
 143          $datesIsArray = count($dates) > 1;
 144          if (!$valuesIsArray && !$datesIsArray) {
 145              return ExcelError::NA();
 146          }
 147          if (count($values) != count($dates)) {
 148              return ExcelError::NAN();
 149          }
 150  
 151          $datesCount = count($dates);
 152          for ($i = 0; $i < $datesCount; ++$i) {
 153              try {
 154                  $dates[$i] = DateTimeExcel\Helpers::getDateValue($dates[$i]);
 155              } catch (Exception $e) {
 156                  return $e->getMessage();
 157              }
 158          }
 159  
 160          return self::xirrPart2($values);
 161      }
 162  
 163      private static function xirrPart2(array &$values): string
 164      {
 165          $valCount = count($values);
 166          $foundpos = false;
 167          $foundneg = false;
 168          for ($i = 0; $i < $valCount; ++$i) {
 169              $fld = $values[$i];
 170              if (!is_numeric($fld)) {
 171                  return ExcelError::VALUE();
 172              } elseif ($fld > 0) {
 173                  $foundpos = true;
 174              } elseif ($fld < 0) {
 175                  $foundneg = true;
 176              }
 177          }
 178          if (!self::bothNegAndPos($foundneg, $foundpos)) {
 179              return ExcelError::NAN();
 180          }
 181  
 182          return '';
 183      }
 184  
 185      /**
 186       * @return float|string
 187       */
 188      private static function xirrPart3(array $values, array $dates, float $x1, float $x2)
 189      {
 190          $f = self::xnpvOrdered($x1, $values, $dates, false);
 191          if ($f < 0.0) {
 192              $rtb = $x1;
 193              $dx = $x2 - $x1;
 194          } else {
 195              $rtb = $x2;
 196              $dx = $x1 - $x2;
 197          }
 198  
 199          $rslt = ExcelError::VALUE();
 200          for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
 201              $dx *= 0.5;
 202              $x_mid = $rtb + $dx;
 203              $f_mid = (float) self::xnpvOrdered($x_mid, $values, $dates, false);
 204              if ($f_mid <= 0.0) {
 205                  $rtb = $x_mid;
 206              }
 207              if ((abs($f_mid) < self::FINANCIAL_PRECISION) || (abs($dx) < self::FINANCIAL_PRECISION)) {
 208                  $rslt = $x_mid;
 209  
 210                  break;
 211              }
 212          }
 213  
 214          return $rslt;
 215      }
 216  
 217      /**
 218       * @return float|string
 219       */
 220      private static function xirrBisection(array $values, array $dates, float $x1, float $x2)
 221      {
 222          $rslt = ExcelError::NAN();
 223          for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
 224              $rslt = ExcelError::NAN();
 225              $f1 = self::xnpvOrdered($x1, $values, $dates, false, true);
 226              $f2 = self::xnpvOrdered($x2, $values, $dates, false, true);
 227              if (!is_numeric($f1) || !is_numeric($f2)) {
 228                  break;
 229              }
 230              $f1 = (float) $f1;
 231              $f2 = (float) $f2;
 232              if (abs($f1) < self::FINANCIAL_PRECISION && abs($f2) < self::FINANCIAL_PRECISION) {
 233                  break;
 234              }
 235              if ($f1 * $f2 > 0) {
 236                  break;
 237              }
 238              $rslt = ($x1 + $x2) / 2;
 239              $f3 = self::xnpvOrdered($rslt, $values, $dates, false, true);
 240              if (!is_float($f3)) {
 241                  break;
 242              }
 243              if ($f3 * $f1 < 0) {
 244                  $x2 = $rslt;
 245              } else {
 246                  $x1 = $rslt;
 247              }
 248              if (abs($f3) < self::FINANCIAL_PRECISION) {
 249                  break;
 250              }
 251          }
 252  
 253          return $rslt;
 254      }
 255  
 256      /**
 257       * @param mixed $rate
 258       * @param mixed $values
 259       * @param mixed $dates
 260       *
 261       * @return float|string
 262       */
 263      private static function xnpvOrdered($rate, $values, $dates, bool $ordered = true, bool $capAtNegative1 = false)
 264      {
 265          $rate = Functions::flattenSingleValue($rate);
 266          $values = Functions::flattenArray($values);
 267          $dates = Functions::flattenArray($dates);
 268          $valCount = count($values);
 269  
 270          try {
 271              self::validateXnpv($rate, $values, $dates);
 272              if ($capAtNegative1 && $rate <= -1) {
 273                  $rate = -1.0 + 1.0E-10;
 274              }
 275              $date0 = DateTimeExcel\Helpers::getDateValue($dates[0]);
 276          } catch (Exception $e) {
 277              return $e->getMessage();
 278          }
 279  
 280          $xnpv = 0.0;
 281          for ($i = 0; $i < $valCount; ++$i) {
 282              if (!is_numeric($values[$i])) {
 283                  return ExcelError::VALUE();
 284              }
 285  
 286              try {
 287                  $datei = DateTimeExcel\Helpers::getDateValue($dates[$i]);
 288              } catch (Exception $e) {
 289                  return $e->getMessage();
 290              }
 291              if ($date0 > $datei) {
 292                  $dif = $ordered ? ExcelError::NAN() : -((int) DateTimeExcel\Difference::interval($datei, $date0, 'd'));
 293              } else {
 294                  $dif = Functions::scalar(DateTimeExcel\Difference::interval($date0, $datei, 'd'));
 295              }
 296              if (!is_numeric($dif)) {
 297                  return $dif;
 298              }
 299              if ($rate <= -1.0) {
 300                  $xnpv += -abs($values[$i]) / (-1 - $rate) ** ($dif / 365);
 301              } else {
 302                  $xnpv += $values[$i] / (1 + $rate) ** ($dif / 365);
 303              }
 304          }
 305  
 306          return is_finite($xnpv) ? $xnpv : ExcelError::VALUE();
 307      }
 308  
 309      /**
 310       * @param mixed $rate
 311       */
 312      private static function validateXnpv($rate, array $values, array $dates): void
 313      {
 314          if (!is_numeric($rate)) {
 315              throw new Exception(ExcelError::VALUE());
 316          }
 317          $valCount = count($values);
 318          if ($valCount != count($dates)) {
 319              throw new Exception(ExcelError::NAN());
 320          }
 321          if ($valCount > 1 && ((min($values) > 0) || (max($values) < 0))) {
 322              throw new Exception(ExcelError::NAN());
 323          }
 324      }
 325  }