US Treasury Rate Analysis Using Excel

The US Government, via the US Treasury, offers a wide range of fixed income securities to investors for the purposes of Government funding. These securities are categorised as US Treasury bills, notes or bonds – all are equivalent, the different terminology simply refers to the different maturities. The are often referred to simply as “US Treasuries”:

  • Treasury Bonds: Mature in either 20 or 30 years
  • Treasury Notes: Mature between 2 and 10 years
  • Treasury Bills: Mature from 4 weeks upto one year

The US Treasury Rate (or Yield) is the effective interest rate that the US Government pays on it’s debt. This is the rate that holders of a US Treasury can expect to receive, and as you might expect this rate varies depending on a number of factors.

Some of the many factors, both US and global, that can influence rates are:

  • How much confidence investors have in the US economy as well as the global economy
  • Current and expected inflation rates
  • Current and expected market uncertainty and volatility

The most influential and closely monitored US Treasury is the 10 Year Treasury Note. It is used as a benchmark rate for setting lending rates, in particular for banks to calculate mortgage rates. It is also used as a gauge of investor confidence.

When investor confidence drops, US Treasury rates tend to drop as investors move out of risky assets and into the safe haven of US Government backed securities – and conversely when investors are confident, which usually means stock markets are buoyant then rates will increase to attract reluctant investors back into Treasuries.

Using Excel we can look at current and historical US Treasury rates. The US Treasury makes this data freely available and you can use the Excel Price Feed Add-in formulas to easily get this data into your Excel spreadsheet.

Using the Excel formula below we can retrieve a historical time series (1000 daily data points) of US Treasury yields:

=EPF.Nasdaq.HistoricLookback("USTREASURY","YIELD", 1000)

And then plot this data (for the 10 Year Treasury Note) using an Excel chart:

US 10 Year Treasury historical data plotted on an Excel chart.

We can see that during 2020, when the world was in the grip of the COVID-19 pandemic, yield/rates dropped dramatically down to around 0.5% as investors moved out of risky assets and US Treasuries became highly sought after. Yields then steadily rose but have dropped back recently as inflation spiked and interest rates rose around the globe to combat it.

In addition to analysing historical trends of a single US Treasury we can analyse the difference between Treasuries of different maturities, otherwise known as the spread.

Below we can see the 10 Year – 2 Year Spread, perhaps the most common spread used for analysis, and we can see that it is currently “inverted”, i.e. below zero – the 10 Year yield is less than the 2 Year yield. This is often recognised as a leading indicator of a forthcoming recession:

US 10 year minus 2year treasury yield spread

We can use another of the Excel Price Feed formulas, such as the one below:

=EPF.Nasdaq.Last("USTREASURY","YIELD","10 yr")

to retrieve a single data point, the current yield for a single maturity (in this case the 10 Year). Applying a simple data bar visualization in Excel enables us to see how current yields across maturities compare:

US Treasury Yield Rates in an Excel spreadsheet

We can now clearly see that the 10 year currently has the lowest yield, whereas the 6 month rate has the highest – a spread of over 1%.

I hope this article has given a good overview of what US Treasuries are and how you can use Excel and the Excel Price Feed Add-in formulas to bring US Treasury data into your Excel spreadsheet.

[Click here to download the spreadsheet used in this article]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s