Jul 11 2020 03:24 PM
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't get my head around How to calculate the annual ROR for the 3 COMBINED
Jul 12 2020 06:15 AM
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:
Jul 12 2020 07:49 AM
Jul 12 2020 08:39 AM
Solution
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.
Jul 12 2020 08:39 AM
Solution
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.