Simba's backtesting spreadsheet [a Bogleheads community project]

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

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

Post by siamond »

calcada wrote: Wed Nov 18, 2020 12:33 pm It would be cool if we could see how correlations between assets changed overtime on a graph.
The Simba spreadsheet only tracks annual returns, which makes correlation math rather coarse (just think about the big March 2020 drawdown we experienced, it will probably not show whatsoever in the annual 2020 return). Computing correlation based on monthly returns would be much more meaningful, in truth. And this would be a much better foundation for historical correlation graphs... But monthly returns are outside the scope of this spreadsheet. Tools like Portfolio Visualizer are better at this kind of analysis.
calcada wrote: Wed Nov 18, 2020 12:33 pm Also Momentum and Value are supposed to have a negative correlation according to this paper: http://pages.stern.nyu.edu/~lpederse/pa ... ywhere.pdf

"Value and momentum are negatively correlated with each other, both within and across asset classes."

The correlation should be around -0.60 yet it is 0.55 for MTUM:Int'l Value and 0.80 for MTUM:US Value on the spreadsheet. What causes this discrepancy?
I assume you meant iShares MTUM vs. iShares VLUE (and the MSCI indices allowing to extend corresponding history back in time). Yes, I heard Cliff Asness make this claim multiple times, but this is definitely NOT what the corresponding MSCI indices displayed in the past 40-ish years.

I was wondering if this was a side-effect of annual vs monthly returns, so I just ran a quick test with monthly values since 1982 and... same results. Then I varied start/end dates and... same results.

I suspect Cliff assembled a rather theoretical model which might be a tad disconnected from reality. Or he used definitions of value and momentum which are very different from MSCI definitions. Not sure. Could be a good topic of discussion, maybe open a dedicated thread for this?
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

For those of you who are itching to see 2020 returns, here is a DRAFT of the v20a update and a couple of new (minor) features. Please remember that last year's inflation rate is just a coarse estimate at this point in time.

http://bit.ly/3b4d0B1

Please note that the official v20a release will be markedly different as:
1. I plan to reorder the list of funds / data_series (as visible in Analyze_Series first column, etc)
2. we're working on a significant update of the bond fund simulator, leveraging new historical sources and using biannual data points

So... until you have a 'early user' personality, better wait a little longer! This being said, I would welcome feedback from early users...
edge
Posts: 3833
Joined: Mon Feb 19, 2007 6:44 pm
Location: NY

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

Post by edge »

Thanks!!
ivan.deryabin
Posts: 10
Joined: Thu Jan 07, 2021 2:04 am

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

Post by ivan.deryabin »

Hello!
Thank you for the fantastic table!
I am studying how it works. I do not understand what are the figures in Row data sheet. Changes of indexes normalised to current value? There is nothing in readme adout it.
For example cell x157 is 20.99 - what is this? change absolute or %?
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

ivan.deryabin wrote: Thu Jan 07, 2021 2:33 am Hello!
Thank you for the fantastic table!
I am studying how it works. I do not understand what are the figures in Row data sheet. Changes of indexes normalised to current value? There is nothing in readme adout it.
For example cell x157 is 20.99 - what is this? change absolute or %?
Welcome. It means a 20.99% total return (dividends/distributions included and reinvested) for the corresponding year.

When using a Total Return index, then it's equal to (R2/R1)-1, where R1 is the index value on Dec 31st of the previous and R2 is the index on Dec 31st of the current year. Well, times 100.
ivan.deryabin
Posts: 10
Joined: Thu Jan 07, 2021 2:04 am

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

Post by ivan.deryabin »

Ok. Thank you.
Also i am trying to recalculate site https://portfoliocharts.com/
And input the same portfolio weights to the spreadsheet.
And i do not understand what parameter you take to https://portfoliocharts.com/portfolio/portfolio-matrix/ matrix as long term aseline rate. How find it in the table and compare results.
Also I calculated All seasons portfolio in the table and see that its IRR higher then it should be,
User avatar
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

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

Post by Tyler9000 »

ivan.deryabin wrote: Thu Jan 07, 2021 4:35 pm Ok. Thank you.
Also i am trying to recalculate site https://portfoliocharts.com/
And input the same portfolio weights to the spreadsheet.
And i do not understand what parameter you take to https://portfoliocharts.com/portfolio/portfolio-matrix/ matrix as long term aseline rate. How find it in the table and compare results.
Also I calculated All seasons portfolio in the table and see that its IRR higher then it should be,
While Siamond and I share a passion for good data, Portfolio Charts is my thing and is separate from his great work with the Simba Spreadsheet. To not distract from the discussion here, I'll be happy to answer any of your questions about Portfolio Charts calculations via PM.
ivan.deryabin
Posts: 10
Joined: Thu Jan 07, 2021 2:04 am

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

Post by ivan.deryabin »

Ok. Thank you all
Please excuse my knowledge from completely forgotten the CFA preparation course.
I want to combine Compare Portfolio with the consensus forecast from Bloomberg.
And make a weighted comparison between historical ratios (from 1970), 2020 ratios negative (overvalued), and consensus forecast.

Also i Have a table of consensus forecast 2021. - https://docs.google.com/spreadsheets/d/ ... sp=sharing
This forecast is only for 3 instruments:
Instrument Consensus value 2021 Forecast contributors (banks) Current value Potrfolio inpact, % of portfolio
10 Year Treasury Rate 1,26% 10 0,96% -3,00%
Gold 2 185 4 1954 11,82%
S&P 500 4 031 7 3726 8,18%

Can ai use your correlation ratios to spread this forecast to all other groups of instruments? Just multiply correlation and Forecast?

Does it have any sense?
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

ivan.deryabin wrote: Fri Jan 08, 2021 10:49 ami Have a table of consensus forecast 2021. - https://docs.google.com/spreadsheets/d/ ... sp=sharing
Ivan, you should grant read-only access to all viewers if you want us to be able to easily access a Google Sheet.
ivan.deryabin wrote: Fri Jan 08, 2021 10:49 amCan ai use your correlation ratios to spread this forecast to all other groups of instruments? Just multiply correlation and Forecast?

Does it have any sense?
Hm. I am afraid not. Multiplying the past correlation ratio between two data series (e.g. annual returns) by a specific forecast is just mixing apples and oranges, it won't give you anything meaningful. Typical forecasts (or expected returns models) are usually based on a combination of past earnings, dividends and current prices for a given market segment. Each market segment is fairly independent from each other in terms of earnings (and dividends). I don't see how one can use the forecast for a given market segment to infer a forecast for another market segment.

Maybe check Research Associates to find forecasts for a whole area of market segments?
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Here is the first official update for this brave new year. Please download the corresponding (Excel) spreadsheet by clicking here: https://bit.ly/39lYtxR.
=> Feedback welcome. I'll wait a few more days to update the wiki page, just in case some further tweaking is needed.
=> Careful, a few 2020 numbers are still approximate, notably inflation.

