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

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

400 lines 16.3 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\Financial;
4
5	use DateTime;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Exception;
8	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Financial\Constants as FinancialConstants;
9	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
10	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
11	use TablePress\PhpOffice\PhpSpreadsheet\Shared\Date;
12
13	class Coupons
14	{
15	private const PERIOD_DATE_PREVIOUS = false;
16	private const PERIOD_DATE_NEXT = true;
17
18	/**
19	* COUPDAYBS.
20	*
21	* Returns the number of days from the beginning of the coupon period to the settlement date.
22	*
23	* Excel Function:
24	* COUPDAYBS(settlement,maturity,frequency[,basis])
25	*
26	* @param mixed $settlement The security's settlement date.
27	* The security settlement date is the date after the issue
28	* date when the security is traded to the buyer.
29	* @param mixed $maturity The security's maturity date.
30	* The maturity date is the date when the security expires.
31	* @param mixed $frequency The number of coupon payments per year (int).
32	* Valid frequency values are:
33	* 1 Annual
34	* 2 Semi-Annual
35	* 4 Quarterly
36	* @param mixed $basis The type of day count to use (int).
37	* 0 or omitted US (NASD) 30/360
38	* 1 Actual/actual
39	* 2 Actual/360
40	* 3 Actual/365
41	* 4 European 30/360
42	* @return string\|int\|float
43	*/
44	public static function COUPDAYBS(
45	$settlement,
46	$maturity,
47	$frequency,
48	$basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
49	) {
50	$settlement = Functions::flattenSingleValue($settlement);
51	$maturity = Functions::flattenSingleValue($maturity);
52	$frequency = Functions::flattenSingleValue($frequency);
53	$basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;
54
55	try {
56	$settlement = FinancialValidations::validateSettlementDate($settlement);
57	$maturity = FinancialValidations::validateMaturityDate($maturity);
58	self::validateCouponPeriod($settlement, $maturity);
59	$frequency = FinancialValidations::validateFrequency($frequency);
60	$basis = FinancialValidations::validateBasis($basis);
61	} catch (Exception $e) {
62	return $e->getMessage();
63	}
64
65	$daysPerYear = Helpers::daysPerYear(Functions::scalar(DateTimeExcel\DateParts::year($settlement)), $basis);
66	if (is_string($daysPerYear)) {
67	return ExcelError::VALUE();
68	}
69	$prev = self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_PREVIOUS);
70
71	if ($basis === FinancialConstants::BASIS_DAYS_PER_YEAR_ACTUAL) {
72	return abs((float) DateTimeExcel\Days::between($prev, $settlement));
73	}
74
75	return (float) DateTimeExcel\YearFrac::fraction($prev, $settlement, $basis) * $daysPerYear;
76	}
77
78	/**
79	* COUPDAYS.
80	*
81	* Returns the number of days in the coupon period that contains the settlement date.
82	*
83	* Excel Function:
84	* COUPDAYS(settlement,maturity,frequency[,basis])
85	*
86	* @param mixed $settlement The security's settlement date.
87	* The security settlement date is the date after the issue
88	* date when the security is traded to the buyer.
89	* @param mixed $maturity The security's maturity date.
90	* The maturity date is the date when the security expires.
91	* @param mixed $frequency The number of coupon payments per year.
92	* Valid frequency values are:
93	* 1 Annual
94	* 2 Semi-Annual
95	* 4 Quarterly
96	* @param mixed $basis The type of day count to use (int).
97	* 0 or omitted US (NASD) 30/360
98	* 1 Actual/actual
99	* 2 Actual/360
100	* 3 Actual/365
101	* 4 European 30/360
102	* @return string\|int\|float
103	*/
104	public static function COUPDAYS(
105	$settlement,
106	$maturity,
107	$frequency,
108	$basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
109	) {
110	$settlement = Functions::flattenSingleValue($settlement);
111	$maturity = Functions::flattenSingleValue($maturity);
112	$frequency = Functions::flattenSingleValue($frequency);
113	$basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;
114
115	try {
116	$settlement = FinancialValidations::validateSettlementDate($settlement);
117	$maturity = FinancialValidations::validateMaturityDate($maturity);
118	self::validateCouponPeriod($settlement, $maturity);
119	$frequency = FinancialValidations::validateFrequency($frequency);
120	$basis = FinancialValidations::validateBasis($basis);
121	} catch (Exception $e) {
122	return $e->getMessage();
123	}
124
125	switch ($basis) {
126	case FinancialConstants::BASIS_DAYS_PER_YEAR_365:
127	// Actual/365
128	return 365 / $frequency;
129	case FinancialConstants::BASIS_DAYS_PER_YEAR_ACTUAL:
130	// Actual/actual
131	if ($frequency == FinancialConstants::FREQUENCY_ANNUAL) {
132	$daysPerYear = (int) Helpers::daysPerYear(Functions::scalar(DateTimeExcel\DateParts::year($settlement)), $basis);
133
134	return $daysPerYear / $frequency;
135	}
136	$prev = self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_PREVIOUS);
137	$next = self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_NEXT);
138
139	return $next - $prev;
140	default:
141	// US (NASD) 30/360, Actual/360 or European 30/360
142	return 360 / $frequency;
143	}
144	}
145
146	/**
147	* COUPDAYSNC.
148	*
149	* Returns the number of days from the settlement date to the next coupon date.
150	*
151	* Excel Function:
152	* COUPDAYSNC(settlement,maturity,frequency[,basis])
153	*
154	* @param mixed $settlement The security's settlement date.
155	* The security settlement date is the date after the issue
156	* date when the security is traded to the buyer.
157	* @param mixed $maturity The security's maturity date.
158	* The maturity date is the date when the security expires.
159	* @param mixed $frequency The number of coupon payments per year.
160	* Valid frequency values are:
161	* 1 Annual
162	* 2 Semi-Annual
163	* 4 Quarterly
164	* @param mixed $basis The type of day count to use (int) .
165	* 0 or omitted US (NASD) 30/360
166	* 1 Actual/actual
167	* 2 Actual/360
168	* 3 Actual/365
169	* 4 European 30/360
170	* @return string\|float
171	*/
172	public static function COUPDAYSNC(
173	$settlement,
174	$maturity,
175	$frequency,
176	$basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
177	) {
178	$settlement = Functions::flattenSingleValue($settlement);
179	$maturity = Functions::flattenSingleValue($maturity);
180	$frequency = Functions::flattenSingleValue($frequency);
181	$basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;
182
183	try {
184	$settlement = FinancialValidations::validateSettlementDate($settlement);
185	$maturity = FinancialValidations::validateMaturityDate($maturity);
186	self::validateCouponPeriod($settlement, $maturity);
187	$frequency = FinancialValidations::validateFrequency($frequency);
188	$basis = FinancialValidations::validateBasis($basis);
189	} catch (Exception $e) {
190	return $e->getMessage();
191	}
192
193	/** @var int $daysPerYear */
194	$daysPerYear = Helpers::daysPerYear(Functions::Scalar(DateTimeExcel\DateParts::year($settlement)), $basis);
195	$next = self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_NEXT);
196
197	if ($basis === FinancialConstants::BASIS_DAYS_PER_YEAR_NASD) {
198	$settlementDate = Date::excelToDateTimeObject($settlement);
199	$settlementEoM = Helpers::isLastDayOfMonth($settlementDate);
200	if ($settlementEoM) {
201	++$settlement;
202	}
203	}
204
205	return (float) DateTimeExcel\YearFrac::fraction($settlement, $next, $basis) * $daysPerYear;
206	}
207
208	/**
209	* COUPNCD.
210	*
211	* Returns the next coupon date after the settlement date.
212	*
213	* Excel Function:
214	* COUPNCD(settlement,maturity,frequency[,basis])
215	*
216	* @param mixed $settlement The security's settlement date.
217	* The security settlement date is the date after the issue
218	* date when the security is traded to the buyer.
219	* @param mixed $maturity The security's maturity date.
220	* The maturity date is the date when the security expires.
221	* @param mixed $frequency The number of coupon payments per year.
222	* Valid frequency values are:
223	* 1 Annual
224	* 2 Semi-Annual
225	* 4 Quarterly
226	* @param mixed $basis The type of day count to use (int).
227	* 0 or omitted US (NASD) 30/360
228	* 1 Actual/actual
229	* 2 Actual/360
230	* 3 Actual/365
231	* 4 European 30/360
232	*
233	* @return float\|string Excel date/time serial value or error message
234	*/
235	public static function COUPNCD(
236	$settlement,
237	$maturity,
238	$frequency,
239	$basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
240	) {
241	$settlement = Functions::flattenSingleValue($settlement);
242	$maturity = Functions::flattenSingleValue($maturity);
243	$frequency = Functions::flattenSingleValue($frequency);
244	$basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;
245
246	try {
247	$settlement = FinancialValidations::validateSettlementDate($settlement);
248	$maturity = FinancialValidations::validateMaturityDate($maturity);
249	self::validateCouponPeriod($settlement, $maturity);
250	$frequency = FinancialValidations::validateFrequency($frequency);
251	FinancialValidations::validateBasis($basis);
252	} catch (Exception $e) {
253	return $e->getMessage();
254	}
255
256	return self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_NEXT);
257	}
258
259	/**
260	* COUPNUM.
261	*
262	* Returns the number of coupons payable between the settlement date and maturity date,
263	* rounded up to the nearest whole coupon.
264	*
265	* Excel Function:
266	* COUPNUM(settlement,maturity,frequency[,basis])
267	*
268	* @param mixed $settlement The security's settlement date.
269	* The security settlement date is the date after the issue
270	* date when the security is traded to the buyer.
271	* @param mixed $maturity The security's maturity date.
272	* The maturity date is the date when the security expires.
273	* @param mixed $frequency The number of coupon payments per year.
274	* Valid frequency values are:
275	* 1 Annual
276	* 2 Semi-Annual
277	* 4 Quarterly
278	* @param mixed $basis The type of day count to use (int).
279	* 0 or omitted US (NASD) 30/360
280	* 1 Actual/actual
281	* 2 Actual/360
282	* 3 Actual/365
283	* 4 European 30/360
284	* @return string\|int
285	*/
286	public static function COUPNUM(
287	$settlement,
288	$maturity,
289	$frequency,
290	$basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
291	) {
292	$settlement = Functions::flattenSingleValue($settlement);
293	$maturity = Functions::flattenSingleValue($maturity);
294	$frequency = Functions::flattenSingleValue($frequency);
295	$basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;
296
297	try {
298	$settlement = FinancialValidations::validateSettlementDate($settlement);
299	$maturity = FinancialValidations::validateMaturityDate($maturity);
300	self::validateCouponPeriod($settlement, $maturity);
301	$frequency = FinancialValidations::validateFrequency($frequency);
302	FinancialValidations::validateBasis($basis);
303	} catch (Exception $e) {
304	return $e->getMessage();
305	}
306
307	$yearsBetweenSettlementAndMaturity = DateTimeExcel\YearFrac::fraction(
308	$settlement,
309	$maturity,
310	FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
311	);
312
313	return (int) ceil((float) $yearsBetweenSettlementAndMaturity * $frequency);
314	}
315
316	/**
317	* COUPPCD.
318	*
319	* Returns the previous coupon date before the settlement date.
320	*
321	* Excel Function:
322	* COUPPCD(settlement,maturity,frequency[,basis])
323	*
324	* @param mixed $settlement The security's settlement date.
325	* The security settlement date is the date after the issue
326	* date when the security is traded to the buyer.
327	* @param mixed $maturity The security's maturity date.
328	* The maturity date is the date when the security expires.
329	* @param mixed $frequency The number of coupon payments per year.
330	* Valid frequency values are:
331	* 1 Annual
332	* 2 Semi-Annual
333	* 4 Quarterly
334	* @param mixed $basis The type of day count to use (int).
335	* 0 or omitted US (NASD) 30/360
336	* 1 Actual/actual
337	* 2 Actual/360
338	* 3 Actual/365
339	* 4 European 30/360
340	*
341	* @return float\|string Excel date/time serial value or error message
342	*/
343	public static function COUPPCD(
344	$settlement,
345	$maturity,
346	$frequency,
347	$basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
348	) {
349	$settlement = Functions::flattenSingleValue($settlement);
350	$maturity = Functions::flattenSingleValue($maturity);
351	$frequency = Functions::flattenSingleValue($frequency);
352	$basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;
353
354	try {
355	$settlement = FinancialValidations::validateSettlementDate($settlement);
356	$maturity = FinancialValidations::validateMaturityDate($maturity);
357	self::validateCouponPeriod($settlement, $maturity);
358	$frequency = FinancialValidations::validateFrequency($frequency);
359	FinancialValidations::validateBasis($basis);
360	} catch (Exception $e) {
361	return $e->getMessage();
362	}
363
364	return self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_PREVIOUS);
365	}
366
367	private static function monthsDiff(DateTime $result, int $months, string $plusOrMinus, int $day, bool $lastDayFlag): void
368	{
369	$result->setDate((int) $result->format('Y'), (int) $result->format('m'), 1);
370	$result->modify("$plusOrMinus $months months");
371	$daysInMonth = (int) $result->format('t');
372	$result->setDate((int) $result->format('Y'), (int) $result->format('m'), $lastDayFlag ? $daysInMonth : min($day, $daysInMonth));
373	}
374
375	private static function couponFirstPeriodDate(float $settlement, float $maturity, int $frequency, bool $next): float
376	{
377	$months = 12 / $frequency;
378
379	$result = Date::excelToDateTimeObject($maturity);
380	$day = (int) $result->format('d');
381	$lastDayFlag = Helpers::isLastDayOfMonth($result);
382
383	while ($settlement < Date::PHPToExcel($result)) {
384	self::monthsDiff($result, $months, '-', $day, $lastDayFlag);
385	}
386	if ($next === true) {
387	self::monthsDiff($result, $months, '+', $day, $lastDayFlag);
388	}
389
390	return (float) Date::PHPToExcel($result);
391	}
392
393	private static function validateCouponPeriod(float $settlement, float $maturity): void
394	{
395	if ($settlement >= $maturity) {
396	throw new Exception(ExcelError::NAN());
397	}
398	}
399	}
400