A Returns Spreadsheet for Bogleheads

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

Re: A Returns Spreadsheet for Bogleheads

Post by moshe » Sat Oct 01, 2016 11:18 am

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

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sat Oct 01, 2016 11:32 am

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.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

moshe
Posts: 450
Joined: Thu Dec 12, 2013 1:18 pm
Location: Boston, MA

Re: A Returns Spreadsheet for Bogleheads

Post by moshe » Sat Oct 01, 2016 11:41 am

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

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sat Oct 01, 2016 12:09 pm

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.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

birdec
Posts: 36
Joined: Fri Jul 11, 2014 10:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by birdec » Tue Nov 08, 2016 1:45 pm

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
Posts: 1098
Joined: Thu Oct 20, 2011 12:23 am
Location: Calgary, AB
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by Peculiar_Investor » Fri Jan 06, 2017 8:54 am

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: 13
Joined: Thu Feb 11, 2016 2:41 pm

Re: A Returns Spreadsheet for Bogleheads

Post by nakatomi » Wed Feb 01, 2017 6:32 pm

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
Posts: 1098
Joined: Thu Oct 20, 2011 12:23 am
Location: Calgary, AB
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by Peculiar_Investor » Wed Feb 01, 2017 7:03 pm

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: 50
Joined: Tue Feb 09, 2016 4:39 pm

Re: A Returns Spreadsheet for Bogleheads

Post by ancho » Fri Mar 10, 2017 8:40 pm

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: 530
Joined: Thu Apr 23, 2015 8:47 pm

Re: A Returns Spreadsheet for Bogleheads

Post by GreatOdinsRaven » Fri Mar 10, 2017 11:20 pm

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.

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sat Mar 11, 2017 7:08 pm

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
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

User avatar
GreatOdinsRaven
Posts: 530
Joined: Thu Apr 23, 2015 8:47 pm

Re: A Returns Spreadsheet for Bogleheads

Post by GreatOdinsRaven » Sat Mar 11, 2017 7:22 pm

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.

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sat Mar 11, 2017 7:26 pm

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
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by sean.mcgrath » Sun Apr 09, 2017 7:45 am

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

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Apr 09, 2017 8:35 am

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!
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

moshe
Posts: 450
Joined: Thu Dec 12, 2013 1:18 pm
Location: Boston, MA

Re: A Returns Spreadsheet for Bogleheads

Post by moshe » 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.

Thank you,
~Moshe
My money has no emotions. ~Moshe | | I'm the world's greatest expert on my own opinion. ~Bruce Williams

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Mon Aug 14, 2017 9:21 am

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.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

moshe
Posts: 450
Joined: Thu Dec 12, 2013 1:18 pm
Location: Boston, MA

Re: A Returns Spreadsheet for Bogleheads

Post by moshe » Mon Aug 14, 2017 1:43 pm

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: 2
Joined: Fri Feb 09, 2018 8:51 am

Re: A Returns Spreadsheet for Bogleheads

Post by Drerwski » Fri Feb 09, 2018 9: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?

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Fri Feb 09, 2018 11:23 am

Drerwski wrote:
Fri Feb 09, 2018 9: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.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

Drerwski
Posts: 2
Joined: Fri Feb 09, 2018 8:51 am

Re: A Returns Spreadsheet for Bogleheads

Post by Drerwski » Fri Feb 09, 2018 10:51 pm

Thanks for the response.
Not sure exactly how, but after playing with it for a while I solve the problem.

airelleofmusic
Posts: 87
Joined: Mon May 01, 2017 8:54 am

Re: A Returns Spreadsheet for Bogleheads

Post by airelleofmusic » Wed Feb 28, 2018 5:42 pm

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 !

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Wed Feb 28, 2018 6:57 pm

airelleofmusic wrote:
Wed Feb 28, 2018 5: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?
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

airelleofmusic
Posts: 87
Joined: Mon May 01, 2017 8:54 am

Re: A Returns Spreadsheet for Bogleheads

Post by airelleofmusic » Thu Mar 01, 2018 3:26 am

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 !

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Thu Mar 01, 2018 7:22 am

airelleofmusic wrote:
Thu Mar 01, 2018 3: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.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

airelleofmusic
Posts: 87
Joined: Mon May 01, 2017 8:54 am

Re: A Returns Spreadsheet for Bogleheads

Post by airelleofmusic » Thu Mar 01, 2018 7: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).
LBYM and enjoy life ! Thanks BH !

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Thu Mar 01, 2018 7:58 am

airelleofmusic wrote:
Thu Mar 01, 2018 7: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.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

User avatar
1210sda
Posts: 1405
Joined: Wed Feb 28, 2007 8:31 am

Re: A Returns Spreadsheet for Bogleheads

Post by 1210sda » Thu Mar 01, 2018 8:40 am

Thank you Longinvest for all the tools you have provided for BH's.
1210

airelleofmusic
Posts: 87
Joined: Mon May 01, 2017 8:54 am

Re: A Returns Spreadsheet for Bogleheads

Post by airelleofmusic » Thu Mar 01, 2018 6:01 pm

@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: 4
Joined: Thu Feb 08, 2018 5:51 am

Re: A Returns Spreadsheet for Bogleheads

Post by rs397 » Fri Mar 02, 2018 5:06 am

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

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Fri Mar 02, 2018 7:51 am

rs397 wrote:
Fri Mar 02, 2018 5: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 5: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 5:06 am
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 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 5: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
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

J0721
Posts: 1
Joined: Fri Mar 09, 2018 8:37 pm

Re: A Returns Spreadsheet for Bogleheads

Post by J0721 » Fri Mar 09, 2018 8:50 pm

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!

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Mar 11, 2018 3:17 pm

J0721,
J0721 wrote:
Fri Mar 09, 2018 8: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 8: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 8: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.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

LinusCastle
Posts: 3
Joined: Thu May 17, 2018 7:46 pm

Return Spreadsheet For Bogleheads

Post by LinusCastle » Thu May 17, 2018 8:33 pm

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: 47393
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by LadyGeek » Thu May 17, 2018 9:14 pm

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 » 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

User avatar
Ketawa
Posts: 1933
Joined: Mon Aug 22, 2011 1:11 am
Location: DC

Re: A Returns Spreadsheet for Bogleheads

Post by Ketawa » Fri May 18, 2018 10:19 pm

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 » Sun May 20, 2018 8:12 am

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 » Fri Jun 22, 2018 5:12 pm

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: 1426
Joined: Sun Jul 06, 2014 3:46 pm

Re: A Returns Spreadsheet for Bogleheads

Post by randomizer » Sun Aug 19, 2018 4:17 pm

longinvest wrote:
Mon Feb 15, 2016 10: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 — HODL the course!

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Aug 19, 2018 4:22 pm

randomizer wrote:
Sun Aug 19, 2018 4:17 pm
longinvest wrote:
Mon Feb 15, 2016 10: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.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

User avatar
randomizer
Posts: 1426
Joined: Sun Jul 06, 2014 3:46 pm

Re: A Returns Spreadsheet for Bogleheads

Post by randomizer » Sun Aug 19, 2018 4:35 pm

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 — HODL the course!

spammagnet
Posts: 915
Joined: Wed Apr 27, 2016 9:42 pm

Re: A Returns Spreadsheet for Bogleheads

Post by spammagnet » Sun Aug 19, 2018 11:32 pm

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.
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

longinvest
Posts: 2907
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Mon Aug 20, 2018 6:19 am

spammagnet wrote:
Sun Aug 19, 2018 11:32 pm
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.
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.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

Post Reply