Number of Coupons per year.

[dowtym]

This seems so simple, but I'm having a mental block. Given, Maturity Date, Purchase Date, and Period, what would be a good function or formula to return the number of payments in a specified year.
e.g. A CD with a maturity of 2/5/25, purchase/settlement date of 3/4/24, and monthly payments would have 2 payments/coupons in 2025 (maturity and 1/5/2025) and 10 payments in 2024. If the Period was semi-annual, you'd have one payment in 2025 (at maturity) and one payment in 2024 (8/5/24). If it was a zero-coupon, you'd have one payment at maturity in 2025 and zero payments in 2024.

 

[Cubist]

Try COUPNUM although it doesn't have a monthly option.

 

[dowtym]

Try COUPNUM although it doesn't have a monthly option.

Thanks...I'd thought of that but it was returning the total number of coupons and not coupons per any given year. I attempted to cheat the formula buy inserting a Max(12/31/2024,Date) into the maturity and Min(1/1/2024,Date) into the Settlement inputs and that worked. However, the limitation of not having monthly coupons was unfortunate. I then considered an IF statement to just count the months for a monthly coupon and use Coupnum for the rest. But it finally occurred to me that =ROUNDUP((MIN(Maturity Date,12/31/2024)-MAX(Settlement Date,1/1/2024))/(365/term),0) works for all inputs.. Term is 12 for monthly, 2 for semimonthly, etc.
It's not elegant, but it works. I guess 365 will be slighly inaccurate on leap years so I can tweak that later. Thanks for removing the mental roadblock.