SOLVED

annual rate of return for a number of investments -

%3CLINGO-SUB%20id%3D%22lingo-sub-1516622%22%20slang%3D%22en-US%22%3Eannual%20rate%20of%20return%20for%20a%20number%20of%20investments%20-%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516622%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3B%20driving%20me%20crazy%20-%20how%20do%20I%20calculate%20an%20annual%20rate%20of%20return%20for%20e.g.%203%20different%20investments%20in%20the%20same%20company%20for%20different%20amounts%20at%20different%20times%3F%26nbsp%3B%20Easy%20to%20do%20one%20at%20a%20time%2C%20but%20I%20can't%20get%20my%20head%20around%20How%20to%20calculate%20the%20annual%20ROR%20for%20the%203%20%3CSTRONG%3ECOMBINED%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1516622%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516991%22%20slang%3D%22en-US%22%3ERe%3A%20annual%20rate%20of%20return%20for%20a%20number%20of%20investments%20-%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516991%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725884%22%20target%3D%22_blank%22%3E%40theseus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20use%20the%20XIRR%20function.%20For%20that%20you%20need%20the%20dates%20of%20each%20of%20the%20purchase%20transactions%2C%20the%20amount%20paid%20out%20(as%20negative%20values)%20and%20then%20the%20current%20market%20value%20of%20the%20totality.%20If%20there%20were%20dividends%20along%20the%20way%2C%20they%20could%20be%20added%20into%20the%20cash%20flow%20sequence.%20See%20attached.%20You%20can%20add%20more%20descriptions%20to%20the%20data%20table%2C%20as%20desired.%20All%20XIRR%20needs%20are%20the%20dates%20and%20the%20cash%20flow%20data.%20The%20first%20one%20needs%20to%20be%20an%20outflow%20(negative)%2C%3C%2FP%3E%3CP%3EHere's%20an%20image%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1594559684069.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204803iFE0FE7BF4D2E437F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22mathetes_0-1594559684069.png%22%20alt%3D%22mathetes_0-1594559684069.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517045%22%20slang%3D%22de-DE%22%3ESubject%3A%20annual%20rate%20of%20return%20for%20a%20number%20of%20investments%20-%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517045%22%20slang%3D%22de-DE%22%3E%3CA%20href%3D%22https%3A%2F%2Fwww.finanzrechner.org%2Fgeldanlage-rechner%2Frenditerechner%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.finanzrechner.org%2Fgeldanlage-rechner%2Frenditerechner%2F%3C%2FA%3E%20%3CBR%20%2F%3E%20If%20you%20want%20it%20fast...%20%3CBR%20%2F%3E%20or%20%3CBR%20%2F%3E%20%3CA%20href%3D%22https%3A%2F%2Fwww.herber.de%2Fforum%2Farchiv%2F544to548%2F545651_Fondsrechner.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.herber.de%2Fforum%2Farchiv%2F544to548%2F545651_Fondsrechner.html%3C%2FA%3E%20%3CBR%20%2F%3E%20if%20you%20want%20to%20make%20something%20yourself.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20If%20it%20helped%20it's%20good...%20if%20not%2C%20ovoid%20ignorirers...%20is%20of%20a%20beginner..%20%3CBR%20%2F%3E%20Have%20fun%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517096%22%20slang%3D%22en-US%22%3ERe%3A%20annual%20rate%20of%20return%20for%20a%20number%20of%20investments%20-%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517096%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F725884%22%20target%3D%22_blank%22%3E%40theseus%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20revised%20version%20of%20the%20spreadsheet%20I%20posted%20earlier%2C%20with%20more%20variable%20dates%20of%20purchase%20(which%20XIRR%20can%20handle)%2C%20as%20well%20as%20dividend%20inflows.%20Play%20around%20by%20changing%20the%20numbers%20and%20dates%2C%20perhaps%20to%20reflect%20your%20real%20life%20situation.%20There%20are%20only%20two%20calculated%20cells%3A%20A8%2C%20which%20contains%20%3CSTRONG%3E%3DTODAY()%3C%2FSTRONG%3E%20so%20it%20will%20always%20be%20up-to-date%3B%20and%20E6%2C%20which%20has%20the%20%3CSTRONG%3EXIRR%3C%2FSTRONG%3E%20function%20in%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1517681%22%20slang%3D%22en-US%22%3ERe%3A%20annual%20rate%20of%20return%20for%20a%20number%20of%20investments%20-%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1517681%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%3B%20regards%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

4 Replies
Highlighted

@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:

mathetes_0-1594559684069.png

 

Highlighted
https://www.finanzrechner.org/geldanlage-rechner/renditerechner/
Wenn man es schnell haben möchte...
oder
https://www.herber.de/forum/archiv/544to548/545651_Fondsrechner.html
wenn man selber etwas basteln möchte.

Wenn es geholfen ist es gut...wenn nicht, eifach ignorireren...ist ja von ein Anfänger..
Viel Spaß
Highlighted
Best Response confirmed by theseus (New Contributor)
Solution

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

Highlighted

Thank you; regards