A Returns Spreadsheet for Bogleheads

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
User avatar
LadyGeek
Site Admin
Posts: 46754
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by LadyGeek » Sun Jan 04, 2015 12:16 pm

Cosmo wrote:
longinvest wrote:
Cosmo wrote:Dumb question. Anyone know if this is compatable with iMac's Excel equivalent, Numbers? Cosmo
It is definitely not a dumb question! Unfortunately, I do not have an iMac to try it.

Would you be willing to download the spreadsheet and open it with Numbers, and tell us if the "Returns" and "Data" sheets looks similar to the Wiki's screenshots (with identical numbers)?
Yes! I was planning on doing this later today. Whether it works or not, thanks for spending so much time on this endeavor. Cosmo
Thank you!

If it does not work in Numbers, LibreOffice Calc supports OS X: LibreOffice - Free Office Suite

Since longinvest is developing in LibreOffice Calc, I'd say the chances of this working in OS X are very good.
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.

mcguireb1
Posts: 5
Joined: Wed Jan 07, 2015 3:01 pm

Re: A Returns Spreadsheet for Bogleheads

Post by mcguireb1 » Wed Jan 07, 2015 3:10 pm

Thanks for the nice spreadsheet. I found it today and I am trying it. So far it looks like it will do what I want it to.

I have entered data from 2008 to current. On the Calculations page it only shows calculations to Nov 09. Any way to get it to calculate out to the current date?

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Wed Jan 07, 2015 4:25 pm

mcguireb1 wrote:Thanks for the nice spreadsheet. I found it today and I am trying it. So far it looks like it will do what I want it to.

I have entered data from 2008 to current. On the Calculations page it only shows calculations to Nov 09. Any way to get it to calculate out to the current date?
This would happen if there are missing data in December 09. You should see a red indicator on the right of the Balance column asking for missing data.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

mcguireb1
Posts: 5
Joined: Wed Jan 07, 2015 3:01 pm

Re: A Returns Spreadsheet for Bogleheads

Post by mcguireb1 » Wed Jan 07, 2015 6:24 pm

Oh, I see! I have to fill in all the zeros if there were no contributions or withdrawals! It worked! Thank you so very much! I love it.

One more suggestion. I have accounts at a couple different places. I also have an individual accounts and a Roth. It would be great to be able to have a calculation on all the accounts together. I can open a new spreadsheet and track each account separately but it would be nice to have an aggregated amount as well. Just an idea.

Thanks again for the work on this spreadsheet!

mcguireb1
Posts: 5
Joined: Wed Jan 07, 2015 3:01 pm

Re: A Returns Spreadsheet for Bogleheads

Post by mcguireb1 » Wed Jan 07, 2015 6:38 pm

Man, I hate to bug you. But one other thing that would be nice.

I would like to see the overall rate of return for the entire period, not annualized. So, if over the 5 years and 2 months time for example, the total dollar increase (less deposits and withdrawals) and the percentage rate. I don't know much about complicated spreadsheets. The basics yes, but this doosie it way beyond my simple skills.

Next time you are bored and just can't wait to tweak this wonderful thing a little, there is something to consider.

Again, thanks so much for this wonderful tool.

User avatar
SailingAway
Posts: 84
Joined: Wed Feb 09, 2011 6:14 pm
Location: Floating around somewhere

Re: A Returns Spreadsheet for Bogleheads

Post by SailingAway » Wed Jan 07, 2015 7:12 pm

Longinvest,
Really appreciate the spreadsheet. Obviously, a lot of work went into it. Would be nice if on all the "what was your return last year posts", we had a common way of calcing the returns for everyone. Anyway, I've tinkered around a little and notice you're not really asking for requests :beer , but I have cobbled together annual IIR's by year totals, as well as overall IRR. The running IRR is great, but I like to see the past IRR by year as well. Just a suggestion. The data is obviously already there, but I'm not good enough, or haven't tried hard enough to figure out how to break it out by year automatically. I'm just grabbing a year at a time, running the calc and posting them back to the returns page.

Just a suggestion.. Thanks again for all the hard work.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Wed Jan 07, 2015 8:01 pm

Mcguireb1 and SailingAway, thanks for the suggestions.

Feature requests:
  • Multiple-account handling.
  • Historical annual returns.
