A Returns Spreadsheet for Bogleheads

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
moshe
Posts: 565
Joined: Thu Dec 12, 2013 12:18 pm
Location: Boston, MA

Re: A Returns Spreadsheet for Bogleheads

Post by moshe »

longinvest wrote:
moshe wrote: It is possible to make the IRR calculation in C12 on the Returns page a bit more interesting by changing it from an annualized return to the recent month end IRR by adding this formula someplace on the calculations sheet:
Moshe,

In version 1.9 of the spreadsheet, the Trailing Investor Return (IRR) is only annualized for periods longer than 12 months, not for shorter periods, if that is what you are asking for. Are you using version 1.9?
No and yes. The formula addition which is based on the annualized calculation should(unless my logic is faulty, which is certainly possible) give the total month since the beginning of data points IRR since annualized is less interesting in month 6 of the year, for example.

~Moshe
My money has no emotions. ~Moshe | | I'm the world's greatest expert on my own opinion. ~Bruce Williams
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

moshe wrote:The formula addition which is based on the annualized calculation should(unless my logic is faulty, which is certainly possible) give the total month since the beginning of data points IRR since annualized is less interesting in month 6 of the year, for example.
Moshe,

The Investor Return is just a description of the average past return on invested money (taking into account contributions and withdrawals) since the beginning of the user's investment history (in the spreadsheet). It does not represent a current growth rate or anything like it. It has no predictive power; it says nothing about future returns.

The most common way to report an average historical return is using an annualized number (except when a period is shorter than one year). I see no reason to change this. Doing otherwise would be misleading.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
moshe
Posts: 565
Joined: Thu Dec 12, 2013 12:18 pm
Location: Boston, MA

Re: A Returns Spreadsheet for Bogleheads

Post by moshe »

Understood. Poor choice of words on my part as my intended suggestion was to add this additional information not replace the annualized information.

~Moshe
My money has no emotions. ~Moshe | | I'm the world's greatest expert on my own opinion. ~Bruce Williams
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

Moshe,

The reason I developed the spreadsheet was so I could get an overall view of my investment accounts and be able to see if I am accumulating any significant tracking error. The Investor Return is already a spurious information, because it is useless for detecting tracking errors; its only use is for comparing my investment returns to having left my money into a savings account.

Unfortunately, our human brain has been trained to project past experiences into the future. In most areas not related to investing, this is a very good thing. For example, remembering that smashing one's finger using a hammer hurts and projecting that smashing it again is likely to hurt leads us to be more careful in the future.

But, in investing, this natural instinct is misleading. We're naturally inclined to believe that the investment with the highest past returns will be the one with the highest returns ahead. Adding all kinds of bells and whistles to the returns spreadsheet would just encourage this, and thus be counterproductive for my objectives.

My objectives are to:
  1. Provide a tool to independently calculate one's past returns. This is particularly important for those who receive statements which report past performance. It is important to be able to check that the performance is correctly calculated. Sometimes, reported numbers are not clearly labeled as money-weighted or time-weighted. The spreadsheet can be used to find out which type is return is actually reported.
  2. Allow to get an overall view across multiple accounts, specially when one has investments scattered between employer retirement account, brokerage account, spouse accounts, etc.
  3. Allow the user to finally realize, after a few months of use, that trailing investment returns change every month, often significantly; that if past returns are constantly changing, future ones are likely to continue doing so; that predicting future returns is futile and that anything written about them in the financial press is just silly noise.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
birdec
Posts: 50
Joined: Fri Jul 11, 2014 10:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by birdec »

My data starts on 2/29/16 (with the first Contribution/Withdrawal month being March 2016). I think because of this, I'm not able to see YTD numbers - only 1, 3, and 6 month returns.

Is it possible to view returns given a custom date range? As in, 3/1/16 - 11/1/16.
Also, is it possible to view returns of individual accounts or just the aggregate portfolio?

Thanks.
User avatar
Peculiar_Investor
Site Admin
Posts: 2442
Joined: Thu Oct 20, 2011 12:23 am
Location: Calgary, AB 🇨🇦
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by Peculiar_Investor »

nakatomi wrote:Longinvest:

Thanks for this great spreadsheet. I've got it up and running for my accounts and it's very helpful.

I have a suggestion for future versions: please include an 'Enable/Disable' switch for each account. Such a switch would be really helpful in the data entry phase because it would allow one to compare the returns against what's reported on each website.

