XIRR

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to calculate the internal rate of return for an investment fund. I got it to work for one of the funds but I am having trouble with 2 other funds. The formula returns 0 and I know that is incorrect. If I only include some of the data, it will return a % - why would this be?
 
Hi Christie B

I think we'll need some data for this problem.

There are some bugs in the XIRR function though!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Christie B said:
I am trying to calculate the internal rate of return for an
Click to expand...
investment fund. I got it to work for one of the funds but I am having
trouble with 2 other funds. The formula returns 0 and I know that is
incorrect. If I only include some of the data, it will return a % -
why would this be?
 
Hi Christie B

Not at all. But I'm off to bed shortly; even us Aussies sleep when
there's no cricket on!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Christina!

I've looked at the workbook and there are a few problems. But there
are solutions:

XIRR like IRR has a third argument. It a guess rate. In the absence of
this argument it uses a default rate of 10%. That usually works but in
those two cases you have negative returns. If you insert in the
"rogue" formulas -0.9, you'll find that they will both resolve.

In fact, to my mind this is a bug. If I use the IRR on these cash
flows the function returns an error message #DIV/0! Whilst I never
liked that error message, at least it is an error message and not a
(potentially) rational answer. The 0 return is potentially rational
and in my view this is a bug.

You'll find that the guess rate of -0.9 will almost always produce an
answer irrespective of whether the return is positive or negative.


But there are still problems! The classic test of the XIRR is that
XNPV using the XIRR return as a discount rate should, by definition,
return 0. But with your cash flows the answer I get is #NUM! I perform
my test by going back to basics and discounting each cash flow at the
daily equivalent of the calculated XIRR for the number of days from
the initial flow. The sum of those discounted flows approaches 0.

All that before the second cup of coffee!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi PHONG!

Try setting up a mirror column that reverses the signs of the flows
and see what happens.

What happens if you apply XNPV to the cash flow?

You can always send a copy of just the flow to me and I'll take a look
and report back. There are a number of problems with the XIRR and XNPV
functions that require base formula crosschecks.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
PHONG said:
Norman, I too have a series of cashflows that is definitely negative
Click to expand...
IRR (one positive CF, but mostly negative CF's including the original
investment). However, I have used the value of -0.9 as the XIRR
guess, and still return a #NUM error. Any other suggestions?
 
Back
Top