A Returns Spreadsheet for Bogleheads

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

A Returns Spreadsheet for Bogleheads

Post by longinvest » Sat Nov 01, 2014 9:50 pm

It sometimes happens that Bogleheads report the return of their portfolio (in threads like "What are you up YTD?"). When new Bogleheads ask about how to compute returns, they're often told about Excel's XIRR function, which is a very good way to compute an investor's return (how well am I doing with my investments), but which is not an appropriate return to compare to widely available fund and index returns such as the S&P 500 YTD return [added:] or to the return of other investors.

As a solution to this, I have built a Returns Spreadsheet for Bogleheads. I have tried to keep it as simple to use as possible. Hopefully, members will simply copy/paste their returns from the spreadsheet into their posts.

For each month, one only needs to provide three pieces of data:
  • Total contributions. (Money added to portfolio, excluding any internal cash flows such as dividend payments).
  • Total withdrawals. (Money removed from portfolio, excluding any internal cash flows such as fees).
  • Portfolio balance at the close of the last day of the month.
The spreadsheet computes two types of returns(*):
  • Investor return (money weighted, IRR) for the entire investment period.
  • Portfolio returns (time weighted, comparable to other returns) for YTD, 1 year, 3 years, 5 years, etc.
[edited:] You'll find the download links on the Wiki page: wiki:Calculating personal returns

(*) 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.


SCREENSHOTS

Trailing Returns

Image

Growth of $10,000 Chart

Image

Comments are welcome.
Last edited by longinvest on Wed Jul 08, 2015 8:40 am, edited 12 times in total.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

gkaplan
Posts: 7034
Joined: Sat Mar 03, 2007 8:34 pm
Location: Portland, Oregon

Re: A Returns Spreadsheet for Bogleheads

Post by gkaplan » Sat Nov 01, 2014 9:53 pm

longinvest wrote:It sometimes happens that Bogleheads report the return of their portfolio (in threads like "What are you up YTD?"). When new Bogleheads ask about how to compute returms, they're often told about Excel's XIRR function, which is a very good way to compute an investor's return (how well am I doing with my investments), but which is not an appropriate return to compare to widely available returns such as the S&P 500 YTD return....
Why would I want to compare my portfolio to the S&P 500 when my portfolio in no way mirrors that index?
Gordon

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sat Nov 01, 2014 10:02 pm

gkaplan, I would go one step further: Why would one compute the return of his portfolio? Maybe just knowing its current balance and contribution (or withdrawal) rate should be sufficient for a Boglehead. During accumulation, the "savings rate" is probably more important than the return, and during retirement, controlling the withdrawal rate is paramount to avoid premature depletion.

But, hey, many members like to compare things, so I'm just providing a tool. :happy
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

User avatar
danyn
Posts: 8
Joined: Thu Dec 19, 2013 9:38 pm

Re: A Returns Spreadsheet for Bogleheads

Post by danyn » Sun Nov 02, 2014 12:07 am

longinvest wrote:It sometimes happens that Bogleheads report the return of their portfolio (in threads like "What are you up YTD?"). When new Bogleheads ask about how to compute returms, they're often told about Excel's XIRR function, which is a very good way to compute an investor's return (how well am I doing with my investments), but which is not an appropriate return to compare to widely available fund and index returns such as the S&P 500 YTD return [added:] or to the return of other investors.

As a solution to this, I have built a Returns Spreadsheet for Bogleheads. I have tried to keep it as simple to use as possible...
Comments are welcome.
Very Nicely done. :sharebeer

I loaded my data using Vanguard performance report / table. I have only been with Vanguard for 14 months so the data load was not too difficult.

On the Vanguard site, I do not get YTD figures yet ... The 1 year return numbers in the Spreadsheet closely match the Vanguard calculations for my portfolio. I like the spreadsheet and plan to use it in the future. Appreciate that the sheets can be unprotected; that allowed me to add some sidebar calculations and check totals. Big thanks!!

