"W" wrote:
Purchase Date = K20 = 4/9/2012
Maturity = L20 = 11/1/2017
Coupon = M20 = 8.63%
Percent of Par at Purchase = N20 = 94%
Par Value = D20 = $1000
Pays Bi Annually

Using the YIELD function I enter:
=YIELD(K20,L20,M20,N20*D20,D20,2)
This is giving back 2.01% which looks wrong.
[....]
Where is error in this formula?

Following bond industry convention, the price (pr) and redemption value must
be stated as an amount per units of $100 face value. See the YIELD Help
page.

(That is very confusing, IMHO. But it is similar to my HP-12C calculator
inputs.)

So you should write the equivalent of:

=YIELD(DATE(2012,4,9),DATE(2017,11,1),8.63%,94,100 ,2)

(Note: I ass-u-me that 4/9/2012 and 11/1/2017 are in the form m/d/yyyy, not
d/m/yyyy.)

That returns about 10.06%.

In your formula, you should use N20*D20/10 and D20/10 for the 4th and 5th
parameters. We divide by 10 because D20/100 = 10.

(Note: Although you use "par value" and "redemption value" interchangeably,
they might be different for a callable bond and you actually compute "yield
to call".)

As a double-check, note that YTM is the IRR of the cash flows. So we can
set up the following cash flows (forgive me if things do not align well in
your view):


B C
1 4/9/2012 -940.00
2 10/9/2012 43.15
3 4/9/2013 43.15
4 10/9/2013 43.15
5 4/9/2014 43.15
6 10/9/2014 43.15
7 4/9/2015 43.15
8 10/9/2015 43.15
9 4/9/2016 43.15
10 10/9/2016 43.15
11 4/9/2017 43.15
12 10/9/2017 43.15
13 11/1/2017 1010.91
14 4/9/2018

C13 is the redemption value (1000) plus interest for the fractional coupon
period ending on the date in C14.

XIRR(C1:C13,B1:B13) returns about 10.39%.

That is significantly different because XIRR assumes compounded interest,
whereas semiannual coupons are computed using simple interest. That is, the
annual rate is divided by 2.

We can compensate to some degree by computing the following instead:

2*(SQRT(1+XIRR(C1:C13,B1:B13))-1)

That returns about 10.14%, compared to about 10.06% for YIELD.

There are many possible reasons for the difference. But hopefully that is
close enough to demonstrate the correctness of the YIELD result [1].


-----
[1] With my own formula for the NPV and using Solver to determine the YTM, I
get about 10.04%. I am still investigating to try to understand the
difference, albeit small.