How to Calculate XIRR for Annualized Returns

January 24, 2011 | Comments (13)

Written by

Jae Jun

follow me on

Facebook

Twitter

Calculate XIRR Fast and Easy

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 Returns

With 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 Returns

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

To Calculate XIRR easily, Download the Portfolio Return XIRR Spreadsheet

  • Excel 2007 and newer only.
  • No special plug ins required.
  • Just enable macros and run.
  • Jacob Chakola

    In the downloaded files, cannot figureout the excel file.It contains 4 folders,(-rels,docprops,xl,content types), using excel 2007 version

  • Paul
  • http://www.oldschoolvalue.com Jae Jun

    @ Jacob,
    If you look at the download link, it is an XLSM file, not a zip. Rename the zip to .XLSM

    It’s a windows thing.

    @Paul,
    Thanks!

  • http://www.actiuniieftine.ro David @ Actiuni Ieftine

    Saw it on Fat Pitch Financials as well. One quick question: if we split XIRR by the number of days in a year and then multiply by the number of days past from this year, do we get a number that we can compare to the YTD return of mutual/hedge funds/indexes?

    Thanks!

  • Jacob

    Get it right.
    Thanking you

  • http://www.oldschoolvalue.com Jae Jun

    @ David,

    No that won’t work. What you are asking is much more complex and requires a different formula which is on the spreadsheet, but is hidden in the free version.

    1. Andy

      If you stopped trading after Jun 30, 2010, your annualized return should be 50% by 12/31/2010. But the XIRR shows only 6.20% (I changed the start date to 1/1/2010 with $10,000 in balance.) Why?

    2. http://www.oldschoolvalue.com Jae Jun

      @ Andy,
      It’s because portfolio returns should not be calculated on absolute returns. That’s what I used to do but it’s not a good way. Plus if you deposited money inbetween, you really didn’t earn 50%.

      You and I could drive a total distance of 500miles from A to B.
      But if you did it in 5 hours while it took me 10 hours, there is clearly a difference in performance.

    3. http://www.eden-partnerships.com Assaf Nathan

      Hello,

      I think you got it wrong. XIRR is used to calculate internal return, and it uses “guesses” to guess the return.

      There is no replacement to straight-forward calculations.

      If you intend to do money management, you cannot use this function.

      Let me give you an example:

      I have 1000$ to invest from a first investor that i’ll manage the money to.
      I invest the money in January 1st, and lose 99% up to 31st of June.
      So I’m left with 10$ out of the initial 1000$.
      Now I get another deposit of 1000$ from another investor.
      Till December 31st, i do 100% on the portfolio and end up with 2000$.
      Xirr will show you 0% internal return, while your return us actually
      (-98%) (minus 98) for the year.

      XIRR does not take into account fluctuations in the inrerest rate of the principal, it takes a constant rate of return.

      If you will use XIRR for latter example, you are creating some kind of a “ponzi” scheme in which the second investor paid for the first’s losses.

      Thanks
      Assaf

    4. http://www.eden-partnerships.com Assaf Nathan

      Hello,

      I think you got it wrong. XIRR is used to calculate internal return, and it uses “guesses” to guess the return.

      There is no replacement to straight-forward calculations.

      If you intend to do money management, you cannot use this function.

      Let me give you an example:

      I have 1000$ to invest from a first investor that i’ll manage the money to.
      I invest the money in January 1st, and lose 99% up to 31st of June.
      So I’m left with 10$ out of the initial 1000$.
      Now I get another deposit of 1000$ from another investor.
      Till December 31st, i do 100% on the portfolio and end up with 2000$.
      Xirr will show you 0% internal return, while your return us actually
      (-98%) (minus 98) for the year.

      XIRR does not take into account fluctuations in the inrerest rate of the principal, nor fluctuations in the principal itself. it takes a constant rate of return.

      If you will use XIRR for latter example, you are creating some kind of a “ponzi” scheme in which the second investor paid for the first’s losses.

      Thanks
      Assaf

    5. http://www.eden-partnerships.com Assaf Nathan

      In other words:
      To calculate your return, you must calculate the return on a CONSTANT principal.

      If you get some kind of a cash movement (withdrawal or deposit) you MUST freeze – calculate the return before the transaction, record it, and start fresh with the new principal.
      You can do it on a daily basis, monthly, yearly, or combined, as long as you calculate every period in which there was no money movement independently.

      Later, you just multiply the yields to generate a yearly yield.

      For instance, in my previous example, you multiply
      -99% by 100% and get:
      (1+(-0.99))*(1+1.00) – 1 = -0.98 = -0.98%

      You can also do this calculation for 1 day, then 1 month, 6 months, 1 day again, etc, till you complete a year. Your yearly yield will be the multiplication.

      Unfortunately, this is the ONLY way to calculate returns on a portfolio with money that cones in and out – investments and withdrawals in case of a fund or expenses and salaries in case of a personal portfolio.

      Using XIRR will generate erratic numbers in ALL cases, the error magnitude depends on the magnitude of cashflows comparing to the principal and on the volatility of your portfolio.

      But then again, do you really want errors when you measure your results? :-)
      Just dont use XIRR and use the plain pencil and notebook method, just like Warren Buffett did.

    6. http://www.oldschoolvalue.com Jae Jun

      Hi Assaf,

      Thanks for the heads up and advice. I’ll look into it again.
      But regarding XIRR, since most people only need to measure one portfolio, XIRR is a perfectly fine shorthand method of automating all the calculations.

      But you are right about the calculations when multiple accounts and transactions are involved. There must be a quicker and easier way though.

    7. Derek E

      If I want to compare my returns versus the market stated returns, or versus a mutual funds stated return for a given year is this the way to do my calculations?

    x
    Receive Email Tips and Strategies on Achieving Higher Stock Returns

    Bonus: FREE Set of 9 Investing Spreadsheets