For example, I was entering all my data for Vanguard, and I found myself wanting to disable all my other accounts temporarily so I could verify that the Investor return was in the ballpark of the number reported by Vanguard.

I realize that the purpose of this tool is to prevent users from getting too focused on a single account instead of the aggregate, but I still think an Enable/Disable switch would be valuable. I ended up customizing my copy of the spreadsheet to do this.
I would also be interested in this same functionality, for the same reasons.

@nakatomi, could you share how you customize it.
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
nakatomi
Posts: 67
Joined: Thu Feb 11, 2016 1:41 pm

Re: A Returns Spreadsheet for Bogleheads

Post by nakatomi »

Peculiar_Investor wrote:
nakatomi wrote:
@nakatomi, could you share how you customize it.
Hi Peculiar_Investor -
Sorry didn't see this earlier as I was traveling.

Basically, added a column to the 'Portfolio' tab called 'Enabled', in which a value of 1 means enabled, and 0 means disabled.

Next, I modified the Balance column for each account to multiply by the Enabled column.

That makes it easy to turn on and off individual accounts.
User avatar
Peculiar_Investor
Site Admin
Posts: 2442
Joined: Thu Oct 20, 2011 12:23 am
Location: Calgary, AB 🇨🇦
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by Peculiar_Investor »

Thanks. I'll give that a try.
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
ancho
Posts: 99
Joined: Tue Feb 09, 2016 3:39 pm

Re: A Returns Spreadsheet for Bogleheads

Post by ancho »

Apologies if this has been answered - I searched the thread and couldn't find it:

If my available statements go back different lengths of time on different accounts, what's the best way to address it in the spreadsheet?

E.g.

Account 1:
Opened 2008
Statements available back to 2011

Account 2:
Opened 2013
Statements available for 2016-present

Account 3:
Opened 2014
All statements available

-What do I select as the starting month? Do I just start all accounts at the most recent month for which ALL statements available (i.e. Jan 2016 in the example above)? Or do I start each account with the earliest available statement, knowing that it won't represent my complete portfolio returns.

-If I have year-end balances going back further than monthly statements are available, is there a way to incorporate that information?
User avatar
GreatOdinsRaven
Posts: 572
Joined: Thu Apr 23, 2015 8:47 pm

Re: A Returns Spreadsheet for Bogleheads

Post by GreatOdinsRaven »

nakatomi wrote:
Peculiar_Investor wrote:
nakatomi wrote:
@nakatomi, could you share how you customize it.
Hi Peculiar_Investor -
Sorry didn't see this earlier as I was traveling.

Basically, added a column to the 'Portfolio' tab called 'Enabled', in which a value of 1 means enabled, and 0 means disabled.

Next, I modified the Balance column for each account to multiply by the Enabled column.

That makes it easy to turn on and off individual accounts.
This is INCREDIBLY useful. Perhaps the site administrators would consider adding this functionality to the downloadable template.

What a great idea.
"The greatest enemies of the equity investor are expenses and emotions." -John C. Bogle, Little Book of Common Sense Investing. | | "Winter is coming." Lord Eddard Stark.
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

GreatOdinsRaven wrote:
nakatomi wrote: Basically, added a column to the 'Portfolio' tab called 'Enabled', in which a value of 1 means enabled, and 0 means disabled.
...
That makes it easy to turn on and off individual accounts.
This is INCREDIBLY useful. Perhaps the site administrators would consider adding this functionality to the downloadable template.
While it would be very easy to add this feature, it would open the way for behavioral mistakes.

One should not consider the returns of each account in isolation. During the 2008 crisis, some people did this; they dumped their taxable investments just at the worst of times, because they were looking at their taxable account (which contained most of their stocks) in isolation and did not consider all the bonds they had in their tax-advantaged accounts. They wanted to stop the losses they were seeing (that were much worse at a single account level than their mild overall portfolio losses).

This had such a profound impact on financial adviser and author Rick Ferri that he started recommending that his clients use a similar asset allocation in all of their accounts to avoid such behavioral problems, even if it's not optimal for tax purpose. I suspect that other advisers have also started recommending the use of balanced mutual funds, even if not tax optimal, to avoid this same behavioral mistake.

As a result, I am extremely reluctant to add the requested feature into the spreadsheet. I definitely don't want for a Bogleheads spreadsheet to open the way for severe behavioral mistakes.

longinvest
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
GreatOdinsRaven
Posts: 572
Joined: Thu Apr 23, 2015 8:47 pm

Re: A Returns Spreadsheet for Bogleheads

