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.<\/p>\n
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.<\/p>\n
ImportXML Function in Google Docs<\/h6>\n
The ImportXML function can be used to import data from XML files.\u00a0The syntax of the command is:<\/p>\n
=importXml(<url>,<xpath expression>)<\/em><\/p>\n The Treasury website has XML format files showing real yields for the current month<\/a> and nominal yields for the current month<\/a>.<\/p>\n The ImportXML example shown here will import the real 5-year yields into the spreadsheet.<\/p>\n =importXml(“http:\/\/www.treasury.gov\/resource-center\/data-chart-center\/interest-rates\/Datasets\/real_yield.xml”, “\/\/TC_5YEAR”)<\/em><\/p>\n The “\/\/TC5_YEAR” is an xpath expression which selects all the nodes named “TC_5YEAR” in the target document. \u00a0 Other maturities in the document can be selected by changing the node name. \u00a0For example, the 30-year real yields are selected by changing the xpath expression to “\/\/TC_30YEAR”.<\/p>\n I have created an example Google Docs spreadsheet which uses the ImportXML data to download the real and nominal yields at several maturities<\/a>. \u00a0The 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. \u00a0You must be logged into your Google Docs account to make a copy.<\/p>\n The spreadsheet automatically imports the yield information for the current month to the sheet labeled “Yields”. \u00a0 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.\u00a0 The yield curves and the breakeven inflation curves are plotted in the “Summary” sheet as shown here:<\/p>\n 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.<\/p>\n There are several reasons why the break-even inflation rate is not a perfect estimate of inflation expectations. \u00a0Also, the method shown here is not the most accurate way to calculate the break-even rate.<\/p>\n First, regular Treasury bond investors bear a risk of unexpected inflation, and Treasury yields have a risk premium to compensate for this uncertainty. \u00a0The real yields based on TIPS do not include this risk premium, so it shows up as part of the break-even rate. \u00a0A second distortion is that TIPS are less liquid than Treasuries, \u00a0so TIPS yields contain a liquidity premium which Treasuries do not have. \u00a0Finally, the TIPS yields are adjusted by CPI, and some investors are skeptical of this inflation measure. \u00a0If you don’t believe CPI, then you might be skeptical of the break-even rate since this is a measure of expected CPI.<\/p>\n Another issue with the method shown is that the yields downloaded are the yields for coupon bonds.\u00a0 These bonds pay a coupon every six months, and this means that the duration<\/a> of the bond is less than the maturity. \u00a0So, 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.<\/p>\n To be more accurate, we should calculate the break-even rate of inflation using zero-coupon bond<\/a> yields. \u00a0This requires that the zero-coupon yield curve be bootstrapped from the curve for coupon bonds. \u00a0Unfortunately, this complicates the analysis considerably. \u00a0My previous post on this topic used Octave to bootstrap the zero-coupon curve<\/a>, but in this simple example I’m sticking with the coupon yields. \u00a0 Users of the spreadsheet should be aware of this inaccuracy. \u00a0Also, 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.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/www.calculatinginvestor.com\/wp-json\/wp\/v2\/posts\/4113"}],"collection":[{"href":"http:\/\/www.calculatinginvestor.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.calculatinginvestor.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.calculatinginvestor.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.calculatinginvestor.com\/wp-json\/wp\/v2\/comments?post=4113"}],"version-history":[{"count":57,"href":"http:\/\/www.calculatinginvestor.com\/wp-json\/wp\/v2\/posts\/4113\/revisions"}],"predecessor-version":[{"id":4172,"href":"http:\/\/www.calculatinginvestor.com\/wp-json\/wp\/v2\/posts\/4113\/revisions\/4172"}],"wp:attachment":[{"href":"http:\/\/www.calculatinginvestor.com\/wp-json\/wp\/v2\/media?parent=4113"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.calculatinginvestor.com\/wp-json\/wp\/v2\/categories?post=4113"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.calculatinginvestor.com\/wp-json\/wp\/v2\/tags?post=4113"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Example Google Docs Spreadsheet<\/h6>\n
![\"\"](\"http:\/\/www.calculatinginvestor.com\/wp-content\/uploads\/2012\/08\/yield_curve_google.png\" "\\"yield_curve_google\\"")
<\/a><\/p>\nNotes on the Break-even Inflation Calculation<\/h6>\n