[Summary view]

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

   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       * @return float|string
  93       */
  94      public static function schedulePayment($interestRate, $period, $numberOfPeriods, $principleRemaining)
  95      {
  96          $interestRate = Functions::flattenSingleValue($interestRate);
  97          $period = Functions::flattenSingleValue($period);
  98          $numberOfPeriods = Functions::flattenSingleValue($numberOfPeriods);
  99          $principleRemaining = Functions::flattenSingleValue($principleRemaining);
 100  
 101          try {
 102              $interestRate = CashFlowValidations::validateRate($interestRate);
 103              $period = CashFlowValidations::validateInt($period);
 104              $numberOfPeriods = CashFlowValidations::validateInt($numberOfPeriods);
 105              $principleRemaining = CashFlowValidations::validateFloat($principleRemaining);
 106          } catch (Exception $e) {
 107              return $e->getMessage();
 108          }
 109  
 110          // Validate parameters
 111          if ($period <= 0 || $period > $numberOfPeriods) {
 112              return ExcelError::NAN();
 113          }
 114  
 115          // Return value
 116          $returnValue = 0;
 117  
 118          // Calculate
 119          $principlePayment = ($principleRemaining * 1.0) / ($numberOfPeriods * 1.0);
 120          for ($i = 0; $i <= $period; ++$i) {
 121              $returnValue = $interestRate * $principleRemaining * -1;
 122              $principleRemaining -= $principlePayment;
 123              // principle needs to be 0 after the last payment, don't let floating point screw it up
 124              if ($i == $numberOfPeriods) {
 125                  $returnValue = 0.0;
 126              }
 127          }
 128  
 129          return $returnValue;
 130      }
 131  
 132      /**
 133       * RATE.
 134       *
 135       * Returns the interest rate per period of an annuity.
 136       * RATE is calculated by iteration and can have zero or more solutions.
 137       * If the successive results of RATE do not converge to within 0.0000001 after 20 iterations,
 138       * RATE returns the #NUM! error value.
 139       *
 140       * Excel Function:
 141       *        RATE(nper,pmt,pv[,fv[,type[,guess]]])
 142       *
 143       * @param mixed $numberOfPeriods The total number of payment periods in an annuity
 144       * @param mixed $payment The payment made each period and cannot change over the life of the annuity.
 145       *                           Typically, pmt includes principal and interest but no other fees or taxes.
 146       * @param mixed $presentValue The present value - the total amount that a series of future payments is worth now
 147       * @param mixed $futureValue The future value, or a cash balance you want to attain after the last payment is made.
 148       *                               If fv is omitted, it is assumed to be 0 (the future value of a loan,
 149       *                               for example, is 0).
 150       * @param mixed $type A number 0 or 1 and indicates when payments are due:
 151       *                      0 or omitted    At the end of the period.
 152       *                      1               At the beginning of the period.
 153       * @param mixed $guess Your guess for what the rate will be.
 154       *                          If you omit guess, it is assumed to be 10 percent.
 155       *
 156       * @return float|string
 157       */
 158      public static function rate(
 159          $numberOfPeriods,
 160          $payment,
 161          $presentValue,
 162          $futureValue = 0.0,
 163          $type = FinancialConstants::PAYMENT_END_OF_PERIOD,
 164          $guess = 0.1
 165      ) {
 166          $numberOfPeriods = Functions::flattenSingleValue($numberOfPeriods);
 167          $payment = Functions::flattenSingleValue($payment);
 168          $presentValue = Functions::flattenSingleValue($presentValue);
 169          $futureValue = ($futureValue === null) ? 0.0 : Functions::flattenSingleValue($futureValue);
 170          $type = ($type === null) ? FinancialConstants::PAYMENT_END_OF_PERIOD : Functions::flattenSingleValue($type);
 171          $guess = ($guess === null) ? 0.1 : Functions::flattenSingleValue($guess);
 172  
 173          try {
 174              $numberOfPeriods = CashFlowValidations::validateInt($numberOfPeriods);
 175              $payment = CashFlowValidations::validateFloat($payment);
 176              $presentValue = CashFlowValidations::validatePresentValue($presentValue);
 177              $futureValue = CashFlowValidations::validateFutureValue($futureValue);
 178              $type = CashFlowValidations::validatePeriodType($type);
 179              $guess = CashFlowValidations::validateFloat($guess);
 180          } catch (Exception $e) {
 181              return $e->getMessage();
 182          }
 183  
 184          $rate = $guess;
 185          // rest of code adapted from python/numpy
 186          $close = false;
 187          $iter = 0;
 188          while (!$close && $iter < self::FINANCIAL_MAX_ITERATIONS) {
 189              $nextdiff = self::rateNextGuess($rate, $numberOfPeriods, $payment, $presentValue, $futureValue, $type);
 190              if (!is_numeric($nextdiff)) {
 191                  break;
 192              }
 193              $rate1 = $rate - $nextdiff;
 194              $close = abs($rate1 - $rate) < self::FINANCIAL_PRECISION;
 195              ++$iter;
 196              $rate = $rate1;
 197          }
 198  
 199          return $close ? $rate : ExcelError::NAN();
 200      }
 201  
 202      /** @return float|string */
 203      private static function rateNextGuess(float $rate, int $numberOfPeriods, float $payment, float $presentValue, float $futureValue, int $type)
 204      {
 205          if ($rate == 0.0) {
 206              return ExcelError::NAN();
 207          }
 208          $tt1 = ($rate + 1) ** $numberOfPeriods;
 209          $tt2 = ($rate + 1) ** ($numberOfPeriods - 1);
 210          $numerator = $futureValue + $tt1 * $presentValue + $payment * ($tt1 - 1) * ($rate * $type + 1) / $rate;
 211          $denominator = $numberOfPeriods * $tt2 * $presentValue - $payment * ($tt1 - 1)
 212              * ($rate * $type + 1) / ($rate * $rate) + $numberOfPeriods
 213              * $payment * $tt2 * ($rate * $type + 1) / $rate + $payment * ($tt1 - 1) * $type / $rate;
 214          if ($denominator == 0) {
 215              return ExcelError::NAN();
 216          }
 217  
 218          return $numerator / $denominator;
 219      }
 220  }