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: 3333
Joined: Mon May 28, 2012 5:50 am

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Mon Oct 24, 2016 10:22 pm

LadyGeek wrote:'Simba 15e', row 161: Sharpe Ratio - the entire row is blank. I don't understand why each fund in this row points to Column K, SCG / VISGX (Vanguard Small Capitalization Growth) for the ratio calculation. Is the intent to compare each fund to VISGX?

Well, you found an old bug which has been lingering for quite a while... The intent was to compare to T-bills, of course. I fixed the formula in the currently official version of the Simba spreadsheet (v15d), and a couple of other temporary versions of the spreadsheet. Good catch, LadyGeek, thank you.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Tue Oct 25, 2016 4:53 pm

siamond wrote:(I am stuck on MSCI Emerging, as the index was created in 1988, but somehow the MSCI Web site only provides returns since 2000 for the Net Series while the Gross series does start in 1988, and I can't find more on Morningstar - arumph. The 1988-1994 numbers in our Simba spreadsheet are gross numbers, coming from the DFA Matrix document, rounded up from the corresponding MSCI data series.)

Sigh. After a rather protracted exchange with the MSCI Support folks, I finally got this weird answer:
Kindly note there is no data available before December 29, 2000 for the MSCI Emerging Markets Index in Net variant. Data can be created as back tested, but it would require a subscription.
Seems to me that they are trying to make me pay for what is essentially a bug on their Web site... Yeah, well, maybe not. :shock:

I compared the EM net and gross series for the known returns (2000-2015), and there is a delta of roughly 0.3%. I also compared to the actual VEIEX returns, and there is no question that the net series is a better match. Oh, and I did verify that Vanguard was using the Net numbers as the benchmark of reference when applicable (they recently switched to FTSE). So it seems to me that we have two choices:
1. we stay factual and stick to the gross series for 1988-1994
2. we try to get it 'right' and subtract 0.3% from the gross series to approximate a net data series

Thoughts?

PS. all details in my comparison spreadsheet, which I am beefing up with various findings as a I plow through the numbers:
https://drive.google.com/open?id=0B0svRQGBG_eaVDNyUXJaLTN3c3M

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Fri Oct 28, 2016 11:19 am

Hm. Not a lot of feedback on my pending questions (see past few posts, and also this thread about VTRIX). Some opinions would really very welcome as I am in the process of making multiple decisions (and I have a couple more I didn't even document yet)... Help! :!:

I need to put my scrubbing project on hold for a little while, so here is the current status for those of you who might be interested in details.
- the current *draft* for the new 16a version of the Simba spreadsheet can be found here.
- backup material (the Vanguard data I received, plus more stuff about indexes, NR vs GR returns, etc) can be found here (aka the comparison spreadsheet)
Last edited by siamond on Fri Oct 28, 2016 12:20 pm, edited 1 time in total.

User avatar
Tyler9000
Posts: 286
Joined: Fri Aug 21, 2015 11:57 am

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by Tyler9000 » Fri Oct 28, 2016 11:45 am

siamond wrote:
siamond wrote:(I am stuck on MSCI Emerging, as the index was created in 1988, but somehow the MSCI Web site only provides returns since 2000 for the Net Series while the Gross series does start in 1988, and I can't find more on Morningstar - arumph. The 1988-1994 numbers in our Simba spreadsheet are gross numbers, coming from the DFA Matrix document, rounded up from the corresponding MSCI data series.)


I compared the EM net and gross series for the known returns (2000-2015), and there is a delta of roughly 0.3%. I also compared to the actual VEIEX returns, and there is no question that the net series is a better match. Oh, and I did verify that Vanguard was using the Net numbers as the benchmark of reference when applicable (they recently switched to FTSE). So it seems to me that we have two choices:
1. we stay factual and stick to the gross series for 1988-1994
2. we try to get it 'right' and subtract 0.3% from the gross series to approximate a net data series


I'm having a tough time directly interpreting your spreadsheet comparison (there's a lot of data!).

Assuming the 0.3% delta is consistent, then it makes sense. But from your earlier post, I got the impression that net vs. gross isn't quite that consistent. In that case, I can see the argument for keeping it "factual" with the best data we have available.

There's also the matter of the older DFA EM data representing gross numbers, and they're sketchy enough as-is without also adding additional modifiers. So we're still likely to have a gross/net change in that data set either way.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Fri Oct 28, 2016 12:13 pm

Tyler9000 wrote:I'm having a tough time directly interpreting your spreadsheet comparison (there's a lot of data!).

Assuming the 0.3% delta is consistent, then it makes sense. But from your earlier post, I got the impression that net vs. gross isn't quite that consistent. In that case, I can see the argument for keeping it "factual" with the best data we have available.

There's also the matter of the older DFA EM data representing gross numbers, and they're sketchy enough as-is without also adding additional modifiers. So we're still likely to have a gross/net change in that data set either way.

Feel free to reach out by private message if you want to understand more details. I admittedly didn't try to make the comparison spreadsheet friendly (this isn't intended to become a sustained public document), I'm just trying to capture all the data points I gather. Mostly for myself to avoid re-doing the same research two years down the road!

I went through all the International series (except for the IFA part), and you're right, the NR vs GR delta is NOT consistent at all, and at times truly puzzling. Note that the MSCI Emerging Markets data series is the *only* one suffering from lack of NR history. And I checked, Vanguard always used those as benchmarks. So I'm pretty set on the idea of using NR data whenever possible. And yet I do understand your point. It does bug me to provide EM returns that are artificially 'rosy' though. Hm, that one is tough.
=> Other views?

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by longinvest » Sat Oct 29, 2016 2:07 pm

siamond wrote:=> Other views?

I am in favor of estimating net index returns for MSCI Emerging, but the fundamental question that remains (and that applies to all backtesting) is: how useful are the historical returns of asset classes in which it was nearly impossible for retail investors to invest at the time?

Examples: gold returns in years it was illegal to own gold, S&P 500 returns before Jack Bogle created VFINX, Barclays Aggregate Bond Index before the creation of VBMFX, TIPS returns before the creation of TIPS in 1997 (or the introduction of TIPS funds and ETFs), etc.

I guess that my question does not belong to this technical thread, though. :wink:
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

User avatar
Kevin M
Posts: 8840
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by Kevin M » Sat Oct 29, 2016 4:13 pm

longinvest wrote:<snip> but the fundamental question that remains (and that applies to all backtesting) is: how useful are the historical returns of asset classes in which it was nearly impossible for retail investors to invest at the time?

It's an excellent question, and one worth discussing.

On the plus side, I think the idea is that the historical data has merit because we can now easily invest in these things, so it's worth looking at the "what if we could have invested in them back then" scenario. Or maybe it's: if future economic conditions are similar to economic conditions at various times in the past, then the past historical returns should contribute to the sample population that we can use to develop etimates of future returns. Of course nothing is exactly the same as in the past, but there are fundamentals like interest rates, inflation, valuations, growth rates, etc. that can be compared.