inbox788
Posts: 5667
Joined: Thu Mar 15, 2012 5:24 pm

Re: A Returns Spreadsheet for Bogleheads

Post by inbox788 » Sun Nov 02, 2014 12:47 am

gkaplan wrote:Why would I want to compare my portfolio to the S&P 500 when my portfolio in no way mirrors that index?
For lack of a better choice. Do you have a better alternative for yourself? What about all investors in general?

Brokers have compared to cash and too many investors have been satisfied with "at least I'm making money" ignoring risk and expenses, especially high expense mutual funds and hedge funds.

If someone made 18% returns, it appears pretty good, but in 2013, not so.

While the s&p 500 isn't the market, it's a close approximation. Arguably, you may use a total market index fund, but that opens up to things like VT, VTI, Fidelity Spartan or Schwab equivalents, and that's just a start. Sp500 is like the new DJIA.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Nov 02, 2014 8:03 am

One of my objectives is to make it as easy as possible to copy returns into a post. Here's what I get if I copy my own results (actual results replaced by "x"):

INVESTOR RETURN
(Money weighted return, internal rate of return)

Period mm/dd/yyyy - mm/dd/yyyy
Investor Return x.xx%


PORTFOLIO RETURNS
(Time weighted returns, comparable returns)

Portfolio returns
Date YTD 1 year 3 years 5 years 10 years 15 years 20 years 25 years 30 years
mm/dd/yyyy x.xx% x.xx% x.xx% x.xx% - - - - -


The portfolio returns are not visually aligned in nice columns, which makes it difficult to match returns with period (YTD, 1 year, etc.).

I plan to modify the layout as follows:

INVESTOR RETURN
(Money weighted return, internal rate of return)

Period mm/dd/yyyy - mm/dd/yyyy
Investor Return x.xx%

PORTFOLIO RETURNS
(Time weighted returns, comparable returns)

Date mm/dd/yyyy
YTD x.xx%
1 year x.xx%
3 years x.xx%
5 years x.xx%
10 years -
15 years -
20 years -
25 years -
30 years -


1- What do you think?
2- Should I add this spreadsheet to our Wiki?
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

The Wizard
Posts: 12359
Joined: Tue Mar 23, 2010 1:45 pm
Location: Reading, MA

Re: A Returns Spreadsheet for Bogleheads

Post by The Wizard » Sun Nov 02, 2014 8:29 am

This spreadsheet is fine for people who like to fiddle with numbers to three decimal places.
But realize that tomorrow's change in market values will throw all of today's computations off.
For years now, I've been content just to monitor the YTD total return of the various funds that I'm invested in..
Attempted new signature...

User avatar
grayfox
Posts: 4936
Joined: Sat Sep 15, 2007 4:30 am

Re: A Returns Spreadsheet for Bogleheads

Post by grayfox » Sun Nov 02, 2014 8:33 am

Outstanding work, longinvest! Thanks!

BTW, I had a really easy time entering the data. Using LibeOffice, I just cut a column of month end balances from another spreadsheet and pasted numbers only and voila, all the data is entered.

If everyone that posts returns uses this spreadsheet, then we know they are all using the same computation.

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

Re: A Returns Spreadsheet for Bogleheads

Post by pshonore » Sun Nov 02, 2014 8:48 am

Tried to download and open in Excel. Got an error message about "unreadable data" when trying to open. Using the repair option gives this error message
Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded. Replaced Part: /xl/worksheets/sheet2.xml part with XML error. Illegal xml character. Line 2, column 419506.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Nov 02, 2014 9:05 am

pshonore wrote:Tried to download and open in Excel. Got an error message about "unreadable data" when trying to open. Using the repair option gives this error message
Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded. Replaced Part: /xl/worksheets/sheet2.xml part with XML error. Illegal xml character. Line 2, column 419506.
I have developed the spreadsheet using LibreOffice, but usually, it should work just fine in Excel. Sometimes, it's Google Drive that messes up things.

