• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

XIRR Question

T

TKORL

New Member
Hello. The function I have below shows the cash flows yielding an XIRR of 0%, however, I do not believe that is correct....does anybody understand why this might be happening? Appreciate any insight.

10/29/2010 $ 84,044.62
1/5/2011 $ (535,634.58)
1/21/2011 $ 250,516.48
1/31/2011 $ 1,096,619.23
4/15/2011 $ 127,027.71
4/29/2011 $ 1,502,056.73
6/27/2011 $ (1,138,769.16)
7/8/2011 $ (1,874,721.02)
7/29/2011 $ 94,664.84
8/26/2011 $ (1,443,535.19)
10/14/2011 $ 1,234,071.72
11/9/2011 $ (944,859.40)
12/15/2011 $ (787,382.83)
1/27/2012 $ 253,280.87
2/17/2012 $ 787,382.83
3/20/2012 $ (262,460.94)
3/23/2012 $ (803,451.87)
4/17/2012 $ (2,624,609.43)
5/11/2012 $ (267,817.29)
6/6/2012 $ (524,921.89)
7/6/2012 $ 720,017.96
7/27/2012 $ (66,575.33)
10/5/2012 $ 527,534.66
11/30/2012 $ 2,235,664.02
1/18/2013 $ 315,086.72
2/8/2013 $ (1,339,086.45)
2/25/2013 $ (57,338.21)
5/6/2013 $ (61,291.09)
5/6/2013 $ 271,248.33
5/10/2013 $ 2,106,003.46
6/21/2013 $ 1,587,905.44
7/26/2013 $ 265,799.99
7/26/2013 $ 862,188.32
10/31/2013 $ (37,035.43)
11/8/2013 $ 132,429.85
12/31/2013 $ 11,102,071.00
 
Not related to your question, but since your dates are already sorted, why not use IRR instead of XIRR? Result then is 8.17%

Also, why are there 2 entries for 7/26/2013?
 
Thanks, it is because there are two separate cash flows. I understand XIRR is more robust than IRR, so we use that particular function...
 
Hi ,

The help on this function is :
Values is a series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value.

Dates is a schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than the first payment date, but they may occur in any order.
Click to expand...
I think your data is flouting either or both of these rules.

Narayan
 
Thanks, I figured it out. On a similar note, I have these two cash flow sets....I am wondering why they give such strikingly different IRR metrics. Does anybody know why they are so divergent?

9/30/2013 ($61,141.00)
9/30/2013 ($112,500.00)
9/30/2013 ($64,286.00)
9/30/2013 ($1,350,000.00)
9/30/2013 ($519,643.00)
9/30/2013 ($257,842.00)
10/10/2013 ($1,349,446.00)
12/26/2013 ($112,500.00)
1/31/2014 $3,582,066.00
XIRR= 0.00%

9/27/2013 -173,641.00
9/27/2013 -322,128.00
9/27/2013 -1,869,643.00
10/10/2013 -1,349,446.00
12/26/2013 -112,500.00
1/31/2014 $3,582,066.00
XIRR= -18.43%
 

Attachments

Hi, what I meant to say is that your link contains a spreadsheet with an alternative way of calculating IRR using Newton's method. I find that when I try to use the sheet, it is not able to calculate IRR....furthermore, Excel is doing it wrong.
 
Hi ,

I am sorry if I have confused you ; my takeaway from the links was that you should always use a negative guess ; this will always give the correct results. If you try your examples with a negative guess for both , you can see.

Narayan
 
Appreciate your response, but that's the thing though, even when I enter in a negative guess, it does not always give the correct answer. I have attached a simple example of one case.
 

Attachments

Hi ,

What can I say ?

According to you , what should be the XIRR value for the outflows and inflows that you have given ?

Narayan
 
Hi ,

XIRR totally depends on the time period of the dates range ; since you show everything happening within 2 days , the figure that Excel's XIRR function returns is correct ; change the 1/31/2014 to 1/31/2015 and see what you get.

Narayan
 
You must log in or register to reply here.
Back
Top