Simba's backtesting spreadsheet [a Bogleheads community project]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
jeg208
Posts: 42
Joined: Wed Jul 05, 2017 2:42 pm

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

Post by jeg208 » Fri Dec 29, 2017 9:17 am

This is an amazing thread. Keep up the great work Siamond, I look forward to the updates.

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

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

Post by siamond » Thu Jan 04, 2018 12:18 am

Let's come back to the VTMGX/VDVIX topic, as I had planned to address it for the first Simba update of 2018. First, here is the full context, and the proposal that was made when we discussed the topic a few months ago.
Snarfanio wrote:
Mon Oct 09, 2017 1:03 pm
After looking more into this spreadsheet, I have a couple of questions/observations.
1) For Int'l Developed EAFE, VTMGX Admiral is used. Why not the Investor version, which is used for all other Vanguard funds in the spreadsheet?
Barry Barnitz wrote:
Tue Oct 10, 2017 7:24 pm
The confusion regarding the Developed Market Index involves the merger of the former Developed Market fund into the Tax-Managed Fund, which existed with admiral shares.

We tagged this on the wiki tax history page for the former fund----> Vanguard Developed Markets Index Fund (VDMIX) tax distributions and on the like page for the post-merger fund ---> Vanguard Developed Markets Index Fund (VDVIX) tax distributions. We attempt to track the expense history of the merged fund on this page---> Vanguard Developed Markets Index Fund (VDVIX) expenses.
siamond wrote:
Sat Oct 14, 2017 12:50 pm
Here is the full quote from the VTMGX 2016 Shareholder Report, which fully confirms the timeline I had derived from Barry's description on the wiki pages:
The fund’s original Investor Shares were renamed Admiral Shares as of the close of business on May 13, 2011. The Investor Shares
recommenced on December 19, 2013, and are presented separately.
'Presented separately' means the inception of VDVIX.

The 2011 Certified Shareholder Report (Semi-Annual) doesn't provide more explanations, but the salient point is described in two places:
I want to note two important changes we’ve made that will benefit shareholders in the Tax-Managed Funds. First, Vanguard has replaced each fund’s Investor Shares with lower-cost Admiral Shares. In addition, for the four funds in this report, we have eliminated the 1% fee for redemptions of shares held for less than five years. Both changes are part of our ongoing efforts to reduce the cost and complexity of investing for Vanguard clients.
The fund offers two classes of shares: Admiral Shares and Institutional Shares. Admiral Shares are available to any investor who meets the fund’s minimum purchase requirements. Institutional Shares are designed for investors who meet certain administrative, service, and account-size criteria. Prior to May 14, 2011, the fund offered Investor Shares. Effective at the close of business on May 13, 2011, the Investor Shares were converted to Admiral Shares.
Ok, so I'll introduce the following change, as discussed in this post for the first 2018 update of the Simba spreadsheet:
[...] I should use VTMGX as is from 2000 to 2010, use VTMGX + 0.10% for 2011 to 2013, then use VDVIX for 2014+. And use the VDVIX ER for the pre-inception time (which is based on the MSCI EAFE NR USD index data series).

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

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

Post by siamond » Thu Jan 04, 2018 12:38 am

Studying the VDVIX/VTMGX matter a little more, I actually found out that there is a simpler way to proceed. Although VDVIX's inception date is listed by Vanguard as 12/19/2013, the corresponding data series on Morningstar starts on 08/17/1999, which is the inception date of VTMGX. Here is a chart comparing the two trajectories (click on the image to see a larger display).

Image

It is kind of hard to see the difference on the chart, here are the annual returns numbers:

Code: Select all

	VDVIX	VTMGX
2000	-14.36	-14.29
2001	-22.00	-21.94
2002	-15.68	-15.62
2003	 38.56	 38.67
2004	 20.15	 20.25
2005	 13.51	 13.60
2006	 26.17	 26.27
2007	 11.06	 11.15
2008	-41.32	-41.27
2009	 28.17	 28.27
2010	  8.27	  8.36
2011	-12.58	-12.51
2012	 18.47	 18.56
2013	 21.96	 22.06
2014	 -5.82	 -5.66
2015	 -0.29	 -0.18
2016	  2.36	  2.45
2017	 26.31	 26.40
The CAGR difference (roughly 0.10%) is basically equal to the current difference between the ERs for VDVIX and for VTMGX. I would assume that Vanguard reconstructed full histories for an investor fund and the corresponding admiral fund as if both funds had been created in 1999 (in lieu of the somewhat troubled history documented by Barry), and shared the two data series with Morningstar.

And this all makes our life simpler then, we can use VDVIX returns (and ER) in lieu of VTMGX, et voila, problem solved.

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

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

Post by siamond » Thu Jan 04, 2018 12:27 pm

Here is a solid draft of the Simba v17a update to come, including the 2017 returns for all regular funds being tracked. It also includes a few minor fixes, and a couple of new metrics and charts. Some numbers (e.g. inflation) are not final.

If some of the regular users could give it a quick go, and tell me if they notice any issue, I would appreciate it. In the mean time, I'll run a few more sanity checks. Plan is to let this simmer until Monday, then publish an official update.

