A Returns Spreadsheet for Bogleheads

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
longinvest
Posts: 3108
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Tue Jul 07, 2015 7:51 pm

I have just uploaded a new version (1.8) of the Bogleheads returns spreadsheet.

As usual, you'll find the download links on the wiki:Calculating personal returns page.

This version bring significant improvements and comes in three flavors:
  1. Google Docs - Sheets (online) NEW!
  2. Microsoft Office - Excel (.xlsx)
  3. LibreOffice - Calc (.ods)
Changes:
  • Support multiple accounts.
  • Provide historical annual returns.
  • Improve the Growth of $10,000 chart (custom chart for each of the three spreadsheet programs).
  • Detect cells with missing or extraneous data.
Enjoy!
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

xycodex@gmail.com
Posts: 1
Joined: Mon Aug 03, 2015 4:35 pm

Re: A Returns Spreadsheet for Bogleheads

Post by xycodex@gmail.com » Mon Aug 03, 2015 4:36 pm

Is there a way to change the start date? I want to see my stats from 2011.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Mon Aug 03, 2015 6:40 pm

xycodex@gmail.com wrote:Is there a way to change the start date? I want to see my stats from 2011.
Yes, on the Portfolio tab. There are entries for Start Year and Month.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

Aish
Posts: 128
Joined: Thu Mar 20, 2014 7:49 pm

Re: A Returns Spreadsheet for Bogleheads

Post by Aish » Mon Aug 03, 2015 8:28 pm

A nice feature now that we have separate accounts would be to show the overall rate of return for each account separately.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Tue Aug 04, 2015 6:52 am

Aish wrote:A nice feature now that we have separate accounts would be to show the overall rate of return for each account separately.
Looking at the returns 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 use a separate return spreadsheet just for it.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

beermike
Posts: 1
Joined: Sat Jul 18, 2015 7:34 pm
Location: Canada

Re: A Returns Spreadsheet for Bogleheads

Post by beermike » Sat Aug 15, 2015 4:42 pm

Excellent spreadsheet, thanks for the great effort!
Do you think there would be any value in having a summary/breakdown of contributions vs investment gains?

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Aug 16, 2015 11:36 am

beermike wrote:Excellent spreadsheet, thanks for the great effort!
Do you think there would be any value in having a summary/breakdown of contributions vs investment gains?
You're welcome.

There are both contributions and withdrawals. How would you summarize this? What would be the use of this information?
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

bayview
Posts: 1565
Joined: Thu Aug 02, 2012 7:05 pm
Location: WNC

Re: A Returns Spreadsheet for Bogleheads

Post by bayview » Thu Sep 03, 2015 5:34 am

The only data that I've entered has been for 2015. Now that I don't have much to do other than watch my daily balances go roller-coastering :D , I'd like to add earlier dates, contributions, and so forth.

I cautiously poked around, trying to figure out how, but everything seemed to involve messing with the dreaded grey cells. Do I just enter a bunch of rows and ignore the warnings, and let it untangle itself when I'm done? I have quite a few years' worth of stuff to enter, so I'd like to not destroy what I've done so far.

Or do I just start all over from 1990-whatever?

I have this running in both Google Docs and Excel.
The continuous execution of a sound strategy gives you the benefit of the strategy. That's what it's all about. --Rick Ferri

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Thu Sep 03, 2015 6:10 am

bayview wrote:The only data that I've entered has been for 2015. Now that I don't have much to do other than watch my daily balances go roller-coastering :D , I'd like to add earlier dates, contributions, and so forth.

I cautiously poked around, trying to figure out how, but everything seemed to involve messing with the dreaded grey cells. Do I just enter a bunch of rows and ignore the warnings, and let it untangle itself when I'm done? I have quite a few years' worth of stuff to enter, so I'd like to not destroy what I've done so far.

Or do I just start all over from 1990-whatever?

I have this running in both Google Docs and Excel.
bayview,

I would start a new spreadsheet, but I would save some time by copy/pasting existing data from the older spreadsheet to the appropriate rows of the new one.

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

sharpjm
Posts: 657
Joined: Fri Feb 20, 2015 2:41 pm

Re: A Returns Spreadsheet for Bogleheads

Post by sharpjm » Thu Sep 03, 2015 9:15 am

