Portfolio Returns for 2012 and How To Calculate Your Returns

When I read this post recently, I knew it was time for a personal first: calculating our actual portfolio returns. You can see how our asset allocation has shifted a bit and I wanted to find out the results over the last year. Last year we added 2.5x the starting value to our portfolio so the market return is too small to see.

When I started out investing most of the funds went into bonds to give us a down payment for a house. This was around the time a bad smell started coming out of American housing so the bonds did well and we bought some stocks on the cheap too. When we bought the house it was a good time to cash out the rest of the portfolio too so we reset to 0 and have been building back up since then.

At that time I had started using Quicken but I found that it was very hard to enter details of the transactions, the automatic import didn’t help much, and the display where it shows the portfolio returns was very confusing. I went back to my investment tracking spreadsheet which is much easier but shows a nearly-useless number telling us how much the market value exceeds the amount we’ve invested (8.4% right now).

Finally I did the only thing that makes sense: open Excel and use the XIRR function to calculate returns. We make a monthly addition to our portfolio which was the same amount every month last year, and also had 4 extra additions throughout the year. I made a list of those, entered the function, and found that we got a return of 9.4% in 2012.

I was expecting something around 10% since we have nearly equal weightings in 3 major equity indexes that ranged from 7 – 16%. We let the bond allocation continue to slide to almost nothing this year so it didn’t drag down the returns much. The MSCI World index returned about 11.5% since it holds less in the Canadian index so you could say we under-performed the nearest benchmark this year. That’s good because I want to buy more at low prices!

This calculation doesn’t include a small workplace pension (with a current value that is 6% of our portfolio) or the RESP we opened this year, mainly because it would take a few minutes to find the information and I would have to enter twice as many transactions. The pension plan is invested entirely in the Canadian index as part of our overall allocation and the RESP is similar to that allocation so I don’t think this affects the results too much.

If you want to calculate your own returns this is simple to do. For example if you started the year with a $100,000 portfolio, invested $5,000 each quarter, and the balance was $132,000 at the end of the year, you would make a table like this:

	A	B
1	Amount	Date
2	$100,000	1/1/2012
3	$5,000	2/15/2012
4	$5,000	5/15/2012
5	$5,000	8/15/2012
6	$5,000	11/15/2012
7	-$132,000	12/31/2012

Important: The final balance has to be a negative number, as though you sold your whole portfolio, to do the calculation correctly. It will fail if you don’t put in at least one positive and one negative number. 

Then in another cell you would enter this formula:

=XIRR(A2:A7, B2:B7)

If you entered the information above this should give you a rate of 10.9% (make sure the cell is formatted as a percentage). This is always shown as an annualized return rate even if you put in numbers over a shorter or longer time. Excel is just putting different interest rates into an NPV calculation to see which one gets a value closest to 0 which is why you need the negative number for the final balance.

And that’s all it takes to figure out your returns. If you have withdrawals from the portfolio you can enter those as negative amounts to adjust the results. It really is simple to do this calculation as long as you have the information readily available. It’s so easy I may start doing this every quarter and putting it in our net worth spreadsheet. If I dig out some more old transactions I’ll have nearly 2 years of information to get started.