Simba's backtesting spreadsheet [a Bogleheads community project]

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
staythecourse
Posts: 6993
Joined: Mon Jan 03, 2011 8:40 am

Post by staythecourse »

Like many I would like thank simba for tirelessly putting all this together.

I feel like this app. Does need a few cautionary notes as a reminder not to data mine:
1. For the same reasons the sec does it: past performance does not predict future returns.
2. Remember that many of the indexs/ funds had no investible produc for much of the time points. This makes some of the more dated data purely theoritical. Keep in mind the real starting dates for the specific funds of interest.

Thanks again simba.
"The stock market [fluctuation], therefore, is noise. A giant distraction from the business of investing.” | -Jack Bogle
jlhod1
Posts: 34
Joined: Wed Aug 05, 2009 9:11 pm

Bills

Post by jlhod1 »

That is about right. Here is the return of the ST T-Bill index from Vanguard:

https://personal.vanguard.com/us/funds/ ... st=tab%3A1


2.64% in 2010
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

stoub wrote:A minor labeling error for the next revision: the symbol for "VG Int'l Small" should be VFSVX, not VFWIX.
SET - Thanks for pointing the typo. I have fixed this for the next version.

Btw - Welcome to the forum.

Best Regards,
Simba
Last edited by simba on Sat Jan 29, 2011 4:43 pm, edited 1 time in total.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

rmelv wrote:Hey I'm a little bit confused. I made a hypothetical portfolio that is 100% t-bills/treasury money market and I'm showing a 2.76% percent return for 2010.

That's not right, is it?

Thanks for your wonderful tool. It has really helped me analyze my portfolio :)
rmelv - There was a typo in the returns for VUSXX (Admiral Treasury Money Mkt). The actual returns for this fund is 0.01% instead of 2.76%. I have corrected this in the next revision. I have also corrected the symbol I was using in the Portfolio worksheet for TBills from VMPXX to VUSXX in the next version.

Best Regards,
Simba
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

staythecourse wrote:Like many I would like thank simba for tirelessly putting all this together.

I feel like this app. Does need a few cautionary notes as a reminder not to data mine:
1. For the same reasons the sec does it: past performance does not predict future returns.
2. Remember that many of the indexs/ funds had no investible produc for much of the time points. This makes some of the more dated data purely theoritical. Keep in mind the real starting dates for the specific funds of interest.

Thanks again simba.
Staythecourse - I currently have this in the README worksheet
Please use this spreadsheet only for learning purposes and as a need for diversification. Don't change your AA purely based on historical returns.
Past returns are not indicative of future returns.
I have added a header for DISCLAIMER in the next revision.

I started this spreadsheet as a learning tool and have always stressed to use it for its entertainment value, rather than to decide/change one's Asset Allocation based purely on historical returns.

rmelv/Staythecourse - Thank you for your compliments.

Best Regards,
Simba
staythecourse
Posts: 6993
Joined: Mon Jan 03, 2011 8:40 am

Post by staythecourse »

Simba, please don't take offense at my comments.

I use your spreadsheet often myself and commend your work.

I just want to make sure other less experienced investor don't get "carried away".

Thanks again.
"The stock market [fluctuation], therefore, is noise. A giant distraction from the business of investing.” | -Jack Bogle
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

staythecourse wrote:Simba, please don't take offense at my comments.

I use your spreadsheet often myself and commend your work.

I just want to make sure other less experienced investor don't get "carried away".

Thanks again.
STC - None taken. I appreciate your comments and definitely agree with you about the disclaimer.

Best Regards,
Simba
SteveK
Posts: 11
Joined: Wed Aug 26, 2009 3:11 pm

Simba - rolling returns

Post by SteveK »

Hi Simba,

You've provided a tremendous tool. The additition of the rolling period charts is very helpful. In addition to the graph, can you have the data for 3,5,7,10 and 15 years displayed to the side or bottom with Standard Deviation and comparision to Total Market or any other index or portfolio desired for comparision? Here's the way I do it.

Rolling 3 Year Returns