Mcguireb1, I'm not sure I understand your other request. Using the annualized compound return, I would simply use my calculator to compute ((1+return)^5.16667)-1 and get the cumulative return. I hesitate to display cumulative returns, as they'll cause more confusion than help. Everybody (almost) expects annual returns for periods longer than one year. One of the goals of this spreadsheet is to reduce confusion.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

mcguireb1
Posts: 5
Joined: Wed Jan 07, 2015 3:01 pm

Re: A Returns Spreadsheet for Bogleheads

Post by mcguireb1 » Wed Jan 07, 2015 8:57 pm

Alright, I am fine with no cumulative return.

Thanks for the formula. But I am an amateur and don't know what that means. Can you explain how to use it?

Thank you for all your help.

crg11
Posts: 411
Joined: Sat Jan 04, 2014 8:16 am

Re: A Returns Spreadsheet for Bogleheads

Post by crg11 » Wed Jan 07, 2015 9:07 pm

LadyGeek wrote:
Cosmo wrote:
longinvest wrote:
Cosmo wrote:Dumb question. Anyone know if this is compatable with iMac's Excel equivalent, Numbers? Cosmo
It is definitely not a dumb question! Unfortunately, I do not have an iMac to try it.

Would you be willing to download the spreadsheet and open it with Numbers, and tell us if the "Returns" and "Data" sheets looks similar to the Wiki's screenshots (with identical numbers)?
Yes! I was planning on doing this later today. Whether it works or not, thanks for spending so much time on this endeavor. Cosmo
Thank you!

If it does not work in Numbers, LibreOffice Calc supports OS X: LibreOffice - Free Office Suite

Since longinvest is developing in LibreOffice Calc, I'd say the chances of this working in OS X are very good.
I haven't tried the newest version in Numbers, but the initial 1.0 version worked perfectly.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Wed Jan 07, 2015 11:43 pm

mcguireb1 wrote:Alright, I am fine with no cumulative return.

Thanks for the formula. But I am an amateur and don't know what that means. Can you explain how to use it?

Thank you for all your help.
Here's how to transform an annualized 4.7% return into a 5 year 2 months cumulative return:
  • Divide the months by 12 and add them to the year to obtain the length in years: 2/12 = 0.16667 => length = 5.16667 years.
  • Add 1 to the return: 1 + 4.7% = 1.047
  • Using a scientific or financial calculator:
    1. Type 1.047
    2. Press the Y^x (Y to the power x) function
    3. Type 5.16667
    4. Press =
    5. Subtract 1
On my calculator, I get 0.268 which is 26.8%. This would be the cumulative return on a dollar invested in the portfolio for 5 years 2 months.
Last edited by longinvest on Wed Jan 07, 2015 11:45 pm, edited 1 time in total.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Wed Jan 07, 2015 11:44 pm

crg11 wrote:I haven't tried the newest version in Numbers, but the initial 1.0 version worked perfectly.
Thanks for the report!
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

mcguireb1
Posts: 5
Joined: Wed Jan 07, 2015 3:01 pm

Re: A Returns Spreadsheet for Bogleheads

Post by mcguireb1 » Thu Jan 08, 2015 3:02 pm

Thanks so much for the explanation. I could not figure out where the 5.16667 came from.

That is very helpful.

crg11
Posts: 411
Joined: Sat Jan 04, 2014 8:16 am

Re: A Returns Spreadsheet for Bogleheads

Post by crg11 » Fri Jan 09, 2015 8:13 pm

Been playing around with this spreadsheet, very happy with its simplicity.

Weird thing is the investor return (14.64%) is WAY different than a XIRR return I calculated in another spreadsheet (7.03%). I must have messed up the XIRR return somehow, maybe included dividends as a contribution or something. Need to dive in more for piece of mind.
Last edited by crg11 on Fri Jan 09, 2015 9:09 pm, edited 1 time in total.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Fri Jan 09, 2015 8:15 pm

crg11 wrote:Been playing around with this spreadsheet, very happy with its simplicity.

Weird thing is the investor return (14.64%) is WAY different than a XIRR return I calculated in another spreadsheet (7.03%). I must have messed up the XIRR return somehow, maybe included dividends as a contribution or something. Need to dive in more for piece of mind.
Investor return is annualized, even for periods shorter than 1 year. I think that XIRR does not annualize shorter than 1 year returns. See next post.
Last edited by longinvest on Fri Jan 09, 2015 8:34 pm, edited 1 time in total.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

User avatar
LadyGeek
Site Admin
Posts: 46754
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by LadyGeek » Fri Jan 09, 2015 8:27 pm

