Spreadsheet for backtesting (includes TrevH's data)

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.

Asset Class Returns:

Postby Barry Barnitz » Sun Feb 08, 2009 4:43 am

We have asset class returns for the fiat currency era (1970-2008) for gold and other asset classes on the wiki:

Asset Class Returns

Gold Bullion Prices 1970 - 2008

regards,
Image | blb | December Birthday Celebration: Ludwig van Beethoven
User avatar
Barry Barnitz
Wiki Admin
 
Posts: 2652
Joined: Mon Feb 19, 2007 11:42 pm

Postby grayfox » Sun Feb 08, 2009 6:57 am

Thanks. I didn't realize how easy it was to add new assets. Incredible work simba.

I notice there is some differences in gold prices from various data sources and therefore calculated gold returns.

Finfacts
http://www.finfacts.ie/Private/curency/ ... tprice.htm

Only Gold
http://www.onlygold.com/TutorialPages/p ... 0yrsfs.htm
User avatar
grayfox
 
Posts: 3945
Joined: Sat Sep 15, 2007 5:30 am
Location: Anytown, USA

Postby DP » Sun Feb 08, 2009 1:00 pm

Hi,
What happened to gold? I thought gold was added to the spreadsheet so that Harry Browne permanent portfolio could be compared.


A Google Docs version of the spreadsheet was made that includes gold. See this prior post in this thread: Paul Douglas Boyer Posted: Fri Feb 06, 2009 9:23 pm. I think a google account is required.

Don
DP
 
Posts: 659
Joined: Thu Apr 17, 2008 6:19 pm

Postby Leif » Sun Feb 08, 2009 1:51 pm

Not sure about your CPI-U numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link

ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt
User avatar
Leif
 
Posts: 1081
Joined: Wed Sep 19, 2007 5:15 pm

Postby simba » Sun Feb 08, 2009 2:31 pm

Leif Eriksen wrote:Not sure about your CPI-U numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link

ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt


I use the Avg-Avg for the CPI-U numbers.
CPI-U for 2008= (Annual Avg for 2008 - Annual Avg for 2007)/(Annual Avg for 2007)

CPI-U for 2008 = (215.303 - 207.342) / 207.342 = 0.03839 = 3.84%

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

Postby Leif » Sun Feb 08, 2009 2:57 pm

simba wrote:
Leif Eriksen wrote:Not sure about your CPI-U numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link

ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt


I use the Avg-Avg for the CPI-U numbers.
CPI-U for 2008= (Annual Avg for 2008 - Annual Avg for 2007)/(Annual Avg for 2007)

CPI-U for 2008 = (215.303 - 207.342) / 207.342 = 0.03839 = 3.84%

Best Regards,
Simba


I guess over time Dec-Dec versus Avg-Avg balances out.
User avatar
Leif
 
Posts: 1081
Joined: Wed Sep 19, 2007 5:15 pm

Postby DaleMaley » Sun Feb 08, 2009 6:04 pm

many thanks Simba !!

:D
Most investors, both institutional and individual, will find that the best way to own common stocks is through an index fund that charges minimal fees. – Warren Buffett
User avatar
DaleMaley
 
Posts: 1484
Joined: Thu Mar 01, 2007 9:04 pm
Location: Fairbury, Illinois

Some corrections

Postby Paul Douglas Boyer » Tue Feb 10, 2009 3:25 pm

Simba, in your spreadsheet, here are some corrections:

Cell SP-85-08!C35 should read "=RATE(Portfolio!$K...
Cell SP-85-08!C36 should read "=RATE(Portfolio!$K...
Column P in SP-85-08 incorrectly points to 72 data.

Also,

I have the values for gold in the Google version of the spreadsheet at:
https://spreadsheets.google.com/ccc?key=pOjc3ot10vgs0eml-DJZKcw&newcopy

I added a returns vs risk chart for 1972 - 2008
and compared against Harry Browne, Coffeehouse, Scott Burns Four Square, and all of the IFA Index Portfolios there.
Paul Douglas Boyer, host, MadMoneyMachine.com
User avatar
Paul Douglas Boyer
 
Posts: 127
Joined: Wed Mar 07, 2007 4:19 pm
Location: Leesburg, VA

Re: Some corrections

Postby MachineGhost » Sat Feb 14, 2009 3:56 am

There is also a bug with deducting the ER present in rev7f and rev7h on the two Data tabs. Although the formula looks logical in English, it is actually being applied in reverse, i.e. if the current year is newer than the YOI, the ER will not be deducted!

Paul Douglas Boyer wrote:Simba, in your spreadsheet, here are some corrections:

Cell SP-85-08!C35 should read "=RATE(Portfolio!$K...
Cell SP-85-08!C36 should read "=RATE(Portfolio!$K...
Column P in SP-85-08 incorrectly points to 72 data.
[/u]
Last edited by MachineGhost on Sat Feb 14, 2009 1:35 pm, edited 2 times in total.
MachineGhost
 
Posts: 55
Joined: Sat Feb 14, 2009 3:46 am

Re: Some corrections

Postby grayfox » Sat Feb 14, 2009 4:53 am

MachineGhost wrote:There is also a bug with deducting the ER present in rev7f and rev7h on the two Data tabs. Although the formula looks logical in English, it is actually being applied in reverse, i.e. if the current year is greater than the YOI, the ER will not be deducted!


It looks like MachineGhost is right. The ER is not deducted.

I wonder why you would want to deduct the ER only for years after YOI?
New funds are getting a free ride with zero ER for the years before they were started. Older funds are paying the ER from the beginning.
User avatar
grayfox
 
Posts: 3945
Joined: Sat Sep 15, 2007 5:30 am
Location: Anytown, USA

Postby grok87 » Sat Feb 14, 2009 10:32 am

simba wrote:
Leif Eriksen wrote:Not sure about your CPI-U numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link

ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt


I use the Avg-Avg for the CPI-U numbers.
CPI-U for 2008= (Annual Avg for 2008 - Annual Avg for 2007)/(Annual Avg for 2007)

CPI-U for 2008 = (215.303 - 207.342) / 207.342 = 0.03839 = 3.84%

Best Regards,
Simba


Hi Simba,
Here's a vote for using the more traditional definition of inflation- i.e. the government definition that results in 0.1% inflation for 2008. It's more consistent with how we define yearly asset class returns, and hence easier to calculate real returns.
For example during 2008 the S&P 500 return was -37.0% and 2008 inflation was 0.1%, so the real return of the S&P 500 was -37.1%, etc.
just my 2 cents,
cheers,
grok, CFA | Danon delenda est
grok87
 
Posts: 6453
Joined: Tue Feb 27, 2007 10:00 pm

Re: Some corrections

Postby Paul Douglas Boyer » Sat Feb 14, 2009 1:11 pm

grayfox wrote:
MachineGhost wrote:There is also a bug with deducting the ER present in rev7f and rev7h on the two Data tabs. Although the formula looks logical in English, it is actually being applied in reverse, i.e. if the current year is greater than the YOI, the ER will not be deducted!


It looks like MachineGhost is right. The ER is not deducted.

I wonder why you would want to deduct the ER only for years after YOI?
New funds are getting a free ride with zero ER for the years before they were started. Older funds are paying the ER from the beginning.


I have updated the Google version of the spreadsheet.
Paul Douglas Boyer, host, MadMoneyMachine.com
User avatar
Paul Douglas Boyer
 
Posts: 127
Joined: Wed Mar 07, 2007 4:19 pm
Location: Leesburg, VA

Re: Some corrections

Postby MachineGhost » Sat Feb 14, 2009 1:50 pm

I don't know if its semantics on yours or my part or whatever, but what I originally meant was that the ER is not being deducted from funds after they are started. i.e. if inception is 2004 for GLD, than the ER will not be deducted for 2005, 2006, 2007, and 2008, but will be deducted for 2004 and earlier years.

Although as you infer, the ER should be deducted for all years.

Maybe the implication was that the ER was automatically deducted from mutual funds total return as opposed to index composites, but that is not the case. Fees are a separate accounting on the yearly statement that is paid out the investor's own pocket, not the fund's. I am not sure about ETF's.

grayfox wrote:
MachineGhost wrote:There is also a bug with deducting the ER present in rev7f and rev7h on the two Data tabs. Although the formula looks logical in English, it is actually being applied in reverse, i.e. if the current year is greater than the YOI, the ER will not be deducted!


It looks like MachineGhost is right. The ER is not deducted.

I wonder why you would want to deduct the ER only for years after YOI?
New funds are getting a free ride with zero ER for the years before they were started. Older funds are paying the ER from the beginning.
Last edited by MachineGhost on Sat Feb 14, 2009 1:58 pm, edited 2 times in total.
MachineGhost
 
Posts: 55
Joined: Sat Feb 14, 2009 3:46 am

Postby MachineGhost » Sat Feb 14, 2009 1:56 pm

I second this.

grok87 wrote:
simba wrote:
Leif Eriksen wrote:Not sure about your CPI-U numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link

ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt


I use the Avg-Avg for the CPI-U numbers.
CPI-U for 2008= (Annual Avg for 2008 - Annual Avg for 2007)/(Annual Avg for 2007)

CPI-U for 2008 = (215.303 - 207.342) / 207.342 = 0.03839 = 3.84%

Best Regards,
Simba


Hi Simba,
Here's a vote for using the more traditional definition of inflation- i.e. the government definition that results in 0.1% inflation for 2008. It's more consistent with how we define yearly asset class returns, and hence easier to calculate real returns.
For example during 2008 the S&P 500 return was -37.0% and 2008 inflation was 0.1%, so the real return of the S&P 500 was -37.1%, etc.
just my 2 cents,
cheers,
MachineGhost
 
Posts: 55
Joined: Sat Feb 14, 2009 3:46 am

Postby grayfox » Sun Feb 15, 2009 2:11 am

grok87 wrote:
simba wrote:
Leif Eriksen wrote:Not sure about your CPI-U numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link

ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt


I use the Avg-Avg for the CPI-U numbers.
CPI-U for 2008= (Annual Avg for 2008 - Annual Avg for 2007)/(Annual Avg for 2007)

CPI-U for 2008 = (215.303 - 207.342) / 207.342 = 0.03839 = 3.84%

Best Regards,
Simba


Hi Simba,
Here's a vote for using the more traditional definition of inflation- i.e. the government definition that results in 0.1% inflation for 2008. It's more consistent with how we define yearly asset class returns, and hence easier to calculate real returns.
For example during 2008 the S&P 500 return was -37.0% and 2008 inflation was 0.1%, so the real return of the S&P 500 was -37.1%, etc.
just my 2 cents,
cheers,


First of all there should be consistency. If average annual return series is used then average annual inflation series should be used.

But aside from that I believe it is mathematically more correct to use average numbers for a period. For instance if you have monthly data you would use monthly average rather than picking a one day like the last day of the month. Otherwise you get biases in the data caused by aliasing. In more simple terms, the average for a month is more representative of that month that a particular day.

So for annual data the series to use should be annual average.
User avatar
grayfox
 
Posts: 3945
Joined: Sat Sep 15, 2007 5:30 am
Location: Anytown, USA

Postby MachineGhost » Sun Feb 15, 2009 2:37 am

But annual data isn't an average, it is ( 12/31/xx divided by 12/31/xx-1 ) -1 or YTD as of 12/31/xx.

grayfox wrote:
grok87 wrote:
simba wrote:
Leif Eriksen wrote:Not sure about your CPI-U numbers. Are you using the All City Average? The spreadsheet reports 3.84 for 2008. U.S. Department Of Labor Bureau of Labor Statistics reports 0.1 for Dec/Dec 2008. See the following link

ftp://ftp.bls.gov/pub/special.requests/cpi/cpiai.txt


I use the Avg-Avg for the CPI-U numbers.
CPI-U for 2008= (Annual Avg for 2008 - Annual Avg for 2007)/(Annual Avg for 2007)

CPI-U for 2008 = (215.303 - 207.342) / 207.342 = 0.03839 = 3.84%

Best Regards,
Simba


Hi Simba,
Here's a vote for using the more traditional definition of inflation- i.e. the government definition that results in 0.1% inflation for 2008. It's more consistent with how we define yearly asset class returns, and hence easier to calculate real returns.
For example during 2008 the S&P 500 return was -37.0% and 2008 inflation was 0.1%, so the real return of the S&P 500 was -37.1%, etc.
just my 2 cents,
cheers,


First of all there should be consistency. If average annual return series is used then average annual inflation series should be used.

But aside from that I believe it is mathematically more correct to use average numbers for a period. For instance if you have monthly data you would use monthly average rather than picking a one day like the last day of the month. Otherwise you get biases in the data caused by aliasing. In more simple terms, the average for a month is more representative of that month that a particular day.

So for annual data the series to use should be annual average.
MachineGhost
 
Posts: 55
Joined: Sat Feb 14, 2009 3:46 am

Postby grayfox » Sun Feb 15, 2009 4:22 am

MachineGhost wrote:But annual data isn't an average, it is ( 12/31/xx divided by 12/31/xx-1 ) -1 or YTD as of 12/31/xx.


How do you know that? The spreadsheet data only has a number for each annual return. It doesn't calculate it from prices within the spreadsheet. You would have to go look at the data source and see how the return was calculated.

For stocks supposed you calculated the annual return as (P1-P0+D0)/P0
The Ps could be the price on 12/31 or 7/1 or the average yearly price. It depends on the data source.

Now a typically person wants to know the return he got from one date to another like 12/31/07 to 12/31/08 so he would use the closing price on the last day of the year. But mathematically it results in biases in the statistics because one random day is not representative of the whole year. Pick a different day and you'll get different results.

On the other hand this spreadsheet is more about getting accurate statistics for different portfolios over various periods. So it probably is better to use annual averages rather than picking an arbitrary day.
User avatar
grayfox
 
Posts: 3945
Joined: Sat Sep 15, 2007 5:30 am
Location: Anytown, USA

Re: Some corrections

Postby simba » Mon Feb 16, 2009 1:27 am

Paul Douglas Boyer wrote:Simba, in your spreadsheet, here are some corrections:

Cell SP-85-08!C35 should read "=RATE(Portfolio!$K...
Cell SP-85-08!C36 should read "=RATE(Portfolio!$K...
Column P in SP-85-08 incorrectly points to 72 data.

Also,

I have the values for gold in the Google version of the spreadsheet at:
https://spreadsheets.google.com/ccc?key=pOjc3ot10vgs0eml-DJZKcw&newcopy

I added a returns vs risk chart for 1972 - 2008
and compared against Harry Browne, Coffeehouse, Scott Burns Four Square, and all of the IFA Index Portfolios there.


Thanks Paul - The next version has the corrections. I have added Gold as well.
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby simba » Mon Feb 16, 2009 1:31 am

grok87/grayfox/MachineGhost,

Earlier my thoughts on this was we use goods/services throughout the year so I used the avg-avg prices to calculate inflation.

But as you have mentioned, the industry and Govt standard is to use Dec-Dec prices to report inflation.

I have updated the spreadsheet to reflect the same. I am making another change to the SS and will upload it sometime tomorrow.

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

Re: Some corrections

Postby simba » Mon Feb 16, 2009 1:41 am

grayfox wrote:
MachineGhost wrote:There is also a bug with deducting the ER present in rev7f and rev7h on the two Data tabs. Although the formula looks logical in English, it is actually being applied in reverse, i.e. if the current year is greater than the YOI, the ER will not be deducted!


It looks like MachineGhost is right. The ER is not deducted.

I wonder why you would want to deduct the ER only for years after YOI?
New funds are getting a free ride with zero ER for the years before they were started. Older funds are paying the ER from the beginning.


If the returns are for a mutual fund then the ER is already deducted. I deduct the ER if the returns are for the benchmark or not true fund returns.

Honestly this was not easy since the ER was not the same since 1972/1985. But the spreadsheet allows the user to change the ER and the fund returns are automatically updated based on the ER and YOI.

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

What Correlates With CPI?

Postby Paul Douglas Boyer » Mon Feb 16, 2009 5:14 pm

What asset classes correlate closest with inflation (CPI)?

I took the CPI numbers and added them to the correlation matrix (1972-2008) in the spreadsheet, did a sort, and here is how it turns out:
(1 means perfect correlation, 0 means perfect non-correlation. Note that a negative correlation means the investment goes down when inflation goes up, right? So bet against it in times of inflation. )
Code: Select all
Asset Class       Ticker   Correlation
TBILL              VMPXX  0.63
GOLD               GOLD   0.52
LTGB               VUSTX -0.40
ST Trsry           VFISX  0.28
Commodities      PCRIX  0.25
5 Yr T                VFITX -0.24
Wellesley           VWINX -0.21
Wellington           VWELX -0.16
SCG                 VISGX  0.13
Total Bond           VBMFX -0.12
Small Cap           NAESX  0.11
EAFED              VDMIX -0.10
Europe             VEURX -0.10
Intl Value          VTRIX -0.10
EAFE85/EM15      EAFE/EM -0.09
500 Idx             VFINX -0.09
LCG                  VIGRX -0.07
Total Market US    VTSMX -0.06
Simulated TIPS    S-TIPS  0.06
Pacific              VPACX -0.06
EM                   VEIEX -0.06
LCV                 VIVAX -0.04
SCV                 VISVX  0.04
REIT                VGSIX -0.01
Windsor           VWNDX -0.01
Mid Cap           VIMSX -0.01
Micro Cap         BRSIX  0.00


Is the take away here that if one enters a long period of higher inflation that the asset classes of T-Bills, Gold, and shorting the Long-Term bond would work best? Or might stocks still outperform even though they are not as closely correlated?

Or what else can we take away from these correlation stats as pertains to investment performance?
Paul Douglas Boyer, host, MadMoneyMachine.com
User avatar
Paul Douglas Boyer
 
Posts: 127
Joined: Wed Mar 07, 2007 4:19 pm
Location: Leesburg, VA

Postby grok87 » Mon Feb 16, 2009 6:04 pm

simba wrote:grok87/grayfox/MachineGhost,

Earlier my thoughts on this was we use goods/services throughout the year so I used the avg-avg prices to calculate inflation.

But as you have mentioned, the industry and Govt standard is to use Dec-Dec prices to report inflation.

I have updated the spreadsheet to reflect the same. I am making another change to the SS and will upload it sometime tomorrow.

Best Regards,
Simba

Great Simba- thanks!
cheers,
grok, CFA | Danon delenda est
grok87
 
Posts: 6453
Joined: Tue Feb 27, 2007 10:00 pm

Re: What Correlates With CPI?

Postby grayfox » Tue Feb 17, 2009 2:10 am

Paul Douglas Boyer wrote:What asset classes correlate closest with inflation (CPI)?


Paul--that is an interesting question, but this is the wrong thread to discuss it. This thread is really about updates to the backtest spreadsheet, fixing errors, etc.

I would suggest starting a new thread and posting those results. Make it a poll. It is worthy of discussion.
User avatar
grayfox
 
Posts: 3945
Joined: Sat Sep 15, 2007 5:30 am
Location: Anytown, USA

Postby Anagoge » Thu Feb 26, 2009 2:04 pm

simba wrote:I have updated the spreadsheet to reflect the same. I am making another change to the SS and will upload it sometime tomorrow.


Simba, did you have any luck completing your updates? I'm particularly interested in investigating the addition of a small amount of gold to a portfolio over time. Thanks!
Anagoge
 
Posts: 3
Joined: Thu Feb 26, 2009 1:59 pm

Postby Cb » Wed Mar 11, 2009 12:11 pm

Image
User avatar
Cb
 
Posts: 268
Joined: Wed Feb 21, 2007 1:39 am

Postby simba » Sun Mar 15, 2009 8:37 am

I've updated the spreadsheet and for those interested, You can download the Excel Spreadsheet [rev8c] and/or OpenOffice version [rev8c]

Changes include - Added Gold returns, Now using month-month CPI instead of avg-avg. New worksheet for comparing Lazy Portfolios.

Sorry for the delay but have been concentrating on the big rocks in my Life (DW gave birth to a precious little girl) :)

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

