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

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!

@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.

Thank you for the explanation, Justin.
For me as a DIY investor who uses index ETFs the XIRR (MWRR) just makes more sense. Especially, since I make regular relatively small (compared to the size of portfolio) contributions. But I can see how TWRR is better for comparing money managers, in particular, when investors make lump sum contributions.

I can also appreciate the challenge for portfolio managers to explain seeming underperformance when it was due to unfortunate timing of contributions rather than anything that the manager had done. I would imagine most clients just care whether they have more money in their account or not and if their return %% look sufficiently impressive (preferably in double digits and definitely higher compared to savings account rates or GIC rates). It must be a difficult task to talk someone out of moving everything into GICs when they see a low MWRR that resulted from contributing at a local peak of the market.

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?

@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.

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.

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.

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.

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!

@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.

http://www.canadianportfoliomanagerblog.com/money-weighted-vs-time-weighted-rates-of-return/

Thank you for the explanation, Justin.

For me as a DIY investor who uses index ETFs the XIRR (MWRR) just makes more sense. Especially, since I make regular relatively small (compared to the size of portfolio) contributions. But I can see how TWRR is better for comparing money managers, in particular, when investors make lump sum contributions.

I can also appreciate the challenge for portfolio managers to explain seeming underperformance when it was due to unfortunate timing of contributions rather than anything that the manager had done. I would imagine most clients just care whether they have more money in their account or not and if their return %% look sufficiently impressive (preferably in double digits and definitely higher compared to savings account rates or GIC rates). It must be a difficult task to talk someone out of moving everything into GICs when they see a low MWRR that resulted from contributing at a local peak of the market.

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?

@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.

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.

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.

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:

=(PRODUCT(IF(ISNUMBER(B2:B9997),1+B2:B9997,1)))^(1/(INDIRECT(ADDRESS(MAX(($A$2:$A$9997″”)*(ROW($A$2:$A$9997))),COLUMN($A$2:$A$9997)))-$A$2+1))-1

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.

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.

Well, using MarketXLS works for me. It’s great.

http://marketxls.com/portfolio-manager/

I use this. It works great for me.

http://marketxls.com/portfolio-manager/