[Summary view]

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

   1  <?php
   2  
   3  namespace PhpOffice\PhpSpreadsheet\Calculation\Financial;
   4  
   5  use PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
   6  use PhpOffice\PhpSpreadsheet\Calculation\Exception;
   7  use PhpOffice\PhpSpreadsheet\Calculation\Financial\Constants as FinancialConstants;
   8  use PhpOffice\PhpSpreadsheet\Calculation\Functions;
   9  
  10  class Amortization
  11  {
  12      /**
  13       * AMORDEGRC.
  14       *
  15       * Returns the depreciation for each accounting period.
  16       * This function is provided for the French accounting system. If an asset is purchased in
  17       * the middle of the accounting period, the prorated depreciation is taken into account.
  18       * The function is similar to AMORLINC, except that a depreciation coefficient is applied in
  19       * the calculation depending on the life of the assets.
  20       * This function will return the depreciation until the last period of the life of the assets
  21       * or until the cumulated value of depreciation is greater than the cost of the assets minus
  22       * the salvage value.
  23       *
  24       * Excel Function:
  25       *        AMORDEGRC(cost,purchased,firstPeriod,salvage,period,rate[,basis])
  26       *
  27       * @param mixed $cost The float cost of the asset
  28       * @param mixed $purchased Date of the purchase of the asset
  29       * @param mixed $firstPeriod Date of the end of the first period
  30       * @param mixed $salvage The salvage value at the end of the life of the asset
  31       * @param mixed $period the period (float)
  32       * @param mixed $rate rate of depreciation (float)
  33       * @param mixed $basis The type of day count to use (int).
  34       *                         0 or omitted    US (NASD) 30/360
  35       *                         1               Actual/actual
  36       *                         2               Actual/360
  37       *                         3               Actual/365
  38       *                         4               European 30/360
  39       *
  40       * @return float|string (string containing the error type if there is an error)
  41       */
  42      public static function AMORDEGRC(
  43          $cost,
  44          $purchased,
  45          $firstPeriod,
  46          $salvage,
  47          $period,
  48          $rate,
  49          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
  50      ) {
  51          $cost = Functions::flattenSingleValue($cost);
  52          $purchased = Functions::flattenSingleValue($purchased);
  53          $firstPeriod = Functions::flattenSingleValue($firstPeriod);
  54          $salvage = Functions::flattenSingleValue($salvage);
  55          $period = Functions::flattenSingleValue($period);
  56          $rate = Functions::flattenSingleValue($rate);
  57          $basis = ($basis === null)
  58              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
  59              : Functions::flattenSingleValue($basis);
  60  
  61          try {
  62              $cost = FinancialValidations::validateFloat($cost);
  63              $purchased = FinancialValidations::validateDate($purchased);
  64              $firstPeriod = FinancialValidations::validateDate($firstPeriod);
  65              $salvage = FinancialValidations::validateFloat($salvage);
  66              $period = FinancialValidations::validateInt($period);
  67              $rate = FinancialValidations::validateFloat($rate);
  68              $basis = FinancialValidations::validateBasis($basis);
  69          } catch (Exception $e) {
  70              return $e->getMessage();
  71          }
  72  
  73          $yearFracx = DateTimeExcel\YearFrac::fraction($purchased, $firstPeriod, $basis);
  74          if (is_string($yearFracx)) {
  75              return $yearFracx;
  76          }
  77          /** @var float */
  78          $yearFrac = $yearFracx;
  79  
  80          $amortiseCoeff = self::getAmortizationCoefficient($rate);
  81  
  82          $rate *= $amortiseCoeff;
  83          $fNRate = round($yearFrac * $rate * $cost, 0);
  84          $cost -= $fNRate;
  85          $fRest = $cost - $salvage;
  86  
  87          for ($n = 0; $n < $period; ++$n) {
  88              $fNRate = round($rate * $cost, 0);
  89              $fRest -= $fNRate;
  90  
  91              if ($fRest < 0.0) {
  92                  switch ($period - $n) {
  93                      case 0:
  94                      case 1:
  95                          return round($cost * 0.5, 0);
  96                      default:
  97                          return 0.0;
  98                  }
  99              }
 100              $cost -= $fNRate;
 101          }
 102  
 103          return $fNRate;
 104      }
 105  
 106      /**
 107       * AMORLINC.
 108       *
 109       * Returns the depreciation for each accounting period.
 110       * This function is provided for the French accounting system. If an asset is purchased in
 111       * the middle of the accounting period, the prorated depreciation is taken into account.
 112       *
 113       * Excel Function:
 114       *        AMORLINC(cost,purchased,firstPeriod,salvage,period,rate[,basis])
 115       *
 116       * @param mixed $cost The cost of the asset as a float
 117       * @param mixed $purchased Date of the purchase of the asset
 118       * @param mixed $firstPeriod Date of the end of the first period
 119       * @param mixed $salvage The salvage value at the end of the life of the asset
 120       * @param mixed $period The period as a float
 121       * @param mixed $rate Rate of depreciation as  float
 122       * @param mixed $basis Integer indicating the type of day count to use.
 123       *                             0 or omitted    US (NASD) 30/360
 124       *                             1               Actual/actual
 125       *                             2               Actual/360
 126       *                             3               Actual/365
 127       *                             4               European 30/360
 128       *
 129       * @return float|string (string containing the error type if there is an error)
 130       */
 131      public static function AMORLINC(
 132          $cost,
 133          $purchased,
 134          $firstPeriod,
 135          $salvage,
 136          $period,
 137          $rate,
 138          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 139      ) {
 140          $cost = Functions::flattenSingleValue($cost);
 141          $purchased = Functions::flattenSingleValue($purchased);
 142          $firstPeriod = Functions::flattenSingleValue($firstPeriod);
 143          $salvage = Functions::flattenSingleValue($salvage);
 144          $period = Functions::flattenSingleValue($period);
 145          $rate = Functions::flattenSingleValue($rate);
 146          $basis = ($basis === null)
 147              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 148              : Functions::flattenSingleValue($basis);
 149  
 150          try {
 151              $cost = FinancialValidations::validateFloat($cost);
 152              $purchased = FinancialValidations::validateDate($purchased);
 153              $firstPeriod = FinancialValidations::validateDate($firstPeriod);
 154              $salvage = FinancialValidations::validateFloat($salvage);
 155              $period = FinancialValidations::validateFloat($period);
 156              $rate = FinancialValidations::validateFloat($rate);
 157              $basis = FinancialValidations::validateBasis($basis);
 158          } catch (Exception $e) {
 159              return $e->getMessage();
 160          }
 161  
 162          $fOneRate = $cost * $rate;
 163          $fCostDelta = $cost - $salvage;
 164          //    Note, quirky variation for leap years on the YEARFRAC for this function
 165          $purchasedYear = DateTimeExcel\DateParts::year($purchased);
 166          $yearFracx = DateTimeExcel\YearFrac::fraction($purchased, $firstPeriod, $basis);
 167          if (is_string($yearFracx)) {
 168              return $yearFracx;
 169          }
 170          /** @var float */
 171          $yearFrac = $yearFracx;
 172  
 173          if (
 174              $basis == FinancialConstants::BASIS_DAYS_PER_YEAR_ACTUAL
 175              && $yearFrac < 1
 176              && DateTimeExcel\Helpers::isLeapYear(Functions::scalar($purchasedYear))
 177          ) {
 178              $yearFrac *= 365 / 366;
 179          }
 180  
 181          $f0Rate = $yearFrac * $rate * $cost;
 182          $nNumOfFullPeriods = (int) (($cost - $salvage - $f0Rate) / $fOneRate);
 183  
 184          if ($period == 0) {
 185              return $f0Rate;
 186          } elseif ($period <= $nNumOfFullPeriods) {
 187              return $fOneRate;
 188          } elseif ($period == ($nNumOfFullPeriods + 1)) {
 189              return $fCostDelta - $fOneRate * $nNumOfFullPeriods - $f0Rate;
 190          }
 191  
 192          return 0.0;
 193      }
 194  
 195      private static function getAmortizationCoefficient(float $rate): float
 196      {
 197          //    The depreciation coefficients are:
 198          //    Life of assets (1/rate)        Depreciation coefficient
 199          //    Less than 3 years            1
 200          //    Between 3 and 4 years        1.5
 201          //    Between 5 and 6 years        2
 202          //    More than 6 years            2.5
 203          $fUsePer = 1.0 / $rate;
 204  
 205          if ($fUsePer < 3.0) {
 206              return 1.0;
 207          } elseif ($fUsePer < 4.0) {
 208              return 1.5;
 209          } elseif ($fUsePer <= 6.0) {
 210              return 2.0;
 211          }
 212  
 213          return 2.5;
 214      }
 215  }