Post by GreatOdinsRaven »

longinvest wrote:
GreatOdinsRaven wrote:
nakatomi wrote: Basically, added a column to the 'Portfolio' tab called 'Enabled', in which a value of 1 means enabled, and 0 means disabled.
...
That makes it easy to turn on and off individual accounts.
This is INCREDIBLY useful. Perhaps the site administrators would consider adding this functionality to the downloadable template.
While it would be very easy to add this feature, it would open the way for behavioral mistakes.

One should not consider the returns of each account in isolation. During the 2008 crisis, some people did this; they dumped their taxable investments just at the worst of times, because they were looking at their taxable account (which contained most of their stocks) in isolation and did not consider all the bonds they had in their tax-advantaged accounts. They wanted to stop the losses they were seeing (that were much worse at a single account level than their mild overall portfolio losses).

This had such a profound impact on financial adviser and author Rick Ferri that he started recommending that his clients use a similar asset allocation in all of their accounts to avoid such behavioral problems, even if it's not optimal for tax purpose. I suspect that other advisers have also started recommending the use of balanced mutual funds, even if not tax optimal, to avoid this same behavioral mistake.

As a result, I am extremely reluctant to add the requested feature into the spreadsheet. I definitely don't want for a Bogleheads spreadsheet to open the way for severe behavioral mistakes.

longinvest
Ok. No biggie.

Why would I want such functionality you might ask-

For me, I want to be able to look at my child's 529 performance (two 529 accounts with a relative short horizon) and be able to turn off her UTMA account (which is invested for a completely different purpose with a decades longer investing lifetime), I'd also like to be able to parse out the performance of each 529 account quickly. Ever wonder how much of a difference there can be between a Vanguard age-based aggressive and an age-based moderate account? You can't get accurate XIRR numbers from Vanguard's website. The annualized returns numbers are inaccurate.