On the minus side, if something was harder and/or more expensive to trade in the past, then you might expect an illiquidity premium in past returns. I believe this argument has been used to explain, for example, the historical small-cap value premium, especially for the smallest decile.

There's also the argument that the world is generally a safer place than it was in the past, and risk premiums decline with increasing safety, so we should expect a smaller equity risk premium, for example, going forward.

Kevin
||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Tyler9000
Posts: 286
Joined: Fri Aug 21, 2015 11:57 am

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by Tyler9000 » Sat Oct 29, 2016 5:13 pm

From my perspective, index funds don't make markets -- they simply track them. So reconstructing the performance of an index prior to its inception using the same methods of the index is a perfectly reasonable thing to do.

Note that indices themselves also do this. For example, the Russell 2000 index was founded in 1984. But if you search online for Russell 2000 data, they provide numbers since 1979. Like any company selling a new product, they reconstructed the previous 5 years of data to demonstrate how the index worked. The most important thing from a backtesting perspective is the methodology of the index rather than the fund itself.

Whether the performance of an index changed over time due to a myriad of factors is a different matter. Uncovering those trends is why we need the data to begin with. ;)

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Sat Oct 29, 2016 5:44 pm

Ok, I think I'll stick with a 'Net' estimate for Emerging Markets. Doing otherwise would be inconsistent and misleading, I'm afraid. Yes, this implies a slightly clunky computation for 1988-1994, but be it. Hopefully, at some point, MSCI will fix their data gap. I'll take a note to monitor that from time to time.

As to the previous years (1972-1987), we currently use the IFA EM index, which is defined as:
* January 1970 – December 1987: 50% IFA Int'l Value and 50% IFA Int'l Small Cap

