Coupons::doNothing() - Code Metrics - Inspection of "Drop all Scrutinizer annotations" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Failed Conditions

Pull Request — master (#3743)

by Adrien

created 2023-09-26 04:28 UTC

Coupons::doNothing() A

↳ Parent: Project

Complexity

Conditions	1
Paths	1

Size

Total Lines	3
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	2
CRAP Score	1

Importance

Changes

Metric	Value
eloc	1
dl	0
loc	3
ccs	2
cts	2
cp	1
rs	10
c	0
b	0
f	0
cc	1
nc	1
nop	1
crap	1

<?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
     *
     * @return float|string
     */
    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 = ($basis === null)
            ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
            : Functions::flattenSingleValue($basis);

        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
     *
     * @return float|string
     */
    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 = ($basis === null)
            ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
            : Functions::flattenSingleValue($basis);

        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 = ($basis === null)
            ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
            : Functions::flattenSingleValue($basis);

        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 = 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 = ($basis === null)
            ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
            : Functions::flattenSingleValue($basis);

        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 = ($basis === null)
            ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
            : Functions::flattenSingleValue($basis);

        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 = ($basis === null)
            ? FinancialConstants::BASIS_DAYS_PER_YEAR_NASD
            : Functions::flattenSingleValue($basis);

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

PHPOffice / PhpSpreadsheet

Pull Request — master (#3743)

Coupons::doNothing() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like