Toronto Team  

  • T416.203.0067
  • 1.866.242.0203
  • F416.203.0544
  • 8 Wellington Street East
    3rd Floor
  • Toronto, Ontario M5E 1C5

PWL 2012 Rate of Return Calculator

November 5, 2012 - 5 comments

The Canadian Securities Regulators (CSA) has recently proposed rules to increase firm disclosure regarding performance reporting to their retail clients. Although these changes would be a step in the right direction, I don’t see this as a huge win for Canadian investors, for a number of reasons:

  1. If adopted, some firms not currently providing percentage return information to their retail clients may be given 2 years to implement this reporting requirement (likely dragging their feet every step of the way).
  2. Disclosure would be on the individual account level (do investors really care about the +10% returns of their TFSA account, when their much larger RRSP account returned -10%?).
  3. Benchmarking overall portfolio performance would still be futile (investors would have numerous account returns, not a single overall portfolio return necessary for adequate comparisons).
  4. Some firms may use a dollar-weighted rate of return while others may use a time-weighted rate of return (making an apples-to-apples comparison of performance between firms impossible).

For those Canadian investors who are not provided with a regular assessment of their portfolio’s performance, there is still hope.  With a little patience (and PWL’s help), you can calculate your very own portfolio rate of return (using the Modified Dietz Method).

Your ammunition

Your mission…should you choose to accept it

Step 1: Calculate your total month-end portfolio value, starting with December 2011.

Step 2: List all 2012 cash flows into (+) and out of (-) the portfolio for each month.  Include the day of the month beside each transaction.

Ignore dividends and interest – they are not relevant to this exercise (as long as they are not withdrawn from the portfolio). Transfers from one account to another within your portfolio (a.k.a. “journals”) should also be excluded, as the overall portfolio value does not change.
Step 3: Input each month-end portfolio value (from Step 1) into column E of the spreadsheet, as shown in the example below.
Step 4: Input each cash flow amount and day (from Step 2) into the same row that coincides with the month the cash flow occurred (there is room for up to 5 separate cash flows per month).

The year-to-date (YTD) return of 7.66% is shown in the bottom left-hand corner of the spreadsheet.  As the year goes on, investors can continue to update the spreadsheet until they have one full year worth of data. 

Even if your firm is lagging behind in terms of performance reporting, you now have the tools necessary to do this yourself. Please feel free to email me with any questions you may have regarding calculating your portfolio’s rate of return:

Additional tips for U.S. dollar accounts

  • If you have any U.S. dollar accounts, the month-end values must be converted to Canadian dollars (multiply the U.S. dollar account values by the appropriate monthly closing exchange rate in the USD -> CAD column from the Bank of Canada USD/CAD closing rate summary website).
  • All contribution and withdrawal amounts from your U.S. dollar accounts must also be converted to Canadian dollars.  Visit the Bank of Canada Daily noon exchange rates: 10-year lookup website, enter the transaction date (yyyy/mm/dd), check off the U.S. dollar (close) box, and click Submit.  Multiply the contribution or withdrawal amount by the closing exchange rate in the USD -> CAD column.


Special thanks to Michael Simioni, Chief Compliance Officer (CCO), PWL Capital Inc., and Raymond Kerzerho, Director of Research, PWL Capital Inc., for their comments and insights.


By: Justin Bender with 5 comments.
Filed under: Performance, Rate of Return
  13/01/2014 4:03:28 PM
Justin Bender
@ig - it would most likely differ depending on a number of factors. If you haven't done so already, please feel free to read my white paper on that same topic:
  13/01/2014 1:14:44 AM
Hi Justin, how would XIRR formula in Excel compare to the above calculations?
  17/12/2012 2:42:23 PM
Justin Bender
@winston – the 2012 calculator is sufficient to calculate an annual return for leap years. I will be posting a 2013 calculator at the beginning of next year that can be used for all other years. Calculating an annualized return over a number of years is a bit trickier...I’ll be sure to include a step-by-step on how to do this when I post the updated calculator. @Barrie Rowland – are you holding ETFs or mutual funds? If so, you can generally ignore the dividends and interest for the purposes of calculating your rate of return (these are already included in the market value of the security).
  14/12/2012 8:37:59 PM
Barrie Rowland
All my dividends and interest are used to purchase additional shares thus showing an increase in value of my portfolio. So would those amounts be a positive cash flow or should they be ignored as stated in the tutorial? Thank you
  14/12/2012 4:11:53 PM
Hi How would I add previous years to the spreadsheet? 1 year is not enough. Thank you

 Security code