# OR function with bond coupon payments

#### gcefaloni

##### Board Regular
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!

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### joeu2004

##### Well-known Member
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?

ABCDE
1today2/20/20192/20/20197/1/20296/30/2029
2end nxt mon3/31/20193/31/20198/31/20297/31/2029
3maturity8/1/20298/31/20298/31/20298/31/2029
4next coup5/1/20192/28/20198/31/20298/31/2029
5this or nxt mon?FALSETRUETRUEFALSE

<tbody>
</tbody>
Rich (BB code):
``````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

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

Replies
2
Views
67
Replies
12
Views
632
Replies
1
Views
154
Replies
3
Views
246
Replies
3
Views
157

1,129,387
Messages
5,636,010
Members
416,892
Latest member
Bensch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back