Michaelcanalizo
asked on
Return analysis for multiple investments in Excel
How to create an accurate measurement for the return for different assets (Stock A, Stock B, Stock C), which were purchased at different times, in different accounts (Pension Plan, Investment, IRA), at different prices and in different quantities. Some of the investments are short term (less than one year), and some are for more than one year.
How can I create an accurate measurement for the different assets (Stock A, B, C, and not the individual lots), which can be update as the time frame increases, and or the Stocks have new or additional lots added?
How can the return for an investment held for say 45 days be compared to an investment held for say 3 years?
I have tried using weighted average, and a 'daily rate of return',but my results do not seem correct.
Please see the attached spreadsheet which contains the formulas being used.
Please advise on the correct method to utilize as this information is critical to my decision process.
Your assistance will be greatly appreciated.
Return-Analysis-Question.xlsx
How can I create an accurate measurement for the different assets (Stock A, B, C, and not the individual lots), which can be update as the time frame increases, and or the Stocks have new or additional lots added?
How can the return for an investment held for say 45 days be compared to an investment held for say 3 years?
I have tried using weighted average, and a 'daily rate of return',but my results do not seem correct.
Please see the attached spreadsheet which contains the formulas being used.
Please advise on the correct method to utilize as this information is critical to my decision process.
Your assistance will be greatly appreciated.
Return-Analysis-Question.xlsx
It is worth noting that each change of sign in the cash flows will produce a distinct "answer" to the question on rate of return. If you have a mix of Purchases and Sales over the period, then there will be more than one correct answer. XIRR will return one of those answers, but you can bias it towards one or another of the answers by picking different values for the third parameter.
Brad
Brad
ASKER
Thank you for your response. I have tried to use the XIRR calculation, but the results do not make sense; please see the attached spreadsheet (S13:S27; W113:W27). The result is a return of 54.50%, which is not rational: the Gain is only $14,010 over an average period of 29.7 days. Is it possible the XIRR is an annual rate of return? That is, the actual rate of return for the period of time (average of 29.7 days), requires the XIRR to be divided by 365 days, thus producing a the actual return of 0.15%?
My ultimate objective is to compare the actual rate of return of multiple investments (all stocks), purchased over different periods of time, in different lot sizes, and at different prices. Such a comparison would allow me to ascertain how the stocks actually compare in their rate of return. If my calculation is correct (cell W23, which is 0.15%), then this result would seem to present such a comparison, but is this correct?
My ultimate objective is to compare the actual rate of return of multiple investments (all stocks), purchased over different periods of time, in different lot sizes, and at different prices. Such a comparison would allow me to ascertain how the stocks actually compare in their rate of return. If my calculation is correct (cell W23, which is 0.15%), then this result would seem to present such a comparison, but is this correct?
You are correct: XIRR is an annualized rate of return.
If you are comparing investments held for different periods of time, you can look at the percentage change in value or you can try to annualize it. Both comparisons offer useful information, but only an annualized calculation takes into account the length of holding period.
If you are comparing investments held for different periods of time, you can look at the percentage change in value or you can try to annualize it. Both comparisons offer useful information, but only an annualized calculation takes into account the length of holding period.
ASKER
How would the annualized calculation be performed, since the length of holding period is certainly important?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For the Pension plan, I arranged your cash flows and dates into the following table:
I could then calculate the annualized rate of return for the portfolio, taking into account the holding periods, as:
=XIRR(T11:T15,U11:U15,0.4)