Forum Discussion

theseus's avatar
theseus
Copper Contributor
Jul 11, 2020
Solved

annual rate of return for a number of investments -

Hi; driving me crazy - how do I calculate an annual rate of return for e.g. 3 different investments in the same company for different amounts at different times?  Easy to do one at a time, but I can'...
excel
Formulas and Functions
office 365
  • mathetes's avatar
    mathetes
    Jul 12, 2020

    theseus 

     

    Here's a revised version of the spreadsheet I posted earlier, with more variable dates of purchase (which XIRR can handle), as well as dividend inflows. Play around by changing the numbers and dates, perhaps to reflect your real life situation. There are only two calculated cells: A8, which contains =TODAY() so it will always be up-to-date; and E6, which has the XIRR function in it.

mathetes's avatar
mathetes
Silver Contributor
Jul 12, 2020

theseus 

 

I would use the XIRR function. For that you need the dates of each of the purchase transactions, the amount paid out (as negative values) and then the current market value of the totality. If there were dividends along the way, they could be added into the cash flow sequence. See attached. You can add more descriptions to the data table, as desired. All XIRR needs are the dates and the cash flow data. The first one needs to be an outflow (negative),

Here's an image:

 

Resources