### Charting a 30yr Zero-Coupon Bond Investment

My very good friend and fellow bond investor recently shared with me an exchange he had with one of his stock fanatic buddies. A common myth among the fanatics is that stock investments, over time, generate higher rates of return than bond investments. In response to this assertion my friend presented the following chart he was pointed to by notable bond investor A. Gary Schilling:

The reply he received was both typical and painful to read:

Fanatic: “Where are you getting 18% from bonds? That seems insanely high. That chart looks like it was from the back of a cereal box.”

In the stock fanatic’s delusional narrative, after all, bonds *shouldn’t* generate higher rates of return than stocks, and when they do, they don’t – or they can’t. Either the data is phony, the observers are insane, or the information is all propaganda.

But the first question – where, or how, did the bond investment generate an 18% return – is non-trivial. For example, it might not be readily apparent to an inexperienced investor exactly how 30-year US Treasury Bonds issued in January 2014 with a 3.75% coupon generated over a 16% return-on-investment as of the end of July. How can a note that pays a fixed, 3.75% annual coupon return over 16% in just 6 months?

Well, since it’s such an easy question to answer and such a fun exercise to perform on a Sunday afternoon, let’s walk through a reconstruction of Mr. Schilling’s chart using data we pull from the Federal Reserve’s website. I think this will be instructive, as we will learn how to pull historical data from a primary source and analyze it using some basic financial functions in MS Excel. **[Note: we will assume the reader has at least a high school education and is familiar with the concepts of exponential growth, compounding, and discounting.]**

Our procedure can be roughly outlined as follows:

- Rate history construction
- Download relevant data using the Federal Reserve’s Data Download Program
- Reconstruct any missing data via linear interpolation

- Investment performance calculation
- Compute monthly % price appreciation/depreciation
- Compute monthly accrued interest
- Compute monthly net return-on-investment

- Analysis
- Dollar growth over time
- Compounded annualized growth rate
- Chart comparison / overlay Schilling chart

**Step 1a – Downloading Rate Data**

Open a web browser and point to http://www.federalreserve.gov. I strongly recommend adding this page to your ‘Favorites’ list. Scroll to the bottom right-hand side of the page and click the “Selected Interest Rates – H.15” link. At the top of that page, toward the right, click on the link to the Federal Reserve’s Data Download Program.

We will build a data package consisting of historical interest rate data which we can then import into an Excel sheet for analysis. Specifically, we need a monthly time series of nominal 30-yr Treasury bond rates going back to October 1981. To begin, click on the “Build package” button on the left-hand side of the page.

To assemble your package, select the “Selected Interest Rates” data set in step 1; set the instrument to “Treasury constant maturities/Nominal” under step 2; set the maturity to “30-year” in step 3; and set the frequency in step 4 to “Monthly”. Click the “Add to package” button which should take you to the “Review your package” page. Click on the “Format package” button.

Click the “Dates” radio button and set the date range from June 1981 to July 2014. We don’t mind downloading the bit of excess data surrounding the relevant range from Oct 1981 to February 2013 depicted in the Schilling chart. Make sure the file type is set to “CSV (comma delimited)”, then click the “Go to download” button at the bottom.

On the “Download your package” page, click the “Download file” button at the bottom, then open the downloaded file. You should see your data package open in Excel with the unformatted time series beginning in Row 7 of the worksheet. Copy-paste the unformatted time series into a new worksheet so that you have room to manipulate the data without having to contend with any garbage at the top.

For charting purposes, and in general, it is easier to work with formatted data. Create a formatted date column in Column B of the worksheet and delete the unformatted Column C. At this point we insert a couple of blank columns to the left of our work area just to give us a little more room. Call me claustrophobic, but I like having plenty of room to work. I also like to create a month-index column (which can go in Column C), which will make it easy to count time later on down the road.

Once you’ve got your index column you’re pretty much ready to dig into the numbers. But before we move on, we have to address one more issue – incomplete data.

**Step 1b – Reconstructing Incomplete Data**

Remember in high school Algebra class when they taught you “Lines and Graphing”, “slope-intercept” and “point-slope” form, “” and all that crap, and you thought, “*When am I ever going to use any of this stuff in real life?*” Well, here’s a good example of when. It comes in handy when you need to complete a sequence of missing numbers, and that’s exactly what we have here.