Rev20a
1a. Added 2020 annual returns: funds and index returns updated; Expense Ratios (ER) updated
1b. Inflation is a rough estimate for now; updates for some synthetic models are pending
2. REORDERED the main data series in Data_Series and dependent tabs (Analyze_Portfolio, etc). Clarified a couple of names.
3. Added new stats per individual data series (components of the custom portfolio) in Analyze_Portfolio, e.g. CAGR, standard deviation, correlation
4a. Performance optimization: replaced all uses of OFFSET() volatile function by non-volative INDEX() equivalent in Portfolio_Math, Analyze_Series, Data_Series
4b. Performance optimization: replaced most uses of OFFSET() volatile function by non-volative INDEX() equivalent in Analyze_Portfolio, Compare_Portfolios, Lazy_Portfolios
5a. Added 1871-2015 series tracking the price of gold in Raw_Data to complement LBMA data; deactivated by default, but useful for historical analysis of pre-1970 vs post-1970 periods.
5b. Added 1974+ series tracking MSCI World Momentum GR USD, to complement MSCI USA Momentum (1982+); deactivated by default, but useful for historical research on momentum factor.
6. Updated 1871-1933 T-Bills returns, reconstructing Prof. Shiller 'R' series from updated commercial paper rates from FRED/NBER.
7. Refreshed full history for Vanguard VFSAX, VTWAX, VHYAX admiral funds (overriding lingering returns from corresponding investor funds)
8a. Updated all returns derived from Longinvest's bond fund model to use the latest version (v2.2), leveraging more extensive historical yields than before and a biannual simulation logic.
8b. LT STRIPS data series updated based on Longinvest's bond fund EqPar Zero 30-20 model till 1996, to better match the Bloomberg Barclays 20-30Y Treasury Strips index benchmark
ivan.deryabin
Posts: 10
Joined: Thu Jan 07, 2021 2:04 am

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

Post by ivan.deryabin »

Thank you great.
Does this address is the same for all versions?
Or are you planning to create a datasourse from it?

I am trying also to combine historical performance data with current year online data from googlefinance. And create a dashboard to calculate expected returns.