Well, instead of reusing those IFA EM numbers, I'd rather simply follow the corresponding core logic and compute 50% of our Int'l Value series and 50% of our Int'l Small Cap series. This core logic sounds rather dubious, but we don't have anything better, and at least the Int'l Value data series is more solid, cf. MSCI EAFE Value (net) index for 1975+. Anyhoo, I didn't dive enough in the IFA numbers yet (I know they've been updated), but this is my current line of thinking, trying to make the best out of lemons... :|

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Mon Oct 31, 2016 2:14 pm

Moving on, let me share another tentative change... Let's discuss International Value. Here are our data sources in the official v15d Simba spreadsheet:
- IFA website (IFA IV, added back expenses): 1972-1974
- MSCI EAFE Value 1975-1996 ==> gross returns
- Vanguard Intl Value (VTRIX) 1997+

About International Value, as discussed in this thread, VTRIX has a complicated history, it wasn't always a value fund (it pivoted in 1997/98), plus it remains an active fund to this day, with no less than three independent active managers! We also have solid history for the MSCI EAFE Value index (from 1975 till now, net and gross returns). VTRIX can be compared to such index, but it doesn't track it (being an active fund).

Now stlutz and myself had the same thought. There is a passive fund for Int'l Value, namely iShares EFV. Which tracks the MSCI EAFE Value index, and seems to do a good job doing it. EFV was launched in 2005, so we have annual returns since 2006. Therefore, we could combine the MSCI EAFE Value (net) series with EFV, and this would be a better and more consistent match for the International Value asset class. A bit of a departure from the Simba spreadsheet being centered on Vanguard funds, but this wouldn't be the only exception.

Moving from EAFE(gross)+VTRIX to EAFE(net)+VTRIX would be a significant change (CAGR 1975+ drops by 0.7%), and we minimally should do that. Moving to EAFE(net)+EFV goes down another notch (0.3% more), but we're staying in the same order of magnitude, and this seems an approach more consistent with passive investing principles. Interested folks can compare the exact numbers in the comparison spreadsheet I posted before.

If we were to do that, I would also provide the entire VTRIX history (starting from 1984) in the new Data_Misc tab, for those interested. So if somebody is truly attached to the old way of combining numbers, a simple copy & paste will do the trick. Feedback welcome.
Last edited by siamond on Mon Oct 31, 2016 3:11 pm, edited 1 time in total.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Mon Oct 31, 2016 11:30 pm

Now, as to the use of some of the IFA data series for some of the early years... Some IFA data series were themselves derived from other sources (e.g. MSCI, DFA Matrix), either fairly directly, or doing some crude math to fill some historical gaps. The MSCI original sources were gross series. So I elected to come back to the original sources, including MSCI net numbers.

IFA EM (Emerging): 1972-1987
=> IFA does crude math here, e.g. 50% IFA Int'l Value Index and 50% IFA Int'l Small. Not having a better idea for methodology (MSCI didn't track Emerging until 1988), I kept the same formula, while applying directly it to our Int'l Value and Int'l Small series.

IFA IV (Int'l Value): 1972-1974
=> here this is simple, IFA uses the MSCI EAFE (gross) data series, I simply switched to MSCI EAFE (net). Note that MSCI EAFE Value starts in 1975.

IFA (Int'l Small): 1972-1996
=> here IFA uses the DFA International Small Cap Index, so I came back to the corresponding DFA Matrix data. There is no corresponding MSCI data series until the end of the 90s.

So overall, the only IFA dependency which remains is the crude methodology used to approximate Emerging returns between 1972-1987. Which is really quite dubious, so I added a **CRUDE DATA** warning in the corresponding Data_Source description of the Emerging Market data series.

PS. I am thinking to introduce a new IFA dependency though. We will have 1970-71 data for pretty much every data series that used to start in 1972 (as this old post from Trev_H had already documented). The main gap is REITs. IFA approximates pre-1972 REITs to 50% Small Caps, 50% Small Caps Value. Why not...

stlutz
Posts: 3971
Joined: Fri Jan 02, 2009 1:08 am

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by stlutz » Tue Nov 01, 2016 12:45 am

IMO I think you're probably going too far in trying to generate numbers. Longinvest's question is valid--does it make sense to backtest against something that you wouldn't have even thought to invest in back at the time? And I'm not talking about the S&P 500 vs. an actively-managed largecap fund. If you can't find REIT data, it's probably because it wasn't investible to individuals back then--in index or actively-managed form.

In the cases where you are saying the data is questionable (i.e. the IFA data), I would just fill it in the with the "default" choice and then leave notes as to what you did. For example, before emerging markets funds were around, you would have just invested it all in an EAFE fund. Before REITS, you probably would have just used additional normal stocks, so just fill those gaps in with TSM.

My $.02.

AlohaJoe
Posts: 2431
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by AlohaJoe » Tue Nov 01, 2016 2:13 am

stlutz wrote:IMO I think you're probably going too far in trying to generate numbers. Longinvest's question is valid--does it make sense to backtest against something that you wouldn't have even thought to invest in back at the time?


For better or worse, the simba spreadsheet is out there and in the wild. I think it is better for the data to be coherent and correct than otherwise.

There were no real investable indexes before the late-1980s (December 1986 for bonds, June 1990 for Europe & Pacific). There was no such thing as defined maturity bond index funds before 1994 (so no backtesting against "intermediate" this or "short" that).

Though it pays to be humble in the face of those massive changes, throwing out the previous 100+ years of market data seems like an over-correction to that uncertainty. And I don't see a good reason to allow it for some asset classes (say, the "S&P 500" and "intermediate government bonds") but not others (international value and EM).

longinvest's own spreadsheet performs backtesting on things that no human could have ever purchased (there was no "index fund" in 1966 after all and no actual person used bonds in the 1930s the way Shiller's data does) so it seems he's already decided it is acceptable to backtest something people couldn't, and didn't, invest it. I think that's the right choice.

Some people spend a lot of time chasing up obscure details. Think of the original Cowles Commission poring over historical stock data in the US or James Murray's herculean effort in compiling the Oxford English Dictionary. I'm glad those people did it; not all of it turns out to be worthwhile but who am I say which efforts are futile, which merely quixotic, and which will pay unanticipated dividends? It isn't as if every minute of my day is decided to "worthwhile" pursuits :sharebeer

AlohaJoe
Posts: 2431
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by AlohaJoe » Tue Nov 01, 2016 2:20 am

stlutz wrote:For example, before emerging markets funds were around, you would have just invested it all in an EAFE fund. Before REITS, you probably would have just used additional normal stocks, so just fill those gaps in with TSM.


There was no TSM fund before 1992, so that doesn't help very much if we're trying to avoid synthetic, uninvestable things. The same applies to EAFE funds, the first of which didn't exist until 2001 when iShares created EFA.

Most of the things we invest in today are extraordinarily recent, which is probably part of the urge to try to understand how they may have performed in very different circumstances.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Tue Nov 01, 2016 9:49 am

stlutz wrote:In the cases where you are saying the data is questionable (i.e. the IFA data), I would just fill it in the with the "default" choice and then leave notes as to what you did. For example, before emerging markets funds were around, you would have just invested it all in an EAFE fund. Before REITS, you probably would have just used additional normal stocks, so just fill those gaps in with TSM.

As long as we have a decent index, personally, I am fairly comfortable using those historical numbers. This isn't perfect, but does remain informative. Plus, being quite blunt, the Simba spreadsheet would be completely gutted and pretty much useless if we were to only use numbers coming from real funds. So I'm trying to stick to its core philosophy.

But I do agree that those IFA 50/50 tricks are more than dubious. For REITs, it's just a couple of years, so I don't think this really matters, and I have sympathy for your 'just use TSM' suggestion. For EM, this is trickier as we're speaking of a much longer period of time (1970-1987). Also, I checked the 50/50 IFA EM formula against known numbers (1988+) last night, and the match is absolutely horrendous (while there might be a small kernel of truth to the REIT formula).

I also had the opportunity to compare the IFA numbers to the GFD EM numbers (which we can't quote), and the 50/50 rule 1970-87 CAGR seems way too rosy. On the other hand, backtracking to EAFE (Developed) would seem quite at odds with what appears to have happened in the 70s and 80s, cf. *the* primary reference, the DMS/Credit-Suisse study from 2014.

Or... we could just call a cat a cat, and acknowledge that EM is an asset class that should be part of the 1985+ data set instead of the 1972+ (or 1970+) data set. And then using EAFE for the 1985-87 gap would be fine.

Image

stlutz
Posts: 3971
Joined: Fri Jan 02, 2009 1:08 am

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by stlutz » Tue Nov 01, 2016 9:55 pm

Just to make sure I was clear above--I'm not arguing that anything that could not have been invested in index form should be excluded from the spreadsheet. If one couldn't buy any kind of mutual fund--active or passive--that targeted the asset class, then I do raise a serious question.

The oldest "emerging markets" mutual fund launched in 1989. So, how exactly how could any of us invested in the asset class before it even existed?

A google search turned up this interesting article about such funds from 1998: http://www.nytimes.com/1998/01/11/busin ... tcome.html

Not too many years ago, such countries were labeled ''undeveloped countries.'' Then, in a bit of verbal political correctness, they became ''less developed countries,'' or L.D.C.'s. That name was often used in discussions of bad bank loans, which did not help their image.

But starting little more than a decade ago, people began to speak of ''emerging markets.'' There were two nice aspects to that. The emerging part implied progress, and the markets part assumed that countries were following the capitalist path. That assumption could become widespread only with the fall of Communism in most of the world, and with the general view that even Communist countries would adopt market economies.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Thu Nov 03, 2016 12:21 pm

Ok, I made a final call on International Value based on feedback received privately and publicly. Data Sources will be the following:

MSCI EAFE NR USD 1970-1974
MSCI EAFE Value NR USD 1975-2005
iShares MSCI EAFE Value (EFV) 2006+

This drops the 1972-2015 CAGR by nearly a point, but such is the price of seeking better data sources... :wink:

User avatar
Tyler9000
Posts: 286
Joined: Fri Aug 21, 2015 11:57 am

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by Tyler9000 » Thu Nov 03, 2016 12:49 pm

Makes sense to me! I always prefer the most accurate data possible.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Fri Nov 04, 2016 5:51 pm

Let's discuss T-Bills now. Which we primarily use as Risk Free Benchmark for Sharpe ratios. In Simba 15d, the data sources are:
- prior to 1972, coarse assumption that 1yr interest rate (from Prof. Shiller, then FRED) provides a decent estimate for T-Bills
- T-Bills 1972-1983
- Vanguard Treasury Money Market Fund (VMPXX) 1984+
- VG Treasury (VMPXX) merged with Admiral Treasury (VUSXX) in Aug 2009

The 1972-83 description wasn't quite specific, but I finally figured out that this came (via indirect means) from SBBI/Ibbotson T-Bills 30-days data series. Ahem.

At the time of the merge between VMPXX and VUSXX, the respective ERs were 0.28% and 0.15% (and VUSXX is now 0.09%), according to the Vanguard press release (hence a discontinuity in our Simba series). With the recent historically low yields, VMPXX was clearly not sustainable any more. As to VUSXX, its first annual return was in 1993. Note that the average duration for VUSXX bills is 71 days, hence between 2 and 3 months. Oh, and VMPXX is kind of lost in history since the fund disappeared (Morningstar doesn't have a trace any more).

Ok, this all seems pretty clunky and inconsistent. Discussing with Kevin_M, we came up with the idea to use the FRED T-Bill 3-month data series, which provides corresponding interest rates on a monthly basis starting from 1934. Which is also what Prof. Damodaran uses for his own T-Bill data series (see here). Prof. Damodaran approximates T-Bills annual return to the arithmetic average of the Jan, Apr, Jul, Oct rates though. It would seem more accurate to do a geometric average. So the plan is to streamline:
- prior to 1934, keep the 1yr interest rate coarse estimate (Prof. Shiller)
- 1934-1992: T-Bills returns as geometric average of Jan, Apr, Jul, Oct rates provided by FRED (TB3MS)
- 1993+: use VUSXX (and use its latest ER to adjust the synthetic returns pre-1993)

As usual, feedback welcome.

stlutz
Posts: 3971
Joined: Fri Jan 02, 2009 1:08 am

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by stlutz » Fri Nov 04, 2016 7:48 pm

As I was reading your post I was thinking to myself, "Just use the 3 month T-bill rate.". :)

AlohaJoe
Posts: 2431
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by AlohaJoe » Fri Nov 04, 2016 8:30 pm

siamond wrote:Let's discuss T-Bills now.


If this is the 52-week T-Bill then shouldn't we be using FRED's 1-year data series[1] instead of trying our own interpolation?

At the very least, the 1-year series seems like a benchmark for the averaged 3-month series.

It would also be interesting to ask Damodaran why he used the arithmetic average, just to make sure there isn't some deeper reason that isn't apparent (at least not apparent to me!)

[1]: https://fred.stlouisfed.org/series/DTB1YR

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by LadyGeek » Fri Nov 04, 2016 8:49 pm

AlohaJoe wrote:It would also be interesting to ask Damodaran why he used the arithmetic average, just to make sure there isn't some deeper reason that isn't apparent (at least not apparent to me!)

If simple interest is used, an arithmetic average is correct. If compounding interest is used, geometric averages are correct (due to the exponential function).

Bonds use simple interest to determine accrued interest between coupon periods: Bond pricing (Price between coupon periods)

I'm going by textbook theory, which may be different than how the returns are used in this spreadsheet. I'm also not an expert on bonds, but I wanted to explain where one would use arithmetic vs. geometric return calculations.
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: 3333
Joined: Mon May 28, 2012 5:50 am

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Fri Nov 04, 2016 9:50 pm

stlutz wrote:As I was reading your post I was thinking to myself, "Just use the 3 month T-bill rate.". :)

Are you suggesting to completely skip VUSXX? Not too sure about that... For me, a risk-free benchmark should map to something investable, at least in the years where we have such direct history. Or did you mean something else?

AlohaJoe wrote:At the very least, the 1-year series seems like a benchmark for the averaged 3-month series.

If we keep VUSXX as the fund of reference, then we're speaking of 1-3 months short-term bills, not 1-yr bills. From the VUSXX description:
The average maturity typically ranges from 30–60 days, and the fund maintains a dollar-weighted average maturity of 60 days or less, and a dollar-weighted average life of 120 days or less.

LadyGeek wrote:If simple interest is used, an arithmetic average is correct. If compounding interest is used, geometric averages are correct (due to the exponential function).

I am really not savvy about bonds & bills, but if I were to use 3-month bills as a risk-free investment, I would buy such bills in January, get the principal back plus interest 3 months later, buy new bills on this basis, get principal/interest 3 months later, etc. Therefore I would use a geometric average to compute my annual return. Or am I missing something?

AlohaJoe wrote:It would also be interesting to ask Damodaran why he used the arithmetic average, just to make sure there isn't some deeper reason that isn't apparent (at least not apparent to me!)

Good idea. Will do.

User avatar
Kevin M
Posts: 8840
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by Kevin M » Sat Nov 05, 2016 11:54 am

siamond wrote:
stlutz wrote:As I was reading your post I was thinking to myself, "Just use the 3 month T-bill rate.". :)

Are you suggesting to completely skip VUSXX? Not too sure about that... For me, a risk-free benchmark should map to something investable, at least in the years where we have such direct history. Or did you mean something else?

Treasury bills are investable, and as a matter of fact Bill Bernstein recommends buying T bills directly rather than using a fund, since the ER is 0 and there is no credit risk to diversify away. Well, Bernstein actually recommends buying all Treasuries directly, not just bills. But I think the argument for just using something like the 3-month T bill to represent T bills is better than for using Treasuries for intermediate-term or long-term bonds, since the short term nature makes it likely that the returns will be quite close to a Treasury money market fund. Have you checked this?

Having said that, I think it's fine to use the fund where data is available if you think it makes more sense, and most Bogleheads probably use a fund rather than following Bernstein's advice. I think using data from FRED makes sense for when fund data is not available and FRED data is. FRED is a reliable source and used by Damodaran, Shiller, et al.

siamond wrote:
AlohaJoe wrote:At the very least, the 1-year series seems like a benchmark for the averaged 3-month series.

If we keep VUSXX as the fund of reference, then we're speaking of 1-3 months short-term bills, not 1-yr bills. From the VUSXX description:
The average maturity typically ranges from 30–60 days, and the fund maintains a dollar-weighted average maturity of 60 days or less, and a dollar-weighted average life of 120 days or less.

Either 3-month or 1-month T bills would be more consistent for what typically seems to be used for the risk-free rate by academics. Damodaran uses 3-month and Fama-French use 1-month. The idea for the RF rate, at least the nominal rate, is that there is little-to-no term risk in addition to no credit risk. This argues for using the 1-month rate, but there is much more history for the 3-month rate (1-month only goes back to 2001). On the other hand, I guess you could use RF from French data as the 1-month T bill.

If you sort FRED T Bills series by popularity, the 3-month, secondary market rate is the most popular, probably for exactly the reasons we're discussing here.

siamond wrote:
LadyGeek wrote:If simple interest is used, an arithmetic average is correct. If compounding interest is used, geometric averages are correct (due to the exponential function).

I am really not savvy about bonds & bills, but if I were to use 3-month bills as a risk-free investment, I would buy such bills in January, get the principal back plus interest 3 months later, buy new bills on this basis, get principal/interest 3 months later, etc. Therefore I would use a geometric average to compute my annual return. Or am I missing something?

My thinking is the same as siamond's. Each bill pays its principal and interest at the end of the term, at which point you need to do something with it, which in the case we're discussing here would be to reinvest it in a new bill of the same term. Since you would be reinvesting the interest as well as the prinicpal, it makes sense to compound by the term of the bill (so quarterly for the 3-month bill, monthly for a 1-month bill, annually for a 1-year bill).

siamond wrote:
AlohaJoe wrote:It would also be interesting to ask Damodaran why he used the arithmetic average, just to make sure there isn't some deeper reason that isn't apparent (at least not apparent to me!)

Good idea. Will do.

Couldn't hurt. While you're at it, you might ask him where he got his t-bill rates prior to 1934, since he only mentions FRED as his data source for t bills, but FRED 3-month secondary rates only go back to 1934.

Kevin
||.......|| Suggested format for Asking Portfolio Questions (edit original post)

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by LadyGeek » Sat Nov 05, 2016 1:08 pm

Kevin M wrote:...
siamond wrote:
AlohaJoe wrote:It would also be interesting to ask Damodaran why he used the arithmetic average, just to make sure there isn't some deeper reason that isn't apparent (at least not apparent to me!)

Good idea. Will do.

Couldn't hurt. While you're at it, you might ask him where he got his t-bill rates prior to 1934, since he only mentions FRED as his data source for t bills, but FRED 3-month secondary rates only go back to 1934.

Kevin

The wiki has some some background info: Aswath Damodaran

His website: Data

The spreadsheet is downloaded from here: Useful Data Sets --> Historical Returns on Stocks, Bonds and Bills - United States: (html), (Spreadsheet)
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.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by longinvest » Sat Nov 05, 2016 2:23 pm

LadyGeek wrote:If simple interest is used, an arithmetic average is correct. If compounding interest is used, geometric averages are correct (due to the exponential function).

Bonds use simple interest to determine accrued interest between coupon periods: Bond pricing (Price between coupon periods)

I'm going by textbook theory, which may be different than how the returns are used in this spreadsheet. I'm also not an expert on bonds, but I wanted to explain where one would use arithmetic vs. geometric return calculations.

Personally, I would look at the data source to try identify what kind of numbers FRED gives us.

On https://fred.stlouisfed.org/series/TB3MS, there's a link to http://www.federalreserve.gov/releases/h15/ which says:
Treasury bills (secondary market) 3 4
...
6-month ...
...
3. Annualized using a 360-day year or bank interest.


At first sight, I don't see an explanation telling us if FRED is making any adjustment to the H.15 series or not. If not, and if I was to translate these numbers into annual returns, I would try to estimate cumulative total returns over a 365.25 day period, but that's just me. :wink:
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VAB/ZRR

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by LadyGeek » Sat Nov 05, 2016 3:44 pm

Perhaps this has something to do with the definition of Bond Equivalent Yield (BEY)?

The wiki has the formula: Treasury bill

Now, look at the US Treasury: Daily Treasury Bill Rates Data, fine print:

The Bank Discount rate is the rate at which a Bill is quoted in the secondary market and is based on the par value, amount of the discount and a 360-day year. The Coupon Equivalent, also called the Bond Equivalent, or the Investment Yield, is the bill's yield based on the purchase price, discount, and a 365- or 366-day year. The Coupon Equivalent can be used to compare the yield on a discount bill to the yield on a nominal coupon bond that pays semiannual interest.


longinvest wrote:...
3. Annualized using a 360-day year or bank interest.

The above comments frame this in a different context. Long story short, the calculations are simple (not exponential) math and is why an arithmetic average is used.*

*This is my guess, but it seems right to me.
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
Kevin M
Posts: 8840
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by Kevin M » Sat Nov 05, 2016 4:14 pm

LadyGeek wrote:Long story short, the calculations are simple (not exponential) math and is why an arithmetic average is used.*

*This is my guess, but it seems right to me.

I don't think this addresses what we're talking about. This applies to the way the annualized yield for a single bill is calculated. Siamond is not proposing use geometric mean for the calculation of yield for a single bill; I believe he would simply divide the annualized yield of the 3-month T bill by 4 to get the return for three months (so no assumption of compounding here). The compounding comes in when you use the proceeds of the matured bill (say after 3 months) to buy the next bill.

I don't think there's enough precision in what we're talking about here that it matters whether you use 360 or 365 days in the calculations. Similarly, it's actually a 13-week bill, which is 91 days not actually three months, so 364 days to roll four 3-month bills in about a year.

My 2 cents.

Kevin
||.......|| Suggested format for Asking Portfolio Questions (edit original post)

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Sat Nov 05, 2016 4:29 pm

Kevin M wrote:I believe he would simply divide the annualized yield of the 3-month T bill by 4 to get the return for three months (so no assumption of compounding here).

Yes, this is exactly what I did. I took the geometric average of the Jan interest rate divided by 4, the April interest rate divided by 4, etc.

Kevin M wrote:The compounding comes in when you use the proceeds of the matured bill (say after 3 months) to buy the next bill.

Yup.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by LadyGeek » Sat Nov 05, 2016 4:34 pm

Kevin M wrote:I don't think this addresses what we're talking about.

Thanks, that helps. I misunderstood where the compounding would be applied.

(And noting siamond's agreement just after Kevin M's post.)
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
Kevin M
Posts: 8840
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by Kevin M » Sat Nov 05, 2016 4:37 pm

Since we're talking about T bills as the riskfree rate, for example to calculate Sharpe ratio, it's worth remembering that the definition of risk free is that there is no uncertainty in return for a specified holding period. So if we're dealing in annual returns, and calculating Sharpe ratios based on annual returns, the holding period is one year, and the riskfree rate in nominal terms is a 1-year T bill. For monthly returns the riskfree rate in nominal terms is a 1-month T bill, which might be why Fama/French use the 1-month T bill as RF in their monthly return series.

We're still faced with the issue that the longest data series from FRED is for the 3-month T bill. There are 1-year T bill rates back to 1959 for 1-year, and back to 1934 for 3-month.

Kevin
||.......|| Suggested format for Asking Portfolio Questions (edit original post)

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Sat Nov 05, 2016 4:40 pm

Kevin M wrote:
siamond wrote:
stlutz wrote:As I was reading your post I was thinking to myself, "Just use the 3 month T-bill rate.". :)

Are you suggesting to completely skip VUSXX? Not too sure about that... For me, a risk-free benchmark should map to something investable, at least in the years where we have such direct history. Or did you mean something else?

Treasury bills are investable, and as a matter of fact Bill Bernstein recommends buying T bills directly rather than using a fund, since the ER is 0 and there is no credit risk to diversify away. Well, Bernstein actually recommends buying all Treasuries directly, not just bills. But I think the argument for just using something like the 3-month T bill to represent T bills is better than for using Treasuries for intermediate-term or long-term bonds, since the short term nature makes it likely that the returns will be quite close to a Treasury money market fund. Have you checked this?

Having said that, I think it's fine to use the fund where data is available if you think it makes more sense, and most Bogleheads probably use a fund rather than following Bernstein's advice. I think using data from FRED makes sense for when fund data is not available and FRED data is. FRED is a reliable source and used by Damodaran, Shiller, et al.

Thanks, Kevin, I keep forgetting that one can directly buy bills and bonds... :oops:

Still, this isn't possible in every type of investment vehicle (e.g. 401k). Also I'd rather stay consistent with similar data series we have (e.g. long-term treasuries, intermediate-term treasuries, etc) where the same consideration would apply. The T-Bill series is used for Sharpe ratios computations, but it is ALSO a regular asset class on its own right, that could be part of a portfolio's AA. So I'd rather stick with the use of a fund in all cases.

PS. as a side note, I don't believe that Vanguard has a money market fund holding 1-year t-bills?

User avatar
Kevin M
Posts: 8840
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by Kevin M » Sat Nov 05, 2016 5:24 pm

siamond wrote:Thanks, Kevin, I keep forgetting that one can directly buy bills and bonds... :oops:

Still, this isn't possible in every type of investment vehicle (e.g. 401k).

No, but per my PM, a Treasury MM fund also may not be availble in 401k/403b plans, and certainly few plans have the VG Treasury fund.

siamond wrote:Also I'd rather stay consistent with similar data series we have (e.g. long-term treasuries, intermediate-term treasuries, etc) where the same consideration would apply. The T-Bill series is used for Sharpe ratios computations, but it is ALSO a regular asset class on its own right, that could be part of a portfolio's AA. So I'd rather stick with the use of a fund in all cases.

Seems reasonable.

siamond wrote:PS. as a side note, I don't believe that Vanguard has a money market fund holding 1-year t-bills?

No, it looks like max maturity for bills in the VG fund is a little less than five months, and as you said, average maturity is closer to two months.

Kevin
||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Kevin M
Posts: 8840
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by Kevin M » Sat Nov 05, 2016 8:32 pm

I was just comparing 1-year to 3-month Treasuries where there is overlap, which is 1960 - 2016 with a gap from 9/2001 to 5/2008 when there were no 1-year Treasuries. While at it I compared arithmetic mean to geometric mean for quarterly 3-month T bills over one-year periods, and the average difference is only 2 basis points with a standard deviation of 4 basis points. Bottom line is that it doesn't make enough difference to matter.

Back to 1-year vs 3-month, I was wondering if these were close enough that it doesn't matter. Looking at a chart of both it kind of looks like it, but the average of the differences (using January values each year) is about 10 basis points and standard deviation is about 50 basis points. The difference between January 1-year bills and average of Jan, Apr, Jul, Oct 3-month bills is 13 basis point with SD of 62 basis points, so even worse. These differences seem like too much to ignore. Just FYI.

Kevin
||.......|| Suggested format for Asking Portfolio Questions (edit original post)

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Sat Nov 05, 2016 11:19 pm

Kevin M wrote:While at it I compared arithmetic mean to geometric mean for quarterly 3-month T bills over one-year periods, and the average difference is only 2 basis points with a standard deviation of 4 basis points. Bottom line is that it doesn't make enough difference to matter.

Agreed on the overall trajectory, but in the years of high inflation (e.g. early 80s), the difference is getting more significant, and a tad harder to ignore.

Anyhoo, to the risk of splitting hairs, we should just try to "do the right thing" here. I am no longer sure if the right thing to do is to use the 3-months rates, or the one-year rates... On one hand, the 3-months rates seem closer to VUSXX. On the other hand, your reasoning that an annual risk-free benchmark should be equated to a one-year T-Bill is intriguing... :?

UPDATE: barring some new inputs (e.g. from Prof. Damodaran), I am going to stick with the simple approach suggested at the end of this post. Seems good enough.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Tue Nov 08, 2016 1:02 pm

Now as to commodities... This one is the most puzzling entry in the 2015 Simba spreadsheet. Current data sources are:
1) Collaterallized Chase Index 1972-1990
2) DJ-AIJ plus T-Bills 1991-1996
3) DJ-AIG plus Yahoo's IPS 'category' returns 1997-2001 (prior to VIPSX)
4) DJ-AIJ plus VIPSX - 2001-2002
5) Pimco PCRIX 2003+