When verifying your returns, be aware that this spreadsheet uses an approximation as noted by longinvest's post from the first page:
longinvest wrote:The spreadsheet approximates (monthly) the effect of irregular cash flows using W. Bernstein's formula (from The Four Pillars of Investing) for calculating time-weighted returns:

cash_flow = contributions - witdrawals
return = (end_balance - cash_flow/2) / (start_balance + cash_flow/2) - 1

As an approximation of XIRR, the spreadsheet splits half cash-flows at month start and end, then computes a regular IRR on the resulting cash flows.

You could compare an exact XIRR calculation with the spreadsheet's approximate one; I'm pretty sure the result will be similar.

The big advantage of the spreadsheet's approach is simplicity. One only needs to provide 3 numbers, for each month: contributions, withdrawals, and portfolio balance.

Using a single set of such data, the spreadsheet will compute both investor returns (IRR) and portfolio returns (time-weighted returns).
XIRR is (by definition) an annualized return. Here's a start: Internal rate of return

We have a few helpful forum threads: XIRR function again and Using XIRR in Excel

That's not to say there's any problem with the approximations. However, when you don't understand what's going on, you need to start from a baseline reference. This may be overkill for crg11's problem (may just be an incorrect data entry), but it's important to keep the perspective in mind.
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.

crg11
Posts: 411
Joined: Sat Jan 04, 2014 8:16 am

Re: A Returns Spreadsheet for Bogleheads

Post by crg11 » Fri Jan 09, 2015 9:00 pm

I think I see the problem...the contributions should include employer contributions, correct?

When I filled in this spreadsheet, I only included my contributions, not employer.

Update: Yep, that was my error! That's what I get for playing with finances on a Friday night. The investor return is now 7.03%, which exactly matches my XIRR return. If that isn't a stamp of approval, I don't know what is.
Last edited by crg11 on Fri Jan 09, 2015 9:09 pm, edited 1 time in total.

User avatar
LadyGeek
Site Admin
Posts: 46754
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by LadyGeek » Fri Jan 09, 2015 9:08 pm

crg11 wrote:I think I see the problem...the contributions should include employer contributions, correct?

When I filled in this spreadsheet, I only included my contributions, not employer.
I'd say employer contributions should count. Remember that the spreadsheet doesn't know and doesn't care where the money comes from. It only cares that the numbers balance (inflow equals outflow).

This picture might help: Cash flow diagrams
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.

crg11
Posts: 411
Joined: Sat Jan 04, 2014 8:16 am

Re: A Returns Spreadsheet for Bogleheads

Post by crg11 » Fri Jan 09, 2015 9:10 pm

Thanks, LadyGeek.

penumbra
Posts: 260
Joined: Thu Mar 29, 2007 2:42 am

Re: A Returns Spreadsheet for Bogleheads

Post by penumbra » Sat Jan 10, 2015 11:56 pm

I have used your spreadsheet on a Mac with Excel. It is fantastic, exactly what I was looking for! Can't thank you enough for all the effort.

What I was unable to do is paste a set of values into the sheet; I have to enter the dollar amount manually, but it does work. If I use the Dropbox version, can I paste in values, do you suppose?

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Jan 11, 2015 9:03 am

penumbra wrote:I have used your spreadsheet on a Mac with Excel. It is fantastic, exactly what I was looking for! Can't thank you enough for all the effort.

What I was unable to do is paste a set of values into the sheet; I have to enter the dollar amount manually, but it does work. If I use the Dropbox version, can I paste in values, do you suppose?
I do not know. Maybe a Mac user can help you.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

User avatar
LadyGeek
Site Admin
Posts: 46754
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by LadyGeek » Sun Jan 11, 2015 9:09 am

I think penumbra was asking 2 questions. The first is about copy-n-paste, the second about Dropbox.

On copy-n-paste:

- Check that the sheet is unprotected.
- Try copying the amounts from Mac Excel to a text editor (plain text editor, not a word processor), then from the text editor to Mac Excel. That should work.
- Try using "Paste Special" --> Values. In theory, that's no different than copy-paste. However, compatibility problems don't often make sense.

On Dropbox:

- The Dropbox and Google versions are the same file, just saved at different websites.
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.

pshonore
Posts: 6419
Joined: Sun Jun 28, 2009 2:21 pm

Re: A Returns Spreadsheet for Bogleheads

