问每月债券的息票日期- excel

EN

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

=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