This refers to an Excel model (http://gnobility.com/Syn_Comm/CCF_1972-2007_san.xls) which has been lost in history... See old post.

I assume that DJ-AIJ is a typo, and line items 2 to 4 actually refer to the DJ-AIG commodity index. Wikipedia documents that it actually got renamed a couple of times and is now known as the Bloomberg Commodity Index (Morningstar ticker: XIUSA04GUL, 1991+ returns). DJ-AIG is a bit of a strange index, the weightings for each commodity are based on market liquidity, and subject to caps.

Browsing a classic book from Roger Gibson, he's somewhat dismissive about DJ-AIG and clearly prefers the more venerable S&P GSCI (Morningstar ticker: XIUSA0001Q). The author has been a big advocate for decades of including commodities in one's portfolio. And we have S&P GSCI historical returns since 1970, which is very convenient. Market weightings are based on market production.

As far as I understand, the Excel model was trying to emulate the fact that PCRIX (which is a Pimco fund) uses TIPS as a collateral reserve for active management of commodity securities. More information can be found in threads like Rethinking commodities. Note that the Total Return flavor of indexes like S&P GSCI or DJ-AIG does measure the returns of collateralized commodity futures (as explained here).

So I would suggest to simplify the whole thing and do the following (which would lead to a fairly similar 1972-2015 CAGR as before):
1) S&P GSCI TR USD 1970-2002
2) Pimco PCRIX 2003+