I tried your spreadsheet the other day and got a ytd return that was about 0.5% off from my own calculations. I believe this was caused by the granularity with using 12 periods in your spreadsheet, while I use 26 periods since I am paid every 2 weeks.

Would it be possible to add a feature where a user can select between monthly contributions and biweekly contributions?

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Thu Sep 03, 2015 9:52 am

sharpjm wrote:I tried your spreadsheet the other day and got a ytd return that was about 0.5% off from my own calculations. I believe this was caused by the granularity with using 12 periods in your spreadsheet, while I use 26 periods since I am paid every 2 weeks.

Would it be possible to add a feature where a user can select between monthly contributions and biweekly contributions?
A 0.5% difference is pretty big. Usually, differences are much smaller than that. This may be caused by having a very short history and big contributions (relative to portfolio size). You should also note that the latest spreadsheet version does not annualize returns for periods shorter than one year.

It would be helpful if you provided more information so that I could preproduce such a big difference in calculations and verify if there really is a problem.

(Think about it; by aggregating monthly contributions and withdrawals, it introduces a maximum timing error of 30 days. When measuring multi-year returns, this is just small noise. For short periods, it's also small noise when contributions and withdrawals are small relative to portfolio size.)
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

sharpjm
Posts: 657
Joined: Fri Feb 20, 2015 2:41 pm

Re: A Returns Spreadsheet for Bogleheads

Post by sharpjm » Thu Sep 03, 2015 10:20 am

longinvest wrote:
sharpjm wrote:I tried your spreadsheet the other day and got a ytd return that was about 0.5% off from my own calculations. I believe this was caused by the granularity with using 12 periods in your spreadsheet, while I use 26 periods since I am paid every 2 weeks.

Would it be possible to add a feature where a user can select between monthly contributions and biweekly contributions?
A 0.5% difference is pretty big. Usually, differences are much smaller than that. This may be caused by having a very short history and big contributions (relative to portfolio size). You should also note that the latest spreadsheet version does not annualize returns for periods shorter than one year.

It would be helpful if you provided more information so that I could preproduce such a big difference in calculations and verify if there really is a problem.

(Think about it; by aggregating monthly contributions and withdrawals, it introduces a maximum timing error of 30 days. When measuring multi-year returns, this is just small noise. For short periods, it's also small noise when contributions and withdrawals are small relative to portfolio size.)
My monthly contributions are roughly 1.2% of my portfolio right now. I'm not sure if that is considered large relative to the balance. I only compared ytd performance, so a 9 month window. My calculations also find an average return, rather than a return during each period and finding the cumulative based on those periods.

Here is my calc for each period

Code: Select all

new_balance = contributions_during_period + previous_balance*( 1 + annualized_return/26/100 )
% note:  divide by 100 to get %
ytd_return = annualized_return*current_period/26
Then it is just an optimization problem by changing annualized_return such that my actual current balance matches the new_balance value.

Perhaps the issue is because I am calculating an averaged return.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Thu Sep 03, 2015 10:39 am

sharpjm wrote: My monthly contributions are roughly 1.2% of my portfolio right now. I'm not sure if that is considered large relative to the balance. I only compared ytd performance, so a 9 month window. My calculations also find an average return, rather than a return during each period and finding the cumulative based on those periods.
It's simply impossible for such small contributions (relative to portfolio size) to cause such a big difference on the return of a 9-month period. So, the problem must be with your calculations.
sharpjm wrote:Here is my calc for each period

Code: Select all

new_balance = contributions_during_period + previous_balance*( 1 + annualized_return/26/100 )
% note:  divide by 100 to get %
ytd_return = annualized_return*current_period/26
Then it is just an optimization problem by changing annualized_return such that my actual current balance matches the new_balance value.

Perhaps the issue is because I am calculating an averaged return.
I see an obvious problems: you divide an annual return by 26 to get a bi-weekly return, instead of calculating the 26th root of the annual growth factor. You repeat the mistake to calculate a cumulative return for 9 months (multiplying instead of exponentiating).
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

sharpjm
Posts: 657
Joined: Fri Feb 20, 2015 2:41 pm

Re: A Returns Spreadsheet for Bogleheads

Post by sharpjm » Thu Sep 03, 2015 11:16 am

longinvest wrote:
sharpjm wrote: My monthly contributions are roughly 1.2% of my portfolio right now. I'm not sure if that is considered large relative to the balance. I only compared ytd performance, so a 9 month window. My calculations also find an average return, rather than a return during each period and finding the cumulative based on those periods.
It's simply impossible for such small contributions (relative to portfolio size) to cause such a big difference on the return of a 9-month period. So, the problem must be with your calculations.
sharpjm wrote:Here is my calc for each period

Code: Select all

new_balance = contributions_during_period + previous_balance*( 1 + annualized_return/26/100 )
% note:  divide by 100 to get %
ytd_return = annualized_return*current_period/26
Then it is just an optimization problem by changing annualized_return such that my actual current balance matches the new_balance value.

Perhaps the issue is because I am calculating an averaged return.
I see an obvious problems: you divide an annual return by 26 to get a bi-weekly return, instead of calculating the 26th root of the annual growth factor. You repeat the mistake to calculate a cumulative return for 9 months (multiplying instead of exponentiating).
Oh I see! Thanks for the comments. My corrected formulas give -2.04% vs your spreadsheet's -1.97%.

Code: Select all

new_balance = contributions_during_period + previous_balance*( 1 + annualized_return/100 ) ^ (1/26)
% note:  divide by 100 to get %
ytd_return = (1 + annualized_return/100) ^ (current_period/26) - 1

bayview
Posts: 1565
Joined: Thu Aug 02, 2012 7:05 pm
Location: WNC

Re: A Returns Spreadsheet for Bogleheads

Post by bayview » Fri Sep 04, 2015 6:34 pm

longinvest wrote:
bayview wrote:The only data that I've entered has been for 2015. Now that I don't have much to do other than watch my daily balances go roller-coastering :D , I'd like to add earlier dates, contributions, and so forth.

I cautiously poked around, trying to figure out how, but everything seemed to involve messing with the dreaded grey cells. Do I just enter a bunch of rows and ignore the warnings, and let it untangle itself when I'm done? I have quite a few years' worth of stuff to enter, so I'd like to not destroy what I've done so far.

Or do I just start all over from 1990-whatever?

I have this running in both Google Docs and Excel.
bayview,

I would start a new spreadsheet, but I would save some time by copy/pasting existing data from the older spreadsheet to the appropriate rows of the new one.

longinvest
That's what I figured. Thanks! :sharebeer
The continuous execution of a sound strategy gives you the benefit of the strategy. That's what it's all about. --Rick Ferri

c078342
Posts: 103
Joined: Mon Jun 15, 2009 9:32 am

Re: A Returns Spreadsheet for Bogleheads

Post by c078342 » Sun Sep 06, 2015 3:44 pm

I'd like to use this spreadsheet to track returns, but on a 1/4ly basis to minimize input. I haven't looked very hard, but I'm hoping there is a "switch" somewhere in the coding to change from 12 periods per annum to 4. I'm pretty Excel proficient but I hate dissecting someone else's work. Thanks in advance.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Sep 06, 2015 5:40 pm

c078342 wrote:I'd like to use this spreadsheet to track returns, but on a 1/4ly basis to minimize input. I haven't looked very hard, but I'm hoping there is a "switch" somewhere in the coding to change from 12 periods per annum to 4. I'm pretty Excel proficient but I hate dissecting someone else's work. Thanks in advance.
Here's a simple solution.

For an account where I receive updates once every 3 months, I would simply enter new information on the month of the statement, then put $0 as contribution and withdrawal for the next two month. As portfolio balance, I would simply copy the last balance for the next two months.

Adding a quarterly option wouldn't really improve much on the above approximation. And, if I add a quarterly option, people will ask for a semi-annual option and possibly other schedules. Different accounts would have different schedules, making the spreadsheet very complex to design and develop. I prefer to stick to simplicity and good enough.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

c078342
Posts: 103
Joined: Mon Jun 15, 2009 9:32 am

Re: A Returns Spreadsheet for Bogleheads

Post by c078342 » Mon Sep 07, 2015 4:12 pm

Thanks for the response and for your effort in creating the spreadsheet. I've been poking around in it and found it's very straightforward to change the period.

SittingOnTheFence
Posts: 295
Joined: Sun Sep 27, 2015 5:30 pm

Re: A Returns Spreadsheet for Bogleheads

Post by SittingOnTheFence » Fri Oct 02, 2015 11:49 pm

Thanks for spreadsheet, still in process of populating sheets & cells . I'm gonna toss the homebrew spreadsheet that I've been using for the last few years.

Using libreCalc version, when adding new acct's to the tabs (eg: account3), the date format (E13) is "Number, General". I've fixed my sheets to display date (thanks for keeping protection opened) but the next upgrade may mean I need to go through all sheets again to fix it.

From the posts here I gather that on the next upgrade I need to copy/paste data for each of the tabs, is this correct?

I think I'm going to need 11 or 12 account sheets (unless I combine some acct's). Can I safely add sheets or will that break some links? I would add by copy/paste the currently empty 'account9' sheet. For certain, it appears I'd have to edit some formula's on the Calculation Sheet if I inserted some sheets.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sat Oct 03, 2015 6:25 am

SittingOnTheFence wrote: Using libreCalc version, when adding new acct's to the tabs (eg: account3), the date format (E13) is "Number, General".
Thanks for reporting it. I'll fix it in the next version.
SittingOnTheFence wrote: From the posts here I gather that on the next upgrade I need to copy/paste data for each of the tabs, is this correct?
Yes.
SittingOnTheFence wrote: I think I'm going to need 11 or 12 account sheets (unless I combine some acct's). Can I safely add sheets or will that break some links? I would add by copy/paste the currently empty 'account9' sheet. For certain, it appears I'd have to edit some formula's on the Calculation Sheet if I inserted some sheets.
Effectively, you'll have to update many formulas, including on the "Missing" sheet.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

