formula for next annual interest due date

cakumar 

I presume that you are referring to bond coupon dates.  I am very weak when it comes to bonds.  So take the following with a grain of salt.

It is my understanding that coupon dates are measured back from the maturity date, not forward from the issue date.  In other words, the maturity date is the last coupon date.

I will try to find an authoritative reference to confirm or refute that.  But I think it is implicit in the fact that the relevant Excel bond functions (below) require only the settlement and maturity dates in order to determine next and previous coupon dates.

With your data input, I would use Current Date as the settlement date.

So, with Maturity Date in B2, I would use the following formulas:

D2:  =COUPNCD(C2,B2,1,0)

E2:  =COUPPCD(C2,B2,1,0)

Format D2 and E2 as a standard or custom date form.  Also, chose an appropriate day count basis; I chose zero.

See the list of bond formulas (et al) by entering "financial functions" without quotes into the Help field.

Caveat:  That assumes that the values in B2 and C2 are bona fide Excel dates.  Double-check that ISNUMBER(--B2) and ISNUMBER(--C2) return TRUE.

The double negate ("--") allows for B2 and C2 to be text that Excel recognizes as dates, based on your regional/language system configuration.

cakumar 

Let's say Issue Date is in A2, Maturity Date in B2 and Today's Date in C2.

If you have Microsoft 365 or Office 2021, enter the following formula in D2.

=LET(d,DATE(YEAR(C2),MONTH(A2),DAY(A2)),e,EDATE(d,12*(d<C2)),IF(e>B2,"",e))

And in E2:

=LET(d,DATE(YEAR(C2),MONTH(A2),DAY(A2)),e,EDATE(d,-12*(d>=C2)),IF(e>B2,"",e))

Format D2 and E2 as dates.

This can be filled down if needed.