Postby Rager1 » Sun Mar 15, 2009 10:15 am

Congratulations on the new baby girl! Also, thank you for the latest version of this fabulous spreadsheet.

Ed
User avatar
Rager1
 
Posts: 802
Joined: Fri Jun 01, 2007 1:03 pm

Postby blackball » Sun Mar 15, 2009 11:38 am

simba wrote:I've updated the spreadsheet and for those interested, You can download the Excel Spreadsheet [rev8c] and/or OpenOffice version [rev8c]

Changes include - Added Gold returns, Now using month-month CPI instead of avg-avg. New worksheet for comparing Lazy Portfolios.

Sorry for the delay but have been concentrating on the big rocks in my Life (DW gave birth to a precious little girl) :)

Best Regards
Simba


Thanks for the update!

BTW, where did you get the returns for Gold? I don't see it in the data sources worksheet.
blackball
 
Posts: 99
Joined: Thu May 01, 2008 10:36 pm

Postby AboveBeyond » Sat Mar 21, 2009 7:33 pm

Appreciate the backtest update Simba! :P
AboveBeyond
 
Posts: 21
Joined: Tue May 27, 2008 11:58 am

Awesome sheet! Other asset classes?

Postby oyz79 » Thu Apr 02, 2009 9:13 pm

Awesome work on the spreadsheet. Is the data available for the following asset classes:

