YIELD Function return a strange number

%3CLINGO-SUB%20id%3D%22lingo-sub-1562289%22%20slang%3D%22ja-JP%22%3EYIELD%20Function%20Return%20a%20Strange%20Number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562289%22%20slang%3D%22ja-JP%22%3E%3CP%3EPromptings%3A%3C%2FP%3E%3CUL%3E%3CLI%3Esettlement%3A%20Jul%2031st%2C%202020%3C%2FLI%3E%3CLI%3Ematurity%3A%20Sep%2030th%2C%202020%3C%2FLI%3E%3CLI%3Erate%3A%202%25%3C%2FLI%3E%3CLI%3Epr%3A%20100.163338941426%3C%2FLI%3E%3CLI%3Eyld%3A%201%25%3C%2FLI%3E%3CLI%3Eredemption%3A%20100%3C%2FLI%3E%3CLI%3Efrequency%3A%204%3C%2FLI%3E%3CLI%3Ebasis%3A%202%3C%2FLI%3E%3C%2FUL%3E%3CP%3EBased%20on%20the%20above%20assumtions%3A%3C%2FP%3E%3CUL%3E%3CLI%3EPRICE(settlement%2C%20maturity%2C%20rate%2C%20yld%2C%20redemption%2C%20frequency%2C%20%5Bbasis%5D)%3D100.16338941426%3C%2FLI%3E%3CLI%3EYIELD(settlement%2C%20matteity%2C%20rate%2C%20pr%2C%20redemption%2C%20frequency%2C%20%5Bbasis%5D)%3D1.01125545674913%25%3C%2FLI%3E%3C%2FUL%3E%3CP%3EQuestion%3A%3C%2FP%3E%3CUL%3E%3CLI%3EThe%20YIELD%20function%20must%20return%201%25%2C%20but%20it%20actually%20returns%201.01125545674913%25.%3C%2FLI%3E%3CLI%3EThe%20difference%201%25%20and%201.01125545674913%25%20is%20too%20large%20if%20the%20difference%20is%20caused%20by%20using%20floating%20point%20numbers.%3C%2FLI%3E%3CLI%3E%3CSPAN%3EIt's%20That%20The%20Yield%20Function%20Doesn't%20Work.%26nbsp%3B%20Is%20this%20a%20bug%3F%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1562289%22%20slang%3D%22ja-JP%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562518%22%20slang%3D%22en-US%22%3ERe%3A%20YIELD%20Function%20return%20a%20strange%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562518%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747633%22%20target%3D%22_blank%22%3E%40massanpc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20that's%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fwestclintech.com%2FBlog%2Ftabid%2F132%2FEntryId%2F74%2FCalculate-the-PRICE-of-a-bond-with-a-monthly-coupon.aspx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwestclintech.com%2FBlog%2Ftabid%2F132%2FEntryId%2F74%2FCalculate-the-PRICE-of-a-bond-with-a-monthly-coupon.aspx%3C%2FA%3E%26nbsp%3Bthe%20reason.%20Jul31%20and%20Sep30%20are%20within%20one%20quarter%2C%20if%20take%20Jun30%20result%20is%20more%20reliable%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1562876%22%20slang%3D%22en-US%22%3ERe%3A%20YIELD%20Function%20return%20a%20strange%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1562876%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20makes%20sense.%26nbsp%3B%26nbsp%3BOn%26nbsp%3B%3CA%20title%3D%22YIELD%20function%20-%20Office%20Support%22%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fyield-function-f5f5ca43-c4bd-434f-8bd2-ed3c9727a4fe%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%3EYIELD%20function%20-%20Office%20Support%3C%2FSPAN%3E%3C%2FA%3E%2C%20the%20following%20remarks%20are%20posted%3A%3C%2FP%3E%3CUL%3E%3CLI%3EIf%20there%20is%20one%20coupon%20period%20or%20less%20until%20redemption%2C%20YIELD%20is%20calculated%20as%20follows%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22566caee5-809e-410b-9459-9b148525f490%22%20style%3D%22width%3A%20423px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209984i9501D5A36933FFA3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22566caee5-809e-410b-9459-9b148525f490%22%20alt%3D%22566caee5-809e-410b-9459-9b148525f490%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI've%20just%20calculated%20the%20above%20equation%20with%20the%20following%20numbers%3A%3C%2FP%3E%3CUL%3E%3CLI%3Epar%20seems%20to%20be%20a%20typo%20in%20price%2Fpr%2C%20and%20it%20is%20100.163338941426%3C%2FLI%3E%3CLI%3EA%20is%20equal%20to%26nbsp%3BCOUPDAYBS(settlement%2Cmaturity%2Cfrequency%2C%5Bbasis%5D)%3D31%3C%2FLI%3E%3CLI%3EE%20is%20equal%20to%26nbsp%3BCOUPDAYS(settlement%2Cmaturity%2Cfrequency%2C%5Bbasis%5D)%3D90%3C%2FLI%3E%3CLI%3EDSR%3DE-A%3D90-31%3D59%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22equation.gif%22%20style%3D%22width%3A%20328px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209989i0048960272D97132%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22equation.gif%22%20alt%3D%22equation.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20YIELD%20functi%3CSPAN%3Eon%20must%20return%26nbsp%3B0.0100000000000147891680%2C%20which%20is%20almost%201%25.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI've%20also%20checked%20the%20calculation%20using%26nbsp%3B%3CA%20title%3D%22((100%2F100%2B2%2F100%2F4)-(100.163338941426%2F100%2B31%2F90*2%2F100%2F4))%2F(100.163338941426%2F100%2B31%2F90*2%2F100%2F4)*(4*90)%2F(90-31)%20-%20Wolfram%7CAlpha%22%20href%3D%22https%3A%2F%2Fwww.wolframalpha.com%2Finput%2F%3Fi%3D%2528%2528100%252F100%252B2%252F100%252F4%2529-%2528100.163338941426%252F100%252B31%252F90*2%252F100%252F4%2529%2529%252F%2528100.163338941426%252F100%252B31%252F90*2%252F100%252F4%2529*%25284*90%2529%252F%252890-31%2529%26amp%3Blang%3Dja%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EWolfram%20Alpha%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20that%20the%20YIELD%20function%20does%20not%20calculate%20as%20defined%20and%20the%20function%20is%20not%20reliable.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?
2 Replies

@massanpc 

Perhaps that's http://westclintech.com/Blog/tabid/132/EntryId/74/Calculate-the-PRICE-of-a-bond-with-a-monthly-coupo... the reason. Jul31 and Sep30 are within one quarter, if take Jun30 result is more reliable

@Sergei Baklan 

Thank you for the reply.

 

It makes sense.  On YIELD function - Office Support, the following remarks are posted:

  • If there is one coupon period or less until redemption, YIELD is calculated as follows:
    566caee5-809e-410b-9459-9b148525f490

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

equation.gif

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

I've also checked the calculation using Wolfram Alpha.

 

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