Just in case, I have uploaded a copy of the spreadsheet on DropBox: http://goo.gl/aMuRrf

Can you confirm that this copy gives you the same problem?
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Nov 02, 2014 9:09 am

grayfox wrote:BTW, I had a really easy time entering the data. Using LibeOffice, I just cut a column of month end balances from another spreadsheet and pasted numbers only and voila, all the data is entered.
No contributions or withdrawals? :confused ... :happy
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

dbr
Posts: 27207
Joined: Sun Mar 04, 2007 9:50 am

Re: A Returns Spreadsheet for Bogleheads

Post by dbr » Sun Nov 02, 2014 9:12 am

I think some posters will appreciate the help for this, so thanks to longinvest.

Why one would want to know or what use to make of it is a different discussion. Personally I think going through the understanding of what is involved is a good thing.

I do know one example that comes up from time to time that is useful. That is using the result to test whether a fund or account is losing return due to costs that can't be identified.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Nov 02, 2014 9:16 am

The Wizard wrote:This spreadsheet is fine for people who like to fiddle with numbers to three decimal places.
You are absolutely right. As I said in an earlier post, most Bogleheads should probably concentrate on their "savings rate" or "withdrawal rate" instead. But, human nature being what it is, it's hard to resist the temptation to look at our returns. :happy
But realize that tomorrow's change in market values will throw all of today's computations off.
For years now, I've been content just to monitor the YTD total return of the various funds that I'm invested in..
But, that's what makes it fun! You can compute new rates of return every month. It's addictive. :mrgreen:
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by pshonore » Sun Nov 02, 2014 10:44 am

longinvest wrote:
pshonore wrote:Tried to download and open in Excel. Got an error message about "unreadable data" when trying to open. Using the repair option gives this error message
Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded. Replaced Part: /xl/worksheets/sheet2.xml part with XML error. Illegal xml character. Line 2, column 419506.
I have developed the spreadsheet using LibreOffice, but usually, it should work just fine in Excel. Sometimes, it's Google Drive that messes up things.

Just in case, I have uploaded a copy of the spreadsheet on DropBox: http://goo.gl/aMuRrf

Can you confirm that this copy gives you the same problem?
The Dropbox copy works fine. When trying to use the other one, the "data" sheet gets messed up during the download and appears blank

User avatar
Aptenodytes
Posts: 3751
Joined: Tue Feb 08, 2011 8:39 pm

Re: A Returns Spreadsheet for Bogleheads

Post by Aptenodytes » Sun Nov 02, 2014 11:02 am

If people are interested in investing in public goods, something I think would be extremely useful would be an online spreadsheet that tracked returns of a small number of model portfolios against different levels of equity exposure. It would have some of the same benefits of the so-called "periodic table" but aggregated into model portfolios.

Such a tool would focus attention on the relationship between AA and return much more usefully than all the "what was your return?" discussion, which I've always thought amounted to a very opaque way as asking "What is your AA?" I think most of us don't really care what our peers are earning, but we do sometimes wonder what other model portfolios are earning. E.g. if someone reports here that they are up 15% ytd, we know that means they are heavily exposed to very high-risk assets, such as individual stocks. I don't care about that. But I am interested in the return of a set-and-forget benchmark that matches my AA, so I can compare to what I get with my various tweaks and tinkers.

Such a tool would amount to an interactive, on-line version of Simba's spreadsheet that let the equity % vary along with the relative asset weights, and was updated more often than once a year. Maybe that's too tall an order.

The dedicated investors can tweak Simba's spreadsheet to do all this already.

placeholder
Posts: 3958
Joined: Tue Aug 06, 2013 12:43 pm

Re: A Returns Spreadsheet for Bogleheads

Post by placeholder » Sun Nov 02, 2014 12:56 pm

