I tried to calculate treasury bonds YTM from their clean prices through different formula on excel ("yield" or "rate") and found the same result. However, I do not know whether using the "yield" function, it is more suitable to set the delivery date to the date of the trade or the last date of coupon given that using the date of the trade gives a different result than the "rate" function?

I have another question regarding the determination of yields on the basis of prices published at auctions. Is the same formula used?


1 Answer 1


The correct date to use is the Settlement Date, which is one business day after the Trade Date, or in the case of a newly auctioned security, the Issue Date.

The Issue Date is typically between T+2 and T+1week for coupon bearing Treasuries. See the Treasury Direct website for examples.

The yield function is preferable to the rate function in exceL. From reading the descriptions, the latter won't work if the settlement date is in the middle of a coupon period.

  • $\begingroup$ Settlement dates depend on market practice. Is it common to set auction settlement on TD+1? What about the formula to be used in excel? which one is more suitable? $\endgroup$
    – novus24
    Jul 12, 2016 at 7:10
  • $\begingroup$ Does the yield function provide a yield for dirty prices? As far as I know, these yields could not be used to compare different bonds? $\endgroup$
    – novus24
    Jul 13, 2016 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.