Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Posts: 61073
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

siamond wrote:
LadyGeek wrote:siamond- I understand the need for geometric math, but I'm having some difficulty to help. Can you please show the first few steps to derive that "little bit of algebra"?
Sure. Let's start from the formula that clearly represents a geometric way to subtracting E from R.
(1+R)/(1+E) - 1
Which is the same as:
(1+R)/(1+E) - (1+E)/(1+E)
Which is the same as:
((1+R) - (1+E)) / (1+E)
Which is the same as:
(R-E) / (1+E)

Makes sense?

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

- The formula for subtraction is also called Relative Return / Excess Return (Geometric). It took some thinking to realize the same formula can be used for any geometric difference (subtraction, which is a ratio in geometric terms) - not just the benchmark shown in the example. Once I realized that, I could understand what you were up to.

- Multiplying by 1, which is substituting the "- 1" with "-(1+E)/(1+E)" to derive the equation.

===================
Since this thread is now on a new page, Kevin M's top-level overview on the exponential part is repeated here. It's helpful to read this first, then siamond's part. CAGR is a very important concept, as it's used for apples-to-apples performance comparisons between two investments.
Kevin M wrote:To add a bit more to the geometric math subtraction of ER ...

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

(1 + r) ^ N

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

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

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

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

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

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

Kevin
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

I am in the process of completely restructuring the spreadsheet, this will have relatively little impact on what is displayed in the user-oriented tabs, but all the formulas and all the under-the-hood math will be streamlined in a much more consistent manner. Fact is it was quite challenging to maintain in its previous state, due to years of accumulated history, plus suboptimal use of numerous defined names.

Here is my working draft, if somebody wants to play around and provide feedback.

