CODEWITHSUNDEEP
vbaexcelportfolio
Mariana da Costa
Mariana da Costa

Reputation: 173

Dates of coupon payments of a bond's portfolio - Excel

I'm a little bit newbie about excel-vba but I am trying to construct a function that gives me the dates of coupon payments for a bond.

Giving a simple example I have this two bonds:

Nominal value: 100;300

Coupon rate: 0,06 ; 0,05

CPN_Freq: 4; 2

Months: 3; 6

Settlement: 01-11-2001; 01-11-2001

Maturity: 15-12-2003; 15-05-2005

Basis: 0;1

And what I want is the date of the coupon payments for each bond, for the first it will be:

15-12-2003 15-09-2003 15-06-2003 15-03-2003 15-12-2002 15-09-2002 15-06-2002 15-03-2002 15-12-2001 01-11-2001 (each in a cell)

I made this code but is not working.

     Function CouponDate( Maturity As Date, Settlement As Date, Months 
        As Date)
          For i= Maturity - Months
          If Maturity - Months > Settlement
             CouponDate = i - Months
          Else
             CouponDate= Settlement
          End if
     End Function

Could u give me a help please? Thanks :)

Upvotes: 0

Views: 1256

Answers (1)

tyg
tyg

Reputation: 15724

You cannot use a function because you have multiple return values. You can use a Sub (without return value) that then writes the return values directly in the desired cells. That's what the additional parameter FirstOutputCell is for: it defines the first cell that should be written.

Public Sub CouponDate(Maturity As Date, Settlement As Date, Months As Long, ByRef FirstOutputCell As Range)
    Dim i As Long
    Dim CouponDate As Date

    i = 0
    Do
        CouponDate = DateAdd("m", -i * Months, Maturity)
        If CouponDate <= Settlement Then
            CouponDate = Settlement
        End If
        FirstOutputCell.Offset(i, 0) = CouponDate
        i = i + 1
    Loop While CouponDate > Settlement
End Sub

If you have Maturity, Settlement and Months in the cells B1, B2 and B3 respectively a call of CouponDate Range("B1"), Range("B2"), Range("B3"), Range("B4") would fill the cells B4 through B7 with the coupon dates (if you want the output in columns not rows simple switch the parameters of the Offset() function). Of course you can also call the function directly specifying the parameters: CouponDate #5/30/2003#, #11/1/2001#, 3, Range("B4")

Make sure the first two parameters are of type Date. Passing the string "15-12-2003" might work, depending on the locale that is set for your OS. Better is to either use an Excel cell formatted as date or - as shown above - date literals of the form #m/d/yyyy#.

The above code will work correctly even for maturity dates at the last day of month. If you want to change the code to be more flexible so that you can specify the days between two coupons you would also have to take into account the appropriate day count convention, making this a whole lot more complicated.

Upvotes: 0

Related Questions