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.