longinvest wrote:gkaplan, I would go one step further: Why would one compute the return of his portfolio? Maybe just knowing its current balance and contribution (or withdrawal) rate should be sufficient for a Boglehead.
I agree wholeheartedly with this (as redacted)!

BahamaMan
Posts: 896
Joined: Wed Oct 01, 2014 5:52 pm

Re: A Returns Spreadsheet for Bogleheads

Post by BahamaMan » Sun Nov 02, 2014 1:22 pm

Great Work Longinvest ! .... As, I am retired, and no longer have contributions, I won't be using this tool. I can just go to Vanguard and look at my performance of my Single Mutual Fund. :mrgreen:

But it would have come in real handy about 30 years ago, when I was jumping around from fund to fund and buying and selling stocks! :mrgreen:
Last edited by BahamaMan on Sun Nov 02, 2014 1:27 pm, edited 1 time in total.

dbr
Posts: 27207
Joined: Sun Mar 04, 2007 9:50 am

Re: A Returns Spreadsheet for Bogleheads

Post by dbr » Sun Nov 02, 2014 1:24 pm

BahamaMan wrote:Great Work Longinvest ! .... As, I am retired, and no longer have contributions, I won't be using this tool. I can just go to Vanguard and look at my performance of my Single Mutual Fund. :mrgreen:

But it would have come in real handy about 30 years ago!!
If you have withdrawals, including cashing rather than reinvesting dividends, then you would want these calculations.

BahamaMan
Posts: 896
Joined: Wed Oct 01, 2014 5:52 pm

Re: A Returns Spreadsheet for Bogleheads

Post by BahamaMan » Sun Nov 02, 2014 1:32 pm

dbr wrote: If you have withdrawals, including cashing rather than reinvesting dividends, then you would want these calculations.
Really? What would I do with them?

dbr
Posts: 27207
Joined: Sun Mar 04, 2007 9:50 am

Re: A Returns Spreadsheet for Bogleheads

Post by dbr » Sun Nov 02, 2014 1:43 pm

BahamaMan wrote:
dbr wrote: If you have withdrawals, including cashing rather than reinvesting dividends, then you would want these calculations.
Really? What would I do with them?
I was pointing out that IF you are interested in finding an internal rate of return for your investments, the result is not just affected by having contributions, which you mentioned, but also by having withdrawals, which a retired person often does have. A person might also be unaware that for this purpose cashing dividends is a withdrawal. I couldn't care less whether or not someone is or isn't interested in calculating these numbers. I do care that someone who wants to know be getting good direction on how to do it. This whole thread and the one in which the discussion originated has been about helping people who wanted to know how to do this, but like many threads here it is easy to divert to discussions about why no one should want to do it.

User avatar
JamesSFO
Posts: 3111
Joined: Thu Apr 26, 2012 10:16 pm

Re: A Returns Spreadsheet for Bogleheads

Post by JamesSFO » Sun Nov 02, 2014 3:39 pm

Very cool, thanks!

michaelsieg
Posts: 550
Joined: Mon Jan 07, 2013 11:02 am

Re: A Returns Spreadsheet for Bogleheads

Post by michaelsieg » Sun Nov 02, 2014 8:28 pm

Thanks for posting this - I want to formally calculate my own return (usually just use the Morningstar number at the end of the year without checking ) and always have been too lazy to do it. So your spreadsheet will help - thanks for making it available to us.
Michael

JustinR
Posts: 750
Joined: Tue Apr 27, 2010 11:43 pm

Re: A Returns Spreadsheet for Bogleheads

Post by JustinR » Sun Nov 02, 2014 10:28 pm

One weakness of this is that it requires your balance at the end of each month. My provider doesn't give me this info, so I can't use it. I only have the balance at the end of each year.

Thank you for the effort though!

2retire
Posts: 371
Joined: Wed Jun 13, 2012 9:00 am

