[Summary view]

Differences Between: [Versions 400 and 401] [Versions 400 and 402] [Versions 400 and 403]

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\Financial\CashFlow\Constant\Periodic;
   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  
  10  class Cumulative
  11  {
  12      /**
  13       * CUMIPMT.
  14       *
  15       * Returns the cumulative interest paid on a loan between the start and end periods.
  16       *
  17       * Excel Function:
  18       *        CUMIPMT(rate,nper,pv,start,end[,type])
  19       *
  20       * @param mixed $rate The Interest rate
  21       * @param mixed $periods The total number of payment periods
  22       * @param mixed $presentValue Present Value
  23       * @param mixed $start The first period in the calculation.
  24       *                       Payment periods are numbered beginning with 1.
  25       * @param mixed $end the last period in the calculation
  26       * @param mixed $type A number 0 or 1 and indicates when payments are due:
  27       *                    0 or omitted    At the end of the period.
  28       *                    1               At the beginning of the period.
  29       *
  30       * @return float|string
  31       */
  32      public static function interest(
  33          $rate,
  34          $periods,
  35          $presentValue,
  36          $start,
  37          $end,
  38          $type = FinancialConstants::PAYMENT_END_OF_PERIOD
  39      ) {
  40          $rate = Functions::flattenSingleValue($rate);
  41          $periods = Functions::flattenSingleValue($periods);
  42          $presentValue = Functions::flattenSingleValue($presentValue);
  43          $start = Functions::flattenSingleValue($start);
  44          $end = Functions::flattenSingleValue($end);
  45          $type = ($type === null) ? FinancialConstants::PAYMENT_END_OF_PERIOD : Functions::flattenSingleValue($type);
  46  
  47          try {
  48              $rate = CashFlowValidations::validateRate($rate);
  49              $periods = CashFlowValidations::validateInt($periods);
  50              $presentValue = CashFlowValidations::validatePresentValue($presentValue);
  51              $start = CashFlowValidations::validateInt($start);
  52              $end = CashFlowValidations::validateInt($end);
  53              $type = CashFlowValidations::validatePeriodType($type);
  54          } catch (Exception $e) {
  55              return $e->getMessage();
  56          }
  57  
  58          // Validate parameters
  59          if ($start < 1 || $start > $end) {
  60              return Functions::NAN();
  61          }
  62  
  63          // Calculate
  64          $interest = 0;
  65          for ($per = $start; $per <= $end; ++$per) {
  66              $ipmt = Interest::payment($rate, $per, $periods, $presentValue, 0, $type);
  67              if (is_string($ipmt)) {
  68                  return $ipmt;
  69              }
  70  
  71              $interest += $ipmt;
  72          }
  73  
  74          return $interest;
  75      }
  76  
  77      /**
  78       * CUMPRINC.
  79       *
  80       * Returns the cumulative principal paid on a loan between the start and end periods.
  81       *
  82       * Excel Function:
  83       *        CUMPRINC(rate,nper,pv,start,end[,type])
  84       *
  85       * @param mixed $rate The Interest rate
  86       * @param mixed $periods The total number of payment periods as an integer
  87       * @param mixed $presentValue Present Value
  88       * @param mixed $start The first period in the calculation.
  89       *                       Payment periods are numbered beginning with 1.
  90       * @param mixed $end the last period in the calculation
  91       * @param mixed $type A number 0 or 1 and indicates when payments are due:
  92       *                    0 or omitted    At the end of the period.
  93       *                    1               At the beginning of the period.
  94       *
  95       * @return float|string
  96       */
  97      public static function principal(
  98          $rate,
  99          $periods,
 100          $presentValue,
 101          $start,
 102          $end,
 103          $type = FinancialConstants::PAYMENT_END_OF_PERIOD
 104      ) {
 105          $rate = Functions::flattenSingleValue($rate);
 106          $periods = Functions::flattenSingleValue($periods);
 107          $presentValue = Functions::flattenSingleValue($presentValue);
 108          $start = Functions::flattenSingleValue($start);
 109          $end = Functions::flattenSingleValue($end);
 110          $type = ($type === null) ? FinancialConstants::PAYMENT_END_OF_PERIOD : Functions::flattenSingleValue($type);
 111  
 112          try {
 113              $rate = CashFlowValidations::validateRate($rate);
 114              $periods = CashFlowValidations::validateInt($periods);
 115              $presentValue = CashFlowValidations::validatePresentValue($presentValue);
 116              $start = CashFlowValidations::validateInt($start);
 117              $end = CashFlowValidations::validateInt($end);
 118              $type = CashFlowValidations::validatePeriodType($type);
 119          } catch (Exception $e) {
 120              return $e->getMessage();
 121          }
 122  
 123          // Validate parameters
 124          if ($start < 1 || $start > $end) {
 125              return Functions::VALUE();
 126          }
 127  
 128          // Calculate
 129          $principal = 0;
 130          for ($per = $start; $per <= $end; ++$per) {
 131              $ppmt = Payments::interestPayment($rate, $per, $periods, $presentValue, 0, $type);
 132              if (is_string($ppmt)) {
 133                  return $ppmt;
 134              }
 135  
 136              $principal += $ppmt;
 137          }
 138  
 139          return $principal;
 140      }
 141  }