Simba v17a DRAFT

Rom1b
Posts: 36
Joined: Fri Jan 05, 2018 5:34 am
Contact:

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

Post by Rom1b » Fri Jan 05, 2018 5:38 am

I just want to take the chance to thank you, this is an amazing tool! :beer
And thanks for the regular updates and maintenance which are just as much work if not more.
Im not (yet?) a regular user but if I find any obvious problems not present in the previous version, Ill report them.
Dr Bernstein - If You Can / free PDF, google it | 3Fund Portfolio www.bogleheads.org/forum/viewtopic.php?f=10&t=88005 | CollectiveThought www.bogleheads.org/forum/viewtopic.php?f=10&t=7353

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

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

Post by siamond » Sat Jan 06, 2018 1:55 am

Arumph, while running some consistency tests, I just noticed that Morningstar changed their mind about the NAV at the end of 2017 for two Vanguard funds (VTIBX and VDAIX). Not by much, but they did change the data between Jan 3rd and Jan 5th... I now remember that I had a similar issue last year. I'm now trying to build more automation in my way of collecting and verifying such returns... A good project for the week-end, I guess.

jmk
Posts: 369
Joined: Tue Nov 01, 2011 7:48 pm

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

Post by jmk » Sat Jan 06, 2018 3:29 pm

Wonder if spreadsheet should switch from cpi-u going forward?

Under the previous tax law, inflation is measured by the consumer price index for all urban consumers, also known as the CPI-U, which essentially tracks the cost of goods and services that affect the typical household. The new law adopts a metric called the "Chained CPI". The key difference is that the Chained CPI assumes that if a particular good or service gets too expensive, consumers will trade down to a cheaper alternative.

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

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

Post by siamond » Sat Jan 06, 2018 4:19 pm

jmk wrote:
Sat Jan 06, 2018 3:29 pm
Wonder if spreadsheet should switch from cpi-u going forward?

Under the previous tax law, inflation is measured by the consumer price index for all urban consumers, also known as the CPI-U, which essentially tracks the cost of goods and services that affect the typical household. The new law adopts a metric called the "Chained CPI". The key difference is that the Chained CPI assumes that if a particular good or service gets too expensive, consumers will trade down to a cheaper alternative.
Yes, interesting question, and I have been pondering about this too. Check this thread, where I tried to figure out exactly how that works, and well, nobody had a truly solid answer, and I'll admit I gave up (at least for now). A couple of things I did understand is that:
- Chained CPI has little history, so we'd have to construct an inconsistent series (CPI-U for most years; C-CPI-U for recent years)
- Chained CPI is just an estimate at T0, then gets refined over the course of two years, then finally gets more accurate

I have to say that although your question is very legitimate, using Chained CPI doesn't seem great for our purpose because of those drawbacks. Maybe people should just use a rule of thumb that CPI-U is a metric which is somewhat overestimated (by 0.25% according to BLS studies). The thing I could do to mitigate though, is to add a field where one can easily customize an 'inflation adjustor' (by default 0% for continuity, could easily be set to 0.25% or else), which would be automatically subtracted from the CPI-U numbers? Would that seem useful?

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

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

Post by siamond » Sat Jan 06, 2018 4:57 pm

siamond wrote:
Sat Jan 06, 2018 1:55 am
Arumph, while running some consistency tests, I just noticed that Morningstar changed their mind about the NAV at the end of 2017 for two Vanguard funds (VTIBX and VDAIX). Not by much, but they did change the data between Jan 3rd and Jan 5th... I now remember that I had a similar issue last year. I'm now trying to build more automation in my way of collecting and verifying such returns... A good project for the week-end, I guess.
Another issue I stumbled upon is that, for a few bond funds, Morningstar doesn't report the very exact same 2017 total-return than Vanguard. VUSTX, VBMFX, VWITX, VWLTX and VFSTX have a 0.01% discrepancy. VWEHX has a 0.02% discrepancy. I had a similar issue last year too. Yeah, this is very minor, but still, it's annoying. I always use the Morningstar numbers for recent years, as they are more precise (more decimals).

jmk
Posts: 369
Joined: Tue Nov 01, 2011 7:48 pm

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

Post by jmk » Sun Jan 07, 2018 12:43 pm

siamond wrote:
Sat Jan 06, 2018 4:19 pm
jmk wrote:
Sat Jan 06, 2018 3:29 pm
Wonder if spreadsheet should switch from cpi-u going forward?

Under the previous tax law, inflation is measured by the consumer price index for all urban consumers, also known as the CPI-U, which essentially tracks the cost of goods and services that affect the typical household. The new law adopts a metric called the "Chained CPI". The key difference is that the Chained CPI assumes that if a particular good or service gets too expensive, consumers will trade down to a cheaper alternative.
Yes, interesting question, and I have been pondering about this too. Check this thread, where I tried to figure out exactly how that works, and well, nobody had a truly solid answer, and I'll admit I gave up (at least for now). A couple of things I did understand is that:
- Chained CPI has little history, so we'd have to construct an inconsistent series (CPI-U for most years; C-CPI-U for recent years)
- Chained CPI is just an estimate at T0, then gets refined over the course of two years, then finally gets more accurate

