Historical Bond Returns - From Rates to Returns [Bond Fund Simulator]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
siamond
Posts: 5125
Joined: Mon May 28, 2012 5:50 am

Re: Historical Bond Returns - From Rates to Returns [Bond Fund Simulator]

Post by siamond » Mon Sep 23, 2019 9:51 pm

AlohaJoe wrote:
Mon Sep 23, 2019 6:24 pm
siamond wrote:
Fri Aug 30, 2019 4:28 pm
longinvest wrote:
Thu Aug 29, 2019 3:29 pm
I think that trying to reconstruct a total-market fund which includes corporates, call features, upgrade/downgrades, etc., with the very simple model of the bond fund simulator would be a mistake.
This is a crude mapping for sure, it has been (and will remain) marked as such in the Simba spreadsheet.
I agree with longinvest and I'm not sure that including it in Simba does more good than harm. A long time ago I looked (briefly) into simulating total bond market returns and came away dubious of the prospect. The main problem I had was the changing composition of the "total market" and the reality that the only comparison available (1976-onwards) coincided with the biggest change in index history: the rise of mortgage backed securities. They didn't exist at all before 1976. Today they make up something like 1/4th of the market.

So I had little confidence that something that was fudged to track post-1976 returns would actually map to pre-1976 returns without some way to at least check the composition of the total market. In 1970 were corporate bonds 25% of the total market, like they are today? Or were they 50%? [...]
I do appreciate the problem and thanks for the great chart. There is no question that the current total-bonds mapping is very coarse. I do believe we need to keep such series for high-level portfolio tests spanning a long period of time, e.g. X% total-stocks, 1-X% total-bonds, where frankly, it doesn't matter much what type of (approximately IT) bonds you're using. But for sure, there is nothing terribly accurate in running such 1871+ tests, whether it's on the stocks side or the bonds side. We have similar issues with LT bonds, by the way, by lack of historical yields for long maturities. It's a never-ending quest to get better historical data, we made great progress thanks to this thread and others, but for sure, there is plenty left to improve.

Overall, I think the discussion should focus on how to do something better to emulate Total-Bonds. Ideas welcome.

User avatar
SimpleGift
Posts: 3396
Joined: Tue Feb 08, 2011 3:45 pm
Location: Central Oregon

Re: Historical Bond Returns - From Rates to Returns [Bond Fund Simulator]

Post by SimpleGift » Mon Sep 23, 2019 9:54 pm

AlohaJoe wrote:
Mon Sep 23, 2019 6:24 pm
So I had little confidence that something that was fudged to track post-1976 returns would actually map to pre-1976 returns without some way to at least check the composition of the total market. In 1970 were corporate bonds 25% of the total market, like they are today? Or were they 50%?
From the chart below, it appears around 1972 that corporate bonds made up 50% of the U.S Aggregate Bond Index. Treasuries then dominated during the 1980s and 1990s, only to be eclipsed by mortgage-backed securities in the 2000s.
  • Image
As you suggest, on the scale of decades, the composition of this aggregate bond index has indeed been a moving target.

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

Re: Historical Bond Returns - From Rates to Returns [Bond Fund Simulator]

Post by siamond » Tue Sep 24, 2019 2:21 pm

As a side note, the SBBI/Ibbotson yearbook provides a US (long-term) corporates data series which starts in 1926. There is also a long-term treasury data series. Assembling a telltale chart to compare them shows relatively limited differences over time. The premiums displayed by corporates is kind of steadily climbing, by roughly 0.4% a year. The primary change to this pattern was the recent financial crisis where corporates badly tumbled, but corporates seem to be slowly on the way towards recovery (albeit with a lower premium displayed over the recent decades).

I don't know if the same holds true for IT corporates vs. treasuries (possibly with a more muted difference), but I'd venture to suggest this isn't too far off of a guess. For a coarse mapping, this tends to indicate that the % of corporates vs. treasuries may not matter that much (if anything, this would underestimate the combined returns a tad, and being conservative is ok for such coarse mapping). Now, as to the impact of mortgage-based securities, that I have no idea (and recent numbers may not help that much, for obvious reasons).

User avatar
LadyGeek
Site Admin
Posts: 58605
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Historical Bond Returns - From Rates to Returns [Bond Fund Simulator]

Post by LadyGeek » Thu Oct 03, 2019 9:21 pm

I've moved jmk's proposed Total Bond Market model discussion into a new thread. See: Historical Bond Returns - [Total Bond Market model]
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

Lee_WSP
Posts: 1349
Joined: Fri Apr 19, 2019 5:15 pm
Location: Arizona

