Coupons::COUPDAYS() - Code Metrics - Inspection of "Merge pull request #4565 from oleibman/issue4588" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Push — master ( ea97af...216db0 )

unknown

created 2025-08-07 05:03 UTC

Coupons::COUPDAYS() A

↳ Parent: Coupons

Complexity

Conditions	5
Paths	9

Size

Total Lines	39
Code Lines	24

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	22
CRAP Score	5

Importance

Changes

Metric	Value
eloc	24
dl	0
loc	39
ccs	22
cts	22
cp	1
rs	9.2248
c	0
b	0
f	0
cc	5
nc	9
nop	4
crap	5

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation\Financial;

use DateTime;
use PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
use PhpOffice\PhpSpreadsheet\Calculation\Exception;
use PhpOffice\PhpSpreadsheet\Calculation\Financial\Constants as FinancialConstants;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
use PhpOffice\PhpSpreadsheet\Shared\Date;

class Coupons
{
    private const PERIOD_DATE_PREVIOUS = false;
    private const PERIOD_DATE_NEXT = true;

    /**
     * COUPDAYBS.
     *
     * Returns the number of days from the beginning of the coupon period to the settlement date.
     *
     * Excel Function:
     *        COUPDAYBS(settlement,maturity,frequency[,basis])
     *
     * @param mixed $settlement The security's settlement date.
     *                              The security settlement date is the date after the issue
     *                                  date when the security is traded to the buyer.
     * @param mixed $maturity The security's maturity date.
     *                            The maturity date is the date when the security expires.
     * @param mixed $frequency The number of coupon payments per year (int).
     *                             Valid frequency values are:
     *                               1    Annual
     *                               2    Semi-Annual
     *                               4    Quarterly
     * @param mixed $basis The type of day count to use (int).
     *                         0 or omitted    US (NASD) 30/360
     *                         1               Actual/actual
     *                         2               Actual/360
     *                         3               Actual/365
     *                         4               European 30/360
     */
    public static function COUPDAYBS(
        mixed $settlement,
        mixed $maturity,
        mixed $frequency,
        mixed $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
    ): string|int|float {
        $settlement = Functions::flattenSingleValue($settlement);
        $maturity = Functions::flattenSingleValue($maturity);
        $frequency = Functions::flattenSingleValue($frequency);
        $basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;

        try {
            $settlement = FinancialValidations::validateSettlementDate($settlement);
            $maturity = FinancialValidations::validateMaturityDate($maturity);
            self::validateCouponPeriod($settlement, $maturity);
            $frequency = FinancialValidations::validateFrequency($frequency);
            $basis = FinancialValidations::validateBasis($basis);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $daysPerYear = Helpers::daysPerYear(Functions::scalar(DateTimeExcel\DateParts::year($settlement)), $basis);
        if (is_string($daysPerYear)) {
            return ExcelError::VALUE();
        }
        $prev = self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_PREVIOUS);

        if ($basis === FinancialConstants::BASIS_DAYS_PER_YEAR_ACTUAL) {
            return abs((float) DateTimeExcel\Days::between($prev, $settlement));
        }

        return (float) DateTimeExcel\YearFrac::fraction($prev, $settlement, $basis) * $daysPerYear;
    }

