POSTED BY April 18, 2014 11:41 am COMMENTS (6)

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

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

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

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

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

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

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