Corporate Bond (investment grade)
International Bond
International Small Cap Value and/or International Small Cap
oyz79
 
Posts: 3
Joined: Thu Apr 02, 2009 9:09 pm

OpenOffice 3.01 Problem?

Postby oldDogNewTrick » Sat Apr 04, 2009 2:27 pm

Thanks very much for this tool. Very helpful!

I seem to be having one minor problem with the .ods version using OpenOffice 3.0.1... the 3 pairs of graphs between lines 69 and 145 of the Portfolio worksheet contain no data or x-axis labels. Is this possibly a version incompatibility?

Regards,
OldDog
oldDogNewTrick
 
Posts: 1
Joined: Sat Apr 04, 2009 1:55 pm

Postby rwwoods » Wed Jul 08, 2009 9:23 am

Simba, please correct a couple of errors in your excellent spreadsheet.

Portfolio Sheet: The CHP captions in the left two graphs reference the older Returns 72-07 sheet.

Returns 72-08 sheet: Cell AN43 is blank and needs to be filled in.
"I'm not so much concerned about the return on my money as the return of my money" - Will Rogers
rwwoods
 
Posts: 1312
Joined: Thu Jun 07, 2007 8:54 pm
Location: The Villages, Florida

Postby Chas » Thu Jul 09, 2009 7:51 pm

