Simba's backtesting spreadsheet [a Bogleheads community project]

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

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

apoptosis66 wrote: Tue Jan 18, 2022 1:14 pm I am looking for some clarification on this spreadsheet's "Downside Deviation vs MAR" calculation.

Using rev21a, Analyze_Portfolio.H96 ...

Formula: {=SQRT(SUM(MIN(0, OFFSET($Portfolio_Math.M$101, E$89, 0, E$88, 1)-OFFSET($Portfolio_Math.$J$101, E$89, 0, E$88, 1))^2) / E$88)}
Value: 4.54%

I have been writing a python program and was using this spreadsheet to check my formulas and I can never get the same value for this. So I tried to break the down the best I could in spreadsheet, and if I do what I think this formula does I get the following: [...]

As you can see if I break the formula down I get 8.12% instead of 4.54%.
Well, you seem to have a really good point. I also broke down the math and ended up with 8.12%. And yes, MIN(x, array) is just the min of all values, which wasn't the intent. Fact is very few people really care about those downside std-deviation metrics and I am afraid that I introduced this faulty formula between 2016 and 2017 when I restructured the spreadsheet and... nobody noticed my mistake until now. Shesh. :shock:

A quick fix would be the following:
{=SQRT(SUM(IF(OFFSET(Portfolio_Math!M$101, E$89, 0, E$88, 1)<OFFSET(Portfolio_Math!$J$101, E$89, 0, E$88, 1), OFFSET(Portfolio_Math!M$101, E$89, 0, E$88, 1)-OFFSET(Portfolio_Math!$J$101, E$89, 0, E$88, 1), 0)^2) / E$88)}

Let me take more time to think to a possibly less clunky solution (ideas welcome!)... I also wonder if we don't have similar mistakes elsewhere in the spreadsheet (obviously, the upside-SD formula is flawed too). In any case, many thanks for spotting the issue and explaining the faulty behavior. This will definitely have to be addressed in the next update.

PS. Here is the reference I used at the time for Sortino/Downside-SD math: http://www.redrockcapital.com/Sortino__ ... apital.pdf
apoptosis66
Posts: 3
Joined: Tue Jan 04, 2022 10:37 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by apoptosis66 »

siamond wrote: Sat Jan 22, 2022 3:53 pm Well, you seem to have a really good point. I also broke down the math and ended up with 8.12%. And yes, MIN(x, array) is just the min of all values, which wasn't the intent. Fact is very few people really care about those downside std-deviation metrics and I am afraid that I introduced this faulty formula between 2016 and 2017 when I restructured the spreadsheet and... nobody noticed my mistake until now. Shesh. :shock:
First off, if you feel bad about this you shouldn't. Programming is hard, and quite frankly I am in awww of what you have done in Excel. This stuff is so much easier in a full programing language. Second, your formula reads exactly like it should. In fact I thought it was my python code for almost 2 days. Anyways, you have given this community a gem of a spreadsheet, I hope don't feel bad about it.

As to what to do? I was thinking you would have to add a column somewhere of just the negative draw downs per fund per year , that seems obnoxious too. As you pointed out no one caught this, and I would say that is because Sharp and Soritino are out of favor these days. I honestly prefer Max Drawdown or Ulcer Index as my risk measure. Doesn't seem like people would care much if these were just removed probably not worth the headache.

Anyways thank you for the spreadsheet, it helped me understand how many of these concepts are programmed.
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

apoptosis66 wrote: Sat Jan 22, 2022 9:32 pmAs to what to do? I was thinking you would have to add a column somewhere of just the negative draw downs per fund per year , that seems obnoxious too. As you pointed out no one caught this, and I would say that is because Sharp and Sortino are out of favor these days. I honestly prefer Max Drawdown or Ulcer Index as my risk measure. Doesn't seem like people would care much if these were just removed probably not worth the headache.

Anyways thank you for the spreadsheet, it helped me understand how many of these concepts are programmed.
I'm trying to avoid being judgmental about risk metrics in the spreadsheet, because opinions differ a lot in this respect, but... on a personal note... I agree with you, Max Drawdown and Ulcer are much more palatable than Sharpe or Sortino. I'll keep them all though, probably sticking to the quick fix described in the previous post, I can't find a better way and this remains reasonably readable.

Official fix will come when I have all the 2021 numbers (a few require waiting until early February). Thanks again for the sharp (ah ah) eyes!
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

Here is the second official update for the new year, this time with final inflation numbers for 2021.

Please download the corresponding (Excel) spreadsheet by clicking here: https://bit.ly/34uwpKf

Rev21b
1. Simba 2021 update fully completed with following steps:
1a. Updated inflation numbers (US and Canada) with official 2021 data.
1b. Updated Stock Calculator data with Tyler's latest numbers derived from French-Fama library.
1c. Updated Shiller S&P 500 TR/PR/CPI with latest. Updated T-Bills with latest derived from FRED TB3MS.
2. Fixed error with calculation of downside and upside standard deviation, impacting Sortino ratio
3a. Vanguard VAIPX now tagged as TIPS (A) since it is an active fund; regular TIPS series is now based on Fidelity FIPDX, which is a passive index fund.
3b. Added in Raw_Data: Short Term TIPS series (based on Vanguard VTIP and its 0-5 index from Barclays) and Long Term TIPS series (based on Pimco LTPZ and its 15+ index from ICE BofA)
WHYCLIFFES
Posts: 10
Joined: Wed Feb 09, 2022 3:02 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by WHYCLIFFES »

Hi I would like to test the UPAR and RPAR (ETF) portfolio construction back to 1970 or 1975, but is it possible to extract monthly returns from the spreadsheet? It enables me to run even more tests.

RPAR is composed like this (I know that TIP does not go back that far):

Global Equities 25.0%
Commodity Producers 15.0%
Physical Gold 10.0%
Treasuries 35.0%
TIPS 35.0%
AlohaJoe
Posts: 6609
Joined: Mon Nov 26, 2007 1:00 pm
Location: Saigon, Vietnam

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by AlohaJoe »

WHYCLIFFES wrote: Sun Mar 06, 2022 12:52 am but is it possible to extract monthly returns from the spreadsheet?
No it isn't possible. It only has annual data.
maxweenus
Posts: 1
Joined: Fri Mar 18, 2022 1:22 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by maxweenus »

WHYCLIFFES wrote: Sun Mar 06, 2022 12:52 am Hi I would like to test the UPAR and RPAR (ETF) portfolio construction back to 1970 or 1975, but is it possible to extract monthly returns from the spreadsheet? It enables me to run even more tests.

RPAR is composed like this (I know that TIP does not go back that far):

Global Equities 25.0%
Commodity Producers 15.0%
Physical Gold 10.0%
Treasuries 35.0%
TIPS 35.0%
Sorry, new to the boards and the backtester but what asset classes are you using to backtest RPAR/UPAR? And how are you getting it back to 70/75 and over 100% allocaton on the sheet? Many thanks!

Anyone can comment based on the above if you can answer my spreadsheet questions. Thanks!
Gecko10x
Posts: 390
Joined: Thu Jun 06, 2013 8:10 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Gecko10x »

siamond wrote: Wed Feb 09, 2022 10:21 am Here is the second official update for the new year, this time with final inflation numbers for 2021.

Please download the corresponding (Excel) spreadsheet by clicking here: https://bit.ly/34uwpKf

