OR function with bond coupon payments

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
117
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
Joined
Mar 2, 2014
Messages
3,021
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Mar 15, 2016
Messages
117
Yes! That works perfectly. Thanks a lot! Never thought of the coupncd function.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top