In our downloaded data package we are missing interest rate data from March 2002 to February 2006, as indicated by the “ND” in Column E in months 249 through 295. We need to reconstruct this data, or at least loosely approximate it so that we have a complete time series to analyze – rather than two distinct segments with a “hole” in the middle. To accomplish that, we interpolate the data using a linear approximation for months 248 through 296. The resulting data – although completely made up – will allow us to work on one complete data set with relative ease.

We use the linear “point-slope” form to arrive at our approximation formula, which we enter into Column F beginning in month 248. Drag the formula down the length of the missing data range, then format the data to show only two decimals. We’re not neat-freaks, but we appreciate order and uniformity.

Finally, we merge our segmented data into a single column in Column G. You can copy-paste or use a simple conditional formula as demonstrated: IF not “ND”, use data from Column E, ELSE use data from Column F. Drag the conditional formula down the entire range and format as a percentage with two decimals.

Now that we have the desired time series we can get down to business.

**Step 2 – Investment Performance Calculation**

We begin in Column H, month 4, which is October 1981. First, we use Excel’s “Present Value” function (PV) to compute the bond price given the particular interest rate for that month. We need to supply the PV function with four parameters: the current interest rate (*rate*), the number of payments left on the bond (*nper*), the bond’s coupon payment (*pmt*), and the bond’s face value (*fv*). For the rate parameter we use the interest rate in Column G. We set the period to 30, as this is a 30yr bond. We set the coupon payment to zero and the face value of the bond to 100. Copy the same formula for November so that we now have a “starting” price and an “ending” price for the month of October. Move over to Column I and compute the % price appreciation/depreciation for that month.

The next step is to compute any “accrued interest” for the month. At this point the stock fanatics will no doubt be jumping up and down in their seats – why are we computing accrued interest if the bond doesn’t pay a coupon? That’s actually not a bad question, but the answer is probably obvious to bond investors:

At the beginning of the each month we hold a bond with a maturity of 30 years, or 360 months. But by the end of the month the maturity of the bond is only 359 months – and that will alter the result of the PV calculation by an amount *approximately equal to one month of accrued interest *on an equivalent coupon-bearing note. Thus we need to correct our % price appreciation value by adding whatever one month of accrued interest would be had our bond paid a coupon.

To do this in the Excel sheet, go to Column J, and for the month of November simply compute 1/12^{th} the yield in Column G for October. Of course you could always create an additional column and compute PV with a period equal to 359/12, but the first method is easier and the results are basically the same.

Now we are ready to go into Column K and compute the total return-on-investment for the month of October. Just add the values in Column I and Column J. If you followed instructions carefully you should arrive at a 43.12% return for the month of October.

Let me repeat – you get a 43.12% return for the month of October. Please don’t tell me there’s something wrong with Excel’s PV function. We assumed you were familiar with the concepts of exponential growth, compounding, and discounting. I will discuss the PV function in another post.

**Step 3 — Analysis**

Our last bit of work is to produce a column that tracks the dollar growth of our investment. Then we can chart the investment and compute its compounded annual growth rate.

Just as in the Schilling chart, we begin with a $100 dollar investment in October 1981. In Column L, enter a value of 100 for October, then in the cell below, use a formula to increase the October value by the % total return in Column K. The row corresponding to month index 5 should now contain formulas in Columns H through L. Select these cells and drag the formulas all the way down to the end of our data range. In the final month, June 2014, we see that our $100 dollar investment has grown to $19,500.23 over a 391-month period. Note that in February 2013 – the last month in the Schilling chart – our investment was worth $20,005.79.

Below the dollar growth range we compute the compounded annual growth rate of the investment. We arrive at a final result of 17.57%. Had we interrupted our dollar growth in February 2013 we would have yielded over 18%.

Finally, we select Columns D and L, and insert an XY-Scatter with Straight Lines chart. Again, just to be orderly, we move the chart into a new sheet and apply a bit of formatting.

**Investment Cereal**

Incidentally, I checked the back of my Kellogg’s Raisin Bran Two Scoops Value Size cereal box – and it actually does give investment advice. Yes, I read it with a sense of healthy skepticism, but I don’t automatically write it off as garbage either. I’m probably more paranoid when I listen to Ivy League PhD’s spout nonsense about lack-of-demand and zero-interest-rate lower-bounds. When it comes to investing, usually you’re best off when you do your own research.

Filed under: Uncategorized | Leave a Comment

## No Responses Yet to “Charting a 30yr Zero-Coupon Bond Investment”