Rev21b
1. Simba 2021 update fully completed with following steps:
1a. Updated inflation numbers (US and Canada) with official 2021 data.
1b. Updated Stock Calculator data with Tyler's latest numbers derived from French-Fama library.
1c. Updated Shiller S&P 500 TR/PR/CPI with latest. Updated T-Bills with latest derived from FRED TB3MS.
2. Fixed error with calculation of downside and upside standard deviation, impacting Sortino ratio
3a. Vanguard VAIPX now tagged as TIPS (A) since it is an active fund; regular TIPS series is now based on Fidelity FIPDX, which is a passive index fund.
3b. Added in Raw_Data: Short Term TIPS series (based on Vanguard VTIP and its 0-5 index from Barclays) and Long Term TIPS series (based on Pimco LTPZ and its 15+ index from ICE BofA)
Fantastic. Any chance you're updating the leveraged version? Figure I'd ask before attempting to combine & update myself, because I'm sure I'll break something :happy
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

Gecko10x wrote: Mon Mar 28, 2022 1:00 pmFantastic. Any chance you're updating the leveraged version? Figure I'd ask before attempting to combine & update myself, because I'm sure I'll break something :happy
Sorry, been a bit distracted by "other things in life"... I don't have plans to update the leveraged version on an annual basis.
McQ
Posts: 1425
Joined: Fri Jun 18, 2021 12:21 am
Location: California

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by McQ »

Historical expense ratios

First, Siamond, plaudits on your stewardship of the Simba spreadsheet. I’ve just now had a chance to delve more deeply into some of the series. It’s an impressive creation.

And forgive me if these questions are duplicates of others already answered, I only browsed pages 22 – 26 of this thread in preparation. I am hoping these questions will be of interest to others, hence this post rather than a PM to you.

This first set of questions concerns Columns AL and AK in the Raw Data tab of Rev21b. Column AL shows the return on VFIAX, while column AK is one of several columns that refer to the S&P 500 total return.

-If you could point me to a clarification of why the S&P 500 returns in columns AG, AJ and AK are not identical, I’d appreciate it.

First, I confirmed that the VFIAX returns in Column AL match those I get if I export data from Morningstar (no error there). Second, I confirmed that Column AK S&P 500 returns match those in my 2020 copy of the SBBI (no error there).

Next, I wanted to get a chart of historical expense ratios on the lowest cost Vanguard version of the 500 index fund (VFIAX currently). I don’t know of a source that calls out year-by-year expense ratios back to the fund's beginning in 1976 (point me there if you could?). I assume they used to be much higher, even on an index fund.

As a proxy, I simply subtract the column AK return from the Column AL VFIAX return. Technically this is tracking error, not expense ratio per se; but the results were interesting, as seen in this chart.

Image

At a gross level, there are no surprises. Expenses stayed at or above 50 bp for the first decade, back when “Bogle’s folly” was a thing. After 1987 a fairly rapid drop takes expenses down to 10 – 15 bp by the mid-1990s. As the 2000s proceed, expenses get cranked down to their current level of +/-5 bp. There are even a couple of small positive tracking errors (securities lending? Daily vs monthly dividend calculations?)

However, there are two anomalous points and I was hoping to draw on BH community expertise for an explanation. In 1983 tracking error jumps to 126 bp; in 1984 it falls to 6 bp. The average of the two would be more or less on trend, so there is probably a timing issue. Can anyone enlighten me? (Dividends paid before or after January 1st is my guess.)

Next, in 2009, which includes the bottom of the 2007-09 bear market, positive tracking error jumps to almost 16 bp. Anyone know why?

Thanks in advance for everyone's help.
You can take the academic out of the classroom by retirement, but you can't ever take the classroom out of his tone, style, and manner of approach.
AlohaJoe
Posts: 6609
Joined: Mon Nov 26, 2007 1:00 pm
Location: Saigon, Vietnam

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by AlohaJoe »

McQ wrote: Mon May 23, 2022 1:54 pm Next, I wanted to get a chart of historical expense ratios on the lowest cost Vanguard version of the 500 index fund (VFIAX currently). I don’t know of a source that calls out year-by-year expense ratios back to the fund's beginning in 1976 (point me there if you could?). I assume they used to be much higher, even on an index fund.
It doesn't go back to 1976 but all SEC filings (i.e. a prospectus which lists expense ratios) since 1994, I think, are available on Edgar. Admittedly, using Edgar is uh....not easy. But the info is all in there with digging.

The Bogleheads wiki includes ERs for the Total Stock Market Fund but it doesn't appear anyone has bothered to do the same for the S&P 500 fund.

https://www.bogleheads.org/wiki/Vanguar ... d_expenses
McQ
Posts: 1425
Joined: Fri Jun 18, 2021 12:21 am
Location: California

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by McQ »

AlohaJoe wrote: Mon May 23, 2022 8:46 pm
McQ wrote: Mon May 23, 2022 1:54 pm Next, I wanted to get a chart of historical expense ratios on the lowest cost Vanguard version of the 500 index fund (VFIAX currently). I don’t know of a source that calls out year-by-year expense ratios back to the fund's beginning in 1976 (point me there if you could?). I assume they used to be much higher, even on an index fund.
It doesn't go back to 1976 but all SEC filings (i.e. a prospectus which lists expense ratios) since 1994, I think, are available on Edgar. Admittedly, using Edgar is uh....not easy. But the info is all in there with digging.

The Bogleheads wiki includes ERs for the Total Stock Market Fund but it doesn't appear anyone has bothered to do the same for the S&P 500 fund.

https://www.bogleheads.org/wiki/Vanguar ... d_expenses
Thanks, AlohaJoe--I had forgotten about Edgar. Pity it only goes back to 1994; as Total Market goes that far back, I'd just use its compiled expenses (per your wiki link, much obliged).

My ultimate goal is to get expense estimates going much farther back, to deflate the reported stock market returns in the SBBI. You may be familiar with this paper by Jones on trading costs for stocks: https://papers.ssrn.com/sol3/papers.cfm ... _id=313681, titled "A Century of Stock Market Liquidity and Trading Costs." He makes it plain the trend in trading costs was not linear; in fact, humped in the middle of the century.

Long before Morningstar, an Arthur Wiesenberger complied mutual fund reports from the 1940s, with expense ratios; but I've only found a few that can be read online at books.google.com. Love to learn if any BH know of a library collection somewhere.
You can take the academic out of the classroom by retirement, but you can't ever take the classroom out of his tone, style, and manner of approach.
Wrench
Posts: 1055
Joined: Sun Apr 28, 2019 10:21 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Wrench »

McQ wrote: Tue May 24, 2022 1:39 pm
AlohaJoe wrote: Mon May 23, 2022 8:46 pm
McQ wrote: Mon May 23, 2022 1:54 pm Next, I wanted to get a chart of historical expense ratios on the lowest cost Vanguard version of the 500 index fund (VFIAX currently). I don’t know of a source that calls out year-by-year expense ratios back to the fund's beginning in 1976 (point me there if you could?). I assume they used to be much higher, even on an index fund.
It doesn't go back to 1976 but all SEC filings (i.e. a prospectus which lists expense ratios) since 1994, I think, are available on Edgar. Admittedly, using Edgar is uh....not easy. But the info is all in there with digging.

The Bogleheads wiki includes ERs for the Total Stock Market Fund but it doesn't appear anyone has bothered to do the same for the S&P 500 fund.

https://www.bogleheads.org/wiki/Vanguar ... d_expenses
Thanks, AlohaJoe--I had forgotten about Edgar. Pity it only goes back to 1994; as Total Market goes that far back, I'd just use its compiled expenses (per your wiki link, much obliged).

My ultimate goal is to get expense estimates going much farther back, to deflate the reported stock market returns in the SBBI. You may be familiar with this paper by Jones on trading costs for stocks: https://papers.ssrn.com/sol3/papers.cfm ... _id=313681, titled "A Century of Stock Market Liquidity and Trading Costs." He makes it plain the trend in trading costs was not linear; in fact, humped in the middle of the century.