Hi Simba,

I have been using your latest version 8c. I know how burdensome upgrading and debugging a major enterprise like your spreadsheet can be. I have noticed various bugs and problems and I have dusted off my rusty 'ol Excel skills and just started corrections myself. However, it would be good to keep the application together so I wanted to ask if you would like some help like maybe we could coauthor or if you are weary with the project I would be interested in picking up the load for you. At any rate if you just wanted to use my help I would have to start a log book and all. My offer is all just in the spirit of fun of course. Let me know.

Thanks,
Chas | | The course of true love never did run smooth. Shakespeare
User avatar
Chas
 
Posts: 846
Joined: Sat Mar 24, 2007 9:41 pm
Location: America

Updated version rev8d

Postby simba » Mon Jul 20, 2009 4:21 pm

An updated version rev8d is now available for those interested.

You can download the Excel Spreadsheet [rev8d] and OpenOffice version [rev8d]

This version includes some bug fixing ; updated sources

MrMatt2532 - I am still looking into the Sortino ratio issue you PM'd me about. Although to be consistent I am using the MAR across the board in the rev8d spreadsheet.

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

Updated version rev8e

Postby simba » Thu Jul 23, 2009 3:07 pm

An updated version rev8e is now available for those interested.

You can download the Excel Spreadsheet [rev8e] and OpenOffice version [rev8e]

