Bond Index Calculator

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
User avatar
Topic Author
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

Bond Index Calculator

Post by Tyler9000 »

I've long been a fan of Longinvest's Bond Fund Simulator and how it has contributed so much to Simba's Backtesting Spreadsheet, but for my own purposes I needed something a little easier to modify to quickly model different types of funds from varying markets and maturity ranges. So I built my own bond index calculator using a slightly different method, and in the same collaborative spirit that Longinvest shows I thought it would be a good idea to share my work. You can read an explanation and download it for yourself here:

https://portfoliocharts.com/bond-index-calculator/

Now I'm not going to claim it's better by any means, as many of the posters here can run circles around me when it comes to detailed bond knowledge. But I do like that it's simple, flexible, and demonstrably matches known good returns reasonably well. For reference, here's how my numbers match both Barclays and Longinvest for similar maturity ranges:

Image
Image
Image

Regardless of what data sources you personally prefer (there are lots of great resources on this board), I hope you find this useful. If anyone has any suggestions for improvements all feedback is welcome.
User avatar
siamond
Posts: 6008
Joined: Mon May 28, 2012 5:50 am

Re: Bond Index Calculator

Post by siamond »

Very cool, Tyler. Thanks a lot for such effort (spreadsheet and commentary) and for openly sharing such useful tool. Now only can this help going back in US history, this will also help a lot to model past international bonds returns.

It would indeed be terrific to have some thorough peer review from a few bonds experts. Crossing my fingers that somebody will volunteer...
AlohaJoe
Posts: 6609
Joined: Mon Nov 26, 2007 1:00 pm
Location: Saigon, Vietnam

Re: Bond Index Calculator

Post by AlohaJoe »

I looked at it many moons ago. It is a great intermediate step between longinvest's approach (complex but accurate) and Ibbotson/Damodaran's approach (simple but less accurate). I can definitely see it being useful for people who want to try out lots of variations on things.
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: Bond Index Calculator

Post by longinvest »

Tayler,

Very interesting.

I'm trying to make sense of the "Calculation Details" section.

For each rung, two values are calculated:
  • Interest Payment = AVERAGE(R1,R2)*V1
  • Capital Appreciation = -PV(R2,M,R1*V1,V1)
where:
R1 is the interest rate at the beginning of the term
R2 is the interest rate at the end of the term
V1 is the account value at the beginning of the term
M is the maturity of the bond rung

The first formula seems to be calculating an annual interest payment. Why use the average of the start and end yields? In a bond/CD ladder, the coupon payment isn't affected by changes in yields, only the value of the rung is affected.

The second formula estimates the value of the rung at the end of the year, yet it uses M to calculate it when the remaining maturity, at the end of the year, is M-1. Why is that?

I would try something simpler.

To estimate the one-year total return of a bond fund of equal-size rungs, I would calculate the total return of each of its rungs as follows:
  • Total return (1 rung, 1 year) = R1 - PV(R2,M-1,R1,1) - 1
The total return of the fund would be the average of the returns of its rungs:
  • Total return (fund, 1 year) = (TR_Rung_1 + TR_Rung_2 + ... + TR_Rung_N) / N
where
TR_Rung_X is the total return of rung #X for the year
N is the total number of rungs

What do you think?
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
Topic Author
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

Re: Bond Index Calculator

Post by Tyler9000 »

Great feedback, Longinvest. Just as I hoped you'd offer. :D
longinvest wrote: Wed Apr 04, 2018 7:26 pm The first formula seems to be calculating an annual interest payment. Why use the average of the start and end yields? In a bond/CD ladder, the coupon payment isn't affected by changes in yields, only the value of the rung is affected.
Well, a liquid bond index fund isn't a simple buy & hold ladder like you'd build for yourself. They have to buy and sell bonds to match the inflows and outflows throughout the year, and they can't simply buy the longest issue and sell the shortest issue when that happens because that could cause the distribution of maturities to deviate from the index. As far as I can tell they buy and sell from each rung as needed to maintain the proper bond allocation all along the curve, and because of that constant liquidity the average rate for the year should actually model the payout a little better. Or at least that's the theory -- I'll test it below.

