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?

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]today[/TD]
[TD="align: right"]2/20/2019[/TD]
[TD="align: right"]2/20/2019[/TD]
[TD="align: right"]7/1/2029[/TD]
[TD="align: right"]6/30/2029[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]end nxt mon[/TD]
[TD="align: right"]3/31/2019[/TD]
[TD="align: right"]3/31/2019[/TD]
[TD="align: right"]8/31/2029[/TD]
[TD="align: right"]7/31/2029[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]maturity[/TD]
[TD="align: right"]8/1/2029[/TD]
[TD="align: right"]8/31/2029[/TD]
[TD="align: right"]8/31/2029[/TD]
[TD="align: right"]8/31/2029[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]next coup[/TD]
[TD="align: right"]5/1/2019[/TD]
[TD="align: right"]2/28/2019[/TD]
[TD="align: right"]8/31/2029[/TD]
[TD="align: right"]8/31/2029[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]this or nxt mon?[/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
</tbody>[/TABLE]

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.