This version fixes the Sharpe Ratio formula to calculate the average of TBills for the time period being tested. It also fixes the returns for 2008 in Returns_85_08 worksheet.

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

Postby stratton » Thu Jul 23, 2009 8:10 pm

Simba,

Thanks for putting in the work on this. Enjoy the newborn!

Paul
User avatar
stratton
 
Posts: 10796
Joined: Sun Mar 04, 2007 6:05 pm
Location: Puget Sound

Postby Chas » Fri Jul 24, 2009 10:31 am

Simba,

I don't know how I overlooked this for so long, but there is a mismatch of the two arrays in the correlation formulas for the calculation of the CHP and Scot Burns portfolios for both the U.S. and Int'l Market. The correlations as listed on the portfolio sheet of versions 8c, 8d, and 8e are grossly in error.

Sorry to be the bearer of bad news, for I know how frustrating it is to put out a new release and have a bug crop up the very next day or so. Still, I can't even begin to say how valuable your work has been to me.

Thanks,
Chas | | The course of true love never did run smooth. Shakespeare
User avatar
Chas
 
Posts: 846
Joined: Sat Mar 24, 2007 9:41 pm
Location: America

Postby simba » Tue Jul 28, 2009 12:31 am

An updated version rev8f is now available for those interested.

You can download the Excel Spreadsheet [rev8f] or the OpenOffice version [rev8f]

