POSTED BY 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.

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:

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

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