I had been creating specific spreadsheets for those purposes but doing so requires the busy-work of re-entering the same data on multiple spreadsheets (combined 529s, 529s+UTMA, patents' accounts with and without the 529s, just the UTMA, etc).
"The greatest enemies of the equity investor are expenses and emotions." -John C. Bogle, Little Book of Common Sense Investing. | | "Winter is coming." Lord Eddard Stark.
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

Ancho,
ancho wrote: -What do I select as the starting month? Do I just start all accounts at the most recent month for which ALL statements available (i.e. Jan 2016 in the example above)? Or do I start each account with the earliest available statement, knowing that it won't represent my complete portfolio returns.
I would lean towards starting at the point where you have all portfolio information, as to calculate total portfolio returns. I don't see any point in calculating partial accounts returns.
ancho wrote: -If I have year-end balances going back further than monthly statements are available, is there a way to incorporate that information?
Only if contribution and withdrawal information is also available, otherwise you'll get Beardstown Ladies type of returns (not a good thing)!

longinvest
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
sean.mcgrath
Posts: 786
Joined: Thu Dec 29, 2016 5:15 am
Location: US in NL

Re: A Returns Spreadsheet for Bogleheads

Post by sean.mcgrath »

Hi longinvest,

Thanks for the spreadsheet -- it looks useful, and I'm in the process of transferring my data into it. One suggestion to the excel version: my currency is Euros, so I'll be changing the $ formats everywhere in the sheets. When I look in the "Number" box with the excel format icons, I see that the cells are formatted "custom." If you change the format to "currency," every user would see their own home currency in the cells.

Sean
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

Sean,
sean.mcgrath wrote:One suggestion to the excel version: my currency is Euros, so I'll be changing the $ formats everywhere in the sheets. When I look in the "Number" box with the excel format icons, I see that the cells are formatted "custom." If you change the format to "currency," every user would see their own home currency in the cells.
Thanks!
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
moshe
Posts: 565
Joined: Thu Dec 12, 2013 12:18 pm
Location: Boston, MA

Re: A Returns Spreadsheet for Bogleheads

Post by moshe »

Any thoughts to reporting the portfolio standard deviation along with portfolio returns? I'd suggest yearly rather than monthly would be more meaningful but you could show both.

Thank you,
~Moshe
My money has no emotions. ~Moshe | | I'm the world's greatest expert on my own opinion. ~Bruce Williams
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

moshe wrote: Mon Aug 14, 2017 8:56 am Any thoughts to reporting the portfolio standard deviation along with portfolio returns? I'd suggest yearly rather than monthly would be more meaningful but you could show both.
I discussed this possibility on another thread, in the past: How to calculate annual standard deviation from monthly returns.

Thanks to the feedback I got, including the wisdom of Advisory Board member Taylor Larimore and forum member rodc*, in particular, I came to the conclusion that it wouldn't be useful for the intended use of the spreadsheet. See this post: viewtopic.php?f=10&t=184082#p2797961.

* Forum member rodc holds a Ph.D. in maths.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
moshe
Posts: 565
Joined: Thu Dec 12, 2013 12:18 pm
Location: Boston, MA

Re: A Returns Spreadsheet for Bogleheads

Post by moshe »

Hi LI,

Thank you.

I would find value in seeing this info displayed as well as the Sharpe ratio. My curiosity is when comparing my personal performance & SD to market performance & SD.

I think the value in providing this information to those without a deep knowledge of financial math is that diversification does provide a "free lunch" of performance with lower risk (as defined by volatility).

Sharpe Ratio = (P- Rf)/SD - P = Portfolio Return, Rf = risk free rate, SD = Standard deviation of portfolio
http://www.investopedia.com/terms/s/sharperatio.asp

All the best,
~Moshe
My money has no emotions. ~Moshe | | I'm the world's greatest expert on my own opinion. ~Bruce Williams
Drerwski
Posts: 20
Joined: Fri Feb 09, 2018 7:51 am

Re: A Returns Spreadsheet for Bogleheads

Post by Drerwski »

Thanks for the spreadsheet.

I am trying to experiment with it but I get the following error when trying to test it.

I have entered a portfolio and there are no other errors in the file.

Worksheet where error appears = Returns
Cell = C12 = (Error message in cell) #NUM!
Cell = C12 = (contents of cell), =Calculations!U6

Can someone explain how to repair the error?
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

Drerwski wrote: Fri Feb 09, 2018 8:08 am Thanks for the spreadsheet.

I am trying to experiment with it but I get the following error when trying to test it.

I have entered a portfolio and there are no other errors in the file.

Worksheet where error appears = Returns
Cell = C12 = (Error message in cell) #NUM!
Cell = C12 = (contents of cell), =Calculations!U6

Can someone explain how to repair the error?
Just based on this information, it's difficult to know what's the problem. I would think that there's probably a problem in the data that was entered into the spreadsheet; this usually results into a #NUM! error message.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
Drerwski
Posts: 20
Joined: Fri Feb 09, 2018 7:51 am

Re: A Returns Spreadsheet for Bogleheads

Post by Drerwski »

Thanks for the response.
Not sure exactly how, but after playing with it for a while I solve the problem.
airelleofmusic
Posts: 96
Joined: Mon May 01, 2017 8:54 am

Re: A Returns Spreadsheet for Bogleheads

Post by airelleofmusic »

Hello everyone,

Many thanks for your work.

Regarding the excel spreadsheet (https://www.bogleheads.org/wiki/Calcula ... al_returns) can you confirm there is an error Line 15 and Line 48 on the first sheet and that it's money-weighted returns instead of time-weighted returns ? Because when I change my contributions, returns change too (and should not if it's time-weighted returns)

Thanks for your help.
LBYM and enjoy life ! Thanks BH !
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

airelleofmusic wrote: Wed Feb 28, 2018 4:42 pm Regarding the excel spreadsheet (https://www.bogleheads.org/wiki/Calcula ... al_returns) can you confirm there is an error Line 15 and Line 48 on the first sheet and that it's money-weighted returns instead of time-weighted returns ? Because when I change my contributions, returns change too (and should not if it's time-weighted returns)
I don't understand. How can past contributions change? Have amended account statements been issued?
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
airelleofmusic
Posts: 96
Joined: Mon May 01, 2017 8:54 am

Re: A Returns Spreadsheet for Bogleheads

Post by airelleofmusic »

Hello,
No I mean that my Trailing portfolio return (time-weighted return, comparable return) is changing when I add for example a 100$ contribution. Is it normal ?
Because normally time-weighted return should not take into account contributions and withdrawals.
Many thanks
LBYM and enjoy life ! Thanks BH !
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

airelleofmusic wrote: Thu Mar 01, 2018 2:26 am No I mean that my Trailing portfolio return (time-weighted return, comparable return) is changing when I add for example a 100$ contribution. Is it normal ?
I think I know what happened.

Yesterday was the last day of February. As a consequence, the spreadsheet has automatically changed the reference point, for calculating trailing returns, from January 2018 to February 2018. It's normal for February 2018 trailing returns to differ from January 2018 trailing returns.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
airelleofmusic
Posts: 96
Joined: Mon May 01, 2017 8:54 am

Re: A Returns Spreadsheet for Bogleheads

Post by airelleofmusic »

No no even if I add a 100$ contribution in August 2017 my Annual portfolio return (time-weighted return, comparable return) for 2017 are changing too (and trailing portfolio returns too).
LBYM and enjoy life ! Thanks BH !
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

airelleofmusic wrote: Thu Mar 01, 2018 6:48 am No no even if I add a 100$ contribution in August 2017 my Annual portfolio return (time-weighted return, comparable return) for 2017 are changing too (and trailing portfolio returns too).
When past contributions are changed, past returns change. It's normal. It has nothing to do with time-weighted relative to money-weighted returns. It's about calculating the returns on different data sets.

Let's look at a small example. In a $0 account, I contribute $100. One year later the account balance is $110. What's my time-weighted trailing 1-year return? It's obviously 10%.

If I change the initial contribution to $105 but keep the $110 final balance, one year later, what's my time-weighted trailing 1-year return?
  • It's ($110 / $105) - 1 = 4.76%
Why are the returns different (10% and 4.76%)? Because they're the trailing returns on two different data sets.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
1210sda
Posts: 1865
Joined: Wed Feb 28, 2007 7:31 am

Re: A Returns Spreadsheet for Bogleheads

Post by 1210sda »

Thank you Longinvest for all the tools you have provided for BH's.
1210
airelleofmusic
Posts: 96
Joined: Mon May 01, 2017 8:54 am

Re: A Returns Spreadsheet for Bogleheads

Post by airelleofmusic »

@longinvest,

I definitely apologize, it's my fault. I forgot to subtract the contribution to the final balance that's why I got different annual returns.

Thanks again for your work,
airelle
LBYM and enjoy life ! Thanks BH !
rs397
Posts: 5
Joined: Thu Feb 08, 2018 4:51 am

Re: A Returns Spreadsheet for Bogleheads

Post by rs397 »

Hi @longinvest,

Thanks so much for providing this excellent spreadsheet. It is really helping me get a better sense of my personal rate of return. I have two questions:

1) How does the investor return compare to the personal rate of return shown on Vanguard's website? Should these two numbers match, and if there is a difference is it because of any mistakes in my calculation? For example, my last 19 year returns on Vanguard show a figure of 8.5%, whereas the portfolio return is 8.1% and the investor return from 1/31/2007 is 7.7%. I don't know how to calculate investor return for only the last 10 year period. All these numbers are current as of 2/28/2018.

2) The other thing I am doing is to compare my returns to a portfolio that is 100% invested in US total stock market index fund. I am using VTSAX as the proxy. What I have done is that I copy the sequence of contributions as if I am buying/selling VTSAX and the entire portfolio stays at 100% VTSAX. I update the total number of shares at the end of each month and enter the ending price of VTSAX. When I do this I get the following numbers: investor return of 8.5% and 10 year portfolio return of 9.8%. I wanted to use this as a benchmark and I am happy how my actual portfolio returns compare to this 100% stock portfolio.

Any feedback or comments would be appreciated.
Thanks.
RS
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

rs397 wrote: Fri Mar 02, 2018 4:06 am 1) How does the investor return compare to the personal rate of return shown on Vanguard's website?
I don't have a Vanguard account, so I don't know what kind of return it displays.
rs397 wrote: Fri Mar 02, 2018 4:06 am I don't know how to calculate investor return for only the last 10 year period. All these numbers are current as of 2/28/2018.
The spreadsheet only calculates an investor return for the entire investment history. It doesn't calculate period-specific (such as 1 year, 3 years, 10 years) trailing investor returns, because this return is highly dependent on the personal contribution and withdrawal history of the investor.
rs397 wrote: Fri Mar 02, 2018 4:06 am2) The other thing I am doing is to compare my returns to a portfolio that is 100% invested in US total stock market index fund.
I would only use a 100% US stocks investment as a benchmark for a portfolio invested into mostly large US stocks. It's important to always select an appropriate benchmark, as different portfolio compositions lead to different returns.
rs397 wrote: Fri Mar 02, 2018 4:06 am I am using VTSAX as the proxy. What I have done is that I copy the sequence of contributions as if I am buying/selling VTSAX and the entire portfolio stays at 100% VTSAX. I update the total number of shares at the end of each month and enter the ending price of VTSAX. When I do this I get the following numbers: investor return of 8.5% and 10 year portfolio return of 9.8%. I wanted to use this as a benchmark and I am happy how my actual portfolio returns compare to this 100% stock portfolio.
According to Vanguard, the trailing 10-year (portfolio) return on Vanguard Total Stock Market Index Fund Admiral Shares (VTSAX) was 9.89% as of February 28, 2018. This rounds up to 9.9%. Personally, I would double (or triple) check the numbers in the spreadsheet and try to find why the spreadsheet's return is 0.1% lower.

