Formula to count the # of occurances in the future

[Im_Offset]

Hello to all my Extremely Excellent Excellers!

Please look at the attached spreadsheet.

There is a list of names in Column B, and an associated date (a future date) in Column C.

In Column D, I am wanting to find the number of future payments which are remaining for each name based on the Maturity Date in Column C.

The date in Column B represents the last payment, and payments are made semi-annually. So to find the number of payments remaining, you take the Maturity date and back up in 6 month steps to today's date. The number of 6 month payment dates to include the maturity date is the number of payments remaining. I tried to show a visual example of everything on the right side of the spreadsheet (i.e. the number of payments for the first name, BofA, is 6).

Can someone give me a formula that I can use in Column D that will give me this information?

Thank you, because although I am learning, clearly,

Im_Offset

 

[Chihiro]

I'm not sure if I understood your requirement accurately. But try this.
=COUPNUM($F$1,C3,2,1)

=COUPNUM(Settlement, Maturity, Frequency, Basis)

F1 is substituted for Settlement Date, which is used to calculate # of coupon payable from Settlement to Maturity. This function rounds to nearest whole coupon.

Frequency: Must be 1, 2 or 4
1 = Annually
2 = Semi-Annually
4 = Quarterly

Basis: Optional
This is Day Count Basis
0 = US(NASD) 30/360
1 = actual/actual
2 = actual/360
3 = actual/365
4 = European 30/360

In your case it should not make tangible difference.

 

[Im_Offset]

I'm not sure if I understood your requirement accurately. But try this.
=COUPNUM($F$1,C3,2,1)

=COUPNUM(Settlement, Maturity, Frequency, Basis)

F1 is substituted for Settlement Date, which is used to calculate # of coupon payable from Settlement to Maturity. This function rounds to nearest whole coupon.

Frequency: Must be 1, 2 or 4
1 = Annually
2 = Semi-Annually
4 = Quarterly

Basis: Optional
This is Day Count Basis
0 = US(NASD) 30/360
1 = actual/actual
2 = actual/360
3 = actual/365
4 = European 30/360

In your case it should not make tangible difference.

Cool. Thank you!