Yield Curves and Break-even Inflation

In a previous post, I showed how to calculate the break-even rate of inflation using the real and nominal yield curve data available from the U.S. Treasury website.

In this post, I will show how to automatically import the most up-to-date yield curve data into a Google Docs Spreadsheet using the “ImportXML” function. This yield curve data is useful for many financial calculations, but in this post I will again use the yield curve data to do a rough estimate of the break-even inflation rate.

Read the “Notes on the Break-even Inflation Calculation” section for an explanation of the break-even rate and a clarification on why the method shown only gives an approximation of the break-even rate.

ImportXML Function in Google Docs

The ImportXML function can be used to import data from XML files. The syntax of the command is:

=importXml(<url>,<xpath expression>)

The Treasury website has XML format files showing real yields for the current month and nominal yields for the current month.

The ImportXML example shown here will import the real 5-year yields into the spreadsheet.

=importXml(“http://www.treasury.gov/resource-center/data-chart-center/interest-rates/Datasets/real_yield.xml”, “//TC_5YEAR”)

The “//TC5_YEAR” is an xpath expression which selects all the nodes named “TC_5YEAR” in the target document.   Other maturities in the document can be selected by changing the node name.  For example, the 30-year real yields are selected by changing the xpath expression to “//TC_30YEAR”.

Example Google Docs Spreadsheet

I have created an example Google Docs spreadsheet which uses the ImportXML data to download the real and nominal yields at several maturities.  The example sheet is read-only, but if you would like to customize you can select “make a copy” from the “File” pull-down and edit the copy of the spreadsheet.  You must be logged into your Google Docs account to make a copy.

The spreadsheet automatically imports the yield information for the current month to the sheet labeled “Yields”.   The “Summary” sheet extracts the most recent set of daily yield curve data from the “Yields” sheet and uses it to estimate the break-even inflation rate.  The yield curves and the breakeven inflation curves are plotted in the “Summary” sheet as shown here:

Continue reading »