How to Calculate Annual Investment Returns Using XIRR

After my 2012 year end net worth update, there was some chatter about how to properly calculate annual investment returns.  The fact is that there are a number of ways to calculate annual investment returns, some easier than others.

For example, if you have a starting portfolio value and an ending portfolio value for a particular year and no contributions or withdrawals, it’s a relatively simple calculation.  In this case, a simple brute force way to calculate a return that I use often is:

[Ending portfolio value (Dec 31st of current year)/Starting portfolio value (Dec 31st of previous year)] – 1

If my portfolio is worth $120,000 at year end but started at $100,000, then my annual investment return is 20%.

However, in most situations, investors add and remove capital from particular investment accounts which can weigh on investment returns.  This is where it can get tricky to calculate manually, so I like to use a trusty spreadsheet to do the work for me. Specifically, the XIRR function.

The XIRR Function

The XIRR function is similar to the IRR (internal rate of return) function but is more robust.  IRR allows for the calculation of returns with “regular” contributions, but XIRR allows for random contributions or withdrawals throughout the year.  The real beauty is that you do not need to be a spreadsheet guru to use these functions.

The Syntax for the function is:

Excel: XIRR(values, dates, [guess])  Open Office: XIRR(values; dates; [guess])

Values are the range of portfolio values; Dates are the range of dates; and Guess is an approximate percentage return (optional).

Here is an example from Canadian Money Forum using Open Office;

To use this function, you’ll need to gather some information on your investment portfolio:

• Value of portfolio at the end of the previous year (put positive number in spreadsheet)
• Value of portfolio at the end of the current year (put negative number in spreadsheet)
• Date(s) and amount(s) of contribution(s) to the account (put postive number in spreadsheet)
• Date(s) and amount(s) of withdrawal(s) from the account (put negative number in spreadsheet)

In this example, at the end of 2011, the portfolio value was $10,000. Throughout 2012, there were 3 contributions to the account at various times in the amount of $1,000.  The final value at the end of 2012 was $15,000.  Using the XIRR function, the return for this portfolio is 17.41%.  If you were to simply subtract the $3,000 in contributions from the end value, then the return would be an overstated 20%.

Another example is with my leveraged dividend portfolio.  In 2011, the portfolio ended with $87,500 in value.  There was a $10,000 contribution at the end of August 2012, and the end value for 2012 was $108,000.  Punching it all into the XIRR function shows a 11.5% return for the year.

There you have it, a relatively easy way to calculate your annual investment returns.  Let me know the XIRR function works out for you.