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
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 Dec 23, 2016 7:22 pm

In LibreOffice Calc, none of the check boxes were "checked" in the plots, but they displayed the 5-years and 10-years rolling returns as you described. I then toggled the check boxes to have the 5-years and 10-years rolling returns enabled. Then, I saved the file again in Microsoft .xls format with this warning:

This document may contain formatting or content that cannot be saved in the currently selected file format “Microsoft Excel 2007/2010/2013 XML”.

Reopening the file resulted in the disappearance of all the chart axes and labels, the plots were useless. This is a compatibility problem.

Saving the file as LibreOffice Calc ".ods" (native) file format, then reopening the spreadsheet was OK. So, if you have LibreOffice Calc, first save the file as .ODS format and work from that.

The alternative is to develop in LibreOffice Calc and save it as Microsoft .xls format. We've had these same problems before, which is why longinvest is using LibreOffice Calc for his Variable percentage withdrawal spreadsheet. It won't be perfect, but the spreadsheet is manageable.

I'm not suggesting you switch to LibreOffice Calc, but just to be aware of compatibility problems.
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 Dec 23, 2016 8:19 pm

LadyGeek wrote:I'm not suggesting you switch to LibreOffice Calc, but just to be aware of compatibility problems.

Ok, got it. Yes, I'm going to stick with Excel XLSX, but I took a note of trying to track/reduce/eliminate those issues at some point. Thanks for the inputs.

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 Dec 23, 2016 9:20 pm

Kevin M wrote:Thanks siamond. Any quick update on the HUGE discrepancies in international stock returns?

Let me detail where the numbers are coming from in both cases, and you'll be able to judge by yourself which series you prefer...

Simba data sources:
- Total International is approximated to EAFE (i.e. Developed/International) for 1985-1987, as we don't have any reliable numbers for EM in this time frame (a topic I'll elaborate on within a few days).
- EAFE is in turn coming from the MSCI EAFE NR USD data series.
- Previous versions of Simba were using the MSCI EAFE GR USD data series, but all Vanguard International funds track the 'NR' indices, so I fixed this in my working version, using 'NR' series in all cases. Differences are fairly minor.
- and then yes, the 1985 return (before any ER adjustment math) is 56.16, while 1986 goes to a dizzying 69.44 and 24.63 in 1987. What a good time for an International investor, according to MSCI.

