hi there,
I'm trying to determine what is the most accurate way in which to calculate the annualised performance of a portfolio over a series of monthly for various periods (1 year, 3 years 5 years etc).
I have a portfolio value at the end of each month and during the month there are usually a series of cash flows (eg. withdrawals, additions to the portfolio).
I've tried using the =POWER((1+C31)*(1+D31)*(1+E31)*(1+F31)*(1+G31)*(1+H31),12/12)-1
the cells referenced are the performance of the portfolio in each month. this is calculated as (the increase/decrease in the portfolio value subtract/add the addiitons/withdrawals to/from the portfolio) divide by (the starting value of the portfolio/add the addiitons/withdrawals to/from the portfolio). This formula doesn't accurately account for the cashflows as well as the formula below.
I have also tried the =xirr(A1:A14,B1:B14) formula. This formula will not provide you with an annualised return, which I need when looking at performances over longer periods (eg. annulised performance over 3, 5, 7 years etc.) The =Power(..... formula calculates various period performances ok. The =xirr(..... formula seems to account for the cashflows more accurately.
I have attached a sample spreadsheet which shows the different methods.
If someone could please provide some feedback on what they think is the best/most accurate way to calculate the performance of a portfolio over various periods, that would be much appreciated.
Cheers,
Easty
Bookmarks