In an effort to expand the amount of free historical market data available to the public, I’ve created a calculator that can model realistic bond fund returns from the underlying interest rates. The spreadsheet is available for anyone to download and use for modeling their own bond funds, and this page is intended to be a central hub for explanations and updates.
/// Bond Index Calculator ///
(Updated 9-23-2022)
CONTENTS
- Overview
- How bonds work
- How a bond index fund works
- Calculation details
- Results & Comparisons
- Conclusions
- Disclaimers
- Feedback
Overview
The Bond Index Calculator is based on source data from the US Treasury and translates yield curves into returns that mirror a real-world index methodology. It does this by modeling the full maturity range of the desired bond index fund and calculating both the coupon payments and price appreciation of each rung on the ladder. The final results are no substitute for actual data from a specific fund, but they track the general performance histories reasonably well and are suitable for general backtesting purposes independent of any specific index provider.
How Bonds Work
Bonds are fixed income contracts where an investor loans money to an entity in return for fixed future payments over a set timeframe. These payments usually include regular interest payments (sometimes called the “yield” or the “coupon”) in addition to the return of the original money at the end of the term.
Because of how the bond market values these contracts, the value of a bond will change over time along with interest rate changes. A bond with a fixed interest rate will be worth more when rates fall and will be worth less when rates rise, so when you decide to sell your bond it may be worth more or less than what you originally paid for it. The total return of a bond includes both the interest payments and the capital appreciation.
The interest rate of a bond will vary depending on the length of the contract (called the “maturity”). There are a few historical exceptions, but usually the market demands higher payments the longer the maturity. So short term bonds generally pay less than long term bonds. In addition, pricing on long term bonds tends to be more volatile than that on short term bonds. This means that long term bonds are more sensitive to interest rate changes and may rise and fall in value more sharply than short term bonds.
The full spectrum of interest rates for bonds offered for sale at any one time over all maturities is called the “term structure of interest rates” or the “yield curve”. There are two types of yield curves available: spot and par. While they are closely related, par curves account for the payments along the way and are what you need to accurately model a bond fund. This information is a lot trickier to find than you might think, but government central banks are usually really good about publishing historical yield curves for treasury bonds (or bunds, gilts, etc. depending on your home country). You can also calculate par yields from spot curves, but that’s outside the scope of this walkthrough.
How A Bond Index Fund Works
In its most general sense, an index fund tracks the performance of a given subset of the market with no active management involved. It sets a strict rules-based definition of “the market” it is following and builds its portfolio accordingly. Index providers that construct and track indices include organizations like CRSP, MSCI, and Barclays. Companies like Vanguard purchase the index data from one of these companies and use it to build their funds. For example, BND is currently based on the Barclays Capital U.S. Aggregate Bond Index.
Bond index funds are actually relatively simple compared to many of their stock brethren. In short, they purchase and maintain a fixed basket of bonds on the open market over a predetermined range of bond maturities and they forward all of the interest payments to bond fund holders.
Bond funds differentiate themselves by the range of bonds they hold. For example, some funds hold only short term bonds while others hold only long term bonds. While every fund is different, common bond index fund maturity ranges look like this:
Short term bonds: 1-3 years
Intermediate bonds: 3-10 years
Long term bonds: 10-30 years
In general, bond index funds do NOT hold bonds all the way to maturity, preferring to sell bonds when they reach the low end of the range for their index and reinvest that money into new issues. So when you see the range as 1-3 years above, bonds are sold (not purchased) when they hit one year remaining.
Calculation Details
In order to calculate the return of a bond index fund, you first need accurate PAR yield curves for the underlying bonds. Each bond maturity will have an associated interest rate. From that rate data, you construct a bond ladder including only the bond maturities appropriate for the bond fund you’re looking to model.
For every rung on that ladder, you next calculate the two sources of bond profit: the interest payment and the bond appreciation due to any interest rate changes. The interest payment is straightforward and is simply the interest rate at the beginning of the term. The capital appreciation due to interest rate changes is a little trickier to calculate, but Excel has a nice PV function to help.
Capital Appreciation = -PV(R2,M-1,R1,1)-1
Where:
R1 is the yield of the bond of maturity M at the start of the term
R2 is the yield of the same bond of maturity M-1 at the start of the next term
M is the maturity of the bond rung at the start of the term
Note that the equation assumes that coupon payments are made annually. To model capital appreciation with semi-annual payments, the equation looks like this:
Capital Appreciation = -PV(R2/2,(M-1)*2,R1/2,1)-1
Each country may have different rules for coupon payments. For example, the United States pays Treasury interest semi-annually while Germany pays Bund interest annually. The difference between the two calculation methods is actually very small (no more than about 0.1%), but if you’re looking for precision be sure to use the right equation.
For each rung of the ladder, add the interest payment and the capital appreciation to get the total return. The total return for the bond index fund as a whole is simply the average return* of each rung specified for the fund.
(*) Real-world bond index funds also weight the rungs according to the market size of each bond issue, and unfortunately this market weighting data is extremely difficult to find. Luckily, as you can see below the returns numbers from equally-weighted rungs already compare quite favorably to known index fund returns so there’s no need to go overboard with the calculations.
Following with typical bond fund convention, the calculator sells all bonds with only a year remaining and does not hold them all the way to maturity.
Results & Comparisons
Here’s how the calculated index returns track known indices over the past 30 years. Short, intermediate, and long funds are modeled using the maturity ranges listed earlier in the article and match the methodologies of the compared real-world indices. All index data is from Morningstar. The red “PC” series represents the Portfolio Charts calculations. Click to enlarge.
Conclusions
In general, the calculated bond indices match the known good numbers using the same maturity ranges quite well and the process holds up in different countries. You see a little more error the longer the average maturity of the bond index fund, but that is to be expected with the additional volatility of the underlying bonds.
Is the data perfect? Of course not! But the goal is not to provide a substitute for the precise institutional sources that I’m sure are well worth the price. For a free data series that fills in the gaps and greatly extends the history coverage of modern index funds for general backtesting purposes, I feel like it’s pretty reasonable.
Disclaimers
The data carries absolutely no guarantee of accuracy. It is merely an approximation of past returns based on freely available sources, and it may contain errors.
The exact methods of real-world index funds are far more complicated than can be modeled here with the provided data. The goal is be reasonably realistic, not precise.
The numbers you find here may not accurately represent the performance of any individual index fund. Always thoroughly research any fund you purchase.
Feedback
While I’ve put a lot of work into this and feel pretty good about the results, I want the numbers to be as accurate and helpful as possible and am very open to input. If anyone has any questions, comments, corrections, or recommended improvements, please don’t hesitate to contact me.