[Summary view]

Differences Between: [Versions 400 and 402]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\Financial\CashFlow\Constant;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\Exception;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Financial\CashFlow\CashFlowValidations;
   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 Periodic
  12  {
  13      /**
  14       * FV.
  15       *
  16       * Returns the Future Value of a cash flow with constant payments and interest rate (annuities).
  17       *
  18       * Excel Function:
  19       *        FV(rate,nper,pmt[,pv[,type]])
  20       *
  21       * @param mixed $rate The interest rate per period
  22       * @param mixed $numberOfPeriods Total number of payment periods in an annuity as an integer
  23       * @param mixed $payment The payment made each period: it cannot change over the
  24       *                            life of the annuity. Typically, pmt contains principal
  25       *                            and interest but no other fees or taxes.
  26       * @param mixed $presentValue present Value, or the lump-sum amount that a series of
  27       *                            future payments is worth right now
  28       * @param mixed $type A number 0 or 1 and indicates when payments are due:
  29       *                      0 or omitted    At the end of the period.
  30       *                      1               At the beginning of the period.
  31       *
  32       * @return float|string
  33       */
  34      public static function futureValue(
  35          $rate,
  36          $numberOfPeriods,
  37          $payment = 0.0,
  38          $presentValue = 0.0,
  39          $type = FinancialConstants::PAYMENT_END_OF_PERIOD
  40      ) {
  41          $rate = Functions::flattenSingleValue($rate);
  42          $numberOfPeriods = Functions::flattenSingleValue($numberOfPeriods);
  43          $payment = ($payment === null) ? 0.0 : Functions::flattenSingleValue($payment);
  44          $presentValue = ($presentValue === null) ? 0.0 : Functions::flattenSingleValue($presentValue);
  45          $type = ($type === null) ? FinancialConstants::PAYMENT_END_OF_PERIOD : Functions::flattenSingleValue($type);
  46  
  47          try {
  48              $rate = CashFlowValidations::validateRate($rate);
  49              $numberOfPeriods = CashFlowValidations::validateInt($numberOfPeriods);
  50              $payment = CashFlowValidations::validateFloat($payment);
  51              $presentValue = CashFlowValidations::validatePresentValue($presentValue);
  52              $type = CashFlowValidations::validatePeriodType($type);
  53          } catch (Exception $e) {
  54              return $e->getMessage();
  55          }
  56  
  57          return self::calculateFutureValue($rate, $numberOfPeriods, $payment, $presentValue, $type);
  58      }
  59  
  60      /**
  61       * PV.
  62       *
  63       * Returns the Present Value of a cash flow with constant payments and interest rate (annuities).
  64       *
  65       * @param mixed $rate Interest rate per period
  66       * @param mixed $numberOfPeriods Number of periods as an integer
  67       * @param mixed $payment Periodic payment (annuity)
  68       * @param mixed $futureValue Future Value
  69       * @param mixed $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  70       *
  71       * @return float|string Result, or a string containing an error
  72       */
  73      public static function presentValue(
  74          $rate,
  75          $numberOfPeriods,
  76          $payment = 0.0,
  77          $futureValue = 0.0,
  78          $type = FinancialConstants::PAYMENT_END_OF_PERIOD
  79      ) {
  80          $rate = Functions::flattenSingleValue($rate);
  81          $numberOfPeriods = Functions::flattenSingleValue($numberOfPeriods);
  82          $payment = ($payment === null) ? 0.0 : Functions::flattenSingleValue($payment);
  83          $futureValue = ($futureValue === null) ? 0.0 : Functions::flattenSingleValue($futureValue);
  84          $type = ($type === null) ? FinancialConstants::PAYMENT_END_OF_PERIOD : Functions::flattenSingleValue($type);
  85  
  86          try {
  87              $rate = CashFlowValidations::validateRate($rate);
  88              $numberOfPeriods = CashFlowValidations::validateInt($numberOfPeriods);
  89              $payment = CashFlowValidations::validateFloat($payment);
  90              $futureValue = CashFlowValidations::validateFutureValue($futureValue);
  91              $type = CashFlowValidations::validatePeriodType($type);
  92          } catch (Exception $e) {
  93              return $e->getMessage();
  94          }
  95  
  96          // Validate parameters
  97          if ($numberOfPeriods < 0) {
  98              return ExcelError::NAN();
  99          }
 100  
 101          return self::calculatePresentValue($rate, $numberOfPeriods, $payment, $futureValue, $type);
 102      }
 103  
 104      /**
 105       * NPER.
 106       *
 107       * Returns the number of periods for a cash flow with constant periodic payments (annuities), and interest rate.
 108       *
 109       * @param mixed $rate Interest rate per period
 110       * @param mixed $payment Periodic payment (annuity)
 111       * @param mixed $presentValue Present Value
 112       * @param mixed $futureValue Future Value
 113       * @param mixed $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
 114       *
 115       * @return float|string Result, or a string containing an error
 116       */
 117      public static function periods(
 118          $rate,
 119          $payment,
 120          $presentValue,
 121          $futureValue = 0.0,
 122          $type = FinancialConstants::PAYMENT_END_OF_PERIOD
 123      ) {
 124          $rate = Functions::flattenSingleValue($rate);
 125          $payment = Functions::flattenSingleValue($payment);
 126          $presentValue = Functions::flattenSingleValue($presentValue);
 127          $futureValue = ($futureValue === null) ? 0.0 : Functions::flattenSingleValue($futureValue);
 128          $type = ($type === null) ? FinancialConstants::PAYMENT_END_OF_PERIOD : Functions::flattenSingleValue($type);
 129  
 130          try {
 131              $rate = CashFlowValidations::validateRate($rate);
 132              $payment = CashFlowValidations::validateFloat($payment);
 133              $presentValue = CashFlowValidations::validatePresentValue($presentValue);
 134              $futureValue = CashFlowValidations::validateFutureValue($futureValue);
 135              $type = CashFlowValidations::validatePeriodType($type);
 136          } catch (Exception $e) {
 137              return $e->getMessage();
 138          }
 139  
 140          // Validate parameters
 141          if ($payment == 0.0) {
 142              return ExcelError::NAN();
 143          }
 144  
 145          return self::calculatePeriods($rate, $payment, $presentValue, $futureValue, $type);
 146      }
 147  
 148      private static function calculateFutureValue(
 149          float $rate,
 150          int $numberOfPeriods,
 151          float $payment,
 152          float $presentValue,
 153          int $type
 154      ): float {
 155          if ($rate !== null && $rate != 0) {
 156              return -$presentValue *
 157                  (1 + $rate) ** $numberOfPeriods - $payment * (1 + $rate * $type) * ((1 + $rate) ** $numberOfPeriods - 1)
 158                      / $rate;
 159          }
 160  
 161          return -$presentValue - $payment * $numberOfPeriods;
 162      }
 163  
 164      private static function calculatePresentValue(
 165          float $rate,
 166          int $numberOfPeriods,
 167          float $payment,
 168          float $futureValue,
 169          int $type
 170      ): float {
 171          if ($rate != 0.0) {
 172              return (-$payment * (1 + $rate * $type)
 173                      * (((1 + $rate) ** $numberOfPeriods - 1) / $rate) - $futureValue) / (1 + $rate) ** $numberOfPeriods;
 174          }
 175  
 176          return -$futureValue - $payment * $numberOfPeriods;
 177      }
 178  
 179      /**
 180       * @return float|string
 181       */
 182      private static function calculatePeriods(
 183          float $rate,
 184          float $payment,
 185          float $presentValue,
 186          float $futureValue,
 187          int $type
 188      ) {
 189          if ($rate != 0.0) {
 190              if ($presentValue == 0.0) {
 191                  return ExcelError::NAN();
 192              }
 193  
 194              return log(($payment * (1 + $rate * $type) / $rate - $futureValue) /
 195                      ($presentValue + $payment * (1 + $rate * $type) / $rate)) / log(1 + $rate);
 196          }
 197  
 198          return (-$presentValue - $futureValue) / $payment;
 199      }
 200  }