Re: Historical Bond Returns - From Rates to Returns [Bond Fund Simulator]

Post by Lee_WSP » Fri Oct 04, 2019 10:58 am

Okay, I've searched for more than five minutes, so I'm throwing in the towel.

How do I create a daily or monthly synthetic STRIPS dataset that I can use for monte carlo and/or backtesting simulations?

Topic Author
longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Historical Bond Returns - From Rates to Returns [Bond Fund Simulator]

Post by longinvest » Fri Oct 04, 2019 6:13 pm

Lee_WSP wrote:
Fri Oct 04, 2019 10:58 am
How do I create a daily or monthly synthetic STRIPS dataset that I can use for monte carlo and/or backtesting simulations?
One could imitate the annual model in the bond fund simulator's EqPar Zero Fund (30 to 21-year) using available monthly yields. One would first need to calculate approximate yields (using a linear approximation) for the numerous missing maturities (e.g. 29 years and 11 months, 29 years and 10 months, ...). Then one would derive synthetic zero-coupon yields, to finally implement the EqPar Zero Fund.

Given the amount of calculations involved, this would be best done using a computer program instead of a spreadsheet. AlohaJoe has already developed a Python program that implements the base model discussed in the first post of this thread. Maybe that program could be modified to implement the equal-par zero-coupon bond fund model. See the end of this earlier post.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

Topic Author
longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Historical Bond Returns - Shiller: From Rates to Returns [Bond Fund Simulator]

Post by longinvest » Fri Jan 03, 2020 3:51 pm

I have uploaded version 1.23 of the Bond Fund Simulator spreadsheet.

It is available online and to download from the links in the first post.

Main changes:
  • Added Bond Fund (15 to 2-year).
  • Added December 31, 2019 yields from FRED daily data.
As usual, comments are welcome.

Enjoy!
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

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

Re: Historical Bond Returns - Shiller: From Rates to Returns [Bond Fund Simulator]

Post by siamond » Sat Jan 04, 2020 12:20 pm

longinvest wrote:
Fri Jan 03, 2020 3:51 pm
I have uploaded version 1.23 of the Bond Fund Simulator spreadsheet.

It is available online and to download from the links in the first post.
Hm. I tried to download the Excel version and got a couple of warnings:
- Dropbox level: .xlsx files are supported but something went wrong.
- Excel level: Alert: We found a problem with some content in 'BogleheadsBondFundSimulator.xlsx'. Do you want us to try to recover as much as we can?

At the end, it seems to work... Still, this is unsettling.

Topic Author
longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Historical Bond Returns - Shiller: From Rates to Returns [Bond Fund Simulator]

Post by longinvest » Sat Jan 04, 2020 12:25 pm

siamond wrote:
Sat Jan 04, 2020 12:20 pm
longinvest wrote:
Fri Jan 03, 2020 3:51 pm
I have uploaded version 1.23 of the Bond Fund Simulator spreadsheet.

It is available online and to download from the links in the first post.
Hm. I tried to download the Excel version and got a couple of warnings:
- Dropbox level: .xlsx files are supported but something went wrong.
- Excel level: Alert: We found a problem with some content in 'BogleheadsBondFundSimulator.xlsx'. Do you want us to try to recover as much as we can?

At the end, it seems to work... Still, this is unsettling.
This is because the file has grown above 5M in size and Office Online refuses to open the .xls exported from LibreOffice Calc to convert it into a .xlsx file. I had to create the .xlsx using Google Sheets.

If you prefer, you can simply use the online Google Sheets version or open the original .ods file with LibreOffice Calc.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

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

Re: Historical Bond Returns - Shiller: From Rates to Returns [Bond Fund Simulator]

Post by siamond » Sat Jan 04, 2020 12:39 pm

longinvest wrote:
Sat Jan 04, 2020 12:25 pm
This is because the file has grown above 5M in size and Office Online refuses to open the .xls exported from LibreOffice Calc to convert it into a .xlsx file. I had to create the .xlsx using Google Sheets.
The Dropbox-level warning didn't bother me, this often happens with large files, but the Excel-level warning is more unsettling. This being said, it doesn't bother me, it all seems to work fine.

Another nitpick: if you could please unify all tabs to have 1871+ returns starting on line 7, this would be appreciated - to minimize risks of errors when copying such useful data!

Topic Author
longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Historical Bond Returns - Shiller: From Rates to Returns [Bond Fund Simulator]

Post by longinvest » Sat Jan 04, 2020 1:05 pm