Post by pshonore » Sun Jan 11, 2015 5:11 pm

LadyGeek wrote:I think penumbra was asking 2 questions. The first is about copy-n-paste, the second about Dropbox.

On copy-n-paste:

- Check that the sheet is unprotected.
- Try copying the amounts from Mac Excel to a text editor (plain text editor, not a word processor), then from the text editor to Mac Excel. That should work.
- Try using "Paste Special" --> Values. In theory, that's no different than copy-paste. However, compatibility problems don't often make sense.
Copy-paste takes whatever is in the "from" cell (formula, number, etc.) and puts it in the destination unchanged. Paste special - values puts in the result of any formula. Big difference if you're not expecting it.

User avatar
LadyGeek
Site Admin
Posts: 46754
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by LadyGeek » Sun Jan 11, 2015 5:34 pm

Yes, but compatibility issues are very difficult to debug when you can't reproduce the problem yourself. You have to try a few things ("shotgun approach") and see what happens.
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.

kulvir
Posts: 2
Joined: Sun Jan 11, 2015 8:48 pm

Re: A Returns Spreadsheet for Bogleheads

Post by kulvir » Sun Jan 11, 2015 8:54 pm

Thanks for the spreadsheet.

I am a novice with excel. Would you kindly share with me how I extend the dates into 2015 and beyond. For exmaple, as I am just starting my start year is 2014 and my start month is 12. Once I enter that onto the data sheet I cant seem to figure out how to add the subsequent months. I am sure this is super simple but again excel is not a platform I have otherwise had a use for.

Thanks!

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Jan 11, 2015 9:18 pm

kulvir wrote:Thanks for the spreadsheet.

I am a novice with excel. Would you kindly share with me how I extend the dates into 2015 and beyond. For exmaple, as I am just starting my start year is 2014 and my start month is 12. Once I enter that onto the data sheet I cant seem to figure out how to add the subsequent months. I am sure this is super simple but again excel is not a platform I have otherwise had a use for.

Thanks!
Kulvir, you only have to wait until the end of the month, and the "January 2015" entry will automatically appear.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

kulvir
Posts: 2
Joined: Sun Jan 11, 2015 8:48 pm

Re: A Returns Spreadsheet for Bogleheads

Post by kulvir » Sun Jan 11, 2015 9:39 pm

Beautiful! Thats like magic :happy

I spent hours searching for a proprietary tool like this before Christmas and to my surprise I couldn't really find much.

I really appreciate the spreadsheet and the assistance.

chiseller
Posts: 51
Joined: Mon Aug 25, 2014 7:55 pm

Re: A Returns Spreadsheet for Bogleheads

Post by chiseller » Thu Mar 12, 2015 8:36 pm

Thanks for the spreadsheet longinvest. I've been looking for something like this.

elliott908
Posts: 2
Joined: Mon Apr 13, 2015 11:35 am

Re: A Returns Spreadsheet for Bogleheads

Post by elliott908 » Thu Apr 30, 2015 12:54 pm

Thanks for creating this fantastic spreadsheet. I had two questions:
1. Employer matching contributions to my 403b should be listed as part of each month's contributions, correct?
2. I have monthly dividends that are paid out as a check, not reinvested. Should those be considered a withdrawal?

Thanks!

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Thu Apr 30, 2015 2:25 pm

elliott908 wrote:Thanks for creating this fantastic spreadsheet. I had two questions:
You're welcome!
elliott908 wrote:1. Employer matching contributions to my 403b should be listed as part of each month's contributions, correct?
Correct.
elliott908 wrote:2. I have monthly dividends that are paid out as a check, not reinvested. Should those be considered a withdrawal?
Yes.

:thumbsup
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

Dandy
Posts: 5216
Joined: Sun Apr 25, 2010 7:42 pm

Re: A Returns Spreadsheet for Bogleheads

Post by Dandy » Fri May 01, 2015 4:18 pm

Interesting - I listened to a Rick Ferri podcast/interview on Bloomberg Masters. He was discussing his approach. He said when potential clients ask about returns he doesn't feel that they get it - i.e. broad based index funds aren't about what is your return - it is the return of the index -less low expense. He is not competing with advisors that tout returns. You aren't buying superior returns you are buying the market and some intellectual property to set up and maintain your investment.

I always wondered when people on this board say something about returns - what are you going to do with the info? You should be mostly getting the returns of the low cost, broad based funds for the most part. Are you going to up your equity allocation? That isn't what should be driving that decision.