We could also replace PCRIX by iShares GSG, which uses S&P GSCI as its benchmark, but GSG is fairly new (2006) and a bit of a strange ETF (check the factsheet). Feedback welcome.

EDIT: see more discussion in this thread.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Mon Nov 14, 2016 3:06 pm

siamond wrote:
Kevin M wrote:While at it I compared arithmetic mean to geometric mean for quarterly 3-month T bills over one-year periods, and the average difference is only 2 basis points with a standard deviation of 4 basis points. Bottom line is that it doesn't make enough difference to matter.

Agreed on the overall trajectory, but in the years of high inflation (e.g. early 80s), the difference is getting more significant, and a tad harder to ignore.

Anyhoo, to the risk of splitting hairs, we should just try to "do the right thing" here. I am no longer sure if the right thing to do is to use the 3-months rates, or the one-year rates... On one hand, the 3-months rates seem closer to VUSXX. On the other hand, your reasoning that an annual risk-free benchmark should be equated to a one-year T-Bill is intriguing... :?

UPDATE: barring some new inputs (e.g. from Prof. Damodaran), I am going to stick with the simple approach suggested at the end of this post. Seems good enough.

After nagging Prof. Damodaran a second time, I did catch his attention, and he was kind enough to give me this answer:

First, it depends on how FRED annualizes the T.Bill returns in the first place (since the 3-month T.Bill returns are reported in annualized forms). If they multiply by four, your point is well taken and you should compound. If not, you should not. FRED is very cagey about this. Second, it the rate is not compounded, you should computing the compounded effect, but the effect on the equity risk premium (my primary output from these tables) will be in the second decimal point and not significant. But you are right! I should be more precise and I will try to see if the annualized rates already include compounding or not.