Re: A Returns Spreadsheet for Bogleheads

Post by 2retire » Mon Nov 03, 2014 5:35 pm

Thanks for taking the time to put this together. As I stated previously, I have been doing the Bernstein/Ferri calculations for a couple of years now. However, I was interested to see how you'd put this into a generic spreadsheet, as I have to update mine every year as the cells my contribs go in changes every year. I always learn some new tricks when examining other people's spreadsheets.

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

Re: A Returns Spreadsheet for Bogleheads

Post by Yesterdaysnews » Mon Nov 03, 2014 6:19 pm

So you fill out the data every month? OR do you add months to the left ledger in the data sheet with time? I ask because I cannot add additional months to the left ledger in the data sheet. I am new to spreadsheets.

For example, I put in start month of 11/2014. I put my total portfolio value in for 10/31/2014. Now I track all the contributions/withdrawals, but how do I enter the info for the following months as I continue this?

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Mon Nov 03, 2014 7:00 pm

Yesterdaysnews wrote:So you fill out the data every month? OR do you add months to the left ledger in the data sheet with time? I ask because I cannot add additional months to the left ledger in the data sheet. I am new to spreadsheets.

For example, I put in start month of 11/2014. I put my total portfolio value in for 10/31/2014. Now I track all the contributions/withdrawals, but how do I enter the info for the following months as I continue this?
On December 1st, an entry for 11/30/2014 will magically appear. :happy
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by Yesterdaysnews » Mon Nov 03, 2014 7:03 pm

Ah brilliant! Really great work here, exactly what I have been looking for. Thanks! :sharebeer

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

Re: A Returns Spreadsheet for Bogleheads

Post by pshonore » Mon Nov 03, 2014 7:12 pm

My guess in on or about Dec 1, a new line will appear for November 2014

edit: (I see the SS creator beat me to it)

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Tue Nov 11, 2014 9:23 pm

For anyone interested, I've just uploaded a new version (1.1) of the returns spreadsheet. (The download links are in http://www.bogleheads.org/forum/posting ... #pr2244484)

New features:
  • It includes a Growth of $10,000 chart.
  • The layout is more cut-and-paste friendly.
  • I've made the spreadsheet more robust when some input data is missing.
Enjoy!
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Wed Nov 12, 2014 8:22 am

I was thinking of creating a Calculating Personal Return page (do you have a better page name suggestion?), in the Wiki, that would briefly explain the difference between "investor return" (money weighted) and "portfolio return" (time weighted), and include a link to the spreadsheet, along with instructions on how to use it.

What do you think?

Also, do you have any comment on the new spreadsheet layout and growth chart?
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

dbr
Posts: 27207
Joined: Sun Mar 04, 2007 9:50 am

Re: A Returns Spreadsheet for Bogleheads

Post by dbr » Wed Nov 12, 2014 9:01 am

longinvest wrote:I was thinking of creating a Calculating Personal Return page (do you have a better page name suggestion?), in the Wiki, that would briefly explain the difference between "investor return" (money weighted) and "portfolio return" (time weighted), and include a link to the spreadsheet, along with instructions on how to use it.

What do you think?

Also, do you have any comment on the new spreadsheet layout and growth chart?
The subject comes up often enough that it makes sense to address it in the Wiki. I'm sure it would be appreciated.

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Nov 16, 2014 9:16 pm

I have added an entry for the spreadsheet in the Bogleheads Wiki, and added an explanation for the difference between investor return and portfolio return:

Calculating personal returns

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

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

Re: A Returns Spreadsheet for Bogleheads

Post by LadyGeek » Sun Dec 07, 2014 9:18 pm

Did I miss something, as I don't see anything about handling irregularly spaced contributions (arbitrary dates) ?

I'm referring to use of the XIRR function: Rate of return (Using a spreadsheet to maintain a portfolio)
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: 3056
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Dec 07, 2014 9:35 pm

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