Long before Morningstar, an Arthur Wiesenberger complied mutual fund reports from the 1940s, with expense ratios; but I've only found a few that can be read online at books.google.com. Love to learn if any BH know of a library collection somewhere.
University libraries seem to have copies. I randomly checked UPenn and Stanford and both have a book by him and the reports going back to 1941. So, check your nearest University library.

Wrench
McQ
Posts: 1425
Joined: Fri Jun 18, 2021 12:21 am
Location: California

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by McQ »

Wrench wrote: Tue May 24, 2022 3:01 pm
McQ wrote: Tue May 24, 2022 1:39 pm ...

Long before Morningstar, an Arthur Wiesenberger complied mutual fund reports from the 1940s, with expense ratios; but I've only found a few that can be read online at books.google.com. Love to learn if any BH know of a library collection somewhere.
University libraries seem to have copies. I randomly checked UPenn and Stanford and both have a book by him and the reports going back to 1941. So, check your nearest University library.

Wrench
Thanks Wrench--and indeed, my very own library proved to have quite a few. Now I'll look for an occasion to travel the 200 miles to get back there. :?
You can take the academic out of the classroom by retirement, but you can't ever take the classroom out of his tone, style, and manner of approach.
sycamore
Posts: 6360
Joined: Tue May 08, 2018 12:06 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by sycamore »

I don't know offhand where you can find the historical expense ratios. Here are some shots in the dark...

1. John Bogle used to have a website where he kept a bunch of his "stuff", possibly including research data.
http://johncbogle.com/wordpress/

Note that it's rather out of date - it's http, not https, for example. It's not maintained anymore as discussed in another thread.

But it does have a "data" page. There is a link to a Vanguard by the Numbers document - it tantalizingly shows assets and ER back to 1974, but that's for Vanguard as a whole, not a specific fund :(

2. The John C. Bogle Center for Financial Literacy. There's a "resources" page with links to various Bogle books & speeches, which themselves may have the ER data?

3. John Bogle's articles on SSRN. Maybe the articles include appendices with ER data?
McQ
Posts: 1425
Joined: Fri Jun 18, 2021 12:21 am
Location: California

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by McQ »

sycamore wrote: Tue May 24, 2022 4:17 pm I don't know offhand where you can find the historical expense ratios. Here are some shots in the dark...

1. John Bogle used to have a website where he kept a bunch of his "stuff", possibly including research data.
http://johncbogle.com/wordpress/

Note that it's rather out of date - it's http, not https, for example. It's not maintained anymore as discussed in another thread.

But it does have a "data" page. There is a link to a Vanguard by the Numbers document - it tantalizingly shows assets and ER back to 1974, but that's for Vanguard as a whole, not a specific fund :(

2. The John C. Bogle Center for Financial Literacy. There's a "resources" page with links to various Bogle books & speeches, which themselves may have the ER data?

3. John Bogle's articles on SSRN. Maybe the articles include appendices with ER data?
Interesting stuff, sycamore, thank you. Here's one for the thread: turns out Wiesenberger had a competitor/alternate information source named Hugh A Johnson. This link takes you to a 1953 copy of the Kiplinger personal finance magazine that describes both W and J and reproduces a table from J showing expense ratios among other data for the few dozen funds in existence then (pp. 9 ff.): https://books.google.com/books?id=TAcEAAAAMBAJ&lpg=PA10

A curious feeling reading investment advice for individual investors from 70 years ago ...
You can take the academic out of the classroom by retirement, but you can't ever take the classroom out of his tone, style, and manner of approach.
User avatar
hiddenpower
Posts: 564
Joined: Tue Nov 17, 2020 11:24 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by hiddenpower »

If I'm trying to simulate HFEA, does this look right?
VTSAX: 165%
VLGSX (LTT): 135%
It puts the portfolio at 300%, and gives an error box around the 300% usage.
I'm then seeing positive rolling returns in the 70s which is unexpected according to this simulated screenshot I found. I also noticed that I'm getting a lot of N/A values. I was expecting to search this section to find the max drawdown but it's filled with NAs (it's the same if I restore the values to the defaults). I imported the file into google sheets FWIW.
Hydromod
Posts: 1052
Joined: Tue Mar 26, 2019 10:21 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Hydromod »

hiddenpower wrote: Thu May 26, 2022 9:05 am If I'm trying to simulate HFEA, does this look right?
VTSAX: 165%
VLGSX (LTT): 135%
It puts the portfolio at 300%, and gives an error box around the 300% usage.
I'm then seeing positive rolling returns in the 70s which is unexpected according to this simulated screenshot I found. I also noticed that I'm getting a lot of N/A values. I was expecting to search this section to find the max drawdown but it's filled with NAs (it's the same if I restore the values to the defaults). I imported the file into google sheets FWIW.
Points to consider. It can makes a big difference how the 3x funds are calculated from the 1x. One needs to account for the daily movements (for volatility decay) and borrowing costs. There isn't enough information provided to understand how you calculated the results, but it can be far off if you are using annual values. I'd ask about that aspect in the HFEA thread.

Also, if I remember correctly, the Simba spreadsheet uses NA prior to data availability. If it has data but drawdowns are NA, I would follow up with Siamond directly.
User avatar
LadyGeek
Site Admin
Posts: 95696
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by LadyGeek »

^^^ Hydromod has a dedicated discussion here: Refinements to Hedgefundie's excellent approach
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: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

McQ wrote: Mon May 23, 2022 1:54 pmThis first set of questions concerns Columns AL and AK in the Raw Data tab of Rev21b. Column AL shows the return on VFIAX, while column AK is one of several columns that refer to the S&P 500 total return.

-If you could point me to a clarification of why the S&P 500 returns in columns AG, AJ and AK are not identical, I’d appreciate it.
Column AG are the returns as computed by Prof. Shiller, SIMULATING an index fund tracking the S&P 500 at no cost, from its individual components. This series has the great advantage of going far back in time, but Prof. Shiller's series has some strange quirks, notably averaging daily prices for a given month (including January for the year to year math). As opposed to snapshot from Jan 1st to Jan 1st like any real-life index or fund does nowadays to compute annual returns. AFAIK, this is due to a desire to be consistent with the spotty data about dividends in the old days, but this creates confusion to no end.

Column AJ and AK is data provided by S&P itself, so this is the real index. The 1936 version (AJ) is computed by reinvesting dividends on an annual basis while the regular version (AK) is computed with dividends reinvested as they come (like a modern fund would do). This being said, the 1936 'annual reinvesting' version becomes identical to the regular version in 1989, when daily reinvesting became common practice. There is also a series with dividends reinvested monthly which I didn't bother including.

The splicing process in column AM takes the best data we have for a given year (while using a very naive and rather debatable ER adjustment process, as neither the original Simba designers nor myself know how to do any better).
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

McQ wrote: Mon May 23, 2022 1:54 pmNext, I wanted to get a chart of historical expense ratios on the lowest cost Vanguard version of the 500 index fund (VFIAX currently). I don’t know of a source that calls out year-by-year expense ratios back to the fund's beginning in 1976 (point me there if you could?). I assume they used to be much higher, even on an index fund.
Morningstar Principia doe provide *some* history about ERs for a given fund, but unfortunately this only covers a decade or two. This can also be reconstructed by computing the geometric difference between Gross Return series and Total Return series, but same issue, not a lot of history.

As AlohaJoe suggested, the ER data is coming straight from Edgar, but in a more digestible format, albeit at a cost and very lacking in history. I choked at the idea of building a took scrubbing this kind of data... Bottomline, no simple answer here.
McQ wrote: Mon May 23, 2022 1:54 pmAs a proxy, I simply subtract the column AK return from the Column AL VFIAX return. Technically this is tracking error, not expense ratio per se; but the results were interesting, as seen in this chart.