For Portfolio Visualizer, it is a little more complicated to explain, but interesting, so bear with me:
- Let's start by the 'Quality minus Junk' monthly data series found here on the AQR Web site (told you, bear with me!)
- Download the equities data set (add aggregates if you wish), and look at the MKT tab. This is source data for the 'quality minus junk' computation (which we really don't care about), let's stick with the source data.
- Those are International returns for various countries (from Austria to Sweden and USA), and one aggregate is 'Global ex USA'.
- Ah, but wait, there is a subtlety. pvguy told me that those are excess returns over risk-free returns (e.g. US T-Bills), and he's correct (see below).
- So do some math to add the T-Bills returns and convert the monthly returns to annual, and you will find what Portfolio Visualizer tells you, I just checked with 1985, 1986, 1987. And yes, 1985 is strikingly different, around 15.3.

Ok, let's dig one step further:
- the 'quality minus junk' data series came from this article from Cliff Asness and al.
- this paper indicates "Our sample consists of 39,308 stocks covering 24 countries between June 1951 and December 2012. The 24 markets in our sample correspond to the countries belonging to the MSCI World Developed Index as of December 31, 2012. We report summary statistics in Table I. Stock returns and accounting data are from the union of the CRSP tape and the XpressFeed Global database. All returns are in USD, do not include any currency hedging, and are measured as excess returns above the U.S. Treasury bill rate."
- a few sentences later, it says "As shown in Table I, with the exception of Canada (whose coverage starts in 1982) for most countries XpressFeed’s Global coverage starts in 1986. Our sample runs from January 1986 to December 2012.". Er, what?
- hm, let's come to the AQR spreadsheet, and pay closer attention to the year 1985. Ah yes, Global Ex-USA is indeed equal to Canada! Whoah! All Canadian posters on this forum suddenly feel quite empowered, right? :beer

You tell me, did I make my case? :wink:

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 Dec 24, 2016 1:48 am

I reported my findings to the Portfolio Visualizer author (pvguy), and he decided to remove the 1985 data point, and start his Total-International series in 1986.

And on my side, I am reconsidering the data sources too, as this investigation made me realize that I could use better MSCI data series. More on this after I sleep on it... This should reduce the remaining discrepancies. Stay tuned!

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 Dec 24, 2016 12:02 pm

As usual, excellent sleuthing, siamond! Thanks.

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

pvguy
Posts: 25
Joined: Mon Sep 23, 2013 7:54 pm
Location: Austin, TX
Contact:

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

Post by pvguy » Sat Dec 24, 2016 12:46 pm

siamond wrote:I reported my findings to the Portfolio Visualizer author (pvguy), and he decided to remove the 1985 data point, and start his Total-International series in 1986.

Thank you Siamond for catching the issue with the AQR data start year. Note that depending on the year the AQR data still has pretty large differences compared to MSCI EAFE (e.g. 1995 & 1999).

Regarding the asset class data source changes, the changes to Portfolio Visualizer asset class data sources were driven by the switch to monthly returns. For portfolio backtesting purposes annual returns are sufficient, but for calculating portfolio risk metrics and for estimating covariance matrices for financial modeling techniques we would prefer to have a higher sampling frequency. With the exception of a few asset classes, e.g., emerging markets, monthly returns are available all the way to 1970s based on the existing indexes (MSCI for international developed, FTSE for REITs, S&P for commodities etc.). I hope to have the backtesting period extended shortly based on the available indexes.

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 Dec 24, 2016 2:14 pm

As previously hinted at, this AQR digging exercise incidentally lead us in another direction. Tyler and myself exchanged ideas last night and we came up with the conclusion that, for Total International, we'd be better off doing the following changes:
- Replace MSCI EAFE by MSCI World ex-USA for Total Int'l 1970-1987
(still a crude mapping by lack of emerging countries -MSCI World is a developed countries index-, but at least this adds Canada)
- Replace the 85/15 EAFE/EM coarse rule by MSCI ACWI ex-USA for Total Int'l 1988-1996
(unfortunately requiring a small manual adjustment to go from GR returns to NR returns - same issue we have for the EM series)

Note that Vanguard VGTSX has been meandering a bit between EAFE and ACWI, see its historical benchmarks below, but they seem to have converged to an ex-US (including Canada!) approach:
MSCI EAFE + Emerging Markets Index through December 15, 2010;
MSCI ACWI ex USA IMI Index through June 2, 2013;
and FTSE Global All Cap ex US Index thereafter.


Tyler is working on advanced tools to compare how well two data series compare (something fancier than the classic tracking error metric), and he confirmed that those choices are better, by comparing 1997+ numbers between the indices and the actual VGTSX trajectory.

And... lo and behold, this also (somewhat) reduces the discrepancies between the AQR series previously discussed and those new numbers.

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 Dec 24, 2016 2:35 pm

And while I was at it, I finally decided to mark those few annual returns which are rather crude mappings in italics, in both the description of data sources, and in the raw data itself. A few cases in point:
- MSCI World ex-USA for Total Int'l 1970-1987 - cf. lack of EM weighing in those returns
- Longinvest's bonds fund spreadsheet (30-11 model) 1871-1941 for LT Treasuries - cf. lack of historical LT interest rates by them
- US Total Stock Market (TSM) for REITs 1970-1971 - yes, this one isn't pretty, but most other major series extend to 1970 now

Careful users can ponder about those debatable choices, and less attentive users should still find fairly decent data series overall.

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 Dec 24, 2016 6:27 pm

Last change of the day, and a big one. After trying quite a few things, it became apparent that there is just no reliable source for Emerging Markets (EM) that we can use for the 1970-1987 time period, before MSCI Emerging kicks in. The only proper series we could identify are provided by Morningstar DMS or by Global Financial Data, which both require an expensive subscription, and cannot be publicly reproduced anyway. The assumption made in previous instances of the Simba spreadsheet of using an IFA formula approximating EM as 50% Int'l Value and 50% Int'l Small doesn't seem to make much sense, and doesn't not work AT ALL for known years - a clear showstopper.

Tyler and myself have been trying to address this problem for quite a while now, but just couldn't find a proper way to proceed, except acknowledge that the EM data series has to be restricted to 1985+ (making a very crude mapping to MSCI World ex USA for 1985-1987 just to fill the gap until the MSCI EM data series begins, which is the true reference). To add a bit of insult to injury, MSCI strangely doesn't provide an 'NR' series for EM until 1999, so I had to make a manual adjustment from the 'GR' series to the 'NR' series, approximating the delta that can be observed in known years (see NR/GR posts starting here).

Bottomline, here are the data sources for the EM series, restricted to 1985+:
- Crude mapping: MSCI World ex USA NR USD 1985-1987 (Developed Only)
- MSCI EM GR USD minus 0.3% 1988-1994
- Vanguard Emerging Markets Stock Index Fund (VEIEX) 1995+

Not exactly a satisfying outcome, but this is the best we could come up with... :| Feedback welcome.

MachineGhost
Posts: 107
Joined: Sat Feb 14, 2009 2:46 am

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

Post by MachineGhost » Sun Dec 25, 2016 9:33 pm

siamond wrote:Not exactly a satisfying outcome, but this is the best we could come up with... :| Feedback welcome.


Frontier, Emerging and Developing were essentially all the same thing in the 1970's. Heck, BRIC didn't even exist until the early 2000's. So I wouldn't worry too much about it because emerging was clearly not investable as an index until whenever it was. What might be more interesting is exactly what context prompted MSCI to come out with an emerging index because they wouldn't have done it unless there was demand in the years leading up to it. So something pulled away from Communism, or what?

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 Dec 28, 2016 12:50 pm

And to finish with International... I scrutinized International Small, and I believe this is due for a change. Another asset class moving in the 1985+ group, with updated data sources. Another overly rosy (past) CAGR that would become significantly less rosy if we proceed - which is my working assumption.

See all details in this dedicated thread.

MachineGhost
Posts: 107
Joined: Sat Feb 14, 2009 2:46 am

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

Post by MachineGhost » Wed Dec 28, 2016 4:08 pm

An interesting thing I learned today is that the S&P 500 is based relative to the price set back in 1941/1942, long before its inception. So they appear to be using the inferior, narrowband S&P 90 to make current day adjustments. I'm not sure what the full consequence of this is, but it seems very unkosher.

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

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

Post by AlohaJoe » Thu Dec 29, 2016 1:51 am

While searching on Google for something totally unrelated I came across this paper on NBER:

Towards A History Of The Junk Bond Market, 1910-1955

In the appendix they provide monthly yields on junk bonds from 1910 to 1955.

I think that's enough to generate returns by using the same method Ibbotson did (which is inferior to longinvest's method; but longinvest's method requires knowing the rate for various terms, which this paper doesn't give us).

