Difference between XIRR and YIELD
 

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

Difference between XIRR and YIELD
 

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?

Difference between XIRR and YIELD
 

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.

Difference between XIRR and YIELD
 

That's it:

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

Difference between XIRR and YIELD
 

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.


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

Difference between XIRR and YIELD
 

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