Your Complete Guide to Index Investing with Dan Bortolotti

Calculate Your Personal Rate of Return

2017-12-02T21:36:10+00:00January 20th, 2014|Categories: Asset Classes, Financial Planning|Tags: |37 Comments

On January 9, I published the 2013 returns of my model portfolios. The equity markets performed spectacularly last year, but most investors are likely to be far more interested in the performance of their own portfolios. Problem is, calculating your personal rate of return is more difficult than most investors realize.

If you’ve made no contributions or withdrawals during the year, the math is simple enough. But what if you made a big lump sum contribution during RRSP season? Or took $7,000 out of your TFSA to buy a used car? If you make monthly automatic monthly contributions, you may have seen your account balance grow every month, but most of that increase is from new money, not investment returns. Any time you introduce cash flows to the portfolio, calculating your rate of return suddenly becomes much harder.

If you work with an advisor, he or she should provide you with your personal rate of return at least once a year. But hard as it is to believe, many aren’t doing this. The Canadian Securities Administrators issued a policy in July making it mandatory, but firms have three years to comply. The good news is you can do your own calculation in Excel using PWL Capital’s 2014 rate of return calculator, created by Justin Bender. The calculator is available free from the PWL Capital website: click the Rate of Return Calculator link on the home page, enter your email and you’ll receive a copy of the Excel file along with instructions for using it.

There are several methods for calculating your rate of return, and each has its strengths and limitations. Justin’s calculator uses the Modified Dietz method, which gives a time-weighted rate of return by accounting for the date of each cash flow. It calculates the portfolio’s monthly rates of return and then links this series to produce an annual figure. (Note that the Modified Dietz method is extremely accurate in most situations, but it can break down when cash flows are very large relative to the size of the overall portfolio.)

A few points to remember as you work with the calculator:

  • If you have several accounts earmarked for retirement savings, you should treat these as a single portfolio. If you’re using proper asset location, you may have bonds in your RRSP and stocks in your taxable account, so these two accounts will have dramatically different returns. But what matters is your overall performance. So don’t calculate the return of each account separately: add up the month-end values of each account and enter this total into the spreadsheet.
  • Do not make adjustments for dividends and other distributions, unless these amounts are withdrawn from the account. This is probably the most common mistake people make when using the calculator. Remember, cash distributions are already accounted for in the month-end portfolios values, so you do not count them as cash inflows.
  • If you transfer money from one account to another (perhaps you took your RRSP contribution from your non-registered account), don’t count this as a contribution or a withdrawal from your overall portfolio.

The current version of the calculator is set up for 2014, but it will work for 2013 and any other non-leap-year. For leap years, use the 2012 version.

20 years of returns

Justin and I have also complied the historical returns of my model portfolios going back 20 years. Of course, none of the ETFs in these portfolios have been around anything like that long (VTI is the oldest, with returns back to June 2001). So as in the past, we have used actual fund returns whenever they were available and index returns elsewhere. Whenever we used index returns, we subtracted the MER of the relevant fund.

Perhaps the most surprising result was how similar the long-term returns were across all three portfolios. From 1994 through 2013, the Über-Tuber—with its multiple asset classes and its tilt to small-cap and value stocks—did have the highest returns and the lowest volatility of the three models. But the differences were very small: the 20-year annualized returns ranged from 7.3% to 7.8%, while the standard deviations also fell within that same narrow range.