I've only skimmed the paper but I'm not sure why they stopped in 1955; that still leaves a pretty large gap in any kind of backtesting spreadsheet unfortunately.

edited to add: After a bit of digging, it still isn't entirely clear why the authors chose 1955 as their end point. However, this appears to be an extension of a previous work. (The previous work had quarterly rates; this has monthly rates.) The previous work used the same time period, 1910-1955. It also looks like the authors are interested in the Depression so the time period chosen may reflect that: they're trying to look at the impact of the Depression so they're only looking at a a few decades around that point, instead of trying to create a time series for the entire century. But that's just my guess based on a bit of googling.
Last edited by AlohaJoe on Wed Jan 04, 2017 12:31 am, 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 » Thu Dec 29, 2016 10:34 am

AlohaJoe wrote:While searching on Google for something totally unrelated I came across this paper on NBER:

Towards A History Of The Junk Bond Market, 1910-1955

In the appendix they provide monthly yields on junk bonds from 1910 to 1955.

I think that's enough to generate returns by using the same method Ibbotson did (which is inferior to longinvest's method; but longinvest's method requires knowing the rate for various terms, which this paper doesn't give us).

I've only skimmed the paper but I'm not sure why they stopped in 1955; that still leaves a pretty large gap in any kind of backtesting spreadsheet unfortunately.

Thanks for the new pointer, AlohaJoe. We happen to have a pretty decent history of high-yield bonds in Simba, since VWEHX annual returns start in 1979. I did a quick search on corresponding Barclays indices and they don't go earlier than the mid-90s. A brief look at yields on FRED give similar results. If we were to work more on the topic, I would aim at extending historical returns to 1970+ to start with, although I am not quite sure how to proceed. Yes, it is a bit baffling that the NBER paper stops in 1955.

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 Dec 29, 2016 10:52 am

One of the last items on my TODO list for the early January official update... Time is running short!

For US factor-oriented funds (value/growth/blend vs size), I extended the applicability of the corresponding MSCI indices as far back in history as possible, replacing corresponding FTSE/Russell annual returns. While keeping FTSE/Russell for older years. Here is what I did so far:

=> for Mid Cap Blend, introduced MSCI US Mid Cap 450 for 1993-1998
=> for Mid Cap Value, extended MSCI US Mid Cap Value to 1993-2006 (instead of 1996-2006)
=> for Mid Cap Growth, extended MSCI US Mid Cap Growth to 1993-2006 (instead of 1996-2006)
=> for Small Cap Value, introduced MSCI US Small Cap Value for 1993-1998
=> for Small Cap Growth introduced MSCI US Small Cap Growth for 1993-1998

This all seems fairly straightforward, and the practical impact of the changes is pretty small. I do not plan to change anything about Large Cap Blend (S&P 500) nor Small Cap Blend, because we have enough history with the corresponding Vanguard funds (VFINX and NAESX).

This leaves Large Cap Value and Large Cap Growth. Here I am more hesitant. In both cases, we have Vanguard funds history going back to 1993 (VIVAX and VIGRX), and this overrides what MSCI Large Value/Growth indices would provide. This also overrides what S&P 500 Growth/Value indices would provide. But we have the broader categories, MSCI USA Value and MSCI USA Growth, which start in 1970 and 1974.