This is all going well, but when I ran a quick test with LibreOffice (I didn't try OpenOffice), I stumbled upon a few annoying issues:
1. the Excel-level conditional formatting is lost in the conversion (darn it, I was pleased by an increased use of it to pinpoint error entries in the portfolios of interest, and color-code the portfolio comparisons).
2. as previously discussed, the checkboxes get clunky (displaying boolean values as numbers to start with, then it gets better as you click on the checkboxes). Nothing blocking, this still works, but it's a tad confusing. Default values now seem to stay what they should be though.
3. the tight positioning of various text boxes in the charts sometimes gets slightly messed up - not too bad, but still annoying.

I'd venture to guess that Problems #2 and #3 aren't that troublesome. Problem #1 could be mitigated by adding explicit error-detection rows and columns (the previous spreadsheet was doing exactly that, this is less elegant though).

Now I have a question. Are there many people actually using the spreadsheet with LibreOffice or OpenOffice? I mean by that a full use of the spreadsheet, not just taking a copy of the raw historical returns to do your own thing. Please let me know.

EDIT: I found a clean solution for what I previously described as Problem #4. The Rolling Returns chart seems to work well with LibreOffice now.

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

siamond wrote:I am in the process of completely restructuring the spreadsheet
Everything seems to work as expected in Google Sheets with the exception of charts in Analyze_Portfolio which seem to have N/A all over the place.

midareff
Posts: 6613
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

excel use only.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

(missed the point... post deleted)
Last edited by siamond on Wed Feb 22, 2017 11:48 pm, edited 1 time in total.

Kevin M
Posts: 11362
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

AlohaJoe wrote:
siamond wrote:I am in the process of completely restructuring the spreadsheet
Everything seems to work as expected in Google Sheets with the exception of charts in Analyze_Portfolio which seem to have N/A all over the place.
Wow, I'm (pleasantly) surprised that anything works at all in Google Sheets! I understand that only Excel and its clones are supported, but it's very cool that the changes siamond has made have gotten it closer to Google Sheets compatibility.

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

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Kevin M wrote:
AlohaJoe wrote:
siamond wrote:I am in the process of completely restructuring the spreadsheet
Everything seems to work as expected in Google Sheets with the exception of charts in Analyze_Portfolio which seem to have N/A all over the place.
Wow, I'm (pleasantly) surprised that anything works at all in Google Sheets! I understand that only Excel and its clones are supported, but it's very cool that the changes siamond has made have gotten it closer to Google Sheets compatibility.
Oh, I'm sorry, I totally misunderstood AlohaJoe's post. I missed the 'Google Sheets' part!

Yes, I gave it a quick try, and it appears that it opens fine (which is progress!), and that the math works ok (albeit quite slowly), and the various metrics are computed as they should, and pleasant surprise, the conditional formatting works! The graphs are mostly screwed up though. Don't think I can do much about that.

chas3
Posts: 2
Joined: Mon Mar 06, 2017 8:26 am

siamond wrote:Now I have a question. Are there many people actually using the spreadsheet with LibreOffice or OpenOffice? I mean by that a full use of the spreadsheet, not just taking a copy of the raw historical returns to do your own thing. Please let me know.
I use LibreOffice for everything. Used to use OpenOffice for years until I found it became dormant.
The changes in rev16c (March 2) are quite an improvement. I found a calc issue with rev16b in the Not-Rebalanced total of the Analyze Tab and then found the 16c fixed it. The reformatting you did is great.
I am used to slight rendering differences/quirks due to Libre, but it is worth the savings. (although I don't always know there is a difference since I don't usually see it in Excel.)

One minor thing I noticed with 16c March2...On the Analyze_Portfoilio Tab, The right hand Rolling Returns chart, the check box to turn on/off the 10 year rolling returns does not work...the 10 year always displays. The left hand chart works fine.
Edit: ooops...already fixed in March 5 rev with the drop down option cells to pick what shows in each chart...nice.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

chas3 wrote:I use LibreOffice for everything. Used to use OpenOffice for years until I found it became dormant.
The changes in rev16c (March 2) are quite an improvement. I found a calc issue with rev16b in the Not-Rebalanced total of the Analyze Tab and then found the 16c fixed it. The reformatting you did is great.
I am used to slight rendering differences/quirks due to Libre, but it is worth the savings. (although I don't always know there is a difference since I don't usually see it in Excel.)

One minor thing I noticed with 16c March2...On the Analyze_Portfoilio Tab, The right hand Rolling Returns chart, the check box to turn on/off the 10 year rolling returns does not work...the 10 year always displays. The left hand chart works fine.
Edit: ooops...already fixed in March 5 rev with the drop down option cells to pick what shows in each chart...nice.
Thanks a lot for the feedback, this is encouraging. LibreOffice compatibility gave me quite some grief, but I think I finally found a way to make it work significantly better (looks like you already found it, with the March 5th update). I still have a few things to improve and to add before officializing this new version, but it appears that we're getting there.

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

May I suggest we rename this spreadsheet so it sounds less amateurish? It also might attract more users. I would suggest something like "The Boglehead's Unofficial Backtesting Spreadsheet" and start a new thread for it also.

Posts: 61073
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

I disagree, as it's important to keep the development history preserved. "Simba" is rather unique and provides a simple keyword for a google search.

Starting a new thread is also discouraged. Once the information is spread across multiple threads, continuity is lost. (This is the same approach taken for new investors' portfolio questions. Keeping everything in one spot ensures we have all the information to provide appropriate guidance.)
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

I would tend to agree with Ladygeek that we should keep the moniker 'Simba'. I do agree that the title of the thread (and also the Simba Wiki page) should be reworked towards something more explicit, and more 'official looking'. This spreadsheet only performs some basic backtesting, there are many things that would require a separate spreadsheet (e.g. rebalancing algorithms, TAA algorithms, withdrawal algorithms, accumulation/DCA algorithms, name it). Actually, its core value isn't so much the computations it performs, but more to be a record keeper of (annual) historical returns. Such historical returns are used in turn within the spreadsheet itself, but also externally (e.g. tools like PortfolioCharts and PortfolioVisualizer) as well as in various private spreadsheets, I'm sure (including a few of mine!).

Maybe rename the thread: "Simba's historical returns and backtesting spreadsheet"? A bit of a mouthful.

Maybe focus on the truly salient point: "Simba's historical returns spreadsheet" (leaving implicit the fact that the spreadsheet comes with some basic backtesting tools to analyze such numbers - will be explained in the Wiki page and the spreadsheet itself)?

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

I like "simba" because it makes it easier to search for, both on Google and on my local computer.

But I like MachineGhost's suggestion of making it clearer it has evolved into a fairly sophisticated project over the years. A parenthetical "Bogleheads project" seems like a good, non-controversial change.

Posts: 61073
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

Got it. The title is changed to: "Simba's backtesting spreadsheet [a Bogleheads community project]"

This represents a continuation in the spirit of the wiki and blog, a community effort, and removes the impression of an amateurish effort. Not to mention keeping the title to a reasonable length.

The wiki page title should not be changed (not needed), but the link back to this thread is revised: Simba's backtesting spreadsheet

If anyone has a better suggestion, let's discuss.

(What's another community project? This: Bogleheads® financial literacy project)
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Fine with me. I like the 'community' wording. I guess I will add more emphasis on the historical returns aspect in the Wiki page then -- when I get to rework it, due to the structural changes coming with the next version (will probably upload tonight or tomorrow).

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

I finally made official the last version (v16c). You can find highlights and download details in this post.

Quite a few changes occurred, mostly under the hood, plus some new functionality. Many thanks to MachineGhost, Dcabler, Tyler9000, LadyGeek, Chas3 and others for very useful feedback on early drafts. As usual, feedback welcome, either on this thread or via PM.

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

Re 16c.
Using win10, MS2007 excel and Monday's 16c, when I go to the Compare_Portfolios sheet, beta row 114 (and alpha row 97) shows as #Name?. If I then transfer the file via thumb drive to my decrepit linux laptop, with libreoffice v5.3 installed, and save as ods, the calculations are then completed correctly. Is this a local (my) problem, or an artifact of Google?
Overall nice work.

jc6309

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

jc6309 wrote:Using win10, MS2007 excel and Monday's 16c, when I go to the Compare_Portfolios sheet, beta row 114 (and alpha row 97) shows as #Name?. If I then transfer the file via thumb drive to my decrepit linux laptop, with libreoffice v5.3 installed, and save as ods, the calculations are then completed correctly. Is this a local (my) problem, or an artifact of Google?
Ah, good catch, thanks a lot for the feedback. I used a pre-canned formula to compute Beta (and Alpha depends on Beta). And this formula made use of post-Excel-2007 functions like VAR.P and COVAR.P... I came back to the more regular VAR() and COVARIANCE() functions, which should solve the problem. The computed beta is slightly different, but as long as you compare values calculated in a consistent manner, this shouldn't matter.

If you download the spreadsheet again, the problem should be solved. Please let me know if you find other issues with Excel 2007.

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

But the formula is still approximate, right? In the attached spread sheet I have a stylized example where the gross return is 10%, the expense ratio is 2% and we invest 1,000 USD for 1 year (252 trading days). If the formula is meant to be exact, where do I go wrong in my spreadsheet (ie. why is cell G8 different from cell G10)?

siamond wrote:
LadyGeek wrote:siamond- I understand the need for geometric math, but I'm having some difficulty to help. Can you please show the first few steps to derive that "little bit of algebra"?
Sure. Let's start from the formula that clearly represents a geometric way to subtracting E from R.
(1+R)/(1+E) - 1
Which is the same as:
(1+R)/(1+E) - (1+E)/(1+E)
Which is the same as:
((1+R) - (1+E)) / (1+E)
Which is the same as:
(R-E) / (1+E)

Makes sense?

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

- The formula for subtraction is also called Relative Return / Excess Return (Geometric). It took some thinking to realize the same formula can be used for any geometric difference (subtraction, which is a ratio in geometric terms) - not just the benchmark shown in the example. Once I realized that, I could understand what you were up to.

- Multiplying by 1, which is substituting the "- 1" with "-(1+E)/(1+E)" to derive the equation.

===================
Since this thread is now on a new page, Kevin M's top-level overview on the exponential part is repeated here. It's helpful to read this first, then siamond's part. CAGR is a very important concept, as it's used for apples-to-apples performance comparisons between two investments.
Kevin M wrote:To add a bit more to the geometric math subtraction of ER ...

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

(1 + r) ^ N

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

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

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

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

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

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

Kevin

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

larssl780 wrote:But the formula is still approximate, right? In the attached spread sheet I have a stylized example where the gross return is 10%, the expense ratio is 2% and we invest 1,000 USD for 1 year (252 trading days). If the formula is meant to be exact, where do I go wrong in my spreadsheet (ie. why is cell G8 different from cell G10)?
You know, those investment companies, they like to take their money right away...

You should have subtracted the daily expenses from the capital every day, and used the outcome at the end of the 252 days period, instead of computing the expenses on their own and combine at the end of the year.

Anyhoo, the Simba formula is approximate for sure. The gross return doesn't come in a perfectly linear way every day, the price goes up and down in an arbitrary manner during the year, and since the 'net return' is affected by both the daily price and the ER being subtracted, the exact outcome cannot be known without having access to daily return series - which we don't have for most years of history.

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

Rookie mistake, thanks for your patience!
siamond wrote: You know, those investment companies, they like to take their money right away...

You should have subtracted the daily expenses from the capital every day, and used the outcome at the end of the 252 days period, instead of computing the expenses on their own and combine at the end of the year.

Anyhoo, the Simba formula is approximate for sure. The gross return doesn't come in a perfectly linear way every day, the price goes up and down in an arbitrary manner during the year, and since the 'net return' is affected by both the daily price and the ER being subtracted, the exact outcome cannot be known without having access to daily return series - which we don't have for most years of history.

sean.mcgrath
Posts: 169
Joined: Thu Dec 29, 2016 6:15 am
Location: US in NL

Hi Siamond,

I'm making my way through the spreadsheet, and it is really a work of art -- beautiful!

I do have a question on the naming of "SD vs. MAR" cell: I had expected that MAR refers to market, but digging deeper discovered it's the 30 day T-bill. Is MAR a standard abbreviation for this? It might be worth adding the definition to the "Data Sources" tab, or in the comments to SD vs. MAR etc., as currently MAR is not defined anywhere that I could find.

cheers,
Sean

Edit: I see now that it is called minimum acceptable return in the cell above. Still suggest that it is defined somewhere as the TBill / risk free return.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

sean.mcgrath wrote:I'm making my way through the spreadsheet, and it is really a work of art -- beautiful!
A work of art? LOL, thank you!
sean.mcgrath wrote:I do have a question on the naming of "SD vs. MAR" cell: I had expected that MAR refers to market, but digging deeper discovered it's the 30 day T-bill. Is MAR a standard abbreviation for this? It might be worth adding the definition to the "Data Sources" tab, or in the comments to SD vs. MAR etc., as currently MAR is not defined anywhere that I could find.

cheers,
Sean

Edit: I see now that it is called minimum acceptable return in the cell above. Still suggest that it is defined somewhere as the TBill / risk free return.
Yes, MAR means Minimum Acceptable Return, this is the terminology used in the definition of the Sortino ratio by corresponding authors (it is also known as 'required rate of return' or 'desired rate of return'). The use of T-Bill returns is just a default setting for it. People who are keenly interested in this kind of ratio can actually customize the definition of MAR in the Portfolio_Math tab. This is documented (maybe too succinctly?) in the README tab, around line 45.

Here are a couple of useful pointers:
https://en.wikipedia.org/wiki/Sortino_ratio
http://www.redrockcapital.com/Sortino__ ... apital.pdf

I have been debating with myself to provide more educational material about the various financial metrics which are used in this spreadsheet. Maybe just a one liner, complemented by a couple of pointers to reference material. It is a bit tricky to get it right though, and I'd rather not duplicate Wiki material in this spreadsheet! Still, this type of feedback reinforces the idea...

sean.mcgrath
Posts: 169
Joined: Thu Dec 29, 2016 6:15 am
Location: US in NL

siamond wrote:
sean.mcgrath wrote:I'm making my way through the spreadsheet, and it is really a work of art -- beautiful!
This is documented (maybe too succinctly?) in the README tab, around line 45.

It is a bit tricky to get it right though, and I'd rather not duplicate Wiki material in this spreadsheet! Still, this type of feedback reinforces the idea...
So it is in there after all!

Thanks, yes it is impossible to have exactly the right level of documentation for all the users of this spreadsheet of course. Just from my experience, what got me on the wrong path was STDEV (Portfolio - MAR), as I just assumed it was the excess SD to the market. When that didn't make sense I went to the Data Sources.

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

siamond

Per your gracious Mar 15 response to my MS Office 2007 Excel problem wherein you reverted the correlation and covariance functions to their pre-Office 2010 status, I have a question as to whether this was the proper long term approach? My tactic, after wrestling with the chagrin of using an antique MS Office version, was to download LibreOffice v 5.3.2 onto a decent computer (Win10, not linux at this point) and to start using that. I thought this preferable to the worst-case alternative of a return to VisiCalc

jc6309

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

jc6309 wrote:Per your gracious Mar 15 response to my MS Office 2007 Excel problem wherein you reverted the correlation and covariance functions to their pre-Office 2010 status, I have a question as to whether this was the proper long term approach? My tactic, after wrestling with the chagrin of using an antique MS Office version, was to download LibreOffice v 5.3.2 onto a decent computer (Win10, not linux at this point) and to start using that. I thought this preferable to the worst-case alternative of a return to VisiCalc
Actually, your comment helped me make statistical formulas more consistent, so I don't see it as a step backward, but a step forward. Also I do know that many people still use Excel 2007, you're not exactly alone, and I'd rather keep that working. As to Visicalc compatibility, now that would be an interesting challenge!

Please let me know how you're doing with LibreOffice. I try to maintain solid compatibility with it, but I know this isn't perfect. Plus I use Excel on a day to day basis, not LibreOffice, so getting the feedback of a regular LibreOffice user would be appreciated.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

siamond wrote:
sean.mcgrath wrote:I do have a question on the naming of "SD vs. MAR" cell: I had expected that MAR refers to market, but digging deeper discovered it's the 30 day T-bill. Is MAR a standard abbreviation for this? It might be worth adding the definition to the "Data Sources" tab, or in the comments to SD vs. MAR etc., as currently MAR is not defined anywhere that I could find.
[...]
I have been debating with myself to provide more educational material about the various financial metrics which are used in this spreadsheet. Maybe just a one liner, complemented by a couple of pointers to reference material. It is a bit tricky to get it right though, and I'd rather not duplicate Wiki material in this spreadsheet! Still, this type of feedback reinforces the idea...
I tried to improve the educational aspect of the spreadsheet (see the README tab in the spreadsheet):
1. adding a glossary listing the various technical terms, and providing links to reference material (e.g. Wikipedia, Investopedia). In a few cases, I added some comment on how those metrics are used in the spreadsheet. I voluntarily stayed shy of paraphrasing any definition though.
2. and adding some explanations about the growth/telltale charts and various ways to use those (a couple of users asked for it)

Here is my working spreadsheet (temporary link, content may change as it's being refined), feedback welcome:

PS. I also added a couple of new charts in the Lazy_Portfolios worksheet. It struck me that this worksheet can be very handy for comparing any large number of portfolios (I did something like that for the 'Investing in the World' study on Financial Page).

sean.mcgrath
Posts: 169
Joined: Thu Dec 29, 2016 6:15 am
Location: US in NL

I like the glossary -- it's clear, and I think I would have looked there with my question.

Yes, the chart is useful. I made something similar in my own version, and expect other people do as well. This avoids double work.

Speaking of double work, I do understand that you can't include the F-F data in the spreadsheet due to copyright, but would it be an idea to make a place for it (i.e., even with column names, along with the links to the data that you already have). Assuming it didn't slow the spreadsheet down too much, eventually there could be ready-made formulas (even graphs) waiting for the data. My main worry would be spreadsheet speed, though, as my own F-F can get laggy.

Oh, one other thought: it might be an idea to put a warning by "maximum drawdown" that this comes from annual data and can understate actual drops. I was a bit surprised when I started to use monthly data at how large the difference can be.

Taylor Larimore
Posts: 29190
Joined: Tue Feb 27, 2007 8:09 pm
Location: Miami FL

With my simple Vanguard 3-fund portfolio I manage to retire comfortably without a spreadsheet.

Best wishes.
Taylor
"Simplicity is the master key to financial success." -- Jack Bogle

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

sean.mcgrath wrote:Speaking of double work, I do understand that you can't include the F-F data in the spreadsheet due to copyright, but would it be an idea to make a place for it (i.e., even with column names, along with the links to the data that you already have). Assuming it didn't slow the spreadsheet down too much, eventually there could be ready-made formulas (even graphs) waiting for the data. My main worry would be spreadsheet speed, though, as my own F-F can get laggy.
All the formulas are preset to deal with additional data series. You just need to follow the instructions I provided at the end of the README section to add your own data series. There are MANY data series in the Prof. French data library. Just adding the names of a select few wouldn't seem terribly helpful? Let me know by PM if you have troubles with the instructions -- after doing it once, it will become very easy for you to do it again.

As a side note, if you're thinking about the 2x3 matrix and the SCV/MCV/etc data series, the way Prof. French breaks it down is *VERY* academic and bears little resemblance to what an actually fund (or index) would do. To better understand what I am saying, I would encourage you to read this excellent post from PortfolioCharts.com. Note that the data series computed by the PortfolioCharts author did find their place in the Simba spreadsheet, providing synthetic history for the factor-oriented Vanguard funds (e.g. VMSVX, etc).
sean.mcgrath wrote:Oh, one other thought: it might be an idea to put a warning by "maximum drawdown" that this comes from annual data and can understate actual drops. I was a bit surprised when I started to use monthly data at how large the difference can be.
Yes, I added a note to this effect, and even put a reminder "Risk Metric (derived from annual returns)" in the legend of the risk/return chart. You're right, this can make a significant difference. A fascinating chart was posted recently on a thread Taylor originated.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

### Re: Investing Without a Spreadsheet.

Taylor Larimore wrote:With my simple Vanguard 3-fund portfolio I manage to retire comfortably without a spreadsheet.
Cool. And for those of you more spreadsheet-inclined, Taylor's 3-fund portfolio is part of the 25 'lazy portfolios' worksheet, where I just added a couple of new charts to help people compare the proposals of various well-known experts, study the corresponding historical returns, as part of the process to reach their own conclusions.

sean.mcgrath
Posts: 169
Joined: Thu Dec 29, 2016 6:15 am
Location: US in NL

### Re: Investing Without a Spreadsheet.

With my simple Vanguard 3-fund portfolio I manage to retire comfortably without a spreadsheet.

Best wishes.
Taylor

I would struggle to retire comfortably without a spreadsheet, Taylor. It would be like retiring without a boat -- I'm sure it's possible, but then where's the fun?

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

Siamond

When is the appropriate time to comment on your work-in-progress (16d)? (It's very minor and certainly can wait.)

Thanks
jc6309

Posts: 61073
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

^^^ Post your comments here at any time, siamond will catch-up when he can. For example:

Compare_Portfolios - Cell D4, P4: Include the acronym in the comment - "Year of First (annual) Return"
Lazy_Portfolios - Cell D4: ditto
Portfolio_Math - Cell D4: ditto
Data_TR_USD - Cell A5: Include the text "No historical return available before that year"
Data_Misc - Cell A5: ditto

Administrative comments (editorial, which have no impact on anything) are very important. Getting the terminology wrong or misunderstanding a concept does not help anyone.

Reviewed in LibreOffice Calc.

==========================
sean.mcgrath wrote:

With my simple Vanguard 3-fund portfolio I manage to retire comfortably without a spreadsheet.

Best wishes.
Taylor

I would struggle to retire comfortably without a spreadsheet, Taylor. It would be like retiring without a boat -- I'm sure it's possible, but then where's the fun?
I also like spreadsheets and agree they're fun. How do I invest? A 3-fund (lazy portfolio) approach for my larger IRA / 401(k) accounts, a target date retirement fund for my smaller Roth IRA account (it's not worth the effort to split into separate funds).

To address Taylor Larimore's comment, I've given a strong show of support for a total-market diversified portfolio. See my comment: Re: Ben Carlson Drawdown Chart
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

jc6309 wrote:When is the appropriate time to comment on your work-in-progress (16d)? (It's very minor and certainly can wait.)
As Ladygeek said, just post here, or send me a PM, and I'll process your feedback in due time. Thank in advance for helping.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

LadyGeek wrote:Compare_Portfolios - Cell D4, P4: Include the acronym in the comment - "Year of First (annual) Return"
Lazy_Portfolios - Cell D4: ditto
Portfolio_Math - Cell D4: ditto
Data_TR_USD - Cell A5: Include the text "No historical return available before that year"
Data_Misc - Cell A5: ditto
Done, thanks.
LadyGeek wrote:Administrative comments (editorial, which have no impact on anything) are very important. Getting the terminology wrong or misunderstanding a concept does not help anyone.
Yup, agreed.

sean.mcgrath
Posts: 169
Joined: Thu Dec 29, 2016 6:15 am
Location: US in NL

Hi siamond,

I believe cells C105 and C106 of the "Compare Portfolios" tab should read "real," rather than "nominal." This is what the comments state, and looks to be the case.

cheers,
Sean

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

sean.mcgrath wrote:I believe cells C105 and C106 of the "Compare Portfolios" tab should read "real," rather than "nominal." This is what the comments state, and looks to be the case.
Good catch. And there was a similar mislabeling in the Lazy_Portfolios tab. Fixed in the working draft. Thanks!

LostCdnInvestor
Posts: 1
Joined: Mon May 22, 2017 4:58 pm

Hello. I'm a Canadian Investor who finds this spreadsheet invaluable in testing Canadian based portfolio's in \$CAD. I've been using the data in the Data_Misc tab to add Canadian data to Data_TR_USD to construct the portfolio's. I'd like to be able to contribute to the spreadsheet data if possible.

My question is that the Canadian inflation data in Data_Misc tab does not seem to match data I get directly from statistics Canada. (http://www.statcan.gc.ca) I'm going to the same source as listed in the spreadsheet, but the values in the spreadsheet do not match the data. Is there a process to get the data in the spreadsheet updated and can someone double check my values ?

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

LostCdnInvestor wrote:Hello. I'm a Canadian Investor who finds this spreadsheet invaluable in testing Canadian based portfolio's in \$CAD. I've been using the data in the Data_Misc tab to add Canadian data to Data_TR_USD to construct the portfolio's. I'd like to be able to contribute to the spreadsheet data if possible.
Ah, this is good to know, I was starting to wonder if the information in Data_Misc was useful to somebody, anybody! Thanks for the encouragement.
LostCdnInvestor wrote:My question is that the Canadian inflation data in Data_Misc tab does not seem to match data I get directly from statistics Canada. (http://www.statcan.gc.ca) I'm going to the same source as listed in the spreadsheet, but the values in the spreadsheet do not match the data. Is there a process to get the data in the spreadsheet updated and can someone double check my values ?
You already found the process, post on this thread, and I'll process it when I have a chance (I'm traveling now, will be home by the end of the week). Maybe the discrepancy is that you computed Jan-to-Jan, and I probably computed it Dec-to-Dec (in the same way we compute the US inflation in the main body of the spreadsheet)? I should have better documented this, actually... Will do.

Posts: 61073
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

Welcome! The best advice comes from your home country. I'd like to make you aware of our sister Canadian forum: Financial Wisdom Forum. If you need assistance with your portfolio, that's the place to go. You'll get expert advice.

We share quite a bit of info and several are members of both forums, including myself. Canadian members have contributed to this spreadsheet.

If you are looking for Canadian data, feel free to ask in Stock/Bond/Index/Fundamental/Asset Class/Quotes - Sources - Financial Wisdom Forum.

Also, check the wiki: finiki, the Canadian financial wiki
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

siamond wrote:Maybe the discrepancy is that you computed Jan-to-Jan, and I probably computed it Dec-to-Dec (in the same way we compute the US inflation in the main body of the spreadsheet)? I should have better documented this, actually... Will do.
Double-checked (yes, this is Dec to Dec), and better documented in next update.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Mid-year update completed! You will find the latest spreadsheet (v16d) in the usual post:
viewtopic.php?f=10&t=2520&p=2753812#p2753812

A few highlights about the changes:
- updated Expenses Ratios (most are now lower, thank you, Vanguard)
- updated US historical numbers based on Tyler's latest Stock Index Calculator

As usual, feedback welcome.

Posts: 61073
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

In your newly minted Glossary section-

Perpetual Spending Rate - I could not find an authoritative definition. Some hints were in this forum thread: Perpetual Withdrawal Rates (see the white paper) and this Morningstar video Swing Factors for the 4% Withdrawal Rate (in context as "perpetuity safe withdrawal rate").

Perpetual Spending Rate does not appear to be commonly used. Could you explain where this metric would be used vs. a Sustainable Spending Rate? The formula is in Compare_Portfolios!E106.

-Typo in Compare_Portfolios!B106 note: "The Perpetual Spending Rate (SSR)..." should be "The Perpetual Spending Rate (PSR)..."

Reviewed with MS Excel 2016.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Perpetual Spending Rate - I could not find an authoritative definition. Some hints were in this forum thread: Perpetual Withdrawal Rates (see the white paper) and this Morningstar video Swing Factors for the 4% Withdrawal Rate (in context as "perpetuity safe withdrawal rate").
No wonder, because I made up this terminology myself, derived from the SSR terminology and some terminology Tyler uses on PortfolioCharts for a similar goal (see below). Which is why I didn't provide a reference. If you have a stock sentence to describe such situation, please share?
LadyGeek wrote:Perpetual Spending Rate does not appear to be commonly used. Could you explain where this metric would be used vs. a Sustainable Spending Rate? The formula is in Compare_Portfolios!E106.
Well, as explained in the glossary, the SSR is the maximum what you can withdraw while not bringing the portfolio under water for the start/end years being studied (it's the basis for an SWR computation in other words). While the PSR is the maximum you can withdraw while keeping the portfolio balance unchanged (in real terms). This provides an upper bound, essentially mapping to two types of goals while retiring, either spend it all, or try to preserve your portfolio in perpetuity (e.g. bequest goal or early retiree with a very long time horizon).
LadyGeek wrote:-Typo in Compare_Portfolios!B106 note: "The Perpetual Spending Rate (SSR)..." should be "The Perpetual Spending Rate (PSR)..."
Fixed, thank you.

Posts: 61073
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

siamond wrote:If you have a stock sentence to describe such situation, please share?
Sorry, I don't have a stock sentence.
siamond wrote:Well, as explained in the glossary, the SSR is the maximum what you can withdraw while not bringing the portfolio under water for the start/end years being studied (it's the basis for an SWR computation in other words). While the PSR is the maximum you can withdraw while keeping the portfolio balance unchanged (in real terms). This provides an upper bound, essentially mapping to two types of goals while retiring, either spend it all, or try to preserve your portfolio in perpetuity (e.g. bequest goal or early retiree with a very long time horizon).
This is the missing piece of the puzzle - an upper bound to the withdrawal rate. I took a stab at revising your definitions.

Sustainable Spending Rate -
The maximum spending rate to keep the investment out of negative territory. This is the maximum amount you should spend each year to avoid running out of money before you plan to do so. Use this rate if you want to spend everything down to zero and leave nothing to your heirs. http://corporate.morningstar.com/ib/doc ... folios.pdf

Perpetual Spending Rate -
The maximum spending rate bringing the investment back to its initial value (inflation-adjusted) at the end of the time period of interest. This is the maximum amount you should spend each year to preserve the full value of your portfolio. Use this rate if you want to leave everything to your heirs.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

Ladygeek, I reused your suggested wording, with minor changes for conciseness. And I added the pointer to the Otar's white paper, and indicated that PSR is a terminology derived from Otar's Perpetual Distribution Rate. Thanks for your help. Spreadsheet updated.

Posts: 61073
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

1. README tab: I recommend saving the README tab at cell A1. Readers will see the instructions first, rather than the revision history.
2. README tab, Row 113: Put Jim Otar's paper on a new line, as the description is masked by the link.
Terminology derived from Perpetual Distribution Rate concept: http://retirementoptimizer.com/Whitepapers/PerpDist.pdf

In need of an update:
Lazy_Portfolios tab, drop-down "Chart's controls". The drop-down lists are Data Validation cells.

1. Row 122, Cell N122.
The description to the left of the drop-down explains what to do. "TIP: you can use the chart's control to select the type of risk metric to display on the chart" is not needed. Instead, explain what this chart is for. "This chart plots the risk metrics which use annual return". At least that's what I think you're doing.

2. Row 171, Cell N171
The description to the left of the drop-down explains what to do. Same as the first chart, but I think the explanation is "This chart plots the risk metrics by volatility."
3. Row 171, drop-down cell J171. The range should be limited to B\$99:B\$106, the same as the above chart.

4. Row 215, Cell N215
The description to the left of the drop-down explains what to do. I think the explanation is "This chart plots market correlations and beta."
5. Row 215, drop-down cell J215. The range should be limited to B\$112:B\$114.

6. Cells B116:B118 are for portfolio totals. None of the charts work when these cells are selected. Did you intend to use these rows in the chart?

(Hopefully, that should be the last of my comments. I didn't check any numbers for accuracy and will leave that for others.)
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

siamond
Posts: 5330
Joined: Mon May 28, 2012 5:50 am

LadyGeek wrote:1. README tab: I recommend saving the README tab at cell A1. Readers will see the instructions first, rather than the revision history.
Terminology derived from Perpetual Distribution Rate concept: http://retirementoptimizer.com/Whitepapers/PerpDist.pdf
Sigh, this is annoying, this is properly displayed with my version of Excel, but not with yours. And the problem goes way beyond this precise case. I widened the columns a bit, but this will never be perfect, I'm afraid.
It's usually obvious by looking at the link or by clicking on the link... I think this is good enough.
LadyGeek wrote:In need of an update:
Lazy_Portfolios tab, drop-down "Chart's controls". The drop-down lists are Data Validation cells.

1. Row 122, Cell N122.
The description to the left of the drop-down explains what to do. "TIP: you can use the chart's control to select the type of risk metric to display on the chart" is not needed. Instead, explain what this chart is for. "This chart plots the risk metrics which use annual return". At least that's what I think you're doing.
Well, this is the classic risk vs. return chart. Except that I parameterized risk. Ok, I changed the text to something more descriptive about the chart.
LadyGeek wrote:2. Row 171, Cell N171
The description to the left of the drop-down explains what to do. Same as the first chart, but I think the explanation is "This chart plots the risk metrics by volatility."
Er, no, this plots a risk (metric) per portfolio. I felt the need for this type of chart when writing my blog entries on international returns. I tuned the explanation.
LadyGeek wrote:3. Row 171, drop-down cell J171. The range should be limited to B\$99:B\$106, the same as the above chart. I changed the tip to a description.
Good catch! Fixed.
LadyGeek wrote:4. Row 215, Cell N215
The description to the left of the drop-down explains what to do. I think the explanation is "This chart plots market correlations and beta."
This plots a ratio or correlation (metric) per portfolio. Seemed interesting to provide broad choices here. I tuned the explanation.
LadyGeek wrote:5. Row 215, drop-down cell J215. The range should be limited to B\$112:B\$114.
No, I did intend to allow this chart to display correlation and beta metrics. Admittedly my 'ratio metric' terminology was a bit terse. Beta is actually a ratio of sorts. I re-ordered, regrouping beta with the other ratios, and spoke of "ratio or correlation metric".
LadyGeek wrote:6. Cells B116:B118 are for portfolio totals. None of the charts work when these cells are selected. Did you intend to use these rows in the chart?
No, this was a data validation mistake, as you pointed out in point #3. I didn't intend to chart those totals. Enough charts!

Posts: 61073
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

Today, I am reviewing the spreadsheet in LibreOffice Calc in Linux.

In README "To Do" "- Check/update BRSIX ER once mid-year report is available". I checked.

The website's factsheet is dated 03/31/2017 ("Literature" tab). This is not the mid-year report, but please double-check your asset class - Small-Cap Blend is listed. Data_TR_USD tab, cell CC18 lists BRSIX as "Micro Cap". It could be a difference between your definition of "Micro Cap" vs. Morningstar's definition.

The BRSIX asset class in L1 is "D10", but does not match the asset class name in CC18 - currently as Micro Cap. I don't know what D10 means, but it should probably match the asset class name.

The notes for cells A3 (ER) and A4 (YoI) mention Vanguard funds. I recommend removing "Vanguard" as several of the funds in this sheet are not Vanguard.

========================
The chart explanations are clear, thank you.

Lazy_Portfolios tab - In the "Risk vs. Returns (nominal)" chart, the large number of data series results in white colored markers for several funds. The legend background is also white, which makes it impossible to identify the funds. Can you change the legend background to gray (but not too dark)? Otherwise, I cannot see the marker identifications.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.