[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\Functions;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
   7  
   8  class Periodic
   9  {
  10      const FINANCIAL_MAX_ITERATIONS = 128;
  11  
  12      const FINANCIAL_PRECISION = 1.0e-08;
  13  
  14      /**
  15       * IRR.
  16       *
  17       * Returns the internal rate of return for a series of cash flows represented by the numbers in values.
  18       * These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur
  19       * at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received
  20       * for an investment consisting of payments (negative values) and income (positive values) that occur at regular
  21       * periods.
  22       *
  23       * Excel Function:
  24       *        IRR(values[,guess])
  25       *
  26       * @param mixed $values An array or a reference to cells that contain numbers for which you want
  27       *                                    to calculate the internal rate of return.
  28       *                                Values must contain at least one positive value and one negative value to
  29       *                                    calculate the internal rate of return.
  30       * @param mixed $guess A number that you guess is close to the result of IRR
  31       *
  32       * @return float|string
  33       */
  34      public static function rate($values, $guess = 0.1)
  35      {
  36          if (!is_array($values)) {
  37              return ExcelError::VALUE();
  38          }
  39          $values = Functions::flattenArray($values);
  40          $guess = Functions::flattenSingleValue($guess);
  41  
  42          // create an initial range, with a root somewhere between 0 and guess
  43          $x1 = 0.0;
  44          $x2 = $guess;
  45          $f1 = self::presentValue($x1, $values);
  46          $f2 = self::presentValue($x2, $values);
  47          for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
  48              if (($f1 * $f2) < 0.0) {
  49                  break;
  50              }
  51              if (abs($f1) < abs($f2)) {
  52                  $f1 = self::presentValue($x1 += 1.6 * ($x1 - $x2), $values);
  53              } else {
  54                  $f2 = self::presentValue($x2 += 1.6 * ($x2 - $x1), $values);
  55              }
  56          }
  57          if (($f1 * $f2) > 0.0) {
  58              return ExcelError::VALUE();
  59          }
  60  
  61          $f = self::presentValue($x1, $values);
  62          if ($f < 0.0) {
  63              $rtb = $x1;
  64              $dx = $x2 - $x1;
  65          } else {
  66              $rtb = $x2;
  67              $dx = $x1 - $x2;
  68          }
  69  
  70          for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
  71              $dx *= 0.5;
  72              $x_mid = $rtb + $dx;
  73              $f_mid = self::presentValue($x_mid, $values);
  74              if ($f_mid <= 0.0) {
  75                  $rtb = $x_mid;
  76              }
  77              if ((abs($f_mid) < self::FINANCIAL_PRECISION) || (abs($dx) < self::FINANCIAL_PRECISION)) {
  78                  return $x_mid;
  79              }
  80          }
  81  
  82          return ExcelError::VALUE();
  83      }
  84  
  85      /**
  86       * MIRR.
  87       *
  88       * Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both
  89       *        the cost of the investment and the interest received on reinvestment of cash.
  90       *
  91       * Excel Function:
  92       *        MIRR(values,finance_rate, reinvestment_rate)
  93       *
  94       * @param mixed $values An array or a reference to cells that contain a series of payments and
  95       *                         income occurring at regular intervals.
  96       *                      Payments are negative value, income is positive values.
  97       * @param mixed $financeRate The interest rate you pay on the money used in the cash flows
  98       * @param mixed $reinvestmentRate The interest rate you receive on the cash flows as you reinvest them
  99       *
 100       * @return float|string Result, or a string containing an error
 101       */
 102      public static function modifiedRate($values, $financeRate, $reinvestmentRate)
 103      {
 104          if (!is_array($values)) {
 105              return ExcelError::VALUE();
 106          }
 107          $values = Functions::flattenArray($values);
 108          $financeRate = Functions::flattenSingleValue($financeRate);
 109          $reinvestmentRate = Functions::flattenSingleValue($reinvestmentRate);
 110          $n = count($values);
 111  
 112          $rr = 1.0 + $reinvestmentRate;
 113          $fr = 1.0 + $financeRate;
 114  
 115          $npvPos = $npvNeg = 0.0;
 116          foreach ($values as $i => $v) {
 117              if ($v >= 0) {
 118                  $npvPos += $v / $rr ** $i;
 119              } else {
 120                  $npvNeg += $v / $fr ** $i;
 121              }
 122          }
 123  
 124          if (($npvNeg === 0.0) || ($npvPos === 0.0) || ($reinvestmentRate <= -1.0)) {
 125              return ExcelError::VALUE();
 126          }
 127  
 128          $mirr = ((-$npvPos * $rr ** $n)
 129                  / ($npvNeg * ($rr))) ** (1.0 / ($n - 1)) - 1.0;
 130  
 131          return is_finite($mirr) ? $mirr : ExcelError::VALUE();
 132      }
 133  
 134      /**
 135       * NPV.
 136       *
 137       * Returns the Net Present Value of a cash flow series given a discount rate.
 138       *
 139       * @param mixed $rate
 140       *
 141       * @return float
 142       */
 143      public static function presentValue($rate, ...$args)
 144      {
 145          $returnValue = 0;
 146  
 147          $rate = Functions::flattenSingleValue($rate);
 148          $aArgs = Functions::flattenArray($args);
 149  
 150          // Calculate
 151          $countArgs = count($aArgs);
 152          for ($i = 1; $i <= $countArgs; ++$i) {
 153              // Is it a numeric value?
 154              if (is_numeric($aArgs[$i - 1])) {
 155                  $returnValue += $aArgs[$i - 1] / (1 + $rate) ** $i;
 156              }
 157          }
 158  
 159          return $returnValue;
 160      }
 161  }