chessmannextmove
Posts: 278
Joined: Mon Feb 23, 2015 1:57 pm

Re: A Returns Spreadsheet for Bogleheads

Post by chessmannextmove » Fri May 01, 2015 4:21 pm

Longinvest.... You are the man!

teddykgb55
Posts: 38
Joined: Fri Feb 20, 2015 11:50 pm

Re: A Returns Spreadsheet for Bogleheads

Post by teddykgb55 » Sat May 02, 2015 1:35 pm

This is an incredible spreadsheet. I'm early in my investing career and it will be very interesting to see the results over a long investing horizon. Thank you for the time and energy in putting this together!

User avatar
digarei
Posts: 767
Joined: Sat Jul 05, 2014 1:41 am
Location: Sacramento
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by digarei » Sun May 03, 2015 2:57 am

Dandy wrote:I always wondered when people on this board say something about returns - what are you going to do with the info? You should be mostly getting the returns of the low cost, broad based funds for the most part. Are you going to up your equity allocation? That isn't what should be driving that decision.
  • emphasis added
This is a fair question but I think the answer is basic to the reason for investing. Your concern is that this information may be misused and lead to ill-advised decisions, correct?

I think simply, so please let me know if this doesn't correspond with reality. Wealth accretion, as precursor to financial independence, requires putting our savings at risk in the hope of gain. If our invested money is lost or reduced in value, we've gained nothing and shouldn't have played the game.

The return, then, is just a metric that objectifies our judgment of our own investing success, to compare investment vehicles, to make plans, establish objectives, assess progress and so forth. For example, I know that an investment in Total Stock Market Idx should provide more gain over time than insured savings from looking at historical returns for the fund/asset class.

Personal investment (portfolio) returns help knowledgable investors make good decisions, and may prompt novice investors to ask pertinent questions. "The market index return for portfolio of X + Y was 14%. Why did my portfolio underperform by 3.5%? I wonder if those fees I read about ('assets under management'? 'loads'?) are hurting my investments?"

And if some people use this tool to switch in and out of funds, or misuse it when comparing dissimilar investments? Though cautioning against such behavior is fully merited, I don't believe, as some apparently do here, that the answer is to avert ones eyes or pretend that investment returns don't matter. Of course, they do.
Connect with Bogleheads in Northern California! Click the link under my user info/avatar.

jay22
Posts: 702
Joined: Thu Aug 23, 2012 8:56 am
Location: Sacramento, CA

Re: A Returns Spreadsheet for Bogleheads

Post by jay22 » Sun May 03, 2015 11:27 am

Best spreadsheet ever. I maintain a couple of spreadsheets for investment purposes, but they are no where near as sophisticated as this one. Cannot thank you enough for putting the effort and creating this. :sharebeer

User avatar
Yesterdaysnews
Posts: 424
Joined: Sun Sep 14, 2014 1:25 pm

Re: A Returns Spreadsheet for Bogleheads

Post by Yesterdaysnews » Sun May 03, 2015 12:54 pm

Thanks for this!

I like to track the return of my investment portfolio and compared it to my 401k which is 100% in an S&P 500 index fund.

This gives me an idea if my active management of my portfolio (kinda a hobby of sorts) is worthwhile compared to a "set it and forget it" investment style using the S&P index fund.

I am not necessarily looking to outperform every year but rather limit large losses/draw-downs.

wk522
Posts: 5
Joined: Tue May 12, 2015 8:43 pm

Re: A Returns Spreadsheet for Bogleheads

Post by wk522 » Tue May 12, 2015 8:46 pm

This is great.I have maintained a spreadsheet for about 5 years and update it with additional features as I find them on the web. This was the :D :D :D one piece of info that had always been missing. Thanks for all your hard work. Please let us know if you update it.

freebz
Posts: 8
Joined: Wed Apr 29, 2015 2:38 pm

Re: A Returns Spreadsheet for Bogleheads

Post by freebz » Fri May 29, 2015 5:34 pm

Ok so I just started using this, My start date being May 2015.

So I went ahead and plugged in my monthly contribution of $458.00 and a balance as of today but the excel sheet doesn't seem to want to throw in anymore months or dates in columns A,D on the data tab.

Am I doing something wrong?

TareNeko
Posts: 581
Joined: Wed Aug 14, 2013 1:27 pm

