Question From a Reader – Calculating Returns

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:

Personal Rate of Return Example

The 26.23% was found using the XIRR formula in Excel. Read this post to learn more about the XIRR function.

8 thoughts on “Question From a Reader – Calculating Returns”

  1. 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.


    “>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 … “

  2. 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?

  3. 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? 🙂

  4. 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.

  5. 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.

  6. 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).

    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!

Comments are closed.