Generating Tenor Dates in Excel

To build a volatility surface or quote prices based on market tenors, the expiry dates corresponding to each tenor must be calculated. In Excel, dates are internally stored as integers with 0 = Jan 1, 1900, 1 = Jan 2, 1900, and so on. Current dates are therefore over 40,000 (e.g., June 11, 2014 is 41,801). Within VBA code, dates can be represented using variables with type Long.

First, VBA functions are required to:

• Increment a date to the next business day.
• Decrement a date to the previous business day.

Note that these functions don't take holidays into account. The built-in VBA function Weekday is used to check the input day of the week:

Function nextBusinessDay(InputDate As Long) As Long
    If Weekday(InputDate) = 7 Then
        'Input Date = Saturday
        nextBusinessDay = InputDate + 2
    ElseIf Weekday(InputDate) = 6 Then
        'Input Date = Friday
        nextBusinessDay = InputDate + 3
    Else
        nextBusinessDay = InputDate + 1
    End If
End Function
Function previousBusinessDay(InputDate As Long) As Long
    If Weekday(InputDate) = 1 Then
        'Input Date = Sunday
        previousBusinessDay = InputDate - 2
    ElseIf Weekday(InputDate) = 2 Then
        'Input Date = Monday
        previousBusinessDay = InputDate - 3
    Else
        previousBusinessDay = InputDate - 1
    End If
End Function

Functions are also required to:

• Calculate the spot date from a horizon date.
• Calculate the horizon date from a spot date.

This can be achieved using VBA functions that increment and decrement a given number of business days. In this code it is assumed that the spot date is always T+2 (i.e., two business days after the horizon):

Function businessDayIncrement(InputDate As Long,Increment As Long) As Long
    Dim Count As Long
    businessDayIncrement = InputDate
    For Count = 1 To Increment
        businessDayIncrement = nextBusinessDay(businessDayIncrement)
    Next Count
End Function
Function businessDayDecrement(InputDate As Long,Decrement As Long) As Long
    Dim Count As Long
    businessDayDecrement = InputDate
    For Count = 1 To Decrement
        businessDayDecrement = previousBusinessDay(businessDayDecrement)
    Next Count
End Function
Function getSpotDateFromHorizon(InputDate As Long) As Long
    getSpotDateFromHorizon = businessDayIncrement(InputDate, 2)
End Function
Function getHorizonFromSpotDate(InputDate As Long) As Long
    getHorizonFromSpotDate = businessDayDecrement(InputDate, 2)
End Function

Market tenors can be specified in terms of a number of weeks (e.g., “2W”), months (e.g., “6M”) or years (e.g., “5Y”), or the overnight tenor (e.g., “ON”). Therefore, the getExpiryFromTenor function must contain different logic for these different cases using the rules outlined in Chapter 10. The built-in VBA function DateAdd is used to go from spot date to delivery date, and special cases around trading “end-end,” and so forth, are all ignored in this code:

Function getExpiryFromTenor(Horizon As Long, Tenor As String) As Long
    Dim Count As Long
    Dim SpotDate As Long, DeliveryDate As Long
    If UCase(Tenor) = "ON" Then
        getExpiryFromTenor = nextBusinessDay(Horizon)
    ElseIf Right(UCase(Tenor), 1) = "W" Then
        Count = Left(Tenor, Len(Tenor) - 1)
        getExpiryFromTenor = Horizon + Count * 7
    ElseIf Right(UCase(Tenor), 1) = "M" Then
        Count = Left(Tenor, Len(Tenor) - 1)
        SpotDate = getSpotDateFromHorizon(Horizon)
        DeliveryDate = DateAdd("M", Count, SpotDate)
        getExpiryFromTenor = getHorizonFromSpotDate(DeliveryDate)
    ElseIf Right(UCase(Tenor), 1) = "Y" Then
        Count = Left(Tenor, Len(Tenor) - 1)
        SpotDate = getSpotDateFromHorizon(Horizon)
        DeliveryDate = DateAdd("yyyy", Count, SpotDate)
        getExpiryFromTenor = getHorizonFromSpotDate(DeliveryDate)
    Else
        MsgBox "Invalid Tenor"
        getExpiryFromTenor = -1
    End If
End Function

The expiry dates for market tenors can now be set up in an Excel sheet. It is neater to use a subroutine that places expiry dates onto the sheet rather than using functions in the cells.

The horizon must be input and column headers for the tenors and expiry dates must be named TenorRef and ExpiryDateRef respectively. The horizon can be a user input or the Excel function =Today() can be used. It is nice to format date cells so they also show the day of the week. This is achieved by formatting cells with a custom format e.g.: “ddd dd-mmm-yy”:

The following subroutine can be used to populate expiry dates on the sheet:

Sub populateExpiryDates()
    Dim Count As Long
    Count = 1
    While Range("TenorRef").Offset(Count, 0) <> ""
        Range("ExpiryDateRef").Offset(Count, 0) = _
	getExpiryFromTenor(Range("Horizon"), _
	Range("TenorRef").Offset(Count, 0))
        Count = Count + 1
    Wend
End Sub