Appreciated your response. YEARFRAC and NETWORKDAYS are
good to know functions. These things might work
For your example, YEARFRAC returns different values than I would
expect.
I have not yet intuited the algorithm that YEARFRAC might be using.
But the relative error is typically less than 0.01%. So it might not
be too bad to use YEARFRAC.
NETWORKDAYS without holidays returns an average of 262 days for each
of the years between 2007 and 2010 inclusive. Why do you want to work
with a 254-day year?
If the difference is due to holidays, that might make it difficult to
use NETWORKDAYS in a VBA implementation. You need to be sure the
holiday array (which can be in an Excel range) includes holidays for
all of the years between the first and last cash flow.
but when converting the calculated XIRR (which is based on 365 days)
to XIRR on 360 might give wrong result I think.
Of course it gives "wrong" results. I said as much myself. The
question is: is it significantly wrong?
Well, I was surprised by the magnitude of the difference even for
360. For your example, (1+XIRR)^(360/365)-1 is about 1.2 percentage
points below the correct IRR using YEARFRAC in computing the discount
rate for each cash flow.
Of course, the difference using 262 (or 254) is even more significant.
Interestingly, however, the difference among XIRRs using 365, 360 and
262 (for 254) in computing the discount rate for each cash flow is not
as great as I expected. The respective IRRs differ by less than 0.38
percentage points, a relative error of less than 1%.
So you might be able to make do with XIRR unmodified, even if your
basis for a year is not 365 days.
I might be possible if we can implement the XIRR formula given by
MS excel in help section manually in Excel cell and change 365 to
360 or whatever year basis.
The IRR must be computed using an interative algorithm. It cannot be
computed by Excel formulas directly.
You might be able to use Goal Seek or Solver. But I think a VBA
implementation of a Newton-Raphson algorithm is more reliable and
probably more efficient.
Some ancillary observations....
1. Why are some dates out of order in your example?
Their incorrect order has no impact on an XIRR-like computation; and
it has no impact on an IRR-like computation, but only because all the
cash flows amounts are the same.
However, I wonder if 1-Jan-09 and 1-Mar-09 should be some other dates
between 1-Apr-09 and 30-Oct-09.
2. Your IRR result (about 10%) is incorrect because you misused the
IRR function.
You probably computed =IRR(B2:B12). But the IRR function assumes that
the intervals between cash flows are the same. That is not the case
for your example.
I note that your cash flow intervals are roughly multiples of
semimonths. So you should be able to use the IRR function if you fill
in the table with semimonthly dates with zero cash flows.
Of course, that would be a semimonthly IRR. To compare with XIRR, you
would need to annualize the IRR result by computing (1+IRR(...))^24-1.
Indeed, the semimonthly IRR is about 1.94%, which is about 58.57% when
annualized, compared to about 59.71% returned by the XIRR function.
Caveat: The IRR function returned an error initially. I had to
provide a "guess". For that, I used (1+XIRR(...))^(1/24)-1, the
annual XIRR rate on a semimonthly basis.
This is a common problem with the Excel IRR function, at least in
XL2003. It might have been improved in XL2010, which did improve some
financial functions.
