Compare Your Return to a Benchmark

Many investors think that they can compare the return for their portfolios with the published return for a benchmark, such as the S&P 500 index over the same period of time, and conclude that the larger return represents the better investment performance. In many cases, this approximate approach provides an acceptable answer. However, published returns assume a single investment typically at the beginning of the time period. If you contribute to or withdraw from your portfolio, comparing your return to the published benchmark return might not mean as much as you might think. For example, if you were contributing regularly during a recent slump in the market, your return might look worse than the benchmark's return, until prices recover. Then, your investing during the low periods would make your return outpace the benchmark. In short, to obtain meaningful results from a comparison of two portfolios, you must evaluate the two portfolios using identical cash flows occurring at the same times. If you contribute to or withdraw money from your portfolio, you'll need historical price and distribution data to properly pit your portfolio against a benchmark return.

Comparing Returns Based Only on Initial Investment

If you created your portfolio with a single investment and haven't added any money to it (which might happen if you roll over a 401(k) plan into an IRA), you can compare your portfolio return with the return for a benchmark without hunting down too much information. All you need is the portfolio value at the beginning and end of the evaluation period. For the benchmark, you can use the annual returns for each year of the evaluation period.

TIP

If you make additional contributions during the evaluation period that are insignificant compared to your initial value, comparing returns based on the initial investment alone is a reasonable approximation. However, if you make significant contributions after the initial one, you must take into account cash flows as described in later in this hack.

Because your portfolio has only an initial and ending value, you can use the Excel IRR function to calculate your return. The IRR function calculates the internal rate of return for a series of cash flows that occur at regular intervals, such as monthly or annually. To calculate the internal rate of return for your portfolio, specify the starting balance as the first cash flow in, enter zeros for the annual cash flows for each year of the evaluation period, and use the positive value of the ending balance for the final cash flow, as shown in .

Figure 1. Use the IRR function to calculate the return for a series of regular cash flows

Because the IRR function assumes that cash flows occur at a regular interval, it requires only the cash flow values, not the dates on which the cash flows occurred. The formula for IRR is shown in .

Example 1. The formula for IRR for a series of regular cash flows

IRR = IRR(B2:B11)

TIP

If you want to calculate the internal rate of return for a different period, you must specify the starting and ending values for the new period. Type the portfolio value for the starting date of the new period in the appropriate cell in the spreadsheet. For example, to evaluate the return for the past five years, enter the portfolio value for December 31, 1999 in cell B6 of the spreadsheet in . Modify the IRR function in cell B13 so the parameter encompasses the new cash flow values, B6:B11 in this case.

To compare your portfolio to an index, the easiest approach is to use a low-cost index mutual fund. For example, the Vanguard 500 Index Fund closely matches the return of the S&P 500 index. If you're evaluating your portfolio over a period that coincides with published annual returns (such as the past ten years), you can use the published fund total return from sites such as Yahoo! Finance, Morningstar, or MSN Money. However, if the period you want isn't published, you can use the annual returns for the fund to calculate the average annual return for the number of years you want.

To find the average annual return, first calculate the cumulative return over the number of years you want to evaluate. For any given year, you multiply the starting value for that year by (1 + year's return) to calculate the ending value. Because the ending value for that year is the starting value for the next year, the formula for the cumulative return for several years results in .

Example 2. Formula for cumulative return for actual annual returns

Cumulative return of five years of actual returns =
    (1+return1)*(1+return2)*(1+return3))*(1+return4)*(1+return5)

The average return for the period is the cumulative return raised to the power of 1/n, where n is the number of years of annual returns you used. The average return for five years is the fifth root of the cumulative return, as shown in .

Example 3. Formula for rolling return

Average return =
    ((1+return1)*(1+return2)*(1+return3)*(1+return4)*(1+return5))^(1/5) - 1

TIP

Subtract 1 from the return so the average return reflects only the increase in value, not the original investment.

If you store the annual returns in a column in an Excel spreadsheet such as cells B2 through B6, the PRODUCT function easily calculates the average return. The PRODUCT function multiplies a range of cells, so an Excel array formula for average return looks like the one in .

Example 4. Using the PRODUCT function to calculate average annual return

5-year average return = {PRODUCT(1+B2:B6)^(1/5) - 1}

Comparing Returns Using Cash Flows

Unfortunately, if you do contribute or withdraw significant amounts of money from your portfolio, you must compare your portfolio to an investment in the benchmark using the same cash flows. Obtaining the historical data you need for an index or even an index mutual fund takes some work and in some cases costs money. In addition to historical prices for the index on the dates of every cash flow, you must also know every index distribution (both dividend payments and capital gain distributions), when they occurred, and the reinvestment price.

Recent historical prices for an index aren't too difficult to find. For example, you can download a spreadsheet (up to seven years of historical prices) from Yahoo! Finance (http://finance.yahoo.com). For the S&P 500 index, BigCharts.com (http://www.bigcharts.com) price data goes back to 1970, although you can't download the prices to a file. Type SPX into the Enter Symbol box and click Historical Quotes in the menu bar. Type the date for which you want a price quote and click Look Up.

Second-Guessing the Past

What if you want to compare the returns that you achieved from past investments to determine which was the better investment? Unfortunately, the need for identical cash flows makes this almost impossible. For example, if you bought the investments at different times or made different numbers of purchases in each one, the timing of the cash flows makes a comparison invalid. You're better off evaluating whether an investment produced the returns you forecasted when you purchased it and learning from your mistakes if it didn't deliver.

Obtaining information about dividend and capital gain distributions is tougher. However, you can divine distribution information in several ways, or take advantage of some online tools that provide a cash-flow-based comparison of returns:

Owning an index fund: If you've owned an index fund in a 401(k) or another account, you can extract the distribution information from your past account statements. Of course, you must figure out how much the distribution is per share and then work this value into a reinvestment calculation in your IRR spreadsheet. Honestly, you're better off spending your time honing your investment skills and managing your portfolio.

Figure 2. The bivio Performance Benchmark report compares your portfolio performance to the results of investing your money in any of 30 Vanguard Index Funds