YTM problem in excel?

[karate718]

Good morning,

I am having a problem with the YIELDMAT function in excel. I am simply checking it against a publicly traded treasury and I am calculating a different yield than is being quoted on the website. I checked with my financial calculator, and got the same result as the website which leads me to believe that I am doing something incorrectly in excel.

Here are the parameters:

Settlement 1/19/2011
Maturity 11/15/2020
Issue Date 1/18/2011
Coupon 2.625%
Price 93.3359375

Instead of using cell references, I even typed in the values manually, so the formula I am using is: =YIELDMAT(40562,44150,40561,0.02625,93.3359375)

Excel is calculating a YTM of 3.539%, whereas the website and my calculator both come up with 3.45%.

Is there something I am doing incorrectly?

Thank you.

 

[karate718]

Ok, I am still digging into this. I changed the mode in my financial calculator to "Begin" instead of "End," meaning that the payments occur at the beginning of the period instead of the end. Lo and behold, my calculator matches what I have in excel.

In the case of a bond, it should be "end," since the bond payments are paid at the end of each 6 month period, not the beginning.

Is there a way to change this in excel?

Thank you.

 

[karate718]

I think I have figured out the issue. Looking at the definition of the YIELDMAT formula, it says "Returns the annual yield of a security that pays interest at maturity." So, it would work for a zero coupon bond. The bond I am working with pays interest semi-annually and I am better off using the YIELD function. That formula is not quite as idiot proof, but it's pretty close. The values it is giving me do match the other calculations I have done and looked up so I am going to go with that.

Thanks for reading.