Login     Register

        Contact Us     Search

Rely on the results of the XIRR function

Oct 18

Written by: Charles Flock
10/18/2011 8:14 PM  RssIcon

11 Financial Calculations that you can’t do in EXCEL - Part 3 of 11
3. Rely on the results of the XIRR function
Here’s a very simple example. Enter the following dates and cash flows into a worksheet.



Then enter the following formula in B10: =XIRR(B2:B10,A2:A10). You should get the following result



EXCEL has calculated a value of 2.98023-09. Let’s reformat that. This should be close enough; 0.00000000298. In other words, approximately zero. Is that really what the internal rate of return is for these cash flows? Before answering, remember that if the value for discount rate is zero, than the discounted value of the cash flows will be equal to the sum of the cash flows. Is that the case here?
Absolutely not. But let’s use this result in XNPV, by putting this formula in B12. Here’s what we get.



The XIRR result certainly doesn’t satisfy the requirement of producing an XNPV of approximately zero. In this case, the XNPV result is actually approximately equal to the sum of the cash flows.
If we calculate the XIRR in SQL Server, we can see what the real result should be.
SELECT wct.XIRR(cf,date,NULL) as XIRR
FROM (VALUES
      ('2010-04-01',5290.5),
      ('2010-04-01',5290.5),
      ('2010-04-01',5290.5),
      ('2010-04-01',5290.5),
      ('2010-11-08',6273.2),
      ('2011-02-01',5417.2),
      ('2011-03-15',5492.05),
      ('2011-05-12',5486.15),
      ('2011-08-31',-20004)
      ) n(date, cf)
This produces the following result.
                     
                  XIRR
----------------------
    -0.592046674219327
Can we check that answer? Well, not in EXCEL (see item 2). Let’s do it in SQL Server.
;WITH mycte(date, cf) as (
SELECT *
FROM (VALUES
      ('2010-04-01',5290.5),
      ('2010-04-01',5290.5),
      ('2010-04-01',5290.5),
      ('2010-04-01',5290.5),
      ('2010-11-08',6273.2),
      ('2011-02-01',5417.2),
      ('2011-03-15',5492.05),
      ('2011-05-12',5486.15),
      ('2011-08-31',-20004)
      ) n(date, cf)
)     SELECT ROUND(wct.XNPV(n.r, cf, date), 8) as XNPV
      FROM (SELECT wct.XIRR(cf, date, NULL) as r from mycte) n, mycte
This produces the following result.
                  XNPV
----------------------
            -1.736E-05

This is quite clearly a bug, and a bug that has been around for quite a while. Even if it returned #NUM! because it couldn’t find a solution it would be better than what it currently does. This is truly a disastrous result for anybody who is using the XIRR function to evaluate performance. There is a huge difference between having an internal rate of return of zero and -60%.

 
Previous: Enter a negative rate into the XNPV function
Next: Calculate the price or yield of a bond on its issue date when it has an odd first period
 

 

Tags:
Categories:
Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service