I don't fully agree about the significance of the issue, cf. late 70s as previously discussed. Anyhoo, I guess we'll stick to our geometric math for now, and stay tuned for further updates from the good professor. Very nice of him to answer in such open manner.

User avatar
Kevin M
Posts: 8840
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by Kevin M » Mon Nov 14, 2016 4:45 pm

siamond wrote:First, it depends on how FRED annualizes the T.Bill returns in the first place (since the 3-month T.Bill returns are reported in annualized forms). If they multiply by four, your point is well taken and you should compound. If not, you should not. FRED is very cagey about this. Second, it the rate is not compounded, you should computing the compounded effect, but the effect on the equity risk premium (my primary output from these tables) will be in the second decimal point and not significant. But you are right! I should be more precise and I will try to see if the annualized rates already include compounding or not.

This doesn't make sense to me. We get from FRED t bill rates, not t bill returns. For the series going back to 1934, the rates are monthly. For the series starting in 1954 you can get daily rates, and aggregate to either average or month-end monthly rates.

Typically when you select a longer time period than the finest granularity you get an average by default (which you can switch to end of period), so I would guess that the monthly rates for the 1934- series are monthly averages of daily rates, but I guess they didn't save the actual daily values. We can verify that the 1934- series is average of daily rates by adding the 1954 daily series, calculating a - b, and see that the line is horizontal at 0 for average daily but not for end of period daily.

