« Politics,… Plain and Simple Politics | Main | My Useless Office Depot Coupon »
Question From a Reader - Calculating Returns
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?
Dan,
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.
Topics: Financial Math Basics, Personal Rate of Return |
January 24th, 2007 at 4:18 pm
[...] All Financial Matters answers a reader question about Calculating Total Returns. [...]
January 24th, 2007 at 5:40 pm
The reinvested dividends should be ignored because they are part of the total return. The $20 account fee should have a negative sign, representing a cash deposit.
See http://www.gummy-stuff.org/xirr.htm
“>And dividends? Uh … yes. I originally suggested that reinvested dividends should be treated as (positive) entries, meaning they’re new investments. In fact, I was wrong and … “
January 25th, 2007 at 1:10 pm
Thanks for the Tip. I just wanted to add that the XIRR function uses a 365 day year. But I THINK that returns are usually calculated on a 360 day year?
January 25th, 2007 at 3:08 pm
JLP, my cousin (and co-worker) just walked into my office with a list of numbers and asked me how to calculaute his total return for the year. My eyes glazed over, but then I remembered you’d written on it recently. I sent him this link. You get him straightened out, okay?
January 28th, 2007 at 6:11 pm
[...] All Financial Matters answers a reader’s question on calculating returns. [...]
January 20th, 2008 at 3:52 pm
I had been trying for sometime to develop a formula I can
use. The samplelooksgood but can the .5be modified to a different percent to makeit more precise to the date the additionor withdrawl was made. I have tried do apercent ofthe year passed but get some strange results.
August 16th, 2008 at 2:01 pm
I appreciate your clear presentation of how to calculate an annual return. It is clear to me from reading that dividend reinvestments should not be entered as additional cash flows. The same would go for interest payments from a bond mutual fund for the same reason. However, my question involves short term and long term capital gains from mutual funds, and if these would be included in the calculation. They are reinvested like dividends, but one could make the argument that they are actually reinvestments of cash flows as well. I am unclear how to model these in my calculation. Any assistance would be greatly appreciated.
September 16th, 2008 at 9:46 am
This site has helped me so much, but my calculations seem to be way off.
We are trying to determine the annual rate of return from 401(k).
Example:
9/13/2002 ($35,871.41) BEG BAL
12/31/2002 ($921.44) Employee Contr.
12/31/2002 ($507.24) Contr. - ER Match
3/31/2003 ($1,653.72) Employee Contr.
3/31/2003 ($620.13) Contr. - ER Match
3/31/2003 $59.01 Div. pd. out
6/30/2003 ($834.74) Employee Contr.
6/30/2003 ($3,944.29) Contr.-Perf.match
6/30/2003 ($312.97) Contr. - ER Match
6/30/2003 $23.67 Div. pd. out
9/12/2003 ($8,019.00) market change(+)
9/12/2003 $52,662.37 END BAL
0.15% IRR
Can anyone help me with the calculations & let me know what I am doing wrong? I need to figure the annual rate of return for the past 10 years to predict what the 401(k) will be worth in 24 years. I am definatley not doing this out of curiosity - legal situation.
Thanks so much!
Lisa