Re: A Returns Spreadsheet for Bogleheads

Post by TareNeko » Fri May 29, 2015 6:03 pm

freebz wrote:Ok so I just started using this, My start date being May 2015.

So I went ahead and plugged in my monthly contribution of $458.00 and a balance as of today but the excel sheet doesn't seem to want to throw in anymore months or dates in columns A,D on the data tab.

Am I doing something wrong?
It will do that automatically when we are in June.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Jul 05, 2015 5:44 pm

I've redeveloped the spreadsheet using Google Docs, so that the spreadsheet could easily be used online.

But, before officially publishing this new version, I'd like to get feedback about it. (It also adds multiple account handling, historical annual returns, and doesn't annualize investor return for period less than one year).

Here's the a link to this new version: https://goo.gl/5NSnKi (Edited: the link has been fixed.)

Added:
If you have a Google account, you can modify the spreadsheet online to add your portfolio data and calculate your returns.

Here's how to proceed:
  1. Follow the above link.
  2. Sign in your Google account (if not already signed in).
  3. Make a copy of the file as follows:
    • File -> Make a copy...
  4. The copy is now yours to play with!


I'm facing a really big dilemma; the growth of $10,000 chart looks nice in Google Docs, but is just terrible in OpenOffice (I don't have Excel). Yet, it's so convenient to be able to use an online spreadsheet.

Mainly, I'm faced with the following choices:
  1. Revert back to using LibreOffice to develop the spreadsheet, as it will also work and display OK under Excel, but it won't work in Google Docs.
  2. Use Google Docs to develop the spreadsheet, but the chart won't look nice under LibreOffice (and Excel?).
What's your opinion?
Last edited by longinvest on Sun Jul 05, 2015 10:20 pm, edited 2 times in total.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Jul 05, 2015 9:17 pm

I have fixed the link (in the previous post) another time. It should now directly open the document as a Google Sheet (Yay!).
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

User avatar
LadyGeek
Site Admin
Posts: 46754
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by LadyGeek » Sun Jul 05, 2015 10:13 pm

Yes, it does. Upon first opening the downloaded file in MS Excel, the warning about "Protected View" appears, hinting it might be unsafe. Ignore this warning, it's a compatibility issue.

The graph initially shows all zeros (compatibility problem with Google export). When I enabled editing, the graph updated itself and fixed the problem - all OK.

From my perspective, there's no advantage to develop in Google Docs because you aren't using any of the online Google Finance functions.

To make things easy for you, just develop in LibreOffice Calc and save the file as .xlsx format. From there, anyone can simply work in LibreOffice Calc, MS Excel, or upload to Google Docs (Sheets) and work online as they wish.

This is starting to sound like java - write once, test everywhere. :)

Update: I did a quick test. Enter the value of "42" in Account1 cell E18 (8/31/2013 balance). This may not be a realistic situation, I'm just testing the limits. Consider auto-scaling the graph. Also, the returns after that point stay negative and trend in a negative direction. Is this correct?
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.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Jul 05, 2015 10:35 pm

LadyGeek wrote:Update: I did a quick test. Enter the value of "42" in Account1 cell E18 (8/31/2013 balance). This may not be a realistic situation, I'm just testing the limits. Consider auto-scaling the graph. Also, the returns after that point stay negative and trend in a negative direction. Is this correct?
Good test! You're really testing the limits of Bernstein's approximation formula, though. 42 causes the approximation to estimate a loss greater than 100%, which the spreadsheet is not able to handle correctly. (Look at cell Calculations!I7). The problem is that the approximation assumes that the investor added half of $1,300 at month end, but the final portfolio value $42 was less than $650, which means that the portfolio had to get negative before the $650 contribution.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Jul 05, 2015 10:57 pm

LadyGeek wrote:Yes, it does. Upon first opening the downloaded file in MS Excel, the warning about "Protected View" appears, hinting it might be unsafe. Ignore this warning, it's a compatibility issue.
That's one thing that really annoys me. You and me know that it's simply a compatibility issue, but why should someone else trust us that it is so, instead of trusting Microsoft's products? I know some non-technical people that have been burned by viruses that would not agree to ignore the warning.

