How to Calculate your Average Annual Rate of Return

With Phase II of the Client Relationship Model (CRM II) fast approaching, Canadian investors will likely be on their own when trying to make sense of their reported rates of return (which will generally be meaningless for benchmarking purposes).  The Modified Dietz rate of return calculator (available in the Calculators section of the blog) continues to be my recommended choice for DIY investors who want to calculate their annual return in a given year.  But once they have a long string of annual returns, how do they go about calculating an average (or “annualized”) return?

Enter the geometric average annual rate of return.  For those investors who still have their G-card, this can be a terrifying equation to tackle.  I’ll admit that the equation has no place in everyday life – it should be restricted to Excel spreadsheets and only allowed to see the light of day once a year (preferably after year-end).

Geometric Average Annual Rate of Return:

Where:

r = Annual rate of return in year i

n = Number of years in the measurement period

I find it best to just jump right into an example when trying to understand how to calculate this return.  Let’s assume an investor has calculated the following annual returns over the past 10 years:

 Year Annual return 2004 5.33% 2005 19.64% 2006 19.18% 2007 -10.47% 2008 -31.78% 2009 14.97% 2010 11.47% 2011 -8.53% 2012 22.35% 2013 23.81%

The investor now wants to calculate their 10-year annualized return in order to compare it to a suitable benchmark return.  Here are the steps they would take using Excel:

Step 1:  Enter the calendar year in column A

Step 2:  Enter the corresponding annual returns in column B

Step 3:  Enter an equation in column C that adds 1 to each annual return in column B:

=1+B2

=1+B3

.

.

.

Step 4:  In a different cell, multiply all numbers from column C together (this can be done by using the PRODUCT function in Excel).  Take the result to the power of 1 divided by the number of years in the measurement period (in our example, the number of years is 10).  Subtract 1 from this result.  This sounds confusing, but the equation in our example would simplify to:

=PRODUCT(C2:C11)^(1/10)-1

This would result in a 10-year annualized return of 5.00%.

Example:  10-year annualized return calculation using Excel

By | 2017-01-17T15:03:01+00:00 November 19th, 2014|Categories: Rates of Return|14 Comments

1. skube December 14, 2016 at 4:12 pm - Reply

I guess I didn’t realize that two separate formulae are required (one for each year, and one for the average).

I’m still a little confused why one can’t simply add each year’s return and divide by 10 to find the average? Isn’t that the way we learned to find the average of something in school? 🙂 Using your example my naive calculation gives a 6.6% average, instead of your 5%. What am I missing?

• Justin December 14, 2016 at 9:20 pm - Reply

@skube: The best way to illustrate the issue with this method of calculating a portfolio’s average return is to assume a \$100,000 portfolio that earns 50% in year 1, and -50% in year two (for a simple average return of 0%).

However, at the end of year 1, the portfolio has grown to \$150,000 [\$100,000 x (1 + (0.50))]. At the end of year 2, the portfolio has decreased to \$75,000 [\$150,000 x (1 + (-0.50))]. The total loss in dollar values is \$25,000, but the percentage loss is 0% using your methodology.

2. Harrison Delfino October 20, 2016 at 9:58 pm - Reply

I use this. It works great for me.﻿
http://marketxls.com/portfolio-manager/

3. Harrison Delfino June 9, 2016 at 10:59 pm - Reply

Well, using MarketXLS works for me. It’s great.﻿
http://marketxls.com/portfolio-manager/

4. NBHMS January 2, 2015 at 2:17 am - Reply

Just to share, I was building a spreadsheet that I could insert yearly values and not have to update the formulas, and came up with the following:

You can drop column C, and just use this formula to calculate using Columns A and B, and it will adjust as you add years:

It’s an “array formula”, so after typing the formula you need to hit CTRL+SHIFT+ENTER rather than just ENTER. If you paste the formula into Excel, you may have to click into the cell before hitting CTRL+SHIFT+ENTER.

• NBHMS January 2, 2015 at 2:23 am - Reply

Unfortunately, looks like the web page formatting is dropping characters out of the formula when the comment posts, so it isn’t functional as posted.

5. Vidas M November 24, 2014 at 3:31 pm - Reply

Thanks for this info! Just few months ago I had to research and figure out how to geometrically link monthly/yearly profit numbers for my investment tracking and I am very glad to see that method I used is described by you as industry standard.

6. BenC November 20, 2014 at 10:07 am - Reply

Thanks Justin. I’ve been using a spreadsheet with a list of deposit dates and deposit amounts, and then at the end the current date and the (negative) current market value. I then use XIRR to calculate the rate of return. How would this compare with the method detailed here?

• Justin November 20, 2014 at 10:29 am - Reply

@BenC – this is a similar question to igra’s. Basically the XIRR (or money-weighted return) is not useful for benchmarking purposes. One of the other objectives of regulators is to help investors better understand benchmarking…which will be difficult since the methodology they chose to calculate the reported return is the money-weighted rate of return.

• BenC November 20, 2014 at 10:55 am - Reply

Thanks Justin. For some reason igra’s comment wasn’t on the page when I was looking at it. I understand the differences between time weighted and money weighted. I was just making sure that XIRR. To me, benchmarking is only useful in comparing two funds, or comparing a fund to its stated goals. Comparing an actual investor’s account to these benchmarks, or fund returns, provides little in useful information. The one way that I see to change this, is to have a service in which one could simulate the actual investments into their current fund, into a different fund. So I I deposit \$50 into fund A and \$50 into fund B on a monthly basis, and I rebalance to 50/50 fund A/fund B yearly, what would that look like if I used fund C instead of fund A and fund D instead of fund B. Basically a service to find the MWRR for your specific portfolio if you used different funds. This could be built into a brokerage’s online tools, but it would be nice for an offline version. Otherwise “benchmarking” and “investor’s portfolios” should probably never be mentioned that close together.

7. igra November 20, 2014 at 12:23 am - Reply

How does this compare to just using XIRR function in a spreadsheet application? Isn’t Modified Dietz an approximation to IRR? If so then why not just use IRR? Thanks!

• Justin November 20, 2014 at 10:25 am - Reply

@Igra – the Modified Dietz method approximates the time-weighted rate of return (which adjusts for cash flows in and out of the portfolio). This return methodology would be more useful if you were benchmarking your performance to an index return (which is also calculated using the time-weighted methodology).

The XIRR (internal rate of return) is also known as the “money-weighted rate of return”. This is useful if an investor wants to know how their portfolio has personally performed. If they happened to contribute a relatively large amount to their portfolio right before a market downturn, their money-weighted rate of return will likely lag the time-weighted rate of return (and vice-versa). This return will be the industry standard, and it is not useful for benchmarking purposes.