And found that a lot of quotes from Google finance formula show wrong data. May be funds tikers mix with stocks?
For example i have (GOOGLEFINANCE(VTMGX;"returnytd") which show - 9%, but the chart is positive.
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

ivan.deryabin wrote: Thu Jan 14, 2021 7:50 am Thank you great.
Does this address is the same for all versions?
Or are you planning to create a datasourse from it?
The link I provided in the previous post is only for the last version, but that's all you need. Older versions are obsolete now. No plan to provide the data in any other form, for now.
ivan.deryabin wrote: Thu Jan 14, 2021 7:50 am I am trying also to combine historical performance data with current year online data from googlefinance. And create a dashboard to calculate expected returns.

And found that a lot of quotes from Google finance formula show wrong data. May be funds tickers mix with stocks?
For example i have (GOOGLEFINANCE(VTMGX;"returnytd") which show - 9%, but the chart is positive.
GoogleFinance() isn't always terribly reliable, notably the "returnytd" type of query. Don't know why. I think there are other threads on the forum discussing GoogleFinance().
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

I updated the US inflation rate with the official 2020 value from the U.S. Bureau of Labor Statistics (1.36%, December to December), same link, see post above.

And I updated the wiki. As usual, a few (rarely used) numbers from last year are still missing and will be updated later.
User avatar
Leif
Posts: 3705
Joined: Wed Sep 19, 2007 4:15 pm

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

Post by Leif »

Thanks for the update Siamond.

For those that want the CPI-U inflation data see below.

Image

2020 inflation Dec-Dec (260.474-256.974)/256.974 = 1.36%
ivan.deryabin
Posts: 10
Joined: Thu Jan 07, 2021 2:04 am

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

Post by ivan.deryabin »

Hello Everyone.
Also, I am confused about the Commodity index trust GSG which represents commodity prices in the Simba table.
I opened the portfolio of this fund and it is full of cash and treasury bills. 99% of cash.
Which commodity does it represent?
I see some food commodity up 10% in 2020
Any other options for better representation?

Also what do you think about testing a ESG Etf in the portfolio? (ESG Investing is the consideration of environmental, social and governance factors )
Do you know any good big ETF on it?
Last edited by ivan.deryabin on Mon Jan 18, 2021 9:17 am, edited 1 time in total.
Barsoom
Posts: 728
Joined: Thu Dec 06, 2018 8:40 am

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

Post by Barsoom »

A note to all:

I started reading this thread from the beginning, and noticed that BH member Gummy (Peter Ponzo) was instrumental in the early formation of this spreadsheet.

I googled him and saw that he passed away in July 2020.

Here is a link to the University of Waterloo remembrance: We remember Professor Emeritus Peter Ponzo.

Here is a link to his obituary: Peter J. Ponzo (1934 - 2020).

I just thought that people would want to know.

-B
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

ivan.deryabin wrote: Mon Jan 18, 2021 5:50 am Hello Everyone.
Also, I am confused about the Commodity index trust GSG which represents commodity prices in the Simba table.
I opened the portfolio of this fund and it is full of cash and treasury bills. 99% of cash.
Which commodity does it represent?
I see some food commodity up 10% in 2020
Any other options for better representation?
As the GSG page indicates, "The iShares S&P GSCI Commodity-Indexed Trust (the 'Trust') seeks to track the results of a fully collateralized investment in futures contracts on an index composed of a diversified group of commodities futures.". It is a rather peculiar construct indeed, but this is what this data series is about, commodity futures. Now why are commodities typically invested as futures (hence a lot of borrowing, hence a lot of cash), I am not quite sure. If you'd like to initiate a more detailed discussion, let me suggest you post in this thread.

PS. I clarified the name of the series in my working version, for the next update.
ivan.deryabin wrote: Mon Jan 18, 2021 5:50 am Also what do you think about testing a ESG Etf in the portfolio? (ESG Investing is the consideration of environmental, social and governance factors )
Do you know any good big ETF on it?
Vanguard FTSE Social Index Fund Admiral Shares (VFTAX) would be a good one. I included its historical annual returns in Raw_Data, but didn't reference it in the other tabs, as its history is too limited and there is no good proxy (e.g. index) for previous years. Note that I also included its polar opposite, the aptly named USA Vice fund (VICEX).
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

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

Post by serbeer »

Barsoom wrote: Mon Jan 18, 2021 6:14 am A note to all:

I started reading this thread from the beginning, and noticed that BH member Gummy (Peter Ponzo) was instrumental in the early formation of this spreadsheet.

I googled him and saw that he passed away in July 2020.

Here is a link to the University of Waterloo remembrance: We remember Professor Emeritus Peter Ponzo.

Here is a link to his obituary: Peter J. Ponzo (1934 - 2020).

I just thought that people would want to know.

-B
Thank you Barsoom. Yes, Peter was instrumental -- and also very smart, helpful and sharing.
One can still benefit greatly from his excellent and detailed hands-on tutorials on Excel, statistics, and math. They can be found at:
https://www.financialwisdomforum.org/gu ... _stuff.htm
I noticed that the site no longer appears to allows to download everything in a single zip archive file, asking for login. Probably due to its size, it is 140Mb compressed. But all the materials appear to still be online and available through the website, including the spreadsheets (I perused a lot of his materials myself while building my personal AA and money management Excel-based system).
RIP
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Here is a very sentimental gift for Valentine's Day... The very latest Simba backtesting spreadsheet! :mrgreen:

Download here, feedback welcome:
https://bit.ly/2NvyAEQ

Rev20b
1. Simba 2020 update fully completed with following steps:
1a. Updated inflation numbers (US and Canada) with official 2020 data.
1b. Double-checked all fund/index returns for last year, including Canada.
1c. Updated Stock Calculator data with Tyler's latest numbers derived from French-Fama library. Settled on CRSP breakpoints (Large=0-85; Mid=70-85; Small=85-98; Value/Growth=50/50).
1d. Updated Shiller S&P 500 TR/PR/CPI with latest. Updated T-Bills with latest derived from FRED TB3MS.
2. Improved naming of commodities data series: Commodity Futures; added Pimco PCRIX historical returns for reference, but iShares GSG remains used by default.
3. Added for reference in Raw_Data: FTSE Canada All Cap Domestic TR CAD series (new benchmark for VCN)
User avatar
cos
Posts: 506
Joined: Fri Aug 23, 2019 7:34 pm
Location: Boston
Contact:

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

Post by cos »

siamond wrote: Sun Feb 14, 2021 11:13 am Here is a very sentimental gift for Valentine's Day... The very latest Simba backtesting spreadsheet! :mrgreen:

Download here, feedback welcome:
https://bit.ly/2NvyAEQ

Rev20b
1. Simba 2020 update fully completed with following steps:
1a. Updated inflation numbers (US and Canada) with official 2020 data.
1b. Double-checked all fund/index returns for last year, including Canada.
1c. Updated Stock Calculator data with Tyler's latest numbers derived from French-Fama library. Settled on CRSP breakpoints (Large=0-85; Mid=70-85; Small=85-98; Value/Growth=50/50).
1d. Updated Shiller S&P 500 TR/PR/CPI with latest. Updated T-Bills with latest derived from FRED TB3MS.
2. Improved naming of commodities data series: Commodity Futures; added Pimco PCRIX historical returns for reference, but iShares GSG remains used by default.
3. Added for reference in Raw_Data: FTSE Canada All Cap Domestic TR CAD series (new benchmark for VCN)
As always, thank you so much! Happy Valentine's Day! :beer
User avatar
corn18
Posts: 2867
Joined: Fri May 22, 2015 6:24 am

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

Post by corn18 »

Just wanted to stop in and say a great big thank you for this spreadsheet. I use the historical return data to feed my own historical model and my Monte Carlo model. Without this data, it would be impossible. Thanks!
Consistently sets low goals and fails to achieve them.
HansT
Posts: 57
Joined: Wed Feb 17, 2021 6:37 pm

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

Post by HansT »

I wanted to explore this for decumulation/retirement purposes -- specifically comparing portfolios over a long back test history. The predefined drawdown metrics suffer a few shortcomings (as I understand them -- corrections welcome!) See also the image below for an illustration of these.

1. Max Withdrawal Rate doesn't control for span of the backtest, so shorter testing periods are naturally biased high. In the image, you can see that the first and second results columns illustrate this. (These are median MWR and PWR for the 25 lazy portfolios, where the back test period beginning is the row label.) The MWR values drift higher as the back test period shortens -- as the principal amount is distributed over a shorter time period.

2. For back test periods before the portfolio was defined (e.g., before 1930 for Wellington), I believe Portfolio_Math sheet assumes 0% return. This penalizes portfolios when back tested before they began. So I created a "filtered" PWR which only begins when a portfolio is defined. This is the third column in the image. You can see that while PWRs are identical after 1985 (the latest portfolio start date), but before this date the PWR (across all portfolios) is inaccurately biased down -- because an increasing number of portfolios begin their history with 0% returns when you test before 1985. The "filtered Perp WR" column removes this bias, and you can see that e.g. the prewar ~3% rates are back up in the ~5% range.

3. I didn't fully understand PWR/cycles, but included that (unfiltered) for reference. These rates correlate 91% with the PWR values, so I feel comfortable focusing on PWR values instead.

Image

So: if I'm back testing my portfolios for decumulation, is this (a filtered PWR) a robust way to compare portfolios?
Please donate to support BH! https://bogleheads.org/support.php
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

HansT wrote: Sun Mar 07, 2021 4:28 pm1. Max Withdrawal Rate doesn't control for span of the backtest, so shorter testing periods are naturally biased high. In the image, you can see that the first and second results columns illustrate this. (These are median MWR and PWR for the 25 lazy portfolios, where the back test period beginning is the row label.) The MWR values drift higher as the back test period shortens -- as the principal amount is distributed over a shorter time period.
So I assume that you kept the end year constant in your test? While varying the start year? Then yes, sure, there will be a drift for shorter time periods, for the reason you explained. This doesn't seem to be a terribly meaningful test though, nobody will retire in 1926 and live until 2020. It would probably make more sense to make the end year a function of the start year, e.g. set cell E86 to "=min(EndYear, E85+30-1)", i.e. a retirement period capped to 30 years.
HansT wrote: Sun Mar 07, 2021 4:28 pm2. For back test periods before the portfolio was defined (e.g., before 1930 for Wellington), I believe Portfolio_Math sheet assumes 0% return. This penalizes portfolios when back tested before they began. So I created a "filtered" PWR which only begins when a portfolio is defined. This is the third column in the image. You can see that while PWRs are identical after 1985 (the latest portfolio start date), but before this date the PWR (across all portfolios) is inaccurately biased down -- because an increasing number of portfolios begin their history with 0% returns when you test before 1985. The "filtered Perp WR" column removes this bias, and you can see that e.g. the prewar ~3% rates are back up in the ~5% range.
When you customize portfolios or change the starting year of the backtest, there is a warning system in column D, the conditional formatting will turn bright red for every data series which doesn't have historical data starting early enough for one of those portfolios being analyzed. If you ignore this warning, then yes, the calculations in Portfolio_Math equate an empty cell to a zero and 0% returns are factored in the math (it's really a side-effect instead of an intended effect, the assumption being that the warning system in column D is not ignored!).
HansT wrote: Sun Mar 07, 2021 4:28 pm3. I didn't fully understand PWR/cycles, but included that (unfiltered) for reference. These rates correlate 91% with the PWR values, so I feel comfortable focusing on PWR values instead.
[...]
So: if I'm back testing my portfolios for decumulation, is this (a filtered PWR) a robust way to compare portfolios?
Let me explain the PWR/cycles stats in Compare_Portfolios and Lazy_Portfolios. I suspect this is what you're looking for. Say the time period is 1970 to 2020 (start year; end year). Say cell E89 keeps its default value (30). Cycles of 30 years within this time period can start in 1970 or 1971... or 1991. But not in 1992, because 1992-2020 is a 29 years cycle. The math in Portfolio_Math will compute the PWR (and SWR) for all those cycles of 30 years which fit in the time period. That's 21 cycles in this precise example.

Then cell D127 (for PWR) will be used and the list of 21 PWRs (one per cycle) will be subject to a percentile function. If cell D127 remains at 5%, the result (in row 127) will be the 5% cut-off of the cycles. In other words, you have a 5% chance of a [backtested] cycle ending under such value. Which is typically viewed as 'safe'. If you're looking for a median value instead of a 'safe' value, set cell D127 to 50%.

Also note that in Analyze_Portfolio, there is a cool chart (around row 272) allowing you to visualize the same per-cycle results for each cycle (vertical axis is the initial year of the cycle), while comparing a portfolio to a benchmark portfolio. I personally find such chart to be more informative (and intuitive) than the classic percentile math.
HansT
Posts: 57
Joined: Wed Feb 17, 2021 6:37 pm

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

Post by HansT »

siamond wrote: Sun Mar 07, 2021 10:36 pm So I assume that you kept the end year constant in your test? While varying the start year? Then yes, sure, there will be a drift for shorter time periods, for the reason you explained. This doesn't seem to be a terribly meaningful test though, nobody will retire in 1926 and live until 2020.
I wanted a test of a portfolio's performance not over a single 30y span, but over as long a history as I can get for that portfolio. It would be great if we could back test all portfolios since 1871 (since we don't know what the future holds). Just pointing out for other users--consider this a PSA--that if this is their need, they should use PWR instead of MWR. No criticism intended.
siamond wrote: Sun Mar 07, 2021 10:36 pm When you customize portfolios or change the starting year of the backtest, there is a warning system in column D, the conditional formatting will turn bright red for every data series which doesn't have historical data
Here I think the naive user could be assisted. Col D indicates when a data series shouldn't be tested (or should be tested with caution), but I think the casual user wants to know when a portfolio shouldn't be tested. Here's a suggestion:

