Excel: U.S. Treasury Settlement Dates

Fixed Income US Treasury traders may require the settlement date for calculating yields in Excel. Unfortunately, there is not an RTD function for pulling in the settlement date from CQG. However, there is a function in Excel that is perfect for the task: the WORKDAY.INTL() function.

According to Excel’s Help file: “This function returns the serial number of the date before or after a specified number of workdays with custom weekend parameters. Weekend parameters indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays.”

In the downloadable Excel sample, cell B1 uses the RTD formula for today’s date for the BrokerTec 2-year T-Note, symbol BTC02. Any US Treasury symbol can be used.

=RTD("cqg.rtd", ,"ContractData", "BTC02", "T_Date",, "T")

The Settlement date in cell B2 uses the WORKDAY.INTL() function:

=WORKDAY.INTL(B1,1,1,F2:J12)

Looking at the function arguments we see that the start date is from cell B1.

Treasuries settle the next day business day from the trade date. Therefore, the second parameter is “1”. The third parameter, Weekend, is set to 1, which indicates that Saturday and Sunday are not working days. Finally, this function has a feature where you can list holiday dates or access a table (here, cells F2:J12 are the holiday dates) and the function will not consider a holiday a business day.

For example, Friday (1/14/2022) is the business day before Martin Luther King’s birthday (1/17/2022) so Excel knows to return Tuesday (1/18/2022) for the settlement date.

This holiday table goes out to 2025.

Downloads

Tags

Disclaimer

Trading and investment carry a high level of risk, and CQG, Inc. does not make any recommendations for buying or selling any financial instruments. We offer educational information on ways to use our sophisticated CQG trading tools, but it is up to our customers and other readers to make their own trading and investment decisions or to consult with a registered investment advisor. The opinions expressed here are solely those of the author and do not reflect the opinions of CQG, Inc. or its affiliates.