I have to say that although your question is very legitimate, using Chained CPI doesn't seem great for our purpose because of those drawbacks. Maybe people should just use a rule of thumb that CPI-U is a metric which is somewhat overestimated (by 0.25% according to BLS studies). The thing I could do to mitigate though, is to add a field where one can easily customize an 'inflation adjustor' (by default 0% for continuity, could easily be set to 0.25% or else), which would be automatically subtracted from the CPI-U numbers? Would that seem useful?
I tend to agree with you. The chained-cpi for now will just be used to adjust tax brackets, which is separate from returns analysis. It might eventually be used for ss adjustments; but there is no plan at present. Also, there is the integrity of the data which has a relatively constant measure.

The ability to tailor inflation is intriguing. I could see that being useful not only for the chained-cpi issue but also because (so I've heard) many bogleheads report personal inflation much lower than cpi-u. So the more frugal folks could subtract out an adjuster to get a more realistic report of how inflation adjusted returns would be subjectively for them. It will be interesting to see what others say.

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

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

Post by siamond » Sun Jan 07, 2018 12:53 pm

jmk wrote:
Sun Jan 07, 2018 12:43 pm
The ability to tailor inflation is intriguing. I could see that being useful not only for the chained-cpi issue but also because (so I've heard) many bogleheads report personal inflation much lower than cpi-u. So the more frugal folks could subtract out an adjuster to get a more realistic report of how inflation adjusted returns would be subjectively for them. It will be interesting to see what others say.
Yep, you read my mind. I proceeded (cell BZ4 in Data_TR_USD), it's really a small change. I'll probably publish an official update on Monday, after double-checking one last time that Morningstar didn't change numbers again behind my back... :shock:

Latest draft is at the same place: click here.

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

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

Post by siamond » Wed Jan 10, 2018 11:05 am

The official Simba spreadsheet v17a update is now available. You can download it by clicking on this link. Feedback welcome.

Rev17a:
1. Spreadsheet updated for 2017 returns and latest Expense Ratios (ER)
2. Inflation and some historical returns 2017 are only an educated guess for now, waiting for official numbers to come later in January
3a. Fixed VWINX (Wellesley) 1970 coarse mapping - in Rev16d, it wasn't updated with latest LCV value from Tyler's Stock Index Calculator
3b. Fixed 1985-89 coarse mapping for USMV data series - copy and paste error dating from Rev16e
3c. Some historical numbers used to be rounded to two decimals =>now including all known decimals
4. Replaced VTMGX (Int'l Developed/EAFE) by VDVIX, to use the investor's data series of the fund, for consistency with the other funds.
5. Extended entire spreadsheet to 2025 and improved automation f(last year), to ease future annual updates
6a. Added regular SWR (Safe Withdrawal Rate) and PWR (Perpetual Withdrawal Rate) computation for all retirement cycles in a given time period (e.g. 30 years cycles)
6b. Added a portfolio cycles comparison chart in Analyze_Portfolio (e.g. 30 years retirement cycles) with flexible controls
7. Replaced Real/Nominal and Premium True/False controls in Analyze_Portfolio by single Real/Nominal/Premium control
8. Added more Expense Ratio historical data series in Data_Misc: now tracking ERs for VTSMX, VFINX, NAESX, VGTSX, VEIEX, VTRIX, VBMFX, VWELX, FMAGX.
9. Added a setting in Data_TR_USD (top of the inflation column) allowing to adjust the inflation rate by a fixed factor.

Bob
Posts: 194
Joined: Mon Feb 26, 2007 5:15 pm

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

Post by Bob » Wed Jan 10, 2018 1:24 pm

Questions about the "SWR" computation in the "Analyze_Portfolio" sheet of 'Backtest...rev17a' (chart starting in Line 235)

1) Are the annual withdrawals assumed to be increased by the rate of inflation each year? For instance, if rate calculated for 1970 was 4.5% and one took $45,000 from a $1 million portfolio then the next year(1971) they take $45,000 again or they are assumed to take $45,000 plus the rate of inflation in 1970?

2) Does the annual withdrawal occur at beginning or end of each year?

Thanks in advance for the clarification.
BTW, SUPER product you all have created and expanded on over time. Thanks !

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

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

Post by siamond » Wed Jan 10, 2018 2:06 pm

Bob wrote:
Wed Jan 10, 2018 1:24 pm
Questions about the "SWR" computation in the "Analyze_Portfolio" sheet of 'Backtest...rev17a' (chart starting in Line 235)

1) Are the annual withdrawals assumed to be increased by the rate of inflation each year? For instance, if rate calculated for 1970 was 4.5% and one took $45,000 from a $1 million portfolio then the next year(1971) they take $45,000 again or they are assumed to take $45,000 plus the rate of inflation in 1970?

2) Does the annual withdrawal occur at beginning or end of each year?