Total Mkt Total Mkt My My
Return St. Dev. Return St. Dev.
1987 1989 15.46% 13.69% 9.52% 5.36%
1988 1990 12.46% 17.86% 9.02% 6.09%
1989 1991 17.55% 22.02% 12.07% 8.59%
1990 1992 11.42% 20.52% 9.82% 8.47%
1991 1993 17.64% 14.15% 13.85% 5.64%
1992 1994 6.51% 5.90% 6.53% 8.43%
1993 1995 14.39% 18.48% 10.03% 11.22%
1994 1996 17.92% 18.07% 7.70% 10.68%
1995 1997 29.10% 7.57% 11.98% 6.18%
1996 1998 25.00% 5.25% 8.26% 1.41%
1997 1999 25.97% 4.31% 6.88% 3.36%
1998 2000 10.92% 19.69% 7.04% 3.57%
1999 2001 -0.48% 19.97% 6.50% 3.58%
2000 2002 -14.30% 5.89% 7.52% 2.45%
2001 2003 -2.59% 27.77% 8.50% 4.20%
2002 2004 5.32% 26.49% 9.33% 3.65%
2003 2005 16.13% 13.17% 9.27% 3.73%
2004 2006 11.26% 4.87% 7.89% 1.59%
2005 2007 8.90% 5.65% 8.02% 1.67%
2006 2008 -8.46% 27.90% 4.59% 7.32%
2007 2009 -5.10% 33.34% 5.85% 9.42%
10.24% 15.84% 8.58% 5.55%
staythecourse
Posts: 6993
Joined: Mon Jan 03, 2011 8:40 am

Post by staythecourse »

Hi Simba,

Love the rolling returns. Is it just me or does the column for 1985-2010 for nominal and real return start at 1972 and seem to go only to time point 1988 at most.

Are they mislabeled? The time period on the 1972- 2010 seems to be labeled correct.

Thanks.
"The stock market [fluctuation], therefore, is noise. A giant distraction from the business of investing.” | -Jack Bogle
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

staythecourse wrote:Hi Simba,

Love the rolling returns. Is it just me or does the column for 1985-2010 for nominal and real return start at 1972 and seem to go only to time point 1988 at most.

Are they mislabeled? The time period on the 1972- 2010 seems to be labeled correct.

Thanks.
STC - You got sharp eyes :) The chart was pulling up incorrect years. This has been fixed in the next revision (rev10c)

Edited to add the next revision version.

Best Regards,
Simba
Last edited by simba on Wed Feb 09, 2011 10:53 am, edited 2 times in total.
staythecourse
Posts: 6993
Joined: Mon Jan 03, 2011 8:40 am

Post by staythecourse »

simba wrote:
staythecourse wrote:Hi Simba,

Love the rolling returns. Is it just me or does the column for 1985-2010 for nominal and real return start at 1972 and seem to go only to time point 1988 at most.

Are they mislabeled? The time period on the 1972- 2010 seems to be labeled correct.

Thanks.
STC - You got sharp eyes :) The chart was pulling up incorrect years. This has been fixed in the next revision.

Best Regards,
Simba
Thanks.
"The stock market [fluctuation], therefore, is noise. A giant distraction from the business of investing.” | -Jack Bogle
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Post by Bongleur »

>This has been fixed in the next revision.

Post 1 has rev10b. Is that the fixed version you refer to above?

Adding the date of the current rev to Post 1 would be helpful.
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.
re@51.5
Posts: 196
Joined: Thu Sep 02, 2010 7:51 am
Location: West Michigan

Post by re@51.5 »

Bongleur wrote:>This has been fixed in the next revision.

Post 1 has rev10b. Is that the fixed version you refer to above?
No.

Post #1: "Last edited by simba on Wed Jan 26, 2011 11:21 am"

Post #1,000,0000: quote from Simba, "Posted: Mon Feb 07, 2011 4:52 pm ... This has been fixed in the next revision."
Adding the date of the current rev to Post 1 would be helpful.
Yes.

