# How to write XIRR formula on excel using different Year basis than 365.

D

#### Darpan Patel

This is the Analysis tool function of XIRR in MS Excel 2007 which uses
year basis as 365. Go to XIRR function 'help' to find the formula used
for XIRR calculation. Let's say We have some loan product with only
254 days year basis(business days only). How can we create function in
Excel which fulfills our requirement.

I need to find XIRR with 254 days calender or 360 days calender.

date cashflow
22-Dec-07 -98000
1-Jan-08 16275 IRR XIRR (365 days) XIRR (254 days)
1-Mar-08 16275 10% 0.597120339
30-Oct-08 16275
15-Feb-09 16275
1-Apr-09 16275
1-Jan-09 16275
1-Mar-09 16275
30-Oct-09 16275
15-Feb-10 16275
1-Apr-10 16275

I'll appreciate everyone's thought and help.

Thanks,
DP

This is the Analysis tool function of XIRR in MS Excel 2007 which uses
year basis as 365. Go to XIRR function 'help' to find the formula used
for XIRR calculation. Let's say We have some loan product with only
254 days year basis(business days only). How can we create function in
Excel which fulfills our requirement.
I need to find XIRR with 254 days calender or 360 days calender.

First, you would need to explain (and implement) how to take the
difference between two dates in terms of only 254 business days or 360-
day years.

For example, what is the correct difference between 1-Jan-08 and 1-
Mar-08?

YEARFRAC might be used for the 360-day basis. But I have never tested
it myself. I have numerous defects in some ATP financial functions.

But even if you did that, you would then need to develop your own
algorithm for converging on the IRR in those terms. That's doable;
but it's a lot of work (or web searching).

Alternatively, I suspect you would not see a significance difference
between the correct approach (above) and the following approximation:

(1+XIRR(...))^254/365 - 1

(1+XIRR(...))^360/365 - 1

YEARFRAC might be used for the 360-day basis.  But I have never
tested it myself.  I have numerous defects in some ATP financial
functions.

And perhaps NETWORKDAYS with an appropriate list of holidays will work

And perhaps NETWORKDAYS with an appropriate list of holidays will work

Appreciated your response. YEARFRAC and NETWORKDAYS are good to know
functions. These things might work but when converting the calculated
XIRR (which is based on 365 days) to XIRR on 360 might give wrong
result I think. 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.

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
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

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.

Embellishment....

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.

That is, using YEARFRAC to compute date differences with a divisor of
360, and using NETWORKDAYS to compute date differences with a divisor