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
[…] https://www.canadianportfoliomanagerblog.com size: 452px x342px […]
[…] https://www.canadianportfoliomanagerblog.com size: 452px x342px […]
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?
@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.
I use this. It works great for me.
http://marketxls.com/portfolio-manager/
Well, using MarketXLS works for me. It’s great.
http://marketxls.com/portfolio-manager/
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.
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.
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.
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.
https://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.