By JLP | January 24, 2007
My How ‘Total Returns’ Are Calculated post received the following comment from a reader named Dan:
I get a little confused when I try to make these calculations to work with (1) purchases that occur at different times and (2) account fees. As an example of each:
- Suppose I buy somes shares in January. I earn a dividend in March and reinvest that. Then in April, the price has changed, and I buy more shares. I earn another dividend in September, then December. Can I calculate a “combined APY” that represents my return on both those transactions together?
- Suppose everything is the same as above, except that in August, I have to pay a $20 fee to maintain the account. It matters -when- this fee is paid, right? How do I consider that in my calculations? Are there any good reference books for this?
This is fairly easy to do in Excel. This particular calculation is called the Personal Rate of Return. I did a tutorial on this last April, which you can read here. Just remember for the formula to work, you must have both negative and positive numbers.
So, I would enter any deposits you make into the account as negative numbers and withdrawals (such as the $20 account fee you mentioned) as positive numbers. CORRECTION: I was wrong. You do not include reinvested dividends in the calculation because they are accounted for in the end result. The $20 fee also should be included as a negative number.
It should looks something like this:
The 26.23% was found using the XIRR formula in Excel. Read this post to learn more about the XIRR function.