Mike
As Merton Miller, a Nobel laureate at the University of Chicago, puts it, "I'll never understand why they call bonds 'fixed' income."
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

Bongleur wrote:>This has been fixed in the next revision.

Post 1 has rev10b. Is that the fixed version you refer to above?

Adding the date of the current rev to Post 1 would be helpful.
Post 1 will always have the latest version.
User avatar
Topic Author
simba
Posts: 540
Joined: Mon Feb 19, 2007 6:47 pm

Post by simba »

The latest version of the spreadsheet (rev10c) is now available.

You can download the latest version of Excel Spreadsheet [rev10c] or the OpenOffice version [rev10c]

The latest version will always be reflected in post #1 for this Topic - Spreadsheet for backtesting

Best Regards,
Simba
phositadc
Posts: 443
Joined: Mon Jul 26, 2010 6:17 pm

Post by phositadc »

Thanks dude. You are a spreadsheet wizard!
rmelv
Posts: 106
Joined: Wed Jan 26, 2011 6:35 pm

Post by rmelv »

Past returns may not predict future returns, but you are still a god send! Thanks for all of your hard work. I wonder how many portfolios were influenced by your spreadsheet... Funny to think about :wink:
staythecourse
Posts: 6993
Joined: Mon Jan 03, 2011 8:40 am

Post by staythecourse »

rmelv wrote:Past returns may not predict future returns, but you are still a god send! Thanks for all of your hard work. I wonder how many portfolios were influenced by your spreadsheet... Funny to think about :wink:
I am not implying this Towards Simba's work, but I've actually thought if everyone is using your spreadsheet there may be a new risk named the "Simba risk". This would be the risk if Simba made a mistake in data entry and everyone used it incorrect to play with their portfolios.

I guess the diversification of having thousands use the spreadsheet would likely find out and eliminate mistakes such as data entry. Sort of a Efficient Spreadsheet Hypothesis.

:D

Thanks again.
"The stock market [fluctuation], therefore, is noise. A giant distraction from the business of investing.” | -Jack Bogle
jlhod1
Posts: 34
Joined: Wed Aug 05, 2009 9:11 pm

Simba Risk - revisited

Post by jlhod1 »

Simba Risk, I love it!

I actually think the bigger risk is for the user who creates a portfolio that ends up so curve fitted that they fail to remember that rule #1.

Namely, an a priori assumption that you can never know what will do best in the future, so you must own a lot of asset classes.

For me the primary value of the spreadsheet is seeing the power of annual rebalancing and to see what happens when I make different choices regarding *how* I achieve ownership of asset classes. For example, Total bond vs T-Bills. What was the long term affect on risk/return.

I also love playing with the years that the portfolio is held to see how it performed in different economic cycles (ie stagflation).

