XIRR calculation is not correct , please help !

POSTED BY rajan.panchal24 ON April 18, 2014 11:41 am COMMENTS (6)

Hi All,

I googled some information about calculating XIRR and YTD and found below article useful:

http://whitecoatinvestor.com/how-to-calculate-your-return-the-excel-xirr-function/

Following the about link. I was trying to calculated the XIRR for the investment done in 2011 by me. The data is as below:

Date Cash flows
9/8/2011 1000 invested
11/10/2011 1000 invested
11/14/2011 1000 invested
12/12/2011 1000 invested
12/31/2011 -3687.60015 Assuming Redeeming
Type Return Formula
XIRR -0.40646 XIRR(J17:J21,G17:G21, 10%)
XIRR % -40.6464
YTD -0.15035 SUM((1+XIRR(J17:J21,G17:G21))^((DATE(2011,12,31)-DATE(2011,9,8))/365)-1)
YTD %  -15.0348
15% of 4000 601.392 (15.0348*4000)/100 = 601.392
Diff between amt invested and final amount 312.3999 4000-3687.60015 = 312.3999

Now the thing is that using XIRR I am getting YTD as -601 and \”difference between invested amount and final total amount is -312. I am not able to tally both amount.

I am not sure where I am wrong.. Can someone point out the mistake.

6 replies on this article “XIRR calculation is not correct , please help !”

  1. ashalanshu says:

    Dear Rajan, In case of negative returns just like your example, XIRR throws incorrect answers. Pattu has discussed it in his blog. You may have a look there in freefincal.

    This negative result may be the reason of wrong answer.

    Thanks

    Ashal

    1. VinManHBK007 says:

      Please go through the website http://freefincal.com/ to know more about this and that too in detail with a working calculator.

  2. ashalanshu says:

    Dear Rajan, in your original calculation, the YTD is meant for few days, not for whole year. Same can be understood from the exl. link shaered by you.

    Thanks

    Ashal

    1. rajan.panchal24 says:

      Yes Ashal. I m too calculating for returns only sept 2011 to 31 st dec 2011 which is less than a year.

  3. ashalanshu says:

    Dear Rajan, when all the investments were not made on 8th Sept itself, how can you calculate YTD for that date for all investments?

    XIRR figure is correct.

    Thanks

    Ashal

    1. rajan.panchal24 says:

      Hi Ashal,
      That what the blog says for calculating YTD. It also contains an excel file. The formula I used was this :SUM((1+XIRR(J17:J21,G17:G21))^((DATE(2011,12,31)-DATE(2011,9,8))/365)-1)
      The below blog also gives same formula to calculate return for specific days:
      http://www.financialwisdomforum.org/gummy-stuff/XIRR-YTD.htm

      Thanks,
      Rajan

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Download Our FREE Ebook!

Available only for first 100 people today

Download Our FREE Ebook!

Available only for first 100 people today