Wrong coupon days for maturities at the end of month COUPPCD / COUPNCD

[myfonj]

Coupon dates rules are apparently quite complicated:

• When maturity is the last day of month:
  • => all coupon days are last days of corresponding months as well.
• When maturity is in day-in-month not present in coupon month:
  • => coupon day is clamped to nearest last day of month.
• Otherwise (maturity day-in-month is "below 29"):
  • => all coupon days shares same day-in-month.

Apparently Excel 2016 applies "end-of-month rule" not mentioned in official docs but described in various financial resources.

End-of-Month Rule
The end-of-month rule affects a bond's coupon payment structure. When the rule is in effect, a security that pays a coupon on the last actual day of a month will always pay coupons on the last day of the month. This means, for example, that a semiannual bond that pays a coupon on February 28 in nonleap years will pay coupons on August 31 in all years and on February 29 in leap years.
-- https://www.mathworks.com/help/finance/pricing-and-computing-yields-for-fixed-income-securities.html

1 .. 15 .. 27 28 <- maturity in shorter month
|     |     | |
|     |     | `---------.
↓     ↓     ↓           ↓
1 .. 15 .. 27 28 29 30 31 <- coupon day

1 .. 15 .. 27 28 29 30 31 <- maturity
|     |     |  |  |  |  |
|     |     |  +--+--+--`
↓     ↓     ↓  ↓
1 .. 15 .. 27 28 <- coupon day in shorter month

Months lengths and coupon period shifts

||      ||m1.  |m2.  |m3.  |m4.  |m5.  |m6.  |m7.  |m8.  |m9.  |m10. |m11. |m12. |
||      ||   31|28/29|   31| 30  |   31| 30  |   31|   31| 30  |   31| 30  |   31|
||--------------------------------------------------------------------------------
|| 2./4 ||m4.  |m5.  |m6.  |m7.  |m8.  |m9.  |m10. |m11. |m12. |m1.  |m2.  |m3.  |
||      || 30  |   31| 30  |   31|   31| 30  |   31| 30  |   31|   31|28/29|   31|
||--------------------------------------------------------------------------------
|| 2./2 ||m7.  |m8.  |m9.  |m10. |m11. |m12. |m1.  |m2.  |m3.  |m4.  |m5.  |m6.  |
|| 3./4 ||   31|   31| 30  |   31| 30  |   31|   31|28/29|   31| 30  |   31| 30  |
||--------------------------------------------------------------------------------
|| 4./4 ||m10. |m11. |m12. |m1.  |m2.  |m3.  |m4.  |m5.  |m6.  |m7.  |m8.  |m9.  |
||      ||   31| 30  |   31|   31|28/29|   31| 30  |   31| 30  |   31|   31| 30  |

COUPPCD and COUPNCD computation examples for end of month days; dates in `dd.MM.yyyy` format; dates with day-number-in-month different from maturity date are in **bold**.

data with formulas

Settlement	43586	=B2	=C2	=D2	=E2	=F2
Maturity	43739	=B3-1	=C3-1	=D3-1	=E3-1	=F3-1
Frequency	2	=B4	=C4	=D4	=E4	=F4
Basis	4	=B5	=C5	=D5	=E5	=F5
COUPPCD	=COUPPCD(B2;B3;B4;B5)	=COUPPCD(C2;C3;C4;C5)	=COUPPCD(D2;D3;D4;D5)	=COUPPCD(E2;E3;E4;E5)	=COUPPCD(F2;F3;F4;F5)	=COUPPCD(G2;G3;G4;G5)
COUPNCD	=COUPNCD(B2;B3;B4;B5)	=COUPNCD(C$2;C$3;C$4;C$5)	=COUPNCD(D$2;D$3;D$4;D$5)	=COUPNCD(E$2;E$3;E$4;E$5)	=COUPNCD(F$2;F$3;F$4;F$5)	=COUPNCD(G$2;G$3;G$4;G$5)
Settlement	40299	=B9	=C9	=D9	=E9	=F9
Maturity	41334	=B10-1	=C10-1	=D10-1	=E10-1	=F10-1
Frequency	2	=B11	=C11	=D11	=E11	=F11
Basis	4	=B12	=C12	=D12	=E12	=F12
COUPPCD	=COUPPCD(B9;B10;B11;B12)	=COUPPCD(C9;C10;C11;C12)	=COUPPCD(D9;D10;D11;D12)	=COUPPCD(E9;E10;E11;E12)	=COUPPCD(F9;F10;F11;F12)	=COUPPCD(G9;G10;G11;G12)
COUPNCD	=COUPNCD(B9;B10;B11;B12)	=COUPNCD(C9;C10;C11;C12)	=COUPNCD(D9;D10;D11;D12)	=COUPNCD(E9;E10;E11;E12)	=COUPNCD(F9;F10;F11;F12)	=COUPNCD(G9;G10;G11;G12)
Settlement	40299	=B16	=C16	=D16	=E16	=F16
Maturity	40969	=B17-1	=C17-1	=D17-1	=E17-1	=F17-1
Frequency	2	=B18	=C18	=D18	=E18	=F18
Basis	4	=B19	=C19	=D19	=E19	=F19
COUPPCD	=COUPPCD(B16;B17;B18;B19)	=COUPPCD(C16;C17;C18;C19)	=COUPPCD(D16;D17;D18;D19)	=COUPPCD(E16;E17;E18;E19)	=COUPPCD(F16;F17;F18;F19)	=COUPPCD(G16;G17;G18;G19)
COUPNCD	=COUPNCD(B16;B17;B18;B19)	=COUPNCD(C16;C17;C18;C19)	=COUPNCD(D16;D17;D18;D19)	=COUPNCD(E16;E17;E18;E19)	=COUPNCD(F16;F17;F18;F19)	=COUPNCD(G16;G17;G18;G19)
Settlement	40299	=B23	=C23	=D23	=E23	=F23
Maturity	41153	=B24-1	=C24-1	=D24-1	=E24-1	=F24-1
Frequency	2	=B25	=C25	=D25	=E25	=F25
Basis	4	=B26	=C26	=D26	=E26	=F26
COUPPCD	=COUPPCD(B23;B24;B25;B26)	=COUPPCD(C23;C24;C25;C26)	=COUPPCD(D23;D24;D25;D26)	=COUPPCD(E23;E24;E25;E26)	=COUPPCD(F23;F24;F25;F26)	=COUPPCD(G23;G24;G25;G26)
COUPNCD	=COUPNCD(B23;B24;B25;B26)	=COUPNCD(C23;C24;C25;C26)	=COUPNCD(D23;D24;D25;D26)	=COUPNCD(E23;E24;E25;E26)	=COUPNCD(F23;F24;F25;F26)	=COUPNCD(G23;G24;G25;G26)