Using average daily:

Image

Using end of period daily:

Image

Kevin
||.......|| Suggested format for Asking Portfolio Questions (edit original post)

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Mon Nov 14, 2016 5:38 pm

Kevin M wrote:This doesn't make sense to me. We get from FRED t bill rates, not t bill returns. For the series going back to 1934, the rates are monthly. For the series starting in 1954 you can get daily rates, and aggregate to either average or month-end monthly rates.

Yes, I would tend to agree that there is a bit of confusion. It could make sense to use a mathematical formula to go from annual returns to monthly returns, and one has to be careful with that, as Ladygeek pointed out. But one should go from historical daily/monthly rates to annual rates when annualizing, not the reverse way around. And I don't really care how FRED does it, as it seems to me that the historical monthly rate (or purchase date daily rate) is what it is, this is what applies when buying a 3-month t-bill.

Actually, thinking again to the topic made me realize that we're assuming coupon payment reinvestment when possible (i.e. when buying the next 3-month t-bill). While the rest of the Simba returns, at least the US ones, are expressed as Total-Returns (TR series), which include dividends end-of-the-month reinvestment (see here). So we're consistent. Prof. Damodaran Excel spreadsheet is *also* consistent, but the reverse way around, it does NOT assume any form of intra-year dividends reinvestment in his S&P 500 computation as a case in point, nor intra-year coupon reinvestment for bonds, etc. But in both cases, we really should do a better job of documenting what type of data series we're providing...

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Wed Nov 16, 2016 11:59 am

I swapped a couple more e-mails with Prof. Damodaran, and we basically agreed on what I stated in the previous post, his spreadsheet (t-bills as well as bonds and stocks) documents returns *without* intra-year reinvestment (e.g. coupon payments, dividends). While our Simba spreadsheet documents returns *with* intra-year reinvestment (e.g. TR series). Which explains his way of estimating t-bills (using arithmetic averages) and our way of estimating t-bills (using geometric averages). Those are just two distinct data series, with different goals.

I added a couple of lines of explanations to this effect in my working version of the spreadsheet, and this should close the t-bill topic for now.
Last edited by siamond on Wed Nov 16, 2016 12:14 pm, edited 1 time in total.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Wed Nov 16, 2016 12:12 pm

Back to commodity futures (aka CCFs), based on the discussion in this thread, I think we'll settle on a more 'passive' way of viewing things, and replace the Chase/DJ-AIG/PCRIX approach by the following:

1) S&P GSCI TR USD 1970-2006
2) iShares GSG 2007+ (passive ETF, tracking the S&P GSCI)


I did the update in my working version of the spreadsheet, this makes the overall CAGR drop significantly (be it), while still displaying the low correlation (slightly negative) between commodities and regular equities that one can expect.

As a side note and for archive, I stumbled upon this write-up, which provides extensive background and thought process about the previous way of doing things (Chase/DJ-AIG/PCRIX). Perfectly understandable, but still overly complicated, imho.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Wed Nov 16, 2016 4:02 pm

AlohaJoe wrote:I noticed today that AQR has a number of datasets that may be interesting to add in. From what I can tell, AQR is fine with including them in things like this spreadsheet so long as they are credited. (The full requirements on their website when you download something.)

- Momentum Indexes back to 1980: https://www.aqr.com/library/data-sets/m ... es-monthly

Fryxell wrote:In my Google backtesting spreadsheet I use MTUM and AMOMX for large momentum. I use DWAS and ASMOX for small momentum. The AQR funds only go back to 2010. [...] There is no Vanguard momentum fund.

Back to AQR and momentum. I wasn't too hot about including them, but I decided to give it a fair try in my working spreadsheet. I spliced the AQR indexes for large-cap and small-cap momentum with the actual funds (AMOMX and ASMOX), using the actual funds ERs for the entire time series (1980-2015). Then I compared to TSM (Total US), LCV (Large-Cap Value) and SCV (Small-Cap Value). Looks like a nice trajectory.

Image

A closer look at the telltale chart shows that the time period with the real-life returns isn't so convincing though. Let's focus on the 2005-2015 period (see below). Meh, doesn't look interesting anymore. Admittedly, this is a very short period of time, and other more established factors like SCV didn't make much of a difference either, but I don't know, I have some troubles to convince myself that those AQR data series are worth keeping in there. Note that the value factor appears to be cyclical, and maybe there are good reasons for that, but it is harder to believe that MoM is cyclical. Thoughts?

Image

PS. hm, those telltale charts would look cleaner if I were to start one year earlier (all lines aligned on the same starting point). Will do.

AlohaJoe
Posts: 2431
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by AlohaJoe » Thu Nov 17, 2016 12:21 am

siamond wrote:Back to AQR and momentum. I wasn't too hot about including them, but I decided to give it a fair try in my working spreadsheet. [...] I have some troubles to convince myself that those AQR data series are worth keeping in there. Note that the value factor appears to be cyclical, and maybe there are good reasons for that, but it is harder to believe that MoM is cyclical. Thoughts?


It is probably worth trying to think about "what are the criteria for adding something new", since there is an ongoing maintenance cost. Both in terms of the updates (which is small), trying to extend the series back further historically, and in arguing about whether the data series chosen is appropriate/realistic/whatever.

I think there is as much (or as little) value in having momentum as having commodities. Actually, I think there is more value in momentum, since most academics consider it on par with value and size. For instance, in a recent paper "Five concerns with the five-factor model" one of the concerns is that it leaves out momentum:

Because momentum is too pervasive and important to ignore, most studies do not only report 3-factor alphas, but also 4-factor alphas, based on the 3-factor model augmented with a momentum factor (WML, winner minus losers).

However, the momentum premium has turned out to be too “pervasive” (Fama and French, 2008, p. 1653) and strong to simply ignore, and is by now an established factor. The 4-factor model, i.e. the 3-factor model augmented with a momentum factor, is as popular in the asset pricing literature as the 3-factor model.


On the other hand, I am wary of including factors given the current factor hype. I am a moderate believer in factors but I'm suspicious of the fact that the entire world seems to have jumped on the bandwagon right now. Having talked myself through it, I'd leave momentum out this year but probably add it in next year unless the factor-craze totally implodes.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Thu Nov 17, 2016 1:18 am

AlohaJoe wrote:I think there is as much (or as little) value in having momentum as having commodities. Actually, I think there is more value in momentum, since most academics consider it on par with value and size. [...]
On the other hand, I am wary of including factors given the current factor hype. I am a moderate believer in factors but I'm suspicious of the fact that the entire world seems to have jumped on the bandwagon right now. Having talked myself through it, I'd leave momentum out this year but probably add it in next year unless the factor-craze totally implodes.