Actually, I wouldn't do that at all; I wouldn't try to calculate a benchmark-based "personal contribution" investor return. I would simply compare my personal portfolio's time-weighted returns to its benchmark's returns. This is sufficient. As of today, the only actionable number is my portfolio's balance!

Anecdote: I've carefully entered my entire historical investment data into my personal spreadsheet copy a few years ago. This last Summer, I went through all of my historical monthly account statements to double check the data. I found a couple of erroneous entries. :annoyed
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
J0721
Posts: 31
Joined: Fri Mar 09, 2018 7:37 pm

Re: A Returns Spreadsheet for Bogleheads

Post by J0721 »

Thanks for putting this together it is a really helpful worksheet. I have a few questions I hope you can help with.

1. We have a few accounts some started back in 2011 some more recently. Do I 0 fill the more recent accounts until I get to the dates where I have data at acct opening? Does the workbook automatically handle the accounts with 0 balance by filtering them out of the return calls when they were not yet funded like 2011?

2. A couple of the accounts are 401k and only have quarterly statements so do I just put the full contribution amounts in the quarter end month? Since I don't get a new balance figure until the next quarter end I just repeat the balance amount until the next statement. If me 3/31 balance is 50,000 I will show that for April and May then on the 6/31 entry I will put in my 6/31 statement balance.

