+ Reply to Thread
Results 1 to 7 of 7

Difference between XIRR and YIELD

  1. #1

    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


  2. #2
    Roman
    Guest

    Re: Difference between XIRR and YIELD

    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/0...functions.html

    http://support.microsoft.com/default...;en-us;Q214105


  3. #3

    Re: 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?


  4. #4
    Roman
    Guest

    Re: Difference between XIRR and YIELD

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


  5. #5
    Roman
    Guest

    Re: Difference between XIRR and YIELD

    That's it:

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


  6. #6

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


    [email protected] 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



  7. #7

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1