    /**
     * COUPDAYS.
     *
     * Returns the number of days in the coupon period that contains the settlement date.
     *
     * Excel Function:
     *        COUPDAYS(settlement,maturity,frequency[,basis])
     *
     * @param mixed $settlement The security's settlement date.
     *                              The security settlement date is the date after the issue
     *                                  date when the security is traded to the buyer.
     * @param mixed $maturity The security's maturity date.
     *                            The maturity date is the date when the security expires.
     * @param mixed $frequency The number of coupon payments per year.
     *                             Valid frequency values are:
     *                               1    Annual
     *                               2    Semi-Annual
     *                               4    Quarterly
     * @param mixed $basis The type of day count to use (int).
     *                         0 or omitted    US (NASD) 30/360
     *                         1               Actual/actual
     *                         2               Actual/360
     *                         3               Actual/365
     *                         4               European 30/360
     */
    public static function COUPDAYS(
        mixed $settlement,
        mixed $maturity,
        mixed $frequency,
        mixed $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
    ): string|int|float {
        $settlement = Functions::flattenSingleValue($settlement);
        $maturity = Functions::flattenSingleValue($maturity);
        $frequency = Functions::flattenSingleValue($frequency);
        $basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;

        try {
            $settlement = FinancialValidations::validateSettlementDate($settlement);
            $maturity = FinancialValidations::validateMaturityDate($maturity);
            self::validateCouponPeriod($settlement, $maturity);
            $frequency = FinancialValidations::validateFrequency($frequency);
            $basis = FinancialValidations::validateBasis($basis);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        switch ($basis) {
            case FinancialConstants::BASIS_DAYS_PER_YEAR_365:
                // Actual/365
                return 365 / $frequency;
            case FinancialConstants::BASIS_DAYS_PER_YEAR_ACTUAL:
                // Actual/actual
                if ($frequency == FinancialConstants::FREQUENCY_ANNUAL) {
                    $daysPerYear = (int) Helpers::daysPerYear(Functions::scalar(DateTimeExcel\DateParts::year($settlement)), $basis);

                    return $daysPerYear / $frequency;
                }
                $prev = self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_PREVIOUS);
                $next = self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_NEXT);

                return $next - $prev;
            default:
                // US (NASD) 30/360, Actual/360 or European 30/360
                return 360 / $frequency;
        }
    }

    /**
     * COUPDAYSNC.
     *
     * Returns the number of days from the settlement date to the next coupon date.
     *
     * Excel Function:
     *        COUPDAYSNC(settlement,maturity,frequency[,basis])
     *
     * @param mixed $settlement The security's settlement date.
     *                              The security settlement date is the date after the issue
     *                                  date when the security is traded to the buyer.
     * @param mixed $maturity The security's maturity date.
     *                            The maturity date is the date when the security expires.
     * @param mixed $frequency The number of coupon payments per year.
     *                             Valid frequency values are:
     *                               1    Annual
     *                               2    Semi-Annual
     *                               4    Quarterly
     * @param mixed $basis The type of day count to use (int) .
     *                         0 or omitted    US (NASD) 30/360
     *                         1               Actual/actual
     *                         2               Actual/360
     *                         3               Actual/365
     *                         4               European 30/360
     */
    public static function COUPDAYSNC(
        mixed $settlement,
        mixed $maturity,
        mixed $frequency,
        mixed $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
    ): string|float {
        $settlement = Functions::flattenSingleValue($settlement);
        $maturity = Functions::flattenSingleValue($maturity);
        $frequency = Functions::flattenSingleValue($frequency);
        $basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;

        try {
            $settlement = FinancialValidations::validateSettlementDate($settlement);
            $maturity = FinancialValidations::validateMaturityDate($maturity);
            self::validateCouponPeriod($settlement, $maturity);
            $frequency = FinancialValidations::validateFrequency($frequency);
            $basis = FinancialValidations::validateBasis($basis);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        /** @var int $daysPerYear */
        $daysPerYear = Helpers::daysPerYear(Functions::Scalar(DateTimeExcel\DateParts::year($settlement)), $basis);
        $next = self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_NEXT);

        if ($basis === FinancialConstants::BASIS_DAYS_PER_YEAR_NASD) {
            $settlementDate = Date::excelToDateTimeObject($settlement);
            $settlementEoM = Helpers::isLastDayOfMonth($settlementDate);
            if ($settlementEoM) {
                ++$settlement;
            }
        }

        return (float) DateTimeExcel\YearFrac::fraction($settlement, $next, $basis) * $daysPerYear;
    }

    /**
     * COUPNCD.
     *
     * Returns the next coupon date after the settlement date.
     *
     * Excel Function:
     *        COUPNCD(settlement,maturity,frequency[,basis])
     *
     * @param mixed $settlement The security's settlement date.
     *                              The security settlement date is the date after the issue
     *                                  date when the security is traded to the buyer.
     * @param mixed $maturity The security's maturity date.
     *                            The maturity date is the date when the security expires.
     * @param mixed $frequency The number of coupon payments per year.
     *                             Valid frequency values are:
     *                               1    Annual
     *                               2    Semi-Annual
     *                               4    Quarterly
     * @param mixed $basis The type of day count to use (int).
     *                         0 or omitted    US (NASD) 30/360
     *                         1               Actual/actual
     *                         2               Actual/360
     *                         3               Actual/365
     *                         4               European 30/360
     *
     * @return float|string Excel date/time serial value or error message
     */
    public static function COUPNCD(
        mixed $settlement,
        mixed $maturity,
        mixed $frequency,
        mixed $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
    ): string|float {
        $settlement = Functions::flattenSingleValue($settlement);
        $maturity = Functions::flattenSingleValue($maturity);
        $frequency = Functions::flattenSingleValue($frequency);
        $basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;

        try {
            $settlement = FinancialValidations::validateSettlementDate($settlement);
            $maturity = FinancialValidations::validateMaturityDate($maturity);
            self::validateCouponPeriod($settlement, $maturity);
            $frequency = FinancialValidations::validateFrequency($frequency);
            FinancialValidations::validateBasis($basis);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        return self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_NEXT);
    }

    /**
     * COUPNUM.
     *
     * Returns the number of coupons payable between the settlement date and maturity date,
     * rounded up to the nearest whole coupon.
     *
     * Excel Function:
     *        COUPNUM(settlement,maturity,frequency[,basis])
     *
     * @param mixed $settlement The security's settlement date.
     *                              The security settlement date is the date after the issue
     *                                  date when the security is traded to the buyer.
     * @param mixed $maturity The security's maturity date.
     *                            The maturity date is the date when the security expires.
     * @param mixed $frequency The number of coupon payments per year.
     *                             Valid frequency values are:
     *                               1    Annual
     *                               2    Semi-Annual
     *                               4    Quarterly
     * @param mixed $basis The type of day count to use (int).
     *                         0 or omitted    US (NASD) 30/360
     *                         1               Actual/actual
     *                         2               Actual/360
     *                         3               Actual/365
     *                         4               European 30/360
     */
    public static function COUPNUM(
        mixed $settlement,
        mixed $maturity,
        mixed $frequency,
        mixed $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
    ): string|int {
        $settlement = Functions::flattenSingleValue($settlement);
        $maturity = Functions::flattenSingleValue($maturity);
        $frequency = Functions::flattenSingleValue($frequency);
        $basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;

        try {
            $settlement = FinancialValidations::validateSettlementDate($settlement);
            $maturity = FinancialValidations::validateMaturityDate($maturity);
            self::validateCouponPeriod($settlement, $maturity);
            $frequency = FinancialValidations::validateFrequency($frequency);
            FinancialValidations::validateBasis($basis);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $yearsBetweenSettlementAndMaturity = DateTimeExcel\YearFrac::fraction(
            $settlement,
            $maturity,
            FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
        );

        return (int) ceil((float) $yearsBetweenSettlementAndMaturity * $frequency);
    }

    /**
     * COUPPCD.
     *
     * Returns the previous coupon date before the settlement date.
     *
     * Excel Function:
     *        COUPPCD(settlement,maturity,frequency[,basis])
     *
     * @param mixed $settlement The security's settlement date.
     *                              The security settlement date is the date after the issue
     *                              date when the security is traded to the buyer.
     * @param mixed $maturity The security's maturity date.
     *                            The maturity date is the date when the security expires.
     * @param mixed $frequency The number of coupon payments per year.
     *                             Valid frequency values are:
     *                               1    Annual
     *                               2    Semi-Annual
     *                               4    Quarterly
     * @param mixed $basis The type of day count to use (int).
     *                         0 or omitted    US (NASD) 30/360
     *                         1               Actual/actual
     *                         2               Actual/360
     *                         3               Actual/365
     *                         4               European 30/360
     *
     * @return float|string Excel date/time serial value or error message
     */
    public static function COUPPCD(
        mixed $settlement,
        mixed $maturity,
        mixed $frequency,
        mixed $basis = FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
    ): string|float {
        $settlement = Functions::flattenSingleValue($settlement);
        $maturity = Functions::flattenSingleValue($maturity);
        $frequency = Functions::flattenSingleValue($frequency);
        $basis = Functions::flattenSingleValue($basis) ?? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD;

        try {
            $settlement = FinancialValidations::validateSettlementDate($settlement);
            $maturity = FinancialValidations::validateMaturityDate($maturity);
            self::validateCouponPeriod($settlement, $maturity);
            $frequency = FinancialValidations::validateFrequency($frequency);
            FinancialValidations::validateBasis($basis);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        return self::couponFirstPeriodDate($settlement, $maturity, $frequency, self::PERIOD_DATE_PREVIOUS);
    }

    private static function monthsDiff(DateTime $result, int $months, string $plusOrMinus, int $day, bool $lastDayFlag): void
    {
        $result->setDate((int) $result->format('Y'), (int) $result->format('m'), 1);
        $result->modify("$plusOrMinus $months months");
        $daysInMonth = (int) $result->format('t');
        $result->setDate((int) $result->format('Y'), (int) $result->format('m'), $lastDayFlag ? $daysInMonth : min($day, $daysInMonth));
    }

    private static function couponFirstPeriodDate(float $settlement, float $maturity, int $frequency, bool $next): float
    {
        $months = 12 / $frequency;

        $result = Date::excelToDateTimeObject($maturity);
        $day = (int) $result->format('d');
        $lastDayFlag = Helpers::isLastDayOfMonth($result);

        while ($settlement < Date::PHPToExcel($result)) {
            self::monthsDiff($result, $months, '-', $day, $lastDayFlag);
        }
        if ($next === true) {
            self::monthsDiff($result, $months, '+', $day, $lastDayFlag);
        }

        return (float) Date::PHPToExcel($result);
    }

    private static function validateCouponPeriod(float $settlement, float $maturity): void
    {
        if ($settlement >= $maturity) {
            throw new Exception(ExcelError::NAN());
        }
    }
}


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

PHPOffice / PhpSpreadsheet

Push — master ( ea97af...216db0 )

Coupons::COUPDAYS() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like