[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;
   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          $yearFrac = DateTimeExcel\YearFrac::fraction($purchased, $firstPeriod, $basis);
  74          if (is_string($yearFrac)) {
  75              return $yearFrac;
  76          }
  77  
  78          $amortiseCoeff = self::getAmortizationCoefficient($rate);
  79  
  80          $rate *= $amortiseCoeff;
  81          $fNRate = round($yearFrac * $rate * $cost, 0);
  82          $cost -= $fNRate;
  83          $fRest = $cost - $salvage;
  84  
  85          for ($n = 0; $n < $period; ++$n) {
  86              $fNRate = round($rate * $cost, 0);
  87              $fRest -= $fNRate;
  88  
  89              if ($fRest < 0.0) {
  90                  switch ($period - $n) {
  91                      case 0:
  92                      case 1:
  93                          return round($cost * 0.5, 0);
  94                      default:
  95                          return 0.0;
  96                  }
  97              }
  98              $cost -= $fNRate;
  99          }
 100  
 101          return $fNRate;
 102      }
 103  
 104      /**
 105       * AMORLINC.
 106       *
 107       * Returns the depreciation for each accounting period.
 108       * This function is provided for the French accounting system. If an asset is purchased in
 109       * the middle of the accounting period, the prorated depreciation is taken into account.
 110       *
 111       * Excel Function:
 112       *        AMORLINC(cost,purchased,firstPeriod,salvage,period,rate[,basis])
 113       *
 114       * @param mixed $cost The cost of the asset as a float
 115       * @param mixed $purchased Date of the purchase of the asset
 116       * @param mixed $firstPeriod Date of the end of the first period
 117       * @param mixed $salvage The salvage value at the end of the life of the asset
 118       * @param mixed $period The period as a float
 119       * @param mixed $rate Rate of depreciation as  float
 120       * @param mixed $basis Integer indicating the type of day count to use.
 121       *                             0 or omitted    US (NASD) 30/360
 122       *                             1               Actual/actual
 123       *                             2               Actual/360
 124       *                             3               Actual/365
 125       *                             4               European 30/360
 126       *
 127       * @return float|string (string containing the error type if there is an error)
 128       */
 129      public static function AMORLINC(
 130          $cost,
 131          $purchased,
 132          $firstPeriod,
 133          $salvage,
 134          $period,
 135          $rate,
 136          $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 137      ) {
 138          $cost = Functions::flattenSingleValue($cost);
 139          $purchased = Functions::flattenSingleValue($purchased);
 140          $firstPeriod = Functions::flattenSingleValue($firstPeriod);
 141          $salvage = Functions::flattenSingleValue($salvage);
 142          $period = Functions::flattenSingleValue($period);
 143          $rate = Functions::flattenSingleValue($rate);
 144          $basis = ($basis === null)
 145              ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
 146              : Functions::flattenSingleValue($basis);
 147  
 148          try {
 149              $cost = FinancialValidations::validateFloat($cost);
 150              $purchased = FinancialValidations::validateDate($purchased);
 151              $firstPeriod = FinancialValidations::validateDate($firstPeriod);
 152              $salvage = FinancialValidations::validateFloat($salvage);
 153              $period = FinancialValidations::validateFloat($period);
 154              $rate = FinancialValidations::validateFloat($rate);
 155              $basis = FinancialValidations::validateBasis($basis);
 156          } catch (Exception $e) {
 157              return $e->getMessage();
 158          }
 159  
 160          $fOneRate = $cost * $rate;
 161          $fCostDelta = $cost - $salvage;
 162          //    Note, quirky variation for leap years on the YEARFRAC for this function
 163          $purchasedYear = DateTimeExcel\DateParts::year($purchased);
 164          $yearFrac = DateTimeExcel\YearFrac::fraction($purchased, $firstPeriod, $basis);
 165          if (is_string($yearFrac)) {
 166              return $yearFrac;
 167          }
 168  
 169          if (
 170              ($basis == FinancialConstants::BASIS_DAYS_PER_YEAR_ACTUAL) &&
 171              ($yearFrac < 1) && (DateTimeExcel\Helpers::isLeapYear($purchasedYear))
 172          ) {
 173              $yearFrac *= 365 / 366;
 174          }
 175  
 176          $f0Rate = $yearFrac * $rate * $cost;
 177          $nNumOfFullPeriods = (int) (($cost - $salvage - $f0Rate) / $fOneRate);
 178  
 179          if ($period == 0) {
 180              return $f0Rate;
 181          } elseif ($period <= $nNumOfFullPeriods) {
 182              return $fOneRate;
 183          } elseif ($period == ($nNumOfFullPeriods + 1)) {
 184              return $fCostDelta - $fOneRate * $nNumOfFullPeriods - $f0Rate;
 185          }
 186  
 187          return 0.0;
 188      }
 189  
 190      private static function getAmortizationCoefficient(float $rate): float
 191      {
 192          //    The depreciation coefficients are:
 193          //    Life of assets (1/rate)        Depreciation coefficient
 194          //    Less than 3 years            1
 195          //    Between 3 and 4 years        1.5
 196          //    Between 5 and 6 years        2
 197          //    More than 6 years            2.5
 198          $fUsePer = 1.0 / $rate;
 199  
 200          if ($fUsePer < 3.0) {
 201              return 1.0;
 202          } elseif ($fUsePer < 4.0) {
 203              return 1.5;
 204          } elseif ($fUsePer <= 6.0) {
 205              return 2.0;
 206          }
 207  
 208          return 2.5;
 209      }
 210  }