CODEWITHSUNDEEP
excel
Mariana da Costa
Mariana da Costa

Reputation: 173

Coupon date for a monthly bond - excel

I'm trying to calculate all the cash flows dates for a bond, the frequency date of the bond is monthly. For other frequency I usually use COUPNCD. I tried to use =EDATE(maturity date;-1) but the dates are not right since it's not considering the way coupons are paid, always the same day except when it doesn't exist.

Example:

Settlement date: 31-12-2014 ; Maturity date: 30-05-2016 ; Basis: 2 (actual/360) and Frequency: 4

Computing the coupon dates with =COUPNCD i get the following dates for the cash flows:

28-02-2015 30-05-2015 30-08-2015 30-11-2015 29-02-2016 30-05-2016

If I compute with =EDATE(date;-3) i get:

28-02-2015 30-05-2015 30-08-2015 29-11-2015 29-02-2016 30-05-2016

Do u have any idea that could help me?

Thanks :)

Upvotes: 0

Views: 1729

Answers (2)

MikeS
MikeS

Reputation: 26

I had the same problem regarding functions COUPNCD and COUPNUM – cannot use 12 (for monthly payments) as frequency attribute.

There is a way around – you can write the following user defined function. I use it now in my financial models, and it works perfectly.

Option Explicit
Public Function DC_CoupNum(Settlement As Date, Maturity As Date, NumberOfPayments As Integer) As Integer
    Dim NewDate As Date
    Dim i As Integer
    Dim r As Integer
    Select Case NumberOfPayments

        Case 1
            r = 12
        Case 2
            r = 6
        Case 4
            r = 3
        Case 12
            r = 1
    End Select

    NewDate = Maturity
    i = 0
    Do While NewDate > Settlement
        i = i + 1
        NewDate = Application.WorksheetFunction.EDate(NewDate, -1 * r)
'        Debug.Print NewDate
    Loop
    DC_CoupNum = i

End Function
Public Function DC_CoupNcd(Settlement As Date, Maturity As Date, Frequency As Integer, Optional NextC As Boolean = True) As Date
    'next coupon date after the settlement date

    'NextC = True ... next coupon date
    'NextC = False ... previous coupon date
    Dim m, o

    Select Case Frequency

        Case 1
            m = 12
        Case 2
            m = 6
        Case 4
            m = 3
        Case 12
            m = 1
    End Select

    o = DC_CoupNum(Settlement, Maturity, Frequency) - 1

    If NextC = False Then o = o + 1


    If Maturity = WorksheetFunction.EoMonth(Maturity, 0) Then 'pokud je Maturity konec mesice
        DC_CoupNcd = Application.WorksheetFunction.EoMonth(Maturity, -o * m)
    Else
        DC_CoupNcd = Application.WorksheetFunction.EDate(Maturity, -o * m)
    End If

End Function

Upvotes: 1

tyg
tyg

Reputation: 15724

I cannot reproduce your EDATE() results. Using the following formula I get exactly the same values as you did for the COUPNCD() function:

=EDATE(maturity;0)    ==>  30-05-2016
=EDATE(maturity;-3)   ==>  29-02-2016
=EDATE(maturity;-6)   ==>  30-11-2015
=EDATE(maturity;-9)   ==>  30-08-2015
=EDATE(maturity;-12)  ==>  30-05-2015
=EDATE(maturity;-15)  ==>  28-02-2015

(with maturity being 30-05-2016)

Please check that you use the correct parameters for the EDATE() function, as indicated above.

Upvotes: 0

Related Questions