longinvest wrote: Wed Apr 04, 2018 7:26 pmThe second formula estimates the value of the rung at the end of the year, yet it uses M to calculate it when the remaining maturity, at the end of the year, is M-1. Why is that?
Checking the formula against the calculated price appreciation in the Damodaran spreadsheet (which uses the long form formula rather than PV) confirms that he also uses M and not M-1. (although to be fair, he also uses the original interest rate for the coupon and not the average. :wink: ) I imagine that it's because even though a 10-year bond only has 9 years left to maturity once you hold it a year, the interest rates you're calculating the appreciation from are BOTH rates on 10-year bonds (just a year apart). Like the interest payment issue above, I think it comes down to the perspective of thinking in terms of a continuous liquid collection of rungs rather than single bonds held over time.

longinvest wrote: Wed Apr 04, 2018 7:26 pm I would try something simpler.

To estimate the one-year total return of a bond fund of equal-size rungs, I would calculate the total return of each of its rungs as follows:
  • Total return (1 rung, 1 year) = R1 - PV(R2,M-1,R1,1) - 1
That's indeed a lot simpler and eliminates a whole section of the spreadsheet. Nice.

To explore the different iterations of variables, I tested four options:

A: Average interest rate, M1 PV calculation
B: Beginning interest rate, M1
C: Average interest rate, M-1
D: Beginning interest rate, M-1

Here's how they all stack up:

Image
Image
Image

At some point we're splitting hairs, but my read is that option B models intermediate and long term bonds the best but overshoots short term returns. For short term bonds, option A or D seems to work better. I'm just guessing, but my instinct says that the average interest rate works better for short term bonds because the yield curve is a lot less flat on the low end and changes quite a bit. I also suspect that option D may just be getting lucky by discounting the return of option B just enough to match up nicely. It's less accurate on the intermediate and long options.