On one hand, one could acknowledge that (Total) Value is probably pretty close to (Large) Value. Case in point, TSM and S&P 500 trajectories are very close. Also, surprisingly enough, computing the 1995+ tracking error between MSCI USA Value and Vanguard VIVAX gives a significantly better result than either Russell 1000 Value or MSCI USA Large Value (or MSCI US Prime Market Value). Same pattern for Growth.

On the other hand, I like the consistency across Small/Mid/Large of using MSCI indices for 1993+ (when not overridden by Vanguard actuals), and using Russell before that (which does properly distinguish between Small/Medium/Large). I would vote to keep it that way. Thoughts?

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

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

Post by stlutz » Thu Dec 29, 2016 8:22 pm

On the other hand, I like the consistency across Small/Mid/Large of using MSCI indices for 1993+ (when not overridden by Vanguard actuals), and using Russell before that (which does properly distinguish between Small/Medium/Large). I would vote to keep it that way. Thoughts?


+1 from me.

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 Jan 03, 2017 11:08 pm

I did one more update of the working spreadsheet:
- main change is the LTT series, since longinvest was kind enough to update his bond fund model with improved inputs (e.g. 1942+ NBER long-term interest rates)
- I ran various types of consistency tests, found a couple of minor mistakes (notably on the new MTUM series)
- and did a bit of clean-up

This should be the last update of this DRAFT2 working document, which includes returns till 2015. Now I'm going to create a new 'DRAFT3' working document and start inserting the 2016 returns... Stay tuned.

In parallel, I uncovered a new issue, some of the historical returns Vanguard provided for their own funds do not match corresponding Morningstar data. Usually, it's a basis point or two, no big deal, but I found a couple of more disturbing cases. So far, I chose to trust Vanguard over Morningstar, but I'll try to contact them to see if they have some ideas of what is happening here.

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 Jan 04, 2017 10:59 pm

And... here is the long-awaited update with all changes previously discussed AND the 2016 returns. I will keep it in draft form for a few more days, as I am a bit puzzled by those few discrepancies between Vanguard and Morningstar, although the impact should be very minimal. Also maybe an early tester will notice some issue to address before we finalize the public version.

Here is the link for the spreadsheet v16a, DRAFT3:
https://drive.google.com/open?id=0B0svRQGBG_eaODBNSGZfamxza1U

The winner of the year is VGPMX (Precious Metals) with a 50.64% surge in 2016. To put things in perspective, VGPMX dropped by double-digits four years in a row before that.

The loser of the year is VGHCX (Health) with a -8.99% drop in 2016. To put things in perspective, VGHCX remains the best performer for the past 30 years (since 1985).

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 » Thu Jan 05, 2017 4:11 pm

^^^ Perhaps, but your comments should be taken in the context of historical performance.

New investors should be reminded that past performance does not predict future results.

See the wiki: Callan periodic table of investment returns
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 » Thu Jan 05, 2017 4:39 pm

LadyGeek wrote:^^^ Perhaps, but your comments should be taken in the context of historical performance.

New investors should be reminded that past performance does not predict future results.

See the wiki: Callan periodic table of investment returns

That was... my exact point... :wink:

dcabler
Posts: 254
Joined: Wed Feb 19, 2014 11:30 am

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

Post by dcabler » Thu Jan 05, 2017 4:52 pm

siamond wrote:
LadyGeek wrote:^^^ Perhaps, but your comments should be taken in the context of historical performance.

New investors should be reminded that past performance does not predict future results.

See the wiki: Callan periodic table of investment returns

That was... my exact point... :wink:


Pretty much how I read siamond's 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 » Thu Jan 05, 2017 5:04 pm

It was mine as well. However, I often take the perspective of someone who is learning and may not understand the intent. Hence, my wiki link.
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 » Sun Jan 08, 2017 1:00 am

siamond wrote:And... here is the long-awaited update with all changes previously discussed AND the 2016 returns. I will keep it in draft form for a few more days, as I am a bit puzzled by those few discrepancies between Vanguard and Morningstar, although the impact should be very minimal. Also maybe an early tester will notice some issue to address before we finalize the public version.

Here is the link for the spreadsheet v16a, DRAFT3:
https://drive.google.com/open?id=0B0svRQGBG_eaODBNSGZfamxza1U

I have been running various sanity tests, notably about some lazy portfolios that Barry Barnitz recently documented through blog posts on Financial Page. This led us to tweak a few things to improve consistency, and one thing leading to another, I decided to add one more data series, Short Term Bonds (STB):
- Vanguard Short-Term Bond Index Fund Investor Shares (VBISX) 1995+
- Bloomberg Barclays US Government/Credit 1-5 Yr TR USD 1976-1994
- Longinvest's bonds fund (4-2 model) for previous years.