SittingOnTheFence
Posts: 295
Joined: Sun Sep 27, 2015 5:30 pm

Re: A Returns Spreadsheet for Bogleheads

Post by SittingOnTheFence » Sat Oct 03, 2015 2:05 pm

longinvest wrote:
SittingOnTheFence wrote: I think I'm going to need 11 or 12 account sheets (unless I combine some acct's). Can I safely add sheets or will that break some links? I would add by copy/paste the currently empty 'account9' sheet. For certain, it appears I'd have to edit some formula's on the Calculation Sheet if I inserted some sheets.
Effectively, you'll have to update many formulas, including on the "Missing" sheet.
OK, great. The design is done well and makes this modification fairly painless.

I will use copy/paste after inserting a new sheet & I think most of the formula's will copy, but will need to manually edit them on that new sheet.

It looks like there are just a few columns in the Calculation sheet with formulas I need to update to add the new sheet(s), & on the Portfolio sheet for added acct names.

Besides those 2 sheets, do any of the other sheets have formulas I may need to edit? I didn't see any others.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sat Oct 03, 2015 2:17 pm

SittingOnTheFence wrote:Besides those 2 sheets, do any of the other sheets have formulas I may need to edit? I didn't see any others.
I don't think so.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

crinkles2
Posts: 12
Joined: Fri Nov 28, 2014 8:18 pm