3. Lastly is there a way to filter the accounts in and out of the return calculation. It is great to see it at a portfolio level but I would also like to see how each account has performed on its own.

Thanks!
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

J0721,
J0721 wrote: Fri Mar 09, 2018 7:50 pm 1. We have a few accounts some started back in 2011 some more recently. Do I 0 fill the more recent accounts until I get to the dates where I have data at acct opening? Does the workbook automatically handle the accounts with 0 balance by filtering them out of the return calls when they were not yet funded like 2011?
Setting the initial balance of the account to $0 is enough. There's no need to fill remaining entries with zeros.
J0721 wrote: Fri Mar 09, 2018 7:50 pm 2. A couple of the accounts are 401k and only have quarterly statements so do I just put the full contribution amounts in the quarter end month? Since I don't get a new balance figure until the next quarter end I just repeat the balance amount until the next statement. If me 3/31 balance is 50,000 I will show that for April and May then on the 6/31 entry I will put in my 6/31 statement balance.
That seems good enough. It's important to include personal and employer contributions.
J0721 wrote: Fri Mar 09, 2018 7:50 pm 3. Lastly is there a way to filter the accounts in and out of the return calculation. It is great to see it at a portfolio level but I would also like to see how each account has performed on its own.
The spreadsheet does not support this feature because looking at returns in isolation could lead to behavioral mistakes.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
LinusCastle
Posts: 3
Joined: Thu May 17, 2018 7:46 pm

Return Spreadsheet For Bogleheads

Post by LinusCastle »

I am looking for someone to answer questions about the use of an online excel spreadsheet entitled "Return Spreadsheet for Bogleheads." This device is supposed to calculate various money-weighted and time-weighted measures of portfolio performance. Is anyone familiar with this spreadsheet or can anyone direct me to a knowledgeable source? -Linus
User avatar
LadyGeek
Site Admin
Posts: 95691
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by LadyGeek »

LinusCastle, Welcome! I moved your post into the on-going support thread. See the first post for more info.

Here's the wiki link: Calculating personal returns

FYI - I added a shortcut to the wiki's search box. Typing in "Return spreadsheet for Bogleheads" will get you there. (You only need to type in "Return" to see it.)
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.
LinusCastle
Posts: 3
Joined: Thu May 17, 2018 7:46 pm

Re: A Returns Spreadsheet for Bogleheads

Post by LinusCastle »

Regarding the "Returns Spreadsheet for Bogleheads," I assume that the numbered account sheets are supposed to contain data for different portfolios and that returns are calculated for each portfolio. The reason I ask is that I have properly created two different portfolios in separate account sheets but have only one set of investor and portfolio returns. Should there not be a separate set of returns for each portfolio/account? -Linus
User avatar
Ketawa
Posts: 2521
Joined: Mon Aug 22, 2011 1:11 am
Location: DC