Mr Rosco
Posts: 40
Joined: Thu Nov 28, 2013 8:24 am

Re: A Returns Spreadsheet for Bogleheads

Post by Mr Rosco » Sun Dec 21, 2014 8:02 am

Great spreadsheet! I am interested in calculating my portfolio returns but because it takes time check all of my accounts I would prefer not to track it monthly.

Is there a way I can make this spreadsheet work if I enter my account balances less frequently? For example what if I only wanted to enter them on January 1 and July 1?

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Dec 21, 2014 8:40 am

Mr Rosco wrote:Great spreadsheet! I am interested in calculating my portfolio returns but because it takes time check all of my accounts I would prefer not to track it monthly.

Is there a way I can make this spreadsheet work if I enter my account balances less frequently? For example what if I only wanted to enter them on January 1 and July 1?
Here is the approach I would use to make the spreadsheet work (approximating my returns) using a bi-annual account statements.

1- I would duplicate my portfolio's December 31 balance for the next 5 months, and do the the same with it's June 30 balance.

2- I would enter my actual contributions (and withdrawals) as lump sums in June and December. Let say that I contribute $1,000 per month: I would enter $0 as contribution for January to May, and $6,000 as contribution for June. I would do the same with the second half of the year.

That's it. It's not perfect, but it should be good enough.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Tue Dec 30, 2014 7:53 pm

Just in time for the end of the year, I have just uploaded a new version (1.2) of the Bogleheads returns spreadsheet.

You'll find the download links on the Wiki page: Calculating personal returns.

It's a minor revision with:
  • A somewhat improved layout of its main sheet.
Enjoy!
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

avbferry
Posts: 74
Joined: Tue Jan 03, 2012 9:09 am

Re: A Returns Spreadsheet for Bogleheads

Post by avbferry » Wed Dec 31, 2014 2:20 am

longinvest wrote:but which is not an appropriate return to compare to widely available fund and index returns such as the S&P 500 YTD return [added:] or to the return of other investors.
Spreadsheet looks great.

Would you mind explaining why it isn't appropriate to compare returns using the XIRR formula?

Finance noob here.

Many thanks :)

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Wed Dec 31, 2014 2:38 am

avbferry wrote:
longinvest wrote:but which is not an appropriate return to compare to widely available fund and index returns such as the S&P 500 YTD return [added:] or to the return of other investors.
Spreadsheet looks great.

Would you mind explaining why it isn't appropriate to compare returns using the XIRR formula?

Finance noob here.

Many thanks :)
Thanks.

First, XIRR is what I call an Investor Return. I describe it in the Wiki as:
Investor return

The investor return is also called money-weighted return or internal rate of return. It represents the annual rate of return of the invested money, while taking into account the timing of various contributions to and withdrawals from the portfolio.

A simple way to understand investor return is this: it is the interest rate on a savings account, such that the same sequence (and timing) of contributions and withdrawals would end up with the same final balance.
As this return is affected by the investor's specific contribution and withdrawal history, comparing it to another return not affected by an identical sequence of contributions and withdrawals is inappropriate.

The Portfolio Return (calculated by the spreadsheet) removes the effect of contributions and withdrawals. This plain return can then be compared to other plain returns, appropriately. The Portfolio Return is the one illustrated on growth-of-$10,000 charts.

The XIRR (Investor Return) is the return actually experienced by the investor. If he happens to contribute and withdraw at the right time, he gets higher returns than his portfolio. The reverse is true, too.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

KlangFool
Posts: 10445
Joined: Sat Oct 11, 2008 12:35 pm

Re: A Returns Spreadsheet for Bogleheads

Post by KlangFool » Wed Dec 31, 2014 10:05 am

Thank you for the spreadsheet.

KlangFool

User avatar
Tortuga
Posts: 115
Joined: Sat Aug 16, 2008 9:51 pm
Location: Houston, Texas

Re: A Returns Spreadsheet for Bogleheads