Re: A Returns Spreadsheet for Bogleheads

Post by crinkles2 » Sat Nov 07, 2015 10:28 pm

Just also wanted to say a big thank you for this sheet. I am using the google sheets version. I had been using multiple files for multiple accounts since January of this year. I even had individual files for my individual holdings (e.g. VT, etc) in each account.

This latest version with multi account capability really helped me see my accounts as a whole rather than isolation, and let me let go of tracking individual ETFs (the info of which I can just get off the internet).

I am including 2x tax advantaged accounts, 2x mortgage offset accounts, kids CD's we are saving, and an EF in bonds, to see just what our combined investments (other than day to day cash accounts) are doing together.

Much appreciated!

One thing which would be great, is to be able to assign a stock-bond-cash split to each account, which can be aggregated and weighted by account value to display an overall asset allocation at the front page.

hawkipa
Posts: 3
Joined: Mon Dec 07, 2015 6:56 am

Re: A Returns Spreadsheet for Bogleheads

Post by hawkipa » Mon Dec 07, 2015 7:08 am

Hi,
First and absolutely foremost I wanted to say thank you for this spreadsheet. It is awesome. I've been looking for something like this for ages and so delighted to have found it.

As a bit of an excel geek I have been looking through the formula's and I wonder if someone can explain to me a formula in the calculations tab.

It is specifically the formula to calculate the month returns in column I.

What I don't understand is within the formula in cell I6 =IF(A6="","",IF((F6+G6/2)=0,0,(H6-G6/2)/(F6+G6/2)-1))