rev8f - Fixed Correlations issues; Updated Sortino Ratio formula ; Added the ability to change the time period for comparing portfolios ; Added the ability to change the time period for comparing Lazy Portfolios. See the Revision History in the spreadsheet for complete list of changes

I am happy to inform that a user now has the ability to change the time period when comparing the portfolios in the Portfolio worksheet. Implemented the same feature for comparing the Lazy Portfolios.

Thanks to MrMatt2532 who informed me about the incorrect Sortino Ratio formula.

Chas - Hopefully you won't find any correlation issues in the new version ;)

Feedback is very much appreciated (especially if its the +ve kind) :D

Edit: Sortino Ratio is incorrect in Returns_72_08 and Returns_85_08 (Missing the *100 in part of the formula) but the formula is correct in the rest of the spreadsheet. I fixed this prior to uploading the 8f version but it looks like I uploaded the incorrect version. To avoid confusion, I'll upload rev8g in couple of days.

Best Regards,
Simba
Last edited by simba on Tue Jul 28, 2009 9:30 am, edited 1 time in total.
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby stratton » Tue Jul 28, 2009 12:47 am

simba wrote:Feedback is very much appreciated (especially if its the +ve kind) :D

Thanks for adding more blank entries. I have lots of oddball data sets for various things and its easier to play with them if there is more slots.

