January 24th, 2011 by Jae Jun
Print
PDF The current portfolio tracking spreadsheet has its shortcomings as it doesn’t include non invested cash when calculating returns.The true returns of any portfolio will include all cash flows and I have found the XIRR function in excel to be the best to calculate annualized returns.If calculating returns was as simple as taking the beginning balance and ending balance and then calculating the absolute return, tracking investment returns would be so much easier.But there is time value in money and once you start depositing or withdrawing cash and receiving dividends, it makes calculating annualized returns that much more difficult.Investing $1,000 in January certainly is much different to investing $1,000 in December, before the year end.That’s where the XIRR feature in Excel comes in.Using XIRR to Calculate Annualized ReturnsWith XIRR you can calculate annualized returns even when cash flow for your account is irregular.As an example, the starting balance is $10,000 with regular deposits and some gains totaling a portfolio balance of $15,000 on Jun 27 2010.
At first glance, without taking into account the cash deposits, you could be fooled into thinking the return is 50%. However, if you use the XIRR function, the calculated annualized return is much less at 28.8%.As you can see, if the cash flows become longer and irregular with different cash flows, the calculation by hand becomes virtually impossible.Free Spreadsheet to Calculate Annualized and Cumulative ReturnsNow you can calculate your returns quickly and easily with this free spreadsheet to calculate your portfolio returns.Enter your beginning balance at the very top with deposits as positive values and withdrawals as negative values.Enter the date next to each corresponding cash flow and if you need to calculate the return for any particular date, enter it into the third column.
Just make sure the ending balance is negative with a date to prevent errors.The results will then look like the image below.
Full credit goes to a site called Gummy Stuff that is unfortunately no longer online for giving me this idea.Download Portfolio Return XIRR Spreadsheet
Excel 2007 and newer only.No special plug ins required.Just enable macros and run.