That is why I would never create a portfolio like Larry Swedroe's hypothetical "Minimize Fat Tails", even though it looks great when looking backwards. If it doesn't contain most asset classes (Large, Small, Dom, Int'l, Value, and ideally Real Estate) I would be extremely cautious.
wtoner
Posts: 7
Joined: Mon Nov 29, 2010 11:07 am

General Questions

Post by wtoner »

Correlation to me (and I know little) seems to be directional only and does not include amplitude? Asset A goes down 10 %, Asset B goes up .1% and correlation is -1 but B is not hedging A signficantly. Does anyone have info, research, or thoughts on this concept.

Also, not sure what I want to ask.. but MPT seems closely tied to standard deviations, so what is the significance of the VIX? Say stocks normally have a StdDev of 15% but at a market bottom, the VIX can reflect (and perhaps accurately) a StdDev of 70-80%. Can this be factored into a model? Should it be? Further, the implied volatility can be imputed (and used) from any asset with an option, obviously.
Does anyone have info, research, or thoughts on this concept.
User avatar
stratton
Posts: 11085
Joined: Sun Mar 04, 2007 4:05 pm
Location: Puget Sound

Post by stratton »

Amplitude doesn't matter with correlation. Two items can be 100% correlatied and one item can be 10x more volatile.
...and then Buffy staked Edward. The end.
User avatar
stratton
Posts: 11085
Joined: Sun Mar 04, 2007 4:05 pm
Location: Puget Sound

Post by stratton »

Where are the international small cap numbers sourced from? There is no source for 20 years of it.

In the spreadsheet the data source is listed as:
Intl Small IFA website (added back expenses):1972-1974
Intl Small 1975-1996
VG Intl Explorer - 1997 - 2009
FTSE All-World ex-US Small Cap Idx (VFSVX) 2010 -
What is the source of data for the bolded ones?

TRP Intl Discovery (PRIDX) which contains EM goes back to 1989 for perf numbers.

Templeton Global Smaller Comp A (TEMGX) goes back to 1982 with perf numbers and is about 25% US and has EM according to a 1994 SEC filing I was looking at.

Paul
...and then Buffy staked Edward. The end.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 9:36 am

Post by Bongleur »

Could it be tweaked to show the dollar amount & % amount involved in rebalancing whenever rebalancing is needed?

Here's an idea to account for physical gold as a store of emergency value:

At portfolio creation, any chosen $$ of gold is put to the side. Its not part of the 100%

When rebalancing is required and the loss above a threshold (and if desired, a loss in both equity and bond classes) (indicating some sort of market disaster) then the gold is used as if it were outside cash, except that its value is increased by some factor.

So if $10,000 was required to rebalance, only say $5000 of gold would be deducted (factor of 2).

The factor is larger when the magnitude of rebalancing is larger (the theory that gold multiplies quickly in value during such times).

The gold is replenished to its starting value (or up to the $$ available) only when a year's gain is above some threshold; the amount above the threshold is cashed in as gold. Don't know how to estimate the current price though.
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.
kyounge1956
Posts: 333
Joined: Mon May 31, 2010 5:39 pm

Post by kyounge1956 »

simba wrote:The latest version of the spreadsheet (rev10c) is now available.

You can download the latest version of Excel Spreadsheet [rev10c] or the OpenOffice version [rev10c]

The latest version will always be reflected in post #1 for this Topic - Spreadsheet for backtesting

Best Regards,
Simba
Hi Simba,
thanks for all the work you do on these spreadsheets. I was looking at some sample asset allocations the other day and I think I've discovered a slight correction that needs to be made. On both of the "Returns" tabs, I don't think the conditional formatting has been applied to all the cells it should have. When I entered enter a 1972-2010 portfolio of 5% each Small-cap Value, REIT, EAFE & commodities, 40% 5-year Treasuries and 40% TIPS, the worst drawdown is -3.51% (in Column AM, line 43) but the orange cell is on line 29, which is the next worst return (+0.21%). I can't tell whether the Real Return columns are affected because the worst real return is up toward the top of the spreadsheet. Using the same AA and the 1985-2010 data, the worst return is -4.07% (Column AZ, line 30), but the orange cell is on line 16, the next worst return, and the same glitch occurs in the Real Return.

I don't recall which version I downloaded—I think it was the Excel one, but I don't have Excel on my computer so I'm not completely certain. The spreadsheet I have opens fine in NeoOffice on a Mac Mini using OS 10.5.8, if that tells you anything.
mikewitteman
Posts: 10
Joined: Wed Mar 16, 2011 2:17 am

--> Simba

Post by mikewitteman »

Your backtested portfolio spreadsheet is a gem. Thanks so much for creating it and posting it. It is exactly what I was looking for! :) :)
User avatar
Houston101
Posts: 103
Joined: Fri Feb 26, 2010 2:15 pm

Post by Houston101 »

Simba this is indeed a gem, thank you very much.

Will it be correct to assume that the gains are calculated without taking into account dividends?
User avatar
Cb
Posts: 275
Joined: Tue Feb 20, 2007 11:39 pm

Post by Cb »

Houston101 wrote:Simba this is indeed a gem, thank you very much.

Will it be correct to assume that the gains are calculated without taking into account dividends?
No...the returns include dividends, and have fund ER's deducted.
User avatar
muddyglass
Posts: 133
Joined: Sat Jan 29, 2011 1:11 am