siamond wrote:
Sat Jan 04, 2020 12:39 pm
longinvest wrote:
Sat Jan 04, 2020 12:25 pm
This is because the file has grown above 5M in size and Office Online refuses to open the .xls exported from LibreOffice Calc to convert it into a .xlsx file. I had to create the .xlsx using Google Sheets.
The Dropbox-level warning didn't bother me, this often happens with large files, but the Excel-level warning is more unsettling. This being said, it doesn't bother me, it all seems to work fine.
OK. Let me try to explain this differently. The Microsoft Excel software always complains with unsettling warnings when a file was created using a non-Microsoft software. I was, in the past, able to eliminate this warning by using Microsoft's free online office.com site, importing a .xls which was created using the free LibreOffice software (as an export of the original .ods file), opening this .xls file with Microsoft's free online software and using it to export a .xlsx file. As the .xlsx file was created using Microsoft software, Excel didn't complain about it.

Because of the additional 2020 data and new bond fund model, the .xls file size has grown over 5M, which is now above the size limit supported by Microsoft's free online office.com site. As a result, I wasn't able to use this software to create the .xlsx file; I had to use Google Sheets, instead, resulting into the unsettling warning that you got.

I've just noticed that if I create a .xlsx using LibreOffice calc, it remains well under 5M! It's only 1.8M. Maybe I could trick Microsoft's online software to create a duplicate that won't show a warning. I'll try that.
siamond wrote:
Sat Jan 04, 2020 12:39 pm
Another nitpick: if you could please unify all tabs to have 1871+ returns starting on line 7, this would be appreciated - to minimize risks of errors when copying such useful data!
Good idea.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

Topic Author
longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Historical Bond Returns - From Rates to Returns [Bond Fund Simulator]

Post by longinvest » Sat Jan 04, 2020 1:31 pm

OK. Letting Microsoft's online software "fix" the .xlsx file worked.

I've uploaded version 1.24 where I normalized the start line of all funds.

Please let me know if the warning is gone.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

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

Re: Historical Bond Returns - Shiller: From Rates to Returns [Bond Fund Simulator]

Post by siamond » Sat Jan 04, 2020 2:16 pm

longinvest wrote:
Fri Jan 03, 2020 3:51 pm
[*] Added Bond Fund (15 to 2-year).
Thanks for doing that. This probably came from our discussion quite a few posts ago about the best mapping for a Total-Bonds series (e.g. extending VBTLX history), where I suggested that 15-2 might (?!) be a better mapping, if only to diversify maturities. Well, this doesn't match reality very well as the following Telltale demonstrates (Barclays TBM is the Bloomberg Barclays US Aggregate Bond TR USD index series). I guess it was worth a try, but no luck... So I'll stick to the 10-4 mapping for the next Simba update.

Image

PS. longinvest, don't feel obligated to maintain the 15-2 model in the future. This was just an idea, it doesn't work, no point losing any more time on it.

User avatar
LadyGeek
Site Admin
Posts: 58605
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Historical Bond Returns - From Rates to Returns [Bond Fund Simulator]

Post by LadyGeek » Sat Jan 04, 2020 2:19 pm

I have Microsoft Excel (Office 2019) and don't see any warnings except that the file is opened in "Protected view". This is normal for files downloaded from the internet. Once I started editing, the warning disappeared as expected.

Suggestion (low priority): Move the "Changes" tab location to be the first tab. Version info and other administrative notes are usually at the front of the spreadsheet.

Due to the large number of tabs, it might be helpful to add a note that you have a "Spreadsheets" tab.

Perhaps rename the "Changes" tab to "ReadMe" and use it for documenting revisions and additional notes?
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

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

Re: Historical Bond Returns - From Rates to Returns [Bond Fund Simulator]

Post by siamond » Sat Jan 04, 2020 2:25 pm

longinvest wrote:
Sat Jan 04, 2020 1:31 pm
OK. Letting Microsoft's online software "fix" the .xlsx file worked.

I've uploaded version 1.24 where I normalized the start line of all funds.

Please let me know if the warning is gone.
Perfect. Warnings disappeared at both levels. And normalization is much appreciated. All good!

Topic Author
longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Historical Bond Returns - Shiller: From Rates to Returns [Bond Fund Simulator]

Post by longinvest » Sat Jan 04, 2020 2:43 pm