The takeaway message should be clear: don’t obsess over the small details of your asset mix, and don’t embrace complicated portfolios if you prefer simplicity. Just build a diversified, low-cost portfolio, stay invested, and rebalance when necessary. Do that and the returns will look after themselves.


  1. Chris January 20, 2014 at 8:17 am

    What rebalancing interval was used to compute the model returns?

  2. Slacker January 20, 2014 at 9:11 am

    Level up.

    How to calculate real after inflation return over a period of time?

    More level up.

    How to calculate real after inflation AND after tax return over a period of time?

  3. Kyle January 20, 2014 at 9:15 am

    Aha! I suspected that the returns numbers for my RBC mutual funds were a bit inflated.
    I’m about to start my own passive index investing. How can I calculate the exact cost from the MER of my mutual funds? (To see how much trading costs will compare.)

  4. Canadian Couch Potato January 20, 2014 at 9:39 am

    @Chris: The model portfolio returns assume annual rebalancing on January 1.

  5. will January 20, 2014 at 9:44 am

    I track my portfolio using the XIRR function in a googledocs spreadsheet. I find it works well, though its a bit inflexible about how you format your columns and data. Once you get the hang of it though it works well.

    Since I invest money biweekly every payday, I just update the total portfolio value every payday to track it. I also do it in a googledocs spreadsheet so I can use the googlefinance function to pull the actual market price of my funds.

    Not that I’m always watching it, but its nice that I can just open up my spreadsheet and it pulls all the data to give me an up to date view of everything.

    I also track my ACB for each fund which I find helpful as I can watch it slowly drift down as dividends get re-invested (I add them in as if they are free shares rather than worry about the $ amount.), while the fund price slowly drifts up. It’s a good reminder that everything is growing over time (and that im still ahead even if the market drops a little.)

  6. Robert_M January 20, 2014 at 10:27 am


    I was very interested in your reference to the policy change that came about in July. This is the second time I have heard about it but not from any mainstream sources. You would think that either the Harper government or the industry would be spinning the story to their benefit.

    As for my advisor, he has been MIA since April 2012. No doubt he is still sulking from losing a portion of the investments to passive ETFs. I am waiting until the 1 year anniversary before reaching out to him. Then we’ll have a frank discussion about where our relationship is going.

    This is rather unusual since the active portion of the portfolio actually beat the couch potato returns by 2 percent. I suspect that this is due to a rather large exposure to US and Asian markets. Regardless,you would think that the advisor would want to trumpet the success of the funds.

    I remain, as always, totally confused over how this business treats its customers.



  7. Neil January 20, 2014 at 10:53 am

    Kyle: I’ve found that RBC is actually fairly up-front about MERs. All of their fund information pages on the website have the Series A MER disclosed on the right hand side under the “Fund Details” box. The index page for all of their funds is , you can click on the funds that you own.

  8. Republic of Jon January 20, 2014 at 11:05 am

    Could you include in your model portfolio performance pdf the 1 year return, i.e., 2013’s return? This would be helpful in comparing to other portfolios, or my investing peers who like to actively manage their portfolios.

  9. Holger January 20, 2014 at 11:17 am

    Very good advice. I also track my rate of return using the Modified Dietz Method. In implementing that, I found Justin Bender’s white paper on this subject incredibly helpful:

    Question to Dan and others: How to valuate your RRSP holdings compared to non registered investments? After all, when I start to draw income from my RRSP, it will be taxed. To account for that, in my cross-account valuation roll-up, I multiply the market value of my RRSP holdings by (1 – expected tax %). Does this make sense?

  10. Joel January 20, 2014 at 11:54 am

    Is there anything wrong with using the XIRR function?

  11. MoneyMatters January 20, 2014 at 12:08 pm

    Hello CCP,
    Looking at your returns, it’s interesting that all 3 are so close over the 20 years, given there was a larger gap in some of the years. One question was when looking at VTI vs VUN, I get that VTI is more tax efficient in an RRSP, but is there any way to know how “much” this really adds up to as a drag? If I’m with iTrade and can do the $30/quarter fee, am I better in the long run with VTI, and how do I go about calculating just how much better it is?

  12. Neil January 20, 2014 at 12:14 pm

    @Holger: I’m assuming from the context of the question that you’re talking about a net worth calculation? I don’t see the deferred tax as relevant to the portfolio rate of return, which simply “is what it is”.

    I account for RRSP deferred taxes as a liability on the balance sheet, rather than messing with the asset side.

  13. Francis January 20, 2014 at 1:16 pm

    One major flaw with comparing 20 years of different porfolio return is they assume you have all your money in 20 years ago and not contributing any after. Correct me if I am wrong but if somebody start 20 years ago with 50 000$ and contributing 2000$ every month to whatever is off target allocation, the buy low opportunity should be higher in a more complex portfolio than a simple porfolio and the gap in return should be higher between the 2 method over the long run.

  14. will January 20, 2014 at 2:39 pm

    @ Joel

    XIRR works fine most of the time, only drawback is that it can break sometimes if your transactions get really complicated. Shouldn’t be an issue if you are just tracking overall portfolio that you just do regular contributions to and don’t take money out of very often.

  15. Kurt January 20, 2014 at 2:57 pm

    Would it be possible to include a “Hypothetical Growth of $10,000” type of graphic for each of your portfolios? Refer to the one on the VTI page as example.

    I always find that graph to be a useful way to visualize how an investment has performed historically.

  16. Andrew January 20, 2014 at 3:08 pm

    This is so interesting as I just read the article by Justin Bender he wrote in 2011 about calculating returns which refers to Weigh House’s cash flow RoR calculator.
    I will use the new spreadsheet but I also look at the Stingy Investor Asset Mixer for benchmarks and the Weigh House. To simplify I use net cash flows on a quarterly basis.
    I can’t wait until my bank offers better RoR data that incorporates all cash flows.

  17. joel January 20, 2014 at 4:48 pm

    @Will: But isn’t the point of XIRR to be able to calculate your annualized rate of return from irregular contributions? I thought IRR is for regular contributions that happen on the same day of the month?

  18. David January 20, 2014 at 6:51 pm

    @joel & @will: I also use the excel XIRR function for calculating annualized returns. It easily handles new contributions and also transfers out of the account, no problem. You just need to have a date column for every transaction in/out of the account. It’s pretty easy to set up, I’m not sure why anyone would need anything else?? Maybe Justin B can comment if there are some shortcomings to using XIRR?

  19. Be'en January 20, 2014 at 6:58 pm

    TDW does not give portfolio RORs. I should give PWLs calculator a go!

  20. Canadian Couch Potato January 20, 2014 at 7:51 pm

    @MoneyMatters: Great question about quantifying the annual drag of using VUN in a registered account: Justin and I are working on white paper that will be filled with these details. For a rough calculation, just look at the yield (about 2%) and multiply by 15% to account for the withholding tax (2% x .015 = 0.30%) and add the additional 0.10% in MER. That would make the total drag about 0.40%. Of course, you need to account for the fact that you are likely incur currency conversion costs to buy VTI and weigh the tradeoff.

    @Francis: You’re not quite right: the multi-year returns assume annual rebalancing, which is not the same as making a single contribution and leaving it alone. There are ways you can calculate rates of return that assume rebalancing at certain thresholds (such as whenever any asset class falls 10%, etc.) but I prefer to keep things simple.

    @joel and David: There’s nothing wrong with the XIRR function in Excel, but the results may be slightly different than Modified Dietz. Justin’s white paper explains in detail:

    It’s also important not to assume that everyone can build their own spreadsheet and correctly use the XIRR function. Justin’s is by far the most user-friendly ROR Excel calculator I’ve seen.

  21. Francis January 20, 2014 at 10:55 pm

    @CCP : Even if you rebalance yearly all your porfolio, I think you miss my point that in real life most of the people will add new money in the porfolio in a 20 years period and these return are only good for the money you invest 20 years ago and rebelance yearly.

    Conclude that a global vs uper tuber porfolio is more or less the same over these return might be a simplist conclusion.

  22. Jungle January 20, 2014 at 11:43 pm

    Francis: From my own XIRR experience, I would say yes; but only in the beginning, when your balance is small. Once you get a large portfolio, small contributions will not have as much effect since the bulk of the portfolio is riding the waves of the market. The contributions only cause a ripple.

  23. will January 21, 2014 at 8:47 am

    @David and Joel

    Yes that is what XIRR is for and it usually works really well. I have seen cases where the dates and deposits/withdrawals are so wonky that it fails and can’t calculate a return properly.

    It shouldn’t happen in regular use though, just something to keep in mind.

    My only complaint with XIRR otherwise is how restrictive it is with how you arrange your data. It was a pain to set it up properly at first as I had to re-arrange the way I had everything tracked to accommodate it. Once it is set up it works great though.

  24. Gerry P January 21, 2014 at 2:54 pm

    I have sent for the return calculator and give it a shot, though I’m not a frequent user of spreadsheets. In the meantime, I’m doing some rebalancing and, according to the Morningstar figures, my fundamentally-weighted ETF’s outperformed my cap-weighted ETF’s by quite a bit in 2013; for example CLU.C returned 50.7%, compared to VFV’s 40%, and CIE outperformed VEF by about 6%. (CAD hedging of VEF explains part of this latter difference.)
    Do you think this outperformance of fundamentally-weighted ETF’s in 2013 is a temporary blip?

  25. Canadian Couch Potato January 21, 2014 at 3:27 pm

    @Gerry P: Fundamental indexes are essentially value indexes, and value did very well in 2013. The RAFI indexes actually have quite a good track record, which isn’t too surprising given that the value premium is well known. I’m not sure the outperformance is sustainable with fees of 0.72% or more, but it’s a reasonable strategy: I wouldn’t call it a blip. That said, CLU.C did not return 50.7% in 2013. Both iShares and Morningstar are reporting 43.19%.

  26. Gerry P January 21, 2014 at 3:41 pm

    Thanks for the reply, CCP. I take your point about fundamentally- weighted indexes being very similar to value indexes and 2013 being strong for these indexes. I agree, these fundamentally-weighted funds are more expensive, and long-term, may not be worth the extra cost. The 50.7% figure I mentioned I was the “average annualized 1-yr” figure from my morningstar report for CLU.C –not sure how they calculate that figure, but perhaps it goes back one year from January 21, 2014, hence the difference from the 43.19% you quote. Or it may include the Cdn. currency depreciation…?

  27. Canadian Couch Potato January 21, 2014 at 3:47 pm

    @Gerry P: Any published return for CLU.C would include the USD appreciation, and the 1-year return as of January 21 is even lower, according to Morningstar’s website. So I don’t know where that number comes from.

  28. Gerry P January 21, 2014 at 4:04 pm

    Hmm, I wonder about this discrepancy too. I’ll ask my online brokerage to explain how Morningstar gets its “average annualized return” figures.

  29. mark January 22, 2014 at 3:02 pm

    Speaking of your returns, I just had a look at your 20 year history of performance of these portfolio’s, they are all basically identical, I see no need to have anything more than the simple couch potato, it has the fewest funds and that means lower trading and rebalance cost.
    I see no need whatso-ever for more than 3 broad base equity etf, and 1 bond etf, put it on auto-pilot and forget about it for a couple of years till rebalancing. There is way too much noise with all the etf choices and it is confusing.
    Just saying.



  30. Jakub January 23, 2014 at 12:58 am

    Having trouble using the downloaded excel. But here is what I have done.

    No matter how the cash flows for each day there is a certain balance.
    Each of these balances is there for certain number of days.

    1. Figure out profit/loss. Subtract total of all invested cashflows over the period from final value of your assets at the end of period. (for accountants Assets – Equity)
    2. Figure out what balances were invested and for what number of days. (used spreadsheet for that one myself – and yes this tracked only money I put in changes in assets such as dividends are irrelevant here).
    3. Calculate weighted average balance, from the above.
    4. Then using that average balance, total number of days, and your profit/loss, get your daily return rate. With PV being the average sum invested and FV was the PV + (profit/loss). n was the total number of days.
    5. Convert daily rate to yearly.

    Details I found on wikipedia looked up weighted average and compound interest.

    Does this work? Or am I making a mistake somewhere? Is there a way to calculate this much easier?


  31. David January 23, 2014 at 10:21 am

    You can combine google finance and google docs to import stock data into a spreadsheet automatically. Check out this link!

  32. Mike D January 23, 2014 at 9:02 pm

    This is one of the great things about RBC Direct Investing – there is a page where your rates of return are displayed using the Modified Dietz method, and provides tabular and graphical output.

  33. Kyle January 24, 2014 at 8:41 am

    Neil: Thanks for the link, but what I meant specifically is how can I calculate what the MER percentage costs me? If I make monthly contributions to my mutual fund (it’s for my RRSP) and never withdraw is the, say, 2% MER subracted from the amount of money I put into the fund? Or is taken from the interest that is reinvested for me quarterly? Or does the fund scoop out 2% of my shares every year on Dec 31? How do I compare this to the costs of index investing (smaller fund cost plus the cost of the trades)?

  34. Neil January 24, 2014 at 11:39 am

    @Kyle: Ah, sorry I misunderstood your question. You pay the MER indirectly, it’s automatically paid from the fund’s assets and reflected in its net asset value (the price you pay per unit). This means that returns that are reported on your account statement or on the website is shown on an after-fee basis. To approximate costs, it’s enough to take the MER and multiply it by your average annual balance.

    You may be interested in Dan’s post for deciding between mutual funds and ETFs, which includes a spreadsheet you can use:

  35. Frugality Gone Wrong - Weekend Readings January 25, 2014 at 12:31 pm

    […] Calculate Your Personal Rate of Return […]

  36. Jane November 16, 2014 at 8:30 pm

    Here’s the link for the calculator

Leave A Comment