**Added:**Here are

*permanent*links to the spreadsheet:

- Online: BogleheadsBondFundSimulator (Google Sheets).
- Download: BogleheadsBondFundSimulator.xlsx (in Microsoft Office Excel format).
- Download: BogleheadsBondFundSimulator.ods (in LibreOffice Calc format).

To summarize the problem: Shiller provides long-term (10-year government bond), and short-term (1-year government bill) historical rates. While we can reasonably expect a 1-year bill to deliver a 1-year total return equal to its interest rate (when bought at par), the same cannot be said of the 1-year total return of a 10-year bond. After one year, the total return of a 10-year bond is not only due to its coupon, but also to the change in value of its principal, as the bond has become a 9-year bond in a new rate environment, yet its coupon has not changed.

Now for the solution.

Naive Approach

Some people derive an annual total return for a 10-year bond as follows. They assume that the bond is bought at par and sold one year later, in a new rate environment. As the bond has a 9-year maturity, one year later, and 9-year rates are not available, they approximate the price of the bond using the new 10-year rate. Here's the actual calculation.

**RB:**10-year rate when the bond is bought.

**RS:**10-year rate when the bond is sold, one year later.

**PV( interest_rate, number_payments, payment, future_value ):**Financial function that calculates the

*present value*of an investment.

1_year_bond_return = (coupon + sell_price) / buy_price = ( (1 X RB) - PV( RS, 9, 1 X RB, 1 ) ) / 1

Note that the PV() function returns a negative number, representing an amount paid for the investment, whereas

*payment*and

*future_value*are positive numbers, representing amounts received from the investment.

So, what's the problem, you'll ask me? Actually there are two problems.

The first one is obvious. As we use a 10-year rate to approximate a 9-year rate, we introduce an error in our calculation. In a rate environment where shorter-term rates are usually lower than longer-term rates (this has been the case since the 1930s), this leads the above formula to underestimate the value of the bond when it is sold. So, it's not too bad. Maybe the actual 1-year return using a proper 9-year rate would have been a tad higher.

The second problem is more serious. If we were to calculate the 1-year return of a 10-year bond using Shiller's historical rates and use them to represent historical bond returns, we would effectively be looking at the historical returns of a very special type of bond fund: a bond fund which buys a single 10-year bond each year and, one year later, receives a coupon, sells the now 9-year bond, and uses the proceeds to buy the new 10-year bond. Such a bond fund would have a high duration, much higher than the usual bond funds used by Bogleheads (e.g. short or intermediate-term bond funds). Higher duration implies higher volatility in the investor's

*safe*part of his portfolio. That's not what we want to model.

A Simple Improvement

In the VPW backtesting spreadsheet, I tried to correct for the high duration of the naive approach by using a set of two bonds: a 10-year bond and a 1-year bill. So, the VPW's hypothetical bond fund works like this: each year, the fund uses half of its money to buy a 10-year bond and the other half to buy a 1-year bill. One year later, the fund receives two coupons, the capital of the bill, and sells the now 9-year bond on the market. It then uses the money to buy a new pair of bonds. The average maturity of the pair, at the beginning of the year, is (10 years + 1 year) / 2 = 5.5 years. At the end of the year, it's 4.5 years, for an overall average maturity of 5 years. Its duration* is a little lower, and thus in line with the duration of typical Bogleheads bond funds.

* Duration represents the weighted average maturity of all payments (coupons and principals), and is thus necessarily lower than average maturity for a fund of bonds with coupons.

Calculating an annual return for the bond-bill pair is simple: you take the average of the 1-year return of the bond and the rate of the bill.

1_year_bond_bill_return = (1_year_bond_return + bill_rate) / 2

You'll tell me:

*There ya go! You've got what you need. Leave me alone.*Right?

But, I'm still annoyed. The bond-bill pair is not how a bond fund usually works. To top it off, it accumulates calculation errors due to the unavailability of 9-year rates.

A Better and Self-Correcting Approach

Remember the error due to using 10-year rates in lieu of 9-year rates? What if we could build a model where any such error would be guaranteed to be corrected over time? What if this self-correcting model was much closer to how bond funds actually work?

Here's my proposal.

We have both 10-year bond rates and 1-year bill rates. This means that we can precisely model the cash flows of a 10 year bond bought at par and sold one year prior to maturity. Here's an example:

In 1960, the 10-year bond rate was 4.72% and in 1969 the 1-year bill rate was 8.05%. So, in 1960, we could buy a 10-year government bond for $100 with an annual coupon of $4.72. In 1969, this bond still had a $4.72 coupon and $100 par value but had only one year left until maturity in a 8.05% 1-year bill environment. The value of this bond, in 1969, was thus:

value = -PV( 8.05, 1, 4.72, 100 ) = $96.92

To double check this, just consider that if you pay $96.92 and one year later you receive $4.72 (coupon) and $100 (principal), your return was: ($4.72 + $100) / $95.92 - 1 = 8.05%.

So, here is the precise sequence of cash flows (nothing is

*approximated*):

**1960:**-$100.00

**1961:**$4.72

**1962:**$4.72

**1963:**$4.72

**1964:**$4.72

**1965:**$4.72

**1966:**$4.72

**1967:**$4.72

**1968:**$4.72

**1969:**$4.72 + $96.92 = $101.64

Neat, isn't it?

Now, we can go one step further. Like we could calculate the precise cash flows of a bond bought in 1960 and sold in 1969, we could also calculate the precise cash flows of a bond bought in 1961 and sold in 1970. And so on.

In other words, we could model a bond fund that worked like a bond ladder, except that it sold its bonds one year prior to maturity, like real-life bond funds do. This bond fund would, at the start of each year, contain 9 bonds maturing in 10, 9, 8, ..., 2 years. One year later the fund would sell its 1-year bond, combine the proceeds of the sale with all received coupons to buy a new 10-year bond.

What's nice about this model is that nothing is approximate. All coupons are reinvested at a known rate (the current 10-year rate at the time of reinvestment). All cash flows are thus precise, based on available historical data.

But, this still does not give us annual return data; it only gives us the internal cash flows of the bond fund.

To calculate annual returns, we would need to calculate the present value of the bond fund, every year. To do that, we would need the historical 9-year, 8-year, ..., 2-year rates. But we don't.

What can we do? We can calculate approximate 9-year, 8-year, ..., 2-year rates and use them to do our

*present value*calculations.

Won't that introduce errors in the calculated

*present value*of the bond fund, every year? Yes, it will. But, the thing is that any error will necessarily be compensated by

*offsetting errors*in later years. This is because all internal cash flows are precise. It simply cannot be otherwise. Don't you love mathematics?

In other words, if we use approximate 9-year, 8-year, ..., 2-year rates to derive annual returns for our bond fund, any error will self-correct over time, within 9 years, to be precise. But other temporary errors will have been added for newer bonds, so, we'll never have exact historical annual returns at any point in time, but when looking at cumulative returns over longer periods, returns will become increasingly precise.

How do we come up with such approximate rates? I propose to simply use a linear approximation:

**2_year_rate**= 1_year_rate + (10_year_rate - 1_year_rate) X (2 - 1) / (10 - 1)

**3_year_rate**= 1_year_rate + (10_year_rate - 1_year_rate) X (3 - 1) / (10 - 1)

**4_year_rate**= 1_year_rate + (10_year_rate - 1_year_rate) X (4 - 1) / (10 - 1)

**5_year_rate**= 1_year_rate + (10_year_rate - 1_year_rate) X (5 - 1) / (10 - 1)

**6_year_rate**= 1_year_rate + (10_year_rate - 1_year_rate) X (6 - 1) / (10 - 1)

**7_year_rate**= 1_year_rate + (10_year_rate - 1_year_rate) X (7 - 1) / (10 - 1)

**8_year_rate**= 1_year_rate + (10_year_rate - 1_year_rate) X (8 - 1) / (10 - 1)

**9_year_rate**= 1_year_rate + (10_year_rate - 1_year_rate) X (9 - 1) / (10 - 1)

In summary, I am confident that such

*self-correcting*returns would be of higher quality than those of the

*naive approach*(as used by cFIREsim) or of the

*bond-bill pair approach*(as used by the VPW backtesting spreadsheet).

I have implemented this in a spreadsheet and intend to include it in the next revision of the VPW backtesting spreadsheet.

Here are my questions:

- Is there any comment about the proposed approach?
- Would there be any interest to add such calculated pre-1972 bond returns, as well as calculated stock returns, based on Prof. Shiller's data set, into Simba's backtesting spreadsheet?