Forum Discussion

massanpc's avatar
massanpc
Copper Contributor
Aug 03, 2020

YIELD Function return a strange number

Assumptions:

  • settlement: Jul 31st, 2020
  • maturity: Sep 30th, 2020
  • rate: 2%
  • pr: 100.163338941426
  • yld: 1%
  • redemption: 100
  • frequency: 4
  • basis: 2

Based on the above assumptions:

  • PRICE(settlement, maturity, rate, yld, redemption, frequency, [basis])=100.163338941426
  • YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])=1.01125545674913%

Question:

  • The YIELD function must return 1%, but it actually returns 1.01125545674913%.
  • The difference 1% and 1.01125545674913% is too large if the difference is caused by using floating point numbers.
  • It seems that the YIELD function doesn't work.  Is this a bug?
excel
Formulas and Functions

2 Replies

    • massanpc's avatar
      massanpc
      Copper Contributor
      Aug 03, 2020

      SergeiBaklan 

      Thank you for the reply.

       

      It makes sense.  On https://support.microsoft.com/en-us/office/yield-function-f5f5ca43-c4bd-434f-8bd2-ed3c9727a4fe, the following remarks are posted:

      • If there is one coupon period or less until redemption, YIELD is calculated as follows:

      I've just calculated the above equation with the following numbers:

      • par seems to be a typo in price/pr, and it is 100.163338941426
      • A is equal to COUPDAYBS(settlement,maturity,frequency,[basis])=31
      • E is equal to COUPDAYS(settlement,maturity,frequency,[basis])=90
      • DSR=E-A=90-31=59

      The YIELD function must return 0.0100000000000147891680, which is almost 1%.

      I've also checked the calculation using https://www.wolframalpha.com/input/?i=%28%28100%2F100%2B2%2F100%2F4%29-%28100.163338941426%2F100%2B31%2F90*2%2F100%2F4%29%29%2F%28100.163338941426%2F100%2B31%2F90*2%2F100%2F4%29*%284*90%29%2F%2890-31%29&lang=ja.

       

      It seems that the YIELD function does not calculate as defined and the function is not reliable.

Resources