Recently, Million Dollar Journey had a post showing how to calculate investment returns using the spreadsheet function XIRR. After reading a few questions in the comment section of that blog post and thinking about how I compute my own returns, I realized that this is trickier than it seems. This post gives step-by-step instructions (with actual spreadsheets) for how to calculate your investment returns.
Throughout this post I’ll use the following fictitious example of an RRSP account opened in 2011.
2011 Feb. 22: Open account and deposit $10,000 cash.
2011 Mar. 11: Buy 400 ABC shares ($23/share + $10 commission).
2011 Aug. 17: Deposit $2500 cash.
2011 Sep. 12: Buy a $3000 bond paying 2%/year interest.
2011 Dec. 30: ABC pays 50-cent/share dividend.
2011 Dec. 30: Bond pays $60 interest.
2012 Jan. 16: Sell bond for $2960 (includes embedded commission).
2012 Mar. 20: Withdraw $3500 cash.
2012 Dec. 31: ABC pays 55-cent/share dividend.
To compute yearly returns, we need to know the total account holdings at the end of each year. We can calculate the cash balance from the data above, but we need closing prices for all investments. To compute quarterly returns, you’d need account holdings at the end of each quarter. Here’s the data we’ll use for this example:
2011 Dec. 30: Account holdings
– $550 cash
– bond worth $3000
– 300 ABC shares worth $24 each
Total account value $13,150
2012 Dec. 31: Account holdings
– $175 cash
– 300 ABC shares worth $28 each
Total account value $11,375
I’ve created a spreadsheet that captures the account transactions without doing anything yet to calculate investment returns.
To understand how we calculate returns, it helps to think of an account (or collection of accounts) as a black-box investment. Money goes into and out of the black box, but the goings on inside the black box don’t matter directly, except for how they affect the amount of money that ultimately comes out. We record the money flow from the point of view of the investor who is outside the black box.
So, the initial $10,000 deposit is money out of your pocket, so it counts as minus $10,000. The $2500 deposit gets recorded as minus $2500. The later withdrawal gets recorded as plus $3500. It’s very important to get the plus and minus signs right; your results can be way off if you get any of them wrong.
Here is the part that may be tricky to understand: the purchase of the shares and the bond, the sale of the bond, the dividends, and the interest are all internal goings on that don’t matter. That’s right – they don’t matter. Remember to think of yourself as outside the account’s black box. No money left your pocket or entered your pocket for any of these transactions.
If we stopped here with just the 3 cash transactions, we couldn’t get useful results because we wouldn’t be taking into account the final account holdings. The idea behind the black-box approach is that the box starts empty, finishes empty, and we see how it performed in between. To do this we add a fake transaction that basically drains the entire account of the $11,375 it had at the end of 2012.
So we only need to take into account 3 real transactions and the final account holdings to compute the investment return. The spreadsheet function XIRR computes the account’s Internal Rate of Return (IRR) for us. Here is a spreadsheet that calculates the annualized account return for our example.
The annualized return over the entire period turns out to be 11.70%. The XIRR function is based on a 365-day year, so it will report slightly smaller than expected returns for leap years. I included a check column in the spreadsheet above. If an account charges (or pays) daily interest at a rate that compounds to 11.70% in 365 days with same cash inflows and outflows as our example, it should start and end with zero dollars. This is the definition of internal rate of return.
Calculating Yearly Returns
To calculate returns for each year, we need to break the cash inflows and outflows into separate years. To do this we need to add “Account Balance” entries at the end of each year. In our example, the account balance at the end of 2011 was $13,150.
But we can’t just pretend to take all the money out of the account and leave it that way. We need to pretend to put it back. I just put in a fake re-buy to handle this. So each account balance statement has a corresponding “Starting Investment” entry for the same (but opposite sign) dollar amount on the same day. The following spreadsheet computes the annual returns for 2011 and 2012 for our example.
So, we made 6.93% in 2011 and 16.38% in 2012. Once again, I showed the check column.
Note that because XIRR gives annualized returns, we didn’t actually make 6.93% for all of 2011. Of course, the account balance jumped around many times throughout the two years and so we were making these returns on different account balances each day; a balance of zero prior to 2011 Feb. 22 is just a special case of this account balance variation.
Returns for an Entire Portfolio
I tend not to worry much about the returns in my individual accounts. I treat all of my accounts as one large portfolio. So, I just include all the cash flows to and from all my investment accounts in one spreadsheet. This gives me annual returns for my whole portfolio.
That’s pretty much it. I discuss a few technicalities below that don’t affect most people.
To compute returns quarterly, add in “Account Balance” and “Starting Investment” entries on the last day of each quarter. Then use XIRR in each quarter. But remember that XIRR gives annualized returns. If you then want the quarterly return, take the return r from XIRR and calculate (1+r)^(1/4)-1.
It can be tricky to account for income taxes when computing investment returns on a portfolio that includes a non-tax advantaged investment account. I’ve done this for my own portfolio, but I find it challenging to think through how to properly handle the built-in tax liabilities of deferred capital gains and other tax complications. So, I’ll leave it to the reader to work this out for his or her own account.
You may want to adjust your returns in leap years for the fact that XIRR is based on a 365-day year. The difference is small and can be ignored for most purposes, but here is how to adjust for it: Take the return r given by XIRR and compute (1+r)^(366/365)-1.