Thanks in advance for the clarification.
BTW, SUPER product you all have created and expanded on over time. Thanks !
1) yes, this is inflation-adjusted every year
2) beginning of the year
3) thank you on behalf of past and present contributors, appreciated.

As a side note, for the Excel *and* algebra nuts, I found a remarkably compact way of computing the SWR/SAFEMAX across multiple portfolio cycles. Dive in the Portfolio_Math tab if so inclined and/or send me a PM.

I'll add clarifications about 1) and 2). Sorry, been playing with that stuff for so long, I often forget to provide specifics. Please keep providing feedback, this is very useful.

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

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

Post by siamond » Sun Jan 28, 2018 12:14 pm

Somebody suggested this possible addition in a private message:
List the 'CAGR of last tested year' in separate row. I know its already possible, it would just make it a little easier to compare the last to history in just one run.
Technically, this is very easy to do. I am not too sure this is terribly useful though (already very easy to change the start date to be equal to the end date to figure out a portfolio return for a single year), and I am wary about adding new things to Simba without a strong reason to do so. Still, maybe this could be a good usability idea.

Thoughts?

Bongleur
Posts: 2066
Joined: Fri Dec 03, 2010 10:36 am

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

Post by Bongleur » Sun Jan 28, 2018 8:27 pm

Most people could create a new sheet that would copy whatever they wanted.
The hard part is creating an "on-off switch" that would tell the sheet whether or not to perform the copy function.
Turn it ON, run the Simba Test, turn it Off, run another Simba Test. Now you can compare the two runs.

With some formatting, you can do a layout where each column saves the values for a run. Each column has its own on/off switch.

LABEL TEXT / INPUT VALUE / RESULT VALUE then the next run: INPUT VALUE / RESULT VALUE
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.

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

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

Post by siamond » Sun Jan 28, 2018 10:13 pm

Bongleur wrote:
Sun Jan 28, 2018 8:27 pm
Most people could create a new sheet that would copy whatever they wanted.
The hard part is creating an "on-off switch" that would tell the sheet whether or not to perform the copy function.
Turn it ON, run the Simba Test, turn it Off, run another Simba Test. Now you can compare the two runs.

With some formatting, you can do a layout where each column saves the values for a run. Each column has its own on/off switch.

LABEL TEXT / INPUT VALUE / RESULT VALUE then the next run: INPUT VALUE / RESULT VALUE
One could also use two copies of the spreadsheet, open two windows on your computer, position them side-to-side, and you can easily compare two different runs. Or just rely on the two sets of columns in Analyze_Portfolio and Compare_Portfolio to compare two tests side to side. Or do what you suggested. Etc. Many ways to approach that without requiring a change to the spreadsheet itself.

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

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

Post by siamond » Mon Feb 05, 2018 2:24 pm

I finally completed a mini-project I wanted to do for a while... Document the inner workings of the Simba backtesting spreadsheet. The objective is two-fold:
- Provide a better understanding of how the spreadsheet works, to facilitate peer reviews and future maintenance,
- Describe techniques that have been used to compute various investment metrics in a compact manner, which could be useful in other contexts (e.g. your own spreadsheet).

I authored a series of blog articles to do so:
- First article: elaborating on how the spreadsheet is constructed, and providing an overview of its layered structure.
- Second article: elaborating on how risk metrics (e.g. volatility, drawdowns, etc) and risk ratios (e.g. Sharpe, Sortino, etc) are computed.
- Third article: elaborating on more advanced topics (e.g. unbalancing, safe withdrawal rate). Notable finding: a Safe Withdrawal Rate is just a simple harmonic mean.
- Fourth article: addressing a few miscellaneous topics (e.g. end label on charts, compatibility issues, spreadsheet analytics).

I added the corresponding pointers to the Simba wiki page.

amori
Posts: 3
Joined: Fri Feb 09, 2018 4:12 am

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

Post by amori » Fri Feb 09, 2018 4:22 am

Thank you Siamond and Boglehead community, I came across the SIMBA spreadsheet this week, along with the four four wiki articles explaining various aspects, this is quite handy as a sandbox for testing various scenarios using historical data. I see a lot of effort has been put into this with the forum posts going back to 2007.

On a miscellaneous note, as I am using an old machine, and the graphs would generally appear with a bit of delay, for my personal local copy, I am re-saving the file using Excel Binary format (one of the options under Save As), reduces both the file size as well as the graphing time. [Mind you, this save-as feature is only useful for MS Excel users].

User avatar
AtlasShrugged?
Posts: 608
Joined: Wed Jul 15, 2015 6:08 pm

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

Post by AtlasShrugged? » Fri Feb 09, 2018 8:18 am

Bogleheads....I updated the sheet with my Fido funds. One fund, FXSIX, has an inception date of 1988. I am only able to find returns back to 2006. Any ideas where to go to get annual returns by year, since inception?
“If you don't know, the thing to do is not to get scared, but to learn.”

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

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

Post by dcabler » Fri Feb 09, 2018 8:25 am