Why is the flow number of the formula (G6) divided by 2 as part of the calculation.

I presume that the division of the flow by 2 is to time weight it in some way but I'm not totally sure that is why, just my guess?

Any ideas/responses gratefully received.

Thanks in advance.
Paul

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Mon Dec 07, 2015 11:22 am

Paul,

Congratulations! It is a very good thing to analyze a spreadsheet before using it.

Here's the explanation you seek. As suggested by W. Bernstein in The Four Pillars of Investing, half the contributions and withdrawals are applied at the start of the month, and the other half at the end of the month.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

hawkipa
Posts: 3
Joined: Mon Dec 07, 2015 6:56 am

Re: A Returns Spreadsheet for Bogleheads

Post by hawkipa » Mon Dec 07, 2015 11:35 am

Thank you so much Longinvest. I really appreciate you confirming.
Regards
Paul

henders
Posts: 12
Joined: Thu Jun 25, 2015 3:40 pm

deleted

Post by henders » Tue Dec 08, 2015 2:19 am

deleted
Last edited by henders on Tue Aug 01, 2017 5:16 am, edited 1 time in total.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Tue Dec 08, 2015 4:21 pm

Henders,
henders wrote: [*] In Account 3 tab, I have put $9954 as contribution in June 2011. What should I put in Balance fields? I have individual stocks and ETF's and don't know what the balance was on those particular months. I didn't make any withdrawal - should I put $0 ? If I don't have the balance data, is this spreadsheet useless to me?
My brokerage firm sends me a monthly statement with a month-end portfolio balance. That's what I use. It also provides historical monthly balance data online. But, your brokerage might not.

If you don't have monthly account balances, leave them empty. The spreadsheet will not be happy about it (it will report missing data), but that's fine as it is actually missing. Yet the spreadsheet will still compute an accurate investor return (XIRR, money-weighted return) as long as you're able to provide (1) an initial balance on May 31, 2011, (2) a final balance on November 30, 2015, and (3) all cash flows, of course.

It's definitely more fun when monthly balances are provided, though, as you then get accurate portfolio returns and all warnings go away. When balance data is missing, any reported portfolio return should be disregarded, thus all the warnings.
henders wrote: [*] Can I rename the Account 1 etc. tabs to my ticker? At the moment, Libreoffice doesn't give the rename option.
You have to "unprotect" the sheet, before trying to rename it. I recommend that you re-protect it (no password) once it's renamed.
henders wrote: [*] The dates are in U.S format. Can I change it to British format?
Once the sheet is unprotected, you can change not only the date, but also the currency format.
henders wrote: [*] The portfolio tab has 9 account. If I was to add Account 10, I get error: protected cell. Is there a way around this?
That one is more difficult to do. If you want to add a sheet, you'll have to update other sheets appropriately:
  • Missing
  • Portfolio
  • Calculations
It's feasible, but it's a pain.
henders wrote: Thanks.
You're welcome.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

User avatar
vinnydabody
Posts: 86
Joined: Sun Oct 18, 2015 7:25 am
Location: Chicagoland

Re: A Returns Spreadsheet for Bogleheads

Post by vinnydabody » Tue Dec 08, 2015 5:56 pm