So I guess we could vary the methodology depending on the index type, but personally I'd prefer to stick with one consistent method. In that case I'd still vote for option A (what I'm doing now) since it appears to work reasonably well in all three models. Although I'm not married to that if someone can convince me otherwise.
Last edited by Tyler9000 on Wed Apr 04, 2018 10:36 pm, edited 1 time in total.
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: Bond Index Calculator

Post by longinvest »

I think I should have clarified my formula:
  • TR_Rung_X (Total return, 1 rung, 1 year) = SY - PV(EY,M-1,SY,1) - 1
Where:
SY (start yield): is the yield of a bond of maturity M at the start of the first year
EY (end yield): is the yield of a bond of maturity M-1 at the start of the second year
M (maturity): is the maturity of the rung at the start of the first year

In other words, both yields are for the same bond, but at two points in time (one year apart). The bond's maturity goes down by one year, of course, after one year.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
Topic Author
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

Re: Bond Index Calculator

Post by Tyler9000 »

longinvest wrote: Wed Apr 04, 2018 10:36 pm I think I should have clarified my formula:
  • TR_Rung_X (Total return, 1 rung, 1 year) = SY - PV(EY,M-1,SY,1) - 1
Where:
SY (start yield): is the yield of a bond of maturity M at the start of the first year
EY (end yield): is the yield of a bond of maturity M-1 at the start of the second year
M (maturity): is the maturity of the rung at the start of the first year

In other words, both yields are for the same bond, but at two points in time (one year apart). The bond's maturity goes down by one year, of course, after one year.
Ah -- got it. Makes perfect sense.

You da' man. It backtests just fine. :wink:
PC is my current formula, while LI is your suggestion.

Image
Image
Image

You just created a lot of work for me this weekend, but that clearly works very well for all models. I think we have a winner.

Aaaaand that's why I like running ideas by this board.

:sharebeer
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: Bond Index Calculator

Post by longinvest »

Tyler9000 wrote: Wed Apr 04, 2018 11:00 pm You just created a lot of work for me this weekend [...]
:sharebeer
Sorry about that! :sharebeer
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: Bond Index Calculator

Post by longinvest »

Just in case anybody's interested, the yield curve is just an approximation. There's unfortunately no "perfect" yield curve. I've explained this in these posts: Also, I think that the yields we use are, for each year, the "January average" yield, but reported index and fund returns are as of December 31st. As a result, it's important to compare growth over time, instead comparing annual returns which are misaligned.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
Topic Author
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

Re: Bond Index Calculator

Post by Tyler9000 »

longinvest wrote: Wed Apr 04, 2018 11:11 pm Also, I think that the yields we use are, for each year, the "January average" yield, but reported index and fund returns are as of December 31st.
It depends on the source. FYI -- all of my numbers above use end-of-month December par yields from the US treasury: https://www.treasury.gov/resource-cente ... NC-YC.aspx
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: Bond Index Calculator

Post by longinvest »

Tyler9000 wrote: Wed Apr 04, 2018 11:43 pm
longinvest wrote: Wed Apr 04, 2018 11:11 pm Also, I think that the yields we use are, for each year, the "January average" yield, but reported index and fund returns are as of December 31st.
It depends on the source. FYI -- all of my numbers above use end-of-month December par yields from the US treasury: https://www.treasury.gov/resource-cente ... NC-YC.aspx
Great!
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
Topic Author
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

Re: Bond Index Calculator

Post by Tyler9000 »

FYI -- the calculator explanation and associated spreadsheet have been updated with the feedback from Longinvest. The spreadsheet itself is now dead simple and in my experience the results are impressively accurate for a DIY data series. I'm a big believer in continuous improvement, so feel free to offer any more suggestions you might have.

Thanks again for the help. It's always good to learn something new.
User avatar
siamond
Posts: 6008
Joined: Mon May 28, 2012 5:50 am

Re: Bond Index Calculator

Post by siamond »

Tyler9000 wrote: Thu Apr 05, 2018 10:45 pmThe spreadsheet itself is now dead simple and in my experience the results are impressively accurate for a DIY data series.
It is indeed impressively simple! Very cool.

Now this begs the question... In layman's terms, what are the main differences between this new simulator and Longinvest's original bond fund simulator? I am not asking about inner mechanics or usability, but more about the quality of the outcome? Why would one be more accurate than the other, and in which circumstances?

And another question. Given that the spreadsheet became so simple, couldn't we easily derive a version of it which would provide monthly returns?
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: Bond Index Calculator

Post by longinvest »

siamond wrote: Fri Apr 06, 2018 12:50 pm In layman's terms, what are the main differences between this new simulator and Longinvest's original bond fund simulator?
My simulator provides a realistic simulation of a personal bond portfolio structured similarly to a ladder, except that the shortest rung is sold before maturity. Given precise buy-time and sell-time yields*, this simulator reconstructs exact cash-flows, regardless of the precision of intermediate yields. Annual total returns are approximated, but given the exact cash-flow property, we know that imprecisions will self-correct over time.

* And assuming bonds are bought at par.

The problem I was trying to solve was that of estimating past bond returns using only 10-year and 1-year yields going back to 1871. I knew that the intermediate yields were incorrect (a linear approximation based on the 1 and 10-year yields), but the self-correcting property gave me confidence that the calculated returns wouldn't be too bad of an approximation.

Tayler's new simpler simulator has no such self-correcting property. It isn't trying to simulate a personal bond portfolio, either. It's trying to simulate an index bond fund without knowing market caps. It approximates market caps by equal weighting the rungs. It is also estimating some of the intermediate yields for its older years (1969-1974).
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
Topic Author
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

Re: Bond Index Calculator

Post by Tyler9000 »

siamond wrote: Fri Apr 06, 2018 12:50 pm Now this begs the question... In layman's terms, what are the main differences between this new simulator and Longinvest's original bond fund simulator? I am not asking about inner mechanics or usability, but more about the quality of the outcome? Why would one be more accurate than the other, and in which circumstances?
I'll defer to Longinvest on the theory as to why one method should be more accurate than the other, but I can speak to how much of a measurable difference it makes.

To compare apples-to-apples, I copied the interest rates from Longinvest's spreadsheet and used them in my own (the latest version with LI's excellent suggested formula). I also made sure the maturity ranges matched up. This will let us compare the error between calculation methods with the exact same data sources.

Image
Image
Image

For reference, the RMSE (standard deviation of the difference) since 1970 between the PC method and LI method is 0.04 for short term bonds, 0.51 for intermediate term bonds, and 0.89 for long term bonds. That means that the calculated returns are within about 8 basis points of each other 95% of the time for STB. They're within about 1% of each other 95% of the time for ITB. And they're within about 1.8% of each other 95% of the time for LTB. Personally I'd call that a nice match, but I'll leave it to others to decide for themselves how much error between methods they're comfortable with.

When it comes to the issue of replicating bond index fund data, I believe my numbers are a little more accurate than the current LI numbers both theoretically and practically simply because I use the Dec31st interest rates (the same that the index funds use) while LI uses the January average. But they're still pretty close, and given the same rates I have full confidence that his numbers would be similar to mine within the same error ranges as you see above. His numbers using EOY rates may indeed be a little more accurate than mine relative to Barclays, although I can't test that myself.