JCE66 wrote:
Fri Feb 09, 2018 8:18 am
Bogleheads....I updated the sheet with my Fido funds. One fund, FXSIX, has an inception date of 1988. I am only able to find returns back to 2006. Any ideas where to go to get annual returns by year, since inception?
FXSIX tracks the same index as VFINX in the Simba spreadsheet. For the purposes of any accuracy that might matter, why not just use what's already in Simba? Another alternative is to use VFINX, but adjust for the difference in e/r between FXSIX and VFINX and create a pseudo-version of VFINX. Then you can splice together the actual returns of FXSIX you have with the pseud-version you created.

User avatar
AtlasShrugged?
Posts: 608
Joined: Wed Jul 15, 2015 6:08 pm

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

Post by AtlasShrugged? » Fri Feb 09, 2018 8:31 am

FXSIX tracks the same index as VFINX in the Simba spreadsheet. For the purposes of any accuracy that might matter, why not just use what's already in Simba?
OH!!! DUH! What an awesome suggestion. I had not thought of that. :happy
“If you don't know, the thing to do is not to get scared, but to learn.”

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

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

Post by AlohaJoe » Fri Feb 09, 2018 8:42 am

JCE66 wrote:
Fri Feb 09, 2018 8:18 am
Bogleheads....I updated the sheet with my Fido funds. One fund, FXSIX, has an inception date of 1988. I am only able to find returns back to 2006. Any ideas where to go to get annual returns by year, since inception?
All annual reports are filled with the SEC. The SEC has a public database called Edgar that contains all kinds of things. I'm not sure how far back it goes, though. That is, I don't know if something from 1988 would been entered in it. But I'd check there if I couldn't find it anywhere else.

User avatar
AtlasShrugged?
Posts: 608
Joined: Wed Jul 15, 2015 6:08 pm

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

Post by AtlasShrugged? » Fri Feb 09, 2018 8:56 am

All annual reports are filled with the SEC.
Thx AlohaJoe...I took dcabler's suggestion. I found the closest analogue in the VG funds, and 'took' their returns from 1988 onward to backfill what I did not have for my Fido funds. It won't be 'exact', but I think for my purposes, it will be Ok.

This backtesting spreadsheet is incredible. I think I need to play with it a little more to really understand the outputs, and some of the charts. What I find a little concerning is the SWR and PWR charts. They are telling me something on the order of 7.5% SWR and 6.5% PWR for my portfolio, which would seem awfully high. But if it is correct, I will not be unhappy.

Here is a question. Is there a way to use Simba's sheet with the VPW sheet. Or is that an apples to oranges thing?
“If you don't know, the thing to do is not to get scared, but to learn.”

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

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

Post by LadyGeek » Fri Feb 09, 2018 9:57 am

AlohaJoe wrote:
Fri Feb 09, 2018 8:42 am
...The SEC has a public database called Edgar that contains all kinds of things. I'm not sure how far back it goes, though. That is, I don't know if something from 1988 would been entered in it. But I'd check there if I couldn't find it anywhere else.
The wiki has some background info: EDGAR
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

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

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

Post by siamond » Fri Feb 09, 2018 11:41 am

