Bond Index Calculator
Bond Index Calculator
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/bondindexcalculator/
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:
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.
https://portfoliocharts.com/bondindexcalculator/
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:
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.
Re: Bond Index Calculator
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...
It would indeed be terrific to have some thorough peer review from a few bonds experts. Crossing my fingers that somebody will volunteer...
Re: Bond Index Calculator
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.

 Posts: 3288
 Joined: Sat Aug 11, 2012 8:44 am
Re: Bond Index Calculator
Tayler,
Very interesting.
I'm trying to make sense of the "Calculation Details" section.
For each rung, two values are calculated:
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 M1. Why is that?
I would try something simpler.
To estimate the oneyear total return of a bond fund of equalsize rungs, I would calculate the total return of each of its rungs as follows:
TR_Rung_X is the total return of rung #X for the year
N is the total number of rungs
What do you think?
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)
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 M1. Why is that?
I would try something simpler.
To estimate the oneyear total return of a bond fund of equalsize rungs, I would calculate the total return of each of its rungs as follows:
 Total return (1 rung, 1 year) = R1  PV(R2,M1,R1,1)  1
 Total return (fund, 1 year) = (TR_Rung_1 + TR_Rung_2 + ... + TR_Rung_N) / N
TR_Rung_X is the total return of rung #X for the year
N is the total number of rungs
What do you think?
Bogleheads investment philosophy  singleETF balanced portfolio  VBAL
Re: Bond Index Calculator
Great feedback, Longinvest. Just as I hoped you'd offer.
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, M1
D: Beginning interest rate, M1
Here's how they all stack up:
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.
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 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.
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 M1. (although to be fair, he also uses the original interest rate for the coupon and not the average. ) I imagine that it's because even though a 10year 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 10year 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 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 M1. Why is that?
That's indeed a lot simpler and eliminates a whole section of the spreadsheet. Nice.longinvest wrote: ↑Wed Apr 04, 2018 7:26 pmI would try something simpler.
To estimate the oneyear total return of a bond fund of equalsize rungs, I would calculate the total return of each of its rungs as follows:
 Total return (1 rung, 1 year) = R1  PV(R2,M1,R1,1)  1
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, M1
D: Beginning interest rate, M1
Here's how they all stack up:
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.

 Posts: 3288
 Joined: Sat Aug 11, 2012 8:44 am
Re: Bond Index Calculator
I think I should have clarified my formula:
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 M1 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.
 TR_Rung_X (Total return, 1 rung, 1 year) = SY  PV(EY,M1,SY,1)  1
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 M1 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.
Bogleheads investment philosophy  singleETF balanced portfolio  VBAL
Re: Bond Index Calculator
Ah  got it. Makes perfect sense.longinvest wrote: ↑Wed Apr 04, 2018 10:36 pmI think I should have clarified my formula:
Where:
 TR_Rung_X (Total return, 1 rung, 1 year) = SY  PV(EY,M1,SY,1)  1
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 M1 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.
You da' man. It backtests just fine.
PC is my current formula, while LI is your suggestion.
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.

 Posts: 3288
 Joined: Sat Aug 11, 2012 8:44 am
Re: Bond Index Calculator
Sorry about that!
Bogleheads investment philosophy  singleETF balanced portfolio  VBAL

 Posts: 3288
 Joined: Sat Aug 11, 2012 8:44 am
Re: Bond Index Calculator
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.
Bogleheads investment philosophy  singleETF balanced portfolio  VBAL
Re: Bond Index Calculator
It depends on the source. FYI  all of my numbers above use endofmonth December par yields from the US treasury: https://www.treasury.gov/resourcecente ... NCYC.aspxlonginvest wrote: ↑Wed Apr 04, 2018 11:11 pmAlso, 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.

 Posts: 3288
 Joined: Sat Aug 11, 2012 8:44 am
Re: Bond Index Calculator
Great!Tyler9000 wrote: ↑Wed Apr 04, 2018 11:43 pmIt depends on the source. FYI  all of my numbers above use endofmonth December par yields from the US treasury: https://www.treasury.gov/resourcecente ... NCYC.aspxlonginvest wrote: ↑Wed Apr 04, 2018 11:11 pmAlso, 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.
Bogleheads investment philosophy  singleETF balanced portfolio  VBAL
Re: Bond Index Calculator
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.
Thanks again for the help. It's always good to learn something new.
Re: Bond Index Calculator
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?

 Posts: 3288
 Joined: Sat Aug 11, 2012 8:44 am
