Difference between Excel Yield Function and Manual Computation of YTM

CFA Level I Quant
#1

I was trying to solve this question:

Example 9
Example 9542×640 73.3 KB

As you can see the example 9 solution, the answer is -1.445% by doing it manually, however when I try to use Excel YIELD function I get an answer slightly different:

=YIELD(DATE(2020,1,1), DATE(2022,1,1), 0.02, 100, 93.091, 1, 1)
result: -0.014803

  • The bond was issued on January 1, 2020, and I’m computing the yield as of January 1, 2022.
  • It pays an annual 2% coupon, so frequency = 1.
  • The bond price two years later is 93.091, and the redemption value is 100.

At first I thought the “basis” input of YIELD function should be change to other values rather than 0 (30/360) but other values didn’t change the result even a bit. (This is a secondary question that why changing basis input from 0 to 1, 2, 3 doesn’t change the result?)

I am getting 2 different results of -1.445% by manual computation of YTM and -1.480% by Excel YIELD function and I can not figure out why this deviation happened.

My guess is since Excel is using a Newtonian estimation for solving the rate equation, it has some error in the result but this error is huge to me.

#2

You can get -0.014803 if the reinvestment rate is the same as the interest rate you are solving for.
It’s a root of 100\times (1+r)^{2}=2\times (1+r)+2+93.091

-0.014449 is a root of 100\times (1+r)^{2}=2\times (1+0.02)+2+93.091

1 Like
#3

Now I see, thank you so much. I had spent hours trying to figure out why this happened and finally your answer made it all the way clear for me.

1 Like