XIRR strange behaviour

**phe** · 09-05-2013, 04:26 AM

When using XIRR to calculate the internal rate of return for an investment of $10.000 that after 2 year has become $12.100. I'm expecting this to be 10%

If I use XIRR to calculate this for the 2 years period 2010-2012 it return 10% as expected but if I change to 2011-2013 it returns 9,98566%:

A1:=DATE(2010;1;1) B1: -10.000
A2:=DATE(2012;1;1) B2: 12.100

XIRR(B1:B2;A1:A2)=10%

BUT

A1:=DATE(2011;1;1) B1: -10.000
A2:=DATE(2013;1;1) B2: 12.100

XIRR(B1:B2;A1:A2)=9,98566%

Why?

Peter

**FinancialEngineer** · 09-05-2013, 12:29 PM

2012 was a leap year thus it had 366 days that is cause of the minor difference in your XIRR results

XIRR uses a Actual/365 day count convention when discounting the cash flows in it's XNPV equation

**FinancialEngineer** · 09-05-2013, 11:52 PM

A1:=DATE(2010;1;1) B1: -10.000
A2:=DATE(2012;1;1) B2: 12.100
A3:=(A2-A1)/365
A3: 2

-10.000 + 12.100 (1+i)^(-2) = 0
12.100 (1+i)^(-2) = 10.000
(1+i)^(-2) = 10.000 / 12.100
(1+i)^(2) = 12.100 / 10.000
(1+i) = (12.100 / 10.000)^(1/2)
i = [ (12.100 / 10.000)^(0,5) ] - 1
i = [ (1,21)^(0,5) ] - 1
i = 1,1 - 1
i = 0,1
i = 10%

A1:=DATE(2011;1;1) B1: -10.000
A2:=DATE(2013;1;1) B2: 12.100
A3:=(A2-A1)/365
A3: 2,00273973

-10.000 + 12.100 (1+i)^(-2,00273973) = 0
12.100 (1+i)^(-2,00273973) = 10.000
(1+i)^(-2,00273973) = 10.000 / 12.100
(1+i)^(2,00273973) = 12.100 / 10.000
(1+i) = (12.100 / 10.000)^(1/2,00273973)
i = [ (12.100 / 10.000)^(0,4993160054719562) ] - 1
i = [ (1,21)^(0,4993160054719562) ] - 1
i = 1,099856587738287252054960812161 - 1
i = 0,099856587738287252054960812161
i = 9,98566%

**phe** · 09-06-2013, 01:25 AM

Thanks a lot for your answer.

**arlu1201** · 09-06-2013, 01:51 AM

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.