Forum Discussion
YIELD Function return a strange number
Perhaps that's http://westclintech.com/Blog/tabid/132/EntryId/74/Calculate-the-PRICE-of-a-bond-with-a-monthly-coupon.aspx the reason. Jul31 and Sep30 are within one quarter, if take Jun30 result is more reliable
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.