Toward the end of December, I received some questions from readers asking about how to calculate average returns on investment portfolios using Excel. In previous posts on this site, you can read about using CAGR, Excel’s XIRR function, and simple averages, but they all come with one big warning, which is that your previous average performance may not necessarily be a good way to set your expectations about your future performance.

In other words, let’s say that you did your calculations of your return over some number of years and found out that you averaged a 5% annual return. That doesn’t necessarily mean that you should expect a 5% return next year. I thought it might be worth delving a little further into the potential pitfalls of using averages.

Let’s say we have three hypothetical portfolios and data on their performances over a 10-year period. Suppose that in each one, we started off with $100 to invest and ended after 10 years with $110.

Using CAGR to calculate their returns, we find that all three portfolios averaged 0.958% over the 10 years. Paltry performance, but hey, this is for illustration purposes only!

Even though we know that all three portfolios all averaged the same 1% return, the key point is that we don’t know what went on between the 10 years merely by looking at the 1% figure.

Take a look at the data and graphs below, and you’ll see three porftolios that behaved very differently indeed (click image below to enlarge):

(Note: the graphs above were generated using BonaVista Microcharts, an Excel add-in)

For each porftolio, I’ve plotted the portfolio’s performance (the line graphs) as well as the implied year-over-year % gains (the bar graphs). Green bars represent positive returns and orange bars, negative returns. Similarly, on the line graphs, green represents the high value and orange the low value of the portfolio’s performance.

As you can see, there are many ways to start with $100 at the beginning of 1991 and end with $110 in 2000. Porfolio A underperformed for many years before improving halfway through. Portfolio B always had small but positive and nearly constant returns each year. Portfolio C started off strong and dropped in recent years. What’s going on here? It’s pretty simple. CAGR only takes ending and beginning amounts to calculate returns and ignores all the stuff that happens in the middle. Remember, CAGR “smoothes out” the performance in the middle to come up with one, constant figure that would have given you the same result over the same period given your initial and ending values.

This is something worth keeping in mind when you’re calculating your own returns and get excited about having made 10% in two years. Taking that amount and projecting that out for several future periods is a very tempting to do, but it’s not necessarily the most realistic or conservative approach. Instead, try projecting returns using lower numbers just to do a sensitivity analysis and examine worse-case scenarios.

There’s one big area of investing where average annual return data pops up all the time, and that is **when you’re evaluating mutual fund performance**. You can find 1-year, 3-year, 5-year, and 10-year average annual return information for almost any mutual fund available. But if you’re relying on only information on past performance to guide your investing choices, then you might want to look further.

Suppose you had a choice to invest in Porfolios A, B, or C, above. How would you determine which one you’d want to invest in? Besides looking at prospectuses, underlying holdings, fees, and other relevant information, you might want to try to find out what, if anything, changed in Portfolio A in 1994 and Portfolio C in 1998 that made them suddenly change performance. Perhaps it was a change in managers? A shift in investments? What does that mean, positively or negatively, for the mutual fund?

Another point worth bringing up is the idea of end point bias, in which a couple of good years’ performance can make a mutual fund’s overall performance shine, even though previous years might have been dreadful. This happens because whenever we look at average returns on mutual funds, we’re almost always looking **backward** from the current period. So, when you read about 1-year, 3-year, 5-year, and 10-year performances in 2007, you’re really looking at 2006, 2004-2006, 2002-2006, and 1997-2006, respectively. If a fund performed poorly and then had a huge boon in 2006, then all those figures might still show some nice returns.

In summary:

- CAGR can be misleading because it only takes beginning and ending values to determine an overall average return, ignoring performance between the two values.
- If you’re projecting future performance, try using smaller values than your average return to gauge “what-if” scenarios.
- Beware of end point bias when reading about mutual fund performance.
- Look beyond past performance and into managers’ tenure, holdings, prospectuses, fees, and other information when choosing a mutual fund.

Remember, finance is an art as well as a science, and don’t let numbers fool you!

***************************************************

**Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss**

***************************************************

## BonaVista Microcharts: a very cool Excel charts add-in | Experiments in Finance

[…] You might have noticed that I’ve been using it in a couple of my posts (like here and here) that include MicroCharts built using Excel 2003. What can I say? I love the application, and even if I’d stumbled upon this product on my own, I’d be inclined to get it to use at work, anyway. MicroCharts offer a slew of different types of charts (pie, line, column, bar, bullet). For such a simple application, I find them pretty powerful. Here are some example column charts (click to enlarge): […]

## Art Invest Online

Real estate investing online…Gone are those days of book keeping and note pads, everything that use to be monitored on paper is been looked in online today. In fact, the market for real estate online investing could be better. With all the investing information online, it is possi…

## Peter

thanks for info on CAGR and XIRR. my situation is a little different I think. I’m looking at a P&L statement covering 4 yrs. I need to measure the “CAGR” of the EBIT figures. For example, the first couple of years may be negative EBIT figures and moving to positive ebit in outer years. (-12, -16, -2, 7) There is really no initial investment per se, or an end withdrawal. What formula is the best way to measure the average yearly growth rate. CAGR or XIRR don’t quite seem to work so well. It is much easier to calc CAGR if numbers are positive for each year’s figures continue to grow larger. Appreciate any thoughts on this. Thanks. Peter

## Please Leave a Comment!