"joeu2004" wrote in message
...
"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)

So you are telling me that YIELD requires you to normalize all values to
$100 par value? So a $1000 bond is normalized to a $100 par bond? A $25
preferred is normalized to a $100 par bond?

Wow, could they make this thing any more obscure?

I confirm that your version gives a correct result and the alternate using
real par values of:

=YIELD(DATE(2012,4,9),DATE(2017,11,1),8.63%,940,10 00,2)

fails to give a correct result.



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

I am using the US standard of MM/DD/YYYY.

Are you telling me if I have my Excel set for a date input of MM/DD/YYYY and
enter a date that YIELD will convert my date into a European date format of
DD/MM/YYYY?

That means I have to rewrite all of my cells with the incredibly verbose
format like:

=YIELD(DATE(YEAR(mydatecell),MONTH(mydatecell),DAY (mydatecell)),.....)

My God....

--
W