[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\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 Interest
  12  {
  13      private const FINANCIAL_MAX_ITERATIONS = 128;
  14  
  15      private const FINANCIAL_PRECISION = 1.0e-08;
  16  
  17      /**
  18       * IPMT.
  19       *
  20       * Returns the interest payment for a given period for an investment based on periodic, constant payments
  21       *         and a constant interest rate.
  22       *
  23       * Excel Function:
  24       *        IPMT(rate,per,nper,pv[,fv][,type])
  25       *
  26       * @param mixed $interestRate Interest rate per period
  27       * @param mixed $period Period for which we want to find the interest
  28       * @param mixed $numberOfPeriods Number of periods
  29       * @param mixed $presentValue Present Value
  30       * @param mixed $futureValue Future Value
  31       * @param mixed $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  32       *
  33       * @return float|string
  34       */
  35      public static function payment(
  36          $interestRate,
  37          $period,
  38          $numberOfPeriods,
  39          $presentValue,
  40          $futureValue = 0,
  41          $type = FinancialConstants::PAYMENT_END_OF_PERIOD
  42      ) {
  43          $interestRate = Functions::flattenSingleValue($interestRate);
  44          $period = Functions::flattenSingleValue($period);
  45          $numberOfPeriods = Functions::flattenSingleValue($numberOfPeriods);
  46          $presentValue = Functions::flattenSingleValue($presentValue);
  47          $futureValue = ($futureValue === null) ? 0.0 : Functions::flattenSingleValue($futureValue);
  48          $type = ($type === null) ? FinancialConstants::PAYMENT_END_OF_PERIOD : Functions::flattenSingleValue($type);
  49  
  50          try {
  51              $interestRate = CashFlowValidations::validateRate($interestRate);
  52              $period = CashFlowValidations::validateInt($period);
  53              $numberOfPeriods = CashFlowValidations::validateInt($numberOfPeriods);
  54              $presentValue = CashFlowValidations::validatePresentValue($presentValue);
  55              $futureValue = CashFlowValidations::validateFutureValue($futureValue);
  56              $type = CashFlowValidations::validatePeriodType($type);
  57          } catch (Exception $e) {
  58              return $e->getMessage();
  59          }
  60  
  61          // Validate parameters
  62          if ($period <= 0 || $period > $numberOfPeriods) {
  63              return ExcelError::NAN();
  64          }
  65  
  66          // Calculate
  67          $interestAndPrincipal = new InterestAndPrincipal(
  68              $interestRate,
  69              $period,
  70              $numberOfPeriods,
  71              $presentValue,
  72              $futureValue,
  73              $type
  74          );
  75  
  76          return $interestAndPrincipal->interest();
  77      }
  78  
  79      /**
  80       * ISPMT.
  81       *
  82       * Returns the interest payment for an investment based on an interest rate and a constant payment schedule.
  83       *
  84       * Excel Function:
  85       *     =ISPMT(interest_rate, period, number_payments, pv)
  86       *
  87       * @param mixed $interestRate is the interest rate for the investment
  88       * @param mixed $period is the period to calculate the interest rate.  It must be betweeen 1 and number_payments.
  89       * @param mixed $numberOfPeriods is the number of payments for the annuity
  90       * @param mixed $principleRemaining is the loan amount or present value of the payments
  91       */
  92      public static function schedulePayment($interestRate, $period, $numberOfPeriods, $principleRemaining)
  93      {
  94          $interestRate = Functions::flattenSingleValue($interestRate);
  95          $period = Functions::flattenSingleValue($period);
  96          $numberOfPeriods = Functions::flattenSingleValue($numberOfPeriods);
  97          $principleRemaining = Functions::flattenSingleValue($principleRemaining);
  98  
  99          try {
 100              $interestRate = CashFlowValidations::validateRate($interestRate);
 101              $period = CashFlowValidations::validateInt($period);
 102              $numberOfPeriods = CashFlowValidations::validateInt($numberOfPeriods);
 103              $principleRemaining = CashFlowValidations::validateFloat($principleRemaining);
 104          } catch (Exception $e) {
 105              return $e->getMessage();
 106          }
 107  
 108          // Validate parameters
 109          if ($period <= 0 || $period > $numberOfPeriods) {
 110              return ExcelError::NAN();
 111          }
 112  
 113          // Return value
 114          $returnValue = 0;
 115  
 116          // Calculate
 117          $principlePayment = ($principleRemaining * 1.0) / ($numberOfPeriods * 1.0);
 118          for ($i = 0; $i <= $period; ++$i) {
 119              $returnValue = $interestRate * $principleRemaining * -1;
 120              $principleRemaining -= $principlePayment;
 121              // principle needs to be 0 after the last payment, don't let floating point screw it up
 122              if ($i == $numberOfPeriods) {
 123                  $returnValue = 0.0;
 124              }
 125          }
 126  
 127          return $returnValue;
 128      }
 129  
 130      /**
 131       * RATE.
 132       *
 133       * Returns the interest rate per period of an annuity.
 134       * RATE is calculated by iteration and can have zero or more solutions.
 135       * If the successive results of RATE do not converge to within 0.0000001 after 20 iterations,
 136       * RATE returns the #NUM! error value.
 137       *
 138       * Excel Function:
 139       *        RATE(nper,pmt,pv[,fv[,type[,guess]]])
 140       *
 141       * @param mixed $numberOfPeriods The total number of payment periods in an annuity
 142       * @param mixed $payment The payment made each period and cannot change over the life of the annuity.
 143       *                           Typically, pmt includes principal and interest but no other fees or taxes.
 144       * @param mixed $presentValue The present value - the total amount that a series of future payments is worth now
 145       * @param mixed $futureValue The future value, or a cash balance you want to attain after the last payment is made.
 146       *                               If fv is omitted, it is assumed to be 0 (the future value of a loan,
 147       *                               for example, is 0).
 148       * @param mixed $type A number 0 or 1 and indicates when payments are due:
 149       *                      0 or omitted    At the end of the period.
 150       *                      1               At the beginning of the period.
 151       * @param mixed $guess Your guess for what the rate will be.
 152       *                          If you omit guess, it is assumed to be 10 percent.
 153       *
 154       * @return float|string
 155       */
 156      public static function rate(
 157          $numberOfPeriods,
 158          $payment,
 159          $presentValue,
 160          $futureValue = 0.0,
 161          $type = FinancialConstants::PAYMENT_END_OF_PERIOD,
 162          $guess = 0.1
 163      ) {
 164          $numberOfPeriods = Functions::flattenSingleValue($numberOfPeriods);
 165          $payment = Functions::flattenSingleValue($payment);
 166          $presentValue = Functions::flattenSingleValue($presentValue);
 167          $futureValue = ($futureValue === null) ? 0.0 : Functions::flattenSingleValue($futureValue);
 168          $type = ($type === null) ? FinancialConstants::PAYMENT_END_OF_PERIOD : Functions::flattenSingleValue($type);
 169          $guess = ($guess === null) ? 0.1 : Functions::flattenSingleValue($guess);
 170  
 171          try {
 172              $numberOfPeriods = CashFlowValidations::validateInt($numberOfPeriods);
 173              $payment = CashFlowValidations::validateFloat($payment);
 174              $presentValue = CashFlowValidations::validatePresentValue($presentValue);
 175              $futureValue = CashFlowValidations::validateFutureValue($futureValue);
 176              $type = CashFlowValidations::validatePeriodType($type);
 177              $guess = CashFlowValidations::validateFloat($guess);
 178          } catch (Exception $e) {
 179              return $e->getMessage();
 180          }
 181  
 182          $rate = $guess;
 183          // rest of code adapted from python/numpy
 184          $close = false;
 185          $iter = 0;
 186          while (!$close && $iter < self::FINANCIAL_MAX_ITERATIONS) {
 187              $nextdiff = self::rateNextGuess($rate, $numberOfPeriods, $payment, $presentValue, $futureValue, $type);
 188              if (!is_numeric($nextdiff)) {
 189                  break;
 190              }
 191              $rate1 = $rate - $nextdiff;
 192              $close = abs($rate1 - $rate) < self::FINANCIAL_PRECISION;
 193              ++$iter;
 194              $rate = $rate1;
 195          }
 196  
 197          return $close ? $rate : ExcelError::NAN();
 198      }
 199  
 200      private static function rateNextGuess($rate, $numberOfPeriods, $payment, $presentValue, $futureValue, $type)
 201      {
 202          if ($rate == 0.0) {
 203              return ExcelError::NAN();
 204          }
 205          $tt1 = ($rate + 1) ** $numberOfPeriods;
 206          $tt2 = ($rate + 1) ** ($numberOfPeriods - 1);
 207          $numerator = $futureValue + $tt1 * $presentValue + $payment * ($tt1 - 1) * ($rate * $type + 1) / $rate;
 208          $denominator = $numberOfPeriods * $tt2 * $presentValue - $payment * ($tt1 - 1)
 209              * ($rate * $type + 1) / ($rate * $rate) + $numberOfPeriods
 210              * $payment * $tt2 * ($rate * $type + 1) / $rate + $payment * ($tt1 - 1) * $type / $rate;
 211          if ($denominator == 0) {
 212              return ExcelError::NAN();
 213          }
 214  
 215          return $numerator / $denominator;
 216      }
 217  }