So, what I like about the use of a Google Sheet is that the spreadsheet is safely residing in the internet, keeping their computer safe. If they don't trust me, they can still use it online. If they trust me, they can download it and ignore the warning.
LadyGeek wrote: From my perspective, there's no advantage to develop in Google Docs because you aren't using any of the online Google Finance functions.
A spreadsheet always works and looks best on the platform it is developed. The advantage I see is that using Google Docs makes the spreadsheet look nicer on first contact. You can call that marketing, if you want. Also, it launches the spreadsheet into the 21st century (cloud computing).
LadyGeek wrote: To make things easy for you, just develop in LibreOffice Calc and save the file as .xlsx format. From there, anyone can simply work in LibreOffice Calc, MS Excel, or upload to Google Docs (Sheets) and work online as they wish.
I don't know which is really easier. In full-screen mode, Google Sheet did really feel quite similar to LibreOffice. There are fewer chart options, though, and I see no way to reconcile Google Sheet and LibreOffice. When the chart works well in one, it doesn't in the other. :annoyed
LadyGeek wrote: This is starting to sound like java - write once, test everywhere. :)
Awful, I know.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Jul 05, 2015 11:28 pm

I've discovered Google Sheet's Time line chart. It looks much better and is more flexible than the previous chart:
Image

It doesn't show at all in LibreOffice, though. (Maybe that's better than a broken chart). I don't know about it's Excel compatibility.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Mon Jul 06, 2015 8:24 am