Image

At a gross level, there are no surprises. Expenses stayed at or above 50 bp for the first decade, back when “Bogle’s folly” was a thing. After 1987 a fairly rapid drop takes expenses down to 10 – 15 bp by the mid-1990s. As the 2000s proceed, expenses get cranked down to their current level of +/-5 bp. There are even a couple of small positive tracking errors (securities lending? Daily vs monthly dividend calculations?)

However, there are two anomalous points and I was hoping to draw on BH community expertise for an explanation. In 1983 tracking error jumps to 126 bp; in 1984 it falls to 6 bp. The average of the two would be more or less on trend, so there is probably a timing issue. Can anyone enlighten me? (Dividends paid before or after January 1st is my guess.)

Next, in 2009, which includes the bottom of the 2007-09 bear market, positive tracking error jumps to almost 16 bp. Anyone know why?
I'd say it's probably about tracking error. Let me hazard a guess. In 2009, we witnessed unprecedented daily volatility and I strongly suspect this made real-life funds struggle to track the index on a few key days. Which, as luck would have it, could have resulted in better returns... I have no factual information about such guess though, I am just speculating. And I have very little clue about 1983, except that 82/83 had some rather troubled moments too (aka Volker's Bear).

(oh, and yes, security lending does provide a non-negligible 'bonus'; contact Barry Barnitz for some historical information about this, he's been tracking it for multiple funds for a while - yes, this is deep in Edgar too! At least for fairly recent years)
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

Hydromod wrote: Thu May 26, 2022 10:05 am
hiddenpower wrote: Thu May 26, 2022 9:05 am If I'm trying to simulate HFEA, does this look right?
VTSAX: 165%
VLGSX (LTT): 135%
It puts the portfolio at 300%, and gives an error box around the 300% usage.
I'm then seeing positive rolling returns in the 70s which is unexpected according to this simulated screenshot I found. I also noticed that I'm getting a lot of N/A values. I was expecting to search this section to find the max drawdown but it's filled with NAs (it's the same if I restore the values to the defaults). I imported the file into google sheets FWIW.
Points to consider. It can makes a big difference how the 3x funds are calculated from the 1x. One needs to account for the daily movements (for volatility decay) and borrowing costs. There isn't enough information provided to understand how you calculated the results, but it can be far off if you are using annual values. I'd ask about that aspect in the HFEA thread.

Also, if I remember correctly, the Simba spreadsheet uses NA prior to data availability. If it has data but drawdowns are NA, I would follow up with Siamond directly.
Yes, leveraged funds (and the HFEA strategy) cannot be simulated by using annual returns of regular funds. I PM'ed hiddenpower, pointing him to a separate thread we had a while ago on how to do such math in a better manner.

As to the NAs, I believe this was due to hiddenpower trying to use Google Sheets, which the Simba spreadsheet isn't compatible with. Excel or LibreOffice, that's it!
McQ
Posts: 1425
Joined: Fri Jun 18, 2021 12:21 am
Location: California

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by McQ »

siamond wrote: Thu May 26, 2022 5:22 pm
McQ wrote: Mon May 23, 2022 1:54 pmNext, I wanted to get a chart of historical expense ratios on the lowest cost Vanguard version of the 500 index fund (VFIAX currently). I don’t know of a source that calls out year-by-year expense ratios back to the fund's beginning in 1976 (point me there if you could?). I assume they used to be much higher, even on an index fund.
Morningstar Principia doe provide *some* history about ERs for a given fund, but unfortunately this only covers a decade or two. This can also be reconstructed by computing the geometric difference between Gross Return series and Total Return series, but same issue, not a lot of history.

As AlohaJoe suggested, the ER data is coming straight from Edgar, but in a more digestible format, albeit at a cost and very lacking in history. I choked at the idea of building a took scrubbing this kind of data... Bottomline, no simple answer here.
McQ wrote: Mon May 23, 2022 1:54 pmAs a proxy, I simply subtract the column AK return from the Column AL VFIAX return. Technically this is tracking error, not expense ratio per se; but the results were interesting, as seen in this chart.

Image

At a gross level, there are no surprises. Expenses stayed at or above 50 bp for the first decade, back when “Bogle’s folly” was a thing. After 1987 a fairly rapid drop takes expenses down to 10 – 15 bp by the mid-1990s. As the 2000s proceed, expenses get cranked down to their current level of +/-5 bp. There are even a couple of small positive tracking errors (securities lending? Daily vs monthly dividend calculations?)

However, there are two anomalous points and I was hoping to draw on BH community expertise for an explanation. In 1983 tracking error jumps to 126 bp; in 1984 it falls to 6 bp. The average of the two would be more or less on trend, so there is probably a timing issue. Can anyone enlighten me? (Dividends paid before or after January 1st is my guess.)

Next, in 2009, which includes the bottom of the 2007-09 bear market, positive tracking error jumps to almost 16 bp. Anyone know why?
I'd say it's probably about tracking error. Let me hazard a guess. In 2009, we witnessed unprecedented daily volatility and I strongly suspect this made real-life funds struggle to track the index on a few key days. Which, as luck would have it, could have resulted in better returns... I have no factual information about such guess though, I am just speculating. And I have very little clue about 1983, except that 82/83 had some rather troubled moments too (aka Volker's Bear).

(oh, and yes, security lending does provide a non-negligible 'bonus'; contact Barry Barnitz for some historical information about this, he's been tracking it for multiple funds for a while - yes, this is deep in Edgar too! At least for fairly recent years)
Thanks so much for both your replies, Siamond, I really appreciate it. Very helpful clarifications.

I know you had to throw up your hands about backwards projection of expense ratios. I don't see you had any choice. If you ever revisit the issue, the paper by C.M Jones in my reply to AlohaJoe will be helpful. For an ongoing project I will have to estimate expense ratios on the SBBI S&P returns back to 1926, and on the Shiller data for a few years more. As Jones notes, the equity premium has probably been overstated by 50 to 100bp, once you take the standpoint of what a pre-Vanguard investor, with Vanguard intent, could have achieved in the marketplace of those years after expected expenses.

I'll share results once achieved (not next week or next month :-)
You can take the academic out of the classroom by retirement, but you can't ever take the classroom out of his tone, style, and manner of approach.
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

McQ wrote: Thu May 26, 2022 10:15 pmI know you had to throw up your hands about backwards projection of expense ratios. I don't see you had any choice. If you ever revisit the issue, the paper by C.M Jones in my reply to AlohaJoe will be helpful. [...] I'll share results once achieved (not next week or next month
I took a quick look and this certainly will be handy for your purpose, but for Simba, we would need a full history for all funds of relevance (some displaying very distinct dynamics than others, e.g. international funds), which is a much bigger challenge! So I think we're pretty much stuck with the less-than-ideal current approach. This bring said, yes, I'll be quite interested to read about your results.
User avatar
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Tyler9000 »

McQ wrote: Thu May 26, 2022 10:15 pm I'll share results once achieved (not next week or next month :-)
I just wanted to second Siamond's interest in your work on this. I look forward to seeing what you're able to share.
McQ
Posts: 1425
Joined: Fri Jun 18, 2021 12:21 am
Location: California

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by McQ »

Tyler9000 wrote: Fri May 27, 2022 3:16 pm
McQ wrote: Thu May 26, 2022 10:15 pm I'll share results once achieved (not next week or next month :-)
I just wanted to second Siamond's interest in your work on this. I look forward to seeing what you're able to share.
Thanks Tyler, I do well with encouragement :sharebeer

As a down payment, in case I get distracted and this effort takes a while, here is a periodization for “estimating the historical costs of owning a broad stock market index before the era of the index fund,” with a few notes on sources discovered thus far.

My motivation: before today’s era of ETF index funds you couldn’t own the stock market or any good proxy for it without incurring significant costs. Today you pay single digit basis points annually for an index ETF; as recently as 1990 you paid 20-25 bp; and when the Vanguard 500 index fund began (first full year was 1977) you paid about 60 bp.

Accordingly, any equity premium or long-term stock investment return estimated using the Stocks, Bonds, Bills & Inflation yearbook—or the Simba spreadsheet before 1977—must be overstated, relative to the returns that could have been realized relative to expenses. And as John Bogle showed on many an occasion, having to subtract 50 – 100 bp from an annualized return of about 10% has a baleful effect on wealth accumulation over longer periods.

A typical retort might be that all the other historical asset data out there is also estimated without costs, therefore the omission cancels out when it comes to calculation of the equity premium or other asset comparison. But that’s not correct: it has always been possible to purchase and hold a government debt instrument at very low cost. The overstatement is concentrated in historical stock market returns (and corporate or total bond returns).

Periods

Again, it is not an issue after December 31, 1976: once simply uses the VFIAX returns in the Simba spreadsheet to get the after-expense return from owning a broad portfolio of stocks.

The more distant past can be divvied up as follows:

1940s to 1976: Wiesenberger, Johnson and similar Morningstar predecessors table expense ratios and fund returns. These are the records I’ll be examining shortly. I’m hoping to get tracking error as well as average expense ratios.

1927 to 1940s: There is an SEC report for the earlier part of the period. It is remarkably modern in tone, i.e., compares fund performance to an unmanaged index (S&P 90). There is a later SEC report from 1962 generally accounted to have kicked off the modern era of academic mutual fund research; I am working through it now.

1918 to 1927: There is only one open-end fund in this era, and closed-end funds may not be tabled. I’m expecting to take the 1927-1940 data and extrapolate back to my 1918 beginning (that’s when long Treasury bonds of the modern sort became available, and is the terminus of my current project). The Jones paper cited earlier will be helpful in making that extrapolation.

1917 and before: I probably won’t attempt. It would all be guesswork (although Jones’ cost estimates do go back to 1900).

An important finding from Jones: expenses have not been constant over time. There can be no simple extrapolation backwards from, say, the 1977-1981 expense ratio on VFIAX.

Here is an interesting table from Bogle’s blog, which I found courtesy of sycamor. A few comments and then a regression analysis follow.

Image

1) In the 1970s and early 1980s, Vanguard could not offer an index fund for a substantially lower cost than its existing stable of actively managed funds. The overall Vanguard expense ratio of 60 – 66 bp is about the same as the tracking error for VFIAX in those years (see my earlier post).

2) The expenses on everything drop steadily over time. Bogle had every reason to hammer the rest of the industry on costs: the Vanguard record shows what a firm committed to passing on as much of the asset return as possible to investors could accomplish.

3) I ran a simple regression on selected columns, with expense ratio expressed in bp as the dependent variable.

Findings

a) The passage of time explains most of the drop in expense ratios: R-square = 92.2%. The beta on calendar year is -1.3 bp per year.
b) Next I added assets under management (e.g., economies of scale). This slightly improves prediction (R-square 94.5%).
c) Then I added assets in index funds. This doesn’t improve R-square much (95.4%), but the beta is significant, and it does amp up the calendar year beta to -2 bp per year.