I'm tempted to go to the barra.com site and create sets in Euros, Yen and GBP sometime for some research.

Paul
User avatar
stratton
 
Posts: 10796
Joined: Sun Mar 04, 2007 6:05 pm
Location: Puget Sound

Postby simba » Thu Aug 06, 2009 4:12 pm

An updated version rev8h is now available for those interested.
See the Revision history in the README worksheet for a list of changes.

You can download the Excel Spreadsheet [rev8h] or the OpenOffice version [rev8h]

A special thanks to Al(acr123) and Matt (MrMatt2532).

Cheers,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Postby palffy » Wed Aug 19, 2009 8:10 am

simba, first of all, thanks for all of the work you're doing, spreadsheet looks great! do you mind making sure that the BRSIX data for 2008 is correct? It may be -39.49 instead of the -44.61 currently listed...
palffy
 
Posts: 6
Joined: Sun Mar 29, 2009 9:20 pm

Market Neutral

Postby goolsby » Mon Aug 24, 2009 10:51 am

All --

Can somebody add a market neutral or hedge fund allocation to the spreadsheet?
goolsby
 
Posts: 2
Joined: Mon Aug 24, 2009 10:48 am

Postby simba » Mon Aug 24, 2009 12:05 pm

palffy wrote:simba, first of all, thanks for all of the work you're doing, spreadsheet looks great! do you mind making sure that the BRSIX data for 2008 is correct? It may be -39.49 instead of the -44.61 currently listed...


I checked the returns for BRSIX. Yahoo and M* have the returns of 39.49 but the fund's website has the 2008 returns for BRSIX at -41.74. This matches the returns in their SEC filing.

I'll correct it to -41.74 in the next version of the spreadsheet.

Cheers,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Re: Market Neutral

Postby simba » Mon Aug 24, 2009 12:21 pm

goolsby wrote:All --

Can somebody add a market neutral or hedge fund allocation to the spreadsheet?


Welcome to the forum. You can add any fund to your local copy. You only need to have the returns of the fund you are trying to add. As you can see from the comments in the topic, it's pretty straight forward to add your own funds.

For Example, if you need to add the returns for Dodge and Cox's stock fund.

If you have the mutual fund returns from 1972-2008
Go to the Data_72_08 worksheet
1. Enter the name of the fund (D&C LCV)
2. Enter the symbol for the fund (DODGX)
3. Enter the ER (.52)
4. Enter the returns from 1972-2008 starting at cell AC8.

If you are using a hybrid of fund returns and synthetic or index returns,
1. Enter the name of the fund (D&C LCV)
2. Enter the symbol for the fund (DODGX)
3. Enter the ER (.52)
4. Enter the returns starting at cell AC65.

This ensures that the ER is deducted from the synthetic/index returns.

For 1985-2008 returns
Go to the Data_85_08 worksheet
1. Enter the name of the fund (D&C LCV)
2. Enter the symbol for the fund (DODGX)
3. Enter the ER (.52)
4. Enter the returns from 1985-2008 starting at cell AP8.

If you are using a hybrid of fund returns and synthetic or index returns,
1. Enter the name of the fund (D&C LCV)
2. Enter the symbol for the fund (DODGX)
3. Enter the ER (.52)
4. Enter the returns starting at cell AP55.

