"joeu2004" wrote in message
...
"W" wrote:
I am perplexed why does the Yield to Maturity function
need to be fed an "issue date" as a parameter? If I
invest in a bond today with maturity of 2/1/2013 and the
bond sell for 80% of par and has a 5% coupon, why would I
care about an issue date? The formula is:
YIELDMAT(settlement,maturity,issue,rate,pr,basis)

I doubt that is the formula verbatim that you are using, unless those are
actually named references. In any case, you need to tell us the values of
each referenced cell.

And to answer your question....

Many bonds are purchased in a secondary market some time after the issue
date.

Although the Help page for YIELDMAT neglects to mention it, I believe that
YIELDMAT solves for "yld" in the PRICEMAT formula. See the Help page for
PRICEMAT[*].

The point is: note that PRICEMAT is reduced by the fraction of interest
applicable to the period between issue and settlement dates.

The reason is: that interest gets paid to the previous bond holder.

Note: YIELDMAT does not allow for settlement = issue, IIRC. It is a
defect, IMHO. If the settlement and issue dates are the same, enter
issue+1
for the YIELDMAT settlement parameter. Usually, that causes only a very
small error in the result. YMMV.


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. If we are buying the bond at
less than par, how can a yield to maturity be lower than the coupon value of
8.63%?

Where is error in this formula?

--
W