9

I have both an IRA in Vanguard and an old employer's 401k with Fidelity management. I would like to compare the performance between the two accounts over time, in order to determine if I should rollover the 401K into an IRA, possibly at Vanguard.

I have dollar cost averaged with both accounts, meaning that I have had $X per month invested into each account over the last Y years. Attempting to be a prudent investor, I chose to diversify my mutual fund and index fund holdings into numerous (think ~5 diff investments) per account.

I fundamentally believe that Vanguard's lower expense ratios will most likely result in better performance over this time frame, but would like to test my theory and see if I can compare the performance between these two investment accounts. However, it would appear that it may be a monumental task to get the mutual fund & index fund prices for every month over the last Y years, along with the amount of the investment bought, into a spreadsheet and then calculate a rate of return.

So therefore I have the following questions:

  1. Does anyone have any suggestions to compare the rate of return for account 1 vs account 2 with this dollar cost averaging scenario?
  2. Has anyone performed such an analysis in the past, and if so, how did you do it and what were the results?
  3. Any tips for setting up an Excel spreadsheet to do this?

Time is also an important factor for this analysis. I'm willing to put in a good 8 hours or more if necessary to do the analysis, but I don't think it's worth that much more than that. Thoughts?

Edit: Both answers are great, but I've accepted the James answer since he included the spreadsheet with two examples for how to dynamically calculate the information I need to perform an analysis.

8

Google Docs spreadsheets have a function for filling in stock and fund prices.

You can use that data to graph (fund1 / fund2) over some time period.

Syntax: =GoogleFinance("symbol", "attribute", "start_date", "num_days|end_date", "interval")

where:

  • "symbol" - stock symbol
  • "attribute" - high, low, open, close, vol, or all (quote also works, and defaults to close).
  • "start_date" - the date for the historical data. When only the start_date is specified, the historical data is just for that day.
  • "num_days" | "end_date" - can be either the end date for the time period over which you want to see historical data, or the number of days from the start date. Any number less than 50 is considered to be num_days. Otherwise it is considered an end_date.
  • "interval" - this specifies the interval "DAILY" or "WEEKLY", or 1 and 7 can also be used. This is the granularity at which the stock data is shown at daily or weekly intervals. Please note that queries for recent dates may not have information, as historical data may not be available for a day or two.

This analysis won’t include dividends or distributions. Yahoo provides adjusted data, if you want to include that.

  • This is awesome and the exact type of information I was hoping to learn about! The yahoo portfolio website also has dynamic spreadsheet examples like gummy-stuff.org/Excel/Yahoo2.xls that I'll check out as well. Thanks for the links! – CrimsonX Aug 20 '10 at 16:48
  • Also, dollar cost averaging is equivalent to the harmonic mean of your entry point prices. I think google docs has a HARMEAN function. – James Roth Aug 20 '10 at 17:34
9

The number you are trying to calculate is called the Internal Rate of Return (IRR). Google Spreadsheets (and excel) both have an XIRR function that can do this for you fairly simply.

Setup a spreadsheet with 1 column for dates, 1 column for investment.

Mark your investments as negative numbers (payment to invest). All investments will be negative.

Mark your last row with today's date and today's valuation (positive). All withdrawals will be positive, so you are pretending to withdrawal your entire account for the purpose of calculation.

Do not record dividends or other interim returns unless you are actually withdrawing money.

The XIRR function will calculate your internal rate of return with irregularly timed investments.

Links: Article explaining XIRR function (sample spreadsheet in google docs to modify)

  • It is not enough to stress the importance of this tip so I try to explain why (not just because it is useful). There exists no formula to calculate higher polynomials like easy quadratics so this kind of problems belong to computer brains. Next time you meet a computer-head claiming to do all their return calculations in their head please ask them would s/he explain some apparently easy numerical methods (but cumbersome, needing 9999...9 iterations), non-unique complex solution or some nice recursive return calculation. Y (+1) – user1770 Mar 31 '11 at 6:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.