This may seem a tad redundant as we already have ST Treasuries and ST Investment Grade, but those are active funds, and VBISX (which is more of a total-bonds flavor) is a true index fund. And this proves useful for a couple of lazy portfolios (Bill Bernstein No Brainer and Frank Armstrong Ideal Index). So it seemed like a good addition to introduce, even if it's a bit last minute.

PS. for those of you who already downloaded DRAFT3, please download it again, and note that I had to shift things around a bit to make room for STBs in the list of asset classes. This should be the last change like that, no more re-ordering - in 2016, at least.

dcabler
Posts: 254
Joined: Wed Feb 19, 2014 11:30 am

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

Post by dcabler » Sun Jan 08, 2017 8:46 am

Speaking of "one more thing" (now why does that remind me of that old TV show, Columbo?) :-)
Ever think about adding 1 year CD's? I'm sure it won't be too incredibly different than some of the other short term items in the spreadsheet, but it has been a very common investment vehicle.

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

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

Post by AlohaJoe » Sun Jan 08, 2017 9:48 am

dcabler wrote:Speaking of "one more thing" (now why does that remind me of that old TV show, Columbo?) :-)
Ever think about adding 1 year CD's?


Are there any good (free) sources of historical 1 year CD rates? FRED has 6-month CDs going back to 1964 but not 12-month CDs that I can find. (And they discontinued the 6-month series.)

dcabler
Posts: 254
Joined: Wed Feb 19, 2014 11:30 am

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

Post by dcabler » Sun Jan 08, 2017 10:04 am

AlohaJoe wrote:
dcabler wrote:Speaking of "one more thing" (now why does that remind me of that old TV show, Columbo?) :-)
Ever think about adding 1 year CD's?


Are there any good (free) sources of historical 1 year CD rates? FRED has 6-month CDs going back to 1964 but not 12-month CDs that I can find. (And they discontinued the 6-month series.)


I seem to have run across one recently - but of course I didn't bookmark it. At one time, for kicks, I was in the local university library and I noticed that the NY Times has them published daily at least back to the early 1970's when I looked in the archives (microfilm!). I had almost forgotten about the days when interest rates and stocks prices were published as fractions instead of decimals. Anyway, I'll see if I can locate the other source I recently came across.

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

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

Post by siamond » Sun Jan 08, 2017 1:09 pm

dcabler wrote:Speaking of "one more thing" (now why does that remind me of that old TV show, Columbo?) :-)
Ever think about adding 1 year CD's? I'm sure it won't be too incredibly different than some of the other short term items in the spreadsheet, but it has been a very common investment vehicle.

I can take a note for next rounds, but this round is closed (besides fixing bugs), no more "one more thing"... :wink:

What is special about 1-year CDs? As opposed to other durations?

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 » Sun Jan 08, 2017 1:50 pm

I've bought many, many CDs over the last six+ years, but never a 1-year CD. Mostly 5-year, a few 6- and 7-year, and a few 2- and 3-year CDs that had rates closer to 5-year at the time. Generally a good savings account pays about as much as a 1-year CD, sometimes even a bit more.

Note that FRED CD rates are not representative of competitive CDs, like the ones I buy. The FRED 5-year jumbo rate currently is only 0.80% (https://fred.stlouisfed.org/graph/?g=cjfF.), while it's easy to get a 5-year CD with a rate of 2.25% or more (DepositAccounts: 5 Year CD Rates).

I think it would be great if we could cobble together something that represented investing in good, direct CDs, including taking advantage of the early withdrawal options to reinvest at higher rates if justified, but I seriously doubt the data is easily available if available at all. DepositAccounts.com shows rate histories for some banks and credit unions, but not for many years back; e.g., late 2009 for PenFed.

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

dcabler
Posts: 254
Joined: Wed Feb 19, 2014 11:30 am

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

Post by dcabler » Sun Jan 08, 2017 5:54 pm

siamond wrote:
dcabler wrote:Speaking of "one more thing" (now why does that remind me of that old TV show, Columbo?) :-)
Ever think about adding 1 year CD's? I'm sure it won't be too incredibly different than some of the other short term items in the spreadsheet, but it has been a very common investment vehicle.

I can take a note for next rounds, but this round is closed (besides fixing bugs), no more "one more thing"... :wink:

What is special about 1-year CDs? As opposed to other durations?


Nothing special - just convenient, if it exists, since the rest of Simba reports 1 year returns. As reported in this thread, a lot of people build ladders out of longer CD's. I do know some who withdraw one year's expenses, putting 1/2 of it in a 6 month CD. I also know folks who buffer X number of years in CD's without laddering.

If it gets put in, though, it probably needs to be simple - just an idea what parking in cash for a bit looks like. I doubt we'll be able to find historical rates for different durations that go back very far and I'm fairly certain that we won't find "best" rates available for each duration going back far enough to make it useful. An "average" for one or two durations is the best I think we could hope for, at least in the early 70's
Last edited by dcabler on Sun Jan 08, 2017 6:08 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 » Sun Jan 08, 2017 6:03 pm

