[Summary view]

Differences Between: [Versions 400 and 401]

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