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