How to Calculate Investment Returns
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.
Calculating 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. To edit any of these spreadsheets, you need to go to the “File” menu and “Make a copy”.
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.
Some Technicalities
Quarterly Returns
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.
Taxes
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.
Leap Years
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.
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.
Calculating 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. To edit any of these spreadsheets, you need to go to the “File” menu and “Make a copy”.
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.
Some Technicalities
Quarterly Returns
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.
Taxes
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.
Leap Years
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.
Excellent stuff, and even better that the spreadsheets can help out so easily. I assume the XIRR is available on Google Spreadsheet too?
ReplyDeleteFor the taxable accounts, would you estimate the potential taxes owing (for example the capital gains tax on the price gain) at the end of the year and then subtract that from the balance to get the after-tax return? As long as you can get a good estimate that seems like the most accurate way.
ReplyDeleteIf you find yourself moving between different tax brackets you could establish one tax rate (the highest one) to use for these calculations so you can get a consistent view of your returns.
Another blog recently posted about return calculation methods and included one I hadn't thought of - splitting the year into quarters (or to be more accurate, splitting it at each point when you make a trade), calculating the return for each of these periods, and then stringing them together.
This is supposed to give a more accurate view of the effects of your security selection (or asset allocation) with less impact from the timing. It sounds interesting but although I find XIRR very easy to use, this other method sounds too time-consuming.
@Big Cajun Man: The XIRR function is in both Excel and Google spreadsheet. The links I placed in this post are to Google spreadsheets.
ReplyDelete@Simply Rich Life: Deducting any actual taxes paid each year definitely makes sense. Where things become more difficult is built-up capital gains on an asset you hold for many years. The yearly drag on returns due to capital gains taxes is lower the longer you hold an asset. One way to handle this is to estimate taxes each year if you liquidated and deduct this amount from the portfolio, but you'd have to reset the adjusted cost base (ACB) each year or else you'll be charging the same taxes every year. This method overly penalizes early years, but isn't too bad. Any other method I've thought of involves predicting in advance how long you'll hold an equity.
How you handle varying tax rates depends on what you're trying to achieve. If you want a consistent view of the profitability of your stock-picking skills in a taxable account compared to other investors, you're method of sticking to one tax rate seems good. If you want to measure your skills at investing while taking into account your varying tax rate, then it makes sense to use real rates.
I think the quarters idea was a comment on the Million Dollar Journey post. Splitting into quarters or splitting at trade points isn't any more accurate. XIRR accurately takes into account cash flows at different times. However, some people may want a more fine-grained view of their returns. I'm happy with yearly figures, but I can see some people preferring quarterly figures. I don't see much sense in getting figures every time you make a trade. Believing that this somehow gives a more accurate view indicates to me that the person doesn't understand how XIRR works. The internal rate of return isn't a perfect measure, but its limitations aren't related to mishandling transaction timing.
The alternative mentioned was at http://www.caniretireyet.com/computing-your-overall-investment-return/ (Time-Weighted Returns). The choice to use one or the other depends on what you want to measure so XIRR works for me.
DeleteFor the impact of taxes I would probably be most interested in knowing the long-term after tax return (from purchase to sale). The closest you could get to calculating this is to include the longest time period available each time you do the calculation. As you pointed out that number could shift due to the length of time included so the result might not end up being meaningful. At least dividends and interest are simpler :)
@Simply Rich Life: I prefer IRR to approximate methods. In the past, IRR was difficult to compute, but it's easy now that it's built right into spreadsheets.
DeleteGoing back and recalculating past years returns to reflect the fact that you didn't realize certain capital gains is one approach. I'm not sure there is any clearly best approach here.
Wow, thanks Michael. It's far from obvious that only deposits, withdrawals, and balances actually matter. I've never tried to compute IRR but I'm sure I would have taken quite some effort to rediscover this fact.
ReplyDelete@Patrick: You're welcome. The key is to think in terms of the black box.
DeleteMichael,
ReplyDeleteI have always made my contributions as a positive number and subtracted my balance at the end. Seems to give similar results. Why do you subtract contributions and add deductions and not the reverse?
Thanks,
James
@James: Either way works as long as you're consistent. It just comes down to your mental model. Suppose someone comes to you to pitch an investment where you give them $10,000 now and they return $3000 per year for 5 years. Because it feels like you're giving money away initially and getting it back later, the $10,000 seems negative and the multiple $3000 amounts feel positive. In the case of an investment account, it may feel to you more as though the money is remaining under your control so that the contributions are positive. You're thinking about it from the account's point of view rather than the point of view of the person (you) who does the deposits or withdrawals. Either way of thinking gives the same return as long as you don't mix them.
DeleteMichael, would you also share a spreadsheet format for portfolio management? Let's say I have several investments accounts each containing several funds, I contribute, I shift between accounts, I re-balance... And I want to see IRRs.
ReplyDelete@AnatoliN: If you want to calculate your IRR for a collection of accounts that represent your whole portfolio, the process is similar to what I described in the post. All that matters is the cash flows from outside all the accounts; cash flows between accounts don't matter. To get your return in some time period, you need the account value at the beginning and end of the period and the external cash flows. That's it.
DeleteIf you're looking for spreadsheet tools to help with rebalancing, I have that for my own portfolio, but I haven't figured out how to make it generic enough to be useful to others yet.
I am looking for a spreadsheet, your explanations on IRR calculation were sufficient. Ideas I have so far are either too bulky and rigid or insufficiently error-proof.
Delete@AnatoliN: I'm not sure I can help with the bulk or rigidity, but one trick I use to reduce errors is that I record all transactions to make sure my data matches the account balance. Then I know I haven't recorded an entry with the wrong sign (+/-). A disadvantage is that this involves recording every transaction even though most of them are irrelevant for the IRR computation.
DeleteGreat post. Any idea how you would "string together" your yearly returns to come up with your average rate of return since the start? It would also be nice to track say your 3yr, 5yr, 10yr returns as well.
ReplyDelete@Colin: Thanks. The XIRR spreadsheet function can do all of this for you. Start with all the cash flows in chronological order. Make sure that there are "account balance" and "starting investment" entries at the end of each year. You can think of the "starting investment" entries as the beginning of each year, and the "account balance" entries at the end of each year. Then just apply the XIRR function to whatever span of years you like; it will give you the compound average annualized return.
Delete