Code: Select all

set cell Lazy_Portfolios!E90 to {= E$85 >= MAX(IF(ISNUMBER(E$5:E$81),$D$5:$D$81,0))} 
and drag.  (Yes that's an array formula.)  
Then we can calculate whether the portfolio is "valid" for testing since "Starting Year for backtest":

Image
siamond wrote: Sun Mar 07, 2021 10:36 pm Let me explain the PWR/cycles stats in Compare_Portfolios and Lazy_Portfolios [...]

Also note that in Analyze_Portfolio, there is a cool chart (around row 272) allowing you to visualize the same per-cycle results for each cycle (vertical axis is the initial year of the cycle), while comparing a portfolio to a benchmark portfolio. I personally find such chart to be more informative (and intuitive) than the classic percentile math.
Thank you very much for that explanation!!!
Please donate to support BH! https://bogleheads.org/support.php
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

HansT wrote: Tue Mar 09, 2021 8:23 am I wanted a test of a portfolio's performance not over a single 30y span, but over as long a history as I can get for that portfolio. It would be great if we could back test all portfolios since 1871 (since we don't know what the future holds). Just pointing out for other users--consider this a PSA--that if this is their need, they should use PWR instead of MWR. No criticism intended.
Historical data is unfortunately HARD to get. What's in the spreadsheet represents a lot of research. If you stumble upon some new data source allowing to go further back in history, do tell! And yes, I like the PWR metric too, but most people are more fond of other metrics, so the spreadsheet tries to be fairly comprehensive and non-judgmental.
HansT wrote: Tue Mar 09, 2021 8:23 am
siamond wrote: Sun Mar 07, 2021 10:36 pm When you customize portfolios or change the starting year of the backtest, there is a warning system in column D, the conditional formatting will turn bright red for every data series which doesn't have historical data
Here I think the naive user could be assisted. Col D indicates when a data series shouldn't be tested (or should be tested with caution), but I think the casual user wants to know when a portfolio shouldn't be tested. Here's a suggestion [...]
For Analyze_Portfolio and Compare_Portfolios, column D should be good enough as it only applies to the data series actually used for (a small number of) tested portfolios, but yes, in the context of Lazy_Portfolios, it becomes too coarse, you're right. Ok, I'll add something like that in the next version, thanks for the feedback, appreciated.
seajay
Posts: 1656
Joined: Sat May 01, 2021 3:26 pm
Contact:

Addition of Precious Metals data since 1871 for Simba's backtesting spreadsheet

Post by seajay »

Precious metals data back to 1871 :

Pre 1933 and money/gold were convertible, under such situation a reasonable choice would have been to hold money deposited with the treasury to earn some interest (T-Bills) that at any time could be liquidated into gold. Twas like the treasury paying you for it to securely store your gold.

.... so in Simba's spreadsheet I filled in values for Precious Metals to be the same as T-Bills for 1871 to 1933

As in the US investment gold holding/trading was prohibited between 1933 and 1975 a investor who might otherwise have held gold might have opted for silver instead. To source such silver price change data I used
https://www.usgs.gov/centers/nmic/histo ... ted-states to enter into the spreadsheet

For the remaining years, more recent decades ... they were already populated with gold price change values.

So now I have a version where I can backtest PM mixed with other assets etc. since 1871 :D

Perhaps those that maintain the spreadsheet might like to consider making the same change to the official version.

===============================
PS

