hi, please see code below...i keep on get in a "subscript out of range" error for the array v(n), my option base is 0 even if i reset the array to v(n-1) it does not accept. is the something i am missing Public Function datevec(d1 As Variant, d2 As Variant, freq As Variant) Dim v() Dim n As Long Dim c1, c2 As Date ReDim v(n) As Variant If IsDate(d1) = True And IsDate(d2) = True Then c1 = DateSerial(Year(d1), Month(d1), Day(d1)) c2 = DateSerial(Year(d2), Month(d2), Day(d2)) n = Application.Round(VBA.DateDiff("d", c1, c2) / 365 * freq, 0) Select Case freq Case 1 For Each i In v(n) v(i) = VBA.DateAdd("yyyy", i + 1, c1) Next i Case 2 For Each i In v(n) v(i) = VBA.DateAdd("q", 2 * (i + 1), c1) Next i Case 4 For Each i In v(n) v(i) = VBA.DateAdd("q", i + 1, c1) Next i Case 12 For Each i In v(n) v(i) = VBA.DateAdd("M", i + 1, c1) Next i End Select datevec = v End If End Function

A quick explanation of what I am trying to achieve: for each bond that is selling in the stock market, there will be an issue date, maturity date, frequency of interest payments and lastly the interest rate. What I want is to have a function that allows me to input a date any time before the bond matures, and give me as an output a series of outstanding cashflows and the corresponding dates. Explanation of the variables in the code Valdate = todays date Ipos = indicator +1 or -1 depending if I have bought or borrowed the bond Notional = amount of bond D1 = date of bond issue D2 = date of maturity Freq = frequency of interest payments Coupon = interest rate V() = vector of all cashflow dates for the bond Cashflow = 2 by p matrix of the bond (dates and corresponding amounts) Code: Function bondcashflow(valdate, ipos, notional, d1, d2, freq, coupon) Dim p, m As Integer Dim v() Dim cashflow() ReDim cashflow(2, p) As Variant Dim n As Long n = Application.Round(DateDiff("d", d1, d2) / 365 * freq, 0) For i = 0 To n - 1 If freq = 1 Then v(i) = DateAdd("yyyy", 1 * i, d1) ElseIf freq = 2 Then v(i) = DateAdd("q", 2 * i, d1) ElseIf freq = 4 Then v(i) = DateAdd("q", 1 * i, d1) ElseIf freq = 12 Then v(i) = DateAdd("m", 1 * i, d1) End If m = 0 If valdate < v(i) Then m = m + 1 End If Next i p = n - m For k = 1 To p cashflow(1, k) = v(k + m - 1) Do While k < p cashflow(2, k) = ipos * coupon * notional Loop Next k cashflow(2, p) = ipos * (notional + coupon * notional) bondcashflow = cashflow End Function

Please see my code below, for some reason it fails. can you help definitions: valdate = date today ipos = an indicator = +1 or -1 depending if a bond was bought or borrowed. notional = amount of bond bought or borrowed d1 = date when bond was launched d2 = date when bond matures freq = frequency of interest repayments coupon = interest rate used to calculate coupon repayments Function bondcashflow(valdate, ipos, notional, d1, d2, freq, coupon) Dim p, m As Integer Dim v() Dim cashflow() ReDim cashflow(2, m) As Variant Dim n As Long n = Application.Round(DateDiff(dateinterval.Day, d1, d2) / 365, 0) * freq For i = 0 To n - 1 If freq = 1 Then v(i) = DateAdd(dateinterval.Year, i, d1) ElseIf freq = 2 Then v(i) = DateAdd(dateinterval.Month, 6 * i, d1) ElseIf freq = 4 Then v(i) = DateAdd(dateinterval.Month, 3 * i, d1) Else: v(i) = DateAdd(dateinterval.Year, 12 * i, d1) End If m = 0 If valdate < v(i) Then m = m + 1 End If Next i p = n - m For k = 0 To p - 1 cashflow(k, 1) = v(m) Do While k < p - 1 cashflow(k, 2) = ipos * coupon * notional Loop Next k cashflow(p - 1, 2) = ipos * (notional + coupon * notional) bondcashflow = cashflow End Function

given a vector of dates (past dates and future ones), how can one extract a new vector showing remaining future dates from today :((

I have an asset (bond) with start date, coupon payment dates and the maturity date. I would like to write a programm to give me the next cashflow dates any time during the life of the bond. :((