siamond wrote: Fri Apr 06, 2018 12:50 pm And another question. Given that the spreadsheet became so simple, couldn't we easily derive a version of it which would provide monthly returns?
Absolutely. Assuming you have end-of-month interest rates, converting the spreadsheet to find monthly returns should be pretty simple.
Last edited by Tyler9000 on Sat Apr 07, 2018 12:07 am, edited 8 times in total.
User avatar
patrick013
Posts: 3301
Joined: Mon Jul 13, 2015 7:49 pm

Re: Bond Index Calculator

Post by patrick013 »

Tyler9000 wrote: Wed Apr 04, 2018 12:00 am Regardless of what data sources you personally prefer (there are lots of great resources on this board), I hope you find this useful. If anyone has any suggestions for improvements all feedback is welcome.
So if I knew or projected the TRSY10 interest rate for 2018, 2019,
and 2020 and estimated the yield curve based on mean spreads
over the projected FFR this spreadsheet looks like it would give a
pretty good projected yield then for a 10 year treasury ladder or
index type investment. Might fill those in projecting the FFR at 3%
at the end of 2020 and using historical mean spreads for most maturities
(at least 2-5-10 year mean spreads) while just smoothing out the
maturities where mean spreads aren't easily available. For a forward
estimate I can see it working. :)
age in bonds, buy-and-hold, 10 year business cycle
User avatar
patrick013
Posts: 3301
Joined: Mon Jul 13, 2015 7:49 pm

Re: Bond Index Calculator

Post by patrick013 »

Tyler9000 wrote: Wed Apr 04, 2018 12:00 am But I do like that it's simple, flexible, and demonstrably matches known good returns reasonably well.
Could you release the password ? Can't add annual data without it.

Thanks.
age in bonds, buy-and-hold, 10 year business cycle
User avatar
Topic Author
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

Re: Bond Index Calculator

Post by Tyler9000 »

patrick013 wrote: Sat Apr 07, 2018 12:46 pm Could you release the password ? Can't add annual data without it.
I like to keep it moderately protected so that I can stand behind the calculations by preventing multiple versions from floating around. But I also like to make things useful. I updated the spredsheet so that you can change the start year labels in case you want to model a different date range. It's not quite what you asked for, but hopefully that helps.
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: Bond Index Calculator

Post by longinvest »

Tyler9000 wrote: Sat Apr 07, 2018 2:36 pm I like to keep it moderately protected so that I can stand behind the calculations by preventing multiple versions from floating around. But I also like to make things useful. I updated the spredsheet so that you can change the start year labels in case you want to model a different date range. It's not quite what you asked for, but hopefully that helps.
Tyler, I use different approach with my spreadsheets. While I protect calculation fields in my spreadsheets, I don't set up a password. This way, people can easily unprotect the fields if they need to.

As for having multiple versions floating around, I don't care. I provide the official download links. People who use my official links know that they're getting the official versions.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
patrick013
Posts: 3301
Joined: Mon Jul 13, 2015 7:49 pm

Re: Bond Index Calculator

Post by patrick013 »

Tyler9000 wrote: Sat Apr 07, 2018 2:36 pm
patrick013 wrote: Sat Apr 07, 2018 12:46 pm Could you release the password ? Can't add annual data without it.
I like to keep it moderately protected so that I can stand behind the calculations by preventing multiple versions from floating around. But I also like to make things useful. I updated the spredsheet so that you can change the start year labels in case you want to model a different date range. It's not quite what you asked for, but hopefully that helps.
No problem. I was able to copy/paste the sheets to a new spreadsheet and
resolve some cell/file reference problems so it is functioning OK after that.

Put in yield curve numbers for 2018 to 2021 and output was calc'd. I think
it needs a little fine tuning but what doesn't.
age in bonds, buy-and-hold, 10 year business cycle
User avatar
Topic Author
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

Re: Bond Index Calculator

Post by Tyler9000 »

longinvest wrote: Sat Apr 07, 2018 2:52 pm Tyler, I use different approach with my spreadsheets. While I protect calculation fields in my spreadsheets, I don't set up a password. This way, people can easily unprotect the fields if they need to.
Cool -- thanks for pointing that out. It helped me finish my thought and check both calculation methods with the same December rates against the Barclays numbers. As I suspected, both methods are reasonably accurate relative to the real thing and your intermediate calculations are indeed quite impressive.
:sharebeer