siamond wrote:
Sat Jan 04, 2020 2:16 pm
Thanks for doing that. This probably came from our discussion quite a few posts ago about the best mapping for a Total-Bonds series (e.g. extending VBTLX history), where I suggested that 15-2 might (?!) be a better mapping, if only to diversify maturities. Well, this doesn't match reality very well as the following Telltale demonstrates (Barclays TBM is the Bloomberg Barclays US Aggregate Bond TR USD index series). I guess it was worth a try, but no luck... So I'll stick to the 10-4 mapping for the next Simba update.
It would be nice if you could provide a comparison of the volatility the the funds and Barclays index (along with an intermediate Treasury index). I think that 10-2 is closer, in volatility, to a total bonds fund than 10-4. I think that 10-4 is more representative to the volatility of an intermediate Treasury fund. A comparison would help to confirm or disprove this.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

Topic Author
longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Historical Bond Returns - From Rates to Returns [Bond Fund Simulator]

Post by longinvest » Sat Jan 04, 2020 3:44 pm

LadyGeek wrote:
Sat Jan 04, 2020 2:19 pm
I have Microsoft Excel (Office 2019) and don't see any warnings except that the file is opened in "Protected view". This is normal for files downloaded from the internet. Once I started editing, the warning disappeared as expected.

Suggestion (low priority): Move the "Changes" tab location to be the first tab. Version info and other administrative notes are usually at the front of the spreadsheet.

Due to the large number of tabs, it might be helpful to add a note that you have a "Spreadsheets" tab.

Perhaps rename the "Changes" tab to "ReadMe" and use it for documenting revisions and additional notes?
Thanks LadyGeek.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

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

Re: Historical Bond Returns - Shiller: From Rates to Returns [Bond Fund Simulator]

Post by siamond » Sat Jan 04, 2020 5:06 pm

longinvest wrote:
Sat Jan 04, 2020 2:43 pm
It would be nice if you could provide a comparison of the volatility the the funds and Barclays index (along with an intermediate Treasury index). I think that 10-2 is closer, in volatility, to a total bonds fund than 10-4. I think that 10-4 is more representative to the volatility of an intermediate Treasury fund. A comparison would help to confirm or disprove this.
Here are the Simba stats for the existence of the Barclays index, if this is what you were looking for. Note that the VBTLX and VSIGX numbers are the actuals extended with index/model numbers back to 1976.

Image

I am a little wary about volatility stats with an annual model though. Working on other projects with monthly numbers kind of opened my eyes to the limitations of annual data... Still, you're right, 10-2 seems closer from the volatility perspective. I definitely would prioritize a better CAGR mapping though.

Topic Author
longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Historical Bond Returns - From Rates to Returns [Bond Fund Simulator]

Post by longinvest » Sat Jan 04, 2020 5:45 pm

Thanks, Siamond.

The reason I prefer to consider volatility in priority, when looking for a surrogate, is because it's one of the more stable properties of asset classes. Take, for example, US and international stocks. Over the last decade, they've had significantly different returns (CAGR). Yet, both asset classes had relatively similar volatility, significantly higher than the volatility of intermediate bonds. International stocks were somewhat more volatile (2% higher stdev) due to the additional currency volatility layer. Such properties are remarkably stable over time, in the sense that stocks are significantly more volatile than intermediate bonds, and that international stocks are usually a little more volatile than domestic stocks. (This is true in Canada, too, and I'm sure it's true elsewhere). Of course, the volatility measurements (and ratios) vary over different time periods.

In other words, I'm a little more confident that the volatility of 10-2 will remain closer to the volatility of total bonds, in the future, than about the fact the 10-4 will continue to be a better CAGR match. CAGR relations are less stable over time than volatility relations. I think that this also applies to the distant past, in periods for which we don't have enough data to check for the best CAGR match. I'll concede, though, that because intermediate bonds are part of the total bond market, one could also argue that there are still good chances that 10-4 could remain a better CAGR match in the future (and possibly in the distant past).

This is minor stuff. Both 10-2 and 10-4 are good enough. We just have our small preferences.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

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

Re: Historical Bond Returns - From Rates to Returns [Bond Fund Simulator]

Post by siamond » Sat Jan 04, 2020 10:37 pm

longinvest wrote:
Sat Jan 04, 2020 5:45 pm
This is minor stuff. Both 10-2 and 10-4 are good enough. We just have our small preferences.
Fair enough. And I do understand where you're coming from. I actually made it super simple to pick 10-4 instead of 10-2 in the splicing process for the VBTLX data series, just one cell to flip from TRUE to FALSE... And I clearly marked both mappings as CRUDE (while I didn't use such marking for the models used to extend treasury-only data series, which I am more confident with, for obvious reasons). I'll publish the Simba update tomorrow, once I have a chance to run a few more sanity checks.

Thanks again for the hard work on this helpful simulator.

Post Reply