Post by muddyglass »

hello simba (and all others who contributed to the spreadsheet), thanks for the mother of all spreadsheets! your work is really appreciated. i had a lot of fun messing around with this thing!
floydtime
Posts: 413
Joined: Sat Feb 26, 2011 9:09 pm
Location: A book

Post by floydtime »

Hello Simba, your hard work (and especially your sharing of it!) is very much appreciated.

I find it interesting, under the Lazy_Portfolios_85 tab, that the Annualized returns of all of the lazy portfolios, save one, are surprisingly close.

I'm also quite surprised to see Wellesley holding its own with the lower volatility.

I know, I know...past performance. I only said it is interesting. :)
"Do not value money for any more nor any less than its worth; it is a good servant but a bad master" - Alexandre Dumas
johan851
Posts: 240
Joined: Sun Apr 17, 2011 12:59 pm

Post by johan851 »

Thanks for building this! I was wishing over the last couple of days that there was something exactly like this to sort of visualize how different allocations behaved in the past. It was really, really helpful for someone getting started.
User avatar
CWRadio
Posts: 646
Joined: Sat Feb 02, 2008 9:04 am
Location: SE Michigan Ionosphere

Post by CWRadio »

Simba thanks for all your hard work.

I was reading Scott Burns article called "Has Your Portfolio Recovered Yet?", that lead me to ask you the question.

http://assetbuilder.com/blogs/scott_bur ... d-yet.aspx

Is it possible to add an option that would allow us to enter a percent for withdrawal monthly. Example:

If you invested $1 million in a Portfolio or total of all Portfolios at the start of 2007 and reinvested all dividends and capital gains distributions and then withdraw $3,333.33 (4% a year) a month (start of 2007) what would the Portfolios have at the end of 2010?

Thanks
John Z
Posts: 521
Joined: Sun Nov 14, 2010 4:42 pm

Post by John Z »

Hi Simba,
I downloaded your spreadsheet several days ago and am amazed at the amount of info here for lazy portfolios. I do have one question:
On the lazy portfolio tab I entered start year 2007 because I wanted to see how the coffeehouse did in the last 4 years. I went to the portfolio vs return risk chart and noticed that P20, Scott Burns Four Square shows std deviation a bit over 10% but the figure in column D shows 19.28%. Am I missing something? I have rev10c.
Thank you,
John

MY MISTAKE. Did the same thing (above) on another PC with a better display and see that I was looking at the wrong symbol. Very sorry.
Last edited by John Z on Thu Jun 30, 2011 11:39 am, edited 2 times in total.
sschullo
Posts: 2839
Joined: Sun Apr 01, 2007 8:25 am
Location: Long Beach, CA
Contact:

Post by sschullo »

Simba and Trev,
Thanks for all of your hard work.

Quick question: are the returns of the different portfolios from 1985 to 2010 listed under tab Lazy Portfolio 85, lines 116-141?
Looks like it to me, but just verifying.

Thanks so much,
Steve
Never in the history of market day-traders’ has the obsession with so much massive, sophisticated, & powerful statistical machinery used by the brightest people on earth with such useless results.
sschullo
Posts: 2839
Joined: Sun Apr 01, 2007 8:25 am
Location: Long Beach, CA
Contact:

Post by sschullo »

floydtime wrote:Hello Simba, your hard work (and especially your sharing of it!) is very much appreciated.

I find it interesting, under the Lazy_Portfolios_85 tab, that the Annualized returns of all of the lazy portfolios, save one, are surprisingly close.

I'm also quite surprised to see Wellesley holding its own with the lower volatility.

I know, I know...past performance. I only said it is interesting. :)
Ditto.
Never in the history of market day-traders’ has the obsession with so much massive, sophisticated, & powerful statistical machinery used by the brightest people on earth with such useless results.
paper200
Posts: 289
Joined: Sat Feb 02, 2008 10:40 am

Post by paper200 »

Hi Simba,

