tablepress / libraries / vendor / PhpSpreadsheet / Calculation / Financial / Amortization.php

Amortization.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Calculation/Financial/Amortization.php

216 lines 7.5 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\Financial;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Exception;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Financial\Constants as FinancialConstants;
8	use TablePress\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 = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;
58
59	try {
60	$cost = FinancialValidations::validateFloat($cost);
61	$purchased = FinancialValidations::validateDate($purchased);
62	$firstPeriod = FinancialValidations::validateDate($firstPeriod);
63	$salvage = FinancialValidations::validateFloat($salvage);
64	$period = FinancialValidations::validateInt($period);
65	$rate = FinancialValidations::validateFloat($rate);
66	$basis = FinancialValidations::validateBasis($basis);
67	} catch (Exception $e) {
68	return $e->getMessage();
69	}
70
71	$yearFracx = DateTimeExcel\YearFrac::fraction($purchased, $firstPeriod, $basis);
72	if (is_string($yearFracx)) {
73	return $yearFracx;
74	}
75	/** @var float $yearFrac */
76	$yearFrac = $yearFracx;
77
78	$amortiseCoeff = self::getAmortizationCoefficient($rate);
79
80	$rate *= $amortiseCoeff;
81	$rate = (float) (string) $rate; // ugly way to avoid rounding problem
82	$fNRate = round($yearFrac * $rate * $cost, 0);
83	$cost -= $fNRate;
84	$fRest = $cost - $salvage;
85
86	for ($n = 0; $n < $period; ++$n) {
87	$fNRate = round($rate * $cost, 0);
88	$fRest -= $fNRate;
89
90	if ($fRest < 0.0) {
91	switch ($period - $n) {
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 = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;
145
146	try {
147	$cost = FinancialValidations::validateFloat($cost);
148	$purchased = FinancialValidations::validateDate($purchased);
149	$firstPeriod = FinancialValidations::validateDate($firstPeriod);
150	$salvage = FinancialValidations::validateFloat($salvage);
151	$period = FinancialValidations::validateFloat($period);
152	$rate = FinancialValidations::validateFloat($rate);
153	$basis = FinancialValidations::validateBasis($basis);
154	} catch (Exception $e) {
155	return $e->getMessage();
156	}
157
158	$fOneRate = $cost * $rate;
159	$fCostDelta = $cost - $salvage;
160	// Note, quirky variation for leap years on the YEARFRAC for this function
161	$purchasedYear = DateTimeExcel\DateParts::year($purchased);
162	$yearFracx = DateTimeExcel\YearFrac::fraction($purchased, $firstPeriod, $basis);
163	if (is_string($yearFracx)) {
164	return $yearFracx;
165	}
166	/** @var float $yearFrac */
167	$yearFrac = $yearFracx;
168
169	if (
170	$basis == FinancialConstants::BASIS_DAYS_PER_YEAR_ACTUAL
171	&& $yearFrac < 1
172	) {
173	$temp = Functions::scalar($purchasedYear);
174	if (is_int($temp) \|\| is_string($temp)) {
175	if (DateTimeExcel\Helpers::isLeapYear($temp)) {
176	$yearFrac *= 365 / 366;
177	}
178	}
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	}
216