Comparing IRA vs 401K's rate-of-return with dollar cost averaging

Question

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.

Answer 1

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.

Answer 2

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)