LadyGeek, I'm getting to the point where I think that I should bite the bullet and maintain three separate versions of the spreadsheet:
  • a LibreOffice version, as it provides long-term sustainability (open-source software),
  • a Google Docs (Sheets) version, as it provides attractiveness by working at the end of a click even on a mobile device, and
  • a Microsoft Excel version (I'll have to buy Windows and Microsoft Office licenses and install them in a virtual machine), as it is the most popular spreadsheet software.
The main advantage of multiple versions would be that each version would work perfectly "out of the box": no more warnings on Excel downloads, and version-specific charts that display correctly.

I could probably get away with maintaining a single master version in Google, as it is able to export to the other two formats. I would then fix the layout and regenerate the charts in the exported .ods and .xlsx files, and upload these modified files.

Users would have the choice of clicking on a direct link to a Google Docs (Sheets) document, or download links for LibreOffice (.ods) and Excel (.xslx) documents.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

TareNeko
Posts: 581
Joined: Wed Aug 14, 2013 1:27 pm

Re: A Returns Spreadsheet for Bogleheads

Post by TareNeko » Mon Jul 06, 2015 9:47 am

Thank you for your work on this, it is really appreciated.

Would it be possible to generate separate account tabs based on someone's actual account numbers instead of a default of 9?

Also, since the there are multiple account tabs, return/performance of each tab can be useful.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Mon Jul 06, 2015 10:25 am

TareNeko wrote:Thank you for your work on this, it is really appreciated.

Would it be possible to generate separate account tabs based on someone's actual account numbers instead of a default of 9?
You can already do this easily! Just rename the tabs you use*, and delete hide the ones you don't use. Don't delete unused sheets; that will break the calculations.

* There is a menu for this on the tab itself; just click on the small inverted rectangle and choose "Rename..." (or "Hide Sheet").
Image
TareNeko wrote:Also, since the there are multiple account tabs, return/performance of each tab can be useful.
Looking at the return of a single account in isolation can cause behavioral mistakes:
https://www.bogleheads.org/wiki/Asset_a ... r_behavior
This philosophy of trying to protect investors from this behavioral pitfall is expressed by Rick Ferri: "[T]here is a hidden risk with having different allocations in taxable versus non-taxable, and we saw this risk turn into reality during 2008 and early 2009. A few clients terminated their higher risk taxable portfolio because that specific portfolio was losing more money than the more conservative non-taxable portfolio. In other words, they separated their portfolios in their mind and compared returns rather than looking at the big picture."
Note that if one has legitimate reasons to look at the isolated return of a specific account (maybe because it is an actively managed account), then it is easy enough to simply create another return spreadsheet copy just for it.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

User avatar
LadyGeek
Site Admin
Posts: 46754
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: A Returns Spreadsheet for Bogleheads

Post by LadyGeek » Mon Jul 06, 2015 8:39 pm

longinvest wrote:
LadyGeek wrote:Update: I did a quick test. Enter the value of "42" in Account1 cell E18 (8/31/2013 balance). This may not be a realistic situation, I'm just testing the limits. Consider auto-scaling the graph. Also, the returns after that point stay negative and trend in a negative direction. Is this correct?
Good test! You're really testing the limits of Bernstein's approximation formula, though. 42 causes the approximation to estimate a loss greater than 100%, which the spreadsheet is not able to handle correctly. (Look at cell Calculations!I7). The problem is that the approximation assumes that the investor added half of $1,300 at month end, but the final portfolio value $42 was less than $650, which means that the portfolio had to get negative before the $650 contribution.
This is a good example on the consequences of extrapolating performance to predict future results. That's why it's important to state the conditions the numbers were calculated - the date is very important.
longinvest wrote:LadyGeek, I'm getting to the point where I think that I should bite the bullet and maintain three separate versions of the spreadsheet:
  • a LibreOffice version, as it provides long-term sustainability (open-source software),
  • a Google Docs (Sheets) version, as it provides attractiveness by working at the end of a click even on a mobile device, and
  • a Microsoft Excel version (I'll have to buy Windows and Microsoft Office licenses and install them in a virtual machine), as it is the most popular spreadsheet software.
The choice is up to you, as it's a mix of complexity and use of your time. (You have another spreadsheet to work on...)

Hold-off on purchasing Excel. If you're interested in cloud computing, take a look at Free Microsoft Office Online, Word, Excel, Powerpoint. You wouldn't need to purchase Windows, either - but I'm not sure on that. I don't know the limitations on this product, but give it a try.
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.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Mon Jul 06, 2015 9:23 pm

LadyGeek wrote:
longinvest wrote:
LadyGeek wrote:Update: I did a quick test. Enter the value of "42" in Account1 cell E18 (8/31/2013 balance). This may not be a realistic situation, I'm just testing the limits. Consider auto-scaling the graph. Also, the returns after that point stay negative and trend in a negative direction. Is this correct?
Good test! You're really testing the limits of Bernstein's approximation formula, though. 42 causes the approximation to estimate a loss greater than 100%, which the spreadsheet is not able to handle correctly. (Look at cell Calculations!I7). The problem is that the approximation assumes that the investor added half of $1,300 at month end, but the final portfolio value $42 was less than $650, which means that the portfolio had to get negative before the $650 contribution.
This is a good example on the consequences of extrapolating performance to predict future results. That's why it's important to state the conditions the numbers were calculated - the date is very important.
You really got me looking deeper at what was happening there. I've been thinking about it ever since you posted this.

I am now convinced that my first assessment (e.g. my answer to you, above) was incorrect; the observed growth behavior is not due to Bernstein's approximation. It is also false to say that the spreadsheet cannot cope with a loss bigger than 100%.

The problem was in my own understanding of the chart.


Here's a simple example to illustrate my new understanding.

I invest $100 in some investment A in January 2000. In June 2000, A goes bankrupt (leaving me with a portfolio of 0$). In January 2001, I make a new $100 investment in company B. In January 2002, my portfolio is valued $400 (B has quadrupled).

If I was to chart a growth of $10,000 chart of my portfolio, what would it look like?

To answer this, I must first understand what this graph is meant to illustrate. It is meant to illustrate the growth of a single lump sum investment in the personal portfolio managed by longinvest from January 2000 to January 2002.

What we know is that, had somebody invested $10,000 in January 2000 in longinvest's portfolio, the $10,000 would have dropped to $0 in June 2000. As no new money is added, this $0 doesn't growth back higher than $0 when longinvest's management (or luck?) improves in 2001, growing the portfolio by 300% in a single year. Why? Simply because $0 + 300% = $0.

So, the chart must necessarily look as a line dropping from $10,000 to $0 within the first 6 months of 2000, and then staying there.


What about losses bigger than 100%?

A growth of $10,000 always assumes that the "remaining" investment grows at the same rate as the portfolio. What does it mean to grow -$100 by 5%? If I grow a $100 debt by 5%, it will transform into a $105 debt. So, -$100 "grows" to -$105.

In other words, when the balance gets negative, a growth of $10,000 assumes that the investor is now shorting the market!


Interesting, isn't it?
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Mon Jul 06, 2015 10:02 pm

LadyGeek wrote:Hold-off on purchasing Excel. If you're interested in cloud computing, take a look at Free Microsoft Office Online, Word, Excel, Powerpoint. You wouldn't need to purchase Windows, either - but I'm not sure on that. I don't know the limitations on this product, but give it a try.
Thanks for the suggestion! I'll try that.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international)stocks/(nominal/inflation-indexed)bonds | VCN/VXC/VLB/ZRR

Post Reply