JCE66 wrote:
Fri Feb 09, 2018 8:56 am
What I find a little concerning is the SWR and PWR charts. They are telling me something on the order of 7.5% SWR and 6.5% PWR for my portfolio, which would seem awfully high. But if it is correct, I will not be unhappy.
Be careful to the timeframe... If you do not include time periods like the oil crisis (1972+), pre-oil-crisis (1960+), and the great depression (1927+), you'll miss the really big troubles and this would give you a false impression of high returns for SWR/PWR stats.
JCE66 wrote:
Fri Feb 09, 2018 8:56 am
Here is a question. Is there a way to use Simba's sheet with the VPW sheet. Or is that an apples to oranges thing?
The PMT formula behind VPW is very straightforward, i.e. -PMT(rate, #periods, portfolio-value, 0, 1). Rate is AA_stocks * 5.1% + AA_Bonds + 1.8%. So it is very easy to develop your own spreadsheet (or custom derivation of Simba) that would support it. This being said, analyzing and comparing multiple variations of a withdrawal method is a larger project (several of us have been playing with that for a while!). Which is why I always stayed short of adding something like that in Simba - besides the SWR/PWR metrics, as a baseline of sorts.

User avatar
AtlasShrugged?
Posts: 608
Joined: Wed Jul 15, 2015 6:08 pm

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

Post by AtlasShrugged? » Fri Feb 09, 2018 12:57 pm

This being said, analyzing and comparing multiple variations of a withdrawal method is a larger project (several of us have been playing with that for a while!). Which is why I always stayed short of adding something like that in Simba - besides the SWR/PWR metrics, as a baseline of sorts.
siamond....Thank you so much for your answers. And for the extra support for questions I ask. I am very grateful.

On another note, I only went back to 1988. I'll have to go back further. I am using the closest 'VG analogues' to my Fido funds. Imperfect solution, but hopefully it will work.
“If you don't know, the thing to do is not to get scared, but to learn.”

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

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

Post by siamond » Fri Feb 09, 2018 2:55 pm

JCE66 wrote:
Fri Feb 09, 2018 12:57 pm
On another note, I only went back to 1988. I'll have to go back further. I am using the closest 'VG analogues' to my Fido funds. Imperfect solution, but hopefully it will work.
Ah yes, this explains the high numbers. Send me a PM with the list of Fido funds, maybe I can help you backtracking a bit more.

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

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

Post by siamond » Sat Feb 10, 2018 1:05 am

Here is a draft of version 17b. Feedback would be appreciated over the week-end before I finalize next week.

Here are the changes:
1a. Updated inflation from BLS with finalized 2017 number (2.11%)
1b. Updated historical data series S&P500(h), IT Bonds(h), T-Bills(h) + Canadian series (Data_Misc) with finalized 2017 numbers
1c. Removed US Home Price Index data series in Data_Misc (starting Feb-18, access to such data is more constrained by S&P Core Logic)
2. Added new Vanguard Total World Stock (VTWSX) data series - extended with MSCI World and FTSE World back to 1970
3. Added new SEC Yield data series in Data_Misc (most starting from 1990): VBMFX, VFITX, VWSTX, VWITX, VWLTX
4. Added a 'pie chart' representation of the asset allocation of the portfolio under study in Analyze_Portfolio
5. Extended the portfolio cycles comparison chart to support standard deviation, Sharpe ratio and correlation metrics (e.g. against US, international, benchmark portfolio) - in addition to the existing CAGR, SWR and PWR metrics
6. Added a new risk ratios comparison chart in Compare_Portfolios

Here is an example of the pie chart:

Image

Here is an example of the portfolio cycles chart (running cycles of 30 years, first one starting in 1970, then 1971, etc):

Image

Here is an example of the risk ratios chart:

Image

jc6309
Posts: 7
Joined: Wed Feb 22, 2017 9:44 am

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

Post by jc6309 » Sat Feb 10, 2018 10:24 am

Siamond
Re today's draft 17b, the pie chart in Analyze Portfolios is not showing properly om my MS office 2007. There's some overprinting towards the bottom and the ASSETS in the title has two exceedingly heavy black bars obscuring it. another artifact of Office 2007?
jc6309

jc6309
Posts: 7
Joined: Wed Feb 22, 2017 9:44 am

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

Post by jc6309 » Sat Feb 10, 2018 10:37 am

Siamond
An addendum
OS is win 10 64 bit
The pie chart shows properly on LibreOffice 6.0

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

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

Post by siamond » Sat Feb 10, 2018 2:08 pm

jc6309 wrote:
Sat Feb 10, 2018 10:24 am
Re today's draft 17b, the pie chart in Analyze Portfolios is not showing properly om my MS office 2007. There's some overprinting towards the bottom and the ASSETS in the title has two exceedingly heavy black bars obscuring it. another artifact of Office 2007?
I am not entirely surprised. The pie chart gave me some grief. It doesn't always display properly on LibreOffice either. I would have much preferred to list the labels on the side, and only show the % inside the pie slices. Unfortunately, I cannot find a way to do that in a fully automated manner (the 'tip' I provided on top of the chart allows to do it, but implies a manual refresh of a filter, which hides some rows, which have then to be restored, can't use that as the default mechanism). Ideas welcome.

EDIT: I figured out that a similar topic was discussed in a separate thread, here is a post where I posted a prototype based on a multi-ring doughnut chart.

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

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

Post by siamond » Sun Feb 11, 2018 12:07 am

Here is an UPDATED draft of version 17b. See revision details here. More feedback would be appreciated over the week-end before I finalize next week.

I reformatted the Asset Allocation pie chart, leveraging on the ideas from the thread listed in the previous post. I think it works much better, including for complicated AAs. The LibreOffice display isn't as neat as Excel, but still usable. Here is an example (click on the image for a larger display).

Image

EDIT: just updated (again) the current draft, to improve the alignment of the outer ring with the inner ring.
Last edited by siamond on Sun Feb 11, 2018 12:12 pm, edited 1 time in total.

amori
Posts: 3
Joined: Fri Feb 09, 2018 4:12 am

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

Post by amori » Sun Feb 11, 2018 5:28 am

Love the new graph.

Also the graph reminds me, I have been using U.S based categories and data as a proxy for Canadian ones (i.e. REITS) -- oh well :D

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

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

Post by siamond » Sun Feb 11, 2018 9:15 am

amori wrote:
Sun Feb 11, 2018 5:28 am
Love the new graph.

Also the graph reminds me, I have been using U.S based categories and data as a proxy for Canadian ones (i.e. REITS) -- oh well :D
I'm glad you like it. There is a mapping table in Data_TR_USD (scroll right towards column CB), which you can easily tweak if you want to make the pie chart look closer to the reality of your situation. This table drives the inner ring of the new pie chart, and (for now) isn't used for anything else.

To answer a similar question I got by e-mail, the factor-based funds (e.g. iShares MTUM, QUAL, etc) are 100% US, so I classified them accordingly. If you are actually viewing them as proxy for more geographically diversified funds, you could either re-classify them as 'others' (like I did by default for gold, energy, health care, etc) or you could be more subtle and make it 0.5 US Stocks and 0.5 Int'l Stocks. This table is used in a SUMPRODUCT() function, so it's fine to use the corresponding entries as weights, as long as they add up to 1.

