Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /home/calcul9/public_html/wp-content/themes/suffusion/functions/media.php on line 580

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /home/calcul9/public_html/wp-content/themes/suffusion/functions/media.php on line 583

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /home/calcul9/public_html/wp-content/themes/suffusion/functions/media.php on line 586

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /home/calcul9/public_html/wp-content/themes/suffusion/functions/media.php on line 589

Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /home/calcul9/public_html/wp-content/themes/suffusion/functions/media.php on line 592
Loading Yield Curve Data into a Google Docs Spreadsheet » The Calculating Investor
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:

This example chart uses yields from 8/21/2012, but the actual spreadsheet will automatically generate a new chart using the most recent yields available.

Notes on the Break-even Inflation Calculation

There are several reasons why the break-even inflation rate is not a perfect estimate of inflation expectations.  Also, the method shown here is not the most accurate way to calculate the break-even rate.

First, regular Treasury bond investors bear a risk of unexpected inflation, and Treasury yields have a risk premium to compensate for this uncertainty.  The real yields based on TIPS do not include this risk premium, so it shows up as part of the break-even rate.  A second distortion is that TIPS are less liquid than Treasuries,  so TIPS yields contain a liquidity premium which Treasuries do not have.  Finally, the TIPS yields are adjusted by CPI, and some investors are skeptical of this inflation measure.  If you don’t believe CPI, then you might be skeptical of the break-even rate since this is a measure of expected CPI.

Another issue with the method shown is that the yields downloaded are the yields for coupon bonds.  These bonds pay a coupon every six months, and this means that the duration of the bond is less than the maturity.  So, the yield for the 30-year bond is a weighted average of interest rates at various payment maturities (all the coupon and principal payments) and is not a targeted measure of the 30-year interest rate.

To be more accurate, we should calculate the break-even rate of inflation using zero-coupon bond yields.  This requires that the zero-coupon yield curve be bootstrapped from the curve for coupon bonds.  Unfortunately, this complicates the analysis considerably.  My previous post on this topic used Octave to bootstrap the zero-coupon curve, but in this simple example I’m sticking with the coupon yields.   Users of the spreadsheet should be aware of this inaccuracy.  Also, note the when interest rates are low, as they are today, the distortion due to this simplification is relatively small, but when rates rise the distortion will be larger.

5 Responses to “Loading Yield Curve Data into a Google Docs Spreadsheet”

  1. I’m not clear why you just can’t use quoted yields on the strips like


    • The strips would work great for building the nominal curve. I agree that it would be more accurate (for what I’m trying to do) than using the coupon bond yields, but I’m not sure if there is an equivalent zero-coupon source for real interest rates? I don’t know if there is any significant market for TIPS strips? Also, the Treasury website data can be automatically pulled into a Google Docs spreadsheet very easily and cleanly since it is available in XML format. I haven’t found a similar XML based data source for strips…but there may be one out there.

  2. I don’t know how robust the quotes are but I do know that at the institutional level the market is pretty active. If an arbitrage exists to recreate a coupon issue it will be exploited quickly.
    It was just a thought!

    • It’s a good idea for the zero coupon nominal yields. I’ve read that the strips market is very liquid, so I think using the strips yields would be accurate. I wasn’t aware that the WSJ had the strips quotes. I’ve looked them up before via my online brokerage site, but hadn’t seen them elsewhere. Thanks for the link! Now, if I could just find a data source that has the strips quotes in an XML format!

      Also, after reading your first comment I did a search on “TIPS strips” to see if there was any traded security which would give me the zero coupon real yields. I found this article from 2008: http://seekingalpha.com/article/107144-tips-strips-redux Not sure if there have been any changes since 2008, but it looks it might be difficult to find the zero coupon securities for the real rates…still need to bootstrap for those I guess.

  3. Thank you for this walkthrough! Very helpful.

    But the XML calls appear to run into a problem when the markets are closed on a weekday. For example, check out the February 2015 Nominal Yields table in your spreadsheet. The market was closed on 2/16/15; if you check the Nominal Yields XML file, you’ll see the date is presented, but there were no yields provided. Since dates and yields are presently separate XML calls, this leads to an inconsistencies moving forward and, in fact, the data are incorrect for dates after 2/16/15.

    Oddly enough, there is no problem for the Real Yield table. Maybe this is a one-time issue?

Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>