Re: A Returns Spreadsheet for Bogleheads

Post by Ketawa »

LinusCastle wrote: Fri May 18, 2018 8:52 pm Regarding the "Returns Spreadsheet for Bogleheads," I assume that the numbered account sheets are supposed to contain data for different portfolios and that returns are calculated for each portfolio. The reason I ask is that I have properly created two different portfolios in separate account sheets but have only one set of investor and portfolio returns. Should there not be a separate set of returns for each portfolio/account? -Linus
The separate worksheets are there to make it easier to input contributions & withdrawals from multiple accounts. Returns are calculated for the entire portfolio across all the accounts, so there is only one set of returns. If you want to calculate it for separate accounts, save multiple workbooks.
LinusCastle
Posts: 3
Joined: Thu May 17, 2018 7:46 pm

Re: A Returns Spreadsheet for Bogleheads

Post by LinusCastle »

Thank you for explain that returns are calculated across all accounts. I manage multiple accounts with different beneficiaries and investment objectives, and I would like to track returns without creating a workbook for each account. Oh well. -Linus
LucaSchmidt
Posts: 1
Joined: Fri Jun 22, 2018 5:05 pm

Re: A Returns Spreadsheet for Bogleheads

Post by LucaSchmidt »

Hi,
I have 2 general question on the return calculations.

1)
You record cash flows and balances once a month.
Doesn't this mean some level of inaccuracy as it there is no difference whether you gain 50$ in 3 days or 30 days (within the month)
Can this inaccuracy impact the validity of the portfolio returns ?

2)
How can you see your absolute dollar cash return generated across the whole time horizon.

Many thanks
Luca
User avatar
randomizer
Posts: 1547
Joined: Sun Jul 06, 2014 3:46 pm

Re: A Returns Spreadsheet for Bogleheads

Post by randomizer »

longinvest wrote: Mon Feb 15, 2016 9:32 am I have uploaded a new version (1.9) of the returns spreadsheet.

