tablepress / libraries / vendor / PhpSpreadsheet / Calculation / Financial / Securities / Price.php

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

278 lines 11.8 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\Financial\Securities;
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\Financial\Coupons;
9	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Financial\Helpers;
10	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
11	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
12	use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper;
13
14	class Price
15	{
16	/**
17	* PRICE.
18	*
19	* Returns the price per $100 face value of a security that pays periodic interest.
20	*
21	* @param mixed $settlement The security's settlement date.
22	* The security settlement date is the date after the issue date when the security
23	* is traded to the buyer.
24	* @param mixed $maturity The security's maturity date.
25	* The maturity date is the date when the security expires.
26	* @param mixed $rate the security's annual coupon rate
27	* @param mixed $yield the security's annual yield
28	* @param mixed $redemption The number of coupon payments per year.
29	* For annual payments, frequency = 1;
30	* for semiannual, frequency = 2;
31	* for quarterly, frequency = 4.
32	* @param mixed $basis The type of day count to use.
33	* 0 or omitted US (NASD) 30/360
34	* 1 Actual/actual
35	* 2 Actual/360
36	* 3 Actual/365
37	* 4 European 30/360
38	*
39	* @return float\|string Result, or a string containing an error
40	* @param mixed $frequency
41	*/
42	public static function price(
43	$settlement,
44	$maturity,
45	$rate,
46	$yield,
47	$redemption,
48	$frequency,
49	$basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
50	) {
51	$settlement = Functions::flattenSingleValue($settlement);
52	$maturity = Functions::flattenSingleValue($maturity);
53	$rate = Functions::flattenSingleValue($rate);
54	$yield = Functions::flattenSingleValue($yield);
55	$redemption = Functions::flattenSingleValue($redemption);
56	$frequency = Functions::flattenSingleValue($frequency);
57	$basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;
58
59	try {
60	$settlement = SecurityValidations::validateSettlementDate($settlement);
61	$maturity = SecurityValidations::validateMaturityDate($maturity);
62	SecurityValidations::validateSecurityPeriod($settlement, $maturity);
63	$rate = SecurityValidations::validateRate($rate);
64	$yield = SecurityValidations::validateYield($yield);
65	$redemption = SecurityValidations::validateRedemption($redemption);
66	$frequency = SecurityValidations::validateFrequency($frequency);
67	$basis = SecurityValidations::validateBasis($basis);
68	} catch (Exception $e) {
69	return $e->getMessage();
70	}
71
72	$dsc = (float) Coupons::COUPDAYSNC($settlement, $maturity, $frequency, $basis);
73	$e = (float) Coupons::COUPDAYS($settlement, $maturity, $frequency, $basis);
74	$n = (int) Coupons::COUPNUM($settlement, $maturity, $frequency, $basis);
75	$a = (float) Coupons::COUPDAYBS($settlement, $maturity, $frequency, $basis);
76
77	$baseYF = 1.0 + ($yield / $frequency);
78	$rfp = 100 * ($rate / $frequency);
79	$de = $dsc / $e;
80
81	$result = $redemption / $baseYF ** (--$n + $de);
82	for ($k = 0; $k <= $n; ++$k) {
83	$result += $rfp / ($baseYF ** ($k + $de));
84	}
85	$result -= $rfp * ($a / $e);
86
87	return $result;
88	}
89
90	/**
91	* PRICEDISC.
92	*
93	* Returns the price per $100 face value of a discounted security.
94	*
95	* @param mixed $settlement The security's settlement date.
96	* The security settlement date is the date after the issue date when the security
97	* is traded to the buyer.
98	* @param mixed $maturity The security's maturity date.
99	* The maturity date is the date when the security expires.
100	* @param mixed $discount The security's discount rate
101	* @param mixed $redemption The security's redemption value per $100 face value
102	* @param mixed $basis The type of day count to use.
103	* 0 or omitted US (NASD) 30/360
104	* 1 Actual/actual
105	* 2 Actual/360
106	* 3 Actual/365
107	* 4 European 30/360
108	*
109	* @return float\|string Result, or a string containing an error
110	*/
111	public static function priceDiscounted(
112	$settlement,
113	$maturity,
114	$discount,
115	$redemption,
116	$basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
117	) {
118	$settlement = Functions::flattenSingleValue($settlement);
119	$maturity = Functions::flattenSingleValue($maturity);
120	$discount = Functions::flattenSingleValue($discount);
121	$redemption = Functions::flattenSingleValue($redemption);
122	$basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;
123
124	try {
125	$settlement = SecurityValidations::validateSettlementDate($settlement);
126	$maturity = SecurityValidations::validateMaturityDate($maturity);
127	SecurityValidations::validateSecurityPeriod($settlement, $maturity);
128	$discount = SecurityValidations::validateDiscount($discount);
129	$redemption = SecurityValidations::validateRedemption($redemption);
130	$basis = SecurityValidations::validateBasis($basis);
131	} catch (Exception $e) {
132	return $e->getMessage();
133	}
134
135	$daysBetweenSettlementAndMaturity = Functions::scalar(DateTimeExcel\YearFrac::fraction($settlement, $maturity, $basis));
136	if (!is_numeric($daysBetweenSettlementAndMaturity)) {
137	// return date error
138	return StringHelper::convertToString($daysBetweenSettlementAndMaturity);
139	}
140
141	return $redemption * (1 - $discount * $daysBetweenSettlementAndMaturity);
142	}
143
144	/**
145	* PRICEMAT.
146	*
147	* Returns the price per $100 face value of a security that pays interest at maturity.
148	*
149	* @param mixed $settlement The security's settlement date.
150	* The security's settlement date is the date after the issue date when the
151	* security is traded to the buyer.
152	* @param mixed $maturity The security's maturity date.
153	* The maturity date is the date when the security expires.
154	* @param mixed $issue The security's issue date
155	* @param mixed $rate The security's interest rate at date of issue
156	* @param mixed $yield The security's annual yield
157	* @param mixed $basis The type of day count to use.
158	* 0 or omitted US (NASD) 30/360
159	* 1 Actual/actual
160	* 2 Actual/360
161	* 3 Actual/365
162	* 4 European 30/360
163	*
164	* @return float\|string Result, or a string containing an error
165	*/
166	public static function priceAtMaturity(
167	$settlement,
168	$maturity,
169	$issue,
170	$rate,
171	$yield,
172	$basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
173	) {
174	$settlement = Functions::flattenSingleValue($settlement);
175	$maturity = Functions::flattenSingleValue($maturity);
176	$issue = Functions::flattenSingleValue($issue);
177	$rate = Functions::flattenSingleValue($rate);
178	$yield = Functions::flattenSingleValue($yield);
179	$basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;
180
181	try {
182	$settlement = SecurityValidations::validateSettlementDate($settlement);
183	$maturity = SecurityValidations::validateMaturityDate($maturity);
184	SecurityValidations::validateSecurityPeriod($settlement, $maturity);
185	$issue = SecurityValidations::validateIssueDate($issue);
186	$rate = SecurityValidations::validateRate($rate);
187	$yield = SecurityValidations::validateYield($yield);
188	$basis = SecurityValidations::validateBasis($basis);
189	} catch (Exception $e) {
190	return $e->getMessage();
191	}
192
193	$daysPerYear = Helpers::daysPerYear(Functions::scalar(DateTimeExcel\DateParts::year($settlement)), $basis);
194	if (!is_numeric($daysPerYear)) {
195	return $daysPerYear;
196	}
197	$daysBetweenIssueAndSettlement = Functions::scalar(DateTimeExcel\YearFrac::fraction($issue, $settlement, $basis));
198	if (!is_numeric($daysBetweenIssueAndSettlement)) {
199	// return date error
200	return StringHelper::convertToString($daysBetweenIssueAndSettlement);
201	}
202	$daysBetweenIssueAndSettlement *= $daysPerYear;
203	$daysBetweenIssueAndMaturity = Functions::scalar(DateTimeExcel\YearFrac::fraction($issue, $maturity, $basis));
204	if (!is_numeric($daysBetweenIssueAndMaturity)) {
205	// return date error
206	return StringHelper::convertToString($daysBetweenIssueAndMaturity);
207	}
208	$daysBetweenIssueAndMaturity *= $daysPerYear;
209	$daysBetweenSettlementAndMaturity = Functions::scalar(DateTimeExcel\YearFrac::fraction($settlement, $maturity, $basis));
210	if (!is_numeric($daysBetweenSettlementAndMaturity)) {
211	// return date error
212	return StringHelper::convertToString($daysBetweenSettlementAndMaturity);
213	}
214	$daysBetweenSettlementAndMaturity *= $daysPerYear;
215
216	return (100 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate * 100))
217	/ (1 + (($daysBetweenSettlementAndMaturity / $daysPerYear) * $yield))
218	- (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate * 100);
219	}
220
221	/**
222	* RECEIVED.
223	*
224	* Returns the amount received at maturity for a fully invested Security.
225	*
226	* @param mixed $settlement The security's settlement date.
227	* The security settlement date is the date after the issue date when the security
228	* is traded to the buyer.
229	* @param mixed $maturity The security's maturity date.
230	* The maturity date is the date when the security expires.
231	* @param mixed $investment The amount invested in the security
232	* @param mixed $discount The security's discount rate
233	* @param mixed $basis The type of day count to use.
234	* 0 or omitted US (NASD) 30/360
235	* 1 Actual/actual
236	* 2 Actual/360
237	* 3 Actual/365
238	* 4 European 30/360
239	*
240	* @return float\|string Result, or a string containing an error
241	*/
242	public static function received(
243	$settlement,
244	$maturity,
245	$investment,
246	$discount,
247	$basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
248	) {
249	$settlement = Functions::flattenSingleValue($settlement);
250	$maturity = Functions::flattenSingleValue($maturity);
251	$investment = Functions::flattenSingleValue($investment);
252	$discount = Functions::flattenSingleValue($discount);
253	$basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;
254
255	try {
256	$settlement = SecurityValidations::validateSettlementDate($settlement);
257	$maturity = SecurityValidations::validateMaturityDate($maturity);
258	SecurityValidations::validateSecurityPeriod($settlement, $maturity);
259	$investment = SecurityValidations::validateFloat($investment);
260	$discount = SecurityValidations::validateDiscount($discount);
261	$basis = SecurityValidations::validateBasis($basis);
262	} catch (Exception $e) {
263	return $e->getMessage();
264	}
265
266	if ($investment <= 0) {
267	return ExcelError::NAN();
268	}
269	$daysBetweenSettlementAndMaturity = DateTimeExcel\YearFrac::fraction($settlement, $maturity, $basis);
270	if (!is_numeric($daysBetweenSettlementAndMaturity)) {
271	// return date error
272	return StringHelper::convertToString(Functions::scalar($daysBetweenSettlementAndMaturity));
273	}
274
275	return $investment / (1 - ($discount * $daysBetweenSettlementAndMaturity));
276	}
277	}
278