Difference between XIRR and YIELD

[Guest]

Hi,

Can someone explain why in very easy samples XIRR gives a slightly
different (and wrong) result than YIELD?

Here is one sample:
Bond Face value 100,000. Issue march, 4, 2005. Maturity march, 4, 2006.
Coupon 5%; basis 3 (365/365); Settlement June 1st 2005, price 101.

This gives a yield of 3.5976969%

Now if you try to use XIRR, with 2 cashflows as:

June 1st 2005-> -102,219.18 (-(101,000+accrued cpn as of June 1st))
March 4 2006 -> 105,000.00

This gives a xirr of 3.6133815%. This is wrong and if you try to check
it by computing how you get at maturity with such a rate you get
(102,219.18+102,219.18*3.6133815%*276/365)=105,012.12

In fact xirr gives the same result as yield only when the settlement
date is equal to the issue or a coupon date (i.e. when the accrued
coupon is 0 at settlement date).

Thanks in advance

 

[Roman]

Hi,
in my opinion the XIRR function is designed for more complicated
calculations and it is one of many possible ways of IRR calculations.
The results are not absolutly exact as the function uses iterations and
it probably has a given level of accuracy. For more info see these:

http://packetguy.blogspot.com/2005/06/excel-xirr-and-xnpv-functions.html

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q214105

 

[Guest]

That was also my first thought. But I have written my own XIRR routine
in order to control the accuracy, and although I get a more precise
result than excel, I still have a difference.

Is it possible that somehow xirr assumes you reinvest the positive cash
flows at the same rate (i.e. 105012,12 represents the NPV of 105000),
while yield does not?

 

[Roman]

I don´t think so. I'd rather suspect the way IR is annualized. You use
linear method but I´m not sure excel does it the same way.

 

[Roman]

That's it:

105000=102219.18 *(1,0361338^(1/365))^276

 

[sebas_cp]

The first possible source for the differencecould be that the
bondcashflows and the XIRR cashflows will not be the same. For one, the
bond will use bond basis for the coupons regardless of the days in the
coupon periods. This means, that a semmianaual bond will pay C/2, a
Quarterly bond will pay C/4 and so on. This could be one source of
differences.

The most probable one, I think is a difference in CONVENTION., the XIRR
will return an annual compounding interest rate, while the Yield will
depend on the coupon frequency. Since the coupon is annual in your
example, and the bond has less than a year to go, the yield will be
expressed in simple interest(or compounded for 276 days if that makes
sense to you):

(105/102.22-1)*360/276 = 3.5976969%,

on the other hand if you calculate:

(105/102.22)^(365/276)-1 = 3.613381%

You can convert the yield to XIRR in this case by:

((XIRR%+1)^(276/365)-1)*365/276

Interest Rate conventions are confusing, hope this helpes!!

cheers.

 

[Guest]

aaaah!

Thanks very much. I *think* I understood. In my case xirr returns a
compounded rate, while yield "knows" that the coupon is final, and just
compute a straight rate from the settlement date/price to the maturity.

I will make some other test...