Changes:
  • Handle closed accounts gracefully. (e.g. Don't ask for zeros every month).
As usual, comments are welcome.
How does one mark an account as closed? Sorry if this is obvious, but I can't see it.
87.5:12.5, EM tilt — HODL the course!
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

randomizer wrote: Sun Aug 19, 2018 4:17 pm
longinvest wrote: Mon Feb 15, 2016 9:32 am I have uploaded a new version (1.9) of the returns spreadsheet.

Changes:
  • Handle closed accounts gracefully. (e.g. Don't ask for zeros every month).
As usual, comments are welcome.
How does one mark an account as closed? Sorry if this is obvious, but I can't see it.
When the last provided account balance is $0, the spreadsheet doesn't report an error when the contribution/withdrawal/balance of succeeding months aren't provided.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
randomizer
Posts: 1547
Joined: Sun Jul 06, 2014 3:46 pm

Re: A Returns Spreadsheet for Bogleheads

Post by randomizer »

longinvest wrote: Sun Aug 19, 2018 4:22 pm When the last provided account balance is $0, the spreadsheet doesn't report an error when the contribution/withdrawal/balance of succeeding months aren't provided.
Thank you!
87.5:12.5, EM tilt — HODL the course!
spammagnet
Posts: 2481
Joined: Wed Apr 27, 2016 9:42 pm

Re: A Returns Spreadsheet for Bogleheads

Post by spammagnet »

longinvest wrote: Sun Aug 19, 2018 4:22 pmWhen the last provided account balance is $0, the spreadsheet doesn't report an error when the contribution/withdrawal/balance of succeeding months aren't provided.
What's the easiest way to adopt v1.9 if I have a lot of data in 1.8? As I will be closing some accounts soon, it probably would be advantageous to do that.

Thanks
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

spammagnet wrote: Sun Aug 19, 2018 11:32 pm
longinvest wrote: Sun Aug 19, 2018 4:22 pmWhen the last provided account balance is $0, the spreadsheet doesn't report an error when the contribution/withdrawal/balance of succeeding months aren't provided.
What's the easiest way to adopt v1.9 if I have a lot of data in 1.8? As I will be closing some accounts soon, it probably would be advantageous to do that.
Here's how I did it:
  1. I cut & pasted the data, doing 2 operations per account (contributions/withdrawals, balance).
  2. To detect errors, I checked that the final amount, in the old and new spreadsheets, was identical in the "Grows to" column of the "Display" sheet. That's the final amount $10,000 grows to.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
bgf
Posts: 2085
Joined: Fri Nov 10, 2017 8:35 am

Re: A Returns Spreadsheet for Bogleheads

Post by bgf »

this is the work of absolute genius. i've been tracking my own data with spreadsheets i made myself but this is... well, far superior. thank you!
“TE OCCIDERE POSSUNT SED TE EDERE NON POSSUNT NEFAS EST"
dtb80
Posts: 1
Joined: Tue Feb 26, 2019 9:19 am

Re: A Returns Spreadsheet for Bogleheads

Post by dtb80 »

Thanks for making this great spreadsheet!

I've been investing since 2005 and I'm in the process of transferring all of my data to this spreadsheet in Google Sheets. Whilst I've been very diligent in recording the fund that I bought, the amount and the price that I paid for any given month, I have not been recording the value of my pot at the end of each month and it looks like this is a key part of info this spreadsheet needs.

Can anyone give me some advice as to how to address this?
retiringwhen
Posts: 4743
Joined: Sat Jul 08, 2017 10:09 am
Location: New Jersey, USA

Re: A Returns Spreadsheet for Bogleheads

Post by retiringwhen »

dtb80 wrote: Tue Feb 26, 2019 9:22 am Thanks for making this great spreadsheet!

I've been investing since 2005 and I'm in the process of transferring all of my data to this spreadsheet in Google Sheets. Whilst I've been very diligent in recording the fund that I bought, the amount and the price that I paid for any given month, I have not been recording the value of my pot at the end of each month and it looks like this is a key part of info this spreadsheet needs.

Can anyone give me some advice as to how to address this?
If you have the price and amount paid ,you can derive the # of shares purchased ($invested/$price-per-share)

From there, you will need to use a ticker lookup service (yahoo, Google, Vanguard website, etc. and get prices for the different dates. That can get pretty detailed, but you should be able to find the data.

You would then just create a formula for each month that totals the # of shares against published price to get the the balance. Of course you will have to be careful to be sure you time the additional of dividend reinvestment, but i assume you would have date from what you describe above.

I had a similar issue, what I did was only create annual answers to those questions and I hacked the spreadsheet to allow for annual entries only for years before 2017. Unfortunately my sheet is pretty customized to my situation, but I could share the idea.

What i ended up with is a dynamic worksheet that has manually entered balance/flow data for years before I started to track returns in longvest’s sheet. For years since the beginnning of tracking it pulls off the 12/31 balance and automatically adds each year to the bottom of the sheet. This allows me to do the same return calculations he has monthly but viewed annually. So basically I have annualized returns that I then look at from a 1, 3, 5, 10 and 20 year view and an IRR calculation as well. Because the flows are rolled up to an annual level, the overlapping periods do not get the same results as the monthly calculations. So for the more recent years, it is better to use the monthly calculations for any detailed analysis.

If there is interest in a system like that I can try to genericize my sheet and share with longvest as a new tab. I wouldn’t want to fork his great work.
charis23
Posts: 128
Joined: Mon Jan 02, 2017 2:25 pm

Re: A Returns Spreadsheet for Bogleheads

Post by charis23 »

Thank you for this spreadsheet longinvest!

Quick question, does the spreadsheet assume monthly contributions are made at the beginning of a month? I'm assuming there may be small differences compared to say what Vanguard calculates if one is investing twice a month.
Topic Author
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest »

charis23 wrote: Thu Mar 07, 2019 5:16 pm Quick question, does the spreadsheet assume monthly contributions are made at the beginning of a month? I'm assuming there may be small differences compared to say what Vanguard calculates if one is investing twice a month.
The spreadsheet makes no specific assumption. It approximates the unknown investment date by calculating as if half the amount was invested (or withdrawn) at the beginning of the month and the other half at the end of the month as was suggested* by William Bernstein in The Four Pillars of Investing.

* He was making the suggestion in the context of annual return calculations.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
rst113
Posts: 24
Joined: Fri Apr 30, 2010 10:50 am

Re: A Returns Spreadsheet for Bogleheads

Post by rst113 »

How would I add more than 9 accounts? A couple are no longer active/rolled over but I presume deleting those will mess up the tracked returns. I can add a tab but I know I would have to do something to the display and/or calculations tabs. Thanks so much for this I diligently track all my accounts monthly using this and love the overall picture it gives.
Post Reply