Now you can go the Portfolio tab of the spreadsheet and add allocation to the new fund you just added.

Hope that helps

Cheers,
Simba
User avatar
simba
 
Posts: 540
Joined: Mon Feb 19, 2007 8:47 pm

Tech Support

Postby goolsby » Mon Aug 24, 2009 3:02 pm

Simba,

Thanks. Not sure how to get the data into the spreadsheet for some of the different hedge fund indices (managed futures, equity market neutral, global macro, etc) since they are relatively new and data hard to find. Thought someone out there might have already built that into spreadsheet and willing to share.

Let me know if you can help.
goolsby
 
Posts: 2
Joined: Mon Aug 24, 2009 10:48 am

Re: Tech Support

Postby stratton » Mon Aug 24, 2009 6:49 pm

goolsby wrote:Simba,

Thanks. Not sure how to get the data into the spreadsheet for some of the different hedge fund indices (managed futures, equity market neutral, global macro, etc) since they are relatively new and data hard to find. Thought someone out there might have already built that into spreadsheet and willing to share.

Let me know if you can help.

Vanguard's market neutral fund goes back 10 years because they bought it from Laudus Rosenberg. Go to the funds web page and dig into the performance tab page.

Forget the hedge fund indexes. They are full of survivorship and backfill bias that Yale's David Swensen estimates is ~7.5% a year.

Rydex used to have a brochure on their web site for the S&P managed futures index they followed that had data back to 1985. The problem is its backtested and their fund tracks about 4% below the index.

Paul
User avatar
stratton
 
Posts: 10796
Joined: Sun Mar 04, 2007 6:05 pm
Location: Puget Sound

Postby Chuck T » Tue Sep 01, 2009 3:20 pm

Simba

Thank you for all the work you have done to develop and share this wonderful tool. It is great and I use it frequently. Chuck
Chuck | Past Performance Is Just That - bob | For info on the SC LowCountry & Savannah GA Area Bogleheads contact me at chucktanner46@gmail.com
Chuck T
 
Posts: 1062
Joined: Wed Sep 03, 2008 4:23 pm
Location: Lowcountry of South Carolina

Return vs. Risk charts

Postby Paul Douglas Boyer » Thu Jan 07, 2010 12:31 am

Simba,

For your consideration, I am linking to a slight enhancement to your spreadsheet which simply adds two charts, one for each time span, that plot the return vs. risk for the 5 portfolios under comparison. Take a look and include them in your official version if you wish. I hope you will, because this helps to quickly visualize a comparison of the portfolios on an equal basis.

Here is the spreadsheet:

http://MadMoneyMachine.com/podcasts/Backtest-Portfolio-returns-rev8i.xls

And here is what the charts looks like:

Image

I look forward to an updated version of the spreadsheet with 2009 returns. If you need any help compiling it, let me know.
Paul Douglas Boyer, host, MadMoneyMachine.com
User avatar
Paul Douglas Boyer
 
Posts: 127
Joined: Wed Mar 07, 2007 4:19 pm
Location: Leesburg, VA

Postby simba » Thu Jan 07, 2010 8:23 pm

Paul - Thanks for your suggestion.

I have added this to my spreadsheet.

I'll release the next version on or around 1/15 when the CPI data is released. I am also waiting for some other returns to be posted.

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

Postby Paul Douglas Boyer » Thu Jan 07, 2010 9:52 pm

simba wrote:I have added this to my spreadsheet.


I have already found an error: I used Average instead of CAGR in the charts.

Here is a link to a fixed version. I have also gone through Vanguard's site to add in their 2009 returns. If you build on this spreadsheet, you may wish to double check the Vanguard returns and especially the Non-Vanguard returns.
And I flipped a couple of 2008 to 2009 switches, but not sure if I got them all.

http://MadMoneyMachine.com/podcasts/Backtest-Portfolio-returns-rev8j.xls

I put a chart in the Lazy_Portfolios_85 tab, feel free to delete it since it does not include all of the portfolios in the table.
Paul Douglas Boyer, host, MadMoneyMachine.com
User avatar
Paul Douglas Boyer
 
Posts: 127
Joined: Wed Mar 07, 2007 4:19 pm
Location: Leesburg, VA

PreviousNext

Return to Investing - Theory, News & General

Who is online

Users browsing this forum: Baidu [Spider], chickadee, rixer, Rosebud, Yahoo [Bot] and 34 guests