I reformatted the corresponding entries in blue, with one decimal displayed, to make more clear that this mapping table can be customized. I also refined the mapping of Vanguard Wellington/Wellesley/Total-World to give an example of weights (Wellington is 2/3rd stocks, 1/3rd bonds; Wellesley is the reverse way around; I assumed 50/50 US Stocks vs. Int'l Stocks for Total-World). And I went through a few hoops to make the outer ring align with the inner ring... most of the time!

amori
Posts: 3
Joined: Fri Feb 09, 2018 4:12 am

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

Post by amori » Mon Feb 12, 2018 12:42 am

If you are actually viewing them as proxy for more geographically diversified funds, you could either re-classify them as 'others'
That I shall do. Thanks again.

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

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

Post by siamond » Mon Feb 12, 2018 6:46 pm

Version 17b is now official, please follow the instructions in this post to download it (or just click on this link). Check the few posts above to get an idea about the new charts.

Rev17b:
1a. Updated inflation from BLS with finalized 2017 number (2.11%)
1b. Updated historical data series S&P500(h), IT Bonds(h), T-Bills(h) + Canadian series (Data_Misc) with finalized 2017 numbers
1c. Removed US Home Price Index data series in Data_Misc (starting Feb-18, access to such data is more constrained by S&P Core Logic)
2. Added new Vanguard Total World Stock (VTWSX) data series - extended with MSCI World and FTSE World back to 1970
3. Added new SEC Yield data series in Data_Misc (most starting from 1990): VBMFX, VFITX, VWSTX, VWITX, VWLTX
4. Added a 'pie chart' representation of the asset allocation of the portfolio under study in Analyze_Portfolio
5. Extended the portfolio cycles comparison chart (in Analyze_Portfolio) to support standard deviation, Sharpe ratio and correlation metrics (e.g. against US, international, benchmark portfolio)
6. Added a new risk ratios comparison chart in Compare_Portfolios

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

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

Post by LadyGeek » Mon Feb 12, 2018 8:53 pm

I'm late to respond to your request for review, but consider this suggestion for the next revision.

- Add your blog series to the "README" tab. The "Inner workings" are an integral part of the documentation. From the wiki page: Simba's backtesting spreadsheet
Inner Workings

As any complex spreadsheet, the inner workings of the Simba spreadsheet may not be quite obvious by just looking at formulas. The following series of blog articles documents various aspects of how it actually works.
As noted by several members, I really like the pie charts ("doughnut" charts, asset allocation in the Analyze_Portfolio tab).
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

Rom1b
Posts: 36
Joined: Fri Jan 05, 2018 5:34 am
Contact:

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

Post by Rom1b » Tue Feb 13, 2018 7:16 am

siamond wrote:
Sat Feb 10, 2018 1:05 am
Here is a draft of version 17b. Feedback would be appreciated over the week-end before I finalize next week.
Thanks Siamond !

Really love this version, excellent work, and some major improvements / novelties.
I can recommend to try it out.
siamond wrote:
Sat Feb 10, 2018 1:05 am

Image

Here is an example of the portfolio cycles chart (running cycles of 30 years, first one starting in 1970, then 1971, etc):

Image

Here is an example of the risk ratios chart:

Image
Really like the pie chart, and more importantly the cycles chart over time for many criteria !

Makes nice possibilities to test different asset behaviours and allocations at different time (ie. crisis or bulls). The returns, but also the risks ratios or correlations to US markets over time.

my personal conclusions/notes after playing a while:
-As expected US stocks hard to beat (Doesnt mean one shouldnt diversify of course), still it is for me a reminder to stay 'practical' as a non-US investor;
-Europe and EM (stocks) returns are close, but significantly more volatile; EM last decade+ have not been doing so well (many german books recommend to overweigh EM like 35%..!);
-Pacific fund lower correlated to US than many other funds, so they deserve some attention;
-Commodities vary much, but my surprise was that they have periods (not that short, quite long) where they clealy beat the US stocks in return/risk (not many other assets can..) - of course many periods where they do much worse.
-REITs very good, more than I thought, both for returns and risk ratios.
-And bonds remain one of the best to dilute risk, also as expected but a nice reminder for me (and the baby tiger inside).

Thanks for the hard work Siamond and also your openness to ideas.
Dr Bernstein - If You Can / free PDF, google it | 3Fund Portfolio www.bogleheads.org/forum/viewtopic.php?f=10&t=88005 | CollectiveThought www.bogleheads.org/forum/viewtopic.php?f=10&t=7353

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

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

Post by siamond » Tue Feb 13, 2018 5:16 pm

LadyGeek wrote:
Mon Feb 12, 2018 8:53 pm
- Add your blog series to the "README" tab. The "Inner workings" are an integral part of the documentation.
Fair point, done in my working document of the future v17c.
Rom1b wrote:
Tue Feb 13, 2018 7:16 am
Thanks for the hard work Siamond and also your openness to ideas.
Thanks a lot for the ideas, your contribution proved quite seminal. Keep the ideas coming!

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

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

Post by siamond » Thu Feb 15, 2018 2:24 pm

As discussed in this new thread, I published a blog article providing various Telltale charts, based on the Simba spreadsheet capabilities.

I took the opportunity to also demonstrate a few of the new charts we now have in Simba (using the latest version). Check it out!

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

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

Post by siamond » Fri Feb 23, 2018 9:19 am

I was wondering how many people download this spreadsheet. Sometimes, I have doubts, due to the fairly limited amount of feedback about it (e.g. just a few posters). So I set up a counter associated with the short URL which allows people to download it.

In the past 2 weeks, v17b has been downloaded more than 150 times. Call me impressed. :beer

Rom1b
Posts: 36
Joined: Fri Jan 05, 2018 5:34 am
Contact:

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

Post by Rom1b » Fri Feb 23, 2018 9:24 am

Congrats, Im not surprised, its a great tool. With the new things in the newer version I imagine there will be even more in the next weeks.
Dr Bernstein - If You Can / free PDF, google it | 3Fund Portfolio www.bogleheads.org/forum/viewtopic.php?f=10&t=88005 | CollectiveThought www.bogleheads.org/forum/viewtopic.php?f=10&t=7353

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

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

Post by longinvest » Fri Feb 23, 2018 9:24 am

siamond wrote:
Fri Feb 23, 2018 9:19 am
I was wondering how many people download this spreadsheet. Sometimes, I have doubts, due to the fairly limited amount of feedback about it (e.g. just a few posters). So I set up a counter associated with the short URL which allows people to download it.

In the past 2 weeks, v17b has been downloaded more than 150 times. Call me impressed. :beer
People tend to only give feedback when something is wrong. The low feedback rate is probably a high-quality indicator. :wink:
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

jc6309
Posts: 7
Joined: Wed Feb 22, 2017 9:44 am

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

Post by jc6309 » Fri Feb 23, 2018 1:39 pm

Siamond
Re Hits, you are undercounting. I have used the short form download once and the traditional twice so far. As to the spreadsheet, remembering G.E.P. Box --Essentially all models are wrong, some are useful -- Simba is very useful.
Thanks

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

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

Post by siamond » Fri Feb 23, 2018 5:29 pm

longinvest wrote:
Fri Feb 23, 2018 9:24 am
People tend to only give feedback when something is wrong. The low feedback rate is probably a high-quality indicator. :wink:
That is a nice and comforting thought. Thank you!
jc6309 wrote:
Fri Feb 23, 2018 1:39 pm
As to the spreadsheet, remembering G.E.P. Box --Essentially all models are wrong, some are useful -- Simba is very useful.
I will acknowledge that I didn't understand the reference and this triggered a Google search... This Wiki page helped me get the point! Interesting quote indeed.

PS. 160 clicks on the short link as of today! :wink:

Leesbro63
Posts: 5517
Joined: Mon Nov 08, 2010 4:36 pm

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

Post by Leesbro63 » Fri Feb 23, 2018 5:59 pm

Just curious: Are “Simba” and “Siamond” the same person?

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

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

Post by LadyGeek » Fri Feb 23, 2018 7:18 pm

^^^ No. Simba is the OP, who started the spreadsheet.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

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

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

Post by siamond » Fri Feb 23, 2018 7:29 pm

LadyGeek wrote:
Fri Feb 23, 2018 7:18 pm
^^^ No. Simba is the OP, who started the spreadsheet.
And that was... nearly 11 years ago! Impressive! I was not born by then -- as a Boglehead, I mean... :D :wink:
simba wrote:
Sun May 13, 2007 3:45 pm
The backtesting tool is a spreadsheet to backtest portfolio returns from 1972 to present [1985 to present if you want to include certain Sector/Tax-Exempt funds that started after 1985] of various Vanguard Mutual Funds. This tool is customizable and you can add the returns for any mutual fund.
All credits to Simba (whoever he/she is) for starting this terrific project.

Leesbro63
Posts: 5517
Joined: Mon Nov 08, 2010 4:36 pm

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

Post by Leesbro63 » Fri Feb 23, 2018 10:19 pm

siamond wrote:
Fri Feb 23, 2018 7:29 pm
LadyGeek wrote:
Fri Feb 23, 2018 7:18 pm
^^^ No. Simba is the OP, who started the spreadsheet.
And that was... nearly 11 years ago! Impressive! I was not born by then -- as a Boglehead, I mean... :D :wink:
simba wrote:
Sun May 13, 2007 3:45 pm
The backtesting tool is a spreadsheet to backtest portfolio returns from 1972 to present [1985 to present if you want to include certain Sector/Tax-Exempt funds that started after 1985] of various Vanguard Mutual Funds. This tool is customizable and you can add the returns for any mutual fund.
All credits to Simba (whoever he/she is) for starting this terrific project.
Oh. I was just curious because Simba quit posting about the same time Siamond joined Bogleheads, and the names are similar. And both are very comfortable working on this spreadsheet. I wonder what happened to Simba?

Post Reply