dcabler wrote:If it gets put in, though, it probably needs to be simple - just an idea what parking in cash for a bit looks like.

We already have a T-Bills series, which should achieve such purpose.

But yes, understood, it might be interesting to have a historical record allowing to backtest CD-centric strategy. Doesn't seem too clear how to keep it simple (given the diversity of such strategies) AND to get access to proper historical records (probably the gating factor anyway). Ideas welcome.

It does seem that some decent history is available: http://www.bankrate.com/finance/cd-rates-history-0112.aspx, but Kevin's point about competitiveness isn't quite compatible with the averaging here...

dcabler
Posts: 254
Joined: Wed Feb 19, 2014 11:30 am

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

Post by dcabler » Sun Jan 08, 2017 6:10 pm

siamond wrote:
dcabler wrote:If it gets put in, though, it probably needs to be simple - just an idea what parking in cash for a bit looks like.

We already have a T-Bills series, which should achieve such purpose.

But yes, understood, it might be interesting to have a historical record allowing to backtest CD-centric strategy. Doesn't seem too clear how to keep it simple (given the diversity of such strategies) AND to get access to proper historical records (probably the gating factor anyway). Ideas welcome.

It does seem that some decent history is available: http://www.bankrate.com/finance/cd-rates-history-0112.aspx, but Kevin's point about competitiveness isn't quite compatible with the averaging here...


Yes, bankrate.com seems to be the go-to place for CD rate history, but doesn't go back far enough, in my opinion. It's why I went to the library way back when. Unfortunately, it was to just see if such things were available (they are). I'll search some more online and, worst case, I'll head back to the library and go through NY Times or WSJ and manually extract them.

Dru

dcabler
Posts: 254
Joined: Wed Feb 19, 2014 11:30 am

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

Post by dcabler » Sun Jan 08, 2017 6:11 pm

dcabler wrote:
siamond wrote:
dcabler wrote:If it gets put in, though, it probably needs to be simple - just an idea what parking in cash for a bit looks like.

We already have a T-Bills series, which should achieve such purpose.

But yes, understood, it might be interesting to have a historical record allowing to backtest CD-centric strategy. Doesn't seem too clear how to keep it simple (given the diversity of such strategies) AND to get access to proper historical records (probably the gating factor anyway). Ideas welcome.

It does seem that some decent history is available: http://www.bankrate.com/finance/cd-rates-history-0112.aspx, but Kevin's point about competitiveness isn't quite compatible with the averaging here...


Yes, bankrate.com seems to be the go-to place for CD rate history, but doesn't go back far enough, in my opinion. It's why I went to the library way back when. Unfortunately, it was to just see if such things were available (they are). I'll search some more online and, worst case, I'll head back to the library and go through NY Times or WSJ and manually extract them.


dcabler
Posts: 254
Joined: Wed Feb 19, 2014 11:30 am

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

Post by dcabler » Sun Jan 08, 2017 6:22 pm

dcabler wrote:
dcabler wrote:
siamond wrote:
dcabler wrote:If it gets put in, though, it probably needs to be simple - just an idea what parking in cash for a bit looks like.

We already have a T-Bills series, which should achieve such purpose.

But yes, understood, it might be interesting to have a historical record allowing to backtest CD-centric strategy. Doesn't seem too clear how to keep it simple (given the diversity of such strategies) AND to get access to proper historical records (probably the gating factor anyway). Ideas welcome.

It does seem that some decent history is available: http://www.bankrate.com/finance/cd-rates-history-0112.aspx, but Kevin's point about competitiveness isn't quite compatible with the averaging here...


Yes, bankrate.com seems to be the go-to place for CD rate history, but doesn't go back far enough, in my opinion. It's why I went to the library way back when. Unfortunately, it was to just see if such things were available (they are). I'll search some more online and, worst case, I'll head back to the library and go through NY Times or WSJ and manually extract them.



Mortgage-x.com has 3 month rates in monthly increments going back to 1967 extracted from FED data. Just a quick visual check around 1970 shows higher rates than the T-bill rates shown in Simba. Might still be of some use. http://mortgage-x.com/general/indexes/codi_history.asp

dcabler
Posts: 254
Joined: Wed Feb 19, 2014 11:30 am

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

Post by dcabler » Sun Jan 08, 2017 6:24 pm

Here's another source of 6 month CD rates going back to the middle of 1964.

http://www.forecast-chart.com/rate-cd-interest.html

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 Jan 09, 2017 4:53 pm

Ok, let's wait until dcabler comes back from his CD rates foray in the dust of the Wall Street Journal then! :wink:

I did a couple of minor updates to the spreadsheet (same link), mostly about inflation:
- added inflation stats to compare to CAGR of a portfolio being studied and as an easy reference to grab
- updated the 2016 inflation number to 1.69 (from 1.64), since BLS provided their November numbers (December not yet available)

I will officialize Tuesday or Wednesday, I'm just trying to give a chance to Vanguard to answer my query about the few numbers not matching Morningstar.

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 Jan 11, 2017 10:09 am

That's it, the Simba spreadsheet v16a is official now! Please check this post to download it, and read about the main changes.

Early testers are strongly encouraged to download this official version, and discard any previous drafts. Note that I did one last minute change last night, revisiting the Gold data series, where we had a bit of a mix up between a price series and a NAV series. I also took the opportunity to switch from SPDR GLD to iShares IAU (lower ER, similar history, more consistent with the rest of the data series).

MachineGhost
Posts: 107
Joined: Sat Feb 14, 2009 2:46 am

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

Post by MachineGhost » Wed Jan 11, 2017 10:43 am

Excellent work, siamond!!!

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

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

Post by Tyler9000 » Wed Jan 11, 2017 11:20 am

Thanks siamond! I know how much work you've put into this, and truly appreciate all that you do.

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 Jan 21, 2017 6:01 pm

For those of you who are very impatient to compute real returns for 2016, the U.S. Bureau of Labor Statistics (BLS) released their finalized inflation number for 2016, and it is a bit higher than expected, at 2.07%.

I will issued a 16b update of the Simba spreadsheet early February, when I get the other few missing data points (e.g. some historical series which depend on the latest FRED rates and Prof. Shiller S&P 500 series).

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

Re: Simba's backtesting spreadsheet

Post by LadyGeek » Sun Jan 22, 2017 11:13 am

Administrative: siamond has requested this thread's title be changed to include "Simba", as that's how most members refer to the spreadsheet.

I changed the title to be consistent with the wiki: Simba's backtesting 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.

MachineGhost
Posts: 107
Joined: Sat Feb 14, 2009 2:46 am

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

Post by MachineGhost » Sun Jan 22, 2017 2:43 pm

siamond wrote:and Prof. Shiller S&P 500 series).


What are you referring to here?

I also have Cowles' third correction white paper somewhere. Was that used in the SPWC?

For that matter, I may also have Cowles' response to defects in his original and seminal momentum paper where he used mathematical malarky to overstate the momentum effect.

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

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

Post by siamond » Sun Jan 22, 2017 3:20 pm

MachineGhost wrote:
siamond wrote:and Prof. Shiller S&P 500 series).

What are you referring to here?

If you go to the 'Data_TR_USD' tab in the spreadsheet, and scroll right a good deal, you'll find three 'historical' data series, one is the annual S&P 500 returns directly derived from Prof. Shiller well-known database. Which has somewhat peculiar dynamics, as Prof. Shiller averages the daily prices in January instead of taking the S&P 500 value on Jan 1st. Can't say I like his methodology (even if I understand the historical issue he faced for the early years), but this series is so well known that I thought useful to provide it as a reference - without judging it. MANY academic studies (or investment discussions on this very forum) are directly based on this Shiller data series combined with either IT bonds or T-Bills, and I felt that it would be useful to have a common reference for those. You can use them in the Compare_Portfolios calculators with the 1871+ columns (S to Y, scroll down a bit).

(as a side note, I am having an e-mail exchange with Prof. Damodaran, as I found a couple of strange things about his T-Bills series for the early years and the recent years; he acknowledged that something might be amiss, and will investigate further. We do NOT use any of Prof. Damodaran data series in Simba, but I was trying to figure out the discrepancies...)

MachineGhost wrote:I also have Cowles' third correction white paper somewhere. Was that used in the SPWC?

For that matter, I may also have Cowles' response to defects in his original and seminal momentum paper where he used mathematical malarky to overstate the momentum effect.

About SPWC, we discussed it in this thread, and for now, we really can't do much with it (Prof. Jones being unresponsive). So I did what we discussed in the corresponding thread, and added a note and a pointer to the corresponding paper in the Data_Misc tab.

What are you referring to with the "Cowles' third correction white paper"? What is this about?

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

Re: Simba's backtesting spreadsheet

Post by AlohaJoe » Mon Jan 23, 2017 6:29 am

I discovered that Professor Schwert makes available both:

- Monthly stock returns 1802-1925: http://schwert.ssb.rochester.edu/mstock.htm
- Daily stock returns 1885-1962: http://schwert.ssb.rochester.edu/dstock.htm

Schwert's paper is older (1990) and takes a different approach than Wilson & Jones but he also tried to "unaverage" the data from Cowles.

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

Re: Simba's backtesting spreadsheet

Post by siamond » Mon Jan 23, 2017 2:42 pm