Re: Bond Index Calculator
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 buytime and selltime yields*, this simulator reconstructs exact cashflows, regardless of the precision of intermediate yields. Annual total returns are approximated, but given the exact cashflow property, we know that imprecisions will selfcorrect 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 10year and 1year yields going back to 1871. I knew that the intermediate yields were incorrect (a linear approximation based on the 1 and 10year yields), but the selfcorrecting property gave me confidence that the calculated returns wouldn't be too bad of an approximation.
Tayler's new simpler simulator has no such selfcorrecting 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 (19691974).
Bogleheads investment philosophy  singleETF balanced portfolio  VBAL
Re: Bond Index Calculator
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.siamond wrote: ↑Fri Apr 06, 2018 12:50 pmNow 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?
To compare applestoapples, 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.
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.
Absolutely. Assuming you have endofmonth 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.
 patrick013
 Posts: 2518
 Joined: Mon Jul 13, 2015 7:49 pm
Re: Bond Index Calculator
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 2510 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, buyandhold, 10 year business cycle
 patrick013
 Posts: 2518
 Joined: Mon Jul 13, 2015 7:49 pm
Re: Bond Index Calculator
Could you release the password ? Can't add annual data without it.
Thanks.
age in bonds, buyandhold, 10 year business cycle
Re: Bond Index Calculator
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.patrick013 wrote: ↑Sat Apr 07, 2018 12:46 pmCould you release the password ? Can't add annual data without it.

 Posts: 3288
 Joined: Sat Aug 11, 2012 8:44 am
Re: Bond Index Calculator
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.Tyler9000 wrote: ↑Sat Apr 07, 2018 2:36 pmI 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.
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.
Bogleheads investment philosophy  singleETF balanced portfolio  VBAL
 patrick013
 Posts: 2518
 Joined: Mon Jul 13, 2015 7:49 pm
Re: Bond Index Calculator
No problem. I was able to copy/paste the sheets to a new spreadsheet andTyler9000 wrote: ↑Sat Apr 07, 2018 2:36 pmI 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.patrick013 wrote: ↑Sat Apr 07, 2018 12:46 pmCould you release the password ? Can't add annual data without it.
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, buyandhold, 10 year business cycle
Re: Bond Index Calculator
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.longinvest wrote: ↑Sat Apr 07, 2018 2:52 pmTyler, 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.
Re: Bond Index Calculator
Quick question about this wonderfully simple calculator...
According to the calculator's methodology, this is centered on the magical formula:
Capital Appreciation = PV(R2, M1, 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 M1 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 semiannual, wouldn't it be more accurate to use the following formula:
Capital Appreciation = PV(R2/2, 2*(M1), 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?
According to the calculator's methodology, this is centered on the magical formula:
Capital Appreciation = PV(R2, M1, 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 M1 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 semiannual, wouldn't it be more accurate to use the following formula:
Capital Appreciation = PV(R2/2, 2*(M1), 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?
Re: Bond Index Calculator
Since no one has replied yet, I will.siamond wrote: ↑Sat Mar 16, 2019 8:12 amQuick question about this wonderfully simple calculator...
According to the calculator's methodology, this is centered on the magical formula:
Capital Appreciation = PV(R2, M1, 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 M1 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 semiannual, wouldn't it be more accurate to use the following formula:
Capital Appreciation = PV(R2/2, 2*(M1), 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?
Yes, the formula that assumes semiannual compounding is more accurate for Treasury notes, bonds, and other bonds that pay interest semiannually. 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 semiannual compounding .
On the other hand, if I'm trying to understand one of #Cruncher's more complicated spreadsheets that involves a series of calculations, , I might convert his semiannual 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
....... Suggested format for Asking Portfolio Questions (edit original post)
Re: Bond Index Calculator
Thanks for the suggestion, and to Kevin for the followup. 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.siamond wrote: ↑Sat Mar 16, 2019 8:12 amSince coupon payments are typical semiannual, wouldn't it be more accurate to use the following formula:
Capital Appreciation = PV(R2/2, 2*(M1), 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?
Re: Bond Index Calculator
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...
Re: Bond Index Calculator
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 realworld interest payment schedule makes sense. So with the annual calculator update I've tweaked it to use semiannual 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.