longinvest, what a fabulous spreadsheet you've compiled! I'm using the google sheets version and set my stuff up back to 1/1/2006, which involved some tracking down of now defunct retirement accounts at a couple of different former employers. Surprisingly, I was able to recover my online access and get the old statements (ain't the internet grand?).

I'm finding now though, with nine accounts and almost 10 years worth of data that the squirrels in the background really have to work hard to update the calculations. Since two of the accounts are now empty and have been for more than 5 years, I wonder if there's an easy way to tell the spreadsheet to ignore those two going forward? I guess I could just archive the spreadsheet and start a new one going back just five years and tracking the active accounts, but I would lose the 10-year returns data.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Tue Dec 08, 2015 6:00 pm

Vinny,

I have the exact same problem. I keep putting zeros, month after month. You're right that I should think of a nicer solution. I'm putting that on the todo list. Thanks for the idea!
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

apc12
Posts: 38
Joined: Mon Apr 13, 2015 4:44 pm

Re: A Returns Spreadsheet for Bogleheads

Post by apc12 » Tue Dec 08, 2015 8:14 pm

Thank you for creating this tool. I just have one question for you regarding accounts that have been opened at a date later than the first account. My oldest account started in 2013, and my newest account was opened a few months ago. In the newest account, do I have to go back and fill in zeros for all of the months from 2013 to 2015? I'm not sure what to do because I have the "Portfolio Start Date" as Feb 2013 because that is when my oldest account started.

For the old account, I entered zeros in all the months after the point in 2014 when I stopped putting money into it. Does that make sense? Thank you for your help.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Tue Dec 08, 2015 10:11 pm

Apc12,

You're doing it right. I'll see if it's possible to avoid back-filling with zeros, but unlike for old closed accounts (which have to be filled with three additional zeros every month), it only has to be done once when a newer account is added.

Remember that it's only a macro-less spreadsheet, not a full program. This severely limits the possibilities, specially that I want to keep it simple enough so that anybody with basic spreadsheet knowledge can understand how it works.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

apc12
Posts: 38
Joined: Mon Apr 13, 2015 4:44 pm

Re: A Returns Spreadsheet for Bogleheads

Post by apc12 » Thu Dec 10, 2015 8:37 am

Ok thanks. I went back and added zeros to all the months before my newest accounts were opened.

Here is what my numbers look like:

Trailing investor return (money-weighted return, internal rate of return)

Investor return as of 11/30/2015
Since* 1/31/2013 1.83%
* Annual compound return

Trailing portfolio return (time-weighted return, comparable return)

Portfolio return as of 11/30/2015
1 month -0.04%
3 months 2.99%
6 months -3.18%
YTD -0.81%
1 year -0.90%


Can you give me a brief summary of what I can interpret from this data, if anything? I'm trying to figure that out by reading it on the wiki but still not sure. It looks to me like I'm not doing very well, but I think this is related to having my money in a stable value fund in 2013 (before I found this blog and learned about what I should be doing, missed lots of growth there) and having all relatively new accounts. I'm 80/20 stock/bond for about 10 months now.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Thu Dec 10, 2015 9:42 am

apc12 wrote:Investor return as of 11/30/2015
Since* 1/31/2013 1.83%
* Annual compound return
The investor return is an internal rate of return; it is really dependent on the precise timing of contributions and withdrawals. It is only useful to you (not to others) as a gauge of how much your money has grown.

In other words, the spreadsheet is saying the following: Your returns are identical to having put all your contributions into (and taken all your withdrawals from) a fixed-rate savings account with a 1.83% interest rate, assuming the exact same timing of contributions and withdrawals.
apc12 wrote: Can you give me a brief summary of what I can interpret from this data, if anything? I'm trying to figure that out by reading it on the wiki but still not sure. It looks to me like I'm not doing very well, but I think this is related to having my money in a stable value fund in 2013 (before I found this blog and learned about what I should be doing, missed lots of growth there) and having all relatively new accounts.
Portfolio returns are useful to compare the returns of your portfolio with a relevant benchmark.

For example, if your portfolio was composed of 50% US stocks, 20% international stocks, 13% emerging markets stocks, and 17% bonds in 2015 (YTD), an appropriate benchmark would be the following weighted average YTD returns of VTI/VXUS/VWO/BND:
(0.5 X 2.48%) + (0.2 X -2.30%) + (0.13 X -13.22%) + (0.17 X 0.80%) = -0.80%

If you find that your portfolio and benchmark returns are not relatively similar, then you have to find out what went wrong. The first possibility is that you made a mistake when entering your data, like forgetting to include some contributions or withdrawals, or you made a mistake entering the portfolio balance in a month, or you included internal money flows into your contributions or withdrawals. Another possibility is that you did not have a fixed allocation over the period, and thus, the benchmark is not representative and it's normal that returns differ.

The deeper philosophical question still remains. What is this all useful for? I really don't see much use for it, for a three-fund portfolio investor like me, other than to learn how returns are quite volatile and change from month to month. This helps understand how projecting a fixed rate of return into the future is a futile exercise. How could it not be a futile exercise, when past returns change from month to month?

If you have a tilted portfolio, it could allow you to compare how your tilted portfolio is doing relative to a non-tiled portfolio. Yet, again, maybe that's not useful, as it would be a bad idea to abandon your tilted portfolio just as it goes on sales (e.g. it has lower returns). Buying high and selling low is not really a good investing approach.

Finally, too many people overestimate their returns by looking at the growth of their portfolio balance without taking into consideration contributions (like the Beardstown Ladies). Or, when they do, they unfairly compare their investor returns (XIRR) to benchmark returns (time-weighted). At least, with the spreadsheet they can easily do a proper comparison of time-weighted portfolio returns to benchmark returns.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

crinkles2
Posts: 12
Joined: Fri Nov 28, 2014 8:18 pm

Re: A Returns Spreadsheet for Bogleheads

Post by crinkles2 » Sat Dec 26, 2015 7:56 pm

Hi, I have entered all my data, and am using a "combined" sheet for 6 accounts.

Recently I duplicated the sheet and split out 2 retirement accounts to a copy, and the 4 non-retirement accounts to another copy.

A table for the returns to Nov 30 is as follows:

Combined Retirement Non Retirement
IRR 3.81 4.46 3.27
Comparable 6.60 4.69 2.74

the retirement accounts are about 42% of the portfolio and the non retirement 58%.

For the IRR, the combined file shows 3.81 which is between the two separated out account... which makes sense.

However, the comparable return for the combined account is much either than either of the two separated files... which doesn't make sense.

So my question is really does the 6.6% make sense? Is it possible that the timing of flows in the combined spreadsheet was enough to make such a difference? I would expect it to lie between 2.74 and 4.69%.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sat Dec 26, 2015 8:10 pm

crinkles2 wrote: REFORMATED FOR EASIER READING

Code: Select all

           Combined Retirement Non Retirement
IRR        3.81     4.46       3.27
Comparable 6.60     4.69       2.74
the retirement accounts are about 42% of the portfolio and the non retirement 58%.

For the IRR, the combined file shows 3.81 which is between the two separated out account... which makes sense.

However, the comparable return for the combined account is much either than either of the two separated files... which doesn't make sense.

So my question is really does the 6.6% make sense? Is it possible that the timing of flows in the combined spreadsheet was enough to make such a difference? I would expect it to lie between 2.74 and 4.69%.
At a first glance, this doesn't look right to me. I'm assuming that you are providing us a comparable return for the same time period (is it 1 year?) for all three spreadsheets above.

First, I would make sure that something didn't go wrong when creating the two additional spreadsheets as follows:
  1. Create two new spreadsheet from scratch, starting with pristine return spreadsheets.
  2. Make sure both spreadsheet share the same start date.
  3. Copy and paste Contributions, WIthdrawals, and Balances directly from the combined file.
  4. Check if the discrepancy persists.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

crinkles2
Posts: 12
Joined: Fri Nov 28, 2014 8:18 pm

Re: A Returns Spreadsheet for Bogleheads

Post by crinkles2 » Sat Dec 26, 2015 8:18 pm

Thanks longinvest - I will try that.

Those figures are for "YTD 2015" from Dec 31, 2014 to Nov 30, 2015.

crinkles2
Posts: 12
Joined: Fri Nov 28, 2014 8:18 pm

Re: A Returns Spreadsheet for Bogleheads

Post by crinkles2 » Sun Dec 27, 2015 12:18 am

I've tried in new fresh sheets... no change unfortunately.

Two of the accounts are offset accounts. I may remove them as the "withdrawals" are imaginary, as they are saved interest on the mortgage. If I remove them, the numbers make sense. They tend to have large movements vs a small account balance, which may be an issue on the last month's assignment of flows to the start and end of month, but I'm not sure.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Dec 27, 2015 12:23 am

Imaginary withdrawals? Sounds like the source of your problems.

The spreadsheet is meant to calculate returns from actual money flows in and out of the portfolio. This should include employer match, and all the investor's contributions. This should exclude all internal money flows, like dividends (when not withdrawn), mutual fund or broker fees, etc.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by randomizer » Wed Dec 30, 2015 7:09 am

Thanks for sharing and maintaining this longinvest. Will be interesting to play with. I expect that if I am doing it right, my returns will track the market (that's the whole point, right?), but it's good to confirm.
87.5:12.5, EM tilt — HODL the course!

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

Re: A Returns Spreadsheet for Bogleheads

Post by AlohaJoe » Wed Dec 30, 2015 9:10 pm

Thanks for creating this. I'm not a professional spreadsheet jockey, so it is always interesting to see how other people do things and learn from them. I've added a few of the ideas here to my personal spreadsheet :)

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Mon Feb 15, 2016 10:32 am

I have uploaded a new version (1.9) of the returns spreadsheet.

As usual, you'll find the download links on the wiki:Calculating personal returns page.

Changes:
  • Handle closed accounts gracefully. (e.g. Don't ask for zeros every month).
  • Handle up to 20 accounts. (Useful for people with multiple historical accounts).
  • Various minor improvements.
As usual, comments are welcome.

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

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

Re: A Returns Spreadsheet for Bogleheads

Post by randomizer » Mon Feb 15, 2016 8:28 pm

longinvest wrote:I have uploaded a new version (1.9) of the returns spreadsheet.

As usual, you'll find the download links on the wiki:Calculating personal returns page.
Thanks longinvest. I just started using the previous version a couple of months ago. It's great. What is the upgrade pathway? Is it just to copy the data over with copy paste from the old copy to the new copy?
87.5:12.5, EM tilt — HODL the course!

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Mon Feb 15, 2016 9:38 pm

randomizer wrote: Thanks longinvest. I just started using the previous version a couple of months ago. It's great. What is the upgrade pathway? Is it just to copy the data over with copy paste from the old copy to the new copy?
Yes, you got it right.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

Mordko
Posts: 15
Joined: Fri Feb 19, 2016 7:05 am

Re: A Returns Spreadsheet for Bogleheads

Post by Mordko » Fri Feb 19, 2016 1:06 pm

The spreadsheet is awesome - thank you very much!

I maintain a spreadsheet in Google Docs, which automatically logs daily values. Combined it with your Returns spreadsheet, so that I don't need to keep recording monthly prices for each account.

A couple of suggestions:

- Statement "Portfolio return as of 19 Feb 2016, 1 month..." is a bit misleading. It's really return for 1 month up to January 31 2016.

- Would be nice to have money-weighted returns side by side with time-weighted returns so that one can evaluate how good he is at "timing the market".

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Fri Feb 19, 2016 2:54 pm

Mordko,

Welcome to the forum!

I am glad that the spreadsheet is useful to you.
Mordko wrote: - Statement "Portfolio return as of 19 Feb 2016, 1 month..." is a bit misleading. It's really return for 1 month up to January 31 2016.
I looked into the three versions (Google Sheets, Microsoft Excel, and LibreOffice Calc), and I see 2016-1-31.

Are you sure you have not inadvertently modified your copy of the spreadsheet?
Mordko wrote: - Would be nice to have money-weighted returns side by side with time-weighted returns so that one can evaluate how good he is at "timing the market".
Actually, it's a feature. The goal of the spreadsheet is to help investors keep track of their portfolio. The trailing time-weighted returns allows them to verify that their portfolio's returns are similar to that of its benchmark. As for the money-weighted return, it is provided for the entire investment history to allow investors to know how rewarding investing has been to them, so far.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

Mordko
Posts: 15
Joined: Fri Feb 19, 2016 7:05 am

Re: A Returns Spreadsheet for Bogleheads

Post by Mordko » Fri Feb 19, 2016 6:10 pm

1. - Yes, thanks. Corrected the date now.

2. Why not give money-and time-weighted returns side by side over the same periods of time, a bit like you can do in Morningstar under Performance?
Last edited by Mordko on Fri Feb 19, 2016 6:23 pm, edited 1 time in total.

Mordko
Posts: 15
Joined: Fri Feb 19, 2016 7:05 am

Re: A Returns Spreadsheet for Bogleheads

Post by Mordko » Fri Feb 19, 2016 6:20 pm

Also... Can I ask why aren't you using XIRR? Thanks.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Fri Feb 19, 2016 6:25 pm

Mordko wrote:Also... Can I ask why aren't you using XIRR? Thanks.
The money-weighted return is calculated using IRR, as it is based on regular monthly data.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Fri Feb 19, 2016 6:32 pm

Mordko wrote:2. Why not give money-and time-weighted returns side by side over the same periods of time, a bit like you can do in Morningstar under Performance?
I only see bad ways to use such information.

One of the main reasons I contributed the spreadsheet to the Bogleheads was to encourage members to stop comparing their XIRR (money-weighted) returns, which simply does not make any sense, mathematically.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic / international) stocks / domestic (nominal / inflation-indexed) long-term bonds | VCN/VXC/VLB/ZRR

Post Reply