Conclusion

The passage of time (=developments in the markets) explains most of the steady drop in Vanguard fund expense.

Even as recently as four decades ago, it was much more costly to offer ownership of a broad stock fund to investors, with an annual drag over 50 bp. Nor was it much cheaper to index than to actively manage a fund.

Not sure what I will find in the years before. Jones argued that costs peaked in the 1960s and early 1970s, so costs in the 1930s, 1940s, and 1950s may be lower than in the first years of VFIAX. Bogle, in another book, has Mass Investors Trust, the oldest open-ended fund, with an expense < 20 bp by 1960.

I’ll let you know what I find. In the meantime, a query for anyone: Almost all the early open-ended funds were load funds, typically 8.5%. If I include the load, after-cost historical returns are going to be that much lower still. Is that kosher, or thumb on the scale?
You can take the academic out of the classroom by retirement, but you can't ever take the classroom out of his tone, style, and manner of approach.
Alpha4
Posts: 176
Joined: Tue Apr 17, 2012 8:47 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Alpha4 »

Dr. McQuarrie,

Just a quick FYI on there being only one open-end fund during the era 1918-1927:

The open-end fund you mentioned in your post (Mass Investors Trust; ticker symbol MITTX) was indeed the first (oldest) open ended stock mutual fund as far as I know; it started in late July of 1924. With that said, I do recall that another open-ended stock mutual fund (Putnam Investors; ticker symbol PINVX) was started in mid-December of 1925 and so also was open for part of this era. All of the descriptive data I have for it (albeit only back to 1998....although I do have monthly total returns back to inception in 1925) shows it as a large-blend stock fund. As far as you are aware, was Putnam Investors ever a blended fund (which would possibly disqualify it from being a pure "stock fund" for purposes of your costs analysis)? I thought the first blended stock/bond funds were Vanguard Wellington (1929), Dodge and Cox Balanced (1931), and the George Putnam Balanced Fund of Boston (1937).
McQ
Posts: 1425
Joined: Fri Jun 18, 2021 12:21 am
Location: California

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by McQ »

Alpha4 wrote: Sun Jun 05, 2022 10:20 pm Dr. McQuarrie,

Just a quick FYI on there being only one open-end fund during the era 1918-1927:

The open-end fund you mentioned in your post (Mass Investors Trust; ticker symbol MITTX) was indeed the first (oldest) open ended stock mutual fund as far as I know; it started in late July of 1924. With that said, I do recall that another open-ended stock mutual fund (Putnam Investors; ticker symbol PINVX) was started in mid-December of 1925 and so also was open for part of this era. All of the descriptive data I have for it (albeit only back to 1998....although I do have monthly total returns back to inception in 1925) shows it as a large-blend stock fund. As far as you are aware, was Putnam Investors ever a blended fund (which would possibly disqualify it from being a pure "stock fund" for purposes of your costs analysis)? I thought the first blended stock/bond funds were Vanguard Wellington (1929), Dodge and Cox Balanced (1931), and the George Putnam Balanced Fund of Boston (1937).
Hello Alpha4:
There were several other open-ended funds started in the 1920s and early 1930s. (“oldest mutual funds” is a productive search term.) Here’s a chart based on those that still have performance records in Morningstar. I start in 1934 because some of the early ones were just starting circa 1929, and would have held plenty of cash during the crash.

Image

Results are as expected: one or another outperforming the S&P index for a time, but the index outperforming the average, by 80 – 100 bp annualized (a reasonable estimate of expenses plus turnover costs).

I knew about PINVX but couldn’t add it because Morningstar no longer carries its performance record (merged into another fund). Love to know where you found the older historical returns for PINVX!

One avenue for research is to get performance for all the large funds, note the tracking error relative to the index, and then define year by year performance for that group of funds as “the best an ordinary investor could do in the absence of an index fund.”

The problem: intense survivorship bias, likely positive (losers didn’t survive ninety years for me to find them in Morningstar). The alternative is to get average expense ratio for a census of funds then in existence, and subtract that from the S&P index. That under-estimates total cost, but that may be the preferable bias. I'll be using closed end funds too.