produced data

Settlement	01.05.2019	01.05.2019	01.05.2019	01.05.2019	01.05.2019	01.05.2019
Maturity	01.10.2019	30.09.2019	29.09.2019	28.09.2019	27.09.2019	26.09.2019
Frequency	2	2	2	2	2	2
Basis	4	4	4	4	4	4
COUPPCD	01.04.2019	31.03.2019	29.03.2019	28.03.2019	27.03.2019	26.03.2019
COUPNCD	01.10.2019	30.09.2019	29.09.2019	28.09.2019	27.09.2019	26.09.2019
Settlement	01.05.2010	01.05.2010	01.05.2010	01.05.2010	01.05.2010	01.05.2010
Maturity	01.03.2013	28.02.2013	27.02.2013	26.02.2013	25.02.2013	24.02.2013
Frequency	2	2	2	2	2	2
Basis	4	4	4	4	4	4
COUPPCD	01.03.2010	28.02.2010	27.02.2010	26.02.2010	25.02.2010	24.02.2010
COUPNCD	01.09.2010	31.08.2010	27.08.2010	26.08.2010	25.08.2010	24.08.2010
Settlement	01.05.2010	01.05.2010	01.05.2010	01.05.2010	01.05.2010	01.05.2010
Maturity	01.03.2012	29.02.2012	28.02.2012	27.02.2012	26.02.2012	25.02.2012
Frequency	2	2	2	2	2	2
Basis	4	4	4	4	4	4
COUPPCD	01.03.2010	28.02.2010	28.02.2010	27.02.2010	26.02.2010	25.02.2010
COUPNCD	01.09.2010	31.08.2010	28.08.2010	27.08.2010	26.08.2010	25.08.2010
Settlement	01.05.2010	01.05.2010	01.05.2010	01.05.2010	01.05.2010	01.05.2010
Maturity	01.09.2012	31.08.2012	30.08.2012	29.08.2012	28.08.2012	27.08.2012
Frequency	2	2	2	2	2	2
Basis	4	4	4	4	4	4
COUPPCD	01.03.2010	28.02.2010	28.02.2010	28.02.2010	28.02.2010	27.02.2010
COUPNCD	01.09.2010	31.08.2010	30.08.2010	29.08.2010	28.08.2010	27.08.2010

couponFirstPeriodDate currently does not reflect this complexity; it just shift resulting date one day earlier if maturity is last day of month, what covers just some cases.

(some cases)

• when maturity is 31st day and coupon month has 30 days
• when maturity is last 30th day of month and coupon day is the leap day
• and when maturity is the leap day and coupon day is last of February in non-leap year (I assume)

PhpSpreadsheet/src/PhpSpreadsheet/Calculation/Financial.php

Lines 27 to 46 in f734783

	private static function couponFirstPeriodDate($settlement, $maturity, $frequency, $next)
	{
	$months = 12 / $frequency;
	$result = Date::excelToDateTimeObject($maturity);
	$eom = self::isLastDayOfMonth($result);
	while ($settlement < Date::PHPToExcel($result)) {
	$result->modify('-' . $months . ' months');
	}
	if ($next) {
	$result->modify('+' . $months . ' months');
	}
	if ($eom) {
	$result->modify('-1 day');
	}
	return Date::PHPToExcel($result);
	}

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[oleibman]

Fixed by PR #2116 in May 2021.