AlohaJoe wrote:I discovered that Professor Schwert makes available [...]

Thanks, AlohaJoe. I quoted your post (and links) in the SPWC thread, which seems more suitable for discussing such content. Great find!

(and I will add a pointer to the data Web page and to the PDF article from Prof. Schwert in the Data_Misc tab of the Simba spreadsheet).

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

Re: Simba's backtesting spreadsheet

Post by siamond » Mon Feb 06, 2017 7:28 pm

Please go check the latest revision of the spreadsheet (v16b) in this post.

Among a few other updates, the 2016 numbers have been finalized, including the inflation. Feedback welcome.

larssl780
Posts: 3
Joined: Tue Feb 07, 2017 4:39 pm

Re: Simba's backtesting spreadsheet

Post by larssl780 » Tue Feb 07, 2017 4:59 pm

Hi guys, I've tried to search for this but haven't managed to find anything detailed, so sorry if this has already been asked to death). What's the motivation for the "net return" calculation (in the tab "Data_TR_USD")?

For reference, it's like this (for example in cell B151 of that tab):

Net return = (Gross return - ER)/(1+ER)

Where does the (1+ER) term come from, ie. why isn't the formula simply Net return = Gross Return - ER?

Kind regards,
Lars

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

Re: Simba's backtesting spreadsheet

Post by siamond » Tue Feb 07, 2017 5:41 pm

larssl780 wrote:Net return = (Gross return - ER)/(1+ER)

Where does the (1+ER) term come from, ie. why isn't the formula simply Net return = Gross Return - ER?

Hi Lars, thank you for your interest.

This is geometric math instead of arithmetic math, which is more appropriate for quantities like returns that compound over time. Do a little bit of algebra and you'll quickly figure out that "(Gross Return - ER)/(1+ER)" is strictly equivalent to "(1 + Gross Return) / (1 + ER) - 1". Which is the geometric way of subtracting ER to Gross Return.

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

Re: Simba's backtesting spreadsheet

Post by LadyGeek » Tue Feb 07, 2017 7:18 pm

larssl780, Welcome! That's an excellent question. If you don't understand the answer, please let us know and we'll try again.

Like now. siamond- I understand the need for geometric math, but I'm having some difficulty to help. Can you please show the first few steps to derive that "little bit of algegra"?

Some relevant formulas are here: Rate of return

(I'm probably missing an obvious point, but can't think of it right now.)

==================
"Compound interest" is an exponential formula and geometric averaging applies.

"Simple interest" does not have exponents in the equation and arithmetic averaging applies.
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: Simba's backtesting spreadsheet

Post by siamond » Tue Feb 07, 2017 8:05 pm

LadyGeek wrote:siamond- I understand the need for geometric math, but I'm having some difficulty to help. Can you please show the first few steps to derive that "little bit of algebra"?

Sure. Let's start from the formula that clearly represents a geometric way to subtracting E from R.
(1+R)/(1+E) - 1
Which is the same as:
(1+R)/(1+E) - (1+E)/(1+E)
Which is the same as:
((1+R) - (1+E)) / (1+E)
Which is the same as:
(R-E) / (1+E)

Makes sense?

PS. to be honest, the corresponding formula in Simba did give me pause the first time I saw it. Took me a minute or two to realize that it was the same as the more intuitive formula I would have used!

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

Re: Simba's backtesting spreadsheet

Post by Kevin M » Tue Feb 07, 2017 9:03 pm

To add a bit more to the geometric math subtraction of ER ...

The return values are annualized return, also referred to as compound annual growth rate, or CAGR. This is the value, r, that you can plug into this formula to get cumulative growth over N years:

(1 + r) ^ N

Since we are compounding r over N years, we must subtract the expense ratio, E, from the gross return, R, in a way that also works with compounding. This is because the expense is subtracted each year, reducing the net amount you have to continue compounding in subsequent years.

Note that if you plug in reasonable values, the difference between R - E and (1+R)/(1+E) - 1 is quite small. Try it with R = 2% and E = 0.2%, in which case R - E = 1.800%, while (1+R)/(1+E)-1 = 1.796%, so they both round to 1.80%. Or try it with R=5% and E=0.2%, and you get 4.80% vs. 4.79%--again, not much difference.

Note the similarity to the difference between the exact and approximate formulas for adjusting returns for inflation. The approximate formula is r = R - I, where r is real return, R is nominal return, and I is inflation rate. The exact formula is r = (1+R)/(1+I) - 1. Again, plugging in some sample numbers, I'll use R = 5% and I = 2%.

Approximate: r = R - I = 5% - 2% = 3%.

Exact: r = (1+R)/(1+I) - 1 = 1.05/1.02 - 1 = 2.94%.

Pretty close, so most people are going to be OK with the approximate value, but it's not exactly correct, and when compounded over many years could make a relevant difference.

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

Post Reply