Commodities were already in the spreadsheet, and were very popular a few years ago (e.g. before 2008 disproved some of their vaunted properties!). So I'd rather keep them in there (hype comes and goes), but I see your point, momentum has much more... momentum nowadays!

I confess that I am a big skeptic about momentum. It seems to me that this is one of those things which may disappear once identified and over-hyped. Maybe I am biased by my intuition here. Additional maintenance cost is minimal, don't worry about that. I don't mind adding those in the next official release if there is more than one individual asking! :wink:

AlohaJoe
Posts: 2431
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by AlohaJoe » Thu Nov 17, 2016 1:29 am

siamond wrote:
AlohaJoe wrote:I think there is as much (or as little) value in having momentum as having commodities. Actually, I think there is more value in momentum, since most academics consider it on par with value and size. [...]
On the other hand, I am wary of including factors given the current factor hype. I am a moderate believer in factors but I'm suspicious of the fact that the entire world seems to have jumped on the bandwagon right now. Having talked myself through it, I'd leave momentum out this year but probably add it in next year unless the factor-craze totally implodes.

Commodities were already in the spreadsheet, and were very popular a few years ago (e.g. before 2008 disproved some of their vaunted properties!). So I'd rather keep them in there (hype comes and goes), but I see your point, momentum has much more... momentum nowadays!


Yeah, that's the scenario I'm thinking about: that the spreadsheet accumulates the detritus of all kinds of "once popular" things that no one uses any more. On the other hand, if there is little upkeep cost maybe it is worth being able to look back at various out-of-favor things. It seems unlikely that we'd add 5-10 things every year, so the scope creep is likely to be quite minimal.

I confess that I am a big skeptic about momentum. It seems to me that this is one of those things being arbitraged into nothingness.


Asness & AQR have a paper on the "myths of momentum": www.iinews.com/site/pdfs/JPM_40th_AQR.pdf that you might (or might not!) find persuasive.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Fri Nov 18, 2016 1:31 pm

AlohaJoe wrote:Asness & AQR have a paper on the "myths of momentum": http://www.iinews.com/site/pdfs/JPM_40th_AQR.pdf that you might (or might not!) find persuasive.

Thank you for sharing, this was a good read. Ok, so I'm prone to myth #7. And no, I didn't find the answer terribly persuasive, but nice try! :wink: This being said, you did convince me that I have to put my bias aside in the decision-making process for inclusion or not, fair point.

Another reason for my reluctance was to use a vehicle which seems more active than passive, and to have the fund provider (AQR) be the same as the index provider (AQR!). This being said, if we were to track iShares MTUM (as Fryxell suggested a while ago), this relies on the MSCI USA Momentum Index. Both the fund and the index were launched in 2013, which is very recent, but the index was backtracked to 1981 (Morningstar symbol F00000Q9HR). And amazingly, the MTUM expense ratio is very low, 0.15%. So I entered the numbers and gave it a quick try, starting in 1985. (SC-MOM and LC-MOM refer to the small-caps and large-caps AQR data series).

Image

Ok, I am starting to think that we have a possible candidate for an addition to the spreadsheet. I am a little bothered by the heavy backtracking though. Say the index were actually created in 1981. In all this time, wouldn't the index definition have changed with all the latest and greatest formulas to capture momentum? (in all fairness, the same happened to value, to a certain extent).

lack_ey
Posts: 5628
Joined: Wed Nov 19, 2014 11:55 pm

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by lack_ey » Fri Nov 18, 2016 11:26 pm

siamond wrote:Ok, I am starting to think that we have a possible candidate for an addition to the spreadsheet. I am a little bothered by the heavy backtracking though. Say the index were actually created in 1981. In all this time, wouldn't the index definition have changed with all the latest and greatest formulas to capture momentum? (in all fairness, the same happened to value, to a certain extent).


With respect to iShares MTUM using the MSCI index and AQR, I'd say that AQR's momentum definition is a bit more traditional, vanilla, etc. They're doing the equivalent of using straight-up P/B for value rather than what is generally recommended by people these days, to use a mix of multiple measures.

Docs:
https://www.aqr.com/~/media/files/fund- ... dology.pdf
https://www.msci.com/eqb/methodology/me ... dology.pdf

For example, using multiple X-month measures (MSCI does 12-month and 6-month) is a common improvement over just the single 12-month-except-the-last-one. I know AQR uses multiple measures for some of their other funds. MSCI also adjusts for volatility, which kind of makes sense, though I don't know off the top of my head the historical effect of that change.

AlohaJoe
Posts: 2431
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by AlohaJoe » Mon Nov 21, 2016 1:56 am

lack_ey wrote:With respect to iShares MTUM using the MSCI index and AQR, I'd say that AQR's momentum definition is a bit more traditional


This is certainly one of the downsides of including new things -- we have to decide which flavor to include :happy My vote would be to pick MTUM over AQR, mostly because the existing choices already have what I'll call a "non-purist Vanguard-slant". By that I mean: the spreadsheet doesn't include the "best" (or more pure) small or value factor funds; it includes Vanguard's offerings. I think MTUM's low-ER makes it more Vanguard-ish than AQR's offerings and more in keeping with the other entries in the spreadsheet. I think it more likely that Vanguard brings to market an MTUM competitor than an AQR competitor; if that ever happens I imagine it likely that the spreadsheet would start tracking the Vanguard fund.

AlohaJoe
Posts: 2431
Joined: Mon Nov 26, 2007 2:00 pm
Location: Saigon, Vietnam

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by AlohaJoe » Mon Nov 21, 2016 2:06 am

siamond wrote:And amazingly, the MTUM expense ratio is very low, 0.15%.


Several of ishares' factor offerings are surprisingly cheap: USMV (US low volatility), QUAL (US quality factor), MTUM, VLUE (US value), SIZE (US size), and EUSA (equal weighted) all have an ER of .15. The international variants are a bit more expensive but not a lot. Generally another .05 for global and .10 for emerging markets. So the US low-vol is .15, the global low-vol is .20, and the emerging markets low-vol is .25.

I can only assume these are priced as loss-leaders to try to capture some of the factor market, since the emerging markets dividend ETF has an ER that is double that.

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

Re: Spreadsheet for backtesting (includes TrevH's data)

Post by siamond » Mon Nov 21, 2016 10:35 am

AlohaJoe wrote:My vote would be to pick MTUM over AQR, mostly because the existing choices already have what I'll call a "non-purist Vanguard-slant". By that I mean: the spreadsheet doesn't include the "best" (or more pure) small or value factor funds; it includes Vanguard's offerings. I think MTUM's low-ER makes it more Vanguard-ish than AQR's offerings and more in keeping with the other entries in the spreadsheet.

Yup, agreed, same thinking here. Done, MTUM added. Thanks for the friendly 'push'.

Post Reply