I love your spreadsheet. Great work in putting together something like this for everyone.

I have a request - currently the way the portfolio "predictions" are designed it takes into account only a lumpsum allocation. Is it possible for you to design an additional feature that will take into account someone allocating on a "yearly" basis to see how a portfolio will evolve given a set of past perfomance of the market? You are an expert with spreadsheets and the problem to design one may be of interest to you, and if you publish, to folks in the forum.

Regards
Nathan
staythecourse
Posts: 6993
Joined: Mon Jan 03, 2011 8:40 am

Post by staythecourse »

paper200 wrote:Hi Simba,

I love your spreadsheet. Great work in putting together something like this for everyone.

I have a request - currently the way the portfolio "predictions" are designed it takes into account only a lumpsum allocation. Is it possible for you to design an additional feature that will take into account someone allocating on a "yearly" basis to see how a portfolio will evolve given a set of past perfomance of the market? You are an expert with spreadsheets and the problem to design one may be of interest to you, and if you publish, to folks in the forum.

Regards
Nathan
Must say Simba if you do accomplish the above you will have done something quite remarkable and should start charging for it (of course not us here on Bogleheads :D )
"The stock market [fluctuation], therefore, is noise. A giant distraction from the business of investing.” | -Jack Bogle
sschullo
Posts: 2839
Joined: Sun Apr 01, 2007 8:25 am
Location: Long Beach, CA
Contact:

Post by sschullo »

I have been comparing 17 years from 1994 - 2010 with my own portfolio and the Wellington fund performance has been the best so far. Ill post results when I am done.
Never in the history of market day-traders’ has the obsession with so much massive, sophisticated, & powerful statistical machinery used by the brightest people on earth with such useless results.
User avatar
Lbill
Posts: 4997
Joined: Thu Mar 13, 2008 11:25 pm
Location: Somewhere between Up and Down

Post by Lbill »

Simba-

Just wanted to verify that the OpenOffice version 10c is not able to display the returns charts on the Porfolio tab, as they are in the Excel version, correct? Is this a limitation of OpenOffice?
"Life can only be understood backward; but it must be lived forward." ~ Søren Kierkegaard | | "You can't connect the dots looking forward; but only by looking backwards." ~ Steve Jobs
Clive
Posts: 1950
Joined: Sat Jun 13, 2009 5:49 am

Post by Clive »

Simba - great spreadsheet - love it :)

Query on the 5 year treasury VFITX for 1995 though that shows a 20.44% gain.

From http://www.federalreserve.gov/releases/h15/data.htm it looks like the 5 year T yields were

1994 6.69
1995 6.38
1996 6.18

So I can't see how a 20.44% gain in 1995 occurred.

Yahoo shows that value http://au.finance.yahoo.com/q/pm?s=VFITX - but just seems ???
rwwoods
Posts: 1312
Joined: Thu Jun 07, 2007 7:54 pm
Location: The Villages, Florida

Post by rwwoods »

Where is Simba? I have not seen a post from him since Feb.
"I'm not so much concerned about the return on my money as the return of my money" - Will Rogers
sschullo
Posts: 2839
Joined: Sun Apr 01, 2007 8:25 am
Location: Long Beach, CA
Contact:

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

Post by sschullo »

Simba and TrevH,
Will you have data for 2011 early next year?
Thanks for your outstanding work.
Steve
Never in the history of market day-traders’ has the obsession with so much massive, sophisticated, & powerful statistical machinery used by the brightest people on earth with such useless results.
wtoner
Posts: 7
Joined: Mon Nov 29, 2010 11:07 am

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

Post by wtoner »

Yes .....where is Simba? Concern grows daily...
edge
Posts: 3833
Joined: Mon Feb 19, 2007 6:44 pm
Location: NY

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

Post by edge »

I wonder if this will be updated?
User avatar
stratton
Posts: 11085
Joined: Sun Mar 04, 2007 4:05 pm
Location: Puget Sound

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

Post by stratton »

