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