Post by Tortuga » Wed Dec 31, 2014 3:33 pm

Longinvest:

Compliments on the spreadsheet - very nice.

On my copy I have added my own tab for pasting table data from Vanguard Personal Performance output. The sequence comes from the table data as youngest to oldest dates so I add an index number to each row and sort to invert the sequence of the table to get oldest to youngest dates. I then paste the appropriate columns into your Data tab.

Using this method it takes me less than 10 minutes to set up a new calculation for a fund or any portfolio subset such as All Taxable or Roth over any time period. Quite neat!

Thanks,

Tortuga

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

Re: A Returns Spreadsheet for Bogleheads

Post by LadyGeek » Sat Jan 03, 2015 12:48 pm

The bottom of every wiki article has an area for readers to supply feedback "Help improve this page" - no login required.

The spreadsheet just got a "Thanks!" from an anonymous reader.

(Ref: Feedback)
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: 3056
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sat Jan 03, 2015 3:18 pm

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

You'll find the download links on the Wiki page: wiki:Calculating personal returns.

Changes:
  • Added 1 month and 3 months returns.
Enjoy!
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by LadyGeek » Sun Jan 04, 2015 10:47 am

Microsoft Excel has decided to scale the "Growth of $10,000" chart X-axis (dates) differently than LibreOffice Calc. :annoyed (The X-axis is formatted as Custom: mmmm yyyy.)

MS Excel starts with "May 2013" and ends with "April 2015" - compare to ending "July 2015" as shown in the wiki: Calculating personal returns

This is a minor concern, as Excel users can simply resize chart size to be wider. Alternatively, the X-axis date format can be changed. To set the date as "May-13" (what looks good to me):

-Select the chart
-Select the X-axis (or go through the menu ribbon to Chart Tools --> Layout --> Axes --> Primary Horizontal Axis --> More Options...)
-Number --> Date --> "Mar-01"
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: 3056
Joined: Sat Aug 11, 2012 8:44 am

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Jan 04, 2015 10:54 am

Having users go and unlock the page (for edition) and play with advanced chart options does not seem very user-friendly to me. Could I change the default chart configuration so that we get good-enough display under both LibreOffice and Microsoft Excel? Would setting the X axis format to MMM-YY work?
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Jan 04, 2015 11:20 am

longinvest wrote:Having users go and unlock the page (for edition) and play with advanced chart options does not seem very user-friendly to me. Could I change the default chart configuration so that we get good-enough display under both LibreOffice and Microsoft Excel? Would setting the X axis format to MMM-YY work?
LadyGeek, I now see that LibreOffice does not save changes to the chart x axis number format. But, I was able to change the format on the Display sheet, and it seems to work fine for me. I'll send you a PM so that you can check how it displays under Microsoft Office.
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

User avatar
Cosmo
Posts: 1199
Joined: Mon Mar 05, 2007 9:46 pm

Re: A Returns Spreadsheet for Bogleheads

Post by Cosmo » Sun Jan 04, 2015 11:58 am

Dumb question. Anyone know if this is compatable with iMac's Excel equivalent, Numbers? Cosmo

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Jan 04, 2015 12:00 pm

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

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

Changes:
  • Improved chart layout.
  • Added "Annual compound return" indications where appropriate.
Enjoy!
Bogleheads investment philosophy | Lifelong Portfolio: 25% each of (domestic/international) stocks / (nominal/inflation-indexed) long-term domestic bonds | VCN/VXC/VLB/ZRR

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

Re: A Returns Spreadsheet for Bogleheads

Post by longinvest » Sun Jan 04, 2015 12:08 pm

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

User avatar
Cosmo
Posts: 1199
Joined: Mon Mar 05, 2007 9:46 pm

Re: A Returns Spreadsheet for Bogleheads

Post by Cosmo » Sun Jan 04, 2015 12:14 pm

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

Post Reply