Search


Subscribe to AFM


Subscribe to AllFinancialMatters
by Email

All Financial Matters

Promote Your Page Too

The American's Creed

Site Sponsors

Books I Recommend


AFM in the Media


Money Magazine May 2008

Real Simple March 2008

Blogroll (Daily Reads)

« | Main | »

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:

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.

Topics: Financial Math Basics, Personal Rate of Return | 8 Comments »


8 Responses to “Question From a Reader – Calculating Returns”

  1. Dividend Money » On The Money Blogs Says:
    January 24th, 2007 at 4:18 pm

    […] All Financial Matters answers a reader question about Calculating Total Returns. […]

  2. The Finance Buff Says:
    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 … “

  3. Joseph Says:
    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?

  4. J.D. @ Get Rich Slowly Says:
    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? :)

  5. Mighty Bargain Hunter » Roundup for week of 21 January 2007, and some carnivals Says:
    January 28th, 2007 at 6:11 pm

    […] All Financial Matters answers a reader’s question on calculating returns. […]

  6. Marv Says:
    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.

  7. J Davis Says:
    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.

  8. Lisa Says:
    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

Comments