PS: I’m not too worried about “blend,” easy to imagine that PINVX started as a stock fund and then changed its mission. If it was always a balanced fund, that will emerge in a comparison with Wellington etc.
You can take the academic out of the classroom by retirement, but you can't ever take the classroom out of his tone, style, and manner of approach.
Alpha4
Posts: 176
Joined: Tue Apr 17, 2012 8:47 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Alpha4 »

McQ wrote: Mon Jun 06, 2022 4:31 pm
Alpha4 wrote: Sun Jun 05, 2022 10:20 pm Dr. McQuarrie,

Just a quick FYI on there being only one open-end fund during the era 1918-1927:

The open-end fund you mentioned in your post (Mass Investors Trust; ticker symbol MITTX) was indeed the first (oldest) open ended stock mutual fund as far as I know; it started in late July of 1924. With that said, I do recall that another open-ended stock mutual fund (Putnam Investors; ticker symbol PINVX) was started in mid-December of 1925 and so also was open for part of this era. All of the descriptive data I have for it (albeit only back to 1998....although I do have monthly total returns back to inception in 1925) shows it as a large-blend stock fund. As far as you are aware, was Putnam Investors ever a blended fund (which would possibly disqualify it from being a pure "stock fund" for purposes of your costs analysis)? I thought the first blended stock/bond funds were Vanguard Wellington (1929), Dodge and Cox Balanced (1931), and the George Putnam Balanced Fund of Boston (1937).
Hello Alpha4:
There were several other open-ended funds started in the 1920s and early 1930s. (“oldest mutual funds” is a productive search term.) Here’s a chart based on those that still have performance records in Morningstar. I start in 1934 because some of the early ones were just starting circa 1929, and would have held plenty of cash during the crash.

Image

Results are as expected: one or another outperforming the S&P index for a time, but the index outperforming the average, by 80 – 100 bp annualized (a reasonable estimate of expenses plus turnover costs).

I knew about PINVX but couldn’t add it because Morningstar no longer carries its performance record (merged into another fund). Love to know where you found the older historical returns for PINVX!

One avenue for research is to get performance for all the large funds, note the tracking error relative to the index, and then define year by year performance for that group of funds as “the best an ordinary investor could do in the absence of an index fund.”

The problem: intense survivorship bias, likely positive (losers didn’t survive ninety years for me to find them in Morningstar). The alternative is to get average expense ratio for a census of funds then in existence, and subtract that from the S&P index. That under-estimates total cost, but that may be the preferable bias. I'll be using closed end funds too.

PS: I’m not too worried about “blend,” easy to imagine that PINVX started as a stock fund and then changed its mission. If it was always a balanced fund, that will emerge in a comparison with Wellington etc.
Dr. McQuarrie,

From everything I can tell PINVX was always a stock fund rather than a blended/balanced fund (that's not saying I can't be wrong on this, though....I may very well be).