Image
Image
Image
User avatar
siamond
Posts: 6008
Joined: Mon May 28, 2012 5:50 am

Re: Bond Index Calculator

Post by siamond »

Quick question about this wonderfully simple calculator...

According to the calculator's methodology, this is centered on the magical formula:
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

Since coupon payments are typical semi-annual, wouldn't it be more accurate to use the following formula:
Capital Appreciation = -PV(R2/2, 2*(M-1), R1/2, 1) - 1

I suspect that in practice this might not change the outcome very much, but wouldn't it make more sense? Thoughts?
User avatar
Kevin M
Posts: 15787
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Bond Index Calculator

Post by Kevin M »

siamond wrote: Sat Mar 16, 2019 8:12 am Quick question about this wonderfully simple calculator...

According to the calculator's methodology, this is centered on the magical formula:
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

Since coupon payments are typical semi-annual, wouldn't it be more accurate to use the following formula:
Capital Appreciation = -PV(R2/2, 2*(M-1), R1/2, 1) - 1

I suspect that in practice this might not change the outcome very much, but wouldn't it make more sense? Thoughts?
Since no one has replied yet, I will.

Yes, the formula that assumes semi-annual compounding is more accurate for Treasury notes, bonds, and other bonds that pay interest semi-annually. This is equivalent to setting frequency = 2 in the spreadsheet PRICE function.

However, as you note, it doesn't make a significant difference. I usually ignore it, and just use the simpler formula that assumes annual compounding, but if I'm engaging with someone like #Cruncher, I'll probably use formulas that assume semi-annual compounding ;-).

On the other hand, if I'm trying to understand one of #Cruncher's more complicated spreadsheets that involves a series of calculations, :confused, I might convert his semi-annual compounding formulas to annual compounding formulas in my own copy, just to get a better understanding of what's going on at a high level (and the results usually don't change to a noticeable degree).

Kevin
If I make a calculation error, #Cruncher probably will let me know.
User avatar
Topic Author
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

Re: Bond Index Calculator

Post by Tyler9000 »

siamond wrote: Sat Mar 16, 2019 8:12 am Since coupon payments are typical semi-annual, wouldn't it be more accurate to use the following formula:
Capital Appreciation = -PV(R2/2, 2*(M-1), R1/2, 1) - 1

I suspect that in practice this might not change the outcome very much, but wouldn't it make more sense? Thoughts?
Thanks for the suggestion, and to Kevin for the follow-up. That does sound like a reasonable improvement worth further exploration, and I plan to do a little work to quantify how much of a difference it makes. Stay tuned.
User avatar
siamond
Posts: 6008
Joined: Mon May 28, 2012 5:50 am

Re: Bond Index Calculator

Post by siamond »

Tyler9000 wrote: Sun Mar 17, 2019 5:18 pmThanks for the suggestion, and to Kevin for the follow-up. That does sound like a reasonable improvement worth further exploration, and I plan to do a little work to quantify how much of a difference it makes. Stay tuned.
I already ran my own numbers and the difference was indeed very small. But... when a formula makes more sense... I would say better use it than an approximation of said formula...
User avatar
Topic Author
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

Re: Bond Index Calculator

Post by Tyler9000 »

Kevin M wrote: Sun Mar 17, 2019 4:54 pm Yes, the formula that assumes semi-annual compounding is more accurate for Treasury notes, bonds, and other bonds that pay interest semi-annually. This is equivalent to setting frequency = 2 in the spreadsheet PRICE function.
siamond wrote: Sun Mar 17, 2019 6:03 pm I already ran my own numbers and the difference was indeed very small. But... when a formula makes more sense... I would say better use it than an approximation of said formula...
I ran a comparison between the two methods, and the difference is indeed quite small (usually no more than about 0.1%). And I completely agree that using the calculation method appropriate for the real-world interest payment schedule makes sense. So with the annual calculator update I've tweaked it to use semi-annual payments.

(BTW, in case there any German investors reading this, for the purposes of Portfolio Charts I made sure to keep the Bund indices using annual interest payments to reflect proper Bund methodology that is a little different from US treasuries.)

Thanks Siamond and Kevin M for the feedback.
:sharebeer
Post Reply