Hi everyone – I’m Justin Bender, Portfolio Manager with PWL Capital in Toronto.
In today’s video, I’m going to show you how to calculate your Modified Dietz Rate
of Return at Questrade.
First, visit canadianportfoliomanagerblog.com to download my free rate of return calculator.
Hover over the DIY Investor’s Toolkit tab at the top right of the screen,
and then click on Calculators.
Next, click on the Download Calculator button underneath the Modified Dietz Rate of Return description
– you can also save the file to your desktop or another location.
Under the Year column, we’ll enter 2016 into the empty cell to the right of the first
December month-end.
We’ll then enter 2017 into any remaining unlocked cells in the Year column.
Next, login to the Questrade online platform.
From the Accounts section, hover over the Reports tab and click on Statements.
Using the drop-down menu, select the first account that you would like to calculate a
rate of return for (this will be the RRSP account in our example).
Next, using the arrows located at the left and right of the screen, locate the December
statement for the year prior to the year that you would like to calculate.
For example, since we are calculating the 2017 rate of return, we will scroll until
we find the December 2016 monthly statement.
Click within the calendar box and then click on the download icon to the right.
At the top right of the statement you’ll find the month-end December balance - $5,000
in our example – you can enter this amount into the Portfolio Value column of the rate
of return spreadsheet (in the same row as the December 2016 month-end).
Now, if your portfolio includes more than one account, add together the month-end balances
of all accounts and include this total figure in the cell.
For example, if you have an RRSP account and a TFSA account each worth $5,000 at the end
of December 2016, enter $10,000 into this cell.
We can repeat this process for the next 12 monthly statements.
You may notice that the year-to-date return is an awesome 134.11%.
Don’t get too excited, though.
This is because we still haven’t entered any portfolio cash flows into the spreadsheet.
Once all of the month-end portfolio values have been entered into the spreadsheet, hover
over the Reports tab again, this time clicking on Account activity.
Using the Date range tool, select the one-year time frame that you are measuring – in our
example, this will be January 1st to December 31st, 2017.
Ensure that only the Deposits and Withdrawals icons are selected as an Activity type (they
will be filled in with colour if they’ve been selected) – deposits and withdrawals
are the only transactions that need to be inputted into the calculator.
When this has been completed, click on Export to Excel.
The three data columns we’ll be using in the downloaded Excel file are the Transaction
Date, Net Amount, and Activity Type.
Starting from the bottom row, we’ll note that the first transaction occurred on January
16th, 2017.
The net amount of this transaction was $500, and the activity type was a deposit (so this
canbe entered into the spreadsheet as a positive value).
If there are any account withdrawals, they should be entered into the calculator as negative values).
The spreadsheet allows for up to five monthly cash flow events.
If you’ve got more than that, consider switching all of your account deposits or withdrawals
to the same day – this way, you can just enter the total net amount into a single cash
flow cell.
We’ll enter the $500 deposit as a positive value in the first cash flow column, and in
the same row as the January 2017 month-end.
We will also use the drop-down menu to select January 16th.
We can repeat the same process for the 11 remaining monthly deposits.
Once you’ve completed all of your inputs, the spreadsheet will provide you with
an accurate year-to-date return – in our example, our portfolio returned 8.94% during the 2017
calendar year.
In my next video, I’ll show you how to benchmark your Modified Dietz rate of return to a portfolio
of suitable ETFs.