PINVX is actually only the third oldest open-ended fund ever (i.e. that ever existed even if it didn't exist all the way to today); State Street Investment Trust (which from what I understand was merged into another State Street fund a long time ago) which had the ticker symbol STSTX was actually started in late July 1924; see https://mutualfundobserver.com/discuss/ ... a-teenager ; also see the link in my PM.

Regarding PINVX's returns...check your PMs.
McQ
Posts: 1425
Joined: Fri Jun 18, 2021 12:21 am
Location: California

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by McQ »

Alpha4 wrote: Mon Jun 06, 2022 9:42 pm ...

Dr. McQuarrie,

From everything I can tell PINVX was always a stock fund rather than a blended/balanced fund (that's not saying I can't be wrong on this, though....I may very well be).

PINVX is actually only the third oldest open-ended fund ever (i.e. that ever existed even if it didn't exist all the way to today); State Street Investment Trust (which from what I understand was merged into another State Street fund a long time ago) which had the ticker symbol STSTX was actually started in late July 1924; see https://mutualfundobserver.com/discuss/ ... a-teenager ; also see the link in my PM.

Regarding PINVX's returns...check your PMs.
Much obliged, alpha4! Not sure how many BH are as archaeologically minded as you and me, but FWIW, here are links to the two key SEC reports on mutual funds, 1942 and 1962, showing performance, assets under management, fees, and much more.

https://books.google.com/books?id=NW0l9 ... &q&f=false You want this Part 2 of the 2000 page report for performance information

https://books.googleusercontent.com/boo ... iXq-afgsDY aka, the Wharton study; this one launched the modern academic literature

Ippolito reviews the literature to 1992 on actively managed mutual fund alpha: https://www.tandfonline.com/doi/abs/10. ... ode=ufaj20

Carhart 1997 gives the classic, "no outperformance to be found:" https://onlinelibrary.wiley.com/doi/ful ... .tb03808.x

Best wishes to all.
You can take the academic out of the classroom by retirement, but you can't ever take the classroom out of his tone, style, and manner of approach.
User avatar
martincmartin
Posts: 900
Joined: Wed Jul 02, 2014 3:04 pm
Location: Boston, MA USA

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by martincmartin »

Thanks again Simba for your incredibly useful spreadsheet.

I'm looking at the S&P data from Shiller's ie_data.xls, which I traced back to column NM of the Raw Data tab. You have annual total returns, whereas Shiller has monthly value and dividends. When I try to compute the TR, I get slightly different numbers.

For example, for 1873, you have -2.5143686476733%. The average of the 12 dividend numbers (1873.01 through 1873.12) is 0.31625. For S&P price, if I use Dec to Dec, I get (4.42 + 0.31625) / 5.07 - 1 = -6.58%. Jan to Jan gives (4.66 + 0.31625) / 5.11 - 1 = -2.617417%, which is closer but still not the same.

What's your methodology for going from Shiller's monthly S&P 500 price and dividend numbers, to your annual total returns in column NM of the Raw Data tab?
User avatar
martincmartin
Posts: 900
Joined: Wed Jul 02, 2014 3:04 pm
Location: Boston, MA USA

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by martincmartin »

siamond wrote: Thu May 26, 2022 10:46 pm .
Hey Siamond, any word on how you computed the annual changes from the monthly values in Shiller's S&P 500? See the comment just above this one.
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

martincmartin wrote: Sun Oct 16, 2022 7:35 pmI'm looking at the S&P data from Shiller's ie_data.xls, which I traced back to column NM of the Raw Data tab. You have annual total returns, whereas Shiller has monthly value and dividends. When I try to compute the TR, I get slightly different numbers.

For example, for 1873, you have -2.5143686476733%. The average of the 12 dividend numbers (1873.01 through 1873.12) is 0.31625. For S&P price, if I use Dec to Dec, I get (4.42 + 0.31625) / 5.07 - 1 = -6.58%. Jan to Jan gives (4.66 + 0.31625) / 5.11 - 1 = -2.617417%, which is closer but still not the same.

What's your methodology for going from Shiller's monthly S&P 500 price and dividend numbers, to your annual total returns in column NM of the Raw Data tab?
Shiller does something funky, averaging the S&P 500 price over the month of January. I picked Jan to Jan to mimic his annual math the best I could, but this is somewhat dubious (I mean, the big picture of cumulative past returns over the year will be ok, but a detailed view year over year gets more weird).

I think the difference between your math and mine is that I assumed monthly dividend reinvestment whenever I could (including the 1871-1936 time period). Hence slightly better returns than what you have. And then, for a reason I cannot understand, new updates of the Shiller spreadsheet often tend to lead to micro-differences in the past... But nothing changing the big picture.

PS. please send me a private message if you want a quick response. I don't browse the BH forum very often nowadays.
toktok
Posts: 4
Joined: Mon Nov 14, 2022 4:30 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by toktok »

I was hoping to find someone, who might be able to answer a mathemathical question on the calculation of portfolio returns in this thread.

I posted a question about how to properly deduct inflation and ongoing costs from a series of monthly index values here: viewtopic.php?t=390271

I'd be very grateful for any kind of help!
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

toktok wrote: Mon Nov 14, 2022 7:59 am I was hoping to find someone, who might be able to answer a mathemathical question on the calculation of portfolio returns in this thread.

I posted a question about how to properly deduct inflation and ongoing costs from a series of monthly index values here: viewtopic.php?t=390271
I'd be very grateful for any kind of help!
I took a tab at your questions on the same thread. As a side note, the Simba spreadsheet uses Net TR returns (and then does a good deal of expense ratio and inflation math), although I advised you in the other thread to use Gross Return (GR) returns. This is kind of historical (I took over this spreadsheet a few years ago, but it existed for a much longer time). I often have some qualms about this GR vs. NR choice and ponder if we shouldn't re-align everything on GR numbers, and keep tax considerations out of this spreadsheet.

But then a LOT of investors simply don't perceive this subtle thing (per-county tax withholding) and/or don't get a tax credit (notably when using such funds in a tax-sheltered vehicle like a 410k or an IRA). Which makes the case for using Net TR series (NR in short), so that people don't get over-inflated expectations. Also index funds tend to document the NR series as the index of reference (although my cynical self thinks it's just a marketing ploy!). Anyhoo... Long story short, Simba uses NR returns for international funds, for better or worse.
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

First Simba update with 2022 returns. Please note that inflation numbers are NOT finalized. As usual, there will be a final update at some point early February.

You can download the spreadsheet here: http://bit.ly/3XdKLFj

Here is the direct link in case your browser is giving you grief (like dcabler reported below):
https://drive.google.com/uc?id=1fexxSsT ... t=download

Rev 22a
1a. Added 2022 annual returns: funds and index returns updated
1b. Expense Ratios (ER) updated, main changes: BRSIX 0.78; EFV 0.34; VTRIX 0.36; VINEX: 0.40; VWNEX: 0.20; VGELX: 0.33; VGHAX: 0.25
1c. Inflation is a rough estimate for now; updates for some synthetic models are pending
2a. Updated in Raw_Data: replaced VMMXX (investor fund, now closed) by VMRXX (admiral fund) - Vanguard Cash Reserves Federal Money Market Fund
2a. Updated in Raw_Data: removed PAGPX (now closed), made AUBAX primary fund for Global Bonds (Active, Unhedged)
Last edited by siamond on Wed Jan 11, 2023 10:17 pm, edited 4 times in total.
HansT
Posts: 57
Joined: Wed Feb 17, 2021 6:37 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by HansT »

Thank you Siamond! Very much appreciated, as always.
Please donate to support BH! https://bogleheads.org/support.php
dcabler
Posts: 4544
Joined: Wed Feb 19, 2014 10:30 am
Location: TX

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by dcabler »

siamond wrote: Sat Jan 07, 2023 3:12 pm First Simba update with 2022 returns. Let's wait a few days to update the wiki page, to see if early users spot something wrong... Please note that inflation numbers are NOT finalized. As usual, there will be a final update at some point early February.

You can download the spreadsheet here: http://bit.ly/3VWb15r

Rev 22a
1a. Added 2022 annual returns: funds and index returns updated
1b. Expense Ratios (ER) updated, main changes: BRSIX 0.78; EFV 0.34; VTRIX 0.36; VINEX: 0.40; VWNEX: 0.20; VGELX: 0.33; VGHAX: 0.25
1c. Inflation is a rough estimate for now; updates for some synthetic models are pending
2a. Updated in Raw_Data: replaced VMMXX (investor fund, now closed) by VMRXX (admiral fund) - Vanguard Cash Reserves Federal Money Market Fund
2a. Updated in Raw_Data: removed PAGPX (now closed), made AUBAX primary fund for Global Bonds (Active, Unhedged)
Siamond - my browser is giving an error message that that the spreadsheet pointed to by your link above can't be downloaded securely.

Cheers.
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

dcabler wrote: Sun Jan 08, 2023 7:49 amSiamond - my browser is giving an error message that that the spreadsheet pointed to by your link above can't be downloaded securely.
I added the direct download link to the previous post. We just can't understand why dcabler's browser (MS Edge) is giving him grief... :shock:

If anybody else has downloading problems, please let me know.
User avatar
Harry Livermore
Posts: 1937
Joined: Thu Apr 04, 2019 5:32 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Harry Livermore »

Siamond, thank you so much for all your hard work on this. I would thank Simba too, but I'm not sure the message would get through- I see Simba has not made a post in many years.
It's very comprehensive and entertaining! I had downloaded a copy a long time ago and not done much with it, but have spent a lot of time with the most recent version.
My own portfolio stacks up very well and it's been a positive mental/ emotional reinforcement for me.
Dr. McQ's recent contributions to this thread have been very informative as well. And there have been many thoughtful posts from very smart people.
What a great resource this group is. I feel like I am standing on the shoulders of very well-meaning giants.
Cheers
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

Harry Livermore wrote: Wed Feb 08, 2023 8:02 am Siamond, thank you so much for all your hard work on this. I would thank Simba too, but I'm not sure the message would get through- I see Simba has not made a post in many years.
It's very comprehensive and entertaining! I had downloaded a copy a long time ago and not done much with it, but have spent a lot of time with the most recent version.
My own portfolio stacks up very well and it's been a positive mental/ emotional reinforcement for me. [...]
Thanks a lot, Harry. I'm glad this proved useful for you.
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

New Simba update with 2022 returns. Inflation numbers are now finalized. Hopefully, 2022 data is stable now.

You can download the spreadsheet here: http://bit.ly/3XjjrnY
(copy the link to a separate browser tab/window if it doesn't download from here)

Rev22b
1. Simba 2022 update fully completed with following steps:
1a. Updated inflation numbers (US and Canada) with official 2022 data.
1b. Updated Stock Calculator data with Tyler's latest numbers derived from French-Fama library.
1c. Updated Shiller S&P 500 TR/PR/CPI with latest. Updated T-Bills with latest derived from FRED TB3MS.
2. Introduced in Raw_Data: iShares Premium Money Market ETF (CMR) as primary fund for T-Bills Canada (2009+)
User avatar
Harry Livermore
Posts: 1937
Joined: Thu Apr 04, 2019 5:32 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Harry Livermore »

OK, I do have a question (really an observation) for the group.
All of the "perpetual withdrawal rates" seem insanely high to me. Isn't the 4% "rule" supported for a 30-year period? How can so many of these portfolios sustain much higher withdrawal rates for a 38-year period?
I'm really focusing on the "Lazy Portfolios" tab. It's a 38 year period and I see 7.94% for my custom lazy portfolio, 8.59% for Taylor's 3-fund, etc. The Harry Browne portfolio is the only one even remotely close to a 4% withdrawal rate.
I followed to the link in ReadMe to Horsemouth's PDF (which I had already read and downloaded previously) and, while interesting, doesn't offer much support to the layman (me)
Obviously it's been a profitable 38 years, and the 4% "rule" is rolling 30-year periods across all history we have available. But the disparity seems crazily large. I guess it just "is what it is", given recent history?
Apologies if this had already been discussed in this thread like 8 years ago or something... ;)
Cheers
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

Harry Livermore wrote: Thu Feb 09, 2023 7:22 am OK, I do have a question (really an observation) for the group.
All of the "perpetual withdrawal rates" seem insanely high to me. Isn't the 4% "rule" supported for a 30-year period? How can so many of these portfolios sustain much higher withdrawal rates for a 38-year period?
I'm really focusing on the "Lazy Portfolios" tab. It's a 38 year period and I see 7.94% for my custom lazy portfolio, 8.59% for Taylor's 3-fund, etc. The Harry Browne portfolio is the only one even remotely close to a 4% withdrawal rate. [..]
I suspect you are looking at row 109 in "Lazy Portfolios". This value is the perpetual withdrawal rate (i.e. starting portfolio value is maintained by the end of the time interval) for ONE very specific time interval, i.e. 1985 to 2022. Which turned out to be a great starting year, starting by ~15 years of a steep bull market. Whoever retired in the late 80s or early 90s got very fortunate (Taylor himself did that, if I remember properly).

The so-called "4% rule" has been backtested across EVERY possible 30-years intervals since 1926 or so, for a simple portfolio of US stocks and bonds. Including the 1929 crisis and the 1973 crisis, and the worst possible starting years (e.g. 1965/1966). It also aims at depleting the portfolio balance at the end of the 30-years period.

In other words, you singled out one specific data point (and a rather rosy one!) instead of the worst possible data point. Now if you take a look at rows 127 (deplete portfolio) and 128 (maintain portfolio), you do get the worst case. Well, not exactly the worst, the spreadsheet is set by default (cell D125) to select the 5% percentile of 'worst cases', which is the usual way of looking at such SWR-like data (which would be row 127).

Now you are going to tell me that those values in row 127 still look really high. Well, the problem with the lazy portfolios is that most of them include asset classes which do not have a ton of historical data. Check cell E86, its default value is 1985. And well, there are only a few 30-years cycles between 1985 and 2022 and they all remain fairly rosy. If you were to change E86 to 1970, you will see a bunch of warnings on row 83 telling you that historical data is lacking for many of those portfolios. For those remaining valid (including Taylor's 3 funds), you'll see that the value in row 127 is lower, because this now includes starting years right before the oil crisis hit. But, we're still in the 5%-ish range because the worst possible starting years are not part of the 1970-2022 time interval.

So... change again cell E86, setting it to 1926 now. Very few portfolios have enough historical data, but Peter Bernstein 60/40 does (and is pretty much the only portfolio which was used for the Trinity study which came up with this 4% SWR rule). Go back to row 127 and here you are, you'll find something closer to 4%. And if you make the percentile criterion more stringent (be my guest, enter 0 in cell D125), you will find the worst case. That is... for the US... Trinity-like studies performed for other countries ended up with significantly lower values (e.g. Italy, Spain, Japan) for 1970+ time periods, largely due to awful inflation periods. I wrote a lengthy blog article on the matter!

This was the technical explanation, I hope I didn't lose you in all those cells and numbers... Now for some more judgmental thoughts... The "4% rule" seems rather silly to me. This is essentially one single worst data point in the US. Something worst might happen, mind you (as experienced by other countries, or simply because the future ain't the past). Something better is much more likely, but definitely not guaranteed. I think the only answer to this consideration is to either accumulate LARGE savings (way more than you'll actually need) or to use a variable withdrawal method (e.g. VPW, ABW, fixed percentage of current portfolio, etc) instead of this "4% of starting portfolio, inflation-adjusted" idea. Something much more adaptive to an unknown future. And... such discussion goes way beyond the scope of this Simba thread! :wink:
User avatar
Harry Livermore
Posts: 1937
Joined: Thu Apr 04, 2019 5:32 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Harry Livermore »

Thanks for the detailed response. I think I intuited most of that prior to my response, but wanted to confirm.
Yes, row 109. I understand that the sequence starting 38 years ago was a particularly good one and we cannot know in advance what our own SOR risk is. Another forum member sent me a PM pointing me to some other resources so as not to bog down this thread.
I'm certainly planning on using some sort of variable withdrawal method, once we start drawing on the portfolio.
The variety of tools available to the layman nowadays is amazing. Thanks again, Siamond.
Cheers
robertdc
Posts: 2
Joined: Tue Feb 14, 2023 11:34 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by robertdc »

Hi,

Canadian Investor, been looking for months to find something almost exactly like this. As someone much less literate in excel though, I'm finding it terribly difficult to get it to do what I want it to, namely show all returns in CAD, and include Canadian asset classes for home bias. I can see the data in the raw data sheet, but cant use them in the sheet's built in calculators? Is there a setting for this? Or are they included for reference only? I can't for the life of me find any information or instruction on how to use Canadian Data in the document anywhere, in the document nor elsewhere.

Thanks all for putting it together, it's quite an amazing source of information.

Thanks,

Rob
B4Xt3r
Posts: 744
Joined: Thu Sep 29, 2016 5:56 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by B4Xt3r »

Just wanted to add my 2023 thanks to this project. With last years returns, my Montecarlo says I gotta save a tiny bit more now for retirmenent. :oops: :D :sharebeer
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by siamond »

robertdc wrote: Tue Feb 14, 2023 11:46 pm Canadian Investor, been looking for months to find something almost exactly like this. As someone much less literate in excel though, I'm finding it terribly difficult to get it to do what I want it to, namely show all returns in CAD, and include Canadian asset classes for home bias. I can see the data in the raw data sheet, but cant use them in the sheet's built in calculators? Is there a setting for this? Or are they included for reference only? I can't for the life of me find any information or instruction on how to use Canadian Data in the document anywhere, in the document nor elsewhere.
Hi there. Yes, the intent of Canadian series is mostly for reference. With a bit of care, one can set up the spreadsheet to use all the tools with those series though. This would be limitative as there are not that many CAD series and the spreadsheet makes an implicit assumption that portfolios are made of a single/consistent currency, but you could certainly run backtests for 3-funds/4-funds portfolios constructs.

Let me assemble a slightly custom version of the spreadsheet with such CAD-centric set-up and I'll share (probably later today). If you find it useful, then maybe I'll take a stab at documenting the process.
Phyneas
Posts: 336
Joined: Tue Apr 27, 2021 9:10 pm

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Phyneas »

siamond wrote: Thu Feb 16, 2023 8:01 am
robertdc wrote: Tue Feb 14, 2023 11:46 pm Canadian Investor, been looking for months to find something almost exactly like this. As someone much less literate in excel though, I'm finding it terribly difficult to get it to do what I want it to, namely show all returns in CAD, and include Canadian asset classes for home bias. I can see the data in the raw data sheet, but cant use them in the sheet's built in calculators? Is there a setting for this? Or are they included for reference only? I can't for the life of me find any information or instruction on how to use Canadian Data in the document anywhere, in the document nor elsewhere.
Hi there. Yes, the intent of Canadian series is mostly for reference. With a bit of care, one can set up the spreadsheet to use all the tools with those series though. This would be limitative as there are not that many CAD series and the spreadsheet makes an implicit assumption that portfolios are made of a single/consistent currency, but you could certainly run backtests for 3-funds/4-funds portfolios constructs.

Let me assemble a slightly custom version of the spreadsheet with such CAD-centric set-up and I'll share (probably later today). If you find it useful, then maybe I'll take a stab at documenting the process.
The Canadian Couch Potato version.
60% AVGE | 20 Year TIPS LMP | 5% Cash
User avatar
Peculiar_Investor
Site Admin
Posts: 2445
Joined: Thu Oct 20, 2011 12:23 am
Location: Calgary, AB 🇨🇦
Contact:

Re: Simba's backtesting spreadsheet [a Bogleheads community project]

Post by Peculiar_Investor »

robertdc wrote: Tue Feb 14, 2023 11:46 pm Hi,

Canadian Investor, been looking for months to find something almost exactly like this. As someone much less literate in excel though, I'm finding it terribly difficult to get it to do what I want it to, namely show all returns in CAD, and include Canadian asset classes for home bias.
Fellow Canadian here. You might want to try Stingy Investor: Periodic Table of Annual Returns for Canadians and it's companion tool Asset Mixer.
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
Post Reply