In the UK the first ever Investment Trust (a stock/company whose primary business is trading in stocks/bonds/investments) Foreign and Colonial started in the second half of the 19th Century. Back then inflation broadly averaged 0%, but in a volatile manner with overall equal amounts of inflation and deflation, sometimes quite high levels. The more common choice of investment was bonds. Foreign and Colonial held a diverse range of global bonds and very much was like a form of World type stock index. Towards the end of the 19th century and into the 20th century it transitioned over to migrating away from bonds and into stocks and where in more recent decades it might be considered a form of World Stock Index and where its historical price gains/losses could conceptually be used as a candidate as the source for a global stock index. I do have data for F&C from 1880 that are in British Pounds, that with Pound/Dollar currency conversion values could be converted to US$ based values (for historic Pound/Dollar : https://www.measuringworth.com/datasets/exchangepound/ ) that might be used to in-fill pre 1970 years data for 'Global Stocks' in the spreadsheet.

"World Stock" in British Pound based yearly nominal total returns ..

Code: Select all

1880	9.72
1881	3.53
1882	4.07
1883	7.32
1884	5.87
1885	11.92
1886	3.59
1887	10.42
1888	4.80
1889	1.34
1890	-7.88
1891	4.28
1892	-0.41
1893	7.40
1894	6.02
1895	5.65
1896	16.70
1897	6.80
1898	3.80
1899	3.70
1900	14.60
1901	-3.10
1902	6.70
1903	1.90
1904	12.00
1905	3.90
1906	10.80
1907	0.40
1908	4.90
1909	10.60
1910	2.10
1911	1.50
1912	3.00
1913	-3.20
1914	-0.30
1915	2.60
1916	-1.60
1917	9.00
1918	20.30
1919	9.70
1920	-19.50
1921	-0.40
1922	25.00
1923	1.30
1924	19.60
1925	14.20
1926	6.60
1927	8.20
1928	16.10
1929	-13.60
1930	-3.80
1931	-18.50
1932	34.23
1933	24.41
1934	14.05
1935	13.99
1936	19.20
1937	-13.04
1938	-9.79
1939	1.83
1940	-4.89
1941	22.73
1942	18.44
1943	11.04
1944	12.45
1945	6.46
1946	17.73
1947	-2.58
1948	-3.68
1949	-5.70
1950	10.96
1951	8.23
1952	-0.14
1953	24.43
1954	48.62
1955	10.86
1956	-9.16
1957	-0.95
1958	47.74
1959	54.86
1960	1.84
1961	1.81
1962	0.38
1963	19.81
1964	-5.41
1965	11.17
1966	-3.84
1967	34.19
1968	48.20
1969	-11.87
seajay
Posts: 1656
Joined: Sat May 01, 2021 3:26 pm
Contact:

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

Post by seajay »

With my previous post data entered, testing 50/50 Total US market/Total Bond against 75/25 World Stock/Precious Metal since 1880 (thumbnail image, click to enlarge)

Image

A 50/50 barbell of stock/gold might be considered a two polar opposites that combine to a central bullet, somewhat like how a 50/50 barbell of short (1 year) and long (20 year) dated treasury combine to a central 10 year bond bullet, such that 75/25 stock/gold might be considered as comparable to 50/50 stock/bond.

Interesting to see how that comparison might have panned out since 1880

Gold in paying no dividends/interest potentially reduced taxation risk.

75/25 Berkshire Hathaway/Gold ... zero dividends/income. Assuming BRK and US stock broadly compared then PV provides a nice view of the comparison since 1987 to 50/50 US/TBM
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

Re: Addition of Precious Metals data since 1871 for Simba's backtesting spreadsheet

Post by siamond »

seajay wrote: Mon Jun 21, 2021 8:32 am Precious metals data back to 1871 : [...]
Perhaps those that maintain the spreadsheet might like to consider making the same change to the official version.
Hi there. I am the volunteer maintaining the Simba spreadsheet. I try to keep the data series as consistent as possible, doing something easily matching one's intuition. Although I understand where you're coming from, mixing T-bills, silver and gold prices in one data series would seem a little too much at odds with consistency and intuitiveness principles.

Hopefully, you've figured out that the process of 'splicing' data sources into a given data series is easy to customize in the 'Raw_Data' tab, for your own needs. It is also easy to create a new data series based on various data sources and reference it in the rest of the spreadsheet. This should make implementing your idea fairly simple once you get the hang of it (and easy to restore when a new version of the 'official' spreadsheet is issued). I am happy to answer questions in this respect.
seajay wrote: Mon Jun 21, 2021 8:32 amI do have data for F&C from 1880 that are in British Pounds, that with Pound/Dollar currency conversion values could be converted to US$ based values [...] that might be used to in-fill pre 1970 years data for 'Global Stocks' in the spreadsheet.
This is an intriguing idea. Could you please share the data source for Foreign and Colonial historical data?
Alpha4
Posts: 176
Joined: Tue Apr 17, 2012 8:47 pm

Re: Addition of Precious Metals data since 1871 for Simba's backtesting spreadsheet

Post by Alpha4 »

seajay wrote: Mon Jun 21, 2021 8:32 am Precious metals data back to 1871 :

Pre 1933 and money/gold were convertible, under such situation a reasonable choice would have been to hold money deposited with the treasury to earn some interest (T-Bills) that at any time could be liquidated into gold. Twas like the treasury paying you for it to securely store your gold.

.... so in Simba's spreadsheet I filled in values for Precious Metals to be the same as T-Bills for 1871 to 1933

As in the US investment gold holding/trading was prohibited between 1933 and 1975 a investor who might otherwise have held gold might have opted for silver instead. To source such silver price change data I used
https://www.usgs.gov/centers/nmic/histo ... ted-states to enter into the spreadsheet

For the remaining years, more recent decades ... they were already populated with gold price change values.

So now I have a version where I can backtest PM mixed with other assets etc. since 1871 :D

Perhaps those that maintain the spreadsheet might like to consider making the same change to the official version.

===============================
PS

In the UK the first ever Investment Trust (a stock/company whose primary business is trading in stocks/bonds/investments) Foreign and Colonial started in the second half of the 19th Century. Back then inflation broadly averaged 0%, but in a volatile manner with overall equal amounts of inflation and deflation, sometimes quite high levels. The more common choice of investment was bonds. Foreign and Colonial held a diverse range of global bonds and very much was like a form of World type stock index. Towards the end of the 19th century and into the 20th century it transitioned over to migrating away from bonds and into stocks and where in more recent decades it might be considered a form of World Stock Index and where its historical price gains/losses could conceptually be used as a candidate as the source for a global stock index. I do have data for F&C from 1880 that are in British Pounds, that with Pound/Dollar currency conversion values could be converted to US$ based values (for historic Pound/Dollar : https://www.measuringworth.com/datasets/exchangepound/ ) that might be used to in-fill pre 1970 years data for 'Global Stocks' in the spreadsheet.

"World Stock" in British Pound based yearly nominal total returns ..

Code: Select all

1880	9.72
1881	3.53
1882	4.07
1883	7.32
1884	5.87
1885	11.92
1886	3.59
1887	10.42
1888	4.80
1889	1.34
1890	-7.88
1891	4.28
1892	-0.41
1893	7.40
1894	6.02
1895	5.65
1896	16.70
1897	6.80
1898	3.80
1899	3.70
1900	14.60
1901	-3.10
1902	6.70
1903	1.90
1904	12.00
1905	3.90
1906	10.80
1907	0.40
1908	4.90
1909	10.60
1910	2.10
1911	1.50
1912	3.00
1913	-3.20
1914	-0.30
1915	2.60
1916	-1.60
1917	9.00
1918	20.30
1919	9.70
1920	-19.50
1921	-0.40
1922	25.00
1923	1.30
1924	19.60
1925	14.20
1926	6.60
1927	8.20
1928	16.10
1929	-13.60
1930	-3.80
1931	-18.50
1932	34.23
1933	24.41
1934	14.05
1935	13.99
1936	19.20
1937	-13.04
1938	-9.79
1939	1.83
1940	-4.89
1941	22.73
1942	18.44
1943	11.04
1944	12.45
1945	6.46
1946	17.73
1947	-2.58
1948	-3.68
1949	-5.70
1950	10.96
1951	8.23
1952	-0.14
1953	24.43
1954	48.62
1955	10.86
1956	-9.16
1957	-0.95
1958	47.74
1959	54.86
1960	1.84
1961	1.81
1962	0.38
1963	19.81
1964	-5.41
1965	11.17
1966	-3.84
1967	34.19
1968	48.20
1969	-11.87
Hi Seajay,

Very interesting find!

The only potential issue/s I see with using the Foreign and Colonial data is:

1. Is it TR or price only (hopefully TR)?

2. Is it annually, quarterly, or monthly (the latter two would be ideal but annually would still be better than what we have now)

3. It is actively managed vs a broad index so we don't know how much active management added/subtracted to returns over the long run and/or by making the return in any given year much better/worse than a broad world stock market index. For instance, 1946 was a slightly bad year for US stocks, a bad year for ex-US developed market stocks overall (roughly equivalent to EAFE+Canada), and a very bad year for EM stocks....but the Foreign and Colonial data shows a gain of over 16%. Was it mostly invested only in British equities that year (because as per the Barclay's Equity Gilt Study historical data, British stocks had a return--in GBP--of a bit over 17% when you count price appreciation plus reinvested dividends)?

4. After 1900 did it still hold a percent in bonds and if so, how much in each year did it hold in bonds and when did it finally transition to being a purely stock-containing investment trust?

5. Is it priced in GBP pounds sterling? How far back do we have daily/monthly data for converting pounds to USD? FRED only goes back to 1970 or 1971 and FXTop only goes back to the early 1950s.

I have another idea for simulating foreign ex-US stock data (since we already have decent US data) for the years before 1970; it will go back at least to the mid-1950s and maybe back to the mid-1920s. Would you (and/or any other board members....they can please feel free to chime in as well) be interested in hearing about it? Once we have said foreign stock data we can combine it with US stock data to calculate a total world stock index for the relevant years before 1970.
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

A fascinating new thread on pre-1970 historical asset returns:
viewtopic.php?f=10&t=353607
skierincolorado
Posts: 2377
Joined: Sat Mar 21, 2020 10:56 am

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

Post by skierincolorado »

siamond wrote: Thu Jul 15, 2021 4:27 pm A fascinating new thread on pre-1970 historical asset returns:
viewtopic.php?f=10&t=353607
Do you know why the ITT returns in the spreadsheet are very different from Portfolio Visualizer? Not just the actual returns which could be due to using a slightly different duration, but the risk-adjusted returns are much worse. For STT on the other hand, they are nearly identical. It has major implications for calculating the efficient frontier. With PV the sharpe ratio of TSM+ITT is very very similar to the sharpe ratio of TSM+STT. With this spreadsheet, TSM+STT has a much better sharpe ratio than TSM+ITT.
User avatar
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

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

Post by Tyler9000 »

skierincolorado wrote: Thu Sep 16, 2021 4:07 pm Do you know why the ITT returns in the spreadsheet are very different from Portfolio Visualizer?
First, be sure you're comparing apples-to-apples on timeframe. When using the "Backtest Portfolio" tool using tickers, PV automatically crops the data to the earliest year of the fund. The more comparable data to Simba is the "Backtest Asset Allocation" tool.

Assuming that's not the issue and you're comparing the annual returns side-by-side, there are definitely some differences on sources. According to the Portfolio Visualizer FAQ, when Vanguard fund data is not available they calculate the returns using the following data for treasury funds:

STT: FRED Interest Rate Data (2-year maturity) 1977-1991
ITT: FRED Interest Rate Data (5-year maturity) 1972-1991
LTT: FRED Interest Rate Data (30-year maturity) 1978-1986

The difference between the sources above and the Simba method is that PV uses bonds of fixed maturities while Simba models bond ladders within the desired maturity range. IMO, the Simba data is better because it more closely follows the methodology of real-world bond funds.

Along those lines, the reason that you're seeing less difference with STT bonds compared to the intermediate and long varieties is that the 2-year fixed maturity is reasonably close to the real-life range between 1-3 years. But simplifying 3-10 year ITTs to a fixed 5 years has greater error, and simplifying 10-30-year LTTs to a fixed 30 years has even more.
skierincolorado
Posts: 2377
Joined: Sat Mar 21, 2020 10:56 am

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

Post by skierincolorado »

Tyler9000 wrote: Thu Sep 16, 2021 4:45 pm
skierincolorado wrote: Thu Sep 16, 2021 4:07 pm Do you know why the ITT returns in the spreadsheet are very different from Portfolio Visualizer?
First, be sure you're comparing apples-to-apples on timeframe. When using the "Backtest Portfolio" tool using tickers, PV automatically crops the data to the earliest year of the fund. The more comparable data to Simba is the "Backtest Asset Allocation" tool.

Assuming that's not the issue and you're comparing the annual returns side-by-side, there are definitely some differences on sources. According to the Portfolio Visualizer FAQ, when Vanguard fund data is not available they calculate the returns using the following data for treasury funds:

STT: FRED Interest Rate Data (2-year maturity) 1977-1991
ITT: FRED Interest Rate Data (5-year maturity) 1972-1991
LTT: FRED Interest Rate Data (30-year maturity) 1978-1986

The difference between the sources above and the Simba method is that PV uses bonds of fixed maturities while Simba models bond ladders within the desired maturity range. IMO, the Simba data is better because it more closely follows the methodology of real-world bond funds.

Along those lines, the reason that you're seeing less difference with STT bonds compared to the intermediate and long varieties is that the 2-year fixed maturity is reasonably close to the real-life range between 1-3 years. But simplifying 3-10 year ITTs to a fixed 5 years has greater error, and simplifying 10-30-year LTTs to a fixed 30 years has even more.
Thank you. Yes I've made sure to only look at 1977-present in PV and in Simba which is when all 4 series are available.

Does the latest bond simulator use an equal weighting of 3-10? It must if it's really just laddering them. This likely explains the difference. Bonds over 7 years in duration have significantly lower risk adjusted returns. By including them in the ITT sythnthetic index, it will have much lower risk-adjusted returns than a 5 year constant maturity synthetic fund. I would have guessed the difference to be a little smaller than what I'm seeing, but it easily explains most of the difference in my mind. Thanks again.
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

skierincolorado wrote: Thu Sep 16, 2021 4:59 pm
Tyler9000 wrote: Thu Sep 16, 2021 4:45 pm
skierincolorado wrote: Thu Sep 16, 2021 4:07 pm Do you know why the ITT returns in the spreadsheet are very different from Portfolio Visualizer?
First, be sure you're comparing apples-to-apples on timeframe. When using the "Backtest Portfolio" tool using tickers, PV automatically crops the data to the earliest year of the fund. The more comparable data to Simba is the "Backtest Asset Allocation" tool.

Assuming that's not the issue and you're comparing the annual returns side-by-side, there are definitely some differences on sources. According to the Portfolio Visualizer FAQ, when Vanguard fund data is not available they calculate the returns using the following data for treasury funds:

STT: FRED Interest Rate Data (2-year maturity) 1977-1991
ITT: FRED Interest Rate Data (5-year maturity) 1972-1991
LTT: FRED Interest Rate Data (30-year maturity) 1978-1986

The difference between the sources above and the Simba method is that PV uses bonds of fixed maturities while Simba models bond ladders within the desired maturity range. IMO, the Simba data is better because it more closely follows the methodology of real-world bond funds.

Along those lines, the reason that you're seeing less difference with STT bonds compared to the intermediate and long varieties is that the 2-year fixed maturity is reasonably close to the real-life range between 1-3 years. But simplifying 3-10 year ITTs to a fixed 5 years has greater error, and simplifying 10-30-year LTTs to a fixed 30 years has even more.
Thank you. Yes I've made sure to only look at 1977-present in PV and in Simba which is when all 4 series are available.

Does the latest bond simulator use an equal weighting of 3-10? It must if it's really just laddering them. This likely explains the difference. Bonds over 7 years in duration have significantly lower risk adjusted returns. By including them in the ITT sytnthetic index, it will have much lower risk-adjusted returns than a 5 year constant maturity synthetic fund. I would have guessed the difference to be a little smaller than what I'm seeing, but it easily explains most of the difference in my mind. Thanks again.
skierincolorado wrote: Thu Sep 16, 2021 4:59 pmDoes the latest bond simulator use an equal weighting of 3-10? It must if it's really just laddering them. [...]
Well, it's a bit more complicated than laddering, but yes, it's an equal-weighting of 3-10 (to mimic Vanguard VSIGX and its index).

If you really want to dive in the wonders of the bond fund simulator, here is the corresponding thread: viewtopic.php?f=10&t=179425

(and sorry if I'm a bit unresponsive nowadays, my available time should improve by the end of September)
Phyneas
Posts: 336
Joined: Tue Apr 27, 2021 9:10 pm

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

Post by Phyneas »

I'm probably using either Simba or PV wrong (or both), but I've noticed that the drawdown numbers can get kind of out of whack. For instance, for the Permanent Portfolio, PV shows ~13%, whereas Simba shows ~3%.
60% AVGE | 20 Year TIPS LMP | 5% Cash
AlohaJoe
Posts: 6609
Joined: Mon Nov 26, 2007 1:00 pm
Location: Saigon, Vietnam

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

Post by AlohaJoe »

Phyneas wrote: Tue Nov 23, 2021 2:24 am I'm probably using either Simba or PV wrong (or both), but I've noticed that the drawdown numbers can get kind of out of whack. For instance, for the Permanent Portfolio, PV shows ~13%, whereas Simba shows ~3%.
PV uses monthly numbers. Simba uses annual numbers.

If you had a data source with daily numbers, the drawdown would be even steeper.
Phyneas
Posts: 336
Joined: Tue Apr 27, 2021 9:10 pm

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

Post by Phyneas »

AlohaJoe wrote: Tue Nov 23, 2021 3:45 am
Phyneas wrote: Tue Nov 23, 2021 2:24 am I'm probably using either Simba or PV wrong (or both), but I've noticed that the drawdown numbers can get kind of out of whack. For instance, for the Permanent Portfolio, PV shows ~13%, whereas Simba shows ~3%.
PV uses monthly numbers. Simba uses annual numbers.

If you had a data source with daily numbers, the drawdown would be even steeper.
Interesting, thank you for the answer. I suppose both have their value in interpreting the results, but would it be correct to say that the monthly numbers are more useful in contextualising the severity of the draw-downs?
60% AVGE | 20 Year TIPS LMP | 5% Cash
AlohaJoe
Posts: 6609
Joined: Mon Nov 26, 2007 1:00 pm
Location: Saigon, Vietnam

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

Post by AlohaJoe »

Phyneas wrote: Tue Nov 23, 2021 5:37 am
AlohaJoe wrote: Tue Nov 23, 2021 3:45 am
Phyneas wrote: Tue Nov 23, 2021 2:24 am I'm probably using either Simba or PV wrong (or both), but I've noticed that the drawdown numbers can get kind of out of whack. For instance, for the Permanent Portfolio, PV shows ~13%, whereas Simba shows ~3%.
PV uses monthly numbers. Simba uses annual numbers.

If you had a data source with daily numbers, the drawdown would be even steeper.
Interesting, thank you for the answer. I suppose both have their value in interpreting the results, but would it be correct to say that the monthly numbers are more useful in contextualising the severity of the draw-downs?
Yes, annual numbers understate the actual volatility. The finer the granularity, the more volatility you'll see.

One great way to see this is the intra-year declines vs. calendar year declines chart that JP Morgan publishes every year in their "Guide to Markets".

https://justusjp.medium.com/intra-year- ... 3fd29c6d9f

The flip side of this is that the less you look at your portfolio, the smaller the drawdowns:

https://twitter.com/Rick_Ferri/status/1 ... e-world%2F

Or consider this. Imagine you have a 70/30 portfolio. And once a month you check the portfolio value. 34% of the time your portfolio will have lost money compared to the last time you checked. One-third of the time!

Now imagine you only check once a year. 16% of the time your portfolio will have lost money compared to the last time you checked. One-sixth of the time! You reduced volatility in half just by checking less often.

Now imagine you only check every three years. 5% of the time your portfolio will have lost money compared to the last time you checked. We've gone from "one-in-three" to "one-in-twenty" just by checking less frequently.

This is "do as I say, not as I do" advice but: there's not really much upside to checking your portfolio value more than once every year or two.
Leesbro63
Posts: 10640
Joined: Mon Nov 08, 2010 3:36 pm

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

Post by Leesbro63 »

AlohaJoe wrote: Tue Nov 23, 2021 6:13 am
Phyneas wrote: Tue Nov 23, 2021 5:37 am
AlohaJoe wrote: Tue Nov 23, 2021 3:45 am
Phyneas wrote: Tue Nov 23, 2021 2:24 am I'm probably using either Simba or PV wrong (or both), but I've noticed that the drawdown numbers can get kind of out of whack. For instance, for the Permanent Portfolio, PV shows ~13%, whereas Simba shows ~3%.
PV uses monthly numbers. Simba uses annual numbers.

If you had a data source with daily numbers, the drawdown would be even steeper.
Interesting, thank you for the answer. I suppose both have their value in interpreting the results, but would it be correct to say that the monthly numbers are more useful in contextualising the severity of the draw-downs?
Yes, annual numbers understate the actual volatility. The finer the granularity, the more volatility you'll see.

One great way to see this is the intra-year declines vs. calendar year declines chart that JP Morgan publishes every year in their "Guide to Markets".

https://justusjp.medium.com/intra-year- ... 3fd29c6d9f

The flip side of this is that the less you look at your portfolio, the smaller the drawdowns:

https://twitter.com/Rick_Ferri/status/1 ... e-world%2F

Or consider this. Imagine you have a 70/30 portfolio. And once a month you check the portfolio value. 34% of the time your portfolio will have lost money compared to the last time you checked. One-third of the time!

Now imagine you only check once a year. 16% of the time your portfolio will have lost money compared to the last time you checked. One-sixth of the time! You reduced volatility in half just by checking less often.

Now imagine you only check every three years. 5% of the time your portfolio will have lost money compared to the last time you checked. We've gone from "one-in-three" to "one-in-twenty" just by checking less frequently.

This is "do as I say, not as I do" advice but: there's not really much upside to checking your portfolio value more than once every year or two.
I get what you are saying. To make a devil's advocate argument...what if you looked in the summer of 1929 and didn't look again for 3 years?
Phyneas
Posts: 336
Joined: Tue Apr 27, 2021 9:10 pm

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

Post by Phyneas »

AlohaJoe wrote: Tue Nov 23, 2021 6:13 am
Phyneas wrote: Tue Nov 23, 2021 5:37 am
AlohaJoe wrote: Tue Nov 23, 2021 3:45 am
Phyneas wrote: Tue Nov 23, 2021 2:24 am I'm probably using either Simba or PV wrong (or both), but I've noticed that the drawdown numbers can get kind of out of whack. For instance, for the Permanent Portfolio, PV shows ~13%, whereas Simba shows ~3%.
PV uses monthly numbers. Simba uses annual numbers.

If you had a data source with daily numbers, the drawdown would be even steeper.
Interesting, thank you for the answer. I suppose both have their value in interpreting the results, but would it be correct to say that the monthly numbers are more useful in contextualising the severity of the draw-downs?
Yes, annual numbers understate the actual volatility. The finer the granularity, the more volatility you'll see.

One great way to see this is the intra-year declines vs. calendar year declines chart that JP Morgan publishes every year in their "Guide to Markets".

https://justusjp.medium.com/intra-year- ... 3fd29c6d9f

The flip side of this is that the less you look at your portfolio, the smaller the drawdowns:

https://twitter.com/Rick_Ferri/status/1 ... e-world%2F

Or consider this. Imagine you have a 70/30 portfolio. And once a month you check the portfolio value. 34% of the time your portfolio will have lost money compared to the last time you checked. One-third of the time!

Now imagine you only check once a year. 16% of the time your portfolio will have lost money compared to the last time you checked. One-sixth of the time! You reduced volatility in half just by checking less often.

Now imagine you only check every three years. 5% of the time your portfolio will have lost money compared to the last time you checked. We've gone from "one-in-three" to "one-in-twenty" just by checking less frequently.

This is "do as I say, not as I do" advice but: there's not really much upside to checking your portfolio value more than once every year or two.
That's really interesting, and it changes my perspective quite a bit frankly. I am guilty of peeking quite often, but both that JPM chart, and your explanation illustrate how counter-productive it really is, plus the stress it induces.
60% AVGE | 20 Year TIPS LMP | 5% Cash
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

FYI, I will do the annual Simba update this weekend. Bit busy with some other stuff right now.
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

HansT wrote: Tue Mar 09, 2021 8:23 am
siamond wrote: Sun Mar 07, 2021 10:36 pm When you customize portfolios or change the starting year of the backtest, there is a warning system in column D, the conditional formatting will turn bright red for every data series which doesn't have historical data
Here I think the naive user could be assisted. Col D indicates when a data series shouldn't be tested (or should be tested with caution), but I think the casual user wants to know when a portfolio shouldn't be tested. Here's a suggestion:

Code: Select all

set cell Lazy_Portfolios!E90 to {= E$85 >= MAX(IF(ISNUMBER(E$5:E$81),$D$5:$D$81,0))} 
and drag.  (Yes that's an array formula.)  
Then we can calculate whether the portfolio is "valid" for testing since "Starting Year for backtest"
Hans, I just implemented your suggested improvement. This will be part of the next update (to be published later today). Thank you for the idea.
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

Here is the first official update for the new year. Please download the corresponding (Excel) spreadsheet by clicking here: https://bit.ly/3f41sP1.
=> Feedback welcome. I'll wait a few more days to update the wiki page, just in case some further tweaking is needed.
=> Careful, a few 2021 numbers are still approximate, notably inflation. This will be updated in February.

Rev21a
1a. Added 2021 annual returns: funds and index returns updated
1b. Expense Ratios (ER) updated, main changes: EFV 0.35; VTRIX to 0.35; VWENX 0.16; VWNEX: 0.19; VGELX: 0.29; FSAGX: 0.76; VFSUX 0.10; PAGPX 0.97; EDV 0.06
1c. Inflation is a rough estimate for now; updates for some synthetic models are pending
2. Added MSCI USA GR USD returns in raw data input for TSM data series, mostly to allow advanced tests directly referencing this MSCI data series
3. Added a portfolio validation sanity check (enough historical data for the portfolio?) in row 83 (Analyze_Portfolio, Compare_Portfolios, Lazy_Portfolios)
User avatar
Tyler9000
Posts: 740
Joined: Fri Aug 21, 2015 11:57 am

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

Post by Tyler9000 »

I always appreciate the hard work you put into this, Siamond. Thanks!
User avatar
steve r
Posts: 1300
Joined: Mon Feb 13, 2012 7:34 pm
Location: Connecticut

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

Post by steve r »

duplicate
Last edited by steve r on Sun Jan 09, 2022 4:05 pm, edited 1 time in total.
"Owning the stock market over the long term is a winner's game. Attempting to beat the market is a loser's game. ..Don't look for the needle in the haystack. Just buy the haystack." Jack Bogle
User avatar
steve r
Posts: 1300
Joined: Mon Feb 13, 2012 7:34 pm
Location: Connecticut

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

Post by steve r »

Tyler9000 wrote: Sun Jan 09, 2022 3:54 pm I always appreciate the hard work you put into this, Siamond. Thanks!
+1 :beer
"Owning the stock market over the long term is a winner's game. Attempting to beat the market is a loser's game. ..Don't look for the needle in the haystack. Just buy the haystack." Jack Bogle
boglesmkcents
Posts: 94
Joined: Tue Jul 24, 2012 4:57 pm

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

Post by boglesmkcents »

Thank you, Siamond -- this is so very helpful and much appreciated as always!
User avatar
siamond
Posts: 6010
Joined: Mon May 28, 2012 5:50 am

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

Post by siamond »

siamond wrote: Sun Jan 09, 2022 3:45 pm Here is the first official update for the new year. Please download the corresponding (Excel) spreadsheet by clicking here: https://bit.ly/3f41sP1.
=> Feedback welcome. I'll wait a few more days to update the wiki page, just in case some further tweaking is needed.
=> Careful, a few 2021 numbers are still approximate, notably inflation. This will be updated in February.
Wiki updated. Let's wait for the official inflation numbers now... :shock:
apoptosis66
Posts: 3
Joined: Tue Jan 04, 2022 10:37 am

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

Post by apoptosis66 »

I am looking for some clarification on this spreadsheet's "Downside Deviation vs MAR" calculation.

Using rev21a, Analyze_Portfolio.H96 ...

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

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

Image

As you can see if I break the formula down I get 8.12% instead of 4.54%. I have been reading everything I can about Excel Array Formulas, but I just can't wrap my head around what this is doing to get that value. I have also become concerned that because I use LibreOffice Calc on Linux maybe someone in the official version of Excel is getting a different value, and this is a bug in LibreOffice?

Any help is appreciated.
apoptosis66
Posts: 3
Joined: Tue Jan 04, 2022 10:37 am

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

Post by apoptosis66 »

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

If you break down the formula to:

=MIN(0, OFFSET($Portfolio_Math.M$101, E$89, 0, E$88, 1)-OFFSET($Portfolio_Math.$J$101, E$89, 0, E$88, 1))

you get #VALUE even if you Ctrl+Shift+Enter making it an array formula. I took this to mean it was returning an array that wouldn't fit into a single cell.

If you got to the next step:

{=SUM(MIN(0, OFFSET($Portfolio_Math.M$101, E$89, 0, E$88, 1)-OFFSET($Portfolio_Math.$J$101, E$89, 0, E$88, 1))^2))}

This returns 10.76% which just happens to be the 2008 (return - Mar) of -32.80% ^ 2

Just to be sure I took out the power of 2:

{=SUM(MIN(0, OFFSET($Portfolio_Math.M$101, E$89, 0, E$88, 1)-OFFSET($Portfolio_Math.$J$101, E$89, 0, E$88, 1))))}

This returns -32.80%

What this says to me, is MIN is not returning an array in this formula and is instead returning the deepest downside - MAR. Which I don't think is the intention. Else why take the average? And the column is Titled "Downside SD vs MAR"

This is the only other example of calculating Sortino Ratio in a spreadsheet I can find, and it expects an array of draw downs not the deepest draw down:

https://www.educba.com/sortino-ratio/

So as of right now I think the spreadsheet is wrong, I just don't know how to fix it because its not behaving like I would expect array formulas to work.

Edit:

Found this Stackoverflow discussion about MIN/MAX in array formulas:

https://stackoverflow.com/questions/321 ... ay-formula

In summary MIN takes an array and returns a single value, so it doesn't work in an array formula and MIN(0, array) is just behaving like MIN(array). Another way to say this is functions that take arrays as inputs, only return single values in array formulas. The solutions look nasty.
Post Reply