edge wrote:I wonder if this will be updated?
Previously the update took place after the CPI-U is issued for year which will probably be in a few weeks.

Paul
...and then Buffy staked Edward. The end.
User avatar
Lbill
Posts: 4997
Joined: Thu Mar 13, 2008 11:25 pm
Location: Somewhere between Up and Down

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

Post by Lbill »

I'd die without my Simba fix!!!!! :beer
"Life can only be understood backward; but it must be lived forward." ~ Søren Kierkegaard | | "You can't connect the dots looking forward; but only by looking backwards." ~ Steve Jobs
sschullo
Posts: 2839
Joined: Sun Apr 01, 2007 8:25 am
Location: Long Beach, CA
Contact:

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

Post by sschullo »

Lbill wrote:I'd die without my Simba fix!!!!! :beer
Yea, I want to find out if my portfolio has beaten more lazy portfolios. I only beat one, 2nd Grader. I need more! :greedy
Never in the history of market day-traders’ has the obsession with so much massive, sophisticated, & powerful statistical machinery used by the brightest people on earth with such useless results.
staythecourse
Posts: 6993
Joined: Mon Jan 03, 2011 8:40 am

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

Post by staythecourse »

Since we are already begging for requests for Simba...

Can you please add a feature that allows to find the value of a portfolio by regular additions to the portfolio over a period of time, i.e. regular monthly additions. It is unusual this is the most common way most folks invest (monthly contributions through a 401k for example) yet there is NO spreadsheet that analyzes returns that way.

Thanks.
"The stock market [fluctuation], therefore, is noise. A giant distraction from the business of investing.” | -Jack Bogle
phositadc
Posts: 443
Joined: Mon Jul 26, 2010 6:17 pm

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

Post by phositadc »

It would be a TON of work to turn this spreadsheet into something that can track the growth of monthly additions. If you look at the data tables that the spreadsheet uses, it just uses yearly returns. To find data for monthly returns (i.e., what would a portfolio grow to if you make a monthly contribution of X) would be quite a task.

That being said, if you are satisfied tracking yearly contributions as opposed to monthly ones, you can easily create such a table yourself. Say you have a current portfolio of $100,000 and you contribute $10,000 per year to it. And say that Simba's spreadsheet shows backtesting returns for your portfolio of 7% per year. After 1 year, you have [($100,000*1.07)+10,000]. On a spreadsheet it is a relatively simple matter to extend this calculation for dozens of iterations, using the result from the previous calculation, so that you can see what value your portfolio would theoretically be sitting at in any given year. For example, for year 2 you would have [((year 1 result)*1.07)+10,000]. And so on and so forth.
sschullo
Posts: 2839
Joined: Sun Apr 01, 2007 8:25 am
Location: Long Beach, CA
Contact:

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

Post by sschullo »

chrikenn wrote:It would be a TON of work to turn this spreadsheet into something that can track the growth of monthly additions. If you look at the data tables that the spreadsheet uses, it just uses yearly returns. To find data for monthly returns (i.e., what would a portfolio grow to if you make a monthly contribution of X) would be quite a task.

That being said, if you are satisfied tracking yearly contributions as opposed to monthly ones, you can easily create such a table yourself. Say you have a current portfolio of $100,000 and you contribute $10,000 per year to it. And say that Simba's spreadsheet shows backtesting returns for your portfolio of 7% per year. After 1 year, you have [($100,000*1.07)+10,000]. On a spreadsheet it is a relatively simple matter to extend this calculation for dozens of iterations, using the result from the previous calculation, so that you can see what value your portfolio would theoretically be sitting at in any given year. For example, for year 2 you would have [((year 1 result)*1.07)+10,000]. And so on and so forth.
I agree. Forget the monthly contribution calculation. Simba you have done enough already. Thanks.
Never in the history of market day-traders’ has the obsession with so much massive, sophisticated, & powerful statistical machinery used by the brightest people on earth with such useless results.
texas_archer
Posts: 427
Joined: Wed Dec 01, 2010 10:20 am

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

Post by texas_archer »

tag for the update
Post Reply