OR function with bond coupon payments

[gcefaloni]

Hi everyone,

I'm trying to make a function that will see whether I will receive a coupon payment of a bond within the next month. So let's say a bond pays 4 coupons every year (once every 3 months) and the maturity of the bond is in August 2029, that means every 3 months (February, May, August, December) I would receive a coupon payment. So I would like to know in January or in February that I have an upcoming bond coupon payment coming in soon.

I have done the following formula but I think it doesn't take the OR function the way I want it to be used. It only shows me the bonds that will expire within the next month as if the OR function always returns a 1 or 0.
=IF(MONTH(E17)-OR(0,3,6,9)-MONTH(TODAY())=0,1,0)


So I want the following: if Month(Maturity Date) - either 0 or 3 or 6 or 9 - month of today() <2) then return 1 or 0. The <2 is because I'd like to know the previous month and the current month if there will be a bond coupon paid out, as some kind of alert. If I put only =0, then it will show me only the exact month where the bond will pay a coupon and not give much of an alert.

Can you guys help me figure out what I'm doing wrong? I feel it revolves around the OR function but I'm not sure how to fix it.

Thanks!

 

[joeu2004]

I would like to know in January or in February that I have an upcoming bond coupon payment coming in soon.

Does this do what you need?

	A	B	C	D	E
1	today	2/20/2019	2/20/2019	7/1/2029	6/30/2029
2	end nxt mon	3/31/2019	3/31/2019	8/31/2029	7/31/2029
3	maturity	8/1/2029	8/31/2029	8/31/2029	8/31/2029
4	next coup	5/1/2019	2/28/2019	8/31/2029	8/31/2029
5	this or nxt mon?	FALSE	TRUE	TRUE	FALSE

<tbody>
</tbody>

Formulas:
B2: =EOMONTH(B1,1)
B4: =COUPNCD(B1,B3,4,1)
B5: =COUPNCD(B1,B3,4,1)<=EOMONTH(B1,1)
Copy formulas across

To return 1 (true) or zero (false), change B5 to:

=--(COUPNCD(B1,B3,4,1)<=EOMONTH(B1,1))
or
=IF(COUPNCD(B1,B3,4,1)<=EOMONTH(B1,1),1,0)

Change the day-count basis, if you wish.

 

[gcefaloni]

Yes! That works perfectly. Thanks a lot! Never thought of the coupncd function.