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:
    April 20, 2014 at 1:05 am

    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

    Reply
    1. VinManHBK007 says:
      April 20, 2014 at 4:12 pm

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

      Reply
  2. ashalanshu says:
    April 19, 2014 at 1:23 pm

    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

    Reply
    1. rajan.panchal24 says:
      April 19, 2014 at 8:45 pm

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

      Reply
  3. ashalanshu says:
    April 19, 2014 at 8:08 am

    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

    Reply
    1. rajan.panchal24 says:
      April 19, 2014 at 10:52 am

      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

      Reply

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.